How to multiple tables in Dataset - Sql Server

The DataSet is a data structure that holds a collection of DataTable objects and their corresponding DataRelationCollection. Each DataTableCollection can contain zero or more DataTable objects, which represent individual tables of data. The SqlDataAdapter, an object provided by ADO.NET, enables us to populate these DataTables within a DataSet. To accomplish this, we can utilize the Fill method of the SqlDataAdapter, which retrieves data from a data source and populates the specified DataTables in the DataSet.

Store data from multiple tables simultaneously

In situations where we need to retrieve and store data from multiple tables simultaneously, we can use the capabilities of the SqlDataAdapter. By providing a single SqlDataAdapter object and specifying multiple SQL statements or commands, we can populate the Dataset with multiple tables in a single operation. This approach saves us from making separate database calls for each table and improves efficiency.

To illustrate this, consider the following VB.NET code snippet:

Full Source VB.NET
Imports System.Data.SqlClient Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim connetionString As String Dim connection As SqlConnection Dim command As SqlCommand Dim adapter As New SqlDataAdapter Dim ds As New DataSet Dim i As Integer Dim firstSql As String Dim secondSql As String connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password" firstSql = "Your First SQL Statement Here" secondSql = "Your Second SQL Statement Here" connection = New SqlConnection(connetionString) Try connection.Open() command = New SqlCommand(firstSql, connection) adapter.SelectCommand = command adapter.Fill(ds, "First Table") adapter.SelectCommand.CommandText = secondSql adapter.Fill(ds, "Second Table") adapter.Dispose() command.Dispose() connection.Close() 'retrieve first table data For i = 0 To ds.Tables(0).Rows.Count - 1 MsgBox(ds.Tables(0).Rows(i).Item(0) & " -- " & ds.Tables(0).Rows(i).Item(1)) Next 'retrieve second table data For i = 0 To ds.Tables(1).Rows.Count - 1 MsgBox(ds.Tables(1).Rows(i).Item(0) & " -- " & ds.Tables(1).Rows(i).Item(1)) Next Catch ex As Exception MsgBox("Can not open connection ! ") End Try End Sub End Class

Conclusion

The SqlDataAdapter provides a powerful mechanism for populating DataTables in a DataSet, allowing us to retrieve and store data from multiple tables in a single operation.