Usage of using DataTable as a Stored Procedure Parameter

Source: Internet
Author: User
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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.