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.






Related Topics
  1. What is ADO.NET Dataset
  2. How to Dataset with Sql Server
  3. How to Dataset with OLEDB Data Source
  4. Search Tables in a Dataset Sql Server
  1. Search Tables in a Dataset OLEDB Data Source
  2. Dataset table row count in SQL Server
  3. Dataset table row count - OLEDB Data Source
  4. How to find column definition - Sql Server
  5. How to find column definition - OLEDB Data Source
  6. How to create DataSet without Databse
  7. How to multiple tables in Dataset - OLEDB Data Source
  8. How to add relations between tables in a Dataset
  9. How to merge tables in a Dataset - Sql Server
  10. How to merge tables in a Dataset - OLEDB Data Source
  11. How to populate a combo box from a dataset in VB.Net
  12. How many tables exist in a database - VB.NET

Related Links
  1. Insert image in a database in VB.NET
  2. Retrieve image from database in VB.NET