how to bind a DataGrid to a join of two tables in a DataSet?

[Bennett Haselton]

I know how to create a DataAdapter that loads data from a data source
into a table in a typed DataSet, and how to set the DataSource and
DataMember properties of a DataGrid so that at run time it will load
the data from that table in the DataSet.

Assuming I've got two tables in the DataSet, how can I set the
DataSource property of the DataGrid to be an inner join of the two
tables in the DataSet?

If I want the DataGrid to get data from just one table, I set the
DataSource property to be an instance of the typed DataSet, and the
DataMember property to be the table where I want to get the data. But
if I want to do an inner join, it's not intuitive to me what to do
instead, and none of the examples in my ASP books show how to do it.

I could create the *DataAdapter* so that it does an inner join to load
the data into the DataSet in the first place. But that's inefficient
if I'm already loading the contents of Table1 and Table2 into the
DataSet for use by some other controls on the page, then if I load a
join of Table1 and Table2 into the DataSet as well, then I'd probably
be loading the entire contents of at least one of the tables twice.
Doing a join on data already in memory seems more efficient.

-Bennett

 

[Martin Dechev]

Hi,

You can add the relationship between the two tables and then get the
parent/child rows' values when databinding. The following example uses the
Northwind database, maybe it will be of help to you:

void BindGrid()
{
System.Data.DataSet ds =
new System.Data.DataSet();
System.Data.SqlClient.SqlConnection conn =
new System.Data.SqlClient.SqlConnection(
@"server=(local);database=Northwind;user id=user;password=pass");
System.Data.SqlClient.SqlDataAdapter da =
new System.Data.SqlClient.SqlDataAdapter(
@"SELECT CustomerID, CompanyName FROM Customers",
conn);
conn.Open();
da.Fill(ds, "Customers");
da = new System.Data.SqlClient.SqlDataAdapter(
@"SELECT OrderID, OrderDate, CustomerID, ShipAddress FROM Orders",
conn);
da.Fill(ds, "Orders");
ds.Relations.Add("CustID",
ds.Tables["Customers"].Columns["CustomerID"],
ds.Tables["Orders"].Columns["CustomerID"]);
DataGrid1.DataSource = ds.Tables["Orders"];
DataGrid1.DataBind();
}

<asp:datagrid id="DataGrid1" runat="server" autogeneratecolumns="False">
<columns>
<asp:templatecolumn headertext="Order ID"><itemtemplate><%#
DataBinder.Eval(Container.DataItem, "OrderID")
%></itemtemplate></asp:templatecolumn>
<asp:templatecolumn headertext="Order Date"><itemtemplate><%#
DataBinder.Eval(Container.DataItem, "OrderDate", "{0:dd MMM yyyy}")
%></itemtemplate></asp:templatecolumn>
<asp:templatecolumn headertext="Customer ID"><itemtemplate><%#
DataBinder.Eval(Container.DataItem, "CustomerID")
%></itemtemplate></asp:templatecolumn>
<asp:templatecolumn headertext="Company Name"><itemtemplate><%#
(Container.DataItem as
System.Data.DataRowView).Row.GetParentRow("CustID")["CompanyName"]
%></itemtemplate></asp:templatecolumn>
</columns>
</asp:datagrid>

Hope this helps
Martin

 

[Bennett Haselton]

Thanks, that was very generous of you to post a complete example with
working code -- I modified the example for my project and it worked as
well!

I was hoping to find a smooth way to do it in the designer, just since
that reduces the chances of coding errors, but sometimes you have to
*GROAN* type.

-Bennett

Hi,

You can add the relationship between the two tables and then get the
parent/child rows' values when databinding. The following example uses the
Northwind database, maybe it will be of help to you:

void BindGrid()
{
System.Data.DataSet ds =
new System.Data.DataSet();
System.Data.SqlClient.SqlConnection conn =
new System.Data.SqlClient.SqlConnection(
@"server=(local);database=Northwind;user id=user;password=pass");
System.Data.SqlClient.SqlDataAdapter da =
new System.Data.SqlClient.SqlDataAdapter(
@"SELECT CustomerID, CompanyName FROM Customers",
conn);
conn.Open();
da.Fill(ds, "Customers");
da = new System.Data.SqlClient.SqlDataAdapter(
@"SELECT OrderID, OrderDate, CustomerID, ShipAddress FROM Orders",
conn);
da.Fill(ds, "Orders");
ds.Relations.Add("CustID",
ds.Tables["Customers"].Columns["CustomerID"],
ds.Tables["Orders"].Columns["CustomerID"]);
DataGrid1.DataSource = ds.Tables["Orders"];
DataGrid1.DataBind();
}

<asp:datagrid id="DataGrid1" runat="server" autogeneratecolumns="False">
<columns>
<asp:templatecolumn headertext="Order ID"><itemtemplate><%#
DataBinder.Eval(Container.DataItem, "OrderID")
%></itemtemplate></asp:templatecolumn>
<asp:templatecolumn headertext="Order Date"><itemtemplate><%#
DataBinder.Eval(Container.DataItem, "OrderDate", "{0:dd MMM yyyy}")
%></itemtemplate></asp:templatecolumn>
<asp:templatecolumn headertext="Customer ID"><itemtemplate><%#
DataBinder.Eval(Container.DataItem, "CustomerID")
%></itemtemplate></asp:templatecolumn>
<asp:templatecolumn headertext="Company Name"><itemtemplate><%#
(Container.DataItem as
System.Data.DataRowView).Row.GetParentRow("CustID")["CompanyName"]
%></itemtemplate></asp:templatecolumn>
</columns>
</asp:datagrid>

Hope this helps
Martin

I know how to create a DataAdapter that loads data from a data source
into a table in a typed DataSet, and how to set the DataSource and
DataMember properties of a DataGrid so that at run time it will load
the data from that table in the DataSet.

Assuming I've got two tables in the DataSet, how can I set the
DataSource property of the DataGrid to be an inner join of the two
tables in the DataSet?

If I want the DataGrid to get data from just one table, I set the
DataSource property to be an instance of the typed DataSet, and the
DataMember property to be the table where I want to get the data. But
if I want to do an inner join, it's not intuitive to me what to do
instead, and none of the examples in my ASP books show how to do it.

I could create the *DataAdapter* so that it does an inner join to load
the data into the DataSet in the first place. But that's inefficient
if I'm already loading the contents of Table1 and Table2 into the
DataSet for use by some other controls on the page, then if I load a
join of Table1 and Table2 into the DataSet as well, then I'd probably
be loading the entire contents of at least one of the tables twice.
Doing a join on data already in memory seems more efficient.

-Bennett