Example of calling a stored procedure in. net

1. You must first create a stored procedure in the database.
Take sqlserver2005 as an example. The database used is the sample database adventureworks provided by sqlserver2005. For details about how to install the database, refer to the 2005 online help topic "Run installation ". Program Install adventureworks sample database and example ".
Create a stored procedure named prd_shoppingcartitem. Code
Create   Procedure Prd_shoppingcartitem
( @ Shoppingcartid   Nvarchar ( 50 ), @ Quantity   Int   =   1 , @ Productid   Int )
As  
If   Exists ( Select   *   From Sales. shoppingcartitem Where Shoppingcartid =   @ Shoppingcartid   And Productid =   @ Productid )
Begin  
Update Sales. shoppingcartitem Set Quantity =   @ Quantity   Where Shoppingcartid =   @ Shoppingcartid   And Productid =   @ Productid  
End  
Else
Begin  
Insert   Into Sales. shoppingcartitem (shoppingcartid, quantity, productid)
Values ( @ Shoppingcartid , @ Quantity , @ Productid )
End  
Go

2. Call this stored procedure in A. NET application Code
String Source =   " Server = rsb_022 \ aryang; Integrated Security = sspi; database = adventureworks " ;
Using (Sqlconnection Conn =   New Sqlconnection (source ))
{
Conn. open ();
Sqlcommand cmd =   New Sqlcommand ( " Prd_shoppingcartitem " , Conn );
Cmd. commandtype = Commandtype. storedprocedure;
Cmd. Parameters. Add ( " @ Shoppingcartid " , Sqldbtype. nvarchar, 50 , " Shoppingcartid " );
Cmd. Parameters. Add ( " @ Quantity " , Sqldbtype. Int, 4 , " Quantity " );
Cmd. Parameters. Add ( " @ Productid " , Sqldbtype. Int, 4 , " Productid " );
Cmd. Parameters [ 0 ]. Value =   " 20622 " ;
Cmd. Parameters [ 1 ]. Value =   7 ;
Cmd. Parameters [ 2 ]. Value =   874 ;
Cmd. executenonquery ();

}

3. advantages of using Stored Procedures
(1) Embedding SQL statements in the application environment is not easy to modify. After modification, you must re-compile the program, it brings a lot of trouble to deployment. If you concentrate your T-SQL on stored procedures, you can quickly and conveniently modify SQL without changing the name of the stored procedure.
(2) stored procedures can help dense queries reduce network traffic, because applications only call stored procedures rather than dozens of SQL statements with hundreds of rows.
(3) stored procedures facilitate Code reusability. A defined stored procedure can be called in multiple places of the application.
(4) the stored procedure is more stable for queries.
(5) stored procedures make your system more secure. For example, SQL statements embedded in applications are easy to receive SQL injection attacks, and the database infrastructure information is easily exposed to people.