ADO.NET calls stored procedures

One: Execute a stored procedure without return parameters (Input)
1: First write a stored procedure in the database, such as creating an addUser stored procedure.
Create Proc addUser
@ID int,
@Name varchar(20),
@Sex varchar(20)
As
Insert Into Users Values( @ID, @Name,@Sex )

2: Create SqlCommand object, And initialize the SqlCommand object such as:
SqlCommand cmd = new SqlCommand( );
cmd.CommandText = "addUser"; // Specify which stored procedure to call
cmd.CommandType = CommandType.StoredProcedure; // Specify the Sql command type to be a stored procedure, The default is Sql statement.
cmd.Connection = con; // Set connection

3: Add stored procedure parameters to the SqlCommand object
SqlParameter param = new SqlParameter( ); // Define a parameter object
param.ParameterName = "@ID"; // Stored procedure parameter name
param.Value = txtID.Text.Trim(); // The value of this parameter
cmd.Parameters.Add( param ); // SqlCommand object adds this parameter object

param = new SqlParameter( "@ Name", txtName.Text.Trim() ); // Abbreviation
cmd.Parameters.Add( param );

4: The SqlCommand object calls the function that executes Sql. For example:
cmd.ExecuteNonQuery();

Two: Execute a stored procedure with return parameters (Output)
1: First write a stored procedure in the database, such as creating a queryUser stored procedure.
alter Proc queryUser
@ID int,
@Suc varchar(10) output
As
select @Suc= 'false'
if exists( Select * From users where u_id = @ID )
select @Suc = 'success'

2: Create a SqlCommand object and initialize the SqlCommand object such as:
SqlCommand cmd = new SqlCommand( );
cmd.CommandText = "queryUser"; //Determine which stored procedure to call
cmd.CommandType = CommandType.StoredProcedure; //Determine The Sql command type is a stored procedure, and the default is Sql statement.
cmd.Connection = con; // Set the connection

3: Add stored procedure parameters to the SqlCommand object
SqlParameter param1 = new SqlParameter( "@ID", txtID.Text ); // Add input parameters
cmd.Parameters. Add( param1 );

SqlParameter param2 = new SqlParameter(); // Add output parameter
param2.ParameterName = "@Suc"; // Name
param2.SqlDbType = SqlDbType.VarChar; // Sql type of output parameter
param2.Size = 10; //Sql type size of the output parameter
param2.Direction = ParameterDirection.Output; //Specify the parameter object as the output parameter type
cmd.Parameters.Add( param2 );

4: SqlCommand object call Execute Sql function. Such as:
cmd.ExecuteNonQuery();
MessageBox.Show( param2.Value.ToString() ); // Output the value of the output parameter

Example of a stored procedure for input parameters:
try
{
SqlCommand cmd = new SqlCommand ();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "addUser";

SqlParameter param = new SqlParameter( );
param.ParameterName = "@ID";
param .Value = txtID.Text.Trim();
cmd.Parameters.Add( param );

param = new SqlParameter( "@Name", txtName.Text.Trim() );
cmd.Parameters.Add( param );

param = new SqlParameter();
param.ParameterName = "@Sex";
param.Value = txtSex.Text.Trim();
cmd.Parameters.Add( param );

//da. InsertCommand = cmd;

if ( cmd.ExecuteNonQuery() == 1 )
{
MessageBox.Show( "Added successfully" );
}
else
{
MessageBox.Show("Failed");
}
}
catch( SqlException ex )
{
MessageBox.Show( ex.Message );
}

Example of stored procedure with output parameters:
try
{
SqlCommand cmd = new SqlCommand( );
cmd.CommandText = "queryUser ";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;

SqlParameter param1 = new SqlParameter( "@ID", txtID.Text );
cmd.Parameters.Add( param1 );

SqlParameter param2 = new SqlParameter();
param2.ParameterName = "@Suc";
param2.SqlDbType = SqlDbType.VarChar;
param2.Size = 10;
param2.Direction = ParameterDirection.Output;
cmd.Parameters.Add( param2 ) ;

cmd.ExecuteNonQuery();

MessageBox.Show( param1.Value.ToString() );
MessageBox.Show( param2.Value.ToString() );

}
catch( SqlException ex )
{
MessageBox.Show( ex.Message );
}

The method to get the return value in ado.net is (c#):
--------------------- ---------------------------------------
SqlConnection dbconn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand("sp_uptmp",dbconn);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter tmpName = cmd.Parameters.Add("@tmpName",SqlDbType.VarChar);
SqlParameter srcPos = _cmd. Parameters .Add("@srcPos",SqlDbType.VarChar);
SqlParameter rtnval = cmd.Parameters.Add("rval",SqlDbType.Int);

tmpName.Direction = ParameterDirection.Input;
srcPos.Direction = ParameterDirection.Input ;
rtnval.Direction = ParameterDirection.ReturnValue;

tmpName.Value = "";
srcPos.Value = "";
dbconn.Open();
cmd.ExecuteNonQuery();
dbconn.Close();

tmpid = (int)rtnval.Value; // Here is the return value

Assume there is a stored procedure as follows:
---------------------------------- ----------

CREATE proc sp_uptmp @tmpName varchar(50),@srcPos varchar(255)
as

Begin TRAN
insert into t_template values(@tmpName,@srcPos)
COMMIT

return isnull(@@identity,0)
GO

---------------------------------------- ------------------------
The method to get the return value in ado.net is (c#):
--------- -------------------------------------------------- -
SqlConnection dbconn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand("sp_uptmp",dbconn);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter tmpName = cmd.Parameters.Add("@tmpName", SqlDbType.VarChar);
SqlParameter srcPos = _cmd.Parameters.Add("@srcPos",SqlDbType.VarChar);
SqlParameter rtnval = cmd.Parameters.Add("rval",SqlDbType.Int);

tmpName.Direction = ParameterDirection.Input;
srcPos.Direction = ParameterDirection.Input;
rtnval.Direction = ParameterDirection.ReturnValue;

tmpName.Value = "";
srcPos.Value = "";
dbconn.Open();
cmd.ExecuteNonQuery ();
dbconn.Close();

tmpid = (int)rtnval.Value; //This is the return value

In the ADO environment, the common practice when calling a stored procedure to query data is:
1 Create Connection Command object
2 Open the connection, assign the parameter name, data type, and value to the Command
3 Execute the Command object
4 Return the Recordset object to the client
In this way, each time the stored procedure is called, the parameters in the stored procedure must be followed Data type to create Parameters object
For example, if a stored procedure requires two parameters @ID int, @Name varchar(10), you need to
' create parameters
cmd.Parameters.Append cmd.CreateParameter("@ID",adInteger,adParamInput,4)
cmd.Parameters.Append cmd.CreateParameter("@Name",adVarChar,adParamInput,10)
'Assign a value to the parameters
cmd("@State") = 1
cmd("@WhereT")="2"
Every When calling a stored procedure, you must manually add all parameters of the stored procedure, and use your own brainpower to ensure that the data types of the parameters are consistent with the parameter information in the stored procedure.
The Command.Parameters object has a Refresh method. The function of this method is to read the names and data types of all parameters required by the current Command object. Using this method, you can write a common function that calls all stored procedures. Now this function is completed A general function for a stored procedure that returns a result set. It's simple and can be refined as needed.

‘Debugging passed in Visual Basic 6.0.
Function GetRsByPro(strConnString As String, strProName As String, arjParameter() As String)
' Returns the queried record set
' strConnString data connection string
' strProName stored procedure name
' arjParameter() array required by the stored procedure
On Error GoTo errMsg
'Create ADO object
Dim Cmd As New Command
' ASP Con = Server.CreateObject("ADODB.Connection")
Dim Con As New Connection
' ASP Set Cmd = Server.CreateObject("ADODB.Command")
Dim Rs As New Recordset
' ASP Set rs = Server.CreateObject("ADODB.Recordset")

'Open the database
Con.Open strConnString
Set Cmd.ActiveConnection = Con
Cmd.Commandtype = adCmdStoredProc
Cmd.Parameters. Refresh
If UBound(arjParameter) Cmd.Parameters.Count Then
Debug.Print "The number of parameters is incorrect"
Exit Function
End If

'Assign values ​​to stored procedure parameters
For i = 0 To Cmd.Parameters .Count - 1
Cmd.Parameters(i).Value = arjParameter(i)
Next

'Set Recordset object
Rs.CursorType = 3
Rs.LockType = 3
Rs.CursorLocation = 3
Set Rs.Source = Cmd
Rs.Open

'Return the result set
Set GetRsByPro = Rs

'Close the data source
Con.Close
Set Con = Nothing
errMsg:
Debug.Print Err.Description
End Function

'Call Demo
Dim Rs As New Recordset
StrConnString=””
StrProName=”pro_GetAllUser”
Dim arjParameter(1)
arjParameter(0)=”1”
arjParameter(1)=”Shandong”
Set Rs= GetRsByPro(strConnString, strProName, arjParameter())

Using the same method, you can also create a general method to call the stored procedure in the .NET development environment.
In ADO.NET, neither the OleDbCommand.Parameters object nor the SqlCommand.Parameters object has a Refresh method to read the parameter information of the stored procedure. .NET provides a DeriveParameters static method in the OleDbCommandBuilder class to achieve the same function.
The description of DeriveParameters in the .NET SDK
"Use the parameter information of the stored procedure specified in SqlCommand to populate the Parameters collection of the specified SqlCommand object."

SqlConnection Conn=new SqlConnection(cnString);
Conn.Open();
SqlCommand Comm=new SqlCommand();
Comm.Connection =conn;
Comm.CommandType =CommandType.StoredProcedure;
Comm.CommandText =proName;
SqlCommandBuilder.DeriveParameters(comm);
//After this method, the SqlParameters object of the SqlCommand object has helped determine the information in the stored procedure
Realize the specific function code to execute any stored procedure and return a DataSet object
File name: TestSqlAccess. cs
// Debug in vs.net through
using System;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Collections;

namespace Erp
{
public sealed class TestSqlAccess
{
#region Get the stored procedure parameter collection
public static SqlParameter [] getParameters(string cnString,string proName)
{
SqlConnection conn=new SqlConnection(cnString);
conn.Open( );
SqlCommand comm=new SqlCommand();
comm.Connection =conn;
comm.CommandType =CommandType.StoredProcedure;
comm.CommandText =proName;

SqlCommandBuilder.DeriveParameters(comm);
SqlParameter [] arPrm =new SqlParameter[comm.Parameters.Count];
for (int i=0;i{
arPrm[i]=new SqlParameter();
arPrm[i].SqlDbType =comm. Parameters[i].SqlDbType;
arPrm[i].ParameterName=comm.Parameters[i].ParameterName;
arPrm[i].Size =comm.Parameters[i].Size;
}
return arPrm;
}
#endregion

#region Execute the Command object and return the DataSet

/////You can call the SqlHelper class provided by Microsoft...

#endregion Execute the Command object and return the DataSet

Use DataReader to return the row sum Parameters

　You can use a DataReader object to return a read-only forward-only data stream. The information contained in the DataReader can come from a stored procedure. This example uses a DataReader object to run a stored procedure with input and output parameters, and then iterates through the returned records to view the return parameters.

　1. Create the following stored procedure on the server running Microsoft SQL Server: Create Procedure TestProcedure

　(

　@au_idIN varchar (11),

　@numTitlesOUT Integer OUTPUT

　)

　AS

　select A. au_fname, A.au_lname, T.title

　from authors as A join titleauthor as TA on

　A.au_id=TA.au_id

　join titles as T

　on T.title_id=TA.title_id

　where A. au_id=@au_idIN

　set @numTitlesOUT = @@Rowcount

　 return (5)

　　2. Create a new Visual C# .NET Windows application project.

　　3. Use the using statement for the System and System.Data namespaces, so that there is no need to qualify declarations in these namespaces in the subsequent code. Add this code to the top of the "Forms" code module. Be sure to copy only the code that corresponds to the provider you selected. SQL client using System.Data.SqlClient;

　　OLE DB data provider using System.Data.OleDb;

　　4. Replace the code in the private Form_Load event with the following code: SQL client SqlConnection PubsConn = new SqlConnection

　　( "Data Source=server;integrated " +

　"Security=sspi;initial catalog=pubs;");

　 SqlCommand testCMD = new SqlCommand

　("TestProcedure", PubsConn);

　testCMD.CommandType = CommandType.StoredProcedure ;

　 SqlParameter RetVal = testCMD.Parameters.Add

　 ("RetVal", SqlDbType.Int);

　 RetVal.Direction = ParameterDirection.ReturnValue;

　 SqlParameter IdIn = testCMD.Parameters.Add

　("@au_idIN" , SqlDbType.VarChar, 11);

IdIn.Direction = ParameterDirection.Input;

SqlParameter NumTitles = testCMD.Parameters.Add

Titles.Direction = ParameterDirection .Output;

IdIn.Value = "213-46-8915";

PubsConn.Open(); ;

.WriteLine("Number of Rows:" + NumTitles.Value );

　 Console.WriteLine("Return Value:" + RetVal.Value);

　OLE DB data provider OleDbConnection PubsConn = new OleDbConnection

　("Provider= SQLOLEDB;Data Source=server;" +

　　"integrated Security=sspi;initial catalog=pubs;");

　OleDbCommand testCMD = new OleDbCommand

　("TestProcedure", PubsConn);

　testCMD.CommandType = CommandType. StoredProcedure;

　OleDbParameter RetVal = testCMD.Parameters.Add

　　("RetVal", OleDbType.Integer);RetVal.Direction = ParameterDirection.ReturnValue;

　　OleDbParameter IdIn = testCMD.Parameters.Add

　　("@au_idIN", OleDbType.VarChar, 11);

　　IdIn.Direction = ParameterDirection.Input;

　　OleDbParameter NumTitles = testCMD.Parameters.Add

　　("@numtitlesout", OleDbType.VarChar, 11);

　　NumTitles.Direction = ParameterDirection.Output;

　　IdIn.Value = "213-46-8915";

　　PubsConn.Open();

　　OleDbDataReader myReader = testCMD.ExecuteReader();

　　Console.WriteLine ("Book Titles for this Author:");

　　while (myReader.Read())

　　{

　　Console.WriteLine ("{0}", myReader.GetString (2));

　　};

　　myReader.Close();

　　Console.WriteLine("Number of Rows:" + NumTitles.Value );

　　Console.WriteLine("Return Value:" + RetVal.Value);

　　5. 修改 Connection 对象的连接字符串，以便指向运行 SQL Server 的计算机。

　　6. 运行此代码。注意，DataReader 检索记录并返回参数值。您可以使用 DataReader 对象的 Read 方法遍历返回的记录。

　　输出窗口显示两本书的标题、返回值 5 和输出参数，其中包含记录的数目 (2)。注意，您必须关闭代码中的 DataReader 才能看到参数值。另请注意，如果关闭了 DataReader，则不必为了查看返回参数而遍历所有记录。