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.






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 - Sql Server
  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