C# Dataset merge tables - Sql Server

The DataSet Object contains copy of the data we requested through the SQL statement. The SqlDataAdapter object allows us to populate DataTable in a DataSet. We can use Fill method in the SqlDataAdapter for populating data in a Dataset. We can populate Dataset with more than one table at a time using SqlDataAdapter Object . The DataTableCollection contains zero or more DataTable objects.

In a DataSet with multiple DataTable objects, you can use DataRelation Objects to relate one table to another, to navigate through the tables, and to return child or parent rows from a related table. In some situation we want to combine the result of multiple SQL query as a single result set. In that case we can use the Dataset's Merge method for doing this. The tables involved in the merge should be identical, that is the columns are 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 ! "); } } } }







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
  5. Find Tables in a Dataset - OLEDB
  6. How to Dataset rows count - Sql Server
  7. How to Dataset rows count - OLEDB
  8. How to find Column Definition SqlServer
  9. How to find Column Definition OLEDB
  10. How to Dyanamic Dataset in C#
  11. C# Dataset with multiple tables - Sql Server
  12. C# Dataset with multiple tables - OLEDB
  13. C# Dataset table relations
  14. C# Dataset merge tables - OLEDB
  15. Bind a dataset to a combo box in C#, bind enum to combobox , bind dictionary to combobox
  16. How to find tables in a Database in C#


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