[ASP.net] GridView 如何動態插入小計資料 (使用SQL函數解法)

2011-07-02

14559
0
ASP.net WebForm
2012-02-10

[ASP.net] GridView 如何動態插入小計資料 (使用SQL 函數解法)

問題來源：

http://social.msdn.microsoft.com/Forums/zh-TW/236/thread/349c010c-66cf-4ecd-90de-49777ff599d9

雖說用UI端的大腸包小腸方式是可以做，但個人覺得這種事還是交由後端處理完再送給前端呈現，比較能減少前端UI程式碼的複雜性

Create table tb_UserTable /*建立Table*/
(
    [id] [int] IDENTITY(1,1) NOT NULL Primary Key,
    [地面設施分類] [nvarchar](50) NULL,
    [地面設施名稱] [nvarchar](50) NULL,
    [地面設施狀態] [nvarchar](50) NULL

)
/*塞資料*/
Insert into tb_UserTable ([地面設施分類],[地面設施名稱],[地面設施狀態]) Values (N'雷達',N'雷達一號',N'可用')
Insert into tb_UserTable ([地面設施分類],[地面設施名稱],[地面設施狀態]) Values (N'雷達',N'雷達二號',N'可用')
Insert into tb_UserTable ([地面設施分類],[地面設施名稱],[地面設施狀態]) Values (N'雷達',N'雷達三號',N'不可用')
Insert into tb_UserTable ([地面設施分類],[地面設施名稱],[地面設施狀態]) Values (N'港口',N'港口一號',N'可用')
Insert into tb_UserTable ([地面設施分類],[地面設施名稱],[地面設施狀態]) Values (N'港口',N'港口二號',N'不可用')
Insert into tb_UserTable ([地面設施分類],[地面設施名稱],[地面設施狀態]) Values (N'機場',N'機場一號',N'可用')
Insert into tb_UserTable ([地面設施分類],[地面設施名稱],[地面設施狀態]) Values (N'機場',N'機場二號',N'可用')
Insert into tb_UserTable ([地面設施分類],[地面設施名稱],[地面設施狀態]) Values (N'機場',N'機場三號',N'可用')
Insert into tb_UserTable ([地面設施分類],[地面設施名稱],[地面設施狀態]) Values (N'機場',N'機場四號',N'不可用')

函數呼叫(分類以逗號區隔)：

前端程式

aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">



    <asp:GridView ID="GridView1" runat="server" />



    </form>
</body>
</html>

.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Text;

public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

        //Step 1.先把分類撈出來
        DataTable dt_Categorys = this.queryDataTable(@"Select [地面設施分類] 
		                                                 From tb_UserTable
		                                                 Group By [地面設施分類]");
        //Step 2.串接分類字串
        StringBuilder sb = new StringBuilder();
        foreach (DataRow dr in dt_Categorys.Rows)
        {
            sb.Append(dr["地面設施分類"].ToString() + ",");
        }
        //把分類字串最後一個逗號去掉
        string categorys = sb.ToString().Substring(0, sb.ToString().Length - 1);

        //Step 3.呼叫函數取得DataTable來繫結GridView
        GridView1.DataSource = this.queryDataTable("Select * from dbo.udf_UserTable(N'" + categorys + "',N'可用')");
        GridView1.DataBind();
    }

    
    protected DataTable queryDataTable(string sql)
    {
        
        using (SqlConnection conn=new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=NorthwindChinese;Integrated Security=True"))
        {
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return (ds.Tables.Count > 0) ? ds.Tables[0] : new DataTable();
            
        }
    
    
    }
}

前端程式執行結果：

最後最重要的主角，SQL函數如下：

Create FUNCTION udf_UserTable 
(@CategoryNames nvarchar(Max),@Status nvarchar(MAX))
RETURNS @User_Table TABLE
   (
   [id] [int] IDENTITY(1,1) NOT NULL,
   [地面設施分類] [nvarchar](50) NULL,
   [地面設施名稱] [nvarchar](50) NULL,
   [地面設施狀態] [nvarchar](50) NULL
   )
BEGIN 
    Declare @TempStr nvarchar(MAX)
    
    WHILE (CHARINDEX(',',@CategoryNames)>0)/*@CategoryNames有包含逗號就一直執行迴圈*/
	BEGIN
		Set @TempStr=SUBSTRING(@CategoryNames,1,CHARINDEX(',',@CategoryNames)-1)/*取出最前面的類別*/
		Insert into @User_Table
		Select [地面設施分類],[地面設施名稱],[地面設施狀態] 
		From tb_UserTable
		Where [地面設施分類]=@TempStr And [地面設施狀態]=@Status
	    
	    
		Insert into @User_Table ([地面設施分類],[地面設施名稱],[地面設施狀態]) Values (@TempStr,('小計'+@Status+'數量: ' + Convert(varchar,@@ROWCOUNT)),'') 
	    
	    
		Set @CategoryNames = REPLACE(@CategoryNames,@TempStr+',','')/*把最前面的類別加上逗號後，取代為空字串再指派回給@CategoryNames*/
	END/*End While*/
	IF(LEN(@CategoryNames)>0 And CHARINDEX(',',@CategoryNames)=0) /*@CategoryNames有值但沒有逗號，表示此為最後一個類別*/
	Begin
	    Set @TempStr=@CategoryNames /*取出類別*/
	    
		Insert into @User_Table
		Select [地面設施分類],[地面設施名稱],[地面設施狀態] 
		From tb_UserTable
		Where [地面設施分類]=@TempStr And [地面設施狀態]=@Status
	    
	    
		Insert into @User_Table ([地面設施分類],[地面設施名稱],[地面設施狀態]) Values (@TempStr,('小計'+@Status+'數量: ' + Convert(varchar,@@ROWCOUNT)),'') 
	    
	    
	End /*End IF*/

   RETURN /*回傳table變數*/
END

2011.7.2 追記Summarizing Data with CUBE and ROLLUP

2012.2.10 點部落格其他類似文章：[ASP.NET]自訂GridView中的小計列

MSDN討論：菜鸟：单表分类汇总求和问题如何写sql语句

回首頁