http://www.cnblogs.com/zhangchenliang/archive/2011/05/19/2050752.html
一、測(cè)試環(huán)境
1、Windows
Server 2008 R2 DataCenter
2、Visual
Studio 2008 Team System With SP1
3、SQL
Server 2008 Enterprise Edition With SP1
由于是SQL Server 2008新特性，所以只能用2008。
二、測(cè)試概述
測(cè)試項(xiàng)目很簡(jiǎn)單，就是添加新用戶
三、準(zhǔn)備數(shù)據(jù)
1、建立數(shù)據(jù)庫(kù)、表、類型、存儲(chǔ)過(guò)程
- <span style="font-size:14px;">IF NOT EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('Users') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
- BEGIN
- CREATE TABLE dbo.Users
- (
- UserID INT IDENTITY(-1, -1) NOT NULL,
- UserName VARCHAR(20) NOT NULL,
- UserPass VARCHAR(20) NOT NULL,
- Sex BIT NULL,
- Age SMALLINT NULL,
- CONSTRAINT PK_Users_UserID PRIMARY KEY(UserID)
- )
- END
- IF NOT EXISTS(SELECT * FROM sys.table_types WHERE name = 'UserTable' AND is_user_defined = 1)
- BEGIN
- CREATE TYPE UserTable AS TABLE
- (
- UserName VARCHAR(20) NOT NULL,
- UserPass VARCHAR(20) NOT NULL,
- Sex BIT NULL,
- Age SMALLINT NULL
- )
- END
- GO</span>
- <span style="font-size:14px;">IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('sp_InsertSingleUser') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
- BEGIN
- DROP PROCEDURE dbo.sp_InsertSingleUser
- END
- GO
- CREATE PROCEDURE dbo.sp_InsertSingleUser
- (
- @User UserTable READONLY
- )
- AS
-
- SET XACT_ABORT ON
- BEGIN TRANSACTION
-
- INSERT INTO dbo.Users(UserName, UserPass, Sex, Age)
- SELECT UserName, UserPass, Sex, Age FROM @User
-
- COMMIT TRANSACTION
- SET XACT_ABORT OFF
- GO</span>
前臺(tái)搭建好表單，后臺(tái)主要是一個(gè)函數(shù)：
1
public
void fnInsertSingleUser(DataTable v_dt)
2 {
3
try
4 {
5 SqlConnection cn=new
SqlConnection(CONN);
6 SqlCommand cmd= cn.CreateCommand();
7 cmd.CommandType= CommandType.StoredProcedure;
8 cmd.CommandText=@"sp_InsertSingleUser";
9 SqlParameter p= cmd.Parameters.AddWithValue("@User",
v_dt);
10
11
DataSet ds =new DataSet();
12 SqlDataAdapter da=new
SqlDataAdapter(cmd);
13 da.Fill(ds);
14 }
15
catch (Exception ex)
16 {
17 throw ex;
18 }
19 }
點(diǎn)擊【添加】按鈕時(shí)調(diào)用存儲(chǔ)過(guò)程。測(cè)試是完成了，也很簡(jiǎn)單，傳遞一個(gè)DataTable做參數(shù)確實(shí)很方便吧，能夠輕松完成原先需要很多編碼的工 作。關(guān)于表變量還是有些道道的，如創(chuàng)建時(shí)判斷其是否存在的語(yǔ)句，刪除表變量前需要先刪除引用表變量的存儲(chǔ)過(guò)程等。一般開發(fā)我大多會(huì)選擇用臨時(shí)表，處理起來(lái) 比較方便，表變量可以作為存儲(chǔ)過(guò)程參數(shù)確實(shí)是一個(gè)獨(dú)特的優(yōu)勢(shì)，希望在SQL Server的未來(lái)版本中能夠繼續(xù)增強(qiáng)對(duì)表變量和臨時(shí)表的支持，尤其是早日支持臨時(shí)表調(diào)試：）
