Returning Multiple Tables into a DataSet

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( _
         ConnectionString)
  Dim objCmd As New _
     System.Data.SqlClient.SqlCommand()
  objCmd.Connection = objConn
  objCmd.CommandType = System.Data.CommandType.Text
  Dim objDS As New System.Data.DataSet()

  Try
    ' Create new DataAdapter
    Dim objDA As New _
        System.Data.SqlClient.SqlDataAdapter(objCmd)
    objDA.SelectCommand = objCmd
    ' Open connection
    objConn.Open()
    ' 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))
    Next

  Catch e As Exception
    ' Calling code must check for thrown errors
    Throw e
  Finally
    ' Clean up
    objConn.Close()
  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 www.karlmoore.com.