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





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 multiple tables in Dataset - OLEDB Data Source
  9. How to add relations between tables in a Dataset
  10. How to merge tables in a Dataset - Sql Server
  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