ADO.net方法
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 using System; 2 using System.Collections.Generic; 3 using System.Data; 4 using System.Data.SqlClient; 5 using System.Reflection; 6 7 namespace DAL.DataUtil 8 { 9 /// <summary> 10 /// SqlServer数据访问控制类 11 /// </summary> 12 public class DataProvider 13 { 14 public string ConnectionString { get; set; } 15 16 #region 私有方法 17 18 /// <summary> 19 /// SqlCommand 对象执行SQL脚本前的准备工作 20 /// </summary> 21 /// <param name="cmd">SqlCommand 对象</param> 22 /// <param name="conn">SqlConnection 对象</param> 23 /// <param name="trans">SqlTransaction 对象</param> 24 /// <param name="cmdType">命令类型:存储过程或普通SQL脚本</param> 25 /// <param name="cmdText">SQL脚本</param> 26 /// <param name="cmdParms">SqlCommand 对象使用的 SqlParameter 参数集合</param> 27 private void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, 28 CommandType cmdType, string cmdText, SqlParameter[] cmdParms) 29 { 30 if (conn.State != ConnectionState.Open) 31 conn.Open(); 32 33 cmd.Connection = conn; 34 cmd.CommandText = cmdText; 35 cmd.CommandType = cmdType; 36 37 if (trans != null) 38 cmd.Transaction = trans; 39 40 if (cmdParms != null) 41 { 42 foreach (SqlParameter param in cmdParms) 43 cmd.Parameters.Add(param); 44 } 45 } 46 47 /// <summary> 48 /// SqlDataAdapter 对象使用前的准备工作 49 /// </summary> 50 /// <param name="adapter">SqlDataAdapter 对象</param> 51 /// <param name="conn">SqlConnection 对象</param> 52 /// <param name="cmdType">命令类型:存储过程或普通SQL脚本</param> 53 /// <param name="cmdText">SQL脚本</param> 54 /// <param name="cmdParms">SqlCommand 对象使用的 SqlParameter 参数集合</param> 55 private void PrepareAdapter(SqlDataAdapter adapter, SqlConnection conn, 56 CommandType cmdType, string cmdText, SqlParameter[] cmdParms) 57 { 58 SqlCommand cmd = new SqlCommand(cmdText, conn); 59 60 cmd.CommandType = cmdType; 61 if (cmdParms != null) 62 { 63 foreach (SqlParameter param in cmdParms) 64 cmd.Parameters.Add(param); 65 } 66 adapter.SelectCommand = cmd; 67 } 68 69 #endregion 70 71 #region 公有方法 72 73 /// <summary> 74 /// 对连接执行 SQL 语句并返回受影响的行数。 75 /// </summary> 76 /// <param name="cmdText">SQL脚本</param> 77 /// <param name="cmdType">命令类型:存储过程或普通SQL脚本</param> 78 /// <returns>受影响的行数</returns> 79 public int ExecuteNonQuery(string cmdText, CommandType cmdType) 80 { 81 return ExecuteNonQuery(cmdText, cmdType, null); 82 } 83 84 /// <summary> 85 /// 对连接执行 SQL 语句并返回受影响的行数。 86 /// </summary> 87 /// <param name="cmdText">SQL脚本</param> 88 /// <param name="cmdType">命令类型:存储过程或普通SQL脚本</param> 89 /// <param name="parameters">SqlParameter 参数集合</param> 90 /// <returns>受影响的行数</returns> 91 public int ExecuteNonQuery(string cmdText, CommandType cmdType, SqlParameter[] parameters) 92 { 93 SqlCommand cmd = new SqlCommand(); 94 int val = 0; 95 using (SqlConnection conn = new SqlConnection(ConnectionString)) 96 { 97 conn.Open(); 98 try 99 { 100 PrepareCommand(cmd, conn, null, cmdType, cmdText, parameters); 101 val = cmd.ExecuteNonQuery(); 102 if (parameters != null) 103 { 104 cmd.Parameters.Clear(); 105 } 106 } 107 catch 108 { 109 conn.Close(); 110 conn.Dispose(); 111 } 112 finally 113 { 114 if (conn.State != ConnectionState.Closed) 115 { 116 conn.Close(); 117 conn.Dispose(); 118 } 119 } 120 return val; 121 } 122 } 123 124 /// <summary> 125 /// 对连接执行多条 SQL 语句,并加入事务处理 126 /// </summary> 127 /// <param name="cmdTexts">SQL 语句数组</param> 128 public void ExecuteNonQueryWithTransaction(string[] cmdTexts) 129 { 130 SqlCommand cmd = new SqlCommand(); 131 132 using (SqlConnection conn = new SqlConnection(ConnectionString)) 133 { 134 conn.Open(); 135 SqlTransaction trans = conn.BeginTransaction(); 136 137 try 138 { 139 foreach (string sql in cmdTexts) 140 { 141 PrepareCommand(cmd, conn, trans, CommandType.Text, sql, null); 142 cmd.ExecuteNonQuery(); 143 } 144 trans.Commit(); 145 } 146 catch 147 { 148 trans.Rollback(); 149 conn.Close(); 150 conn.Dispose(); 151 } 152 finally 153 { 154 if (conn.State != ConnectionState.Closed) 155 { 156 conn.Close(); 157 conn.Dispose(); 158 } 159 } 160 } 161 } 162 163 /// <summary> 164 /// 对连接执行多条 SQL 语句,并加入事务处理 165 /// </summary> 166 /// <param name="commands">SQL命令数组。 167 /// Command 封装了 SqlCommand 对象需要的 CommandText、CommandType、SqlParameterCollection,以便分别执行每一组SQL脚本</param> 168 public void ExecuteNonQueryWithTransaction(Command[] commands) 169 { 170 SqlCommand cmd = new SqlCommand(); 171 172 using (SqlConnection conn = new SqlConnection(ConnectionString)) 173 { 174 conn.Open(); 175 SqlTransaction trans = conn.BeginTransaction(); 176 177 try 178 { 179 foreach (Command command in commands) 180 { 181 PrepareCommand(cmd, conn, trans, command.CommandType, command.CommandText, command.Parameters); 182 cmd.ExecuteNonQuery(); 183 if (command.Parameters != null) 184 { 185 cmd.Parameters.Clear(); 186 } 187 } 188 trans.Commit(); 189 } 190 catch 191 { 192 trans.Rollback(); 193 conn.Close(); 194 conn.Dispose(); 195 } 196 finally 197 { 198 if (conn.State != ConnectionState.Closed) 199 { 200 conn.Close(); 201 conn.Dispose(); 202 } 203 } 204 } 205 } 206 207 /// <summary> 208 /// 执行SQL脚本,返回查询得到的 DataReader 结果集 209 /// </summary> 210 /// <param name="cmdText">SQL脚本</param> 211 /// <param name="cmdType">命令类型:存储过程或普通SQL脚本</param> 212 /// <returns>DataReader 结果集</returns> 213 public SqlDataReader ExecuteReader(string cmdText, CommandType cmdType) 214 { 215 return ExecuteReader(cmdText, cmdType, null); 216 } 217 218 /// <summary> 219 /// 执行SQL脚本进行查询,返回得到的 DataReader 结果集 220 /// </summary> 221 /// <param name="cmdText">SQL脚本</param> 222 /// <param name="cmdType">命令类型:存储过程或普通SQL脚本</param> 223 /// <param name="parameters">SqlParameter 参数集合</param> 224 /// <returns>DataReader 结果集</returns> 225 public SqlDataReader ExecuteReader(string cmdText, CommandType cmdType, SqlParameter[] parameters) 226 { 227 SqlCommand cmd = new SqlCommand(); 228 SqlConnection conn = new SqlConnection(ConnectionString); 229 230 try 231 { 232 PrepareCommand(cmd, conn, null, cmdType, cmdText, parameters); 233 SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 234 if (parameters != null) 235 { 236 cmd.Parameters.Clear(); 237 } 238 239 return reader; 240 } 241 catch 242 { 243 conn.Close(); 244 conn.Dispose(); 245 throw; 246 } 247 finally 248 { 249 if (conn.State != ConnectionState.Closed) 250 { 251 conn.Close(); 252 conn.Dispose(); 253 } 254 } 255 } 256 257 /// <summary> 258 /// 执行查询,并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。 259 /// </summary> 260 /// <param name="cmdText">SQL脚本</param> 261 /// <param name="cmdType">命令类型:存储过程或普通SQL脚本</param> 262 /// <returns>结果集中第一行的第一列</returns> 263 public object ExecuteScalar(string cmdText, CommandType cmdType) 264 { 265 return ExecuteScalar(cmdText, cmdType, null); 266 } 267 268 /// <summary> 269 /// 执行查询,并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。 270 /// </summary> 271 /// <param name="cmdText">SQL脚本</param> 272 /// <param name="cmdType">命令类型:存储过程或普通SQL脚本</param> 273 /// <param name="parameters">SqlParameter 参数集合</param> 274 /// <returns>结果集中第一行的第一列</returns> 275 public object ExecuteScalar(string cmdText, CommandType cmdType, SqlParameter[] parameters) 276 { 277 SqlCommand cmd = new SqlCommand(); 278 object val = new object(); 279 using (SqlConnection conn = new SqlConnection(ConnectionString)) 280 { 281 conn.Open(); 282 try 283 { 284 PrepareCommand(cmd, conn, null, cmdType, cmdText, parameters); 285 val = cmd.ExecuteScalar(); 286 if (parameters != null) 287 { 288 cmd.Parameters.Clear(); 289 } 290 } 291 catch 292 { 293 conn.Close(); 294 conn.Dispose(); 295 } 296 finally 297 { 298 if (conn.State != ConnectionState.Closed) 299 { 300 conn.Close(); 301 conn.Dispose(); 302 } 303 } 304 return val; 305 } 306 } 307 308 /// <summary> 309 /// 执行查询,将查询结果填充到 DataSet 并返回 310 /// </summary> 311 /// <param name="cmdText">SQL脚本</param> 312 /// <param name="cmdType">命令类型:存储过程或普通SQL脚本</param> 313 /// <returns>查询结果集</returns> 314 public DataSet FillDataSet(string cmdText, CommandType cmdType) 315 { 316 return FillDataSet(cmdText, cmdType, null); 317 } 318 319 /// <summary> 320 /// 执行查询,将查询结果填充到 DataSet 并返回 321 /// </summary> 322 /// <param name="cmdText">SQL脚本</param> 323 /// <param name="cmdType">命令类型:存储过程或普通SQL脚本</param> 324 /// <param name="parameters">SqlParameter 参数集合</param> 325 /// <returns>查询结果集</returns> 326 public DataSet FillDataSet(string cmdText, CommandType cmdType, SqlParameter[] parameters) 327 { 328 SqlDataAdapter adapter = new SqlDataAdapter(); 329 DataSet dataSet = new DataSet(); 330 using (SqlConnection conn = new SqlConnection(ConnectionString)) 331 { 332 conn.Open(); 333 try 334 { 335 PrepareAdapter(adapter, conn, cmdType, cmdText, parameters); 336 adapter.Fill(dataSet); 337 } 338 catch 339 { 340 conn.Close(); 341 conn.Dispose(); 342 } 343 finally 344 { 345 if (conn.State != ConnectionState.Closed) 346 { 347 conn.Close(); 348 conn.Dispose(); 349 } 350 } 351 return dataSet; 352 } 353 } 354 355 /// <summary> 356 /// 执行查询,将查询结果填充到 DataTable 并返回 357 /// </summary> 358 /// <param name="cmdText">SQL脚本</param> 359 /// <param name="cmdType">命令类型:存储过程或普通SQL脚本</param> 360 /// <returns>查询结果集</returns> 361 public DataTable FillDataTable(string cmdText, CommandType cmdType) 362 { 363 return FillDataTable(cmdText, cmdType, null); 364 } 365 366 /// <summary> 367 /// 执行查询,将查询结果填充到 DataTable 并返回 368 /// </summary> 369 /// <param name="cmdText">SQL脚本</param> 370 /// <param name="cmdType">命令类型:存储过程或普通SQL脚本</param> 371 /// <param name="parameters">SqlParameter 参数集合</param> 372 /// <returns>查询结果集</returns> 373 public DataTable FillDataTable(string cmdText, CommandType cmdType, SqlParameter[] parameters) 374 { 375 SqlDataAdapter adapter = new SqlDataAdapter(); 376 DataTable table = new DataTable(); 377 using (SqlConnection conn = new SqlConnection(ConnectionString)) 378 { 379 conn.Open(); 380 try 381 { 382 PrepareAdapter(adapter, conn, cmdType, cmdText, parameters); 383 adapter.Fill(table); 384 } 385 catch 386 { 387 conn.Close(); 388 conn.Dispose(); 389 } 390 finally 391 { 392 if (conn.State != ConnectionState.Closed) 393 { 394 conn.Close(); 395 conn.Dispose(); 396 } 397 } 398 return table; 399 } 400 } 401 402 /// <summary> 403 /// 执行只返回一条记录的查询,把返回的记录反射成一个实体对象,实体对象类型由传入的类型决定。 404 /// 传入的类型和返回类型要一致 405 /// </summary> 406 /// <typeparam name="T">要反射封装的对象实体类型。必须和返回类型一致</typeparam> 407 /// <param name="cmdText">SQL脚本</param> 408 /// <param name="cmdType">命令类型:存储过程或普通SQL脚本</param> 409 /// <returns>封装好的对象实体。必须和传入参数类型一致</returns> 410 public T ReflectObject<T>(string cmdText, CommandType cmdType) 411 { 412 return ReflectObject<T>(cmdText, cmdType, null); 413 } 414 415 /// <summary> 416 /// 执行只返回一条记录的查询,把返回的记录反射成一个实体对象,实体对象类型由传入的类型决定。 417 /// 传入的类型和返回类型要一致 418 /// </summary> 419 /// <typeparam name="T">要反射封装的对象实体类型。必须和返回类型一致</typeparam> 420 /// <param name="cmdText">SQL脚本</param> 421 /// <param name="cmdType">命令类型:存储过程或普通SQL脚本</param> 422 /// <param name="parameters">SqlParameter 参数集合</param> 423 /// <returns>封装好的对象实体。必须和传入参数类型一致</returns> 424 public T ReflectObject<T>(string cmdText, CommandType cmdType, SqlParameter[] parameters) 425 { 426 SqlCommand cmd = new SqlCommand(); 427 T obj = default(T); 428 429 using (SqlConnection conn = new SqlConnection(ConnectionString)) 430 { 431 PrepareCommand(cmd, conn, null, cmdType, cmdText, parameters); 432 433 using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleRow)) 434 { 435 if (reader.Read()) 436 { 437 obj = (T)Activator.CreateInstance(typeof(T)); 438 Type type = obj.GetType(); 439 440 for (int i = 0; i < reader.FieldCount; i++) 441 { 442 if (!reader.IsDBNull(i)) 443 { 444 try 445 { 446 type.InvokeMember(reader.GetName(i), BindingFlags.Default | BindingFlags.SetProperty, null, obj, new object[] { reader.GetValue(i) }); 447 } 448 catch (MissingMemberException exception) 449 { 450 //Column/Property names don't match, thus throwing an exception. Ignored 451 System.Diagnostics.Debug.WriteLine(exception.Message); 452 } 453 } 454 } 455 456 reader.Close(); 457 } 458 } 459 if (conn.State != ConnectionState.Closed) 460 { 461 conn.Close(); 462 } 463 } 464 465 return obj; 466 } 467 468 /// <summary> 469 /// 执行查询,把返回的记录集反射成一个实体对象集合,实体对象类型由传入的类型决定。 470 /// 传入的类型和返回类型要一致 471 /// </summary> 472 /// <typeparam name="T">要反射封装的对象实体类型。必须和返回类型一致</typeparam> 473 /// <param name="cmdText">SQL脚本</param> 474 /// <param name="cmdType">命令类型:存储过程或普通SQL脚本</param> 475 /// <returns>封装好的实体集合。必须和传入参数类型一致</returns> 476 public List<T> ReflectCollection<T>(string cmdText, CommandType cmdType) 477 { 478 return ReflectCollection<T>(cmdText, cmdType, null); 479 } 480 481 /// <summary> 482 /// 执行查询,把返回的记录集反射成一个实体对象集合,实体对象类型由传入的类型决定。 483 /// 传入的类型和返回类型要一致 484 /// </summary> 485 /// <typeparam name="T">要反射封装的对象实体类型。必须和返回类型一致</typeparam> 486 /// <param name="cmdText">SQL脚本</param> 487 /// <param name="cmdType">命令类型:存储过程或普通SQL脚本</param> 488 /// <param name="parameters">SqlParameter 参数集合</param> 489 /// <returns>封装好的实体集合。必须和传入参数类型一致</returns> 490 public List<T> ReflectCollection<T>(string cmdText, CommandType cmdType, SqlParameter[] parameters) 491 { 492 SqlCommand cmd = new SqlCommand(); 493 494 using (SqlConnection conn = new SqlConnection(ConnectionString)) 495 { 496 PrepareCommand(cmd, conn, null, cmdType, cmdText, parameters); 497 498 using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) 499 { 500 List<T> objList = new List<T>(); 501 502 while (reader.Read()) 503 { 504 T obj = (T)Activator.CreateInstance(typeof(T)); 505 Type type = obj.GetType(); 506 507 for (int i = 0; i < reader.FieldCount; i++) 508 { 509 if (!reader.IsDBNull(i)) 510 { 511 try 512 { 513 type.InvokeMember(reader.GetName(i), BindingFlags.Default | BindingFlags.SetProperty, null, obj, new object[] { reader.GetValue(i) }); 514 } 515 catch (MissingMemberException exception) 516 { 517 //Column/Property names don't match, thus throwing an exception. Ignored 518 System.Diagnostics.Debug.WriteLine(exception.Message); 519 } 520 } 521 } 522 objList.Add(obj); 523 } 524 525 reader.Close(); 526 if (conn.State != ConnectionState.Closed) 527 { 528 conn.Close(); 529 } 530 return objList; 531 } 532 } 533 } 534 535 #endregion 536 } 537 }