C # ADO. Net Stored Procedure

In the ADO environment, the common practice of calling a stored procedure to query data is as follows:
1. Create a connection command object
2. Open the connection and assign the parameter name, data type, and value to the command.
3. Execute Command object
4. Return the recordset object to the client.
In this way, each call to a stored procedure creates a parameters Object Based on the Data Type of the parameters in the stored procedure.
For example, the stored procedure requires two parameters: @ ID int and @ 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 the parameter
CMD ("@ State") = 1
CMD ("@ wheret") = "2"
Each time a stored procedure is called, all parameters of the stored procedure must be manually added, and the Data Type of parameters and the information consistency of parameters in the stored procedure must be ensured with your own mental power.
Command. the parameters object has a refresh method. When this method is used, it reads the names and Data Types of all the parameters required by the current command object, using this method, you can write a common function that calls all stored procedures. The present function completes the stored procedure of a returned result set. It is simple and can be refined as needed.

'Debugging passed in visualbasic6.0.
Function getrsbypro (strconnstring as string, strproname as string, arjparameter () as string)
'Return the queried record set
'Strconnstring
'Strproname stored procedure name
Array required by the 'arjparameter () Stored Procedure
On Error goto errmsg
'Create an 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 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 ())

You can create a common method to call the stored procedure in the. NET development environment using the same method.
In ADO. net, whether oledbcommand. the parameters object is also sqlcommand. the parameters object does not have the refresh method to read the parameter information of the stored procedure ,. NET provides a static deriveparameters method in the oledbcommandbuilder class to implement the same functions.
Description of deriveparameters in. Net SDK
"Fill in the parameters set of the specified sqlcommand object with the parameter information of 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 helped set the information in the stored procedure.
Implements the execution of any stored procedure to return a specific function of a DataSet objectCode
File Name: testsqlaccess. CS
// Debug through 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
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 Execute Command object to return Dataset

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

# Endregion: Execute Command object to return Dataset