[C#] GridView 練習

論壇問題 - GridView 練習題，討論內還有大神的 Youtube 教學

問題說明

大神 YouTube 教學

• Demo Master Detail GridView with Multi DataKeys
  
• Demo Master Detail GridView with Multi DataKeys Part 2
  

以下為自我練習紀錄

資料庫相關

建立 Table 和測試資料

USE [AdventureWorks2014]
GO

CREATE TABLE [dbo].[GVPractice](
    [RowNO] [int] NOT NULL,
    [ProdNO] [char](5) NOT NULL CONSTRAINT [DF_GVPractice_ProdNO]  DEFAULT (''),
    [ProdName] [nchar](10) NOT NULL CONSTRAINT [DF_Table_1_Name]  DEFAULT (''),
    [SerialNO] [char](3) NOT NULL CONSTRAINT [DF_GVPractice_SerialNO]  DEFAULT (''),
    [Status] [nchar](10) NOT NULL CONSTRAINT [DF_GVPractice_Status]  DEFAULT (''),
    [Area] [char](1) NOT NULL CONSTRAINT [DF_GVPractice_Area]  DEFAULT (''),
    [CreateDate] [date] NULL,
CONSTRAINT [PK_GVPractice] PRIMARY KEY CLUSTERED 
(
    [RowNO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- 建立測試資料
INSERT INTO GVPractice (RowNO,ProdName,ProdNO,SerialNO,Status,Area,CreateDate) 
SELECT 1 , N'鉛筆' , 'B-666' , 'A01' , N'良' , 'A' , '20150501'
UNION ALL       
SELECT 2 , N'鉛筆' , 'B-666' , 'A02' , N'壞' , 'A' , '20150502'
UNION ALL       
SELECT 3 , N'鉛筆' , 'B-666' , 'A03' , N'借' , 'B' , '20150503'
UNION ALL       
SELECT 4 , N'鉛筆' , 'B-666' , 'A04' , N'良' , 'A' , '20150504'
UNION ALL
SELECT 5 , N'擦子' , 'A-333' , 'B01' , N'良' , 'A' , '20150505'
UNION ALL       
SELECT 6 , N'擦子' , 'A-333' , 'B02' , N'良' , 'A' , '20150506'
UNION ALL       
SELECT 7 , N'擦子' , 'A-333' , 'B03' , N'壞' , 'B' , '20150507'
UNION ALL
SELECT 8 , N'滑鼠' , 'C-444' , 'D01' , N'良' , 'A' , '20150508'
UNION ALL
SELECT 9 , N'滑鼠' , 'C-444' , 'D02' , N'良' , 'A' , '20150509'
UNION ALL
SELECT 10 , N'滑鼠' , 'C-444' , 'D03' , N'壞' , 'A' , '20150510'
UNION ALL
SELECT 11 , N'滑鼠' , 'C-444' , 'D04' , N'壞' , 'A' , '20150511'
UNION ALL
SELECT 12 , N'滑鼠' , 'C-444' , 'D05' , N'借' , 'A' , '20150512'
UNION ALL
SELECT 13 , N'鉛筆' , 'B-666' , 'A05' , N'良' , 'A' , '20150513'
UNION ALL
SELECT 14 , N'擦子' , 'A-333' , 'B04' , N'壞' , 'A' , '20150514'
GO

USE [AdventureWorks2014]
GO

CREATE PROCEDURE [dbo].[uspQtyStat] 
(
    @StartDate date, 
    @EndDate date, 
    @ProdNO char(5) = '', 
    @Status nchar(4) = ''
)
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @TSQL nvarchar(max)
 
    SET @TSQL = 
        'SELECT 
             ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NO ,
             ProdName , 
             ProdNO , 
             Status , 
             Area ,
             COUNT(*) AS Qty
         FROM GVPractice
         WHERE CreateDate BETWEEN @StartDate AND @EndDate'
 
    IF @ProdNO <> ''
        SET @TSQL += 
            '
                AND ProdNO = @ProdNO'

    IF @Status <> ''
        SET @TSQL +=
            '
                AND Status = @Status'
 
    SET @TSQL += 
        '
            GROUP BY ProdName , ProdNO , Status , Area'

    EXEC dbo.sp_executesql 
        @TSQL,
        N'@StartDate date, @EndDate date, @ProdNO char(5), @Status nchar(4)',
        @StartDate ,
        @EndDate ,
        @ProdNO ,
        @Status
END
GO

USE [AdventureWorks2014]
GO

CREATE PROCEDURE [dbo].[uspProductSearch] (@StartDate date , @EndDate date , @ProdNO char(5), @Status nchar(10) , @Area char(1))
AS
BEGIN

    SET NOCOUNT ON;

    SELECT * 
    FROM GVPractice
    WHERE CreateDate BETWEEN @StartDate AND @EndDate
        AND ProdNO = @ProdNO
        AND [Status] = @Status
        AND Area = @Area
END
GO

DAO 存取相關

public class Product
{
    public int RowNO { get; set; }
    public string ProdName { get; set; }
    public string ProdNO { get; set; }
    public string SerialNO { get; set; }
    public string Status { get; set; }
    public string Area { get; set; }
    public DateTime CreateDate { get; set; }
}

public class Stat
{
    public int NO { get; set; }
    public string ProdName { get; set; }
    public string ProdNO { get; set; }
    public string Status { get; set; }
    public string Area { get; set; }
    public int Qty { get; set; }
}

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public class DAO
{

    public static List<Stat> QtyStat(DateTime StartDate, DateTime EndDate, string ProdNO, string Status)
    {
        List<Stat> result = new List<Stat>();

        try
        {
            string ConnString = ConfigurationManager.ConnectionStrings["BS"].ToString();
            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                conn.Open();

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "uspQtyStat";
                cmd.Parameters.AddWithValue("@StartDate", StartDate);
                cmd.Parameters.AddWithValue("@EndDate", EndDate);
                cmd.Parameters.AddWithValue("@ProdNO", ProdNO);
                cmd.Parameters.AddWithValue("@Status", Status);

                SqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    Stat S = new Stat()
                    {
                        NO = Convert.ToInt32(dr["NO"]),
                        ProdNO = dr["ProdNO"].ToString(),
                        ProdName = dr["ProdName"].ToString(),
                        Area = dr["Area"].ToString(),
                        Status = dr["Status"].ToString(),
                        Qty = Convert.ToInt32(dr["Qty"])
                    };

                    result.Add(S);
                }
            }
        }
        catch (Exception ex)
        {
            throw new Exception(string.Format("QtyStat() 異常，錯誤訊息為 {0}",ex.Message));
        }

        return result;
    }

    public static List<Product> ProdcutSearch(DateTime StartDate, DateTime EndDate, string ProdNO, string Status, string Area)
    {
        List<Product> result = new List<Product>();

        try
        {
            string ConnString = ConfigurationManager.ConnectionStrings["BS"].ToString();
            using (SqlConnection conn = new SqlConnection(ConnString))
            {

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "uspProductSearch";
                cmd.Parameters.AddWithValue("@StartDate", StartDate);
                cmd.Parameters.AddWithValue("@EndDate", EndDate);
                cmd.Parameters.AddWithValue("@ProdNO", ProdNO);
                cmd.Parameters.AddWithValue("@Status", Status);
                cmd.Parameters.AddWithValue("@Area", Area);

                conn.Open();

                SqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    Product p = new Product()
                    {
                        RowNO = Convert.ToInt32(dr["RowNO"]),
                        ProdNO = dr["ProdNO"].ToString(),
                        ProdName = dr["ProdName"].ToString(),
                        SerialNO = dr["Serialno"].ToString(),
                        Status = dr["Status"].ToString(),
                        Area = dr["Area"].ToString(),
                        CreateDate = Convert.ToDateTime(dr["CreateDate"])
                    };

                    result.Add(p);
                }
            }
        }
        catch (Exception ex)
        {
            throw new Exception(string.Format("ProdcutSearch() 異常，錯誤訊息為 {0}",ex.Message));
        }

        return result;
    }
}

aspx
只紀錄 gvStat 的設定

<asp:GridView 
    ID="gvStat" 
    runat="server" 
    AutoGenerateColumns="False" 
    CellPadding="4" 
    DataKeyNames="ProdNO,Status,Area" 
    OnSelectedIndexChanged="gvStat_SelectedIndexChanged">
</asp:GridView>

aspx.cs

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            // 搜尋控件預設值
            txtStartDate.Text = DateTime.Today.ToString("yyyy/MM/dd");
            txtEndDate.Text = DateTime.Today.ToString("yyyy/MM/dd");
            ddlProdName.SelectedIndex = -1;
            ddlStatus.SelectedIndex = -1;
        }
    }

    protected void btnSearch_Click(object sender, EventArgs e)
    {
        DateTime
            StartDate = Convert.ToDateTime(txtStartDate.Text),
            EndDate = Convert.ToDateTime(txtEndDate.Text);
        string
            ProdNO = ddlProdName.SelectedValue == "ALL" ? "" : ddlProdName.SelectedValue ,
            Status = ddlStatus.SelectedValue == "ALL" ? "" : ddlStatus.SelectedValue;

            List<Stat> StatList = DAO.QtyStat(StartDate, EndDate, ProdNO, Status);
            gvStat.DataSource = StatList;
            gvStat.DataBind();

    }

    protected void gvStat_SelectedIndexChanged(object sender, EventArgs e)
    {

        int index = gvStat.SelectedIndex;
        if (index == -1) return;
        
        // gvStat 的 DataKeys = ProdNO,Status,Area
        string
            ProdNO = gvStat.SelectedDataKey["ProdNO"].ToString(),
            Status = gvStat.SelectedDataKey["Status"].ToString(),
            Area = gvStat.SelectedDataKey["Area"].ToString();

        DateTime
            StartDate = Convert.ToDateTime(txtStartDate.Text),
            EndDate = Convert.ToDateTime(txtEndDate.Text) ;

        List<Product> ProductList = DAO.ProdcutSearch(StartDate, EndDate, ProdNO, Status, Area);
        gvDetail.DataSource = ProductList;
        gvDetail.DataBind();
    }
}

測試結果

參考資料
• 論壇問題出處
• Demo Master Detail GridView with Multi DataKeys
• Demo Master Detail GridView with Multi DataKeys Part 2
• Using Data Classes in ASP.NET - A Beginner's Guide