Using system; using system.data; using system.collections; using system.enterprises; mespace sevcompant {//// /// DatabaseProcedure summary description.
/// The class created for all stored procedures of the associated database, and // best uses it as a global variable using /// public class databasepaseprocedure {private hashtable prochsh = new hashtable (); private hashtable createdProcHsh = new Hashtable (); private SqlConnection cnn = null; private string retParaName = "@ ReturnValue"; public string ReturnParaName {get {return this.retParaName;} set {this.retParaName = value;}} private const string ProcSql = " Select A.Name, B.Name, ColumnProperty (@ ProID, A.Name, 'Precision'), ISNULL (ColumnProperty (@ proid, a.name, 'isoutparam'), 0), isnull (ColumnProperty (@ proid, A .name, 'allownull' .xusertype "; public databaseprocedure (SqlConnection Sqlcnn) {// Get all related stored procedures for all this database this.cnn = SQLCNN; String cmdstr =" SELECT NAME, ID from sysobjects where type = 'p' and status> = 0 "; Sqlcommand cmd = new sqlcommand (cmdstr, this.cn); this.cn.open (); sqldataareader DR = cmd.executeReader Commandbehavior.CloseConnection; While (Dr.Read ()) {prochsh.add (dr [DR [0] .tostring (), DR [1] .tostring ());} Dr.close ();} // // / According to the stored procedure name, the corresponding command process is ///: The default parameter type direction is only: Input, Ouput, ReturnValue does not exist inputput /// but can be modified by yourself after the creation is complete. The following properties (== can be self-modified since the creation is complete, ==): /// parametername direction size precision scale isnullable SqldbType ///> Also added a direction == returnvalue, parametername = @ ReturnValue parameter / // If you want to modify this parameter, you can modify the returnparaname property before this or modify it after the creation is complete (but do this.
Unable to get the correct naming of this parameter from ReturnParaname: ParameterName) /// Recommended for the previous method /// /// Stored Procedure Name / // SqlCommand type public SqlCommand CreateCommand (string procedureName) {// determines named: procedure stored procedure exists if (! procHsh.Contains (procedureName)) throw new ArgumentException ( "does not exist in the database entitled [ " procedure "] stored procedure "); SqlCommand cmd = null; if) {cmd = new SqlCommand (procedureName) (createdProcHsh.Contains (procedureName!); cmd.CommandType = CommandType.StoredProcedure; // generate the database the parameter string procIdVal = procHsh [procedureName]; SqlCommand prCmd = new SqlCommand (DataBaseProcedure.ProcSql, this.cnn); prCmd.Parameters.Add ( "@ proID", procIdVal); this.cnn .Open (); DataMap dt = New DataMap (); SqlDataReader DR = cmd.executeReader (Commandbehavior.CloseConnection); while (Dr.Read ()) {// 0 Parameter 1 Data Type 2 Accuracy 3 Whether Output Parameter 4 can be Kong 5 decumeration 6 bytes SQLParameter Par = New SqlParameter (); par.ParameterName = DR [0] .tostring (); // select a.nam e, //b.name, // columnproperty (@ proid, a.name, 'precision'), //isnull( @proid ,a.name ,'isoutparam'), // isnull (ColumnProperty (@ ProID, A.Name, 'Allownull'), 0), //onlic (@proid ,a.name ,'scale') ,0) // from syscolumns a, systypes b where id = @ ProID and B.XUSERTYPE = a.XUSERTYPE PAR.SQLDBTYPE = DT.dotNetSqldbType (DR [1] .tostring ()); par.precision = convert.tobyte (DR [2] .tostring ()); par.direction = DR [3 ] .ToString () == "1"? ParameterDirection.output: parameterdirection.input;
Bool Nulb = DR [4] .tostring () == "1" true: false; par.set_isnullable (nulb); par.scale = convert.tobyte (DR [5] .tostring ()); par.size = Convert.TOINT32 (DR [6] .tostring ()); cmd.parameters.add (par);} Dr.close (); sqlparameter pret = new sqlparameter (); PRET.PARAMETERNAME = this.RetparaName; PRET.DIRECTION = ParameterDirection.ReturnValue; cmd.Parameters.Add (pret); this.createdProcHsh.Add (procedureName, cmd);} else {cmd = this.createdProcHsh [procedureName] as SqlCommand;} return cmd;}}}
