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

Discussion in 'ASP .Net' started by Bennett Haselton, Oct 5, 2004.

  1. 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
     
    Bennett Haselton, Oct 5, 2004
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:...
    > 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, Oct 5, 2004
    #2
    1. Advertising

  3. 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" <> wrote in message news:<#>...
    > 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" <> wrote in message
    > news:...
    > > 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
     
    Bennett Haselton, Oct 7, 2004
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    0
    Views:
    676
  2. Alan Silver
    Replies:
    0
    Views:
    892
    Alan Silver
    Jun 5, 2006
  3. rob merritt
    Replies:
    0
    Views:
    196
    rob merritt
    Mar 1, 2005
  4. novus
    Replies:
    9
    Views:
    249
    last component
    Dec 14, 2005
  5. Harry
    Replies:
    3
    Views:
    1,076
    Harry
    Oct 8, 2004
Loading...

Share This Page