C# SQL辅助类及扩展方法
SQL辅助类
1 /// <summary> 2 /// SQL辅助类 3 /// 2016-08-12 4 /// </summary> 5 public class SQLUtility 6 { 7 8 public SQLUtility() 9 { 10 TablePre = Config.INSConfig.DBConfig.DBTablePre; 11 } 12 13 /// <summary> 14 /// 数据库对象前缀 15 /// </summary> 16 public string TablePre { get; set; } 17 18 /// <summary> 19 /// 表名称 20 /// </summary> 21 private string _tablename = ""; 22 /// <summary> 23 /// 表名称 24 /// </summary> 25 public string TableName 26 { 27 get { return TablePre + _tablename; } 28 set { _tablename = value; } 29 } 30 31 /// <summary> 32 /// Select临时数据 33 /// </summary> 34 public string Select { get; set; } 35 36 /// <summary> 37 /// sql临时数据 38 /// </summary> 39 public StringBuilder Sql = new StringBuilder(); 40 41 /// <summary> 42 /// Where临时数据 43 /// </summary> 44 public List<string> Where = new List<string>(); 45 46 /// <summary> 47 /// Take临时数据 48 /// </summary> 49 public int Top { get; set; } 50 51 /// <summary> 52 /// OrderBy临时数据 53 /// </summary> 54 public string OrderBy { get; set; } 55 56 /// <summary> 57 /// GroupBy临时数据 58 /// </summary> 59 public string GroupBy { get; set; } 60 61 /// <summary> 62 /// 事物状态 63 /// </summary> 64 public int TransactionState { get; set; } 65 66 /// <summary> 67 /// 不需要操作的实体对象字段 针对 Insert 和 Update 方法 68 /// </summary> 69 public List<string> Operation = new List<string>(); 70 71 /// <summary> 72 /// 参数 73 /// </summary> 74 public List<DbParameter> Parameters = new List<DbParameter>(); 75 76 }
扩展方法
1 /// <summary> 2 /// SQL辅助类扩展方法 3 /// 2016-08-12 4 /// </summary> 5 public static class SQLUtilityExtensions 6 { 7 8 #region 基础扩展方法 9 10 /// <summary> 11 /// 设置操作表名 12 /// [FROM {tableName}] 13 /// </summary> 14 /// <param name="tableName">表名</param> 15 /// <returns></returns> 16 public static SQLUtility From(this SQLUtility utility, string tableName) 17 { 18 utility.TableName = tableName; 19 return utility; 20 } 21 22 /// <summary> 23 /// 通过反射映射T操作表名 24 /// [FROM {tableName}] 25 /// </summary> 26 /// <typeparam name="T">实体对象</typeparam> 27 /// <returns></returns> 28 public static SQLUtility From<T>(this SQLUtility utility) 29 { 30 utility.TableName = SQLHelperExtensions.GetTableName(typeof(T)); 31 return utility; 32 } 33 34 /// <summary> 35 /// 设置查询字段 36 /// [SELECT {strSelect}] 37 /// </summary> 38 /// <param name="strSelect">查询字段</param> 39 /// <returns></returns> 40 public static SQLUtility Select(this SQLUtility utility, string strSelect) 41 { 42 utility.Select = strSelect; 43 return utility; 44 } 45 46 /// <summary> 47 /// 设置查询数量 48 /// [TOP {intTop}] 49 /// </summary> 50 /// <param name="intTop">查询数量</param> 51 /// <returns></returns> 52 public static SQLUtility Top(this SQLUtility utility, int intTop) 53 { 54 utility.Top = intTop; 55 return utility; 56 } 57 58 /// <summary> 59 /// 设置SQL条件语句 60 /// [{strSql}] 61 /// </summary> 62 /// <param name="strSql">SQL条件语句</param> 63 /// <returns></returns> 64 public static SQLUtility Sql(this SQLUtility utility, string strSql) 65 { 66 utility.Sql.Append(strSql); 67 return utility; 68 } 69 70 /// <summary> 71 /// 设置连接查询 72 /// 默认leftFiled为主表字段，rightFiled为连接表字段 73 /// [{joinType} JOIN {joinTableName} ON {SQLUtility.TableName}.{leftFiled} = {joinTableName}.{rightFiled}] 74 /// </summary> 75 /// <param name="joinTableName">连接表名</param> 76 /// <param name="leftFiled">左连接字段</param> 77 /// <param name="rightFiled">右连接字段</param> 78 /// <param name="joinType">连接类型</param> 79 /// <returns></returns> 80 public static SQLUtility Join(this SQLUtility utility, string joinTableName, string leftFiled, string rightFiled, JoinType joinType) 81 { 82 utility.Sql.AppendFormat(" {4} JOIN [{0}] ON [{1}].[{2}] = [{0}].[{3}] ", new object[] { joinTableName, utility.TableName, leftFiled, rightFiled, joinType }); 83 return utility; 84 } 85 86 /// <summary> 87 /// 设置条件语句，无需添加WHERE或AND 88 /// [AND {strWhere}] 89 /// </summary> 90 /// <param name="strWhere">条件语句</param> 91 /// <returns></returns> 92 public static SQLUtility Where(this SQLUtility utility, string strWhere) 93 { 94 if (!string.IsNullOrEmpty(strWhere)) 95 { 96 utility.Where.Add(string.Format(" AND {0} ", strWhere)); 97 } 98 return utility; 99 } 100 101 /// <summary> 102 /// 设置多字段排序语句 103 /// [ORDER BY {orderByFiled}]，[{orderByFiled}] 104 /// </summary> 105 /// <param name="orderByFiled">排序字段或SQL排序语句</param> 106 /// <returns></returns> 107 public static SQLUtility OrderBy(this SQLUtility utility, string orderByFiled) 108 { 109 if (orderByFiled.ToLower().IndexOf("order") > 0) 110 { 111 utility.OrderBy = orderByFiled; 112 } 113 else 114 { 115 utility.OrderBy = string.Format(" ORDER BY {0} ", orderByFiled); 116 } 117 return utility; 118 } 119 120 /// <summary> 121 /// 设置单字段排序语句 122 /// [ORDER BY {orderByFiled} {orderByType}] 123 /// </summary> 124 /// <param name="orderByFiled">排序字段</param> 125 /// <param name="orderByType">排序类型</param> 126 /// <returns></returns> 127 public static SQLUtility OrderBy(this SQLUtility utility, string orderByFiled, OrderByType orderByType) 128 { 129 utility.OrderBy = string.Format(" ORDER BY {0} {1}", orderByFiled, orderByType.ToString()); 130 return utility; 131 } 132 133 /// <summary> 134 /// 设置数据分组 135 /// [Group By {groupByFileds}] 136 /// </summary> 137 /// <param name="groupByFileds">分组字段</param> 138 /// <returns></returns> 139 public static SQLUtility GroupBy(this SQLUtility utility, string[] groupByFileds) 140 { 141 utility.GroupBy = string.Format(" GROUP BY {0} ", string.Join(",", groupByFileds)); 142 return utility; 143 } 144 145 /// <summary> 146 /// 设置SQL IN条件语句 147 /// [AND {fieldName} IN ({inValues})] 148 /// </summary> 149 /// <param name="filedName">字段名</param> 150 /// <param name="inValues">值</param> 151 /// <returns></returns> 152 public static SQLUtility In(this SQLUtility utility, string filedName, string[] inValues) 153 { 154 StringBuilder strIN = new StringBuilder(); 155 if (inValues != null) 156 { 157 foreach (string str in inValues) 158 { 159 if (!string.IsNullOrEmpty(str)) 160 { 161 strIN.AppendFormat("'{0}',", str); 162 } 163 } 164 } 165 if (strIN.ToString().EndsWith(",")) 166 { 167 strIN.Remove(strIN.Length - 1, 1); 168 } 169 utility.Where.Add(string.Format(" AND {0} IN ({1})", filedName, strIN.ToString())); 170 return utility; 171 } 172 173 /// <summary> 174 /// 不需要操作的实体对象字段 针对 Insert 和 Update 方法 175 /// 不影响Where，AddParameter参数 176 /// </summary> 177 /// <param name="operationFileds">不需要操作的实体对象字段</param> 178 /// <returns></returns> 179 public static SQLUtility NoOperation(this SQLUtility utility, string[] operationFileds) 180 { 181 utility.Operation.AddRange(operationFileds); 182 return utility; 183 } 184 185 /// <summary> 186 /// 通过DbProviderFactory创建DbParameter参数 187 /// </summary> 188 /// <param name="parameterName">参数名称</param> 189 /// <param name="parameterValue">参数值</param> 190 /// <returns></returns> 191 public static SQLUtility AddParameter(this SQLUtility utility, string parameterName, object parameterValue) 192 { 193 utility.Parameters.Add(SQLHelper.CreateDbParameter(parameterName, parameterValue)); 194 return utility; 195 } 196 197 /// <summary> 198 /// 添加DbParameter参数集合 199 /// </summary> 200 /// <param name="parameters">参数集合</param> 201 /// <returns></returns> 202 public static SQLUtility AddParameter(this SQLUtility utility, DbParameter[] parameters) 203 { 204 utility.Parameters.AddRange(parameters); 205 return utility; 206 } 207 208 /// <summary> 209 /// 清空SQLUtility对象 210 /// </summary> 211 /// <param name="utility"></param> 212 private static void Clear(this SQLUtility utility) 213 { 214 utility.Sql.Clear(); 215 utility.Where.Clear(); 216 utility.Parameters.Clear(); 217 } 218 219 220 #endregion 221 222 #region 事务处理 223 224 /// <summary> 225 /// 开始事务 226 /// </summary> 227 public static void BeginTransaction(this SQLUtility utility) 228 { 229 SQLHelper.BeginTransaction(); 230 utility.SetTransactionState(); 231 } 232 233 /// <summary> 234 /// 提交事务 235 /// </summary> 236 public static void CommitTransaction(this SQLUtility utility) 237 { 238 SQLHelper.Commit(); 239 utility.SetTransactionState(); 240 } 241 242 /// <summary> 243 /// 回滚事务 244 /// </summary> 245 public static void RollBackTransaction(this SQLUtility utility) 246 { 247 SQLHelper.Rollback(); 248 utility.SetTransactionState(); 249 } 250 251 /// <summary> 252 /// 获取事物状态 253 /// </summary> 254 /// <param name="utility"></param> 255 private static void SetTransactionState(this SQLUtility utility) 256 { 257 utility.TransactionState = SQLHelper.TransactionState; 258 } 259 260 #endregion 261 262 #region 执行SELECT方法 263 264 /// <summary> 265 /// 是否存在记录 266 /// </summary> 267 /// <returns></returns> 268 public static bool Exists(this SQLUtility utility) 269 { 270 try 271 { 272 string executeSql = GetStringSql(utility, "SELECT COUNT(1)"); 273 object objectValue = SQLHelper.ExecuteScalar(executeSql, utility.Parameters.ToArray()); 274 return SQLTypeHelper.ToBool(objectValue); 275 } 276 catch (Exception ex) 277 { 278 throw new Exception("SQLUtility_Exists", ex); 279 } 280 finally 281 { 282 utility.Clear(); 283 } 284 } 285 286 /// <summary> 287 /// 查询行数 288 /// 默认[Count(*)]通过Select方法改变查询参数 289 /// </summary> 290 /// <returns></returns> 291 public static int Count(this SQLUtility utility) 292 { 293 try 294 { 295 string strSelect = string.IsNullOrEmpty(utility.Select) ? "SELECT COUNT(*)" : string.Format("SELECT COUNT({0})", utility.Select); 296 string executeSql = GetStringSql(utility, strSelect); 297 object objectValue = SQLHelper.ExecuteScalar(executeSql, utility.Parameters.ToArray()); 298 return SQLTypeHelper.ToInt(objectValue); 299 } 300 catch (Exception ex) 301 { 302 throw new Exception("SQLUtility_Count", ex); 303 } 304 finally 305 { 306 utility.Clear(); 307 } 308 } 309 310 /// <summary> 311 /// 查询第一行第一列 312 /// 默认[*]通过Select方法改变查询参数 313 /// </summary> 314 /// <returns></returns> 315 public static object Object(this SQLUtility utility) 316 { 317 try 318 { 319 string strSelect = string.IsNullOrEmpty(utility.Select) ? "SELECT TOP 1 *" : string.Format("SELECT TOP 1 {0}", utility.Select); 320 string executeSql = GetStringSql(utility, strSelect); 321 return SQLHelper.ExecuteScalar(executeSql, utility.Parameters.ToArray()); 322 } 323 catch (Exception ex) 324 { 325 throw new Exception("SQLUtility_Object", ex); 326 } 327 finally 328 { 329 utility.Clear(); 330 } 331 } 332 333 /// <summary> 334 /// 查询最大值 335 /// 需明确查询列 通过Select方法改变查询参数 336 /// </summary> 337 /// <returns>SQLUtility.Select参数为NULL或''时 返回-1</returns> 338 public static object Max(this SQLUtility utility) 339 { 340 try 341 { 342 if (string.IsNullOrEmpty(utility.Select)) 343 { 344 return -1; 345 } 346 string strSelect = string.Format("SELECT TOP 1 MAX({0})", utility.Select); 347 string executeSql = GetStringSql(utility, strSelect); 348 return SQLHelper.ExecuteScalar(executeSql, utility.Parameters.ToArray()); 349 } 350 catch (Exception ex) 351 { 352 throw new Exception("SQLUtility_Max", ex); 353 } 354 finally 355 { 356 utility.Clear(); 357 } 358 } 359 360 /// <summary> 361 /// 查询最小值 362 /// 需明确查询列 通过Select方法改变查询参数 363 /// </summary> 364 /// <returns>SQLUtility.Select参数为NULL或''时 返回-1</returns> 365 public static object Min(this SQLUtility utility) 366 { 367 try 368 { 369 if (string.IsNullOrEmpty(utility.Select)) 370 { 371 return -1; 372 } 373 string strSelect = string.Format("SELECT TOP 1 MIN({0})", utility.Select); 374 string executeSql = GetStringSql(utility, strSelect); 375 return SQLHelper.ExecuteScalar(executeSql, utility.Parameters.ToArray()); 376 } 377 catch (Exception ex) 378 { 379 throw new Exception("SQLUtility_Min", ex); 380 } 381 finally 382 { 383 utility.Clear(); 384 } 385 } 386 387 /// <summary> 388 /// 查询数据 389 /// 返回DataTable集合 390 /// </summary> 391 /// <returns></returns> 392 public static DataTable ToTable(this SQLUtility utility) 393 { 394 try 395 { 396 string strSql = GetStringSql(utility); 397 return SQLHelper.ExecuteDataTable(strSql, utility.Parameters.ToArray()); 398 } 399 catch (Exception ex) 400 { 401 throw new Exception("SQLUtility_ToDataTable", ex); 402 } 403 finally 404 { 405 utility.Clear(); 406 } 407 } 408 409 /// <summary> 410 /// 查询数据 411 /// 返回集合中第一个元素。 412 /// </summary> 413 /// <returns></returns> 414 public static T First<T>(this SQLUtility utility) where T : new() 415 { 416 var entitylist = utility.ToList<T>(); 417 return entitylist != null && entitylist.Count > 0 ? entitylist.First() : default(T); 418 } 419 420 /// <summary> 421 /// 查询数据 422 /// 返回集合中第一行元素。 423 /// </summary> 424 /// <returns></returns> 425 public static T ToModel<T>(this SQLUtility utility) where T : new() 426 { 427 try 428 { 429 string strSql = GetStringSql(utility); 430 var dataRows = SQLHelper.ExecuteDataTable(strSql, utility.Parameters.ToArray()).Rows[0]; 431 return SQLHelperExtensions.RowToModel<T>(dataRows); 432 } 433 catch (Exception ex) 434 { 435 throw new Exception("SQLUtility_ToList", ex); 436 } 437 finally 438 { 439 utility.Clear(); 440 } 441 } 442 443 /// <summary> 444 /// 查询数据 445 /// 返回List对象集合 446 /// </summary> 447 /// <typeparam name="T"></typeparam> 448 /// <param name="SQLUtility"></param> 449 /// <returns></returns> 450 public static List<T> ToList<T>(this SQLUtility utility) where T : new() 451 { 452 try 453 { 454 string strSql = GetStringSql(utility); 455 var dataReader = SQLHelper.ExecuteReader(strSql, utility.Parameters.ToArray()); 456 return SQLHelperExtensions.ReaderToList<T>(dataReader); 457 } 458 catch (Exception ex) 459 { 460 throw new Exception("SQLUtility_ToList", ex); 461 } 462 finally 463 { 464 utility.Clear(); 465 } 466 } 467 468 469 /// <summary> 470 /// 执行存储过程返回List集合对象 471 /// 通过AddParameters方法添加参数 472 /// </summary> 473 /// <typeparam name="T">实体对象</typeparam> 474 /// <param name="StoredProcedureName">存储过程名称</param> 475 /// <returns></returns> 476 //public static object StoredProcedureToObject<T>(this SQLUtility utility, string StoredProcedureName) where T : new() 477 //{ 478 // try 479 // { 480 // var dataReader = SQLHelper.(CommandType.StoredProcedure, StoredProcedureName, utility.Parameters.ToArray()); 481 // return SQLHelperExtensions.ReaderToList<T>(dataReader); 482 // } 483 // catch (Exception ex) 484 // { 485 // throw new ExceptionHelper("SQLUtility_StoredProcedure", ex); 486 // } 487 // finally 488 // { 489 // utility.Clear(); 490 // } 491 //} 492 493 494 /// <summary> 495 /// 执行存储过程返回List集合对象 496 /// 通过AddParameters方法添加参数 497 /// </summary> 498 /// <typeparam name="T">实体对象</typeparam> 499 /// <param name="StoredProcedureName">存储过程名称</param> 500 /// <returns></returns> 501 public static List<T> RunProcedureToList<T>(this SQLUtility utility, string procedureName) where T : new() 502 { 503 try 504 { 505 var dataReader = SQLHelper.ExecuteReader(CommandType.StoredProcedure, procedureName, utility.Parameters.ToArray()); 506 return SQLHelperExtensions.ReaderToList<T>(dataReader); 507 } 508 catch (Exception ex) 509 { 510 throw new ExceptionHelper("SQLUtility_StoredProcedure", ex); 511 } 512 finally 513 { 514 utility.Clear(); 515 } 516 } 517 518 #endregion 519 520 #region 执行INSERT方法 521 522 /// <summary> 523 /// 通过实体对象添加数据 524 /// </summary> 525 /// <typeparam name="T">对象类型</typeparam> 526 /// <param name="entity">实体对象</param> 527 /// <returns></returns> 528 public static int Insert<T>(this SQLUtility utility, T entity) where T : class 529 { 530 try 531 { 532 int result = 0; 533 string fieldSql = string.Empty; 534 string valueSql = string.Empty; 535 StringBuilder executeSql = new StringBuilder(); 536 List<DbParameter> parameterList = SQLHelperExtensions.InsertSql<T>(entity, utility.Operation, out fieldSql, out valueSql); 537 538 if (parameterList != null && !string.IsNullOrEmpty(fieldSql) && !string.IsNullOrEmpty(valueSql)) 539 { 540 executeSql.AppendFormat("INSERT INTO [{0}]({1}) ", utility.TableName, fieldSql); 541 executeSql.AppendFormat("VALUES({0});", valueSql); 542 result = SQLHelper.ExecuteNonQuery(executeSql.ToString(), parameterList.ToArray()); 543 if (utility.TransactionState != 0) 544 { 545 utility.SetTransactionState(); 546 } 547 } 548 return result; 549 } 550 catch (Exception ex) 551 { 552 throw new Exception("SQLUtility_Insert", ex); 553 } 554 finally 555 { 556 utility.Clear(); 557 } 558 } 559 560 /// <summary> 561 /// 通过实体对象集合添加数据 562 /// </summary> 563 /// <typeparam name="T">对象类型</typeparam> 564 /// <param name="list">对象集合</param> 565 /// <returns></returns> 566 public static int InsertRange<T>(this SQLUtility utility, List<T> list) where T : class 567 { 568 int result = 0; 569 foreach (var item in list) 570 { 571 result += Insert(utility, item); 572 } 573 return result; 574 } 575 576 /// <summary> 577 /// 通过SQL语句添加数据 578 /// </summary> 579 /// <param name="utility"></param> 580 /// <param name="insertCloumn">需要插入的字段</param> 581 /// <param name="valueCloumn">字段值</param> 582 /// <returns></returns> 583 public static int InsertToSql(this SQLUtility utility, string[] insertCloumn, string[] valueCloumn) 584 { 585 try 586 { 587 int result = 0; 588 StringBuilder executeSql = new StringBuilder(); 589 executeSql.AppendFormat("INSERT INTO [{0}]({1}) ", utility.TableName, string.Join(",", insertCloumn)); 590 executeSql.AppendFormat("VALUES({0}) ", string.Join(",", valueCloumn)); 591 executeSql.AppendFormat("WHERE 1=1 {0} ", string.Join(" ", utility.Where)); 592 result = SQLHelper.ExecuteNonQuery(executeSql.ToString(), utility.Parameters.ToArray()); 593 if (utility.TransactionState != 0) 594 { 595 utility.SetTransactionState(); 596 } 597 return result; 598 } 599 catch (Exception ex) 600 { 601 throw new ExceptionHelper("SQLUtility_UpdateToSql", ex); 602 } 603 finally 604 { 605 utility.Clear(); 606 } 607 } 608 609 610 #endregion 611 612 #region 执行UPDATE方法 613 614 615 /// <summary> 616 /// 通过实体对象更新数据 617 /// </summary> 618 /// <typeparam name="T">对象类型</typeparam> 619 /// <param name="entity">实体对象</param> 620 /// <returns></returns> 621 public static int Update<T>(this SQLUtility utility, T entity) where T : class 622 { 623 try 624 { 625 int result = 0; 626 string setSql = string.Empty; 627 StringBuilder executeSql = new StringBuilder(); 628 List<DbParameter> parameterList = SQLHelperExtensions.UpdateSql<T>(entity, utility.Operation, out setSql); 629 630 if (parameterList != null && !string.IsNullOrEmpty(setSql)) 631 { 632 executeSql.AppendFormat("UPDATE [{0}] SET {1} ", utility.TableName, string.Join(",", setSql)); 633 executeSql.AppendFormat("WHERE 1=1 {0} ", string.Join(" ", utility.Where)); 634 result = SQLHelper.ExecuteNonQuery(executeSql.ToString(), parameterList.ToArray()); 635 if (utility.TransactionState != 0) 636 { 637 utility.SetTransactionState(); 638 } 639 } 640 return result; 641 } 642 catch (Exception ex) 643 { 644 throw new Exception("SQLUtility_Update", ex); 645 } 646 finally 647 { 648 utility.Clear(); 649 } 650 } 651 652 /// <summary> 653 /// 通过SQL语句更新数据 654 /// </summary> 655 /// <param name="updateCloumn">需要更新的列名</param> 656 /// <returns></returns> 657 public static int UpdateToSql(this SQLUtility utility, string[] updateCloumn) 658 { 659 try 660 { 661 StringBuilder strSql = new StringBuilder(); 662 strSql.AppendFormat("UPDATE [{0}] SET {1} ", utility.TableName, string.Join(",", updateCloumn)); 663 strSql.AppendFormat("WHERE 1=1 {0} ", string.Join(" ", utility.Where)); 664 return SQLHelper.ExecuteNonQuery(strSql.ToString(), utility.Parameters.ToArray()); 665 } 666 catch (Exception ex) 667 { 668 throw new ExceptionHelper("SQLUtility_UpdateToSql", ex); 669 } 670 finally 671 { 672 utility.Clear(); 673 } 674 } 675 676 677 #endregion 678 679 #region 执行DELETE方法 680 681 /// <summary> 682 /// 删除数据 683 /// </summary> 684 /// <returns></returns> 685 public static int Delete(this SQLUtility utility) 686 { 687 try 688 { 689 int result = 0; 690 StringBuilder executeSql = new StringBuilder(); 691 if (string.IsNullOrEmpty(utility.TableName)) 692 { 693 throw new Exception("SQLUtility对象中缺少From参数."); 694 } 695 executeSql.AppendFormat("DELETE [{0}] ", utility.TableName); 696 executeSql.AppendFormat("WHERE 1=1 {0} ", string.Join(" ", utility.Where)); 697 result = SQLHelper.ExecuteNonQuery(executeSql.ToString(), utility.Parameters.ToArray()); 698 if (utility.TransactionState != 0) 699 { 700 utility.SetTransactionState(); 701 } 702 return result; 703 } 704 catch (Exception ex) 705 { 706 throw new Exception("SQLUtility_Delete", ex); 707 } 708 finally 709 { 710 utility.Clear(); 711 } 712 } 713 714 #endregion 715 716 /// <summary> 717 /// 获取SQL语句 718 /// </summary> 719 /// <returns></returns> 720 private static string GetStringSql(this SQLUtility utility, string selectSql = null) 721 { 722 StringBuilder strSql = new StringBuilder(); 723 if (utility != null) 724 { 725 if (string.IsNullOrEmpty(selectSql)) 726 { 727 strSql.Append(utility.Top > 0 ? "SELECT TOP " + utility.Top.ToString() : "SELECT "); 728 strSql.Append(string.IsNullOrEmpty(utility.Select) ? " * " : utility.Select); 729 } 730 else 731 { 732 strSql.Append(selectSql); 733 } 734 if (string.IsNullOrEmpty(utility.TableName)) 735 { 736 throw new Exception("SQLUtility对象中缺少From参数."); 737 } 738 strSql.AppendFormat(" FROM [{0}] ", utility.TableName); 739 strSql.AppendFormat(" {0} ", utility.Sql); 740 strSql.AppendFormat(" WHERE 1=1 {0} ", string.Join(" ", utility.Where)); 741 strSql.Append(utility.OrderBy); 742 strSql.Append(utility.GroupBy); 743 } 744 return strSql.ToString(); 745 } 746 }
