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.