To use the stored procedure in C #, first check the SQL statement for creating the test table:
Create Table Test55
(
UID Int Identity ( 1 , 1 ) ,
Class1 varchar ( 20 ) ,
Class2 varchar ( 20 ) ,
Uname varchar ( 20 ) ,
Birth smalldatetime ,
Meno varchar ( 50 )
)
Alter Table Test55
Add Constraint Primary_id primary key ( UID)
|
Create a stored procedure with input, output, and return value parameters:
Create proc proc_out @ UUID int, @ output varchar (200) Output
As
-- Select result set
Select * from test where uid> @ uid
-- Assign values to output parameters
Set @ output = 'total records: '+ convert (varchar (10), (select count (*) from test ))
-- Return is used to return a value to the stored procedure.
Return 200;
Go
Use stored procedures in C:
Use SQL statements with Parameters
Private Void SQL _param ( )
{
Sqlconnection Conn = New Sqlconnection ( "Server =.; uid = sa; Pwd = 1234; database = China" ) ;
// The @ myid parameter is introduced in the SQL statement.
String SQL = "Select * from test where uid> @ myid" ;
Sqlcommand Comm = New Sqlcommand ( SQL , Conn ) ;
// Use the add method of the parameters attribute of Comm to define and assign values to the above @ myid Parameter
// The sqldbtype class provides the same database type as the sqlserver Data Type
Sqlparameter SP = Comm . Parameters . Add ( "@ Myid" , Sqldbtype . Int ) ;
SP . Value = 10 ; // Assign values to input parameters
// The default execution method of the command object is text. You can also skip the next sentence.
Comm . Commandtype = Commandtype . Text ;
// Upload the command object as a parameter of dataadapter
Sqldataadapter Da = New Sqldataadapter ( Comm ) ;
Dataset DS = New Dataset ( ) ;
Da . Fill ( DS) ;
// Bind data to the datagrid1 Control
This . Datagrid1 . Datasource = DS ;
This . Datagrid1 . Databind ( ) ;
}
|
Standard Edition for Stored Procedures
Private Void SQL _proc ( )
{
Sqlconnection Conn = New Sqlconnection ( "Server =.; uid = sa; Pwd = 1234; database = China" ) ;
String SQL = "Proc_out" ;
Sqlcommand Comm = New Sqlcommand ( SQL, Conn ) ;
// Change the command execution type to the stored procedure mode. The default value is text.
Comm . Commandtype = Commandtype . Storedprocedure ;
// Pass an input parameter, which must be assigned a value
Sqlparameter SP = Comm . Parameters . Add ( "@ Uid" , Sqldbtype . Int ) ;
SP . Value = 10;
// Define an output parameter without assigning a value. Direction is used to describe the parameter type.
// Direction is the input parameter by default, and has the output parameter and return value type.
SP = Comm . Parameters . Add ( "@ Output" , Sqldbtype . Varchar , 50 ) ;
SP . Direction = Parameterdirection . Output ;
// Define the return value parameter of the process. After the process is executed, the return value of the process is assigned to paremeters named myreturn.
SP = Comm . Parameters . Add( "Myreturn" , Sqldbtype . Int ) ;
SP . Direction = Parameterdirection . Returnvalue ;
// Use sqldataadapter to automatically open and close the database, and execute the corresponding T-SQL statement or stored procedure
// If the stored procedure only performs related operations, such as cascade deletion or update, use the execute method of sqlcommand.
Sqldataadapter Da = New Sqldataadapter ( Comm ) ;
Dataset DS = New Dataset ( ) ;
Da . Fill ( DS ) ;
// After the stored procedure is executed, the output parameters are displayed.
String Myout = Comm. Parameters [ "@ Output" ] . Value . Tostring ( ) ;
// Print the output parameters:
Response . Write ( "Print output parameters :" + Myout ) ;
// Print the Stored Procedure Return Value
Myout = Comm . Parameters [ "Myreturn" ] . Value . Tostring ( ) ;
Response . Write ( "Stored Procedure return value :" + Myout ) ;
This . Datagrid1 . Datasource = DS ;
This . Datagrid1 . Databind ( ) ;
}
|
The simplest version of stored procedure:
Private Void SQL _jyh ( )
{
// The simplest way to use stored procedures as t-SQL statements. Syntax: exec process name parameter
Sqlconnection Conn = New Sqlconnection ( "Server =.; uid = sa; Pwd = 1234; database = China" ) ;
String SQL = "Execute proc_out 10, '12 '" ;
Sqlcommand Comm = New Sqlcommand ( SQL , Conn ) ;
// Use sqldataadapter to automatically open and close the database, and execute the corresponding T-SQL statement or stored procedure
// If the stored procedure only performs related operations, such as cascade deletion or update, use the execute method of sqlcommand.
Sqldataadapter Da = New Sqldataadapter ( Comm ) ;
Dataset DS = New Dataset ( ) ;
Da. Fill ( DS ) ;
// Bind data
This . Datagrid1 . Datasource = DS ;
This . Datagrid1. Databind ( ) ;
}
|
