关于SqlHelper
在 SqlHelper 类中实现的方法包括：
ExecuteNonQuery。此方法用于执行不返回任何行或值的命令。这些命令通常用于执行数据库更新，但也可用于返回存储过程的输出参数。
ExecuteReader。此方法用于返回SqlDataReader对象，该对象包含由某一命令返回的结果集。
ExecuteDataset。此方法返回DataSet对象，该对象包含由某一命令返回的结果集。
ExecuteScalar。此方法返回一个值。该值始终是该命令返回的第一行的第一列。
ExecuteXmlReader。此方法返回 FOR XML 查询的 XML 片段。
1 using System; 2 using System.Data; 3 using System.Data.SqlClient; 4 using System.Linq; 5 using System.Net.Sockets; 6 7 namespace Micua.Infrastructure.Utility 8 { 9 /// <summary> 10 /// SQL Server数据库访问助手类 11 /// 本类为静态类 不可以被实例化 需要使用时直接调用即可 12 /// Copyright © 2013 Wedn.Net 13 /// </summary> 14 public static partial class SqlHelper 15 { 16 private static readonly string[] localhost = new[] { "localhost", ".", "(local)" }; 17 /// <summary> 18 /// 数据库连接字符串 19 /// </summary> 20 private readonly static string connStr; 21 static SqlHelper() 22 { 23 var conn = System.Configuration.ConfigurationManager.ConnectionStrings["MicuaContext"];//MicuaContext是配置文件里面
ConnectionStrings节点的子节点<Add>的name属性值 24 if (conn!=null) 25 { 26 connStr = conn.ConnectionString; 27 } 28 } 29 30 #region 数据库检测 31 32 #region 测试数据库服务器连接 +static bool TestConnection(string host, int port, int millisecondsTimeout) 33 /// <summary> 34 /// 采用Socket方式，测试数据库服务器连接 35 /// </summary> 36 /// <param name="host">服务器主机名或IP</param> 37 /// <param name="port">端口号</param> 38 /// <param name="millisecondsTimeout">等待时间：毫秒</param> 39 /// <exception cref="Exception">链接异常</exception> 40 /// <returns></returns> 41 public static bool TestConnection(string host, int port, int millisecondsTimeout) 42 { 43 host = localhost.Contains(host) ? "127.0.0.1" : host; 44 using (var client = new TcpClient()) 45 { 46 try 47 { 48 var ar = client.BeginConnect(host, port, null, null); 49 ar.AsyncWaitHandle.WaitOne(millisecondsTimeout); 50 return client.Connected; 51 } 52 catch (Exception) 53 { 54 throw; 55 } 56 } 57 } 58 #endregion 59 60 #region 检测表是否存在 + static bool ExistsTable(string table) 61 /// <summary> 62 /// 检测表是否存在 63 /// </summary> 64 /// <param name="table">要检测的表名</param> 65 /// <returns></returns> 66 public static bool ExistsTable(string table) 67 { 68 string sql = "select count(1) from sysobjects where id = object_id(N'[" + table + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1"; 69 //string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')"; 70 object res = ExecuteScalar(sql); 71 return (Object.Equals(res, null)) || (Object.Equals(res, System.DBNull.Value)); 72 } 73 #endregion 74 75 #region 判断是否存在某张表的某个字段 +static bool ExistsColumn(string table, string column) 76 /// <summary> 77 /// 判断是否存在某张表的某个字段 78 /// </summary> 79 /// <param name="table">表名称</param> 80 /// <param name="column">列名称</param> 81 /// <returns>是否存在</returns> 82 public static bool ExistsColumn(string table, string column) 83 { 84 string sql = "select count(1) from syscolumns where [id]=object_id('N[" + table + "]') and [name]='" + column + "'"; 85 object res = ExecuteScalar(sql); 86 if (res == null) 87 return false; 88 return Convert.ToInt32(res) > 0; 89 } 90 #endregion 91 92 #region 判断某张表的某个字段中是否存在某个值 +static bool ColumnExistsValue(string table, string column, string value) 93 /// <summary> 94 /// 判断某张表的某个字段中是否存在某个值 95 /// </summary> 96 /// <param name="table">表名称</param> 97 /// <param name="column">列名称</param> 98 /// <param name="value">要判断的值</param> 99 /// <returns>是否存在</returns> 100 public static bool ColumnExistsValue(string table, string column, string value) 101 { 102 string sql = "SELECT count(1) FROM [" + table + "] WHERE [" + column + "]=@Value;"; 103 object res = ExecuteScalar(sql, new SqlParameter("@Value", value)); 104 if (res == null) 105 return false; 106 return Convert.ToInt32(res) > 0; 107 } 108 #endregion 109 110 #endregion 111 112 #region 公共方法 113 114 #region 获取指定表中指定字段的最大值, 确保字段为INT类型 115 /// <summary> 116 /// 获取指定表中指定字段的最大值, 确保字段为INT类型 117 /// </summary> 118 /// <param name="fieldName">字段名</param> 119 /// <param name="tableName">表名</param> 120 /// <returns>最大值</returns> 121 public static int QueryMaxId(string fieldName, string tableName) 122 { 123 string sql = string.Format("select max([{0}]) from [{1}];", fieldName, tableName); 124 object res = ExecuteScalar(sql); 125 if (res == null) 126 return 0; 127 return Convert.ToInt32(res); 128 } 129 #endregion 130 131 #region 生成查询语句 132 133 #region 生成分页查询数据库语句 +static string GenerateQuerySql(string table, string[] columns, int index, int size, string wheres, string orderField, bool isDesc = true) 134 /// <summary> 135 /// 生成分页查询数据库语句, 返回生成的T-SQL语句 136 /// </summary> 137 /// <param name="table">表名</param> 138 /// <param name="columns">列集合, 多个列用英文逗号分割(colum1,colum2...)</param> 139 /// <param name="index">页码(即第几页)(传入-1则表示忽略分页取出全部)</param> 140 /// <param name="size">显示页面大小(即显示条数)</param> 141 /// <param name="where">条件语句(忽略则传入null)</param> 142 /// <param name="orderField">排序字段(即根据那个字段排序)(忽略则传入null)</param> 143 /// <param name="isDesc">排序方式(0:降序(desc)|1:升序(asc))(忽略则传入-1)</param> 144 /// <returns>生成的T-SQL语句</returns> 145 public static string GenerateQuerySql(string table, string[] columns, int index, int size, string where, string orderField, bool isDesc = true) 146 { 147 if (index == 1) 148 { 149 // 生成查询第一页SQL 150 return GenerateQuerySql(table, columns, size, where, orderField, isDesc); 151 } 152 if (index < 1) 153 { 154 // 取全部数据 155 return GenerateQuerySql(table, columns, where, orderField, isDesc); 156 } 157 if (string.IsNullOrEmpty(orderField)) 158 { 159 throw new ArgumentNullException("orderField"); 160 } 161 // 其他情况, 生成row_number分页查询语句 162 // SQL模版 163 const string format = @"select {0} from 164 ( 165 select ROW_NUMBER() over(order by [{1}] {2}) as num, {0} from [{3}] {4} 166 ) 167 as tbl 168 where 169 tbl.num between ({5}-1)*{6} + 1 and {5}*{6};"; 170 // where语句组建 171 where = string.IsNullOrEmpty(where) ? string.Empty : "where " + where; 172 // 查询字段拼接 173 string column = columns != null && columns.Any() ? string.Join(" , ", columns) : "*"; 174 return string.Format(format, column, orderField, isDesc ? "desc" : string.Empty, table, where, index, size); 175 } 176 #endregion 177 #region 生成查询数据库语句查询全部 +static string GenerateQuerySql(string table, string[] columns, string wheres, string orderField, bool isDesc = true) 178 /// <summary> 179 /// 生成查询数据库语句查询全部, 返回生成的T-SQL语句 180 /// </summary> 181 /// <param name="table">表名</param> 182 /// <param name="columns">列集合</param> 183 /// <param name="where">条件语句(忽略则传入null)</param> 184 /// <param name="orderField">排序字段(即根据那个字段排序)(忽略则传入null)</param> 185 /// <param name="isDesc">排序方式(0:降序(desc)|1:升序(asc))(忽略则传入-1)</param> 186 /// <returns>生成的T-SQL语句</returns> 187 public static string GenerateQuerySql(string table, string[] columns, string where, string orderField, bool isDesc = true) 188 { 189 // where语句组建 190 where = string.IsNullOrEmpty(where) ? string.Empty : "where " + where; 191 // 查询字段拼接 192 string column = columns != null && columns.Any() ? string.Join(" , ", columns) : "*"; 193 const string format = "select {0} from [{1}] {2} {3} {4}"; 194 return string.Format(format, column, table, where, 195 string.IsNullOrEmpty(orderField) ? string.Empty : "order by " + orderField, 196 isDesc && !string.IsNullOrEmpty(orderField) ? "desc" : string.Empty); 197 } 198 #endregion 199 200 #region 生成分页查询数据库语句查询第一页 +static string GenerateQuerySql(string table, string[] columns, int size, string where, string orderField, bool isDesc = true) 201 /// <summary> 202 /// 生成分页查询数据库语句查询第一页, 返回生成的T-SQL语句 203 /// </summary> 204 /// <param name="table">表名</param> 205 /// <param name="columns">列集合</param> 206 /// <param name="size">显示页面大小(即显示条数)</param> 207 /// <param name="where">条件语句(忽略则传入null)</param> 208 /// <param name="orderField">排序字段(即根据那个字段排序)(忽略则传入null)</param> 209 /// <param name="isDesc">排序方式(0:降序(desc)|1:升序(asc))(忽略则传入-1)</param> 210 /// <returns>生成的T-SQL语句</returns> 211 public static string GenerateQuerySql(string table, string[] columns, int size, string where, string orderField, bool isDesc = true) 212 { 213 // where语句组建 214 where = string.IsNullOrEmpty(where) ? string.Empty : "where " + where; 215 // 查询字段拼接 216 string column = columns != null && columns.Any() ? string.Join(" , ", columns) : "*"; 217 const string format = "select top {0} {1} from [{2}] {3} {4} {5}"; 218 return string.Format(format, size, column, table, where, 219 string.IsNullOrEmpty(orderField) ? string.Empty : "order by " + orderField, 220 isDesc && !string.IsNullOrEmpty(orderField) ? "desc" : string.Empty); 221 } 222 #endregion 223 #endregion 224 225 #region 将一个SqlDataReader对象转换成一个实体类对象 +static TEntity MapEntity<TEntity>(SqlDataReader reader) where TEntity : class,new() 226 /// <summary> 227 /// 将一个SqlDataReader对象转换成一个实体类对象 228 /// </summary> 229 /// <typeparam name="TEntity">实体类型</typeparam> 230 /// <param name="reader">当前指向的reader</param> 231 /// <returns>实体对象</returns> 232 public static TEntity MapEntity<TEntity>(SqlDataReader reader) where TEntity : class,new() 233 { 234 try 235 { 236 var props = typeof(TEntity).GetProperties(); 237 var entity = new TEntity(); 238 foreach (var prop in props) 239 { 240 if (prop.CanWrite) 241 { 242 try 243 { 244 var index = reader.GetOrdinal(prop.Name); 245 var data = reader.GetValue(index); 246 if (data != DBNull.Value) 247 { 248 prop.SetValue(entity, Convert.ChangeType(data, prop.PropertyType), null); 249 } 250 } 251 catch (IndexOutOfRangeException) 252 { 253 continue; 254 } 255 } 256 } 257 return entity; 258 } 259 catch 260 { 261 return null; 262 } 263 } 264 #endregion 265 266 #endregion 267 268 #region SQL执行方法 269 270 #region ExecuteNonQuery +static int ExecuteNonQuery(string cmdText, params SqlParameter[] parameters) 271 /// <summary> 272 /// 执行一个非查询的T-SQL语句，返回受影响行数，如果执行的是非增、删、改操作，返回-1 273 /// </summary> 274 /// <param name="cmdText">要执行的T-SQL语句</param> 275 /// <param name="parameters">参数列表</param> 276 /// <exception cref="链接数据库异常"></exception> 277 /// <returns>受影响的行数</returns> 278 public static int ExecuteNonQuery(string cmdText, params SqlParameter[] parameters) 279 { 280 return ExecuteNonQuery(cmdText, CommandType.Text, parameters); 281 } 282 #endregion 283 284 #region ExecuteNonQuery +static int ExecuteNonQuery(string cmdText, CommandType type, params SqlParameter[] parameters) 285 /// <summary> 286 /// 执行一个非查询的T-SQL语句，返回受影响行数，如果执行的是非增、删、改操作，返回-1 287 /// </summary> 288 /// <param name="cmdText">要执行的T-SQL语句</param> 289 /// <param name="type">命令类型</param> 290 /// <param name="parameters">参数列表</param> 291 /// <exception cref="链接数据库异常"></exception> 292 /// <returns>受影响的行数</returns> 293 public static int ExecuteNonQuery(string cmdText, CommandType type, params SqlParameter[] parameters) 294 { 295 using (SqlConnection conn = new SqlConnection(connStr)) 296 { 297 using (SqlCommand cmd = new SqlCommand(cmdText, conn)) 298 { 299 if (parameters != null) 300 { 301 cmd.Parameters.Clear(); 302 cmd.Parameters.AddRange(parameters); 303 } 304 cmd.CommandType = type; 305 try 306 { 307 conn.Open(); 308 int res = cmd.ExecuteNonQuery(); 309 cmd.Parameters.Clear(); 310 return res; 311 } 312 catch (System.Data.SqlClient.SqlException e) 313 { 314 conn.Close(); 315 throw e; 316 } 317 } 318 } 319 } 320 #endregion 321 322 #region ExecuteScalar +static object ExecuteScalar(string cmdText, params SqlParameter[] parameters) 323 /// <summary> 324 /// 执行一个查询的T-SQL语句，返回第一行第一列的结果 325 /// </summary> 326 /// <param name="cmdText">要执行的T-SQL语句</param> 327 /// <param name="parameters">参数列表</param> 328 /// <exception cref="链接数据库异常"></exception> 329 /// <returns>返回第一行第一列的数据</returns> 330 public static object ExecuteScalar(string cmdText, params SqlParameter[] parameters) 331 { 332 return ExecuteScalar(cmdText, CommandType.Text, parameters); 333 } 334 #endregion 335 336 #region ExecuteScalar +static object ExecuteScalar(string cmdText, CommandType type, params SqlParameter[] parameters) 337 /// <summary> 338 /// 执行一个查询的T-SQL语句，返回第一行第一列的结果 339 /// </summary> 340 /// <param name="cmdText">要执行的T-SQL语句</param> 341 /// <param name="type">命令类型</param> 342 /// <param name="parameters">参数列表</param> 343 /// <exception cref="链接数据库异常"></exception> 344 /// <returns>返回第一行第一列的数据</returns> 345 public static object ExecuteScalar(string cmdText, CommandType type, params SqlParameter[] parameters) 346 { 347 using (SqlConnection conn = new SqlConnection(connStr)) 348 { 349 using (SqlCommand cmd = new SqlCommand(cmdText, conn)) 350 { 351 if (parameters != null) 352 { 353 cmd.Parameters.Clear(); 354 cmd.Parameters.AddRange(parameters); 355 } 356 cmd.CommandType = type; 357 try 358 { 359 conn.Open(); 360 object res = cmd.ExecuteScalar(); 361 cmd.Parameters.Clear(); 362 return res; 363 } 364 catch (System.Data.SqlClient.SqlException e) 365 { 366 conn.Close(); 367 throw e; 368 } 369 } 370 } 371 } 372 #endregion 373 374 #region ExecuteReader +static void ExecuteReader(string cmdText, Action<SqlDataReader> action, params SqlParameter[] parameters) 375 /// <summary> 376 /// 利用委托 执行一个大数据查询的T-SQL语句 377 /// </summary> 378 /// <param name="cmdText">要执行的T-SQL语句</param> 379 /// <param name="action">传入执行的委托对象</param> 380 /// <param name="parameters">参数列表</param> 381 /// <exception cref="链接数据库异常"></exception> 382 public static void ExecuteReader(string cmdText, Action<SqlDataReader> action, params SqlParameter[] parameters) 383 { 384 ExecuteReader(cmdText, action, CommandType.Text, parameters); 385 } 386 #endregion 387 388 #region ExecuteReader +static void ExecuteReader(string cmdText, Action<SqlDataReader> action, CommandType type, params SqlParameter[] parameters) 389 /// <summary> 390 /// 利用委托 执行一个大数据查询的T-SQL语句 391 /// </summary> 392 /// <param name="cmdText">要执行的T-SQL语句</param> 393 /// <param name="action">传入执行的委托对象</param> 394 /// <param name="type">命令类型</param> 395 /// <param name="parameters">参数列表</param> 396 /// <exception cref="链接数据库异常"></exception> 397 public static void ExecuteReader(string cmdText, Action<SqlDataReader> action, CommandType type, params SqlParameter[] parameters) 398 { 399 using (SqlConnection conn = new SqlConnection(connStr)) 400 { 401 using (SqlCommand cmd = new SqlCommand(cmdText, conn)) 402 { 403 if (parameters != null) 404 { 405 cmd.Parameters.Clear(); 406 cmd.Parameters.AddRange(parameters); 407 } 408 cmd.CommandType = type; 409 try 410 { 411 conn.Open(); 412 using (SqlDataReader reader = cmd.ExecuteReader()) 413 { 414 action(reader); 415 } 416 cmd.Parameters.Clear(); 417 } 418 catch (System.Data.SqlClient.SqlException e) 419 { 420 conn.Close(); 421 throw e; 422 } 423 } 424 } 425 } 426 #endregion 427 428 #region ExecuteReader +static SqlDataReader ExecuteReader(string cmdText, params SqlParameter[] parameters) 429 /// <summary> 430 /// 执行一个查询的T-SQL语句, 返回一个SqlDataReader对象, 如果出现SQL语句执行错误, 将会关闭连接通道抛出异常 431 /// ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) 432 /// </summary> 433 /// <param name="cmdText">要执行的T-SQL语句</param> 434 /// <param name="parameters">参数列表</param> 435 /// <exception cref="链接数据库异常"></exception> 436 /// <returns>SqlDataReader对象</returns> 437 public static SqlDataReader ExecuteReader(string cmdText, params SqlParameter[] parameters) 438 { 439 return ExecuteReader(cmdText, CommandType.Text, parameters); 440 } 441 #endregion 442 443 #region ExecuteReader +static SqlDataReader ExecuteReader(string cmdText, CommandType type, params SqlParameter[] parameters) 444 /// <summary> 445 /// 执行一个查询的T-SQL语句, 返回一个SqlDataReader对象, 如果出现SQL语句执行错误, 将会关闭连接通道抛出异常 446 /// ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) 447 /// </summary> 448 /// <param name="cmdText">要执行的T-SQL语句</param> 449 /// <param name="type">命令类型</param> 450 /// <param name="parameters">参数列表</param> 451 /// <exception cref="链接数据库异常"></exception> 452 /// <returns>SqlDataReader对象</returns> 453 public static SqlDataReader ExecuteReader(string cmdText, CommandType type, params SqlParameter[] parameters) 454 { 455 SqlConnection conn = new SqlConnection(connStr); 456 using (SqlCommand cmd = new SqlCommand(cmdText, conn)) 457 { 458 if (parameters != null) 459 { 460 cmd.Parameters.Clear(); 461 cmd.Parameters.AddRange(parameters); 462 } 463 cmd.CommandType = type; 464 conn.Open(); 465 try 466 { 467 SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 468 cmd.Parameters.Clear(); 469 return reader; 470 } 471 catch (System.Data.SqlClient.SqlException ex) 472 { 473 //出现异常关闭连接并且释放 474 conn.Close(); 475 throw ex; 476 } 477 } 478 } 479 #endregion 480 481 #region ExecuteDataSet +static DataSet ExecuteDataSet(string cmdText, params SqlParameter[] parameters) 482 /// <summary> 483 /// 执行一个查询的T-SQL语句, 返回一个离线数据集DataSet 484 /// </summary> 485 /// <param name="cmdText">要执行的T-SQL语句</param> 486 /// <param name="parameters">参数列表</param> 487 /// <returns>离线数据集DataSet</returns> 488 public static DataSet ExecuteDataSet(string cmdText, params SqlParameter[] parameters) 489 { 490 return ExecuteDataSet(cmdText, CommandType.Text, parameters); 491 } 492 #endregion 493 494 #region ExecuteDataSet +static DataSet ExecuteDataSet(string cmdText, CommandType type, params SqlParameter[] parameters) 495 /// <summary> 496 /// 执行一个查询的T-SQL语句, 返回一个离线数据集DataSet 497 /// </summary> 498 /// <param name="cmdText">要执行的T-SQL语句</param> 499 /// <param name="type">命令类型</param> 500 /// <param name="parameters">参数列表</param> 501 /// <returns>离线数据集DataSet</returns> 502 public static DataSet ExecuteDataSet(string cmdText, CommandType type, params SqlParameter[] parameters) 503 { 504 using (SqlDataAdapter adapter = new SqlDataAdapter(cmdText, connStr)) 505 { 506 using (DataSet ds = new DataSet()) 507 { 508 if (parameters != null) 509 { 510 adapter.SelectCommand.Parameters.Clear(); 511 adapter.SelectCommand.Parameters.AddRange(parameters); 512 } 513 adapter.SelectCommand.CommandType = type; 514 adapter.Fill(ds); 515 adapter.SelectCommand.Parameters.Clear(); 516 return ds; 517 } 518 } 519 } 520 #endregion 521 522 #region ExecuteDataTable +static DataTable ExecuteDataTable(string cmdText, params SqlParameter[] parameters) 523 /// <summary> 524 /// 执行一个数据表查询的T-SQL语句, 返回一个DataTable 525 /// </summary> 526 /// <param name="cmdText">要执行的T-SQL语句</param> 527 /// <param name="parameters">参数列表</param> 528 /// <returns>查询到的数据表</returns> 529 public static DataTable ExecuteDataTable(string cmdText, params SqlParameter[] parameters) 530 { 531 return ExecuteDataTable(cmdText, CommandType.Text, parameters); 532 } 533 #endregion 534 535 #region ExecuteDataTable +static DataTable ExecuteDataTable(string cmdText, CommandType type, params SqlParameter[] parameters) 536 /// <summary> 537 /// 执行一个数据表查询的T-SQL语句, 返回一个DataTable 538 /// </summary> 539 /// <param name="cmdText">要执行的T-SQL语句</param> 540 /// <param name="type">命令类型</param> 541 /// <param name="parameters">参数列表</param> 542 /// <returns>查询到的数据表</returns> 543 public static DataTable ExecuteDataTable(string cmdText, CommandType type, params SqlParameter[] parameters) 544 { 545 return ExecuteDataSet(cmdText, type, parameters).Tables[0]; 546 } 547 #endregion 548 #endregion 549 } 550 }
