-
Jan 13th, 2006, 04:52 AM
#1
[.NET 1.x] Filling a DataTable using a DataReader
Note that this code is redundant from .NET 2.0, where you have the DataTable.Load method.
This example uses OleDb but the principle is directly transferable to all other Data namespaces.
VB Code:
Public Function GetFilledTable(ByVal query As String, ByVal connection As OleDbConnection) As DataTable
Dim command As New OleDbCommand(query, connection)
connection.Open()
Dim reader As OleDbDataReader = command.ExecuteReader(CommandBehavior.KeyInfo Or CommandBehavior.CloseConnection)
Dim schema As DataTable = reader.GetSchemaTable()
Dim columns(schema.Rows.Count - 1) As DataColumn
Dim column As DataColumn
'Build the schema for the table that will contain the data.
For i As Integer = 0 To columns.GetUpperBound(0) Step 1
column = New DataColumn
column.AllowDBNull = CBool(schema.Rows(i)("AllowDBNull"))
column.AutoIncrement = CBool(schema.Rows(i)("IsAutoIncrement"))
column.ColumnName = CStr(schema.Rows(i)("ColumnName"))
column.DataType = CType(schema.Rows(i)("DataType"), Type)
If column.DataType Is GetType(String) Then
column.MaxLength = CInt(schema.Rows(i)("ColumnSize"))
End If
column.ReadOnly = CBool(schema.Rows(i)("IsReadOnly"))
column.Unique = CBool(schema.Rows(i)("IsUnique"))
columns(i) = column
Next i
Dim data As New DataTable
Dim row As DataRow
data.Columns.AddRange(columns)
'Get the data itself.
While reader.Read()
row = data.NewRow()
For i As Integer = 0 To columns.GetUpperBound(0)
row(i) = reader(i)
Next i
data.Rows.Add(row)
End While
reader.Close()
Return data
End Function
Last edited by jmcilhinney; Oct 28th, 2008 at 06:01 PM.
-
Feb 28th, 2006, 06:24 PM
#2
Junior Member
Re: Filling a DataTable using a DataReader
Once i have it with the names in it and they choose which name they want. Do I have to use the even listener for the combo box and use the id as the select statement or can i pass the table somehow to it? Global? (i know in java this is a no no) what about in the module?
-
Feb 28th, 2006, 06:29 PM
#3
Re: Filling a DataTable using a DataReader
Originally Posted by lamagra1913
Once i have it with the names in it and they choose which name they want. Do I have to use the even listener for the combo box and use the id as the select statement or can i pass the table somehow to it? Global? (i know in java this is a no no) what about in the module?
I think this question relates to something beyond the scope of this thread. The function provided takes an SQL query in a String and a valid OleDbConnection object and returns a DataTable. What you do with that DataTable is up to you and a completely seperate topic. If this realtes to an existing thread of your's, and I believe it does, then ask the question in that thread.
-
Sep 20th, 2010, 11:06 PM
#4
Fanatic Member
Re: Filling a DataTable using a DataReader
JM can I ask you why you used schema ? even you can directly use
Code:
Dim cmd As Oledb.OledbCommand("SELECT * FROM TableName")
Dim adapter As New Oledb.OledbDataAdapter(cmd, connection)
Dim table As New Datatable
adapter.Fill(table)
For counter = 0 To Table.Rows.Count - 1
Me.lstDisplay.Items.Add(table.Rows(counter).item("Column").tostring
Next
Though I like it, look like a professional code. But I don't know what its for.
P.S
The reason I asked if you notice my code I used that so many times so i think your code make it easier, i guess
Last edited by aNubies; Sep 20th, 2010 at 11:19 PM.
-
Sep 20th, 2010, 11:22 PM
#5
Re: Filling a DataTable using a DataReader
Originally Posted by aNubies
JM can I ask you why you used schema ? even you can directly use
Code:
Dim cmd As Oledb.OledbCommand("SELECT * FROM TableName")
Dim adapter As New Oledb.OledbDataAdapter(cmd, connection)
Dim table As New Datatable
adapter.Fill(table)
For counter = 0 To Table.Rows.Count - 1
Me.lstDisplay.Items.Add(table.Rows(counter).item("Column").tostring
Next
Though I like it, look like a professional code. But I don't know what its for.
The whole point of this thread, as the title says, is filling a DataTable using a DataReader. Your code uses a DataAdapter, so it's not relevant to this topic.
That said, how do you suppose a DataAdapter creates a DataTable? It goes through several methods to get there but, in the end, it creates a DataReader, calls GetSchemaTable and creates the columns in the DataTable from that, pretty much just as I've done here.
-
Sep 20th, 2010, 11:24 PM
#6
Fanatic Member
Re: Filling a DataTable using a DataReader
Is it better to use a datareader like the way you did, or just directly create adapter, table fill the table and so on.
-
Sep 20th, 2010, 11:46 PM
#7
Re: Filling a DataTable using a DataReader
Originally Posted by aNubies
Is it better to use a datareader like the way you did, or just directly create adapter, table fill the table and so on.
Like I said, this code is redundant from .NET 2.0 anyway. In .NET 1.x I probably would have used a DataAdapter to avoid the extra code. From .NET 2.0, if I wasn't intending to use the DataAdapter again to save changes, I would use a DataReader and populate a DataTable with its Load method.
-
Sep 20th, 2010, 11:49 PM
#8
Fanatic Member
Re: Filling a DataTable using a DataReader
So you are using the DataAdapter only if there is a changes happen and DataReader if its for display only and will be discarded like what you explain in your other codebank "Retrieving and Saving Data in Databases". Im just curious about your schema-schema what is that ?
-
Sep 20th, 2010, 11:54 PM
#9
Re: Filling a DataTable using a DataReader
Originally Posted by aNubies
So you are using the DataAdapter only if there is a changes happen and DataReader if its for display only and will be discarded like what you explain in your other codebank "Retrieving and Saving Data in Databases".
Yes.
Originally Posted by aNubies
Im just curious about your schema-schema what is that ?
As I declared it.
Code:
Dim schema As DataTable = reader.GetSchemaTable()
-
Sep 28th, 2010, 11:54 AM
#10
Frenzied Member
Re: Filling a DataTable using a DataReader
JMC ... can you explain why a DataReader is better than an Adapter to populate a DataTable for displaying data only? I tried both & they seem to perform about the same. Thanks...
-
Sep 28th, 2010, 12:23 PM
#11
Re: Filling a DataTable using a DataReader
Why create an extraneous object if its sole purpose is to just select data? On the back end, the adaptor is creating a reader and using it to fill the datatable... so it's not doing anything I can't also manage myself. Now, if I'm going to edit the information, the adaptor hides some of the complexity of getting the changes and submitting the changes to the appropriate commands as needed. So at that point, there is some advantages to having an adaptor over managing it yourself. But in a strict, select, read-only situation... it's over kill (IMHO).
-tg
-
Sep 28th, 2010, 12:48 PM
#12
Frenzied Member
Re: Filling a DataTable using a DataReader
So is this a good, efficient function to return a filled datatable?
Code:
Public Function FilledTable(ByVal sql As String) As DataTable
Using con As New Odbc.OdbcConnection("my connection string")
Using command As New Odbc.OdbcCommand(sql, con)
con.Open()
Using dr As Odbc.OdbcDataReader = command.ExecuteReader
Using myDataTable As New DataTable
Try
myDataTable.Load(dr)
Return myDataTable
Catch ex As Exception
Return Nothing
End Try
End Using
End Using
End Using
End Using
End Function
Can it be improved upon?
-
Sep 28th, 2010, 01:08 PM
#13
Re: Filling a DataTable using a DataReader
if it works, it works... it's not necessarily they way I'd go about doing it... but it's no more nor no less efficient than what I'd do.
-tg
-
Sep 28th, 2010, 01:10 PM
#14
Frenzied Member
Re: Filling a DataTable using a DataReader
yeah, it works, but I'm always looking for improvement. Can you post how you would do it?
-
Sep 28th, 2010, 01:16 PM
#15
Re: Filling a DataTable using a DataReader
something like this:
Code:
Public Function FilledTable(ByVal sql As String) As DataTable
Dim myDataTable As New DataTable
Try
Using con As New Odbc.OdbcConnection("my connection string")
Dim command As New Odbc.OdbcCommand(sql, con)
Using dr As Odbc.OdbcDataReader = command.ExecuteReader
myDataTable.Load(dr)
End Using
End Using
Catch ex As Exception
myDataTable = Nothing
End Try
Return myDataTable
End Function
Changes:
* One exit point for the function
* reduced the number of Usings
* I let the reader open and close the connection
* Expanded the try...catch to cover the whole function, not just the loading
-tg
-
Sep 28th, 2010, 01:23 PM
#16
Frenzied Member
Re: Filling a DataTable using a DataReader
Originally Posted by techgnome
* I let the reader open and close the connection
-tg
Where/how does this happen? As it is it errors out because the connection is not open.
Originally Posted by techgnome
* reduced the number of Usings
-tg
What is your reasoning for doing this? Is it OK to not dispose of the command & datatable, which Using does?
Last edited by nbrege; Sep 28th, 2010 at 01:40 PM.
-
Sep 28th, 2010, 06:11 PM
#17
Re: Filling a DataTable using a DataReader
tg, only a DataAdapter will implicitly open and close a connection when you call Fill, FillSchema or Update. If you're using a DataReader then you must open and close the connection yourself.
nberge, your original code was pretty close to what I'd use except:
1. The whole point of the method is to return a populated DataTable, so you definitely wouldn't dispose the DataTable.
2. I prefer a single point of exit too.
-
Sep 29th, 2010, 06:47 AM
#18
Frenzied Member
Re: Filling a DataTable using a DataReader
Originally Posted by jmcilhinney
1. The whole point of the method is to return a populated DataTable, so you definitely wouldn't dispose the DataTable.
But using a Using block does dispose of the DataTable, as I did in post #12 & it works fine. Also, what about disposing the Command object? I always thought it was good practice to dispose of objects once you are done with them. Can you clarify for me? Thanks...
-
Sep 29th, 2010, 07:00 AM
#19
Re: Filling a DataTable using a DataReader
Originally Posted by nbrege
But using a Using block does dispose of the DataTable, as I did in post #12 & it works fine. Also, what about disposing the Command object? I always thought it was good practice to dispose of objects once you are done with them. Can you clarify for me? Thanks...
You are correct, but you aren't done with the DataTable, are you? You're returning it to be used elsewhere, so it would be disposed there. The Command object isn't going to be used after your method completes so it is appropriate to dispose it, but the DataTable will be used elsewhere so it is not appropriate to dispose it.
-
Sep 29th, 2010, 07:21 AM
#20
Frenzied Member
Re: Filling a DataTable using a DataReader
Originally Posted by jmcilhinney
You are correct, but you aren't done with the DataTable, are you? ..... but the DataTable will be used elsewhere so it is not appropriate to dispose it.
Maybe it depends on how I use the function. I normally would do:
Code:
Dim myDT as DataTable = FilledTable("some sql query goes here")
Once I call the function & assign the returned data to 'myDT' I am done with the DataTable within the function as I see it, so it seems appropriate to dispose of it in the function, via a Using block. Please tell me if my understanding is incorrect.
-
Sep 29th, 2010, 07:30 AM
#21
Re: Filling a DataTable using a DataReader
Yes your understanding is incorrect. It's very simple: dispose an object if and when it is not going to be used any more. If the DataTable is going be used some more then don't dispose it. Where it's going to be used is irrelevant. All that matters is whether it will be used at all.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|