Use stored procedures in C #

This article takes SQL Server2000 as an example. The example database is china and the table is test to describe how to use SQL stored procedures in C.

I. 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)

2. 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

3. Operate the stored procedure in C:

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

}

3.2 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 ();

 

}

3.3 Use lite versions of stored procedures:

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 ();

}

Iv. Summary and understanding:

The sqlcommand class provides the ability to execute T-SQL statements and stored procedures. It can execute not only one SQL statement, but also a group of SQL statements, such as creating tables, for SQL Server, as long as a group of statements that can be executed in the query analyzer can also be executed well through sqlcommand. The default sqlcommand execution method is to execute the T-SQL statement, that is, Comm. commandtype = commandtype. Text. When executing a stored procedure, you can use the exec procedure name parameter to execute the stored procedure as a t-SQL statement, or change the sqlcommand execution method to the execution procedure, comm. commandtype = commandtype. storedprocedure; the main difference between the two storage procedure execution methods is that the former is difficult to obtain the output parameters and return values; the latter is comprehensive, it is also a comprehensive policy defined by Microsoft specifically for executing stored procedures.

(Source: www.zuoyefeng.com)

 

Trackback: http://tb.blog.csdn.net/TrackBack.aspx? Postid = 1623365