DatabaseReturning Multiple Tables into a DataSet

Returning Multiple Tables into a DataSet content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

One of the big new features of the DataSet over the old Recordset is its ability to hold multiple tables of data at a time. But, how many developers take advantage of this feature? How many still use multiple DataSet objects to hold multiple tables?

Don’t answer that one.

If you find yourself victim, you might want to add the following code snippet to your basket. It accepts a connection string and string array of table names. It returns a DataSet containing all the tables requested (not, of course, accounting for errors)—ready for you to add relationships, apply rules, schemas, and all the other whizzy techniques you may wish to embrace.

Here’s the code you’ll need:

Public Function GetDataSet( _
      ByVal ConnectionString As String, _
      ByRef Tables() As String) _
          As System.Data.DataSet

  ' Create connection, command 
  ' object and empty DataSet
  Dim objConn As _ 
     New System.Data.SqlClient.SqlConnection( _
  Dim objCmd As New _
  objCmd.Connection = objConn
  objCmd.CommandType = System.Data.CommandType.Text
  Dim objDS As New System.Data.DataSet()

    ' Create new DataAdapter
    Dim objDA As New _
    objDA.SelectCommand = objCmd
    ' Open connection
    ' Populate the DataSet with specified tables
    Dim intCount As Integer
    For intCount = 0 To Tables.GetUpperBound(0)
        objCmd.CommandText = _
            "SELECT * FROM " & Tables(intCount)
        objDA.Fill(objDS, Tables(intCount))

  Catch e As Exception
    ' Calling code must check for thrown errors
    Throw e
    ' Clean up
  End Try
  Return objDS
End Function

And here’s how you might call this function:

Dim MyDataSet As New DataSet()
Dim Tables(2) As String
Tables(0) = "authors" : Tables(1) = "sales" : Tables(2) = "titles"
MyDataSet = GetDataSet("data source=localhost; _
                        initial catalog=pubs;" & _
  "persist security info=False;user id=sa;pwd= ", Tables)

About the Author

Karl Moore (MCSD, MVP) is an experience author living in Yorkshire, England. He is author of numerous technology books, including the new Ultimate VB .NET and ASP.NET Code Book, plus regularly features at industry conferences and on BBC radio. Moore also runs his own creative consultancy, White Cliff Computing Ltd. Visit his official Web site at

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories