C # simplify the execution of Stored Procedures

The following method is found in actual development and can greatly simplify the code for calling the stored procedure.

First, let's take a look at the general process of calling the stored procedure in C:
1. Open the database connection SqlConnection;
2. Generate a SqlCommand;
3. Fill in parameters to the command object;
4. Execute the stored procedure;
5. Close the connection;
6. other operations.

I will focus on simplifying Step 1. When calling a stored procedure, you only need to pass the name of the stored procedure and the corresponding parameter values. Call example:
DbAccess. run ("p_am_deleteFile", new object [] {LoginId, Request. UserHostAddress, fileId });

Because two values are required during parameter filling, one is the parameter name and the other is the parameter value. Parameter values are imported from outside, so you do not need to consider them. The parameter name is related to the stored procedure and should be determined by the stored procedure name instead of being written to each call. If you can save the stored procedure parameters to a global location, you only need to be able to index the stored procedure according to its name when calling the stored procedure. In actual implementation, I save the information in the database access component and use the name/value pair method. The Code is as follows:
Public class InfoTable: NameObjectCollectionBase
{
Public object this [string key]
{
Get
{
Return (this. BaseGet (key ));
}
Set
{
This. BaseSet (key, value );
}
}
}
Protected static InfoTable procInfoTable = new InfoTable ();
Public static InfoTable ProcInfoTable
{
Get
{
Return procInfoTable;
}
}

In this way, you only need to check the table to know the parameter name of the stored procedure when calling the stored procedure. The implementation code is as follows:
Public DataTable run (string procName, object [] parms, ref int retValue)
{
String [] paramInfo = (string []) (procInfoTable [procName]);
If (paramInfo = null)
{
ErrorInfo. setErrorInfo ("not obtained" + procName +! ");
Return null;
}

Bool bOpened = (dbConn. State = ConnectionState. Open );
If (! BOpened &&! Connect ())
{
Return null;
}

DataSet ds = new DataSet ();
Try
{
SqlCommand cmd = new SqlCommand (procName, dbConn );
Cmd. CommandType = CommandType. StoredProcedure;

For (int I = 0; I <parms. Length & I <paramInfo. Length; ++ I)
{
Cmd. Parameters. Add (new SqlParameter (paramInfo [I], parms [I]);
}

SqlParameter parmsr = new SqlParameter ("return", SqlDbType. Int );
Parmsr. Direction = ParameterDirection. ReturnValue;
Cmd. Parameters. Add (parmsr );

SqlDataAdapter adp = new SqlDataAdapter (cmd );
Adp. Fill (ds );
RetValue = (int) (cmd. Parameters ["return"]. Value );
}
Catch (Exception ex)
{
ErrorInfo. setErrorInfo (ex. Message );

RetValue =-1;
}

If (! BOpened)
Close ();

If (ds. Tables. Count> 0)
Return ds. Tables [0];
Else
Return null;
}

We can see that the parameter list of each stored procedure is stored as a string []. The next step is to fill in many stored procedure parameters in the system into the ProcInfoTable table. The database I use is SQL Server 2000. Here is a stored procedure to solve this annoying problem:
Create PROCEDURE dbo. p_am_procInfo
(
@ ProcName t_str64 -- Name of the stored procedure
)
AS
Begin
Set nocount on

If @ procName = ''begin
Select name as procName
From sysobjects
Where substring (sysobjects. name, 1, 5) = 'P _ am _'
End
Else begin
Select
Syscolumns. name as paramName
From sysobjects, syscolumns
Where sysobjects. id = syscolumns. id
And sysobjects. name = @ procName
Order by colid
End
 
End

This stored procedure has two functions. When the name of a stored procedure is not passed, the stored procedure returns the names of all stored procedures starting with "p_am; after a stored procedure name is input, the stored procedure returns the parameter list of the stored procedure. In this way, the stored procedure parameter list in the system can be retrieved from the beginning of the program and saved to the ProcInfoTable attribute of the database access component. The Code is as follows:
Span. DBAccess dbAccess = new span. DBAccess ();

//
// Construct a parameter table for the stored procedure
//
Span. DBAccess. ProcInfoTable ["p_am_procInfo"] = new string [] {"@ procName "};

//
// Obtain the list of other stored procedures
//
DataTable dt = dbAccess. run ("p_am_procInfo", new object [] {""});
If (dt = null | dt. Rows. Count <= 0)
{
Return;
}

//
// Obtain parameters for other stored procedures
//
Foreach (DataRow dr in dt. Rows)
{
DataTable dtParams = dbAccess. run ("p_am_procInfo", new object [] {dr ["procName"]});
If (dtParams! = Null)
{
String [] paramInfo = new string [dtParams. Rows. Count];
For (int I = 0; I <dtParams. Rows. Count; ++ I)
ParamInfo [I] = dtParams. Rows [I] ["paramName"]. ToString ();

Span. DBAccess. ProcInfoTable [dr ["procName"]. ToString ()] = paramInfo;
}
}

So far, all technical details have been introduced. In addition, several interface functions of the database access object are also provided:

// Open or close the database connection
Public bool connect (string strConn)
Public bool connect ()
Public bool close ()

// Execute the SQL command (only one int is returned)
Public int exec (string procName, object [] parms)
Public int exec (string SQL)

// Run (return a DataTable)
Public DataTable run (string procName, object [] parms, ref int retValue)
Public DataTable run (string procName, object [] parms)
Public DataTable run (string SQL)

// Query by PAGE (page number starts from 1 and returns a DataTable)
Public DataTable pageQuery
(
String selectCmd,
Int pageSize,
Int pageNumber
)