表值變量在批量操作中的用法

表值變量在批量操作中的用法：

以更新地區的排序值為例子：

建立表值變量的方法：

View Code

1 CREATE TYPE Ty_UpdateAreaInfoShowOrder as Table
2 (
3 [ID] int primary key not null,
4 [ShowOrder] int null
5 )

在存儲過程中做批量操作的例子：

View Code

CREATE PROC USP_UpdateAreaInfoShowOrder
@dt Ty_UpdateAreaInfoShowOrder readonly
AS
    BEGIN
    DECLARE @ERRORSUM SMALLINT =0
        BEGIN TRAN
            SET @ERRORSUM=-1;
        UPDATE  m SET M.ShowOrder = d.ShowOrder  FROM MD_AreaInfo m ,@dt d where m.ID = d.ID
            IF(@@ERROR<>0) GOTO ERROR_HANDLE;
            COMMIT TRAN;
            RETURN 0
    ERROR_HANDLE:
    ROLLBACK TRAN;
    RETURN @ERRORSUM;
    END

可以看到存儲過程的傳入參數的表值變量類型（上面所定義的表值變量）

調用存儲過程的方法：

View Code

1  public void BindGrid()
 2         {
 3             using (SqlConnection conn = new SqlConnection(ConnectionString))
 4             {
 5                 using (SqlCommand cmd = conn.CreateCommand())
 6                 {
 7                     cmd.CommandText = "SELECT [ID], [CnName],[EnName],[ShowOrder] FROM MD_AreaInfo where Parentid=0 ORDER BY SHOWORDER ";
 8                     DataSet ds = new DataSet();
 9                     SqlDataAdapter da = new SqlDataAdapter(cmd);
10                     da.Fill(ds);
11                     this.gv.DataSource = ds.Tables[0];
12                     this.gv.DataBind();
13                 }
14             }
15         }
16         public DataTable CreateTable()
17         {
18             DataTable dt = new DataTable();
19             dt.Columns.Add("ID", typeof(int));
20             dt.Columns.Add("ShowOrder", typeof(int));
21             return dt;
22         }
23         protected void btn_Click(object sender, EventArgs e)
24         {
25             DataTable dt = CreateTable();
26             for (int i = 0; i < gv.Rows.Count; i++)
27             {
28                 HiddenField hd = (HiddenField)gv.Rows[i].FindControl("hdID");
29                 TextBox tb = (TextBox)gv.Rows[i].FindControl("txtShowOrder");
30                 DataRow dr = dt.NewRow();
31                 dr[0] = int.Parse(hd.Value);
32                 dr[1] = int.Parse(tb.Text.Trim());
33                 dt.Rows.Add(dr);
34             }
35             RunStore("USP_UpdateAreaInfoShowOrder", dt);
36             BindGrid();
37         }
38         public int RunStore(string storeName, DataTable dt)
39         {
40             using (SqlConnection conn = new SqlConnection(ConnectionString))
41             {
42                 conn.Open();
43                 using (SqlCommand cmd = conn.CreateCommand())
44                 {
45                     cmd.CommandText = storeName;
46                     cmd.CommandType = CommandType.StoredProcedure;
47                     SqlParameter sp = new SqlParameter("@dt", SqlDbType.Structured);
48                     sp.TypeName = "Ty_UpdateAreaInfoShowOrder";
49                     sp.Value = dt;
50                     cmd.Parameters.Add(sp);
51                     return cmd.ExecuteNonQuery();
52                 }
53             }
54             return 0;
55         }

表值參數是 SQL Server 2008 中的新參數類型。表值參數是使用使用者定義的表類型來聲明的。使用表值參數，可以不必建立臨時表或許多參數，即可向 Transact-SQL 語句或例程（如存儲過程或函數）發送多行資料；

表值參數具有更高的靈活性，在某些情況下，可比臨時表或其他傳遞參數清單的方法提供更好的性能。表值參數具有以下優勢：

首次從用戶端填充資料時，不擷取鎖。
提供簡單的程式設計模型。
允許在單個例程中包括複雜的業務邏輯。
減少到伺服器的往返。
可以具有不同基數的表結構。
是強類型。
使用戶端可以指定排序順序和唯一鍵。

限制

表值參數有下面的限制：
- SQL Server 不維護表值參數列的統計資訊。
- 表值參數必須作為輸入 READONLY 參數傳遞到 Transact-SQL 例程。不能在例程體中對表值參數執行諸如 UPDATE、DELETE 或 INSERT 這樣的 DML 操作。
- 不能将表值參數用作 SELECT INTO 或 INSERT EXEC 語句的目标。表值參數可以在 SELECT INTO 的 FROM 子句中，也可以在 INSERT EXEC 字元串或存儲過程中。

注SqlParameter的SqlDbType定義為SqlDbType.Structured；

TypeName為你所定義表值類型名

value 為建構的新的記憶體中的表

舉個例子，建立表值變量

View Code

1 ---------------------------------------------------------
 2  ----建立表值變量Ty_ProductionLocation--------------------
 3  ---------------------------------------------------------
 4  CREATE TYPE Ty_ProductionLocation AS TABLE(
 5  Name NVARCHAR(50) NOT NULL,
 6  CostRate smallmoney NOT NULL,
 7  Availability DECIMAL(18,4) NOT NULL
 8  )
 9  ---------------------------------------------------------
10  ----建立存儲過程usp_AddProductLocation-------------------
11  ---------------------------------------------------------
12  CREATE PROC usp_AddProductLocation
13  @dt Ty_ProductionLocation READONLY
14  AS
15     BEGIN
16         SET NOCOUNT ON;
17         DECLARE @ERRORSUM SMALLINT;
18         BEGIN TRAN;
19             SET @ERRORSUM =-1;
20             INSERT INTO Production.Location(Name,CostRate,Availability,ModifiedDate)
21             SELECT tt.*,GETDATE() FROM @dt tt;
22             IF(@@ERROR<>0) GOTO ERROR_HANDLE;
23         COMMIT TRAN;
24         RETURN 0;
25 ERROR_HANDLE:
26         ROLLBACK TRAN;
27         RETURN @ERRORSUM;
28     END
29 ----------------------------------------------------------
30 --------調用usp_AddProductLocation存儲過程----------------
31 ----------------------------------------------------------
32 DECLARE @dtt Ty_ProductionLocation;
33 INSERT INTO @dtt(Name,CostRate,Availability)  SELECT Name,0,0 FROM  Person.StateProvince;
34 EXEC usp_AddProductLocation @dtt

可以看到，建立表值變量類型，聲明變量來引用它，然後給變量填充資料，然後将值傳遞給存儲過程.

表值變量 在批量操作中的用法

表值變量在批量操作中的用法