SQL stored procedures in C #

I. SQL statement for table creation:

Create Table [tree] (
[Node_id] [int] not null,
[Node_name] [varchar] (20) Collate chinese_prc_ci_as null,
[Pat_id] [int] Null,
[Url] [nvarchar] (50) Collate chinese_prc_ci_as null,
[Icon] [varchar] (20) Collate chinese_prc_ci_as null,
[Memo] [varchar] (30) Collate chinese_prc_ci_as null,
Constraint [tree_pk] primary key clustered
(
[Node_id]
) On [primary]
) On [primary]
Go

2. Create a stored procedure with input, output, and return value parameters:

Code

Create proc proc_out @ UUID int, @ output varchar (200) Output
As
-- Select result set
Select * from tree where node_id> @ uid
-- Assign values to output parameters
Set @ output = 'total records: '+ convert (varchar (10), (select count (*) from tree ))
-- Return is used to return a value to the stored procedure.
Return 200;
Go
 

3. Operate the stored procedure in C:

3.1 use SQL statements with Parameters

Code

Private void SQL _param ()
{

Sqlconnection conn = new sqlconnection ("Server =.; uid = sa; Pwd = sa; database = SMS ");

// The @ myid parameter is introduced in the SQL statement.
String SQL = "select * from tree 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 a value to the input parameter

// 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. dgd_student.datasource = Ds;
This. dgd_student.databind ();

}
 

3.2 Standard Edition for Stored Procedures

Code

Private void SQL _proc ()
{


Sqlconnection conn = new sqlconnection ("Server =.; uid = sa; Pwd = sa; database = SMS ");
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 = 4;

// 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. dgd_student.datasource = Ds;
This. dgd_student.databind ();



}

3.3 Use lite versions of stored procedures:

Code

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 = sa; database = SMS ");
String SQL = "execute proc_out 3, '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. dgd_student.datasource = Ds;
This. dgd_student.databind ();

}

Cases with multiple parameters
Create proc proc_out2 @ uid int, @ patid int, @ output varchar (200) Output

As

-- Select result set

Select * from tree where node_id> @ UID and pat_id = @ patid


-- Assign values to output parameters

Set @ output = 'total records: '+ convert (varchar (10), (select count (*) from tree ))

-- Return is used to return a value to the stored procedure.

Return 200;

Go


Private void more ()
{
Sqlconnection conn = new sqlconnection ("Server =.; uid = sa; Pwd = sa; database = SMS ");
String SQL = "proc_out2 ";
Sqlcommand cmd = new sqlcommand (SQL, Conn );
// Change the command execution type to the stored procedure mode. The default value is text.
Cmd. commandtype = commandtype. storedprocedure;

Cmd. Parameters. Add ("@ uid", sqldbtype. INT). value = 1;
Cmd. Parameters. Add ("@ patid", sqldbtype. INT). value = 1;
Cmd. Parameters. Add ("@ output", sqldbtype. varchar, 100 );
Cmd. Parameters ["@ output"]. Direction = parameterdirection. output;

// Cmd. Parameters. Add (New sqlparameter ("@ uid", sqldbtype. INT). value = 1; // "A1 **";
// Cmd. Parameters. Add (New sqlparameter ("@ patid", sqldbtype. INT). value = 1; // "A2 **";
// Sqlparameter Param = new sqlparameter ("@ output", sqldbtype. varchar, 88 );
// Param. Direction = parameterdirection. output;
// Cmd. Parameters. Add (PARAM );

// Cmd. executenonquery ();
Dataset DS = new dataset ();
Sqldataadapter da = new sqldataadapter (CMD );
Da. Fill (DS );
String rtnstr = cmd. Parameters ["@ output"]. value. tostring ();
Response. Write ("print output parameters:" + rtnstr );
This. dgd_student.datasource = Ds;
This. dgd_student.databind ();
}

Source: http://www.cnblogs.com/jayleke/archive/2010/07/10/1774758.html