C# Dataset merge tbales - 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.
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 ! ");
}
}
}
} |
|