Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > how to bind a DataGrid to a join of two tables in a DataSet?

Reply
Thread Tools

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

 
 
Bennett Haselton
Guest
Posts: n/a
 
      10-05-2004
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
 
Reply With Quote
 
 
 
 
Martin Dechev
Guest
Posts: n/a
 
      10-05-2004
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) om...
> 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



 
Reply With Quote
 
 
 
 
Bennett Haselton
Guest
Posts: n/a
 
      10-07-2004
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

"Martin Dechev" <(E-Mail Removed)> wrote in message news:<#(E-Mail Removed)>...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed) om...
> > 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

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
You must join New York Paid To Read !!!!!!! NewYork PTR is online!!! JOIN NOW!!!!!! Alan Silver ASP .Net 0 06-05-2006 03:27 PM
list.join()... re.join()...? Do they exist? (newbie questions...) googleboy Python 1 10-01-2005 12:56 PM
trying to update a table after making a join select query on two tables rob merritt ASP .Net Datagrid Control 0 03-01-2005 10:33 PM
query to join two tables in 2 different Oracle schema Harry Perl Misc 3 10-08-2004 07:13 PM
Cant Bind a data from a multi-table Inner Join using C# Gareth Stretch ASP .Net 0 06-26-2003 12:59 PM



Advertisments