sqlhelper

最新推荐文章于 2025-08-10 13:24:43 发布

原创最新推荐文章于 2025-08-10 13:24:43 发布 · 432 阅读

CC 4.0 BY-SA版权

文章标签：

某网友项目用sqlhelper 1 using System; 2 using System.Data; 3 using System.Configuration; 4 using System.Web; 5 using System.Web.Security; 6 using System.Web.UI; 7 using System.Web.UI.WebControls; 8 using System.Web.UI.WebControls.WebParts; 9 using System.Web.UI.HtmlControls; 10 using System.Data.SqlClient; 11 using System.Collections; 12 13 /// <summary> 14 /// 数据库的访问类（） 15 /// author:daiwei 16 /// date:09/07/11 17 /// </summary> 18 public abstract class SqlHelper 19 { 20 public SqlHelper() 21 { 22 // 23 // TODO: 在此处添加构造函数逻辑 24 // 25 } 26 27 //从web配置文件中读取数据数据库连接字符串信息，静态只读。 28 public static readonly string ConnectionStringLocalTransaction = ConfigurationManager.ConnectionStrings["pubsConnectionString"].ConnectionString; 29 30 //用哈希表来存储缓存的参数信息， 31 //哈希表可以用来存储各种类型的参数信息 32 //线程同步包装 33 private static Hashtable tmpCache = Hashtable.Synchronized(new Hashtable()); 34 35 /// <summary> 36 /// 37 /// </summary> 38 /// <param name="connectionString">数据库连接字符串</param> 39 /// <param name="cmdType">命令执行类型，包括存储过程，sql语句</param> 40 /// <param name="cmdText">SQL</param> 41 /// <param name="commandParameters">以数组的形式提供命令中所用的参数列表</param> 42 /// <returns>返回命令执行后所影响的行数</returns> 43 public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) 44 { 45 SqlCommand cmd = new SqlCommand(); 46 using (SqlConnection conn = new SqlConnection(connectionString)) 47 { 48 //通过PrePareCommand方法将参数逐个加到SqlCommand的参数集合中 49 PrePareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); 50 51 int val = cmd.ExecuteNonQuery(); 52 53 //清空SqlCommand中参数列表 54 cmd.Parameters.Clear(); 55 return val; 56 } 57 } 58 59 /// <summary> 60 /// 执行命令，返回受影响的行数 61 /// </summary> 62 /// <param name="connection">数据库连接字符串</param> 63 /// <param name="cmdType">命令类型</param> 64 /// <param name="cmdText">SQL</param> 65 /// <param name="commandParameters">命令参数列表</param> 66 /// <returns>受影响函数</returns> 67 public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) 68 { 69 SqlCommand cmd = new SqlCommand(); 70 //cmd.Connection = connection; 71 //cmd.CommandText = cmdText; 72 PrePareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); 73 int val = cmd.ExecuteNonQuery(); 74 cmd.Parameters.Clear(); 75 return val; 76 77 } 78 79 /// <summary> 80 /// 执行命令，返回受影响函数 81 /// </summary> 82 /// <param name="trans">sql事务</param> 83 /// <param name="cmdType">命令类型</param> 84 /// <param name="cmdText">SQL</param> 85 /// <param name="commandParameters">命令参数列表</param> 86 /// <returns>返回执行命令受影响的行数</returns> 87 public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) 88 { 89 SqlCommand cmd = new SqlCommand(); 90 PrePareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters); 91 int val = cmd.ExecuteNonQuery(); 92 cmd.Parameters.Clear(); 93 return val; 94 } 95 96 /// <summary> 97 /// 执行命令，返回结果集 98 /// </summary> 99 /// <param name="connectionString">数据库连接</param> 100 /// <param name="cmdType">命令类型</param> 101 /// <param name="cmdText">SQL</param> 102 /// <param name="commandParameters">命令参数列表参数的</param> 103 /// <returns>SqlDataReader结果集</returns> 104 public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) 105 { 106 SqlCommand cmd = new SqlCommand(); 107 SqlConnection conn = new SqlConnection(connectionString); 108 109 //使用try/catch处理 110 //如果发生异常，则SqlDataReader就不存在， 111 //CommandBehavior.CloseConnection就不会执行，异常将被捕获 112 //关闭数据库连接， 113 try 114 { 115 PrePareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); 116 SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); 117 cmd.Parameters.Clear(); 118 return rdr; 119 } 120 catch 121 { 122 conn.Close(); 123 throw; 124 } 125 } 126 127 /// <summary> 128 /// 执行命令，返回第一条记录的第一列 129 /// </summary> 130 /// <param name="connectionString">数据库连接字符串</param> 131 /// <param name="cmdType">命令类型</param> 132 /// <param name="cmdText">SQL</param> 133 /// <param name="commandParameters">命令参数列表</param> 134 /// <returns>返回一个Object类型的数据，可以通过ConvertTo(Type)方法转换类型</returns> 135 public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) 136 { 137 SqlCommand cmd = new SqlCommand(); 138 using (SqlConnection connection = new SqlConnection(connectionString)) 139 { 140 PrePareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); 141 object val = cmd.ExecuteScalar(); 142 cmd.Parameters.Clear(); 143 return val; 144 } 145 } 146 147 /// <summary> 148 /// 执行命令，返回第一条记录的第一列 149 /// </summary> 150 /// <param name="connection">数据库连接字符串</param> 151 /// <param name="cmdType">命令类型</param> 152 /// <param name="cmdText">SQL</param> 153 /// <param name="commandParameters">命令参数列表</param> 154 /// <returns>返回一个Object类型的数据，可以通过Convert.To(Type)方法转换类型</returns> 155 public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) 156 { 157 SqlCommand cmd = new SqlCommand(); 158 159 PrePareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); 160 object val = cmd.ExecuteScalar(); 161 cmd.Parameters.Clear(); 162 return val; 163 } 164 165 /// <summary> 166 /// 缓存参数列表 167 /// </summary> 168 /// <param name="cacheKey">缓存的键值</param> 169 /// <returns>缓存的参数列表</returns> 170 public static SqlParameter[] GetCachedParameters(string cacheKey) 171 { 172 SqlParameter[] cacheParms = (SqlParameter[])tmpCache[cacheKey]; 173 if (cacheParms == null) 174 { 175 return null; 176 } 177 178 //新建一个参数的克隆列表 179 SqlParameter[] cloneParms = new SqlParameter[cacheParms.Length]; 180 181 //通过循环为克隆参数列表赋值 182 for (int i = 0, j = cacheParms.Length; i < j; i++) 183 { 184 //使用clone方法复制参数列表中的参数 185 cloneParms[i] = (SqlParameter)((ICloneable)cacheParms[i]).Clone(); 186 } 187 188 return cloneParms; 189 } 190 191 /// <summary> 192 /// 缓存参数数组 193 /// </summary> 194 /// <param name="cacheKey">参数缓存的键值</param> 195 /// <param name="commandParameters">被缓存的参数列表</param> 196 public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters) 197 { 198 tmpCache[cacheKey] = commandParameters; 199 } 200 201 /// <summary> 202 /// 为执行命令准备参数 203 /// </summary> 204 /// <param name="cmd">命令</param> 205 /// <param name="conn">数据库连接字符串</param> 206 /// <param name="trans">事务</param> 207 /// <param name="cmdType">命令类型</param> 208 /// <param name="cmdText">SQL</param> 209 /// <param name="cmdParms">返回带参数的命令</param> 210 private static void PrePareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) 211 { 212 //判断数据库连接状态 213 if (conn.State != ConnectionState.Open) 214 { 215 //如果数据库连接未打开，则打开数据库连接 216 conn.Open(); 217 218 } 219 220 cmd.Connection = conn; 221 cmd.CommandText = cmdText; 222 223 //是否需要事物处理 224 if (trans != null) 225 { 226 cmd.Transaction = trans; 227 } 228 229 //命令类型 230 cmd.CommandType = cmdType; 231 232 //添加参数 233 if (cmdParms != null) 234 { 235 foreach (SqlParameter param in cmdParms) 236 { 237 cmd.Parameters.Add(param); 238 } 239 } 240 } 241 }