none
Create DataView from two tables RRS feed

  • Question

  • Hi

    I have two datasets. Dataset1 has table1 and dataset2 has table2. Table2 contains a foreign key (ID column) which is the primary key in table1. What I want to know is how can I create a single dataview of the two tables joined by the ID column. This view will be bounded to a grid. Any help will be much appreciated

     

     

    Thanks

    Wednesday, March 26, 2008 2:52 AM

Answers

  • If DataTables are related and part of same DataSet then you could use next helper class to get joined DataTable

     

    http://support.microsoft.com/kb/325688/en-us

     

    You might also use LINQ to DataSet to achieve required result. Here are some samples

     

    http://msdn2.microsoft.com/en-us/vbasic/bb688086.aspx

     

    Thursday, March 27, 2008 12:18 PM
    Moderator
  • Using LINQ to DataSet is probably the best solution.  I have a sample below that shows how to do a simple example.  If you have any questions just post back to here.

     

    DataSet ds1 = new DataSet("ds1");

    DataSet ds2 = new DataSet("ds1");

    DataTable dt1 = new DataTable("Table1");

    DataTable dt2 = new DataTable("Table2");

     

    ds1.Tables.Add(dt1);

    ds2.Tables.Add(dt2);

    dt1.Columns.Add("id", Type.GetType("System.Int32"));

    dt1.Columns.Add("FirstName", Type.GetType("System.String"));

    dt1.Rows.Add(1, "Carl");

    dt1.Rows.Add(2, "John");

     

    dt2.Columns.Add("key", Type.GetType("System.Int32"));

    dt2.Columns.Add("id", Type.GetType("System.Int32"));

    dt2.Columns.Add("LastName", Type.GetType("System.String"));

    dt2.Rows.Add(1, 1, "Perry");

    dt2.Rows.Add(2, 2, "Piercy");

    dt2.Rows.Add(3, 4, "Johnson");

     

     

    IEnumerable<DataRow> dv = from table1 in ds1.Tables[0].AsEnumerable()

    from table2 in ds2.Tables[0].AsEnumerable()

    where table1.Field<int>("id") == table2.Field<int>("id")

    select table2;

    //select new { id = table1.Field<int>("id"), firstname = table1.Field<string>("FirstName"), lastname = table2.Field<string>("LastName")};

    dataGridView1.AutoGenerateColumns = true;

    dataGridView1.DataSource = DataTableExtensions.CopyToDataTable<DataRow>(dv);

     

     

     

    Thursday, April 3, 2008 4:32 AM

All replies

  • Hey, Amitesh

     

    I would suggest you have only one dataset with both tables in the dataset. Then create a DataTable, add the columns of both tables to the DataTable and fill the row in the DataTable with whatever values.

     

    Here's an example with Northwind:

     

    Code Snippet

    NorthwindDataSet.ProductsRow prodRow;

    NorthwindDataSet.Order_DetailsRow odRow = ds.Order_Details[0];

    prodRow = prod.FindByProductID(odRow.ProductID);

    DataTable tblTemp = new DataTable("Temp");

    foreach (DataColumn col in od.Columns)

    {

    DataColumn colTemp = new DataColumn(col.ColumnName);

    colTemp.AllowDBNull = col.AllowDBNull;

    colTemp.AutoIncrement = col.AutoIncrement;

    colTemp.AutoIncrementSeed = col.AutoIncrementSeed;

    colTemp.AutoIncrementStep = col.AutoIncrementStep;

    colTemp.MaxLength = col.MaxLength;

    colTemp.ReadOnly = col.ReadOnly;

    colTemp.Unique = col.Unique;

    tblTemp.Columns.Add(colTemp);

    }

    foreach (DataColumn col in prod.Columns)

    {

    if (col.ColumnName != "ProductID")

    {

    DataColumn colTemp = new DataColumn(col.ColumnName);

    colTemp.AllowDBNull = col.AllowDBNull;

    colTemp.AutoIncrement = col.AutoIncrement;

    colTemp.AutoIncrementSeed = col.AutoIncrementSeed;

    colTemp.AutoIncrementStep = col.AutoIncrementStep;

    colTemp.MaxLength = col.MaxLength;

    colTemp.ReadOnly = col.ReadOnly;

    colTemp.Unique = col.Unique;

    if (colTemp.ColumnName == "UnitPrice")

    colTemp.ColumnName = "UnitPriceWithDiscount";

    tblTemp.Columns.Add(colTemp);

    }

    }

    tblTemp.Rows.Add(new object[] {/* whatever values */}

    DataView vue = new DataView(tblTemp);

     

     

    Hope this makes sense,

     

    Stefan

    Wednesday, March 26, 2008 4:16 PM
  • You can use Merge method to merge two datatables and than create DataView from the merged table.
    Wednesday, March 26, 2008 9:22 PM
  • If DataTables are related and part of same DataSet then you could use next helper class to get joined DataTable

     

    http://support.microsoft.com/kb/325688/en-us

     

    You might also use LINQ to DataSet to achieve required result. Here are some samples

     

    http://msdn2.microsoft.com/en-us/vbasic/bb688086.aspx

     

    Thursday, March 27, 2008 12:18 PM
    Moderator
  • THe problem here is that the tables are not part of the same dataset. They are in different dataset

     

    Wednesday, April 2, 2008 10:20 PM
  • Using LINQ to DataSet is probably the best solution.  I have a sample below that shows how to do a simple example.  If you have any questions just post back to here.

     

    DataSet ds1 = new DataSet("ds1");

    DataSet ds2 = new DataSet("ds1");

    DataTable dt1 = new DataTable("Table1");

    DataTable dt2 = new DataTable("Table2");

     

    ds1.Tables.Add(dt1);

    ds2.Tables.Add(dt2);

    dt1.Columns.Add("id", Type.GetType("System.Int32"));

    dt1.Columns.Add("FirstName", Type.GetType("System.String"));

    dt1.Rows.Add(1, "Carl");

    dt1.Rows.Add(2, "John");

     

    dt2.Columns.Add("key", Type.GetType("System.Int32"));

    dt2.Columns.Add("id", Type.GetType("System.Int32"));

    dt2.Columns.Add("LastName", Type.GetType("System.String"));

    dt2.Rows.Add(1, 1, "Perry");

    dt2.Rows.Add(2, 2, "Piercy");

    dt2.Rows.Add(3, 4, "Johnson");

     

     

    IEnumerable<DataRow> dv = from table1 in ds1.Tables[0].AsEnumerable()

    from table2 in ds2.Tables[0].AsEnumerable()

    where table1.Field<int>("id") == table2.Field<int>("id")

    select table2;

    //select new { id = table1.Field<int>("id"), firstname = table1.Field<string>("FirstName"), lastname = table2.Field<string>("LastName")};

    dataGridView1.AutoGenerateColumns = true;

    dataGridView1.DataSource = DataTableExtensions.CopyToDataTable<DataRow>(dv);

     

     

     

    Thursday, April 3, 2008 4:32 AM
  • Thanks Carl. Your solution did the magic

     

    Thursday, April 3, 2008 9:00 PM