Sql辅助类
封装了一个(Sql.cs)类 让Sql参数化,事务,返回多种结果集 的写法更简洁和优雅. 虽然这代码不一定适合所有项目, 但可以提供给各位新手,老手...学习和吐槽用.
类库结构, 我简单介绍一下[ CBO.cs ,Null.cs,ObjectMappingInfo.cs] 是dnn的类库里面借的 主要功能是将数据集转换为对象集或对象. ConvertUtil.cs 包含了数据类型转换. 其他几个类实现了一个简单的工厂模式用于创建DbHelper. 这里我主要介绍一下Sql.cs类,也是这个类库的核心 ,调用方式仿Jquery的链式(个人比较喜欢这样的写法).下面是代码
1 //create by ying 2 #region "命名空间" 3 4 using See.Common.Utilities; 5 using System; 6 using System.Collections.Generic; 7 using System.Data; 8 using System.Diagnostics; 9 using System.Linq; 10 using System.Text; 11 using System.Text.RegularExpressions; 12 13 #endregion 14 15 namespace See.Data { 16 /// <summary> 17 /// Sql脚本执行工具类,能返回多类型的结果<br/> 18 /// <para>1.脚本参数化,支持输出参数,支持数据库事务</para> 19 /// <para>2.不同数据库类型的占位符自动切换(仅支持T-Sql)</para> 20 /// <para>3.输出强类型的查询结果,支持返回多结果集</para> 21 /// <para>4.采用链式对象调用方式,让编码简洁优雅</para> 22 /// </summary> 23 /// <remarks> 24 /// <para>1.使用OpenNext方法时在使用完成必须执行CloseNext,已避免不要的数据连接开着占用资源</para> 25 /// <para>2.如果使用某一数据库特有的语法时,只切换占位符是没用的,只能重新写过SQL - -!</para> 26 /// </remarks> 27 public class Sql : IDisposable { 28 29 #region "私有成员" 30 31 private StringBuilder _SqlString; 32 33 private CommandType _CommandType; 34 35 private DbHelper _DbHelper; 36 37 private IDataReader _DataReader; 38 39 private IDbTransaction _DbTransaction; 40 41 private List<IDbDataParameter> _Params = new List<IDbDataParameter>(); 42 43 private bool _CanNextResult = false; 44 45 private bool _CanCloseReader = false; 46 47 private bool _AutoCloseConnection = true; 48 49 #endregion 50 51 #region "构造函数" 52 /// <summary> 53 /// 构造函数 54 /// </summary> 55 /// <param name="sql"></param> 56 /// <param name="type"></param> 57 /// <param name="autoCloseConnection"></param> 58 private Sql(string sql, CommandType type, bool autoCloseConnection) 59 : this(sql, type, autoCloseConnection, DbFactory.Instance.CurrentDbType) { 60 } 61 /// <summary> 62 /// 构造函数 63 /// </summary> 64 /// <param name="sql"></param> 65 /// <param name="type"></param> 66 /// <param name="autoCloseConnection"></param> 67 /// <param name="dbType"></param> 68 private Sql(string sql, CommandType type, bool autoCloseConnection, DbType dbType) { 69 _SqlString = new StringBuilder(sql); 70 _CommandType = type; 71 _DbHelper = DbFactory.Instance.CreateDbHelper(dbType); 72 _AutoCloseConnection = autoCloseConnection; 73 } 74 /// <summary> 75 /// 构造函数 76 /// </summary> 77 /// <param name="sql">表示Sql语句</param> 78 /// <param name="type"></param> 79 /// <param name="autoCloseConnection"></param> 80 /// <param name="helperType"></param> 81 private Sql(string sql, CommandType type, bool autoCloseConnection, Type helperType) { 82 _SqlString = new StringBuilder(sql); 83 _CommandType = type; 84 _AutoCloseConnection = autoCloseConnection; 85 _DbHelper = DbFactory.Instance.CreateDbHelper(helperType); 86 } 87 88 #endregion 89 90 #region "公有属性" 91 /// <summary> 92 /// 获取当前Sql脚本 93 /// </summary> 94 public string SqlText { 95 get { 96 return AutoSwitchPlaceholder(_SqlString.ToString()); 97 } 98 } 99 /// <summary> 100 /// 获取运行时Sql脚本 101 /// </summary> 102 public string RuntimeSqlText { 103 get { 104 return _DbHelper.GetRuntimeSqlText(_SqlString.ToString(), _Params); 105 } 106 } 107 108 #endregion 109 110 #region "公有方法" 111 /// <summary> 112 /// 创建一个Sql对象 113 /// </summary> 114 /// <param name="sql">Sql语句</param> 115 /// <returns><see cref="Sql"/></returns> 116 public static Sql Create(string sql) { 117 return Create(sql, CommandType.Text, true); 118 } 119 /// <summary> 120 /// 创建一个Sql对象 121 /// </summary> 122 /// <param name="sql">Sql语句</param> 123 /// <param name="autoCloseConnection">自动关闭连接</param> 124 /// <returns></returns> 125 public static Sql Create(string sql, bool autoCloseConnection) { 126 return Create(sql, CommandType.Text, autoCloseConnection); 127 } 128 /// <summary> 129 /// 创建一个Sql对象 130 /// </summary> 131 /// <param name="sql">Sql语句</param> 132 /// <param name="type"><see cref="System.Data.CommandType"/></param> 133 /// <returns></returns> 134 public static Sql Create(string sql, CommandType type) { 135 return new Sql(sql, type, true); 136 } 137 /// <summary> 138 /// 创建一个Sql对象 139 /// </summary> 140 /// <param name="sql"></param> 141 /// <param name="type"></param> 142 /// <param name="autoCloseConnection"></param> 143 /// <returns></returns> 144 public static Sql Create(string sql, CommandType type, bool autoCloseConnection) { 145 return new Sql(sql, type, autoCloseConnection); 146 } 147 /// <summary> 148 /// 创建一个Sql对象 149 /// </summary> 150 /// <param name="sql"></param> 151 /// <param name="type"></param> 152 /// <param name="autoCloseConnection"></param> 153 /// <param name="dbType"></param> 154 /// <returns></returns> 155 public static Sql Create(string sql, CommandType type, bool autoCloseConnection, DbType dbType) { 156 return new Sql(sql, type, autoCloseConnection, dbType); 157 } 158 /// <summary> 159 /// 创建一个Sql对象 160 /// </summary> 161 /// <param name="sql"></param> 162 /// <param name="type"></param> 163 /// <param name="autoCloseConnection"></param> 164 /// <param name="helperType"></param> 165 /// <returns></returns> 166 public static Sql Create(string sql, CommandType type, bool autoCloseConnection, Type helperType) { 167 return new Sql(sql, type, autoCloseConnection, helperType); 168 } 169 /// <summary> 170 /// 创建一个Sql对象 171 /// </summary> 172 /// <typeparam name="HelperType"></typeparam> 173 /// <param name="sql"></param> 174 /// <param name="type"></param> 175 /// <param name="autoCloseConnection"></param> 176 /// <returns></returns> 177 public static Sql Create<HelperType>(string sql, CommandType type, bool autoCloseConnection) { 178 return new Sql(sql, type, autoCloseConnection, typeof(HelperType)); 179 } 180 /// <summary> 181 /// 设置Sql语句,此操作将替换原有Sql语句和清空参数 182 /// </summary> 183 /// <param name="sql">新的Sql语句</param> 184 /// <returns></returns> 185 public Sql SetCommand(string sql) { 186 SetCommand(sql, CommandType.Text); 187 return this; 188 } 189 /// <summary> 190 /// 设置Sql语句,此操作将替换原有Sql语句和清空参数 191 /// </summary> 192 /// <param name="sql">新的Sql语句</param> 193 /// <param name="type">参数类型</param> 194 /// <returns></returns> 195 public Sql SetCommand(string sql, CommandType type) { 196 _SqlString = new StringBuilder(sql); 197 _CommandType = type; 198 _Params.Clear(); 199 return this; 200 } 201 /// <summary> 202 /// 追加Sql语句 203 /// </summary> 204 /// <param name="sql"></param> 205 /// <param name="args"></param> 206 /// <returns></returns> 207 public Sql Append(string sql, params object[] args) { 208 _SqlString.AppendFormat(sql, args); 209 return this; 210 } 211 /// <summary> 212 /// 追加Sql语句,并自动在结尾处加上';' 213 /// </summary> 214 /// <param name="sql"></param> 215 /// <param name="args"></param> 216 /// <returns></returns> 217 public Sql AppendLine(string sql, params object[] args) { 218 _SqlString.Append(";"); 219 _SqlString.AppendLine(); 220 _SqlString.AppendFormat(sql, args); 221 return this; 222 } 223 224 public Sql Set(string paramName, object value) { 225 Set(paramName, value, System.Data.DbType.String, 0, ParameterDirection.Input); 226 return this; 227 } 228 /// <summary> 229 /// 设置参数 230 /// </summary> 231 /// <param name="paramName">参数名称</param> 232 /// <param name="value">参数值</param> 233 /// <param name="dbType">数据类型<see cref="System.Data.DbType"/></param> 234 /// <param name="size">参数的大小</param> 235 /// <param name="direction">参数类型<see cref="ParameterDirection"/></param> 236 /// <returns><see cref="Sql"/></returns> 237 public Sql Set(string paramName, object value, System.Data.DbType dbType, int size, ParameterDirection direction) { 238 var parameter = _DbHelper.CreateDataParameter(); 239 parameter.ParameterName = paramName; 240 parameter.Value = value; 241 parameter.Direction = direction; 242 parameter.DbType = dbType; 243 parameter.Size = size; 244 Set(parameter); 245 return this; 246 } 247 /// <summary> 248 /// 设置参数 249 /// </summary> 250 /// <param name="parameters">参数列表</param> 251 /// <returns></returns> 252 public Sql Set(params IDbDataParameter[] parameters) { 253 _Params.AddRange(parameters); 254 return this; 255 } 256 /// <summary> 257 /// 设置参数 258 /// </summary> 259 /// <param name="parameters"></param> 260 /// <returns></returns> 261 public Sql Set(IDictionary<string, object> parameters) { 262 263 foreach (var item in parameters) { 264 Set(item.Key, parameters[item.Key]); 265 } 266 return this; 267 } 268 /// <summary> 269 /// 设置参数,此方法仅在OleDB连接中有效,参数名默认是"?" 270 /// </summary> 271 /// <param name="values">参数值</param> 272 /// <returns>Sql对象</returns> 273 public Sql SetOle(params object[] values) { 274 foreach (var item in values) { 275 Set("?", item); 276 } 277 return this; 278 } 279 /// <summary> 280 /// 设置一个输出参数 281 /// </summary> 282 /// <param name="paramName">参数名</param> 283 /// <param name="dbType"></param> 284 /// <param name="size"></param> 285 /// <returns></returns> 286 public Sql SetOutput(string paramName, System.Data.DbType dbType, int size) { 287 Set(paramName, null, dbType, size, ParameterDirection.Output); 288 return this; 289 } 290 /// <summary> 291 /// 移除参数集合中指定参数名的一个参数 292 /// </summary> 293 /// <param name="paramName">参数名</param> 294 /// <returns></returns> 295 public Sql Remove(string paramName) { 296 _Params.RemoveAll(t => t.ParameterName == paramName); 297 return this; 298 } 299 /// <summary> 300 /// 开启多结果集模式 301 /// </summary> 302 /// <returns></returns> 303 public Sql BeginNextReuslt() { 304 _CanNextResult = true; 305 306 return this; 307 } 308 /// <summary> 309 /// 关闭多结果集模式 310 /// </summary> 311 /// <returns></returns> 312 public Sql EndNextResult() { 313 _CanCloseReader = true; 314 if (_DataReader != null && !_DataReader.IsClosed) { 315 _DataReader.Close(); 316 } 317 return this; 318 } 319 /// <summary> 320 /// 关闭连接 321 /// </summary> 322 /// <returns></returns> 323 public Sql Close() { 324 EndNextResult(); 325 _DbHelper.CloseConnection(); 326 return this; 327 } 328 /// <summary> 329 /// 开启事务 330 /// </summary> 331 /// <returns></returns> 332 public Sql BeginTransaction() { 333 _AutoCloseConnection = false; 334 if (_DbTransaction == null) { 335 _DbTransaction = _DbHelper.DbConnection.BeginTransaction(); 336 } 337 return this; 338 } 339 /// <summary> 340 /// 提交事务 341 /// </summary> 342 /// <returns></returns> 343 public Sql Commit() { 344 if (_DbTransaction == null) { 345 throw new ArgumentNullException("提交事务失败,当前事务为空."); 346 } 347 try { 348 _DbTransaction.Commit(); 349 } catch (Exception) { 350 _DbTransaction.Rollback(); 351 throw; 352 } finally { 353 _DbHelper.CloseConnection(); 354 } 355 return this; 356 } 357 /// <summary> 358 /// 执行语句,并返回影响行数 359 /// </summary> 360 /// <returns>影响行数</returns> 361 public int ForExecuteAndReturnAffectedRow() { 362 try { 363 WirteDebugMessage(); 364 int result = _DbHelper.ExecuteNonQuery(_DbTransaction, SqlText, _CommandType, _AutoCloseConnection, _Params.ToArray()); 365 return result; 366 } catch (Exception ex) { 367 throw new Exception(string.Format("执行SQL语句时发生了错误,当前SQL语句:\"{0}\",{1}", RuntimeSqlText, ex.Message), ex); 368 } 369 } 370 /// <summary> 371 /// 执行Sql语句 372 /// </summary> 373 /// <returns>当前Sql对象</returns> 374 public Sql ForExecute() { 375 ForExecuteAndReturnAffectedRow(); 376 return this; 377 } 378 /// <summary> 379 /// 执行Sql语句 380 /// </summary> 381 /// <param name="affectedCount">输出影响行数</param> 382 /// <returns>当前Sql对象</returns> 383 public Sql ForExecute(ref int affectedCount) { 384 affectedCount = ForExecuteAndReturnAffectedRow(); 385 return this; 386 } 387 /// <summary> 388 /// 执行查询 389 /// </summary> 390 /// <param name="affectedRowAction">接收返回的影响行数方法</param> 391 /// <returns></returns> 392 public Sql ForExecute(Action<int> affectedRowAction) { 393 var affectCount = ForExecuteAndReturnAffectedRow(); 394 if (affectedRowAction != null) { 395 affectedRowAction(affectCount); 396 } 397 return this; 398 } 399 /// <summary> 400 /// 执行查询,返回一个数据集 401 /// </summary> 402 /// <returns>DataSet数据集</returns> 403 public DataSet ForDataSet() { 404 try { 405 WirteDebugMessage(); 406 return _DbHelper.ExecuteDataSet(_DbTransaction, SqlText, _CommandType, _AutoCloseConnection, _Params.ToArray()); 407 } catch (Exception ex) { 408 throw new Exception(string.Format("执行查询时发生了错误,当前SQL语句:\"{0}\",{1}", RuntimeSqlText, ex.Message), ex); 409 } 410 } 411 /// <summary> 412 /// 执行查询,并将结果通过引用类型的参数来传递 413 /// </summary> 414 /// <param name="dataSet">接收返回结果的数据集</param> 415 /// <returns></returns> 416 public Sql ForDataSet(ref DataSet dataSet) { 417 dataSet = ForDataSet(); 418 return this; 419 } 420 /// <summary> 421 /// 执行查询,并将结果通过引用类型的参数来传递 422 /// </summary> 423 /// <param name="valueAction"></param> 424 /// <returns></returns> 425 public Sql ForDataSet(Action<DataSet> valueAction) { 426 var dataSet = ForDataSet(); 427 if (valueAction != null) { 428 valueAction(dataSet); 429 } 430 return this; 431 } 432 /// <summary> 433 /// 执行查询,并返当前数据集中的第一个DataTable 434 /// </summary> 435 /// <returns>查询结果DataTable</returns> 436 public DataTable ForDataTable() { 437 var dataSet = ForDataSet(); 438 if (dataSet.Tables.Count > 0) { 439 return dataSet.Tables[0]; 440 } 441 return null; 442 } 443 /// <summary> 444 /// 执行查询,并通过参数形式返当前数据集中的第一个DataTable 445 /// </summary> 446 /// <param name="dataTable">查询结果DataTable</param> 447 /// <returns></returns> 448 public Sql ForDataTable(ref DataTable dataTable) { 449 dataTable = ForDataTable(); 450 return this; 451 } 452 /// <summary> 453 /// 执行查询并通过委托方式传递 数据集中的第一个DataTable 454 /// </summary> 455 /// <param name="valueAction"></param> 456 /// <returns></returns> 457 public Sql ForDataTable(Action<DataTable> valueAction) { 458 var dataTable = ForDataTable(); 459 if (valueAction != null) { 460 valueAction(dataTable); 461 } 462 return this; 463 } 464 /// <summary> 465 /// 执行查询,并返回当前数据集中第一DataTable的第一行 466 /// </summary> 467 /// <returns>DataRow数据行</returns> 468 public DataRow ForFirstDataRow() { 469 return ForFirstDataRow(true); 470 } 471 /// <summary> 472 /// 执行查询,并返回当前数据集中第一DataTable的第一行 473 /// </summary> 474 /// <param name="isClone">是否克隆第一行数据</param> 475 /// <returns></returns> 476 public DataRow ForFirstDataRow(bool isClone) { 477 var dataTable = ForDataTable(); 478 if (dataTable != null && dataTable.Rows.Count > 0) { 479 if (isClone) { 480 return dataTable.AsEnumerable().FirstOrDefault();//此处是会复制一个DataRow的副本(Clone) 481 } 482 return dataTable.Rows[0]; 483 } 484 return null; 485 } 486 /// <summary> 487 /// 执行查询并返回第条一条记录的第一个值(不能再多结果集中使用) 488 /// </summary> 489 /// <typeparam name="T">值类型</typeparam> 490 /// <returns></returns> 491 public T ForScalar<T>() { 492 return ForScalar<T>(default(T)); 493 } 494 /// <summary> 495 /// 执行查询并返回第条一条记录的第一个值(不能再多结果集中使用) 496 /// </summary> 497 /// <typeparam name="T">值类型</typeparam> 498 /// <param name="defaultValue">默认值</param> 499 /// <returns></returns> 500 public T ForScalar<T>(T defaultValue) { 501 try { 502 WirteDebugMessage(); 503 var result = _DbHelper.ExecuteScalar(_DbTransaction, SqlText, _CommandType, _AutoCloseConnection, _Params.ToArray()); 504 return ConvertUtil.To<T>(result, defaultValue); 505 506 } catch (Exception ex) { 507 throw new Exception(string.Format("执行查询时发生了错误,当前SQL语句:\"{0}\",{1}", RuntimeSqlText, ex.Message), ex); 508 } 509 } 510 /// <summary> 511 /// 执行查询并返回第条一条记录的第一个值(不能再多结果集中使用) 512 /// </summary> 513 /// <typeparam name="T">返回值类型</typeparam> 514 /// <param name="value">返回引用类型的值</param> 515 /// <returns></returns> 516 public Sql ForScalar<T>(ref T value) { 517 value = ForScalar<T>(default(T)); 518 return this; 519 } 520 /// <summary> 521 /// 执行查询并返回第条一条记录的第一个值(不能再多结果集中使用) 522 /// </summary> 523 /// <typeparam name="T">返回值类型</typeparam> 524 /// <param name="value">返回引用类型的值</param> 525 /// <param name="defaultValue">默认值</param> 526 /// <returns></returns> 527 public Sql ForScalar<T>(ref T value, T defaultValue) { 528 value = ForScalar<T>(defaultValue); 529 return this; 530 } 531 /// <summary> 532 /// 执行查询并将查询结果填充至对象中 533 /// </summary> 534 /// <typeparam name="T">返回值类型</typeparam> 535 /// <returns></returns> 536 public T ForObject<T>() where T : class ,new() {//此处加了泛型约束,限制类型T必须是一个类且能被实例化 537 return ExecuteReader<T>((dataReader) => { 538 if (dataReader.Read()) { 539 return CBO.FillObject<T>(dataReader, false); 540 } 541 return null; 542 }); 543 } 544 /// <summary> 545 /// 执行查询并将查询结果填充至对象中 546 /// </summary> 547 /// <typeparam name="T"></typeparam> 548 /// <param name="value"></param> 549 /// <returns></returns> 550 public Sql ForObject<T>(ref T value) where T : class ,new() { 551 value = ForObject<T>(); 552 return this; 553 } 554 /// <summary> 555 /// 执行查询并将查询结果填充至对象中 556 /// </summary> 557 /// <typeparam name="T"></typeparam> 558 /// <param name="valueFill"></param> 559 /// <returns></returns> 560 public Sql ForObject<T>(Action<T> valueFill) where T : class ,new() { 561 if (valueFill != null) { 562 valueFill(ForObject<T>()); 563 } 564 return this; 565 } 566 /// <summary> 567 /// 执行查询返回第条一条记录的第一个值,并转换为指定的类型. 568 /// 此方法类似于ForScalar,不过这个方法能在多结果集中使用,ForScalar则不能 569 /// </summary> 570 /// <typeparam name="T">数据类型</typeparam> 571 /// <returns>返回值</returns> 572 public T ForValue<T>() { 573 return ForValue(default(T)); 574 } 575 /// <summary> 576 /// 执行查询返回第条一条记录的第一个值,并转换为指定的类型. 577 /// 此方法类似于ForScalar,不过这个方法能在多结果集中使用,ForScalar则不能 578 /// </summary> 579 /// <typeparam name="T">数据类型</typeparam> 580 /// <param name="defaultValue">填充无效时的默认值</param> 581 /// <returns>返回值</returns> 582 public T ForValue<T>(T defaultValue) { 583 return ExecuteReader<T>((dataReader) => { 584 if (dataReader.Read()) { 585 return ConvertUtil.To<T>(dataReader[0], defaultValue); 586 } 587 return defaultValue; 588 }); 589 } 590 /// <summary> 591 /// 执行查询返回第条一条记录的第一个值,并转换为指定的类型. 592 /// 此方法类似于ForScalar,不过这个方法能在多结果集中使用,ForScalar则不能 593 /// </summary> 594 /// <typeparam name="T"></typeparam> 595 /// <param name="result"></param> 596 /// <returns></returns> 597 public Sql ForValue<T>(ref T result) { 598 result = ForValue<T>(default(T)); 599 return this; 600 } 601 /// <summary> 602 /// 执行查询返回第条一条记录的第一个值,并转换为指定的类型. 603 /// 此方法类似于ForScalar,不过这个方法能在多结果集中使用,ForScalar则不能 604 /// </summary> 605 /// <typeparam name="T"></typeparam> 606 /// <param name="result"></param> 607 /// <param name="defaultValue"></param> 608 /// <returns></returns> 609 public Sql ForValue<T>(ref T result, T defaultValue) { 610 result = ForValue<T>(defaultValue); 611 return this; 612 } 613 /// <summary> 614 /// 执行查询返回第条一条记录的第一个值,并转换为指定的类型. 615 /// 此方法类似于ForScalar,不过这个方法能在多结果集中使用,ForScalar则不能 616 /// </summary> 617 /// <typeparam name="T"></typeparam> 618 /// <param name="valueAction"></param> 619 /// <returns></returns> 620 public Sql ForValue<T>(Action<T> valueAction) { 621 if (valueAction != null) { 622 valueAction(ForValue<T>()); 623 } 624 return this; 625 } 626 /// <summary> 627 /// 执行查询返回第条一条记录的第一个值,并转换为指定的类型. 628 /// 此方法类似于ForScalar,不过这个方法能在多结果集中使用,ForScalar则不能 629 /// </summary> 630 /// <typeparam name="T"></typeparam> 631 /// <param name="valueAction"></param> 632 /// <param name="defaultValue"></param> 633 /// <returns></returns> 634 public Sql ForValue<T>(Action<T> valueAction, T defaultValue) { 635 if (valueAction != null) { 636 valueAction(ForValue<T>(defaultValue)); 637 } 638 return this; 639 } 640 /// <summary> 641 /// 执行查询并将数据填充值对象集合中 642 /// </summary> 643 /// <typeparam name="T">要填充的对象类型</typeparam> 644 /// <returns>对象<![CDATA[List<T>]]>集合</returns> 645 public List<T> ForList<T>() where T : class ,new() {//此处加了泛型约束,限制T必须是一个类且能被实例化 646 return ExecuteReader<List<T>>((dataReader) => { 647 return (List<T>)CBO.FillCollection<T>(dataReader, new List<T>(), false); 648 }); 649 } 650 /// <summary> 651 /// 执行查询并将数据填充值对象集合中 652 /// </summary> 653 /// <typeparam name="T"></typeparam> 654 /// <param name="resultCollection"></param> 655 /// <returns></returns> 656 public Sql ForList<T>(ref List<T> resultCollection) where T : class ,new() { 657 resultCollection = ForList<T>(); 658 return this; 659 } 660 /// <summary> 661 /// 执行查询并将数据填充值对象集合中 662 /// </summary> 663 /// <typeparam name="T"></typeparam> 664 /// <param name="valueAction"></param> 665 /// <returns></returns> 666 public Sql ForList<T>(Action<List<T>> valueAction) where T : class ,new() { 667 if (valueAction != null) { 668 valueAction(ForList<T>()); 669 } 670 return this; 671 } 672 /// <summary> 673 /// 执行数据读取 674 /// </summary> 675 /// <typeparam name="T">返回结果类型</typeparam> 676 /// <param name="populatedWithData">填充数据方法的委托</param> 677 /// <returns>返回当前结果集</returns> 678 private T ExecuteReader<T>(Func<IDataReader, T> populatedWithData) { 679 if (_CanNextResult && _DataReader != null && !_DataReader.IsClosed) { 680 _DataReader.NextResult(); 681 } else { 682 WirteDebugMessage(); 683 _DataReader = _DbHelper.ExecuteReader(_DbTransaction, SqlText, _CommandType, _Params.ToArray()); 684 } 685 try { 686 return populatedWithData(_DataReader); 687 } catch (Exception ex) { 688 throw new Exception(string.Format("执行查询时发生了错误,当前SQL语句:\"{0}\",{1}", RuntimeSqlText, ex.Message), ex); 689 } finally { 690 if (_CanCloseReader) { 691 CBO.CloseDataReader(_DataReader, true); 692 _CanNextResult = false; 693 } 694 } 695 } 696 /// <summary> 697 /// 获取输出参数 698 /// </summary> 699 /// <typeparam name="T">输出参数类型</typeparam> 700 /// <param name="paramName">输出参数名称</param> 701 /// <returns>返回值</returns> 702 public T ForOutput<T>(string paramName) { 703 return ForOutput(paramName, default(T)); 704 } 705 /// <summary> 706 /// 获取输出参数 707 /// </summary> 708 /// <typeparam name="T"></typeparam> 709 /// <param name="paramName"></param> 710 /// <param name="result"></param> 711 /// <returns></returns> 712 public Sql ForOutput<T>(string paramName, ref T result) { 713 result = ForOutput(paramName, default(T)); 714 return this; 715 } 716 /// <summary> 717 /// 获取输出参数 718 /// </summary> 719 /// <typeparam name="T">输出参数类型</typeparam> 720 /// <param name="paramName">输出参数名称</param> 721 /// <param name="defaultValue">参数默认值</param> 722 /// <returns>返回值</returns> 723 public T ForOutput<T>(string paramName, T defaultValue) { 724 var parameter = _Params.Find(t => t.ParameterName == paramName); 725 return ConvertUtil.To(parameter.Value, defaultValue); 726 } 727 /// <summary> 728 /// 获取输出参数 729 /// </summary> 730 /// <typeparam name="T">输出参数类型</typeparam> 731 /// <param name="paramName">输出参数名称</param> 732 /// <param name="result">引用类型结果</param> 733 /// <param name="defaultValue">参数默认值</param> 734 /// <returns></returns> 735 public Sql ForOutput<T>(string paramName, ref T result, T defaultValue) { 736 result = ForOutput(paramName, defaultValue); 737 return this; 738 } 739 740 #region "接口方法" 741 742 public void Dispose() { 743 if (_DataReader != null && !_DataReader.IsClosed) { 744 _DataReader.Close(); 745 _DataReader.Dispose(); 746 } 747 } 748 749 #endregion 750 751 #endregion 752 753 #region "私有方法" 754 /// <summary> 755 /// 自动切换占位符,仅在没有特殊语法的Sql切换时有效 756 /// </summary> 757 /// <param name="sql">Sql脚本</param> 758 /// <returns>替换占位符后的Sql脚本</returns> 759 private string AutoSwitchPlaceholder(string sql) { 760 761 switch (DbFactory.Instance.CurrentDbType) { 762 case DbType.SqlServer: 763 sql = Regex.Replace(sql, @":([\w]+)", "@$1"); 764 break; 765 case DbType.Oracle: 766 sql = Regex.Replace(sql, @"@([\w]+)", ":$1"); 767 break; 768 case DbType.OleDb: 769 sql = Regex.Replace(sql, @"@([\w]+)|:([\w]+)", "?"); 770 break; 771 case DbType.MySql: 772 sql = Regex.Replace(sql, @"@([\w]+)|:([\w]+)", "?$1"); 773 break; 774 } 775 return sql; 776 } 777 /// <summary> 778 /// 输出调试信息 779 /// </summary> 780 private void WirteDebugMessage() { 781 Debug.WriteLine("当前执行SQL:" + RuntimeSqlText); 782 } 783 784 #endregion 785 786 } 787 }
Sql.cs测试代码
1 using System; 2 using Microsoft.VisualStudio.TestTools.UnitTesting; 3 using See.Data; 4 using See.Test.Entites; 5 using System.Linq; 6 7 namespace See.Test { 8 /// <summary> 9 /// 修改App.config对应的配置,切换不同的数据库测试 10 /// </summary> 11 [TestClass] 12 public class DbHelperFixture { 13 14 /// <summary> 15 /// 执行查询并返回DataTable 16 /// </summary> 17 [TestMethod] 18 public void ForDataTableTest() { 19 var dataTable = Sql.Create("SELECT * FROM TEST_QUERY ORDER BY ID") 20 .ForDataTable(); 21 Assert.IsNotNull(dataTable); 22 Assert.AreEqual<string>("admin", dataTable.Rows[0]["Username"].ToString()); 23 } 24 /// <summary> 25 /// 执行查询并返回对象集合 26 /// </summary> 27 [TestMethod] 28 public void ForListTest() { 29 var users = Sql.Create("SELECT * FROM TEST_QUERY ORDER BY ID") 30 .ForList<UserInfo>(); 31 Assert.AreEqual<int>(2, users.Count); 32 Assert.AreEqual<string>("admin", users.FirstOrDefault().Username); 33 } 34 /// <summary> 35 /// 执行查询并返回一个对象(一条记录) 36 /// </summary> 37 [TestMethod] 38 public void ForObjectTest() { 39 //查询id=1的记录 40 var userInfo = Sql.Create("SELECT * FROM TEST_QUERY WHERE ID=@ID") 41 .Set("@ID", 1) 42 .ForObject<UserInfo>(); 43 44 Assert.IsNotNull(userInfo); 45 Assert.AreEqual<string>("admin", userInfo.Username); 46 } 47 /// <summary> 48 /// 49 /// </summary> 50 [TestMethod] 51 public void ForScalarTest() { 52 var count = Sql.Create("SELECT COUNT(*) FROM TEST_QUERY") 53 .ForScalar<int>(); 54 Assert.AreEqual<int>(2, count); 55 } 56 /// <summary> 57 /// 多个结果取值测试,建议用此方法替代输出参数 58 /// </summary> 59 [TestMethod] 60 public void MutilResultTest() { 61 62 //执行一条Sql 返回多结果集,注意要调用BeginNextReuslt(),否则DataReader默认会在第一个结果集查询完毕被关闭 63 64 Sql.Create("SELECT 'abc'") 65 .AppendLine("SELECT * FROM TEST_QUERY WHERE ID=@ID1") 66 .AppendLine("SELECT * FROM TEST_QUERY WHERE ID=@ID2") 67 .Set("@ID1", 1) 68 .Set("@ID2", 2) 69 .BeginNextReuslt() 70 .ForValue<string>((result) => {// 获取第一个结果 71 Assert.AreEqual<string>("abc", result); 72 }) 73 .ForObject<UserInfo>((userInfo) => {//获取第二个结果集 74 Assert.IsNotNull(userInfo); 75 }) 76 .ForObject<UserInfo>((userInfo) => {//获取第三个结果集 77 Assert.AreEqual<string>("test", userInfo.Username); 78 }) 79 .EndNextResult();//注意执行完毕调用关闭方法 80 } 81 /// <summary> 82 /// 执行SQL测试 83 /// </summary> 84 [TestMethod] 85 public void ForExecuteTest() { 86 //删除表test_CRUD所有数据 87 Sql.Create("DELETE FROM TEST_CRUD") 88 .ForExecute(); 89 90 //注意使用Ole连接时 占位符是? 所有参数都必须填写且要按顺序 91 92 //写法1仅Ole方式中使用,注意参数的个数和参数顺序都要和语句对应 93 94 //写法2可以在多种连接方式使用, 如果是写入2在Ole中也要注意参数个数和顺序 95 96 //---------执行插入语句,插入一条记录,并同时更新设置显示名称为Null------------- 97 98 //写法1 (仅在Ole方式连接有效) 99 //Sql.Create("INSERT INTO TEST_CRUD(ID,USERNAME,DISPLAYNAME) VALUES(?,?,?)") 100 // .SetOle(1, "admin", "管理员") //<=> .SetOle(1).SetOle("amdin").SetOle("管理员") 101 // .AppendLine("UPDATE TEST_CRUD SET DISPLAYNAME=? WHERE ID=?")//追加一条SQL语句 102 // .SetOle(DBNull.Value, 1) 103 // .ForExecute(); 104 105 //写法2 106 Sql.Create("INSERT INTO TEST_CRUD(ID,USERNAME,DISPLAYNAME) VALUES(@ID,@USERNAME,@DISPLAYNAME);") 107 .Set("@ID", 1) 108 .Set("@Username", "admin") 109 .Set("@DisplayName", "管理员") 110 .AppendLine("UPDATE TEST_CRUD SET DISPLAYNAME=@NEWNAME WHERE ID=@ID")//追加一条SQL语句 111 .Set("@NewName", DBNull.Value) 112 .Set("@ID", 1) //如果在非Ole的方式里面,这个ID可以不用设置 113 .ForExecute(); 114 115 //---------------------------------------------------------------------- 116 117 118 //查询一条记录并转为强类型对象 119 var userInfo = Sql.Create("select * from test_CRUD where ID=@ID") 120 .Set("@ID", 1) 121 .ForObject<UserInfo>(); 122 123 Assert.AreEqual<string>("admin", userInfo.Username); 124 125 Assert.IsTrue(string.IsNullOrEmpty(userInfo.DisplayName)); 126 } 127 /// <summary> 128 /// 输出参数测试 129 /// </summary> 130 [TestMethod] 131 public void OutputParameterTest() { 132 133 var outputValue = Sql.Create("TEST_QUERY_OUTPUT", System.Data.CommandType.StoredProcedure)//创建一个执行存储过程的Sql对象 134 .SetOutput("@Count", System.Data.DbType.Int32, 4) //设置输出参数 135 .ForExecute() 136 .ForOutput<int>("@Count");//获取输出参数 137 138 Assert.AreEqual<int>(2, outputValue); 139 } 140 /// <summary> 141 /// 事务测试 142 /// </summary> 143 [TestMethod] 144 public void TransactionTest() { 145 try { 146 //事务作用必须是保持在一个Sql对象上,执行完毕请提交事务 147 Sql.Create("INSERT INTO TEST_CRUD(ID,USERNAME,DISPLAYNAME) VALUES(@ID,@USERNAME,@DISPLAYNAME);") 148 .Set("@ID", 3) 149 .Set("@Username", "事务") 150 .Set("@DisplayName", "事务测试") 151 .BeginTransaction()//开启事务 152 .ForExecute() //执行查询必须在开启事务语句之后执行 153 .SetCommand("SELECT 1 FROM A")//执行一句错误的SQL语句 154 .ForExecute() ////执行查询必须在开启事务语句之后执行 155 .Commit();//提交事务 156 Assert.Fail("未正常抛出异常!"); 157 } catch (Exception ex) { 158 Assert.IsTrue(ex.Message.Contains("对象名 'A' 无效")); 159 } 160 } 161 162 } 163 }
