Filling a DataTable using a DataReader

[jmcilhinney]

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.

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

[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?

[jmcilhinney]

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.

[aNubies]

JM can I ask you why you used schema ? even you can directly use

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

[jmcilhinney]

JM can I ask you why you used schema ? even you can directly use

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.

[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.

[jmcilhinney]

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.

[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". Im just curious about your schema-schema what is that ?

[jmcilhinney]

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.

Im just curious about your schema-schema what is that ?

As I declared it.

Dim schema As DataTable = reader.GetSchemaTable()

[nbrege]

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...

[techgnome]

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

[nbrege]

So is this a good, efficient function to return a filled datatable?

    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?

[techgnome]

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

[nbrege]

yeah, it works, but I'm always looking for improvement. Can you post how you would do it?

[techgnome]

something like this:

     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

[nbrege]

* 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.

* 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?

[jmcilhinney]

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.

[nbrege]

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...

[jmcilhinney]

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.

[nbrege]

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:

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.

[jmcilhinney]

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.