Usage example of using DataTable as a Stored Procedure Parameter

Source: Internet
Author: User

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.

Related Article

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.