/// /// Provides an operational service for the database: execution of the SQL statement, the memory process call /// public abstract class sqlhelper {private static void attachparameters (SQLCommand Command, Sqlparameter [] CommandParameters {FOREACH (SQLParameter P in CommandParameters) {if (p.direction == parameterDirection.inputoutput) && (p.value == null) {p.value = dbnull.Value;} command.Parameters.Add (p); }
Private static void assignparametervalues (Sqlparameter [] CommandParameters, Object [] parametervalues) {if ((CommandParameters == Null) || (parametervalues == null) {return;}
IF (CommandParameters.length! = parametervalues.length) {throw new argumentException ("Parameter Count Does Not Match Parameter Value Count.");}
For (int i = 0, j = commandparameters.lendth; i
private static SQLServer PrepareCommand (SqlCommand cmd, CommandType cmdType, string cmdText, SqlParameter [] intCmdParms, SqlParameter [] outCmdParms) {SQLServer sqlServer = SQLServer.Instance (); cmd.Connection = sqlServer.Connection; cmd.CommandText = cmdText; cmd. CommandType = cmdtype;
if (! intCmdParms = null) {foreach (SqlParameter parm in intCmdParms) cmd.Parameters.Add (parm);} if (! outCmdParms = null) {foreach (SqlParameter parm in outCmdParms) cmd.Parameters.Add (parm);} Return SQLSerVer;}
Private static hashtable parmcache = hashtable.synchronized (new hashtable ()); /// /// parameter array stored cache /// /// key /// Value: parameter array public static void cacheparameters (string cachekey, params sqlparameter [] cmdparms) {PARMCACHE [cachekey] = cmdparms;} /// /// Remove parameter array /// /// key /// parameter array public static sqlparameter [] getCachedParameters (String Cachekey ) {SQLPARMS = (SqlParameter []) PARMCACHE [CacheKey]; if (CachedParms == Null) Return Null; SqlParameter [] ClonedParms = New Sqlparameter [cachedparms.length];
For (int i = 0, j = cachedparms.length; i
Return clonedparms;
/// /// Execute the Transact-SQL statement to the connection and return the number of rows affected. /// /// Specify how to explain the command string /// T-SQL command line or stored procedure name /// Parameter array /// The number of affected rows public static int executenonQuery (CommandType CmdType, String Cmdtext, params sqlparameter [] cmdparms ) {SqlCommand cmd = new SqlCommand (); SQLServer sqlServer = PrepareCommand (cmd, cmdType, cmdText, cmdParms, null); try {sqlServer.Connection.Open (); sqlServer.BeginTransaction (); int val = cmd.ExecuteNonQuery () SQL Server.commitTransAction (); cmd.parameters.clear (); return val;} catch {sqlserver.rollbacktransaction (); throw;} finally {sqlserver.connection.close ();}} /// // / Generate DataReader /// /// Specify how to explain the command string /// T-SQL command line or stored procedure Name /// Parameter array /// The number of affected rows public static sqldataareader ExecuteReader (CommandType) CMDTYPE, STRING CMDText, Params Sqlparameter [] cmdparms) {SQLCommand cmd = new sqlcommand (); sqlserver sqlserver = prepareCommand (cmd, cmdtype, cmdtext, cmdparms, null);
Try {SqldataReader DR = cmd.executeReader (Commandbehavior.CloseConne); cmd.parameters.clear (); returnif;} catch {sqlserver.connection.close (); throw;}}
/// // / execute the query and return the first column of the first line of the result of the result returned by the query. Ignore additional columns or rows. /// /// Specify how to explain the command string /// T-SQL command line or stored procedure name /// Parameter array /// Results The first line of the first line of the first line Public Static Object ExecuteScalar (CommandType CmdType, String Cmdtext, Params SqlParameter [] cmdParms) {SqlCommand cmd = new SqlCommand (); SQLServer sqlServer = PrepareCommand (cmd, cmdType, cmdText, cmdParms, null); try {sqlServer.Connection.Open (); sqlServer.BeginTransaction (); object val = cmd (); Cmd.parameters.clear (); rul;} catch {sqlser.rollbacktransaction (); throw;} finally {sqlserver.connection.close ();}}
/// /// Execute a stored procedure, with output parameter /// /// Stored procedure name /// Output value reference /// Output parameter array /// Enter parameter array public static void ExecuteStoredProcedure string StoredProcedure, ref object [] outValue, SqlParameter [] outCmdParms, params SqlParameter [] cmdParms) {SqlCommand cmd = new SqlCommand (); SQLServer sqlServer = PrepareCommand (cmd, CommandType.StoredProcedure, StoredProcedure, cmdParms, outCmdParms); try {sqlServer .Connection.open (); sqlserver.begintractions (); cmd.executenonQuery (); sqlserver.commitTransAction (); if (outcmdparms! = Null) for (int i = 0; i
