GridView包GridView實作如何將資料以MasterDetail模式顯示

陳淑敏 2016/12/13 09:30:46

0 2226

主題：	ASP.NET_GridView包GridView實作如何將資料以MasterDetail模式顯示
文章簡介：	介紹如何在ASP.NET內用GridView包GridView實作將資料以Master-Detail 的功能（主表明細）顯示
作者：	陳淑敏
版本/產出日期：	V1.0/2016.12.11

1. 前言

• 此篇介紹如何用兩個GridView包覆的方式(俗稱：大腸包小腸)用來呈現Master-Detail的功能（主表明細）顯示，如下圖所示。

2. 開始前準備

實作此Master-Detail的功能是建立於以下版本的環境：

• SQL Server 2008 R2

• Microsoft Visual Studio Ultimate 2013

3. 實作演練

• 先建立資料表[Customer]，執行以下語法即可：

USE [TestDB]

/****** Object: Table [dbo].[Customer] Script Date: 12/11/2016 12:49:12 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Customer](

[CustomerID] [varchar](20) NULL,

[CustomerName] [nvarchar](20) NULL,

[ContactName] [nvarchar](20) NULL,

[ContactTel] [nvarchar](20) NULL,

[Contact_CompanyName] [nvarchar](200) NULL,

[Contact_CompanyAddr] [nvarchar](500) NULL,

[ContactType] [nvarchar](50) NULL,

[Contact_CompanyTel] [nvarchar](20) NULL

) ON [PRIMARY]

INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (960001, N'連小壽', N'魏小平', N'13x1xx7xxxx', N'福氣房地產總公司', N'', N'家庭聯絡人', N'')

INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (960001, N'連小壽', N'連小景', N'13x5xx2xxxx', N'', N'', N'家庭聯絡人', N'')

INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (960001, N'連小壽', N'吕小賀', N'13x5xx7xxxx', N'文明資訊公司', N'', N'緊急聯絡人', N'')

INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (960001, N'連小壽', N'周小進', N'13x6xx3xxxx', N'明智生技協會', N'', N'緊急聯絡人', N'')

INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (960001, N'連小壽', N'游小康', N'13x5xx9xxxx', N'同本人', N'', N'工作聯絡人', N'')

INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (960001, N'連小壽', N'林小雨', N'13x3xx1xxxx', N'同本人', N'', N'工作聯絡人', N'')

INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (111873, N'陳小媛', N'楊小群', N'13x5xx0xxxx', N'', N'', N'家庭聯絡人', N'')

INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (111873, N'陳小媛', N'陳小恩', N'13x1xx0xxxx', N'', N'', N'家庭聯絡人', N'')

INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (111873, N'陳小媛', N'谢小静', N'15x6xx2xxxx', N'', N'', N'緊急聯絡人', N'')

INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (111873, N'陳小媛', N'陳小芳', N'13x0xx0xxxx', N'', N'', N'緊急聯絡人', N'')

INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (111873, N'陳小媛', N'歐小侃', N'13x7xx0xxxx', N'全世界贸易有限公司', N'無', N'工作聯絡人', N'')

INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (111873, N'陳小媛', N'趙小剛', N'15x0xx0xxxx', N'環球贸易有限公司', N'無', N'工作聯絡人', N'')

INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (116886, N'林小健', N'黄小瀟', N'18x5xx3xxxx', N'好康生活用品館', N'無', N'家庭聯絡人', N'')

INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (116886, N'林小健', N'程小龍', N'18x5xx6xxxx', N'真好用金属材料有限公司', N'無', N'家庭聯絡人', N'')

INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (116886, N'林小健', N'盧小城', N'18x5xx5xxxx', N'好便宜金属材料有限公司', N'無', N'緊急聯絡人', N'')

INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (116886, N'林小健', N'董小偉', N'15x5xx6xxxx', N'好好用金属材料有限公司', N'無', N'緊急聯絡人', N'')

INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (116886, N'林小健', N'王小源', N'15x0xx3xxxx', N'天祥小學', N'無', N'工作聯絡人', N'')

INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (116886, N'林小健', N'周小希', N'18x5xx6xxxx', N'天祥小學', N'無', N'工作聯絡人', N'')

• 再建立預儲程序[GetCustomer] / [DeleteCustomer]，撈資料及刪除資料時使用，執行以下語法即可：

/****** Object: StoredProcedure [dbo].[GetCustomer] Script Date: 12/11/2016 12:34:02 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[GetCustomer]

@CustomerID varchar(20)

BEGIN

SET NOCOUNT ON;

select * from TestDB.dbo.Customer

where customerID like case when @CustomerID = '' then '%' else @CustomerID end

END

/****** Object: StoredProcedure [dbo].[DeleteCustomer] Script Date: 12/11/2016 12:34:02 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[DeleteCustomer]

@CustomerID varchar(20)

BEGIN

delete from TestDB.dbo.Customer

where customerID = @CustomerID

END

• 實作Master-Detail模式：

Test.aspx

<%@ Page Language="C#" EnableViewState="true" CodeBehind="Test.aspx.cs" Inherits="WebApplication1.Test" %>

<!DOCTYPE html>

</head>

<body>

<asp:Button ID="btnDelete" runat="server" Text="delete" OnClick="btnDelete_Click" />

<asp:GridView ID="grdCustomer" runat="server" AutoGenerateColumns="False" AllowPaging="True" OnRowDataBound="grdCustomer_RowDataBound" OnRowCommand="grdCustomer_RowCommand">

<asp:TemplateField>

</ItemTemplate>

</asp:TemplateField>

<asp:BoundField DataField="customerID" HeaderText="customerID"/>

<asp:BoundField DataField="customerName" HeaderText="customerName"/>

</Columns>

</asp:GridView>

 </form>

</body>

</html>

Test.aspx.cs

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

namespace WebApplication1

{

public partial class Test : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

BindgrdCustomer();

}

private void BindgrdCustomer()

{

DataTable dtContact = GetContact("");

ViewState["dtContact"] = dtContact;

if (dtContact.AsEnumerable().GroupBy(r =>

r.Field<string>("CustomerID")).Select(g => g.First()).Count() > 0)

{

//篩選出Customer資料

DataTable dtCustomer = dtContact.AsEnumerable().GroupBy(r =>

r.Field<string>("CustomerID")).Select(g => g.First()).CopyToDataTable();

ViewState["dtCustomer"] = dtCustomer;

grdCustomer.DataSource = dtCustomer;

grdCustomer.DataBind();

}

else

{

grdCustomer.DataSource = null;

grdCustomer.DataBind();

Response.Write("<Script language='Javascript'>");

Response.Write("alert('查無資料')");

Response.Write("</" + "Script>");

}

// C#

private DataTable GetContact(string param)

{

DataTable dtTemp;

try

{

//宣告連接字串

string Constr = @"Data Source=ASUS\MSSQLSERVER1;Initial Catalog=TestDB;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False";

//建立SqlConnection

using (SqlConnection conn = new SqlConnection(Constr))

{

using (SqlCommand cmd = new SqlCommand("dbo.GetCustomer", conn))

{

// 設定查詢型態為預存程序

cmd.CommandType = System.Data.CommandType.StoredProcedure;

//加入輸入參數

cmd.Parameters.Add("CustomerID", System.Data.SqlDbType.NChar, 50).Value = param;

conn.Open();

using (dtTemp = new DataTable("Customer"))

{

dtTemp.Load(cmd.ExecuteReader());

}

catch (Exception ex)

{

dtTemp = null;

Response.Write("<Script language='Javascript'>");

Response.Write("alert('" + ex.Message + "')");

Response.Write("</" + "Script>");

}

return dtTemp;

}

private void DeleteContact(string param)

{

try

{

//宣告連接字串

string Constr = @"Data Source=ASUS\MSSQLSERVER1;Initial Catalog=TestDB;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False";

//建立SqlConnection

using (SqlConnection conn = new SqlConnection(Constr))

{

using (SqlCommand cmd = new SqlCommand("dbo.DeleteCustomer", conn))

{

// 設定查詢型態為預存程序

cmd.CommandType = System.Data.CommandType.StoredProcedure;

//加入輸入參數

cmd.Parameters.Add("CustomerID", System.Data.SqlDbType.NChar, 2000).Value = param;

conn.Open();

cmd.ExecuteNonQuery();

}

catch (Exception ex)

{

Response.Write("<Script language='Javascript'>");

Response.Write("alert('" + ex.Message + "')");

Response.Write("</" + "Script>");

}

int subRowCount = 0;

protected void grdCustomer_RowDataBound(object sender, GridViewRowEventArgs e)

{

HtmlInputCheckBox chkSelect;

if (e.Row.RowType == DataControlRowType.Header || e.Row.RowType == DataControlRowType.DataRow)

{

if (e.Row.RowType == DataControlRowType.DataRow)

{

chkSelect = (HtmlInputCheckBox)e.Row.Cells[0].FindControl("chkSelect");

chkSelect.Value = e.Row.Cells[1].Text;

DataTable dtTemp = (DataTable)ViewState["dtContact"];

//篩選出contact資料

var groupList = (from datarow in dtTemp.AsEnumerable()

where datarow.Field<string>("customerID") == e.Row.Cells[1].Text.Trim()

select datarow);

DataTable dtTemp1 = new DataTable();

if(groupList.Count<DataRow>() > 0)

{

dtTemp1 = groupList.CopyToDataTable();

GridView grdContact = CreateGridView(dtTemp1);

GridViewRow gvr = new GridViewRow(-1, 0, DataControlRowType.DataRow, DataControlRowState.Normal);

TableCell cell = new TableCell();

cell.Text = " ";

gvr.Cells.Add(cell);

cell = new TableCell();

cell.Text = " ";

gvr.Cells.Add(cell);

cell = new TableCell();

cell.ColumnSpan= e.Row.Cells.Count - 1;

cell.Controls.Add(grdContact);

gvr.Cells.Add(cell);

subRowCount += 2;

grdCustomer.Controls[0].Controls.AddAt(subRowCount, gvr);

}

private GridView CreateGridView(DataTable dtTemp)

{

GridView grdContact = new GridView();

grdContact.ID = "grdContact";

grdContact.AllowPaging = false;

grdContact.AllowSorting = false;

grdContact.AutoGenerateColumns = false;

grdContact.Style.Add("width", "100%");

grdContact.HeaderStyle.BackColor = System.Drawing.Color.LightGray;

if (dtTemp != null)

{

foreach(DataColumn dc in dtTemp.Columns)

{

BoundField bf = new BoundField();

if (dc.ColumnName == "ContactName")

{

bf.DataField = dc.ColumnName;