C# Dataset merge tables - Sql Server

The DataSet in ADO.NET contains a copy of the data retrieved through a SQL statement. To populate a DataTable within the DataSet, we can use the SqlDataAdapter object. The Fill method of the SqlDataAdapter enables us to 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 more than one table at a time using the SqlDataAdapter object. This means that multiple DataTables can be retrieved and populated within the DataSet in a single operation.

Combine the results of multiple SQL queries

In certain situations, there may be 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 utilized. The Merge method allows you to combine the contents of multiple DataTables into a single DataTable within the DataSet. It is important to note that the tables involved in the merge operation should have identical structures, meaning their columns should have similar data types. 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.SqlClient; 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; SqlConnection connection ; SqlCommand command ; SqlDataAdapter adapter = new SqlDataAdapter(); DataSet ds = new DataSet(); DataTable dt ; string firstSql = null; string secondSql = null; int i = 0; connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"; firstSql = "Your First SQL Statement Here"; secondSql = "Your Second SQL Statement Here"; connection = new SqlConnection(connetionString); try { connection.Open(); command = new SqlCommand(firstSql, connection); adapter.SelectCommand = command; adapter.Fill(ds, "Table(0)"); adapter.SelectCommand.CommandText = secondSql; adapter.Fill(ds, "Table(1)"); adapter.Dispose(); command.Dispose(); connection.Close(); ds.Tables[0].Merge(ds.Tables[1]); dt = ds.Tables[0]; for (i = 0; i <= dt.Rows.Count - 1; i++) { MessageBox.Show(dt.Rows[i].ItemArray[0] + " -- " + dt.Rows[i].ItemArray[1]); } } catch (Exception ex) { MessageBox.Show("Can not open connection ! "); } } } }

Conclusion

Using the functionalities of the DataSet, SqlDataAdapter, DataRelation, and Merge method, you can effectively work with multiple tables, establish relationships between them, and combine query results into a single result set when needed.