ASP.NET Tutorial/ADO.net Database/DataRelation
Using Data Relations
<source lang="csharp">
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="Default_aspx" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server">
<title>Untitled Page</title>
</head> <body>
<form id="form1" runat="server">
<asp:GridView ID="GridView1"
runat="server"
CellPadding="4"
ForeColor="#333333"
GridLines="None"
DataKeyNames="OrderID"
AutoGenerateColumns="False"
PagerSettings-Mode="Numeric"
AllowPaging="true"
PageSize="5"
OnSelectedIndexChanged="OnSelectedIndexChangedHandler" >
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#999999" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
<Columns>
<asp:ButtonField ButtonType="Button" CommandName="Select" Text="Details" />
<asp:BoundField DataField="OrderID" HeaderText="Order ID" />
<asp:BoundField DataField="OrderDate" HeaderText="Order Date" />
<asp:BoundField DataField="CompanyName" HeaderText="Company" />
<asp:BoundField DataField="ContactTitle" HeaderText="Contact" />
<asp:BoundField DataField="Phone" HeaderText="Phone" />
</Columns>
</asp:GridView>
<asp:Panel id="OrderDetailsPanel" runat=server Height=50px Width=125px >
<asp:GridView ID="DetailsGridView" runat="server"
AutoGenerateColumns="False"
BackColor="LightGoldenrodYellow"
BorderColor="Tan"
BorderWidth="1px"
CellPadding="2"
ForeColor="Black"
GridLines="None">
<FooterStyle BackColor="Tan" />
<Columns>
<asp:BoundField DataField="OrderDate" HeaderText="Order Date" />
<asp:BoundField DataField="ProductName" HeaderText="Product" />
<asp:BoundField DataField="UnitPrice" HeaderText="Price" />
<asp:BoundField DataField="Quantity" HeaderText="Quantity" />
</Columns>
<PagerStyle BackColor="PaleGoldenrod"
ForeColor="DarkSlateBlue" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
<HeaderStyle BackColor="Tan" Font-Bold="True" />
<AlternatingRowStyle BackColor="PaleGoldenrod" />
</asp:GridView>
</asp:Panel>
<asp:GridView ID="OrderRelationsGridView" runat="server"
BackColor="White"
BorderColor="#CC9966"
BorderStyle="None"
BorderWidth="1px"
CellPadding="4">
<FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
<RowStyle BackColor="White" ForeColor="#330099" />
<PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
</asp:GridView>
</form>
</body> </html> File: Default.aspx.cs using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Text; using System.Data.SqlClient; public partial class Default_aspx : System.Web.UI.Page {
protected void Page_Load(object sender, EventArgs e)
{
DataSet ds = CreateDataSet();
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
DataView detailsView = new DataView(ds.Tables[1]);
DetailsGridView.DataSource = detailsView;
Session["DetailsView"] = detailsView;
DetailsGridView.DataBind();
OrderRelationsGridView.DataSource = ds.Relations;
OrderRelationsGridView.DataBind();
}
private DataSet CreateDataSet()
{
string connectionString = "Data Source=LocalHost;Initial Catalog=Northwind;Persist Security Info=True;User ID=sa;Password=sa_0001";
System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString);
connection.Open();
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
command.Connection = connection;
StringBuilder s = new StringBuilder("select OrderID, c.rupanyName, c.ContactName, ");
s.Append(" c.ContactTitle, c.Phone, orderDate");
s.Append(" from orders o ");
s.Append("join customers c on c.CustomerID = o.CustomerID");
command.rumandText = s.ToString();
SqlDataAdapter dataAdapter = new SqlDataAdapter();
dataAdapter.SelectCommand = command;
dataAdapter.TableMappings.Add("Table", "Orders");
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
System.Data.SqlClient.SqlCommand command2 =
new System.Data.SqlClient.SqlCommand();
command2.Connection = connection;
StringBuilder s2 = new StringBuilder("Select od.OrderID, OrderDate, p.ProductID, ");
s2.Append(" ProductName, od.UnitPrice, Quantity ");
s2.Append("from Orders o ");
s2.Append("join [Order Details] od on o.orderid = od.orderid ");
s2.Append("join products p on p.productID = od.productid ");
command2.rumandText = s2.ToString();
SqlDataAdapter dataAdapter2 = new SqlDataAdapter();
dataAdapter2.SelectCommand = command2;
dataAdapter2.TableMappings.Add("Table", "Order Details");
dataAdapter2.Fill(dataSet);
System.Data.SqlClient.SqlCommand command3 =
new System.Data.SqlClient.SqlCommand();
command3.Connection = connection;
string strCommand3 = "Select ProductID, ProductName from Products";
command3.rumandText = strCommand3;
SqlDataAdapter dataAdapter3 = new SqlDataAdapter();
dataAdapter3.SelectCommand = command3;
dataAdapter3.TableMappings.Add("Table", "Products");
dataAdapter3.Fill(dataSet);
System.Data.DataRelation dataRelation;
System.Data.DataColumn dataColumn1;
System.Data.DataColumn dataColumn2;
dataColumn1 = dataSet.Tables["Orders"].Columns["OrderID"];
dataColumn2 = dataSet.Tables["Order Details"].Columns["OrderID"];
dataRelation =
new System.Data.DataRelation(
"OrdersToDetails",
dataColumn1,
dataColumn2);
dataSet.Relations.Add(dataRelation);
dataColumn1 = dataSet.Tables["Products"].Columns["ProductID"];
dataColumn2 = dataSet.Tables["Order Details"].Columns["ProductID"];
dataRelation =
new System.Data.DataRelation(
"ProductIDToName",
dataColumn1,
dataColumn2);
dataSet.Relations.Add(dataRelation);
return dataSet;
}
public void OnSelectedIndexChangedHandler(Object sender, EventArgs e)
{
UpdateDetailsGrid();
}
private void UpdateDetailsGrid()
{
int index = GridView1.SelectedIndex;
if (index != -1)
{
DataKey key = GridView1.DataKeys[index];
int orderID = (int)key.Value;
DataView detailsView = (DataView)Session["detailsView"];
detailsView.RowFilter = "OrderID = " + orderID;
DetailsGridView.DataSource = detailsView;
DetailsGridView.DataBind();
OrderDetailsPanel.Visible = true;
}
else
{
OrderDetailsPanel.Visible = false;
}
}
}</source>
