C# Dataset merge tbales - OLEDB

The DataSet in ADO.NET stores a copy of the data obtained through a SQL statement. To populate a DataTable within the DataSet, we can utilize the OleDbDataAdapter object. By using the Fill method of the OleDbDataAdapter, we can retrieve data from a data source and populate the DataTables within the DataSet. This allows for seamless integration of data from the data source into the DataTables within the DataSet.

The DataSet can be populated with multiple tables simultaneously using the OleDbDataAdapter object. This means that you can retrieve and populate more than one DataTable within the DataSet in a single operation.

DataTableCollection object

The DataTableCollection object represents a collection of DataTable objects within the DataSet. It serves as a container for zero or more DataTables, enabling efficient management and organization of the data.

In certain situations, you may encounter a need to combine the results of multiple SQL queries into a single result set. In such cases, the DataSet's Merge method can be employed. The Merge method allows you to combine the contents of multiple DataTables into a single DataTable within the DataSet. It is crucial to note that the tables involved in the merge operation should have identical structures to ensure compatibility and successful merging. The following C# source code shows how to merge the tables from two different dataset.

Full Source C#
using System; using System.Data; using System.Data.OleDb; using System.Windows.Forms; namespace WindowsApplication1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { string connetionString = null; OleDbConnection connection ; OleDbDataAdapter oledbAdapter ; DataSet ds1 = new DataSet(); DataSet ds2 = new DataSet(); DataTable dt ; string firstSql = null; string secondSql = null; int i = 0; 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; i <= dt.Rows.Count - 1; i++) { MessageBox.Show(dt.Rows[i].ItemArray[0] + " -- " + dt.Rows[i].ItemArray[0]); } } catch (Exception ex) { MessageBox.Show("Can not open connection ! "); } } } }

Conclusion

The DataTableCollection object represents a collection of DataTable objects within the DataSet. It serves as a container for zero or more DataTables, enabling efficient management and organization of the data.