Ado. NET calling stored procedures

One: Executes a stored procedure without a return parameter (Input)
1: First write a stored procedure in the database, such as create a 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 initial SqlCommand object such as:
SqlCommand cmd = new SqlCommand ();
Cmd.commandtext = "AddUser"; Make a call to which stored procedure
Cmd.commandtype = CommandType.StoredProcedure; The SQL command type is a stored procedure, which is the default SQL statement.
Cmd. Connection = con; Set connection

3: Add stored procedure parameters to the SqlCommand object
SqlParameter param = new SqlParameter (); Defining 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 to add the Parameter object

param = new SqlParameter ("@Name", TxtName.Text.Trim ()); Shorthand method
Cmd. Parameters.Add (param);

The 4:sqlcommand object invokes the function that executes the SQL. Such as:
Cmd. ExecuteNonQuery ();

Second: Execute a stored procedure with return parameters (Output)
1: First write a stored procedure in the database, such as create a queryuser stored procedure.
Alter PROC Queryuser
@ID int,
@Suc varchar () output
As
Select @Suc = ' false '
if exists (Select * from users where u_id = @ID)
Select @Suc = ' Success '

2: Create SqlCommand object, and initial SqlCommand object such as:
SqlCommand cmd = new SqlCommand ();
Cmd.commandtext = "Queryuser"; Make a call to which stored procedure
Cmd.commandtype = CommandType.StoredProcedure; The SQL command type is a stored procedure, which is the default SQL statement.
Cmd. Connection = con; Set connection

3: Add stored procedure parameters to the SqlCommand object
SqlParameter param1 = new SqlParameter ("@ID", Txtid.text); adding input parameters
Cmd. Parameters.Add (param1);

SqlParameter param2 = new SqlParameter (); Adding output parameters
Param2. ParameterName = "@Suc"; Name
Param2. SqlDbType = SqlDbType.VarChar; SQL type of output parameter
Param2. size = 10;//of SQL type for output parameter
Param2. Direction = ParameterDirection.Output; Specifies that the parameter object is an output parameter type
Cmd. Parameters.Add (PARAM2);

The 4:sqlcommand object invokes the function that executes the SQL. Such as:
Cmd. ExecuteNonQuery ();
MessageBox.Show (param2. Value.tostring ()); Value of output output parameter

Examples of stored procedures that 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 ("add success");
}
Else
{
MessageBox.Show ("failure");
}
}
catch (SqlException ex)
{
MessageBox.Show (ex. Message);
}

Examples of stored procedures for 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 way to get the return value in ADO 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; The return value is here

Suppose there are stored procedures as follows:
---------------------------------------------

CREATE proc sp_uptmp @tmpName varchar ($), @srcPos varchar (255)
As

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

Return IsNull (@ @identity, 0)
GO

------------------------------------------------------------
The way to get the return value in ADO 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; The return value is here

In an ADO environment, the general practice of calling stored procedures to query data is:
1 Creating a connection command object
2 Open connection, give command parameter name, data type, value
3 Execute Command object
4 return to the Recordset object to the client
This is done every time a stored procedure is called to create a parameters object according to the data type of the parameter in the stored procedure
For example, a stored procedure requires two parameters @ID int, @Name varchar (10).
' Create parameters
Cmd. Parameters.Append cmd. CreateParameter ("@ID", adinteger,adparaminput,4)
Cmd. Parameters.Append cmd. CreateParameter ("@Name", advarchar,adparaminput,10)
' Assign a value to a parameter
CMD ("@State") = 1
CMD ("@WhereT") = "2"
Each call to a stored procedure is to manually add all the parameters of this stored procedure, with its own mind to guarantee the data type of the parameter and the consistency of the information in the stored procedure parameters.
The Command.parameters object has a Refresh method that reads the names and data types of all the parameters required by the current command object, which can be written as a common function that invokes all stored procedures, and this function completes a general letter of the stored procedure that returns the result set. Number. Very simple can be refined as needed.

' In VisualBasic6.0 debug passed.
Function Getrsbypro (strconnstring As String, Strproname as String, Arjparameter () as String)
' Return a query's recordset
' Strconnstring data connection string
' Strproname Stored procedure name
The array required by the ' arjparameter () 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 database
Con.open strconnstring
Set cmd.activeconnection = Con
Cmd.commandtype = adCmdStoredProc
Cmd.Parameters.Refresh
If UBound (arjparameter) <> Cmd.Parameters.Count Then
Debug.Print "Wrong Number of parameters"
Exit Function
End If

' Assigning values to stored procedure parameters
For i = 0 to Cmd.parameters.count-1
Cmd.parameters (i). Value = Arjparameter (i)
Next

' Set the Recordset object
Rs.cursortype = 3
Rs.locktype = 3
Rs.cursorlocation = 3
Set Rs.source = CMD
Rs.Open

' Return result set
Set Getrsbypro = Rs

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

' Invoke 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 ())

In the same way. NET development environment can also establish a common method of calling stored procedures.
In ADO, neither the Oledbcommand.parameters object nor the Sqlcommand.parameters object has the Refresh method to read the parameter information of the stored procedure, and. NET provides a deri in the OleDbCommandBuilder class. Veparameters static methods can achieve the same functionality.
Description of DeriveParameters in the. NET SDK
Populates the Parameters collection of the specified SqlCommand object with parameter information for the stored procedure specified in SqlCommand. ”

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 already helped to fix the information in the stored procedure.
Implementation executes any stored procedure returns the specific function code of a DataSet object
File name: TestSqlAccess.cs
Debug through in Vs.net
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 a collection of stored procedure parameters
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<comm. Parameters.count;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 execution Command object returns a dataset

You can call the SqlHelper class provided by Microsoft.

#endregion execution Command object returns a dataset

Returning rows and parameters using DataReader

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

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

(

@au_idIN varchar (11),

@numTitlesOUT Integer OUTPUT

)

As

Select A.au_fname, A.au_lname, T.title

From authors as A joins 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 statements are used for the System and System.Data namespaces, so you do not need to qualify declarations in these namespaces in subsequent code. Add this code to the top of the form code module. Make sure that you copy only the code that corresponds to the provider that 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

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

NumTitles.Direction = ParameterDirection.Output;

Idin.value = "213-46-8915";

Pubsconn.open ();

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

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. Modify the connection string for the Connection object to point to the computer running SQL Server.

6. Run this code. Note that DataReader retrieves the record and returns the parameter value. You can use the Read method of the DataReader object to traverse the returned records.

The Output window shows the title of the two books, the return value 5, and the output parameters, which contain the number of records (2). Note that you must close DataReader in the code to see the parameter values. Also note that if DataReader is turned off, you do not have to traverse all records in order to view the return parameters.

