How to multiple tables in Dataset - OLEDB Data Source

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 OleDbDataAdapter, an object provided by ADO.NET, enables us to populate these DataTables within a DataSet. We can utilize the Fill method of the OleDbDataAdapter to retrieve data from a data source and populate the specified DataTables in the DataSet.

OleDbDataAdapter

In situations where we need to retrieve and store data from multiple tables simultaneously, we can take advantage of the capabilities of the OleDbDataAdapter. By providing a single OleDbDataAdapter object and specifying multiple SQL statements or commands, we can populate the DataSet with multiple tables in a single operation. This approach eliminates the need for separate database calls for each table, enhancing efficiency and performance.

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

Full Source VB.NET
Imports System.Data.OleDb 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 OleDbConnection Dim oledbAdapter As OleDbDataAdapter Dim ds As New DataSet Dim firstSql As String Dim secondSql As String Dim i As Integer connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Your mdb filename;" firstSql = "Your First SQL Statement Here" secondSql = "Your Second SQL Statement Here" connection = New OleDbConnection(connetionString) Try connection.Open() oledbAdapter = New OleDbDataAdapter(firstSql, connection) oledbAdapter.Fill(ds, "First Table") oledbAdapter.SelectCommand.CommandText = secondSql oledbAdapter.Fill(ds, "Second Table") oledbAdapter.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
connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Your mdb filename;"

Conclusion

OleDbDataAdapter provides a powerful mechanism for populating DataTables in a DataSet, enabling us to retrieve and store data from multiple tables in a single operation. The Fill method of the OleDbDataAdapter is utilized to achieve this, allowing us to efficiently populate a Dataset with multiple tables.