Usage example of using DataTable as a Stored Procedure Parameter

I have written several stored procedures in my recent work and need to pass strings to the stored procedures. Because SQL Server 2000 does not have built-in functions similar to split, I have to handle them myself, it is very troublesome to split a column in the foreground dataset into a List <string> with a comma and convert it into a string and pass it to the stored procedure. Today, I read the new features of SQL Server 2008 and found that the use of table variables and the usage of DataTable as a parameter have been made. I just tried it and made a simple discussion.
Download Sample Code

I. Test Environment
1. Windows Server 2008 R2 DataCenter
2. Visual Studio 2008 Team System With SP1
3. SQL Server 2008 Enterprise Edition With SP1
Because it is a new feature of SQL Server 2008, only 2008 can be used.
Ii. Test Overview
The test project is simple, that is, adding a new user
 
3. Prepare data
1. create databases, tables, types, and stored procedures
Copy codeThe Code is as follows:
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

Copy codeThe Code is as follows:
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

A form is set up on the front-end. The back-end is mainly a function:
Copy codeThe Code is as follows:
Public void fnInsertSingleUser (DataTable v_dt)
{
Try
{
SqlConnection cn = new SqlConnection (CONN );
SqlCommand cmd = cn. CreateCommand ();
Cmd. CommandType = CommandType. StoredProcedure;
Cmd. CommandText = @ "sp_InsertSingleUser ";
SqlParameter p = cmd. Parameters. AddWithValue ("@ User", v_dt );
10
DataSet ds = new DataSet ();
SqlDataAdapter da = new SqlDataAdapter (cmd );
Da. Fill (ds );
}
Catch (Exception ex)
{
Throw ex;
}
}

Click Add to call the stored procedure. The test is complete.