SqlHelper類的方法.doc

using System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.SqlClient;using System.Collections;/ / SqlHelper 的摘要說明/ public class SQLHelper / Fields public static readonly string CONN_STRING; private static Hashtable parmCache; static SQLHelper() /definition the Database Connection; SQLHelper.CONN_STRING = Data Source=.;Initial Catalog=two_company;Persist Security Info=True;User ID=sa;Password=sa; SQLHelper.parmCache = Hashtable.Synchronized(new Hashtable(); public static void CacheParameters(string cacheKey, params SqlParameter cmdParms) SQLHelper.parmCachecacheKey = cmdParms; /clear the SqlCommand Params Set it Values is Null public static void ClearParameterValues(params SqlParameter cmdParms) SqlParameter parameterArray1 = cmdParms; for (int num1 = 0; num1 0) & (set1.Tables0.Rows.Count 0) /why Return First Row return set1.Tables0.Rows0; return null; public static DataSet ExecuteDataset(string cmdText) DataSet set2; SqlCommand command1 = new SqlCommand(); SqlConnection connection1 = new SqlConnection(SQLHelper.CONN_STRING); DataSet set1 = new DataSet(); try /Often need to determine if (connection1.State != ConnectionState.Open) connection1.Open(); command1.Connection = connection1; command1.CommandText = cmdText; command1.CommandType = CommandType.Text; SqlDataAdapter adapter1 = new SqlDataAdapter(); adapter1.SelectCommand = command1; adapter1.Fill(set1); set2 = set1; catch throw; finally connection1.Close(); return set2; public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText) return SQLHelper.ExecuteDataset(connection, commandType, commandText, (SqlParameter)null); public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText) return SQLHelper.ExecuteDataset(transaction, commandType, commandText, (SqlParameter)null); public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText) return SQLHelper.ExecuteDataset(connectionString, commandType, commandText, (SqlParameter)null); public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter commandParameters) SqlCommand command1 = new SqlCommand(); SQLHelper.PrepareCommand(command1, connection, null, commandType, commandText, commandParameters); SqlDataAdapter adapter1 = new SqlDataAdapter(command1); DataSet set1 = new DataSet(); adapter1.Fill(set1); command1.Parameters.Clear(); return set1; public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter commandParameters) SqlCommand command1 = new SqlCommand(); SQLHelper.PrepareCommand(command1, transaction.Connection, transaction, commandType, commandText, commandParameters); SqlDataAdapter adapter1 = new SqlDataAdapter(command1); DataSet set1 = new DataSet(); adapter1.Fill(set1); command1.Parameters.Clear(); return set1; public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter commandParameters) DataSet set1; using (SqlConnection connection1 = new SqlConnection(connectionString) connection1.Open(); set1 = SQLHelper.ExecuteDataset(connection1, commandType, commandText, commandParameters); return set1; public static DataSet ExecuteDataset(string connString, CommandType commandType, string commandText, SqlParameter commandParameters, int startRecord, int maxRecords, string srcTable) SqlConnection connection1 = new SqlConnection(connString); SqlCommand command1 = new SqlCommand(); SQLHelper.PrepareCommand(command1, connection1, null, commandType, commandText, commandParameters); SqlDataAdapter adapter1 = new SqlDataAdapter(command1); DataSet set1 = new DataSet(); adapter1.Fill(set1, startRecord, maxRecords, srcTable); /Remember These command1.Parameters.Clear(); return set1; public static DataTable ExecuteDataTable(string cmdText) DataSet set1 = SQLHelper.ExecuteDataset(cmdText); if (set1.Tables.Count 0) return set1.Tables0; return null; public static int ExecuteNonQuery(string cmdText) int num2; SqlCommand command1 = new SqlCommand(); using (SqlConnection connection1 = new SqlConnection(SQLHelper.CONN_STRING) SQLHelper.PrepareCommand(command1, connection1, null, CommandType.Text, cmdText, null); int num1 = command1.ExecuteNonQuery(); command1.Parameters.Clear(); num2 = num1; return num2; /If SqlCommand.ExecuteNonQuery implementation of successful,It Return 1 or 1 else it Return -1 public static int ExecuteNonQuery(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter cmdParms) SqlCommand command1 = new SqlCommand(); SQLHelper.PrepareCommand(command1, conn, null, cmdType, cmdText, cmdParms); int num1 = command1.ExecuteNonQuery(); command1.Parameters.Clear(); return num1; public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter cmdParms) SqlCommand command1 = new SqlCommand(); SQLHelper.PrepareCommand(command1, trans.Connection, trans, cmdType, cmdText, cmdParms); int num1 = command1.ExecuteNonQuery(); command1.Parameters.Clear(); return num1; public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params SqlParameter cmdParms) int num2; SqlCommand command1 = new SqlCommand(); using (SqlConnection connection1 = new SqlConnection(connString) SQLHelper.PrepareCommand(command1, connection1, null, cmdType, cmdText, cmdParms); int num1 = command1.ExecuteNonQuery(); command1.Parameters.Clear(); num2 = num1; return num2; public static SqlDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params SqlParameter cmdParms) SqlDataReader reader2; SqlCommand command1 = new SqlCommand(); SqlConnection connection1 = new SqlConnection(connString); try SQLHelper.PrepareCommand(command1, connection1, null, cmdType, cmdText, cmdParms); SqlDataReader reader1 = command1.ExecuteReader(CommandBehavior.CloseConnection); command1.Parameters.Clear(); reader2 = reader1; catch connection1.Close(); throw; return reader2; public static SqlDataReader ExecuteReader(SqlConnection cn, CommandType cmdType, string cmdText, params SqlParameter cmdParms) SqlDataReader reader2; SqlCommand command1 = new SqlCommand(); try SQLHelper.PrepareCommand(command1, cn, null, cmdType, cmdText, cmdParms); SqlDataReader reader1 = command1.ExecuteReader(CommandBehavior.CloseConnection); command1.Parameters.Clear(); reader2 = reader1; catch cn.Close(); throw; return reader2; public static object ExecuteScalar(string cmdText) object obj2; SqlCommand command1 = new SqlCommand(); using (SqlConnection connection1 = new SqlConnection(SQLHelper.CONN_STRING) SQLHelper.PrepareCommand(command1, connection1, null, CommandType.Text, cmdText, null); object obj1 = command1.ExecuteScalar(); command1.Parameters.Clear(); obj2 = obj1; return obj2; public static object ExecuteScalar(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter cmdParms) SqlCommand command1 = new SqlCommand(); SQLHelper.PrepareCommand(command1, conn, null, cmdType, cmdText, cmdParms); object obj1 = command1.ExecuteScalar(); command1.Parameters.Clear(); return obj1; public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params SqlParameter cmdParms) object obj2; SqlCommand command1 = new SqlCommand(); using (SqlConnection connection1 = new SqlConnection(connString) SQLHelper.PrepareCommand(command1, connection1, null, cmdType, cmdText, cmdParms); object obj1 = command1.ExecuteScalar(); command1.Parameters.Clear(); obj2 = obj1; return obj2; public static SqlParameter GetCachedParameters(string cacheKey) SqlParameter parameterArray1 = (SqlParameter)SQLHelper.parmCachecacheKey; if (parameterArray1 = null) return null; SqlParameter parameterArray2 = new SqlParameterparameterArray1.Length; int num1 = 0; int num2 = parameterArray1.Length; while (num1 num2) parameterArray2num1 = (SqlParameter)(ICloneable)parameterArray1num1).Clone(); num1+; return parameterArray2; /perform SqlCommand private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter cmdParms) if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) cmd.Transaction = trans; cmd.CommandType = cmdType; if (cmdParms != null) SqlParameter parameterArray1 = cmdParms; for (int num1 = 0; num1 parameterArray1.Length; num1+) SqlParameter parameter1 = parameterArray1num1; cmd.Parameters.Add(parameter1); public static SqlCommand CreateCommand(SqlConnection conn, CommandType cmdType, string cmdText, SqlParameter cmdParms) if (conn = null) throw new ArgumentNullException(conn); if (cmdTex