Join Tables in .NET DataSet to Display in DataGridView

Question

I have two tables in a DataSet in .NET. I want to join them on an ID column. Then I want a DataGridView to display two columns from the first table and one column from the second table.

If it makes it easier, the relation between the two tables is one-to-one.

Can it be done?

Answer 1

Well, is it read only? In .NET 3.5 / C# 3.0 you could probably use a LINQ join and an anonymous output type pretty easily:

        DataTable left = new DataTable
        {
            Columns = { {"PK", typeof(int)}, {"Name", typeof(string)}},
            Rows = {{1,"abc"},{2,"def"}}
        }, right = new DataTable
        {
            Columns = { { "FK", typeof(int) }, { "Value", typeof(decimal) } },
            Rows = { { 1, 123.45M }, { 2, 678.9M } }
        };
        var qry = from x in left.Rows.Cast<DataRow>()
                  join y in right.Rows.Cast<DataRow>()
                  on x.Field<int>("PK") equals y.Field<int>("FK")
                  select new
                  {
                      Name = x.Field<string>("Name"),
                      Value = y.Field<decimal>("Value")
                  };
        var data = qry.ToList();

You can then bind to "Name" and "Value" of data. Note it is easier with typed data-sets, since you can lose the Cast<> and Field<> rubbish.

Answer 2

Why not to use the designer? You place both tables in the dataset, you establish the relationship, and change the Fill/Get methods, to something like this (with the "inner join"):

SELECT Productos.idProducto, Productos.Nombre, Precios.Precio, Tiendas.idTienda, Zonas.Zona,Productos.idZona FROM
Productos INNER JOIN Precios ON Productos.idProducto = Precios.idProducto