How to merge tables in a Dataset - OLEDB Data Source

The DataSet serves as a container that holds a copy of the data retrieved through the SQL statement. By using the OleDbDataAdapter object, we can populate a DataTable within the DataSet. The Fill method in the OleDbDataAdapter allows us to retrieve data from the data source and populate the DataTable in the DataSet.

DataTableCollection

The DataTableCollection within the DataSet is responsible for holding zero or more DataTable objects. These DataTable objects represent the individual tables within the DataSet, each containing rows and columns of data.

Merge method

In certain situations, we may need to combine the results of multiple SQL query results into a single result set. To achieve this, we can utilize the Merge method provided by the DataSet. The Merge method allows us to merge the contents of multiple DataTables within the DataSet into a single DataTable. It is important to note that the tables involved in the merge operation should have identical structures, meaning that the columns should have the same names and data types. This ensures compatibility and consistency in combining the data.

By using the Merge method, we can consolidate data from multiple tables into a single result set, enabling us to perform further analysis or processing on the combined data.

The following VB.NET source code shows how to merge the tables from two different dataset.

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 ds1 As New DataSet Dim ds2 As New DataSet Dim dt As DataTable 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(ds1, "First Table") oledbAdapter.SelectCommand.CommandText = secondSql oledbAdapter.Fill(ds2, "Second Table") oledbAdapter.Dispose() connection.Close() ds1.Tables(0).Merge(ds2.Tables(0)) dt = ds1.Tables(0) For i = 0 To dt.Rows.Count - 1 MsgBox(dt.Rows(i).Item(0) & " -- " & dt.Rows(i).Item(1)) Next Catch ex As Exception MsgBox("Can not open connection ! ") End Try End Sub End Class