Laerning ADO.NET 3.5 Cookbook：(1) Synchronizing Master-Detail Data in a Web Forms Application
中文介绍：就是两个表之间的同步的显示，点击主表，子表同步更新。主要使用DataView、DataRowView、DataRelation等DataSet技术。
实现效果如图：
From 《ADO.NET 3.5 Cookbook 2nd Edition 2008》
Problem
You need to create a master-detail pair of GridView controls and synchronize them so that when you
select a record in the master, the child grid is updated with the corresponding
records.
Default Aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs"
Inherits="MasterDetailWebFormDataGrid._Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="headerGridView" runat="server"
onselectedindexchanged="headerGridView_SelectedIndexChanged">
</asp:GridView>
<br />
<asp:GridView ID="detailGridView" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>
|
using System;
using System.Data;
using System.Data.SqlClient;
namespace MasterDetailWebFormDataGrid
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
headerGridView.AutoGenerateSelectButton = true;
headerGridView.SelectedRowStyle.BackColor = System.Drawing.Color.Gray;
DataSet ds = LoadData();
// Bind the SalesOrderHeader GridView.
headerGridView.DataSource = ds.Tables["SalesOrderHeader"].DefaultView;
//获取或设置一个数组，该数组包含了显示在 GridView 控件中的项的主键字段的名称。
headerGridView.DataKeyNames = new string[] { "SalesOrderID" };
Page.DataBind();
}
}
protected DataSet LoadData()
{
string sqlConnectString = @"Data Source=(local);
Integrated security=SSPI;Initial Catalog=AdventureWorks;";
string sqlSelect =
@"SELECT SalesOrderID, OrderDate, SalesOrderNumber, TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesOrderID BETWEEN 43660 AND 43669;
SELECT SalesOrderID, SalesOrderDetailID, OrderQty, ProductID, LineTotal
FROM Sales.SalesOrderDetail
WHERE SalesOrderID BETWEEN 43660 AND 43669;";
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
headerGridView.DataKeyNames = new string[] { "SalesOrderID" };
Page.DataBind();
}
}
protected DataSet LoadData()
{
string sqlConnectString = @"Data Source=(local);
Integrated security=SSPI;Initial Catalog=AdventureWorks;";
string sqlSelect =
@"SELECT SalesOrderID, OrderDate, SalesOrderNumber, TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesOrderID BETWEEN 43660 AND 43669;
SELECT SalesOrderID, SalesOrderDetailID, OrderQty, ProductID, LineTotal
FROM Sales.SalesOrderDetail
WHERE SalesOrderID BETWEEN 43660 AND 43669;";
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
//获取一个集合，它提供源表和 DataTable 之间的主映射。
da.TableMappings .Add("Table", "SalesOrderHeader");
da.TableMappings.Add("Table1", "SalesOrderDetail");
DataSet ds = new DataSet();
da.Fill(ds);
// Add a relation between parent and child table.
ds.Relations.Add("FK_SalesOrderDetail_SalesOrderHeader",
ds.Tables["SalesOrderHeader"].Columns["SalesOrderID"],
ds.Tables["SalesOrderDetail"].Columns["SalesOrderID"]);
// Store data in session variable to store data between
// posts to server.
Session["SalesOrderDataSet"] = ds;
return ds;
}
protected void headerGridView_SelectedIndexChanged(object sender, EventArgs e)
{
if (headerGridView.SelectedIndex != -1)
{
int salesOrderID =
(int)headerGridView.DataKeys[headerGridView.SelectedIndex].Value;
// Get the SalesOrderHeader data view from the session variable.
DataView headerDV =
((DataSet)Session["SalesOrderDataSet"]).
Tables["SalesOrderHeader"].DefaultView;
// Get the selected DataRowView from the SalesOrderHeader table.
headerDV.Sort = "SalesOrderID";
DataRowView headerDRV = headerDV[headerDV.Find(salesOrderID)]; ;
// Bind the child view to the Order Details data grid.
detailGridView.DataSource =
headerDRV.CreateChildView("FK_SalesOrderDetail_SalesOrderHeader");
}
else
{
detailGridView.DataSource = null;
}
detailGridView.DataBind();
}
}
}
da.TableMappings.Add("Table1", "SalesOrderDetail");
DataSet ds = new DataSet();
da.Fill(ds);
// Add a relation between parent and child table.
ds.Relations.Add("FK_SalesOrderDetail_SalesOrderHeader",
ds.Tables["SalesOrderHeader"].Columns["SalesOrderID"],
ds.Tables["SalesOrderDetail"].Columns["SalesOrderID"]);
// Store data in session variable to store data between
// posts to server.
Session["SalesOrderDataSet"] = ds;
return ds;
}
protected void headerGridView_SelectedIndexChanged(object sender, EventArgs e)
{
if (headerGridView.SelectedIndex != -1)
{
int salesOrderID =
(int)headerGridView.DataKeys[headerGridView.SelectedIndex].Value;
// Get the SalesOrderHeader data view from the session variable.
DataView headerDV =
((DataSet)Session["SalesOrderDataSet"]).
Tables["SalesOrderHeader"].DefaultView;
// Get the selected DataRowView from the SalesOrderHeader table.
headerDV.Sort = "SalesOrderID";
DataRowView headerDRV = headerDV[headerDV.Find(salesOrderID)]; ;
// Bind the child view to the Order Details data grid.
detailGridView.DataSource =
headerDRV.CreateChildView("FK_SalesOrderDetail_SalesOrderHeader");
}
else
{
detailGridView.DataSource = null;
}
detailGridView.DataBind();
}
}
}
