首页 > 代码库 > 底层数据访问层代码

底层数据访问层代码

   1 using System;   2 using System.Collections;   3 using System.Collections.Specialized;   4 using System.Data;   5 using System.Data.SqlClient;   6 using System.Configuration;   7 using System.Data.Common;   8 using System.Collections.Generic;   9   10 namespace EIM.DBUtility  11 {  12     /// <summary>  13     /// 数据访问抽象基础类  14     /// Copyright (C) EIM   15     /// </summary>  16     public abstract class DbHelperSQL  17     {  18         //数据库连接字符串(web.config来配置),多数据库可使用DbHelperSQLP来实现.  19         public static string connectionString = PubConstant.ConnectionString;  20         public DbHelperSQL()  21         {  22         }  23   24         #region 公用方法  25         /// <summary>  26         /// 判断是否存在某表的某个字段  27         /// </summary>  28         /// <param name="tableName">表名称</param>  29         /// <param name="columnName">列名称</param>  30         /// <returns>是否存在</returns>  31         public static bool ColumnExists(string tableName, string columnName)  32         {  33             string sql = "select count(1) from syscolumns where [id]=object_id(‘" + tableName + "‘) and [name]=‘" + columnName + "";  34             object res = GetSingle(sql);  35             if (res == null)  36             {  37                 return false;  38             }  39             return Convert.ToInt32(res) > 0;  40         }  41         public static int GetMaxID(string FieldName, string TableName)  42         {  43             string strsql = "select max(" + FieldName + ")+1 from " + TableName;  44             object obj = GetSingle(strsql);  45             if (obj == null)  46             {  47                 return 1;  48             }  49             else  50             {  51                 return int.Parse(obj.ToString());  52             }  53         }  54         public static bool Exists(string strSql)  55         {  56             object obj = GetSingle(strSql);  57             int cmdresult;  58             if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))  59             {  60                 cmdresult = 0;  61             }  62             else  63             {  64                 cmdresult = int.Parse(obj.ToString()); //也可能=0  65             }  66             if (cmdresult == 0)  67             {  68                 return false;  69             }  70             else  71             {  72                 return true;  73             }  74         }  75         /// <summary>  76         /// 表是否存在  77         /// </summary>  78         /// <param name="TableName"></param>  79         /// <returns></returns>  80         public static bool TabExists(string TableName)  81         {  82             string strsql = "select count(*) from sysobjects where id = object_id(N‘[" + TableName + "]‘) and OBJECTPROPERTY(id, N‘IsUserTable‘) = 1";  83             //string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N‘[dbo].[" + TableName + "]‘) AND type in (N‘U‘)";  84             object obj = GetSingle(strsql);  85             int cmdresult;  86             if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))  87             {  88                 cmdresult = 0;  89             }  90             else  91             {  92                 cmdresult = int.Parse(obj.ToString());  93             }  94             if (cmdresult == 0)  95             {  96                 return false;  97             }  98             else  99             { 100                 return true; 101             } 102         } 103         public static bool Exists(string strSql, params SqlParameter[] cmdParms) 104         { 105             object obj = GetSingle(strSql, cmdParms); 106             int cmdresult; 107             if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) 108             { 109                 cmdresult = 0; 110             } 111             else 112             { 113                 cmdresult = int.Parse(obj.ToString()); 114             } 115             if (cmdresult == 0) 116             { 117                 return false; 118             } 119             else 120             { 121                 return true; 122             } 123         } 124         #endregion 125  126         #region  执行简单SQL语句 127  128         /// <summary> 129         /// 执行SQL语句,返回影响的记录数 130         /// </summary> 131         /// <param name="SQLString">SQL语句</param> 132         /// <returns>影响的记录数</returns> 133         public static int ExecuteSql(string SQLString) 134         { 135             using (SqlConnection connection = new SqlConnection(connectionString)) 136             { 137                 using (SqlCommand cmd = new SqlCommand(SQLString, connection)) 138                 { 139                     try 140                     { 141                         connection.Open(); 142                         int rows = cmd.ExecuteNonQuery(); 143                         return rows; 144                     } 145                     catch (System.Data.SqlClient.SqlException e) 146                     { 147                         connection.Close(); 148                         throw e; 149                     } 150                 } 151             } 152         } 153  154         public static int ExecuteSqlByTime(string SQLString, int Times) 155         { 156             using (SqlConnection connection = new SqlConnection(connectionString)) 157             { 158                 using (SqlCommand cmd = new SqlCommand(SQLString, connection)) 159                 { 160                     try 161                     { 162                         connection.Open(); 163                         cmd.CommandTimeout = Times; 164                         int rows = cmd.ExecuteNonQuery(); 165                         return rows; 166                     } 167                     catch (System.Data.SqlClient.SqlException e) 168                     { 169                         connection.Close(); 170                         throw e; 171                     } 172                 } 173             } 174         } 175  176         /// <summary> 177         /// 执行Sql和Oracle滴混合事务 178         /// </summary> 179         /// <param name="list">SQL命令行列表</param> 180         /// <param name="oracleCmdSqlList">Oracle命令行列表</param> 181         /// <returns>执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功</returns> 182         public static int ExecuteSqlTran(List<CommandInfo> list, List<CommandInfo> oracleCmdSqlList) 183         { 184             using (SqlConnection conn = new SqlConnection(connectionString)) 185             { 186                 conn.Open(); 187                 SqlCommand cmd = new SqlCommand(); 188                 cmd.Connection = conn; 189                 SqlTransaction tx = conn.BeginTransaction(); 190                 cmd.Transaction = tx; 191                 try 192                 { 193                     foreach (CommandInfo myDE in list) 194                     { 195                         string cmdText = myDE.CommandText; 196                         SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters; 197                         PrepareCommand(cmd, conn, tx, cmdText, cmdParms); 198                         if (myDE.EffentNextType == EffentNextType.SolicitationEvent) 199                         { 200                             if (myDE.CommandText.ToLower().IndexOf("count(") == -1) 201                             { 202                                 tx.Rollback(); 203                                 throw new Exception("违背要求" + myDE.CommandText + "必须符合select count(..的格式"); 204                                 //return 0; 205                             } 206  207                             object obj = cmd.ExecuteScalar(); 208                             bool isHave = false; 209                             if (obj == null && obj == DBNull.Value) 210                             { 211                                 isHave = false; 212                             } 213                             isHave = Convert.ToInt32(obj) > 0; 214                             if (isHave) 215                             { 216                                 //引发事件 217                                 myDE.OnSolicitationEvent(); 218                             } 219                         } 220                         if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine) 221                         { 222                             if (myDE.CommandText.ToLower().IndexOf("count(") == -1) 223                             { 224                                 tx.Rollback(); 225                                 throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式"); 226                                 //return 0; 227                             } 228  229                             object obj = cmd.ExecuteScalar(); 230                             bool isHave = false; 231                             if (obj == null && obj == DBNull.Value) 232                             { 233                                 isHave = false; 234                             } 235                             isHave = Convert.ToInt32(obj) > 0; 236  237                             if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave) 238                             { 239                                 tx.Rollback(); 240                                 throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0"); 241                                 //return 0; 242                             } 243                             if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave) 244                             { 245                                 tx.Rollback(); 246                                 throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0"); 247                                 //return 0; 248                             } 249                             continue; 250                         } 251                         int val = cmd.ExecuteNonQuery(); 252                         if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0) 253                         { 254                             tx.Rollback(); 255                             throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行"); 256                             //return 0; 257                         } 258                         cmd.Parameters.Clear(); 259                     } 260                     string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC"); 261                     bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList); 262                     if (!res) 263                     { 264                         tx.Rollback(); 265                         throw new Exception("Oracle执行失败"); 266                         // return -1; 267                     } 268                     tx.Commit(); 269                     return 1; 270                 } 271                 catch (System.Data.SqlClient.SqlException e) 272                 { 273                     tx.Rollback(); 274                     throw e; 275                 } 276                 catch (Exception e) 277                 { 278                     tx.Rollback(); 279                     throw e; 280                 } 281             } 282         } 283         /// <summary> 284         /// 执行多条SQL语句,实现数据库事务。 285         /// </summary> 286         /// <param name="SQLStringList">多条SQL语句</param>         287         public static int ExecuteSqlTran(List<String> SQLStringList) 288         { 289             using (SqlConnection conn = new SqlConnection(connectionString)) 290             { 291                 conn.Open(); 292                 SqlCommand cmd = new SqlCommand(); 293                 cmd.Connection = conn; 294                 SqlTransaction tx = conn.BeginTransaction(); 295                 cmd.Transaction = tx; 296                 try 297                 { 298                     int count = 0; 299                     for (int n = 0; n < SQLStringList.Count; n++) 300                     { 301                         string strsql = SQLStringList[n]; 302                         if (strsql.Trim().Length > 1) 303                         { 304                             cmd.CommandText = strsql; 305                             count += cmd.ExecuteNonQuery(); 306                         } 307                     } 308                     tx.Commit(); 309                     return count; 310                 } 311                 catch 312                 { 313                     tx.Rollback(); 314                     return 0; 315                 } 316             } 317         } 318         /// <summary> 319         /// 执行带一个存储过程参数的的SQL语句。 320         /// </summary> 321         /// <param name="SQLString">SQL语句</param> 322         /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param> 323         /// <returns>影响的记录数</returns> 324         public static int ExecuteSql(string SQLString, string content) 325         { 326             using (SqlConnection connection = new SqlConnection(connectionString)) 327             { 328                 SqlCommand cmd = new SqlCommand(SQLString, connection); 329                 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText); 330                 myParameter.Value =http://www.mamicode.com/ content; 331                 cmd.Parameters.Add(myParameter); 332                 try 333                 { 334                     connection.Open(); 335                     int rows = cmd.ExecuteNonQuery(); 336                     return rows; 337                 } 338                 catch (System.Data.SqlClient.SqlException e) 339                 { 340                     throw e; 341                 } 342                 finally 343                 { 344                     cmd.Dispose(); 345                     connection.Close(); 346                 } 347             } 348         } 349         /// <summary> 350         /// 执行带一个存储过程参数的的SQL语句。 351         /// </summary> 352         /// <param name="SQLString">SQL语句</param> 353         /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param> 354         /// <returns>影响的记录数</returns> 355         public static object ExecuteSqlGet(string SQLString, string content) 356         { 357             using (SqlConnection connection = new SqlConnection(connectionString)) 358             { 359                 SqlCommand cmd = new SqlCommand(SQLString, connection); 360                 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText); 361                 myParameter.Value =http://www.mamicode.com/ content; 362                 cmd.Parameters.Add(myParameter); 363                 try 364                 { 365                     connection.Open(); 366                     object obj = cmd.ExecuteScalar(); 367                     if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) 368                     { 369                         return null; 370                     } 371                     else 372                     { 373                         return obj; 374                     } 375                 } 376                 catch (System.Data.SqlClient.SqlException e) 377                 { 378                     throw e; 379                 } 380                 finally 381                 { 382                     cmd.Dispose(); 383                     connection.Close(); 384                 } 385             } 386         } 387         /// <summary> 388         /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例) 389         /// </summary> 390         /// <param name="strSQL">SQL语句</param> 391         /// <param name="fs">图像字节,数据库的字段类型为image的情况</param> 392         /// <returns>影响的记录数</returns> 393         public static int ExecuteSqlInsertImg(string strSQL, byte[] fs) 394         { 395             using (SqlConnection connection = new SqlConnection(connectionString)) 396             { 397                 SqlCommand cmd = new SqlCommand(strSQL, connection); 398                 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image); 399                 myParameter.Value =http://www.mamicode.com/ fs; 400                 cmd.Parameters.Add(myParameter); 401                 try 402                 { 403                     connection.Open(); 404                     int rows = cmd.ExecuteNonQuery(); 405                     return rows; 406                 } 407                 catch (System.Data.SqlClient.SqlException e) 408                 { 409                     throw e; 410                 } 411                 finally 412                 { 413                     cmd.Dispose(); 414                     connection.Close(); 415                 } 416             } 417         } 418  419         /// <summary> 420         /// 执行一条计算查询结果语句,返回查询结果(object)。 421         /// </summary> 422         /// <param name="SQLString">计算查询结果语句</param> 423         /// <returns>查询结果(object)</returns> 424         public static object GetSingle(string SQLString) 425         { 426             using (SqlConnection connection = new SqlConnection(connectionString)) 427             { 428                 using (SqlCommand cmd = new SqlCommand(SQLString, connection)) 429                 { 430                     try 431                     { 432                         connection.Open(); 433                         object obj = cmd.ExecuteScalar(); 434                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) 435                         { 436                             return null; 437                         } 438                         else 439                         { 440                             return obj; 441                         } 442                     } 443                     catch (System.Data.SqlClient.SqlException e) 444                     { 445                         connection.Close(); 446                         throw e; 447                     } 448                 } 449             } 450         } 451         public static object GetSingle(string SQLString, int Times) 452         { 453             using (SqlConnection connection = new SqlConnection(connectionString)) 454             { 455                 using (SqlCommand cmd = new SqlCommand(SQLString, connection)) 456                 { 457                     try 458                     { 459                         connection.Open(); 460                         cmd.CommandTimeout = Times; 461                         object obj = cmd.ExecuteScalar(); 462                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) 463                         { 464                             return null; 465                         } 466                         else 467                         { 468                             return obj; 469                         } 470                     } 471                     catch (System.Data.SqlClient.SqlException e) 472                     { 473                         connection.Close(); 474                         throw e; 475                     } 476                 } 477             } 478         } 479         /// <summary> 480         /// 执行查询语句,返回SqlDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close ) 481         /// </summary> 482         /// <param name="strSQL">查询语句</param> 483         /// <returns>SqlDataReader</returns> 484         public static SqlDataReader ExecuteReader(string strSQL) 485         { 486             SqlConnection connection = new SqlConnection(connectionString); 487             SqlCommand cmd = new SqlCommand(strSQL, connection); 488             try 489             { 490                 connection.Open(); 491                 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 492                 return myReader; 493             } 494             catch (System.Data.SqlClient.SqlException e) 495             { 496                 throw e; 497             } 498  499         } 500         /// <summary> 501         /// 执行查询语句,返回DataSet 502         /// </summary> 503         /// <param name="SQLString">查询语句</param> 504         /// <returns>DataSet</returns> 505         public static DataSet Query(string SQLString) 506         { 507             using (SqlConnection connection = new SqlConnection(connectionString)) 508             { 509                 DataSet ds = new DataSet(); 510                 try 511                 { 512                     connection.Open(); 513                     SqlDataAdapter command = new SqlDataAdapter(SQLString, connection); 514                     command.Fill(ds, "ds"); 515                 } 516                 catch (System.Data.SqlClient.SqlException ex) 517                 { 518                     throw new Exception(ex.Message); 519                 } 520                 return ds; 521             } 522         } 523         public static DataSet Query(string SQLString, int Times) 524         { 525             using (SqlConnection connection = new SqlConnection(connectionString)) 526             { 527                 DataSet ds = new DataSet(); 528                 try 529                 { 530                     connection.Open(); 531                     SqlDataAdapter command = new SqlDataAdapter(SQLString, connection); 532                     command.SelectCommand.CommandTimeout = Times; 533                     command.Fill(ds, "ds"); 534                 } 535                 catch (System.Data.SqlClient.SqlException ex) 536                 { 537                     throw new Exception(ex.Message); 538                 } 539                 return ds; 540             } 541         } 542  543  544  545         #endregion 546  547         #region 执行带参数的SQL语句 548  549         /// <summary> 550         /// 执行SQL语句,返回影响的记录数 551         /// </summary> 552         /// <param name="SQLString">SQL语句</param> 553         /// <returns>影响的记录数</returns> 554         public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms) 555         { 556             using (SqlConnection connection = new SqlConnection(connectionString)) 557             { 558                 using (SqlCommand cmd = new SqlCommand()) 559                 { 560                     try 561                     { 562                         PrepareCommand(cmd, connection, null, SQLString, cmdParms); 563                         int rows = cmd.ExecuteNonQuery(); 564                         cmd.Parameters.Clear(); 565                         return rows; 566                     } 567                     catch (System.Data.SqlClient.SqlException e) 568                     { 569                         throw e; 570                     } 571                 } 572             } 573         } 574  575         /// <summary> 576         /// 执行SQL语句,返回影响的记录数 577         /// </summary> 578         /// <param name="SQLString">SQL语句</param> 579         /// <returns>影响的记录数</returns> 580         public static int ExecuteSql(string SQLString, SqlConnection connection, SqlTransaction trans, params SqlParameter[] cmdParms) 581         { 582             using (SqlCommand cmd = new SqlCommand()) 583             { 584                 try 585                 { 586                     PrepareCommand(cmd, connection, trans, SQLString, cmdParms); 587                     int rows = cmd.ExecuteNonQuery(); 588                     cmd.Parameters.Clear(); 589                     return rows; 590                 } 591                 catch (System.Data.SqlClient.SqlException e) 592                 { 593                     throw e; 594                 } 595             } 596         } 597  598         /// <summary> 599         /// 执行多条SQL语句,实现数据库事务。 600         /// </summary> 601         /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param> 602         public static void ExecuteSqlTran(Hashtable SQLStringList) 603         { 604             using (SqlConnection conn = new SqlConnection(connectionString)) 605             { 606                 conn.Open(); 607                 using (SqlTransaction trans = conn.BeginTransaction()) 608                 { 609                     SqlCommand cmd = new SqlCommand(); 610                     try 611                     { 612                         //循环 613                         foreach (DictionaryEntry myDE in SQLStringList) 614                         { 615                             string cmdText = myDE.Key.ToString(); 616                             SqlParameter[] cmdParms = (SqlParameter[])myDE.Value; 617                             PrepareCommand(cmd, conn, trans, cmdText, cmdParms); 618                             int val = cmd.ExecuteNonQuery(); 619                             cmd.Parameters.Clear(); 620                         } 621                         trans.Commit(); 622                     } 623                     catch 624                     { 625                         trans.Rollback(); 626                         throw; 627                     } 628                 } 629             } 630         } 631         /// <summary> 632         /// 执行多条SQL语句,实现数据库事务。 633         /// </summary> 634         /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param> 635         public static int ExecuteSqlTran(System.Collections.Generic.List<CommandInfo> cmdList) 636         { 637             using (SqlConnection conn = new SqlConnection(connectionString)) 638             { 639                 conn.Open(); 640                 using (SqlTransaction trans = conn.BeginTransaction()) 641                 { 642                     SqlCommand cmd = new SqlCommand(); 643                     try 644                     { 645                         int count = 0; 646                         //循环 647                         foreach (CommandInfo myDE in cmdList) 648                         { 649                             string cmdText = myDE.CommandText; 650                             SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters; 651                             PrepareCommand(cmd, conn, trans, cmdText, cmdParms); 652  653                             if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine) 654                             { 655                                 if (myDE.CommandText.ToLower().IndexOf("count(") == -1) 656                                 { 657                                     trans.Rollback(); 658                                     return 0; 659                                 } 660  661                                 object obj = cmd.ExecuteScalar(); 662                                 bool isHave = false; 663                                 if (obj == null && obj == DBNull.Value) 664                                 { 665                                     isHave = false; 666                                 } 667                                 isHave = Convert.ToInt32(obj) > 0; 668  669                                 if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave) 670                                 { 671                                     trans.Rollback(); 672                                     return 0; 673                                 } 674                                 if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave) 675                                 { 676                                     trans.Rollback(); 677                                     return 0; 678                                 } 679                                 continue; 680                             } 681                             int val = cmd.ExecuteNonQuery(); 682                             count += val; 683                             if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0) 684                             { 685                                 trans.Rollback(); 686                                 return 0; 687                             } 688                             cmd.Parameters.Clear(); 689                         } 690                         trans.Commit(); 691                         return count; 692                     } 693                     catch 694                     { 695                         trans.Rollback(); 696                         throw; 697                     } 698                 } 699             } 700         } 701         /// <summary> 702         /// 执行多条SQL语句,实现数据库事务。 703         /// </summary> 704         /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param> 705         public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.List<CommandInfo> SQLStringList) 706         { 707             using (SqlConnection conn = new SqlConnection(connectionString)) 708             { 709                 conn.Open(); 710                 using (SqlTransaction trans = conn.BeginTransaction()) 711                 { 712                     SqlCommand cmd = new SqlCommand(); 713                     try 714                     { 715                         int indentity = 0; 716                         //循环 717                         foreach (CommandInfo myDE in SQLStringList) 718                         { 719                             string cmdText = myDE.CommandText; 720                             SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters; 721                             foreach (SqlParameter q in cmdParms) 722                             { 723                                 if (q.Direction == ParameterDirection.InputOutput) 724                                 { 725                                     q.Value =http://www.mamicode.com/ indentity; 726                                 } 727                             } 728                             PrepareCommand(cmd, conn, trans, cmdText, cmdParms); 729                             int val = cmd.ExecuteNonQuery(); 730                             foreach (SqlParameter q in cmdParms) 731                             { 732                                 if (q.Direction == ParameterDirection.Output) 733                                 { 734                                     indentity = Convert.ToInt32(q.Value); 735                                 } 736                             } 737                             cmd.Parameters.Clear(); 738                         } 739                         trans.Commit(); 740                     } 741                     catch 742                     { 743                         trans.Rollback(); 744                         throw; 745                     } 746                 } 747             } 748         } 749         /// <summary> 750         /// 执行多条SQL语句,实现数据库事务。 751         /// </summary> 752         /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param> 753         public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList) 754         { 755             using (SqlConnection conn = new SqlConnection(connectionString)) 756             { 757                 conn.Open(); 758                 using (SqlTransaction trans = conn.BeginTransaction()) 759                 { 760                     SqlCommand cmd = new SqlCommand(); 761                     try 762                     { 763                         int indentity = 0; 764                         //循环 765                         foreach (DictionaryEntry myDE in SQLStringList) 766                         { 767                             string cmdText = myDE.Key.ToString(); 768                             SqlParameter[] cmdParms = (SqlParameter[])myDE.Value; 769                             foreach (SqlParameter q in cmdParms) 770                             { 771                                 if (q.Direction == ParameterDirection.InputOutput) 772                                 { 773                                     q.Value =http://www.mamicode.com/ indentity; 774                                 } 775                             } 776                             PrepareCommand(cmd, conn, trans, cmdText, cmdParms); 777                             int val = cmd.ExecuteNonQuery(); 778                             foreach (SqlParameter q in cmdParms) 779                             { 780                                 if (q.Direction == ParameterDirection.Output) 781                                 { 782                                     indentity = Convert.ToInt32(q.Value); 783                                 } 784                             } 785                             cmd.Parameters.Clear(); 786                         } 787                         trans.Commit(); 788                     } 789                     catch 790                     { 791                         trans.Rollback(); 792                         throw; 793                     } 794                 } 795             } 796         } 797         /// <summary> 798         /// 执行一条计算查询结果语句,返回查询结果(object)。 799         /// </summary> 800         /// <param name="SQLString">计算查询结果语句</param> 801         /// <returns>查询结果(object)</returns> 802         public static object GetSingle(string SQLString, params SqlParameter[] cmdParms) 803         { 804             using (SqlConnection connection = new SqlConnection(connectionString)) 805             { 806                 using (SqlCommand cmd = new SqlCommand()) 807                 { 808                     try 809                     { 810                         PrepareCommand(cmd, connection, null, SQLString, cmdParms); 811                         object obj = cmd.ExecuteScalar(); 812                         cmd.Parameters.Clear(); 813                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) 814                         { 815                             return null; 816                         } 817                         else 818                         { 819                             return obj; 820                         } 821                     } 822                     catch (System.Data.SqlClient.SqlException e) 823                     { 824                         throw e; 825                     } 826                 } 827             } 828         } 829  830  831         /// <summary> 832         /// 执行一条计算查询结果语句,返回查询结果(object)。 833         /// </summary> 834         /// <param name="SQLString">计算查询结果语句</param> 835         /// <returns>查询结果(object)</returns> 836         public static object GetSingle(string SQLString, SqlConnection connection, SqlTransaction trans, params SqlParameter[] cmdParms) 837         { 838             using (SqlCommand cmd = new SqlCommand()) 839             { 840                 try 841                 { 842                     PrepareCommand(cmd, connection, trans, SQLString, cmdParms); 843                     object obj = cmd.ExecuteScalar(); 844                     cmd.Parameters.Clear(); 845                     if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) 846                     { 847                         return null; 848                     } 849                     else 850                     { 851                         return obj; 852                     } 853                 } 854                 catch (System.Data.SqlClient.SqlException e) 855                 { 856                     throw e; 857                 } 858             } 859         } 860  861         /// <summary> 862         /// 执行查询语句,返回SqlDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close ) 863         /// </summary> 864         /// <param name="strSQL">查询语句</param> 865         /// <returns>SqlDataReader</returns> 866         public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms) 867         { 868             SqlConnection connection = new SqlConnection(connectionString); 869             SqlCommand cmd = new SqlCommand(); 870             try 871             { 872                 PrepareCommand(cmd, connection, null, SQLString, cmdParms); 873                 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 874                 cmd.Parameters.Clear(); 875                 return myReader; 876             } 877             catch (System.Data.SqlClient.SqlException e) 878             { 879                 throw e; 880             } 881             //            finally 882             //            { 883             //                cmd.Dispose(); 884             //                connection.Close(); 885             //            }     886  887         } 888  889         /// <summary> 890         /// 执行查询语句,返回DataSet 891         /// </summary> 892         /// <param name="SQLString">查询语句</param> 893         /// <returns>DataSet</returns> 894         public static DataSet Query(string SQLString, params SqlParameter[] cmdParms) 895         { 896             using (SqlConnection connection = new SqlConnection(connectionString)) 897             { 898                 SqlCommand cmd = new SqlCommand(); 899                 PrepareCommand(cmd, connection, null, SQLString, cmdParms); 900                 using (SqlDataAdapter da = new SqlDataAdapter(cmd)) 901                 { 902                     DataSet ds = new DataSet(); 903                     try 904                     { 905                         da.Fill(ds, "ds"); 906                         cmd.Parameters.Clear(); 907                     } 908                     catch (System.Data.SqlClient.SqlException ex) 909                     { 910                         throw new Exception(ex.Message); 911                     } 912                     return ds; 913                 } 914             } 915         } 916  917  918         private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms) 919         { 920             if (conn.State != ConnectionState.Open) 921                 conn.Open(); 922             cmd.Connection = conn; 923             cmd.CommandText = cmdText; 924             if (trans != null) 925                 cmd.Transaction = trans; 926             cmd.CommandType = CommandType.Text;//cmdType; 927             if (cmdParms != null) 928             { 929  930  931                 foreach (SqlParameter parameter in cmdParms) 932                 { 933                     if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && 934                         (parameter.Value =http://www.mamicode.com/= null)) 935                     { 936                         parameter.Value =http://www.mamicode.com/ DBNull.Value; 937                     } 938                     cmd.Parameters.Add(parameter); 939                 } 940             } 941         } 942  943         //批量插入数据 944         public static bool BulkInsert(DataTable dt) 945         { 946             bool flag = true; 947             try 948             { 949                 SqlConnection con = new SqlConnection(connectionString); 950                 if (con.State != ConnectionState.Open) 951                 { 952                     con.Open(); 953                 } 954                 SqlBulkCopy bulk = new SqlBulkCopy(con); 955                 for(int i=0;i<dt.Columns.Count;i++) 956                 { 957                     bulk.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName); 958                 } 959  960                 //每次发送的行数 961                 bulk.BatchSize = dt.Rows.Count; 962                 //300秒超时 963                 bulk.BulkCopyTimeout = 300; 964                 bulk.DestinationTableName = dt.TableName; 965                 bulk.WriteToServer(dt); 966  967                 if (con.State != ConnectionState.Closed) 968                 { 969                     con.Close(); 970                 } 971             } 972             catch 973             { 974                 flag = false; 975             } 976             return flag; 977         } 978  979         //返回参数列表 980         public static SqlParameter[] GetPars(System.Collections.Hashtable list) 981         { 982             SqlParameter[] pars = new SqlParameter[list.Count]; 983             int index = 0; 984             foreach (string par in list.Keys) 985             { 986                 pars[index] = new SqlParameter("@" + par, list[par]); 987                 index++; 988             } 989             return pars; 990         } 991  992         #endregion 993  994         #region 存储过程操作 995  996         /// <summary> 997         /// 执行存储过程,返回SqlDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close ) 998         /// </summary> 999         /// <param name="storedProcName">存储过程名</param>1000         /// <param name="parameters">存储过程参数</param>1001         /// <returns>SqlDataReader</returns>1002         public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)1003         {1004             SqlConnection connection = new SqlConnection(connectionString);1005             SqlDataReader returnReader;1006             connection.Open();1007             SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);1008             command.CommandType = CommandType.StoredProcedure;1009             returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);1010             return returnReader;1011 1012         }1013 1014 1015         /// <summary>1016         /// 执行存储过程1017         /// </summary>1018         /// <param name="storedProcName">存储过程名</param>1019         /// <param name="parameters">存储过程参数</param>1020         /// <param name="tableName">DataSet结果中的表名</param>1021         /// <returns>DataSet</returns>1022         public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)1023         {1024             using (SqlConnection connection = new SqlConnection(connectionString))1025             {1026                 DataSet dataSet = new DataSet();1027                 connection.Open();1028                 SqlDataAdapter sqlDA = new SqlDataAdapter();1029                 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);1030                 sqlDA.Fill(dataSet, tableName);1031                 connection.Close();1032                 return dataSet;1033             }1034         }1035         public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times)1036         {1037             using (SqlConnection connection = new SqlConnection(connectionString))1038             {1039                 DataSet dataSet = new DataSet();1040                 connection.Open();1041                 SqlDataAdapter sqlDA = new SqlDataAdapter();1042                 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);1043                 sqlDA.SelectCommand.CommandTimeout = Times;1044                 sqlDA.Fill(dataSet, tableName);1045                 connection.Close();1046                 return dataSet;1047             }1048         }1049 1050 1051         /// <summary>1052         /// 构建 SqlCommand 对象(用来返回一个结果集,而不是一个整数值)1053         /// </summary>1054         /// <param name="connection">数据库连接</param>1055         /// <param name="storedProcName">存储过程名</param>1056         /// <param name="parameters">存储过程参数</param>1057         /// <returns>SqlCommand</returns>1058         private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)1059         {1060             SqlCommand command = new SqlCommand(storedProcName, connection);1061             command.CommandType = CommandType.StoredProcedure;1062             foreach (SqlParameter parameter in parameters)1063             {1064                 if (parameter != null)1065                 {1066                     // 检查未分配值的输出参数,将其分配以DBNull.Value.1067                     if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&1068                         (parameter.Value =http://www.mamicode.com/= null))1069                     {1070                         parameter.Value =http://www.mamicode.com/ DBNull.Value;1071                     }1072                     command.Parameters.Add(parameter);1073                 }1074             }1075 1076             return command;1077         }1078 1079         /// <summary>1080         /// 执行存储过程,返回影响的行数        1081         /// </summary>1082         /// <param name="storedProcName">存储过程名</param>1083         /// <param name="parameters">存储过程参数</param>1084         /// <param name="rowsAffected">影响的行数</param>1085         /// <returns></returns>1086         public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)1087         {1088             using (SqlConnection connection = new SqlConnection(connectionString))1089             {1090                 int result;1091                 connection.Open();1092                 SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);1093                 rowsAffected = command.ExecuteNonQuery();1094                 result = (int)command.Parameters["ReturnValue"].Value;1095                 //Connection.Close();1096                 return result;1097             }1098         }1099 1100         /// <summary>1101         /// 创建 SqlCommand 对象实例(用来返回一个整数值)    1102         /// </summary>1103         /// <param name="storedProcName">存储过程名</param>1104         /// <param name="parameters">存储过程参数</param>1105         /// <returns>SqlCommand 对象实例</returns>1106         private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)1107         {1108             SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);1109             command.Parameters.Add(new SqlParameter("ReturnValue",1110                 SqlDbType.Int, 4, ParameterDirection.ReturnValue,1111                 false, 0, 0, string.Empty, DataRowVersion.Default, null));1112             return command;1113         }1114         #endregion1115 1116     }1117 1118 }
View Code

 

  1 /// <summary>  2         /// 增加一条数据  3         /// </summary>  4         public bool Add(EIM.Model.BD.BD_IPModel model)  5         {  6             StringBuilder strSql=new StringBuilder();  7             strSql.Append("insert into BD_IP(");  8             strSql.Append("ID,IpStart,IpEnd,Country,Area,County,Region,City,Isp,Country_id,Area_id,Region_id,City_id,Isp_id,County_id)");  9             strSql.Append(" values ("); 10             strSql.Append("@ID,@IpStart,@IpEnd,@Country,@Area,@County,@Region,@City,@Isp,@Country_id,@Area_id,@Region_id,@City_id,@Isp_id,@County_id)"); 11             SqlParameter[] parameters = { 12                     new SqlParameter("@ID", SqlDbType.BigInt,8), 13                     new SqlParameter("@IpStart", SqlDbType.NVarChar,50), 14                     new SqlParameter("@IpEnd", SqlDbType.NVarChar,50), 15                     new SqlParameter("@Country", SqlDbType.NVarChar,100), 16                     new SqlParameter("@Area", SqlDbType.NVarChar,100), 17                     new SqlParameter("@County", SqlDbType.NVarChar,100), 18                     new SqlParameter("@Region", SqlDbType.NVarChar,100), 19                     new SqlParameter("@City", SqlDbType.NVarChar,100), 20                     new SqlParameter("@Isp", SqlDbType.NVarChar,50), 21                     new SqlParameter("@Country_id", SqlDbType.NVarChar,50), 22                     new SqlParameter("@Area_id", SqlDbType.NVarChar,50), 23                     new SqlParameter("@Region_id", SqlDbType.NVarChar,50), 24                     new SqlParameter("@City_id", SqlDbType.NVarChar,50), 25                     new SqlParameter("@Isp_id", SqlDbType.NVarChar,50), 26                     new SqlParameter("@County_id", SqlDbType.NVarChar,50)}; 27             parameters[0].Value =http://www.mamicode.com/ model.ID; 28             parameters[1].Value =http://www.mamicode.com/ model.IpStart; 29             parameters[2].Value =http://www.mamicode.com/ model.IpEnd; 30             parameters[3].Value =http://www.mamicode.com/ model.Country; 31             parameters[4].Value =http://www.mamicode.com/ model.Area; 32             parameters[5].Value =http://www.mamicode.com/ model.County; 33             parameters[6].Value =http://www.mamicode.com/ model.Region; 34             parameters[7].Value =http://www.mamicode.com/ model.City; 35             parameters[8].Value =http://www.mamicode.com/ model.Isp; 36             parameters[9].Value =http://www.mamicode.com/ model.Country_id; 37             parameters[10].Value =http://www.mamicode.com/ model.Area_id; 38             parameters[11].Value =http://www.mamicode.com/ model.Region_id; 39             parameters[12].Value =http://www.mamicode.com/ model.City_id; 40             parameters[13].Value =http://www.mamicode.com/ model.Isp_id; 41             parameters[14].Value =http://www.mamicode.com/ model.County_id; 42  43             int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters); 44             if (rows > 0) 45             { 46                 return true; 47             } 48             else 49             { 50                 return false; 51             } 52         } 53         /// <summary> 54         /// 更新一条数据 55         /// </summary> 56         public bool Update(EIM.Model.BD.BD_IPModel model) 57         { 58             StringBuilder strSql=new StringBuilder(); 59             strSql.Append("update BD_IP set "); 60             strSql.Append("ID=@ID,"); 61             strSql.Append("IpStart=@IpStart,"); 62             strSql.Append("IpEnd=@IpEnd,"); 63             strSql.Append("Country=@Country,"); 64             strSql.Append("Area=@Area,"); 65             strSql.Append("County=@County,"); 66             strSql.Append("Region=@Region,"); 67             strSql.Append("City=@City,"); 68             strSql.Append("Isp=@Isp,"); 69             strSql.Append("Country_id=@Country_id,"); 70             strSql.Append("Area_id=@Area_id,"); 71             strSql.Append("Region_id=@Region_id,"); 72             strSql.Append("City_id=@City_id,"); 73             strSql.Append("Isp_id=@Isp_id,"); 74             strSql.Append("County_id=@County_id"); 75             strSql.Append(" where "); 76             SqlParameter[] parameters = { 77                     new SqlParameter("@ID", SqlDbType.BigInt,8), 78                     new SqlParameter("@IpStart", SqlDbType.NVarChar,50), 79                     new SqlParameter("@IpEnd", SqlDbType.NVarChar,50), 80                     new SqlParameter("@Country", SqlDbType.NVarChar,100), 81                     new SqlParameter("@Area", SqlDbType.NVarChar,100), 82                     new SqlParameter("@County", SqlDbType.NVarChar,100), 83                     new SqlParameter("@Region", SqlDbType.NVarChar,100), 84                     new SqlParameter("@City", SqlDbType.NVarChar,100), 85                     new SqlParameter("@Isp", SqlDbType.NVarChar,50), 86                     new SqlParameter("@Country_id", SqlDbType.NVarChar,50), 87                     new SqlParameter("@Area_id", SqlDbType.NVarChar,50), 88                     new SqlParameter("@Region_id", SqlDbType.NVarChar,50), 89                     new SqlParameter("@City_id", SqlDbType.NVarChar,50), 90                     new SqlParameter("@Isp_id", SqlDbType.NVarChar,50), 91                     new SqlParameter("@County_id", SqlDbType.NVarChar,50)}; 92             parameters[0].Value =http://www.mamicode.com/ model.ID; 93             parameters[1].Value =http://www.mamicode.com/ model.IpStart; 94             parameters[2].Value =http://www.mamicode.com/ model.IpEnd; 95             parameters[3].Value =http://www.mamicode.com/ model.Country; 96             parameters[4].Value =http://www.mamicode.com/ model.Area; 97             parameters[5].Value =http://www.mamicode.com/ model.County; 98             parameters[6].Value =http://www.mamicode.com/ model.Region; 99             parameters[7].Value =http://www.mamicode.com/ model.City;100             parameters[8].Value =http://www.mamicode.com/ model.Isp;101             parameters[9].Value =http://www.mamicode.com/ model.Country_id;102             parameters[10].Value =http://www.mamicode.com/ model.Area_id;103             parameters[11].Value =http://www.mamicode.com/ model.Region_id;104             parameters[12].Value =http://www.mamicode.com/ model.City_id;105             parameters[13].Value =http://www.mamicode.com/ model.Isp_id;106             parameters[14].Value =http://www.mamicode.com/ model.County_id;107 108             int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);109             if (rows > 0)110             {111                 return true;112             }113             else114             {115                 return false;116             }117         }118 119         /// <summary>120         /// 删除一条数据121         /// </summary>122         public bool Delete()123         {124             //该表无主键信息,请自定义主键/条件字段125             StringBuilder strSql=new StringBuilder();126             strSql.Append("delete from BD_IP ");127             strSql.Append(" where ");128             SqlParameter[] parameters = {129             };130 131             int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);132             if (rows > 0)133             {134                 return true;135             }136             else137             {138                 return false;139             }140         }141 142 143         /// <summary>144         /// 得到一个对象实体145         /// </summary>146         public EIM.Model.BD.BD_IPModel GetModel()147         {148             //该表无主键信息,请自定义主键/条件字段149             StringBuilder strSql=new StringBuilder();150             strSql.Append("select  top 1 ID,IpStart,IpEnd,Country,Area,County,Region,City,Isp,Country_id,Area_id,Region_id,City_id,Isp_id,County_id from BD_IP ");151             strSql.Append(" where ");152             SqlParameter[] parameters = {153             };154 155             EIM.Model.BD.BD_IPModel model=new EIM.Model.BD.BD_IPModel();156             DataSet ds=DbHelperSQL.Query(strSql.ToString(),parameters);157             if(ds.Tables[0].Rows.Count>0)158             {159                 return DataRowToModel(ds.Tables[0].Rows[0]);160             }161             else162             {163                 return null;164             }165         }166 167 168         /// <summary>169         /// 得到一个对象实体170         /// </summary>171         public EIM.Model.BD.BD_IPModel DataRowToModel(DataRow row)172         {173             EIM.Model.BD.BD_IPModel model=new EIM.Model.BD.BD_IPModel();174             if (row != null)175             {176                 if(row["ID"]!=null && row["ID"].ToString()!="")177                 {178                     model.ID=long.Parse(row["ID"].ToString());179                 }180                 if(row["IpStart"]!=null)181                 {182                     model.IpStart=row["IpStart"].ToString();183                 }184                 if(row["IpEnd"]!=null)185                 {186                     model.IpEnd=row["IpEnd"].ToString();187                 }188                 if(row["Country"]!=null)189                 {190                     model.Country=row["Country"].ToString();191                 }192                 if(row["Area"]!=null)193                 {194                     model.Area=row["Area"].ToString();195                 }196                 if(row["County"]!=null)197                 {198                     model.County=row["County"].ToString();199                 }200                 if(row["Region"]!=null)201                 {202                     model.Region=row["Region"].ToString();203                 }204                 if(row["City"]!=null)205                 {206                     model.City=row["City"].ToString();207                 }208                 if(row["Isp"]!=null)209                 {210                     model.Isp=row["Isp"].ToString();211                 }212                 if(row["Country_id"]!=null)213                 {214                     model.Country_id=row["Country_id"].ToString();215                 }216                 if(row["Area_id"]!=null)217                 {218                     model.Area_id=row["Area_id"].ToString();219                 }220                 if(row["Region_id"]!=null)221                 {222                     model.Region_id=row["Region_id"].ToString();223                 }224                 if(row["City_id"]!=null)225                 {226                     model.City_id=row["City_id"].ToString();227                 }228                 if(row["Isp_id"]!=null)229                 {230                     model.Isp_id=row["Isp_id"].ToString();231                 }232                 if(row["County_id"]!=null)233                 {234                     model.County_id=row["County_id"].ToString();235                 }236             }237             return model;238         }239 240         /// <summary>241         /// 获得数据列表242         /// </summary>243         public DataSet GetList(string strWhere)244         {245             StringBuilder strSql=new StringBuilder();246             strSql.Append("select ID,IpStart,IpEnd,Country,Area,County,Region,City,Isp,Country_id,Area_id,Region_id,City_id,Isp_id,County_id ");247             strSql.Append(" FROM BD_IP ");248             if(strWhere.Trim()!="")249             {250                 strSql.Append(" where "+strWhere);251             }252             return DbHelperSQL.Query(strSql.ToString());253         }254 255         /// <summary>256         /// 获得前几行数据257         /// </summary>258         public DataSet GetList(int Top,string strWhere,string filedOrder)259         {260             StringBuilder strSql=new StringBuilder();261             strSql.Append("select ");262             if(Top>0)263             {264                 strSql.Append(" top "+Top.ToString());265             }266             strSql.Append(" ID,IpStart,IpEnd,Country,Area,County,Region,City,Isp,Country_id,Area_id,Region_id,City_id,Isp_id,County_id ");267             strSql.Append(" FROM BD_IP ");268             if(strWhere.Trim()!="")269             {270                 strSql.Append(" where "+strWhere);271             }272             strSql.Append(" order by " + filedOrder);273             return DbHelperSQL.Query(strSql.ToString());274         }275 276         /// <summary>277         /// 获取记录总数278         /// </summary>279         public int GetRecordCount(string strWhere)280         {281             StringBuilder strSql=new StringBuilder();282             strSql.Append("select count(1) FROM BD_IP ");283             if(strWhere.Trim()!="")284             {285                 strSql.Append(" where "+strWhere);286             }287             object obj = DbHelperSQL.GetSingle(strSql.ToString());288             if (obj == null)289             {290                 return 0;291             }292             else293             {294                 return Convert.ToInt32(obj);295             }296         }297         /// <summary>298         /// 分页获取数据列表299         /// </summary>300         public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)301         {302             StringBuilder strSql=new StringBuilder();303             strSql.Append("SELECT * FROM ( ");304             strSql.Append(" SELECT ROW_NUMBER() OVER (");305             if (!string.IsNullOrEmpty(orderby.Trim()))306             {307                 strSql.Append("order by T." + orderby );308             }309             else310             {311                 strSql.Append("order by T. desc");312             }313             strSql.Append(")AS Row, T.*  from BD_IP T ");314             if (!string.IsNullOrEmpty(strWhere.Trim()))315             {316                 strSql.Append(" WHERE " + strWhere);317             }318             strSql.Append(" ) TT");319             strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);320             return DbHelperSQL.Query(strSql.ToString());321         }322 323         /*324         /// <summary>325         /// 分页获取数据列表326         /// </summary>327         public DataSet GetList(int PageSize,int PageIndex,string strWhere)328         {329             SqlParameter[] parameters = {330                     new SqlParameter("@tblName", SqlDbType.VarChar, 255),331                     new SqlParameter("@fldName", SqlDbType.VarChar, 255),332                     new SqlParameter("@PageSize", SqlDbType.Int),333                     new SqlParameter("@PageIndex", SqlDbType.Int),334                     new SqlParameter("@IsReCount", SqlDbType.Bit),335                     new SqlParameter("@OrderType", SqlDbType.Bit),336                     new SqlParameter("@strWhere", SqlDbType.VarChar,1000),337                     };338             parameters[0].Valuehttp://www.mamicode.com/= "http://www.mamicode.com/BD_IP";339             parameters[1].Valuehttp://www.mamicode.com/= "";340             parameters[2].Value = http://www.mamicode.com/PageSize;>341             parameters[3].Value = http://www.mamicode.com/PageIndex;>342             parameters[4].Value = http://www.mamicode.com/0;>343             parameters[5].Value = http://www.mamicode.com/0;>344             parameters[6].Value = http://www.mamicode.com/strWhere;    >345             return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");346         }*/
View Code

 

底层数据访问层代码