处女篇：自用C#后端SqlHelper.cs类
自用SqlHelper.cs类，此类来自软谋教育徐老师课程SqlHelper.cs！
1 using System; 2 using System.Collections; 3 using System.Collections.Generic; 4 using System.Configuration; 5 using System.Data; 6 using System.Data.SqlClient; 7 using System.Linq; 8 using System.Web; 9 10 namespace PROJECT20160712 11 { 12 /// 此类为抽象类， 13 /// 不允许实例化，在应用时直接调用即可 14 /// </summary> 15 public abstract class SqlHelper 16 { 17 /// <summary> 18 /// 数据库连接字符串 19 /// </summary> 20 21 public static readonly string connectionString = ConfigurationManager.ConnectionStrings["con"].ToString(); 22 // Hashtable to store cached parameters 23 private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable()); 24 25 #region//ExecteNonQuery方法 26 27 /// <summary> 28 ///执行一个不需要返回值的SqlCommand命令，通过指定专用的连接字符串。 29 /// 使用参数数组形式提供参数列表 30 /// </summary> 31 /// <param name="connectionString">一个有效的数据库连接字符串</param> 32 /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param> 33 /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> 34 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> 35 /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns> 36 public static int ExecteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) 37 { 38 SqlCommand cmd = new SqlCommand(); 39 using (SqlConnection conn = new SqlConnection(connectionString)) 40 { 41 //通过PrePareCommand方法将参数逐个加入到SqlCommand的参数集合中 42 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); 43 int val = cmd.ExecuteNonQuery(); 44 //清空SqlCommand中的参数列表 45 cmd.Parameters.Clear(); 46 return val; 47 } 48 } 49 50 /// <summary> 51 ///执行一个不需要返回值的SqlCommand命令，通过指定专用的连接字符串。 52 /// 使用参数数组形式提供参数列表 53 /// </summary> 54 /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param> 55 /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> 56 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> 57 /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns> 58 public static int ExecteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) 59 { 60 return ExecteNonQuery(connectionString, cmdType, cmdText, commandParameters); 61 } 62 63 /// <summary> 64 ///存储过程专用 65 /// </summary> 66 /// <param name="cmdText">存储过程的名字</param> 67 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> 68 /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns> 69 public static int ExecteNonQueryProducts(string cmdText, params SqlParameter[] commandParameters) 70 { 71 return ExecteNonQuery(CommandType.StoredProcedure, cmdText, commandParameters); 72 } 73 74 /// <summary> 75 ///Sql语句专用 76 /// </summary> 77 /// <param name="cmdText">T_Sql语句</param> 78 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> 79 /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns> 80 public static int ExecteNonQueryText(string cmdText, params SqlParameter[] commandParameters) 81 { 82 return ExecteNonQuery(CommandType.Text, cmdText, commandParameters); 83 } 84 85 #endregion 86 #region//GetTable方法 87 88 /// <summary> 89 /// 执行一条返回结果集的SqlCommand，通过一个已经存在的数据库连接 90 /// 使用参数数组提供参数 91 /// </summary> 92 /// <param name="connecttionString">一个现有的数据库连接</param> 93 /// <param name="cmdTye">SqlCommand命令类型</param> 94 /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> 95 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> 96 /// <returns>返回一个表集合(DataTableCollection)表示查询得到的数据集</returns> 97 public static DataTableCollection GetTable(string connecttionString, CommandType cmdTye, string cmdText, SqlParameter[] commandParameters) 98 { 99 SqlCommand cmd = new SqlCommand(); 100 DataSet ds = new DataSet(); 101 using (SqlConnection conn = new SqlConnection(connecttionString)) 102 { 103 PrepareCommand(cmd, conn, null, cmdTye, cmdText, commandParameters); 104 SqlDataAdapter adapter = new SqlDataAdapter(); 105 adapter.SelectCommand = cmd; 106 adapter.Fill(ds); 107 } 108 DataTableCollection table = ds.Tables; 109 return table; 110 } 111 112 /// <summary> 113 /// 执行一条返回结果集的SqlCommand，通过一个已经存在的数据库连接 114 /// 使用参数数组提供参数 115 /// </summary> 116 /// <param name="cmdTye">SqlCommand命令类型</param> 117 /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> 118 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> 119 /// <returns>返回一个表集合(DataTableCollection)表示查询得到的数据集</returns> 120 public static DataTableCollection GetTable(CommandType cmdTye, string cmdText, SqlParameter[] commandParameters) 121 { 122 return GetTable(cmdTye, cmdText, commandParameters); 123 } 124 125 126 /// <summary> 127 /// 存储过程专用 128 /// </summary> 129 /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> 130 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> 131 /// <returns>返回一个表集合(DataTableCollection)表示查询得到的数据集</returns> 132 public static DataTableCollection GetTableProducts(string cmdText, SqlParameter[] commandParameters) 133 { 134 return GetTable(CommandType.StoredProcedure, cmdText, commandParameters); 135 } 136 137 /// <summary> 138 /// Sql语句专用 139 /// </summary> 140 /// <param name="cmdText"> T-SQL 语句</param> 141 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> 142 /// <returns>返回一个表集合(DataTableCollection)表示查询得到的数据集</returns> 143 public static DataTableCollection GetTableText(string cmdText, SqlParameter[] commandParameters) 144 { 145 return GetTable(CommandType.Text, cmdText, commandParameters); 146 } 147 #endregion 148 149 150 /// <summary> 151 /// 为执行命令准备参数 152 /// </summary> 153 /// <param name="cmd">SqlCommand 命令</param> 154 /// <param name="conn">已经存在的数据库连接</param> 155 /// <param name="trans">数据库事物处理</param> 156 /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param> 157 /// <param name="cmdText">Command text，T-SQL语句 例如 Select * from Products</param> 158 /// <param name="cmdParms">返回带参数的命令</param> 159 private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) 160 { 161 //判断数据库连接状态 162 if (conn.State != ConnectionState.Open) 163 conn.Open(); 164 cmd.Connection = conn; 165 cmd.CommandText = cmdText; 166 //判断是否需要事物处理 167 if (trans != null) 168 cmd.Transaction = trans; 169 cmd.CommandType = cmdType; 170 if (cmdParms != null) 171 { 172 foreach (SqlParameter parm in cmdParms) 173 cmd.Parameters.Add(parm); 174 } 175 } 176 177 /// <summary> 178 /// Execute a SqlCommand that returns a resultset against the database specified in the connection string 179 /// using the provided parameters. 180 /// </summary> 181 /// <param name="connectionString">一个有效的数据库连接字符串</param> 182 /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param> 183 /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> 184 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> 185 /// <returns>A SqlDataReader containing the results</returns> 186 public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) 187 { 188 SqlCommand cmd = new SqlCommand(); 189 SqlConnection conn = new SqlConnection(connectionString); 190 // we use a try/catch here because if the method throws an exception we want to 191 // close the connection throw code, because no datareader will exist, hence the 192 // commandBehaviour.CloseConnection will not work 193 try 194 { 195 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); 196 SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); 197 cmd.Parameters.Clear(); 198 return rdr; 199 } 200 catch 201 { 202 conn.Close(); 203 throw; 204 } 205 } 206 #region//ExecuteDataSet方法 207 208 /// <summary> 209 /// return a dataset 210 /// </summary> 211 /// <param name="connectionString">一个有效的数据库连接字符串</param> 212 /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param> 213 /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> 214 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> 215 /// <returns>return a dataset</returns> 216 public static DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) 217 { 218 SqlConnection conn = new SqlConnection(connectionString); 219 SqlCommand cmd = new SqlCommand(); 220 try 221 { 222 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); 223 SqlDataAdapter da = new SqlDataAdapter(); 224 DataSet ds = new DataSet(); 225 da.SelectCommand = cmd; 226 da.Fill(ds); 227 return ds; 228 } 229 catch 230 { 231 conn.Close(); 232 throw; 233 } 234 } 235 236 237 /// <summary> 238 /// 返回一个DataSet 239 /// </summary> 240 /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param> 241 /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> 242 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> 243 /// <returns>return a dataset</returns> 244 public static DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) 245 { 246 return ExecuteDataSet(connectionString, cmdType, cmdText, commandParameters); 247 } 248 249 /// <summary> 250 /// 返回一个DataSet 251 /// </summary> 252 /// <param name="cmdText">存储过程的名字</param> 253 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> 254 /// <returns>return a dataset</returns> 255 public static DataSet ExecuteDataSetProducts(string cmdText, params SqlParameter[] commandParameters) 256 { 257 return ExecuteDataSet(connectionString, CommandType.StoredProcedure, cmdText, commandParameters); 258 } 259 260 /// <summary> 261 /// 返回一个DataSet 262 /// </summary> 263 264 /// <param name="cmdText">T-SQL 语句</param> 265 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> 266 /// <returns>return a dataset</returns> 267 public static DataSet ExecuteDataSetText(string cmdText, params SqlParameter[] commandParameters) 268 { 269 return ExecuteDataSet(connectionString, CommandType.Text, cmdText, commandParameters); 270 } 271 272 273 public static DataView ExecuteDataSet(string connectionString, string sortExpression, string direction, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) 274 { 275 SqlConnection conn = new SqlConnection(connectionString); 276 SqlCommand cmd = new SqlCommand(); 277 try 278 { 279 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); 280 SqlDataAdapter da = new SqlDataAdapter(); 281 DataSet ds = new DataSet(); 282 da.SelectCommand = cmd; 283 da.Fill(ds); 284 DataView dv = ds.Tables[0].DefaultView; 285 dv.Sort = sortExpression + " " + direction; 286 return dv; 287 } 288 catch 289 { 290 conn.Close(); 291 throw; 292 } 293 } 294 #endregion 295 296 297 #region // ExecuteScalar方法 298 299 300 /// <summary> 301 /// 返回第一行的第一列 302 /// </summary> 303 /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param> 304 /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> 305 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> 306 /// <returns>返回一个对象</returns> 307 public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) 308 { 309 return ExecuteScalar(SqlHelper.connectionString, cmdType, cmdText, commandParameters); 310 } 311 312 /// <summary> 313 /// 返回第一行的第一列存储过程专用 314 /// </summary> 315 /// <param name="cmdText">存储过程的名字</param> 316 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> 317 /// <returns>返回一个对象</returns> 318 public static object ExecuteScalarProducts(string cmdText, params SqlParameter[] commandParameters) 319 { 320 return ExecuteScalar(SqlHelper.connectionString, CommandType.StoredProcedure, cmdText, commandParameters); 321 } 322 323 /// <summary> 324 /// 返回第一行的第一列Sql语句专用 325 /// </summary> 326 /// <param name="cmdText">者 T-SQL 语句</param> 327 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> 328 /// <returns>返回一个对象</returns> 329 public static object ExecuteScalarText(string cmdText, params SqlParameter[] commandParameters) 330 { 331 return ExecuteScalar(SqlHelper.connectionString, CommandType.Text, cmdText, commandParameters); 332 } 333 334 /// <summary> 335 /// Execute a SqlCommand that returns the first column of the first record against the database specified in the connection string 336 /// using the provided parameters. 337 /// </summary> 338 /// <remarks> 339 /// e.g.: 340 /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); 341 /// </remarks> 342 /// <param name="connectionString">一个有效的数据库连接字符串</param> 343 /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param> 344 /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> 345 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> 346 /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns> 347 public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) 348 { 349 SqlCommand cmd = new SqlCommand(); 350 351 using (SqlConnection connection = new SqlConnection(connectionString)) 352 { 353 PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); 354 object val = cmd.ExecuteScalar(); 355 cmd.Parameters.Clear(); 356 return val; 357 } 358 } 359 360 /// <summary> 361 /// Execute a SqlCommand that returns the first column of the first record against an existing database connection 362 /// using the provided parameters. 363 /// </summary> 364 /// <remarks> 365 /// e.g.: 366 /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); 367 /// </remarks> 368 /// <param name="connectionString">一个有效的数据库连接字符串</param> 369 /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param> 370 /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> 371 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> 372 /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns> 373 public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) 374 { 375 SqlCommand cmd = new SqlCommand(); 376 PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); 377 object val = cmd.ExecuteScalar(); 378 cmd.Parameters.Clear(); 379 return val; 380 } 381 382 #endregion 383 384 385 /// <summary> 386 /// add parameter array to the cache 387 /// </summary> 388 /// <param name="cacheKey">Key to the parameter cache</param> 389 /// <param name="cmdParms">an array of SqlParamters to be cached</param> 390 public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters) 391 { 392 parmCache[cacheKey] = commandParameters; 393 } 394 395 /// <summary> 396 /// Retrieve cached parameters 397 /// </summary> 398 /// <param name="cacheKey">key used to lookup parameters</param> 399 /// <returns>Cached SqlParamters array</returns> 400 public static SqlParameter[] GetCachedParameters(string cacheKey) 401 { 402 SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey]; 403 if (cachedParms == null) 404 return null; 405 SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length]; 406 for (int i = 0, j = cachedParms.Length; i < j; i++) 407 clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone(); 408 return clonedParms; 409 } 410 411 412 /// <summary> 413 /// 检查是否存在 414 /// </summary> 415 /// <param name="strSql">Sql语句</param> 416 /// <returns>bool结果</returns> 417 public static bool Exists(string strSql) 418 { 419 int cmdresult = Convert.ToInt32(ExecuteScalar(connectionString, CommandType.Text, strSql, null)); 420 if (cmdresult == 0) 421 { 422 return false; 423 } 424 else 425 { 426 return true; 427 } 428 } 429 430 /// <summary> 431 /// 检查是否存在 432 /// </summary> 433 /// <param name="strSql">Sql语句</param> 434 /// <param name="cmdParms">参数</param> 435 /// <returns>bool结果</returns> 436 public static bool Exists(string strSql, params SqlParameter[] cmdParms) 437 { 438 int cmdresult = Convert.ToInt32(ExecuteScalar(connectionString, CommandType.Text, strSql, cmdParms)); 439 if (cmdresult == 0) 440 { 441 return false; 442 } 443 else 444 { 445 return true; 446 } 447 } 448 } 449 }
