2011年3月25日 星期五

關於 SQL 2008 的 Table Value Parameters (TVP)

以往為了將網頁上的多筆資料新增到資料庫，都會將這些資料透過分隔符號組成一個很長的字串或是用 xml 方式來解決（ref:05）。直到 SQL 2008 問世後，開始提供了一個「使用者定義資料表類型」，使得以前揮汗如雨的情況有了改善。簡單的說，從 SQL 2008 開始，您傳給 Store Procedure 或 function 的參數，開始支援「資料表」了。但這裡所指的資料表，並非隨便亂塞一個就行，反而需要事前先在資料庫裡定義資料表類型，日後才能使用。所以要使用 Table Value Parameters(TVP)，首先必須先去定義你自己的資料表類型。



圖1
新增自己的資料表類型，可以參考(圖1)，找到「使用者定義資料表類型」，並點選「新增使用者定義表類型」。

-- ================================
-- Create User-defined Table Type
-- ================================
USE projmng
GO

-- Create the data type
CREATE TYPE dbo.BKTable AS TABLE 
(
 TID int NOT NULL, 
 TName nvarchar(50) NULL,  
    PRIMARY KEY (TID)
)
GO

在目前範例中，我建立了一個名為 BKTable 的自訂資料表類型，有兩個欄位 TID 與 TName ，其中的 TID 是 PKey。
declare @TB BKTable
insert into @TB values
(1,'Paladin'),(2,'Hugo')

select * from @TB
建立完我們自訂的資料表類型後，我好奇地先宣告了一個 BKTable 的變數 @TB 。把它視為是一個 Table ，所以試著把些資料新增進去，再顯示 @TB 的結果，就如(圖2)所示，跟以前使用資料表的方式幾乎一樣。



圖2

為了方便說明，建立了一個實體資料表 TB ，以便待會測試時使用。 

/*-------------------------------------
   create DataTable: TB
  -------------------------------------*/
CREATE TABLE [dbo].[TB](
 [tID] [int] IDENTITY(1,1) NOT NULL,
 [tName] [nvarchar](50) NULL,
 [tKeyin] [nvarchar](20) NULL,
 CONSTRAINT [PK_TB] PRIMARY KEY CLUSTERED 
(
 [tID] 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

接著建立一個 Store Procedure : pr_tb_insert ，用來解釋如何使用 TVP 當作參數傳入 Store Procedure。在下面這段程式裡，傳入參數 @tb 正是我們自訂資料表類型 (BKTable)，唯一特別的地方，是要在參數後方加註 READONLY ，也就是說，針對這個參數 @tb 所指定的資料表內容，就不可以再進行 Insert , Update , Delete 的動作，但你可以使用 Select 或 與其他資料表進行 join (ref:01)。

/*-------------------------------------
   create Procedure: pr_tb_insert
  -------------------------------------*/
CREATE PROCEDURE [dbo].[pr_tb_insert] 
 -- Add the parameters for the stored procedure here
 @tb BKTable READONLY,@keyin nvarchar(50)
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here
 insert into TB 
 select TName,@keyin from @tb
END
GO

以開發工具 VS2010 為例，先將自己的資料存到 Asp.Net 的 DataTable，後續要執行新增的動作時所要注意的地方，是SqlParameter 的資料型別需要設定為「SqlDbType.Structured」，而其他部份則跟你一般在寫的語法沒多大差別。執行完後，就可以看到因呼叫 Store Procedure :pr_tb_insert 而正確的將資料塞進資料表 TB 的結果。

DataTable dt = new DataTable();
DataColumn column = null;

// add column1
column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.ColumnName = "id";
dt.Columns.Add(column);

// add column2
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "Name";
dt.Columns.Add(column);

DataRow dr = null;
// add row1
dr = dt.NewRow();
dr["id"] = "1";
dr["Name"] = "paladin lee";
dt.Rows.Add(dr);

// add row2
dr = dt.NewRow();
dr["id"] = "2";
dr["Name"] = "ken pen";
dt.Rows.Add(dr);

// add row3
dr = dt.NewRow();
dr["id"] = "3";
dr["Name"] = "lillian cheng";
dt.Rows.Add(dr);

Session["MyDT"] = dt;

SqlConnection con;
// 設定連線資訊
string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["projmngConnectionString"].ConnectionString; 
con = new SqlConnection(conStr);
con.Open();
using (con)
{
    // Configure the SqlCommand and SqlParameter.
    SqlCommand sqlCmd = new SqlCommand("pr_tb_insert", con);
    sqlCmd.CommandType = CommandType.StoredProcedure;
    SqlParameter tvpParam = sqlCmd.Parameters.AddWithValue("@tb", dt); //設定 TVP
    tvpParam.SqlDbType = SqlDbType.Structured; //宣告傳入參數的型別是 TVP

    sqlCmd.Parameters.AddWithValue("@keyin", "jj");
    sqlCmd.ExecuteNonQuery();
}
con.Close();

測試的過程中，發現如果你習慣使用 SqlDataSource 來操作資料庫的動作，在處理 TVP 時，會因為找不到可以與參數型別 SqlDbType.Structured 相對應的設定而苦惱。這時可以透過 SessionParameter 指定 SessionField 來處理（ref 06）。它的原理，是將 DataTable 的資料存到 Session ，而後在 SqlDataSource 去指定那個 Session 來當參數。

程式參考：

.aspx
<asp:SqlDataSource ID="ds_insert" runat="server" 
        ConnectionString="<%$ ConnectionStrings:projmngConnectionString %>" 
        SelectCommand="pr_tb_insert" SelectCommandType="StoredProcedure">
        <SelectParameters>
            <asp:SessionParameter ConvertEmptyStringToNull="False" Name="tb" 
                SessionField="MyDT"  />
            <asp:Parameter ConvertEmptyStringToNull="False" Name="keyin" Type="String" />
        </SelectParameters>
    </asp:SqlDataSource>

.aspx.cs 
DataTable dt = new DataTable();
DataColumn column = null;

// add column1
column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.ColumnName = "id";
dt.Columns.Add(column);

// add column2
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "Name";
dt.Columns.Add(column);

DataRow dr = null;
// add row1
dr = dt.NewRow();
dr["id"] = "1";
dr["Name"] = "paladin lee";
dt.Rows.Add(dr);

// add row2
dr = dt.NewRow();
dr["id"] = "2";
dr["Name"] = "ken pen";
dt.Rows.Add(dr);

// add row3
dr = dt.NewRow();
dr["id"] = "3";
dr["Name"] = "lillian cheng";
dt.Rows.Add(dr);

Session["MyDT"]=dt;

ds_insert.SelectParameters["keyin"].DefaultValue = "jason";
ds_insert.Select(new DataSourceSelectArguments());

補充(20110408)：
在一個專案上實際使用 TVP 的功能，而且也採用 SQLDataSource，因為我的資料表非常的大，沒想到發生了以下錯誤：

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 1 ("@tb"): Data type 0x62 (sql_variant) has an invalid type for type-specific metadata.


目前對於這問題，還是沒有明確的解決方式，但放棄使用 SQLDataSource ，卻是眼下可行的方式。


ref 01:Table Valued Parameters New In SQL Server 2008 T-SQL Improvements
ref 02:SQL Server 2008: Table Valued Parameters
ref 03:Sql2008中使用DataTable作为存储过程的参数
ref 04:Table Value Parameters in SQL Server 2008 and .NET (C#)
ref 05:Sales Order Workshop - Articles at SQLServerCentral.com
ref 06:How to set up ASP.NET SQL Datasource to accept TVP
張貼者： 李志堅 於 3/25/2011 03:41:00 下午
標籤：

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)