This article is a spanzhang original, its blog address is: http://blog.9cbs.net/spanzhang. Please indicate the source, thank you! ! The following method is that I have explored in the actual development, which can a large extent simplify the code of calling the stored procedure. First, look at the general process of the C # call the stored procedure: 1. Open the database to connect SQLConnection; 2, generate a SQLCommand; 3, fill the parameters to the command object; 4, perform the stored procedure; 5, turn off the connection; 6, other operations. I am mainly simplified here to simplify the 3rd step, and ultimately you only need to pass the name of the stored procedure and the corresponding parameter values when the stored procedure is called. Calling examples are as follows: dbaccess.run ("p_am_deletefile", new object [] {loginid, refress.userhostaddress, fileId}); because two values must be two values during the fill parameters, one is the name of the parameter, one is the value of the parameter . The parameter value is incorporated by external, no consideration; and the parameter name is related to the stored procedure, it should be able to determine by the stored procedure name without having to write it once. For this problem, if you can save the parameters of the stored procedure to a global place, you can use it according to the name of the stored procedure when calling the stored procedure. When I implement, I am saving this information in the database access component, using the name / value. 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;}} This time you can know the parameter name of the stored procedure when you actually call the stored procedure. The code is as follows: Public DataTable Run (String Procname, Object [] PARMS, REF INT RETVALUE) {string [] paraminfo = (string []); if (paraminfo == null) {ErrorInfo.seterrorInfo "Do not get" procname "parameters!"); 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 Sqldataadapter ADP = New SqlDataAdapter (CMD); ADP.FILL (DS); RetValue = (int) ("Return");} catch (exception ex) {ErrorInfo.seterrorInfo (EX.MESSAGE); RetValue = -1;} IF (! bopened) close (); If (ds.tables.count> 0) Return DS.Tables [0]; Else Return NULL;} It can be seen that the parameter list of each stored procedure is stored for a string []. The next job is to populate the parameters of many stored procedures in the system into the table procinfotable. The database I use is SQL Server 2000. Here is a stored procedure to solve this annoying question: create procedure dbo.p_am_procinfo (@ProcName T_STR64 - stored procedure name) Asbegin 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 the stored procedure is not delivered, the stored procedure returns all the names of the stored procedure starting with "p_am_"; in the past After the stored procedure name, the stored procedure returns the parameter list of the stored procedure. In this way, we can take the stored procedure parameters in the system in the program and save it to the procinfotable property of the database access component. The specific code is as follows: span.dbaccess dbaccess = new span.dbaccess (); //// Configuration Table //span.dbaccess.procinfotable ["p_am_procinfo"] = new string [] {"@ procname"}; /// Other stored procedure list // datatable dt = dbaccess.run ("p_am_procinfo", new object [] {"}); if (dt == null || dt.rows.count <= 0) { Return;} /// The parameter table of other stored procedures // foreach (DATAROW DR IN DT.ROWS) {dataable 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
