Usage of using DataTable as a Stored Procedure Parameter

This is a novel method. I have written several stored procedures in my recent work and need to pass strings to the stored procedures. Because SQLServer2000 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 Liststring with a comma and convert it into a string and pass it to the stored procedure.

This is a novel method. 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 Liststring with a comma and convert it into a string and pass it to the stored procedure.

Relatively novelUsage.

I have written severalStorageProcess, You needStorageProcessTo pass the string, because SQL Server 2000 does not have a built-in function similar to the split function, so you have to process it yourself, split a column in the foreground dataset with commas and save it to a List , And then converted to a string to pass Storage ProcessIt is very troublesome. Today, I read the new features of SQL Server 2008, and found that the use of table variables and DataTable As ParametersOf UsageThen, I tried it and talked about it in a simple way.

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,StorageProcess

Code

1 if not exists (SELECT * FROM dbo. sysobjects WHERE id = OBJECT_ID ('users') and objectproperty (id, N 'isusertable') = 1)
2 BEGIN
3 create table dbo. Users
4 (
5 UserID int identity (-1,-1) not null,
6 UserName VARCHAR (20) not null,
7 UserPass VARCHAR (20) not null,
8 Sex bit null,
9 Age smallint null,
10 CONSTRAINT PK_Users_UserID primary key (UserID)
11)
12 END
13 if not exists (SELECT * FROM sys. table_types WHERE name = 'usertable' AND is_user_defined = 1)
14 BEGIN
15 create type UserTable AS TABLE
16 (
17 UserName VARCHAR (20) not null,
18 UserPass VARCHAR (20) not null,
19 Sex bit null,
20 Age SMALLINT NULL
21)
22 END
23 GO
24

Code

1 if exists (SELECT * FROM dbo. sysobjects WHERE id = OBJECT_ID ('SP _ insertsingleuser') and objectproperty (id, n' IsProcedure ') = 1)
2 BEGIN
3 drop procedure dbo. sp_InsertSingleUser
4 END
5 GO
6 create procedure dbo. sp_InsertSingleUser
7 (
8 @ User UserTable READONLY
9)
10
11
12 SET XACT_ABORT ON
13 BEGIN TRANSACTION
14
15 insert into dbo. Users (UserName, UserPass, Sex, Age)
16 SELECT UserName, UserPass, Sex, Age FROM @ User
17
18 COMMIT TRANSACTION
19 SET XACT_ABORT OFF
20 GO

A form is set up on the front-end. The back-end is mainly a function:

Code

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}

Called when you click Add.StorageProcess. The test is complete.