Filling a DataTable using a DataReader-VBForums
Results 1 to 21 of 21

Thread: Filling a DataTable using a DataReader

  1. #1

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,655

    [.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:
    1. Public Function GetFilledTable(ByVal query As String, ByVal connection As OleDbConnection) As DataTable
    2.         Dim command As New OleDbCommand(query, connection)
    3.  
    4.         connection.Open()
    5.  
    6.         Dim reader As OleDbDataReader = command.ExecuteReader(CommandBehavior.KeyInfo Or CommandBehavior.CloseConnection)
    7.         Dim schema As DataTable = reader.GetSchemaTable()
    8.         Dim columns(schema.Rows.Count - 1) As DataColumn
    9.         Dim column As DataColumn
    10.  
    11.         'Build the schema for the table that will contain the data.
    12.         For i As Integer = 0 To columns.GetUpperBound(0) Step 1
    13.             column = New DataColumn
    14.             column.AllowDBNull = CBool(schema.Rows(i)("AllowDBNull"))
    15.             column.AutoIncrement = CBool(schema.Rows(i)("IsAutoIncrement"))
    16.             column.ColumnName = CStr(schema.Rows(i)("ColumnName"))
    17.             column.DataType = CType(schema.Rows(i)("DataType"), Type)
    18.  
    19.             If column.DataType Is GetType(String) Then
    20.                 column.MaxLength = CInt(schema.Rows(i)("ColumnSize"))
    21.             End If
    22.  
    23.             column.ReadOnly = CBool(schema.Rows(i)("IsReadOnly"))
    24.             column.Unique = CBool(schema.Rows(i)("IsUnique"))
    25.             columns(i) = column
    26.         Next i
    27.  
    28.         Dim data As New DataTable
    29.         Dim row As DataRow
    30.  
    31.         data.Columns.AddRange(columns)
    32.  
    33.         'Get the data itself.
    34.         While reader.Read()
    35.             row = data.NewRow()
    36.  
    37.             For i As Integer = 0 To columns.GetUpperBound(0)
    38.                 row(i) = reader(i)
    39.             Next i
    40.  
    41.             data.Rows.Add(row)
    42.         End While
    43.  
    44.         reader.Close()
    45.  
    46.         Return data
    47.     End Function
    Last edited by jmcilhinney; Oct 28th, 2008 at 06:01 PM.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  2. #2
    Junior Member
    Join Date
    Feb 2006
    Posts
    29

    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?

  3. #3

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,655

    Re: Filling a DataTable using a DataReader

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

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  4. #4
    Fanatic Member aNubies's Avatar
    Join Date
    Aug 2008
    Posts
    521

    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.

  5. #5

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,655

    Re: Filling a DataTable using a DataReader

    Quote Originally Posted by aNubies View Post
    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.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  6. #6
    Fanatic Member aNubies's Avatar
    Join Date
    Aug 2008
    Posts
    521

    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.

  7. #7

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,655

    Re: Filling a DataTable using a DataReader

    Quote Originally Posted by aNubies View Post
    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.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  8. #8
    Fanatic Member aNubies's Avatar
    Join Date
    Aug 2008
    Posts
    521

    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 ?

  9. #9

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,655

    Re: Filling a DataTable using a DataReader

    Quote Originally Posted by aNubies View Post
    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.
    Quote Originally Posted by aNubies View Post
    Im just curious about your schema-schema what is that ?
    As I declared it.
    Code:
    Dim schema As DataTable = reader.GetSchemaTable()

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  10. #10
    Frenzied Member
    Join Date
    Jul 2006
    Location
    MI
    Posts
    1,599

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

  11. #11
    PowerPoster
    Join Date
    May 2002
    Posts
    25,520

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  12. #12
    Frenzied Member
    Join Date
    Jul 2006
    Location
    MI
    Posts
    1,599

    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?

  13. #13
    PowerPoster
    Join Date
    May 2002
    Posts
    25,520

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  14. #14
    Frenzied Member
    Join Date
    Jul 2006
    Location
    MI
    Posts
    1,599

    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?

  15. #15
    PowerPoster
    Join Date
    May 2002
    Posts
    25,520

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  16. #16
    Frenzied Member
    Join Date
    Jul 2006
    Location
    MI
    Posts
    1,599

    Re: Filling a DataTable using a DataReader

    Quote Originally Posted by techgnome View Post

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



    Quote Originally Posted by techgnome View Post

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

  17. #17

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,655

    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.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  18. #18
    Frenzied Member
    Join Date
    Jul 2006
    Location
    MI
    Posts
    1,599

    Re: Filling a DataTable using a DataReader

    Quote Originally Posted by jmcilhinney View Post

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

  19. #19

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,655

    Re: Filling a DataTable using a DataReader

    Quote Originally Posted by nbrege View Post
    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.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  20. #20
    Frenzied Member
    Join Date
    Jul 2006
    Location
    MI
    Posts
    1,599

    Re: Filling a DataTable using a DataReader

    Quote Originally Posted by jmcilhinney View Post
    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.

  21. #21

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,655

    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.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.