Merge two different DataTable columns and bind to a control

• Download source code - 23.1 KB

Introduction

In this article, let us see how to bind a combobox with different columns from different DataTables.

For example, I have a DataTable “Server” with columns Server and Database. Another DataTable “server1” with columns Servername and Databasename. Now I want to display all the values in the Server and Servername columns in a single combobox.

Using the code

1. Create a new Windows application. Add a combobox control.
2. I have two XML files which I am going to dump in DataSets.
XML file 1

<?xml version="1.0" standalone="yes" ?> 
- <NewDataSet>
- <Table1>
  <Server>Server1</Server> 
  <Database>Database1</Database> 
  </Table1>
- <Table1>
  <Server>Server2</Server> 
  <Database>Database2</Database> 
  </Table1>
- <Table1>
  <Server>Server3</Server> 
  <Database>Database3</Database> 
  </Table1>
</NewDataSet>

XML file 2

<?xml version="1.0" standalone="yes" ?> 
- <NewDataSet>
- <table2>
  <ServerName>Server4</ServerName> 
  <DatabaseName>Database4</DatabaseName> 
  </table2>
- <table2>
  <ServerName>Server5</ServerName> 
  <DatabaseName>Database5</DatabaseName> 
  </table2>
- <table2>
  <ServerName>Server6</ServerName> 
  <DatabaseName>Database6</DatabaseName> 
  </table2>
</NewDataSet>

3. Now in form_load, add the below code. I am going to merge two DataTables from two DataSets. Then I am going to create a new column in the merged DataSet and fill the new column with values from both DataTables. See the commented lines for more explanation and better understanding.

private void Form1_Load(object sender, EventArgs e)
{
    DataSet ds = new DataSet();
    string path = "C:\\XMLFile1.xml";
    //Read XMLFILE1.XML and save it in ds
    ds.ReadXml(path);
    path = "C:\\XMLFile2.xml";
    DataSet ds2 = new DataSet();
    //Read XMLFILE2.XML and save it in ds2
    ds2.ReadXml(path); 
    //Merge datatable from both datasets
    ds.Tables[0].Merge(ds2.Tables[0]);
    //add new column "All Servers"
    ds.Tables[0].Columns.Add("All Servers", typeof(string));
    //Read datatable and fetch all server and dump it into new column "All servers"
    foreach (DataRow dr in ds.Tables[0].Rows)
    {
        dr["All Servers"] = dr["server"].ToString();
    }
    //Read datatable and fetch all servername and dump it into new column "All servers"
    foreach (DataRow dr in ds.Tables[0].Rows)
    {
        //When we merged 2 datatables if there are 3 rows in datatable 1 and 2 in datatable2 
        //now it will become 5rows,So am checking if its blank, then fill
        //it with servername column from datatable2(initial)
        if (dr["All Servers"] == "")
            dr["All Servers"] = dr["servername"].ToString();
    }
    //Bind the datatable
    comboBox1.DataSource = ds.Tables[0];
    //set displaymember as "All servers", the newly created column.
    comboBox1.DisplayMember = "All Servers";
}

4. Now run the application, check the combobox, and you will have all the servers listed inside the combobox.

I have attached the complete source code also.