In the ADO environment, the regular approach to the stored procedure query data is: 1 Create a Connection Command Object 2 to open the connection, assign the name of the parameter, data type, and value 3 execute the Command object 4 to the RecordSet object to hand it to the RecordSet object. Each time you call a stored procedure, you must create parameter objects according to the data type of the parameters during the stored procedure. For example, the stored procedure requires two parameters @ID int, @ name varchar (10) needs' Creating parameters cmd.parameters.Append cmd.createParameter "@ID", adINteger, adparaminput, 4) cmd.parameters.Append cmd.createParameter ("@ name", advarchar, adparaminput, 10) 'gives the parameter assignment CMD ("@ State") = 1 cmd ("@ WHERET" Every parameter of this stored procedure is manually added each time the stored procedure is sent, and the data type of the parameters and the consistency of the information during the stored procedure are used. Command.Parameters object has a refresh method. When this method is used, read the names and data types of all parameters required by the current Command object. You can write into a shared function that calls all stored procedures. This function is complete. A universal function of the stored procedure for returning the result set. It is very simple to refine it as needed.
'Debugging in VisualBasic6.0. Function GetRsByPro (strConnString As String, strProName As String, arjParameter () As String) 'record set returned query' strConnString data connection string 'strProName stored procedure name' arjParameter () stored procedure requires an array On Error GoTo errMsg 'Create ADO Objects 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 "parameter Number of "exit function endiff" assigns for storage process parameters for i = 0 to cmd.parameters.count - 1 cmd.Parameters (i) .Value = arjParameter (i) Next 'Setting RECORDSET object rs.cursortype = 3 RS .LockType = 3 rs.cursorlocation = 3 set rs.source = cmd rs.open 'Return Results Set GetRSB yPro = Rs 'Close data source Con.Close Set Con = NothingerrMsg: Debug.Print Err.DescriptionEnd Function' call DemoDim Rs As New RecordsetStrConnString = "" StrProName = "pro_GetAllUser" Dim arjParameter (1) arjParameter (0) = "1" ArjParameter (1) = "Shandong" SET RS = getrsbyPro (StrConnString, Stroname, ArjParameter ())
Use the same method to create a general method to call the stored procedure in the .NET development environment. Regardless of the OLEDBCommand.Parameters object or the SQLCommand.Parameters object, there is no refresh method to read the parameter information of the stored procedure, .NET provides a DeriveParameters static method in the OLEDBCommandbuilder class to implement the same functionality. Description of DeriveParameters in .NET SDK "Use the parameter information of the stored procedure specified in SQLCommand, 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); // this method after the object SqlCommand object SqlParameters have helped set a stored procedure Information Implementation Execution Arbitrary Stored Procedure Returns a specific function code file name for a DataSet object: Testsqlaccess.cs // In vs.net debugging via Using System; use system.data; use system.data.sqlclient; Using system.data.oledb; using system.collections;
namespace Erp {public sealed class TestSqlAccess {#region acquisition procedure stored set of 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 #Region Execute Command Object Returns DataSet / You can call Microsoft's SQLHELPER class .. #ndregion Execute a Command object Return to DataSet
