ASP.NET - how to merge two tables in a single dataset

Asked By aman on 06-Oct-11 01:11 AM
hi all,

i want to merge two tables in a single data set........how can i achieve this in asp.net

can anyone please give me some example so that i can make use of it...

thanks and regards
Aman Khan
aneesa replied to aman on 06-Oct-11 01:39 AM
If you are using sql queries, then please use JOIN keyword to join the tables and return the result
aneesa replied to aman on 06-Oct-11 01:42 AM
The BindGrid method.
 
Sub BindGrid()
   Dim myConnection as New SqlConnection (strConn)
 
   Dim DS1 As DataSet
   Dim DS1 As DataSet
   Dim MyCommand As SqlDataAdapter
 
   MyCommand = new SqlDataAdapter("exec s_get_table1", MyConnection)
   DS1 = new DataSet()
   MyCommand.Fill(DS1, "MyTable")
 
   MyCommand = new SqlDataAdapter("exec s_get_table2", MyConnection)
   DS2 = new DataSet()
   MyCommand.Fill(DS2, "MyTable")
 
'Now this code works because the table name for both datasets are the same. 
'Also the data type and column name for both tables are the same.
 
   ds1.merge(ds2)
 
   MyDataGrid.DataSource=DS1.tables(0).DefaultView
   MyDataGrid.DataBind()
End Sub


What, if the schema of two tables are not the same?

In this example we saw that the table structure of both tables (table1 and table2) are the same. If the datatype of the columns in table1 and table2 are not the same, then what will happen? It is obvious that the merge will not take place. Even the compiler will give an error, such as:

<target>.destination and <source>.destination have conflicting properties: DataType property mismatch.

aneesa replied to aman on 06-Oct-11 01:43 AM
[C#]
private void DemonstrateMergeTable(){
   // Create a DataSet with one table, two columns, and ten rows.
   DataSet ds = new DataSet("myDataSet");
   DataTable t = new DataTable("Items");
 
   // Add table to the DataSet
   ds.Tables.Add(t);
 
   // Add columns
   DataColumn c1 = new DataColumn("id", Type.GetType("System.Int32"),"");
   DataColumn c2 = new DataColumn("Item", Type.GetType("System.Int32"),"");
   t.Columns.Add(c1);
   t.Columns.Add(c2);
 
   // DataColumn array to set primary key.
   DataColumn[] keyCol= new DataColumn[1];
 
   // Set primary key column.
   keyCol[0]= c1;
   t.PrimaryKey=keyCol;
 
   // Add a RowChanged event handler for the table.
   t.RowChanged += new DataRowChangeEventHandler(Row_Changed);
 
   // Add ten rows.
   for(int i = 0; i <10;i++){
    DataRow r=t.NewRow();
    r["id"] = i;
    r["Item"]= i;
    t.Rows.Add(r);
   }
   // Accept changes.
   ds.AcceptChanges();
 
   PrintValues(ds, "Original values");
 
   // Create a second DataTable identical to the first.
   DataTable t2 = t.Clone();
 
   // Add three rows. Note that the id column can't be the
   // same as existing rows in the DataSet table.
   DataRow newRow;
   newRow = t2.NewRow();
   newRow["id"] = 14;
   newRow["item"] = 774;
 
   //Note the alternative method for adding rows.
   t2.Rows.Add(new Object[] { 12, 555 });
   t2.Rows.Add(new Object[] { 13, 665 });
 
   // Merge the table into the DataSet
   Console.WriteLine("Merging");
   ds.Merge(t2);
   PrintValues(ds, "Merged With table.");
 
}
 
private void Row_Changed(object sender, DataRowChangeEventArgs e){
   Console.WriteLine("Row Changed " + e.Action.ToString() + "\t" + e.Row.ItemArray[0]);
}
 
private void PrintValues(DataSet ds, string label){
   Console.WriteLine("\n" + label);
   foreach(DataTable t in ds.Tables){
    Console.WriteLine("TableName: " + t.TableName);
    foreach(DataRow r in t.Rows){
     foreach(DataColumn c in t.Columns){
      Console.Write("\t " + r[c] );
     }
     Console.WriteLine();
    }
   }
}
dipa ahuja replied to aman on 06-Oct-11 02:25 AM
 private void Button1_Click(System.Object sender, System.EventArgs e)
  {
    string connetionString = null;
    SqlConnection connection = default(SqlConnection);
    SqlCommand command = default(SqlCommand);
    SqlDataAdapter adapter = new SqlDataAdapter();
    DataSet ds = new DataSet();
    DataTable dt = default(DataTable);
    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];
 
    }
    catch (Exception ex)
    { }
  }
http://vb.net-informations.com/dataset/dataset-merge-tables-sqlserver.htm
Anoop S replied to aman on 06-Oct-11 03:41 PM
You can merge like this way

dstDataSetB.Merge(dstDataSetA)

The rules for merging datasets are :
* All the columns must be present in both datasets
* The data types of all the columns in the datasets must be same
* The column name should match.
* The table also should be the same for both datasets.I mean data table name in the dataset.