Converting Recordset to DataView

  • Thread starter Thread starter kjvt
  • Start date Start date
Jay,

This code works!

And, it helps isolate what is going wrong with my code, but I don't
understand why my code fails. Try the following modification to your
code:

Private Sub SqlServerDataBase()
Const PubsDataBase As String = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Data Source=.;Use Procedure
for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation
ID=MyWorkstation;Use Encryption for Data=False;Tag with column
collation when possible=False;Initial Catalog=pubs;"
Dim conn2 As New ADODB.Connection
Dim strProvider As String = PubsDataBase
conn2.Open(strProvider)
Dim rs As New ADODB.Recordset
rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic
rs.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
rs.Open("select * from titles", conn2)

Me.DataGrid1.DataSource = GetViewFromRS(rs)
Me.DataGrid2.DataSource = GetViewFromRS(rs)

End Sub


Private Function GetViewFromRS(ByVal pRS As ADODB.Recordset) As
DataView

Dim sAdapter As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter
Dim sTable As System.Data.DataTable = New DataTable

sAdapter.Fill(sTable, pRS)

GetViewFromRS = New DataView(sTable)
End Function

When I run this, the second datagrid has no rows. Do you have any
idea what makes this different from your example?

Many thanks to you and Cor for helping me through this.

Kees
 
Kees,
This code works!
Click to expand...
Works? It doesn't work as you expect, as it demonstrates the problem,
correct? ;-)

Interesting, same thing happens here, I should have had a clear between the
two Data.Fills in my example:

I'll ask some others to see if they have any ideas.

Jay

kjvt said:
Jay,

This code works!

And, it helps isolate what is going wrong with my code, but I don't
understand why my code fails. Try the following modification to your
code:

Private Sub SqlServerDataBase()
Const PubsDataBase As String = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Data Source=.;Use Procedure
for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation
ID=MyWorkstation;Use Encryption for Data=False;Tag with column
collation when possible=False;Initial Catalog=pubs;"
Dim conn2 As New ADODB.Connection
Dim strProvider As String = PubsDataBase
conn2.Open(strProvider)
Dim rs As New ADODB.Recordset
rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic
rs.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
rs.Open("select * from titles", conn2)

Me.DataGrid1.DataSource = GetViewFromRS(rs)
Me.DataGrid2.DataSource = GetViewFromRS(rs)

End Sub


Private Function GetViewFromRS(ByVal pRS As ADODB.Recordset) As
DataView

Dim sAdapter As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter
Dim sTable As System.Data.DataTable = New DataTable

sAdapter.Fill(sTable, pRS)

GetViewFromRS = New DataView(sTable)
End Function

When I run this, the second datagrid has no rows. Do you have any
idea what makes this different from your example?

Many thanks to you and Cor for helping me through this.

Kees
Click to expand...
<<snip>>
 
Hallo Kees (and Jay B),

The only thing I really see is that you create two datatables from one
recordset with both the same name in the function, and that datatable has in
my opinion to disapear. But it stays the underlayer from the datagrid. I
think it should not even display one datagrid.

This should be the newest procedure to create an unvisable array of
datatables. (The dataview is no table, but a view on the table). I tried
what happens if I supply the new datatable to the function and than all goes
well.

I tried it while I changed the dataview for the datatable as datasource for
the datagrid (what is a normal datasource), and then both datagrids where
empty. (As I expected it should be).

It took me longer so Jay B did already answer, a pity.

Cor
 
Jay,

Kees,
Works? It doesn't work as you expect, as it demonstrates the problem,
correct? ;-)
Click to expand...

Oops. You're right, it really doesn't work. I was fooled by the
missing clear statement.
Interesting, same thing happens here, I should have had a clear between the
two Data.Fills in my example:


I'll ask some others to see if they have any ideas.
Click to expand...

I'll keep my fingers crossed. I don't really want to write the code
to do this manually.
Jay

news:[email protected]...
Click to expand...


Kees
 
Hi Jay B,

It becomes very intresting, I was curious if it was maybe a table staying on
the heap (special for you I use that word that I always avoid) with a
reference to it from the dataview.

But it is stranger, try this code.

Or do I understand it not totaly anymore and is there an easy explenation.
(there are things totaly unneeded which I normaly never would do, but I do
that to avoid that you will test it again, by example there is in the
documentation that you have to close the recordset for dispossing the
dataset (I did try it with a dataset also and got the same result).

So I am curious what you think about this.

Cor

Me.DataGrid1.DataSource = GetViewFromRS(rs)
Me.DataGrid2.DataSource = GetViewFromRS(rs)
rs.Close()
conn2.Close()
rs = Nothing
DirectCast(Me.DataGrid1.DataSource, DataView).Table.Dispose()
DirectCast(Me.DataGrid2.DataSource, DataView).Table.Dispose()
GC.Collect()
Dim sta As String = DirectCast(Me.DataGrid1.DataSource,
DataView).Table.TableName
Dim stb As String = DirectCast(Me.DataGrid2.DataSource,
DataView).Table.TableName
'with debugging sta = "0" and stb is "1"

End Sub
Private Function GetViewFromRS(ByVal pRS As ADODB.Recordset) As DataView
Static jay As Integer = 0
Dim sAdapter As New OleDb.OleDbDataAdapter
Dim sTable As New DataTable(jay.ToString)
sAdapter.Fill(sTable, pRS)
GetViewFromRS = New DataView(sTable)
sTable = Nothing
jay += 1
End Function
 
Cor,
As far as I can tell, something happens to the ADODB.Recordset when you use
OleDbDataAdapter.Fill method on the recordset, such that another call to
OleDbDataAdapter.Fill on that same ADODB.Recordset does not work a second
time.

It doesn't really matter how many DataTables you have (one or two) its
subsequent calls to OleDbDataAdapter.Fill that do not do anything...

I'm in the process of submitting something to MS about this, so we shall see
what they have to say. I tried searching the KB but did not find anything, I
don't remember any in Sceppa's book about this either...

Hope this helps
Jay
 
Hi Jay,

I think that conclusion is not right, the main problem for me is that a
dataset that is declared in a function holds scope, there is no way to get
rid of that thing (except setting it to nothing outside the function (you do
not see that but that I did try also by using that casting of the dataview).

When I do the same function normal providing the datatable to the function
everything goes ok.
\\\
private function(byval dt as datatable, byval rs as recordset) as dataview
etc
///

It is of course a strange instruction because you cannot do anyting with
that dataview

Cor
 
Kees,
If you add a Recordset.Requery just before the OleDbDataAdapter.Fill it will
function as you expect, however I not sure you want to have to Requery the
database each time...
Private Function GetViewFromRS(ByVal pRS As ADODB.Recordset) As
DataView

Dim sAdapter As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter
Dim sTable As System.Data.DataTable = New DataTable

pRS.Requery()

sAdapter.Fill(sTable, pRS)

GetViewFromRS = New DataView(sTable)
End Function
Click to expand...

Of course my suggestion of keeping a single DataTable remains, having
GetViewFromRS create a new DataTable each time is resource wasteful.

Hope this helps
Jay
 
Cor,
I think that conclusion is not right, the main problem for me is that a
dataset that is declared in a function holds scope, there is no way to get
Click to expand...
Don't look at Kees's code then, look at my code!

Private Sub SqlServerDataBase()
Const PubsDataBase As String = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Data Source=.;Use Procedure for
Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=My
Workstation;Use Encryption for Data=False;Tag with column collation when
possible=False;Initial Catalog=pubs;"
Dim conn2 As New ADODB.Connection
Dim strProvider As String = PubsDataBase
conn2.Open(strProvider)
Dim rs As New ADODB.Recordset
rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic
rs.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
rs.Open("select * from titles", conn2)

Dim da1 As New OleDb.OleDbDataAdapter
Dim dt1 As New DataTable
da1.Fill(dt1, rs)

Dim da2 As New OleDb.OleDbDataAdapter
Dim dt2 As New DataTable
da2.Fill(dt2, rs)

Me.DataGrid1.DataSource = dt1
Me.DataGrid1.DataSource = dt2

End Sub

dt2 is empty while dt1 has records in it, Kees & I would not expect dt2 to
be empty, as the Recordset itself has records in it. If you use Requery on
rs before filling dt2, then dt2 has records.

It seems that the GetViewFromRS is largely just confusing the issue, hence
in my sample that shows the underlying problem does not include that
function.

Hope this helps
Jay
 
Hi Jay,

I am not looking at Kees code anymore, I know how to get it working on 100
different ways, but please have a look at the part that I did send.

It confuses me that that datatable is still alive while it is created in a
sub.

Therefore because you are in that part theoretical good, I ask if I see
something real wrong.

I am not even able to kill that datatable.
not with dispose and not with = nothing
while it is created in that sub.

Cor
 
Jay,

Kees,
If you add a Recordset.Requery just before the OleDbDataAdapter.Fill it will
function as you expect, however I not sure you want to have to Requery the
database each time...
Click to expand...

Unfortunately, I cannot requery. There may be unsaved changes in the
recordset that I want to display, but not commit to the database. I
guess I'll write a routine that spins through the recordset rows and
updates the datatable "manually" after I have used Fill to create the
table.
Of course my suggestion of keeping a single DataTable remains, having
GetViewFromRS create a new DataTable each time is resource wasteful.
Click to expand...

Do you mean wasteful in terms of the time it takes to create the
table? I thought that the prior table would be released when I bind
the datagrid to the new table/view, so the memory would be freed (once
garbage collected). Anyway, I agree that there's no need to create a
table each time. This will be especially true when I fill the table
manually.

Thank you again.

Kees
 
Cor,
The DataTable will continue to exist as long as you have a reference to it.
Seeing as you were returning a DataView, which you set the
DataGrid.DataSource property to, the DataTable still has a reference to it.

Remember that DataView has a reference to the DataTable it is built over.

Hope this helps
Jay
 
Kees,
Do you mean wasteful in terms of the time it takes to create the
table? I thought that the prior table would be released when I bind
Click to expand...
We discussed this and I thought you understood.

Its wasteful in both the time it takes to build the DataTable, and its
wasteful in that you have N copies of the data, you really only need 2
copies of the data. One in the Recordset and one in the DataTable!

Hope this helps
Jay
 
Kees,
guess I'll write a routine that spins through the recordset rows and
updates the datatable "manually" after I have used Fill to create the
table.
Click to expand...
Rather then refilling the DataTable from scratch each time, I would consider
encapsulating the Recordset & DataTable in a class that uses the events of
the Recordset & DataTable to keep the two in sync.

I would start with something like:

Public Class MyDataTable

Private WithEvents m_rs As ADODB.Recordset
Private WithEvents m_table As DataTable

Public Sub New(ByVal rs As ADODB.Recordset, ByVal table As
DataTable)
m_rs = rs
m_table = table
End Sub

Public Readonly Property Recordset() As ADODB.Recordset
Get
Return m_rs
End Get
End Property

Public Readonly Property Table() As DataTable
Get
Return m_table
End Get
End Property

Private Sub m_rs_RecordChangeComplete(ByVal adReason As
ADODB.EventReasonEnum, ByVal cRecords As Integer, ByVal pError As
ADODB.Error, ByRef adStatus As ADODB.EventStatusEnum, ByVal pRecordset As
ADODB.Recordset) Handles m_rs.RecordChangeComplete
' update m_table with new values from m_rs
End Sub

Private Sub m_table_RowChanged(ByVal sender As Object, ByVal e As
System.Data.DataRowChangeEventArgs) Handles m_table.RowChanged
' update m_rs with new values from m_table
End Sub

End Class

There may be other events you need to monitor, also watch out for recursive
updating.

I have not heard back on why the OleDbDataAdapter.Fill method behaves the
way it does.

Hope this help
Jay
 
Back
Top