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.






Related Topics
  1. What is C# ADO.NET Dataset
  2. C# Datset with Sql Server Data Provider
  3. C# Datset with OLEDB Data Provider
  4. Find Tables in a Dataset - Sql Server
  1. Find Tables in a Dataset - OLEDB
  2. How to Dataset rows count - Sql Server
  3. How to Dataset rows count - OLEDB
  4. How to find Column Definition SqlServer
  5. How to find Column Definition OLEDB
  6. How to Dyanamic Dataset in C#
  7. C# Dataset with multiple tables - Sql Server
  8. C# Dataset with multiple tables - OLEDB
  9. C# Dataset table relations
  10. C# Dataset merge tables - OLEDB
  11. Bind a dataset to a combo box in C#, bind enum to combobox , bind dictionary to combobox
  12. How to find tables in a Database in C#

Related Links
  1. How to insert image in a database
  2. How to retrieve image from database