C#操作sqlserver的DbHelper类（带分页）1、配置⽂件设置：<appSettings><add key="testDB" value="server=192.168.1.10\sw;database=testDB;uid=sa;pwd=Aa123456" /></appSettings>2、操作数据帮助类：using System;using System.Collections.Generic;using System.Configuration;using System.Data;using mon;using System.Data.SqlClient;using System.Linq;using System.Text;using System.Threading.Tasks;namespace SwbAPI{public class DbHelper{//连接字符串static string strConn = ConfigurationManager.AppSettings["JiTuanShangWuDB"].ToString();//static string strConn = "server=192.168.1.238\sw;database=BusinessSystemDB;uid=sa;pwd=Aa123456";#region执⾏查询，返回DataTable对象-----------------------public static DataTable GetTable(string strSQL){return GetTable(strSQL, null);}public static DataTable GetTable(string strSQL, SqlParameter[] pas){return GetTable(strSQL, pas, CommandType.Text);}///<summary>///执⾏查询，返回DataTable对象///</summary>///<param name="strSQL">sql语句</param>///<param name="pas">参数数组</param>///<param name="cmdtype">Command类型</param>///<returns>DataTable对象</returns>public static DataTable GetTable(string strSQL, SqlParameter[] pas, CommandType cmdtype){DataTable dt = new DataTable(); ;using (SqlConnection conn = new SqlConnection(strConn)){SqlDataAdapter da = new SqlDataAdapter(strSQL, conn);mandType = cmdtype;if (pas != null){da.SelectCommand.Parameters.AddRange(pas);}da.Fill(dt);}return dt;}#region根据枚举连接数据库public static DataTable GetTable(string strSQL, DataBaseName dataBaseName){return GetTable(strSQL, null, dataBaseName);}public static DataTable GetTable(string strSQL, SqlParameter[] pas, DataBaseName dataBaseName){return GetTable(strSQL, pas, CommandType.Text, dataBaseName);}///<summary>///执⾏查询，返回DataTable对象///</summary>///<param name="strSQL">sql语句</param>///<param name="pas">参数数组</param>///<param name="cmdtype">Command类型</param>///<returns>DataTable对象</returns>public static DataTable GetTable(string strSQL, SqlParameter[] pas, CommandType cmdtype, DataBaseName dataBaseName){DataTable dt = new DataTable(); ;using (SqlConnection conn = new SqlConnection(GetEnumDes(dataBaseName.ToString()))){SqlDataAdapter da = new SqlDataAdapter(strSQL, conn);mandType = cmdtype;if (pas != null)}#endregion#endregion#region执⾏查询，返回DataSet对象-------------------------public static DataSet GetDataSet(string strSQL){return GetDataSet(strSQL, null);}public static DataSet GetDataSet(string strSQL, SqlParameter[] pas){return GetDataSet(strSQL, pas, CommandType.Text);}///<summary>///执⾏查询，返回DataSet对象///</summary>///<param name="strSQL">sql语句</param>///<param name="pas">参数数组</param>///<param name="cmdtype">Command类型</param>///<returns>DataSet对象</returns>public static DataSet GetDataSet(string strSQL, SqlParameter[] pas, CommandType cmdtype){DataSet dt = new DataSet(); ;using (SqlConnection conn = new SqlConnection(strConn)){SqlDataAdapter da = new SqlDataAdapter(strSQL, conn);mandType = cmdtype;if (pas != null){da.SelectCommand.Parameters.AddRange(pas);}da.Fill(dt);if (dt != null && dt.Tables.Count == 2){dt.Tables[0].TableName = "DataList";dt.Tables[1].TableName = "DataTotal";}else if (dt != null && dt.Tables.Count == 1){dt.Tables[0].TableName = "DataList";}}return dt;}#region根据枚举连接数据库public static DataSet GetDataSet(string strSQL, DataBaseName dataBaseName){return GetDataSet(strSQL, null, dataBaseName);}public static DataSet GetDataSet(string strSQL, SqlParameter[] pas, DataBaseName dataBaseName){return GetDataSet(strSQL, pas, CommandType.Text, dataBaseName);}///<summary>///执⾏查询，返回DataSet对象///</summary>///<param name="strSQL">sql语句</param>///<param name="pas">参数数组</param>///<param name="cmdtype">Command类型</param>///<returns>DataSet对象</returns>public static DataSet GetDataSet(string strSQL, SqlParameter[] pas, CommandType cmdtype, DataBaseName dataBaseName) {DataSet dt = new DataSet(); ;using (SqlConnection conn = new SqlConnection(GetEnumDes(dataBaseName.ToString()))){SqlDataAdapter da = new SqlDataAdapter(strSQL, conn);mandType = cmdtype;if (pas != null){da.SelectCommand.Parameters.AddRange(pas);}da.Fill(dt);if (dt != null && dt.Tables.Count == 2){dt.Tables[0].TableName = "DataList";dt.Tables[1].TableName = "DataTotal";}else if (dt != null && dt.Tables.Count == 1){#endregion#region执⾏⾮查询存储过程和SQL语句-----------------------------public static int ExcuteProc(string ProcName){return ExcuteSQL(ProcName, null, CommandType.StoredProcedure);}public static int ExcuteProc(string ProcName, SqlParameter[] pars){return ExcuteSQL(ProcName, pars, CommandType.StoredProcedure);}public static int ExcuteSQL(string strSQL){return ExcuteSQL(strSQL, null);}public static int ExcuteSQL(string strSQL, SqlParameter[] paras){return ExcuteSQL(strSQL, paras, CommandType.Text);}///执⾏⾮查询存储过程和SQL语句///增、删、改///</summary>///<param name="strSQL">要执⾏的SQL语句</param>///<param name="paras">参数列表，没有参数填⼊null</param>///<param name="cmdType">Command类型</param>///<returns>返回影响⾏数</returns>public static int ExcuteSQL(string strSQL, SqlParameter[] paras, CommandType cmdType){int i = 0;using (SqlConnection conn = new SqlConnection(strConn)){SqlCommand cmd = new SqlCommand(strSQL, conn);mandType = cmdType;if (paras != null){cmd.Parameters.AddRange(paras);}conn.Open();i = cmd.ExecuteNonQuery();conn.Close();}return i;}#region执⾏sql返回⾃增ID///<summary>///执⾏SQL语句，返回⾃增ID///</summary>///<param name="SQLString">SQL语句</param>///<returns>影响的记录数</returns>public static int ExecuteSQLReturnID(string SQLString){using (SqlConnection connection = new SqlConnection(strConn)){using (SqlCommand cmd = new SqlCommand(SQLString, connection)){try{connection.Open();mandText += ";\nSELECT SCOPE_IDENTITY() AS NewID;";int id = Convert.ToInt32(cmd.ExecuteScalar());return id;}catch (System.Data.SqlClient.SqlException e){connection.Close();throw e;}}}}#endregion#region执⾏sql返回⾃增ID///<summary>///执⾏SQL语句，返回⾃增ID///</summary>///<param name="SQLString">SQL语句</param>///<returns>影响的记录数</returns>public static int ExecuteSQLReturnID(string SQLString, SqlParameter[] paras, DataBaseName dataBaseName) {try{if (paras != null){cmd.Parameters.AddRange(paras);}connection.Open();mandText += ";\nSELECT SCOPE_IDENTITY() AS NewID;";int id = Convert.ToInt32(cmd.ExecuteScalar());return id;}catch (System.Data.SqlClient.SqlException e){connection.Close();throw e;}}}}#endregion#region根据枚举连接数据库public static int ExcuteProc(string ProcName, DataBaseName dataBaseName){return ExcuteSQL(ProcName, null, CommandType.StoredProcedure, dataBaseName);}public static int ExcuteProc(string ProcName, SqlParameter[] pars, DataBaseName dataBaseName){return ExcuteSQL(ProcName, pars, CommandType.StoredProcedure, dataBaseName);}public static int ExcuteSQL(string strSQL, DataBaseName dataBaseName){return ExcuteSQL(strSQL, null, dataBaseName);}public static int ExcuteSQL(string strSQL, SqlParameter[] paras, DataBaseName dataBaseName){return ExcuteSQL(strSQL, paras, CommandType.Text, dataBaseName);}///执⾏⾮查询存储过程和SQL语句///增、删、改///</summary>///<param name="strSQL">要执⾏的SQL语句</param>///<param name="paras">参数列表，没有参数填⼊null</param>///<param name="cmdType">Command类型</param>///<returns>返回影响⾏数</returns>public static int ExcuteSQL(string strSQL, SqlParameter[] paras, CommandType cmdType, DataBaseName dataBaseName) {int i = 0;using (SqlConnection conn = new SqlConnection(GetEnumDes(dataBaseName.ToString()))){SqlCommand cmd = new SqlCommand(strSQL, conn);mandType = cmdType;if (paras != null){cmd.Parameters.AddRange(paras);}conn.Open();i = cmd.ExecuteNonQuery();conn.Close();}return i;}#region执⾏sql返回⾃增ID///<summary>///执⾏SQL语句，返回⾃增ID///</summary>///<param name="SQLString">SQL语句</param>///<returns>影响的记录数</returns>public static int ExecuteSQLReturnID(string SQLString, DataBaseName dataBaseName){using (SqlConnection connection = new SqlConnection(GetEnumDes(dataBaseName.ToString()))){using (SqlCommand cmd = new SqlCommand(SQLString, connection)){try{connection.Open();mandText += ";\nSELECT SCOPE_IDENTITY() AS NewID;";int id = Convert.ToInt32(cmd.ExecuteScalar());return id;}catch (System.Data.SqlClient.SqlException e){connection.Close();throw e;#endregion#endregion#endregion#region执⾏查询返回第⼀⾏，第⼀列---------------------------------///<summary>///执⾏SQL语句，返回第⼀⾏，第⼀列///</summary>///<param name="strSQL">要执⾏的SQL语句</param>///<param name="paras">参数列表，没有参数填⼊null</param>///<param name="paras">执⾏类型，</param>///<returns>返回结果</returns>public static object ExcuteScalarSQLObj(string strSQL, SqlParameter[] paras){object i ;using (SqlConnection conn = new SqlConnection(strConn)){SqlCommand cmd = new SqlCommand(strSQL, conn);mandType = CommandType.Text;if (paras != null){cmd.Parameters.AddRange(paras);}conn.Open();if (DBNull.Value == cmd.ExecuteScalar()){i = null;}else{i =cmd.ExecuteScalar();}conn.Close();}return i;}public static int ExcuteScalarSQL(string strSQL){return ExcuteScalarSQL(strSQL, null);}public static int ExcuteScalarSQL(string strSQL, SqlParameter[] paras){return ExcuteScalarSQL(strSQL, paras, CommandType.Text);}public static int ExcuteScalarProc(string strSQL, SqlParameter[] paras){return ExcuteScalarSQL(strSQL, paras, CommandType.StoredProcedure);}///<summary>///执⾏SQL语句，返回第⼀⾏，第⼀列///</summary>///<param name="strSQL">要执⾏的SQL语句</param>///<param name="paras">参数列表，没有参数填⼊null</param>///<returns>返回影响⾏数</returns>public static int ExcuteScalarSQL(string strSQL, SqlParameter[] paras, CommandType cmdType) {int i = 0;using (SqlConnection conn = new SqlConnection(strConn)){SqlCommand cmd = new SqlCommand(strSQL, conn);mandType = cmdType;if (paras != null){cmd.Parameters.AddRange(paras);}conn.Open();if (DBNull.Value == cmd.ExecuteScalar()){i = 0;}else{i = Convert.ToInt32(cmd.ExecuteScalar());}conn.Close();}return i;}#region根据枚举连接数据库public static int ExcuteScalarSQL(string strSQL, DataBaseName dataBaseName){return ExcuteScalarSQL(strSQL, paras, CommandType.Text, dataBaseName);}public static int ExcuteScalarProc(string strSQL, SqlParameter[] paras, DataBaseName dataBaseName){return ExcuteScalarSQL(strSQL, paras, CommandType.StoredProcedure, dataBaseName);}///<summary>///执⾏SQL语句，返回第⼀⾏，第⼀列///</summary>///<param name="strSQL">要执⾏的SQL语句</param>///<param name="paras">参数列表，没有参数填⼊null</param>///<returns>返回影响⾏数</returns>public static int ExcuteScalarSQL(string strSQL, SqlParameter[] paras, CommandType cmdType, DataBaseName dataBaseName) {int i = 0;using (SqlConnection conn = new SqlConnection(GetEnumDes(dataBaseName.ToString()))){SqlCommand cmd = new SqlCommand(strSQL, conn);mandType = cmdType;if (paras != null){cmd.Parameters.AddRange(paras);}conn.Open();if (DBNull.Value == cmd.ExecuteScalar()){i = 0;}else{i = Convert.ToInt32(cmd.ExecuteScalar());}conn.Close();}return i;}#endregion#endregion#region查询获取单个值------------------------------------///<summary>///调⽤不带参数的存储过程获取单个值///</summary>///<param name="ProcName"></param>///<returns></returns>public static object GetObjectByProc(string ProcName){return GetObjectByProc(ProcName, null);}///<summary>///调⽤带参数的存储过程获取单个值///</summary>///<param name="ProcName"></param>///<param name="paras"></param>///<returns></returns>public static object GetObjectByProc(string ProcName, SqlParameter[] paras){return GetObject(ProcName, paras, CommandType.StoredProcedure);}///<summary>///根据sql语句获取单个值///</summary>///<param name="strSQL"></param>///<returns></returns>public static object GetObject(string strSQL){return GetObject(strSQL, null);}///<summary>///根据sql语句和参数数组获取单个值///</summary>///<param name="strSQL"></param>///<param name="paras"></param>///<returns></returns>public static object GetObject(string strSQL, SqlParameter[] paras){return GetObject(strSQL, paras, CommandType.Text);}///<summary>///执⾏SQL语句，返回⾸⾏⾸列///</summary>///<param name="strSQL">要执⾏的SQL语句</param>///<param name="paras">参数列表，没有参数填⼊null</param>///<returns>返回的⾸⾏⾸列</returns>{SqlCommand cmd = new SqlCommand(strSQL, conn);mandType = cmdtype;if (paras != null){cmd.Parameters.AddRange(paras);}conn.Open();o = cmd.ExecuteScalar();conn.Close();}return o;}#region根据枚举连接数据库///<summary>///调⽤不带参数的存储过程获取单个值///</summary>///<param name="ProcName"></param>///<returns></returns>public static object GetObjectByProc(string ProcName, DataBaseName dataBaseName){return GetObjectByProc(ProcName, null, dataBaseName);}///<summary>///调⽤带参数的存储过程获取单个值///</summary>///<param name="ProcName"></param>///<param name="paras"></param>///<returns></returns>public static object GetObjectByProc(string ProcName, SqlParameter[] paras, DataBaseName dataBaseName){return GetObject(ProcName, paras, CommandType.StoredProcedure, dataBaseName);}///<summary>///根据sql语句获取单个值///</summary>///<param name="strSQL"></param>///<returns></returns>public static object GetObject(string strSQL, DataBaseName dataBaseName){return GetObject(strSQL, null, dataBaseName);}///<summary>///根据sql语句和参数数组获取单个值///</summary>///<param name="strSQL"></param>///<param name="paras"></param>///<returns></returns>public static object GetObject(string strSQL, SqlParameter[] paras, DataBaseName dataBaseName){return GetObject(strSQL, paras, CommandType.Text, dataBaseName);}///<summary>///执⾏SQL语句，返回⾸⾏⾸列///</summary>///<param name="strSQL">要执⾏的SQL语句</param>///<param name="paras">参数列表，没有参数填⼊null</param>///<returns>返回的⾸⾏⾸列</returns>public static object GetObject(string strSQL, SqlParameter[] paras, CommandType cmdtype, DataBaseName dataBaseName) {object o = null;using (SqlConnection conn = new SqlConnection(GetEnumDes(dataBaseName.ToString()))){SqlCommand cmd = new SqlCommand(strSQL, conn);mandType = cmdtype;if (paras != null){cmd.Parameters.AddRange(paras);}conn.Open();o = cmd.ExecuteScalar();conn.Close();}return o;}#endregion#endregion#region查询获取DataReader------------------------------------///<returns>DataReader对象</returns>public static SqlDataReader GetReaderByProc(string procName){return GetReaderByProc(procName, null);}///<summary>///调⽤带有参数的存储过程，返回DataReader对象///</summary>///<param name="procName">存储过程名</param>///<param name="paras">参数数组</param>///<returns>DataReader对象</returns>public static SqlDataReader GetReaderByProc(string procName, SqlParameter[] paras){return GetReader(procName, paras, CommandType.StoredProcedure);}///<summary>///根据sql语句返回DataReader对象///</summary>///<param name="strSQL">sql语句</param>///<returns>DataReader对象</returns>public static SqlDataReader GetReader(string strSQL){return GetReader(strSQL, null);}///<summary>///根据sql语句和参数返回DataReader对象///</summary>///<param name="strSQL">sql语句</param>///<param name="paras">参数数组</param>///<returns>DataReader对象</returns>public static SqlDataReader GetReader(string strSQL, SqlParameter[] paras){return GetReader(strSQL, paras, CommandType.Text);}///<summary>///查询SQL语句获取DataReader///</summary>///<param name="strSQL">查询的SQL语句</param>///<param name="paras">参数列表，没有参数填⼊null</param>///<returns>查询到的DataReader（关闭该对象的时候，⾃动关闭连接）</returns>public static SqlDataReader GetReader(string strSQL, SqlParameter[] paras, CommandType cmdtype){SqlDataReader sqldr = null;SqlConnection conn = new SqlConnection(strConn);SqlCommand cmd = new SqlCommand(strSQL, conn);mandType = cmdtype;if (paras != null){cmd.Parameters.AddRange(paras);}conn.Open();//CommandBehavior.CloseConnection的作⽤是如果关联的DataReader对象关闭，则连接⾃动关闭sqldr = cmd.ExecuteReader(CommandBehavior.CloseConnection);return sqldr;}#region根据枚举连接数据库///<summary>///调⽤不带参数的存储过程，返回DataReader对象///</summary>///<param name="procName">存储过程名称</param>///<returns>DataReader对象</returns>public static SqlDataReader GetReaderByProc(string procName, DataBaseName dataBaseName){return GetReaderByProc(procName, null, dataBaseName);}///<summary>///调⽤带有参数的存储过程，返回DataReader对象///</summary>///<param name="procName">存储过程名</param>///<param name="paras">参数数组</param>///<returns>DataReader对象</returns>public static SqlDataReader GetReaderByProc(string procName, SqlParameter[] paras, DataBaseName dataBaseName) {return GetReader(procName, paras, CommandType.StoredProcedure, dataBaseName);}///<summary>///根据sql语句返回DataReader对象///</summary>///<param name="strSQL">sql语句</param>///<returns>DataReader对象</returns>public static SqlDataReader GetReader(string strSQL, DataBaseName dataBaseName){return GetReader(strSQL, null, dataBaseName);}///<summary>///根据sql语句和参数返回DataReader对象public static SqlDataReader GetReader(string strSQL, SqlParameter[] paras, DataBaseName dataBaseName){return GetReader(strSQL, paras, CommandType.Text, dataBaseName);}///<summary>///查询SQL语句获取DataReader///</summary>///<param name="strSQL">查询的SQL语句</param>///<param name="paras">参数列表，没有参数填⼊null</param>///<returns>查询到的DataReader（关闭该对象的时候，⾃动关闭连接）</returns>public static SqlDataReader GetReader(string strSQL, SqlParameter[] paras, CommandType cmdtype, DataBaseName dataBaseName) {SqlDataReader sqldr = null;SqlConnection conn = new SqlConnection(GetEnumDes(dataBaseName.ToString()));SqlCommand cmd = new SqlCommand(strSQL, conn);mandType = cmdtype;if (paras != null){cmd.Parameters.AddRange(paras);}conn.Open();//CommandBehavior.CloseConnection的作⽤是如果关联的DataReader对象关闭，则连接⾃动关闭sqldr = cmd.ExecuteReader(CommandBehavior.CloseConnection);return sqldr;}#endregion#endregion#region批量插⼊数据---------------------------------------------///<summary>///往数据库中批量插⼊数据///</summary>///<param name="sourceDt">数据源表</param>///<param name="targetTable">服务器上⽬标表</param>public static void BulkToDB(DataTable sourceDt, string targetTable){SqlConnection conn = new SqlConnection(strConn);SqlBulkCopy bulkCopy = new SqlBulkCopy(conn); //⽤其它源的数据有效批量加载sql server表中bulkCopy.DestinationTableName = targetTable; //服务器上⽬标表的名称bulkCopy.BatchSize = sourceDt.Rows.Count; //每⼀批次中的⾏数try{conn.Open();if (sourceDt != null && sourceDt.Rows.Count != 0)bulkCopy.WriteToServer(sourceDt); //将提供的数据源中的所有⾏复制到⽬标表中}catch (Exception ex){throw ex;}finally{conn.Close();if (bulkCopy != null)bulkCopy.Close();}}#region根据枚举连接数据库///<summary>///往数据库中批量插⼊数据///</summary>///<param name="sourceDt">数据源表</param>///<param name="targetTable">服务器上⽬标表</param>public static void BulkToDB(DataTable sourceDt, string targetTable, DataBaseName dataBaseName){SqlConnection conn = new SqlConnection(GetEnumDes(dataBaseName.ToString()));SqlBulkCopy bulkCopy = new SqlBulkCopy(conn); //⽤其它源的数据有效批量加载sql server表中bulkCopy.DestinationTableName = targetTable; //服务器上⽬标表的名称bulkCopy.BatchSize = sourceDt.Rows.Count; //每⼀批次中的⾏数try{conn.Open();if (sourceDt != null && sourceDt.Rows.Count != 0)bulkCopy.WriteToServer(sourceDt); //将提供的数据源中的所有⾏复制到⽬标表中}catch (Exception ex){throw ex;}finally}}#endregion#endregion#region根据枚举返回数据库连接字符串public static string GetEnumDes(string dataBaseName){string conn = string.Empty;if (dataBaseName == "集团商务"){conn = ConfigurationManager.AppSettings["JiTuanShangWuDB"].ToString();}else if (dataBaseName == "数据平台"){conn = ConfigurationManager.AppSettings["ShuJuPingTai"].ToString();}else if (dataBaseName == "⽜经纪") {conn = ConfigurationManager.AppSettings["NiuJingJi"].ToString();}else if (dataBaseName == "会员"){conn = ConfigurationManager.AppSettings["MobilePlatformDB"].ToString();}else if (dataBaseName == "电商官⽹"){conn = ConfigurationManager.AppSettings["ElectricityDB"].ToString();}else if (dataBaseName == "数据平台DI"){conn = ConfigurationManager.AppSettings["ShuJuPingTaiDI"].ToString();}else{conn = ConfigurationManager.AppSettings["JiTuanShangWuDB"].ToString();}return conn;}#endregion#region MyRegion 执⾏事务逻辑///<summary>///有锁的事务⽅法插⼊数据///</summary>///<param name="tran"></param>///<param name="sql"></param>///<param name="pms"></param>///<returns></returns>public static int ExecuteNonQuery(IDbTransaction tran, string sql, params SqlParameter[] pms){using (SqlCommand cmd = new SqlCommand(sql, (SqlConnection)tran.Connection, (SqlTransaction)tran)) {if (pms != null){cmd.Parameters.AddRange(pms);}return cmd.ExecuteNonQuery();}}#endregion}#region数据库--枚举（DataBaseName）///<summary>///数据库类型枚举：集团商务、⽜经纪///</summary>public enum DataBaseName{集团商务,数据平台,⽜经纪,会员,电商官⽹,数据平台DI}#endregion#region MyRegion 事务sqlpublic class TransactionDal{CreateConnection = Connection;}public void BeginTransaction(){dbconnection = new SqlConnection(CreateConnection);dbconnection.Open();transaction = dbconnection.BeginTransaction();}public void CommitTransaction(){if (null != transaction){mit();}}public void RollbackTransaction(){if (null != transaction){transaction.Rollback();}}public void DisposeTransaction(){if (dbconnection.State == ConnectionState.Open){dbconnection.Close();}if (null != transaction){transaction.Dispose();}}}#endregion}3、调⽤存储过程实例：SqlParameter[] param = new SqlParameter[]{new SqlParameter("@type", 0),new SqlParameter("@BaseID", 0),new SqlParameter("@FenGongSiID",model.tb_diqu)}BaseID = DbHelper.ExcuteProc("Proc_Base_Add", param);View Code4、修改实例：public ActionResult HuiFuKeHu(int baseid){var sql = string.Format(@"UPDATE BaseTable SET ZhuangTai=0 WHERE BaseID={0}", baseid); var re = DbHelper.ExcuteSQL(sql);var restr = "";if (re > 0)restr = "ok";elserestr = "no";return Json(new { y = restr });}View Code5、分页帮助类：using System;using System.Collections.Generic;using System.Data;using System.Data.SqlClient;using System.Linq;using System.Text;using System.Web;namespace SwbAPI{public class PagingHelper///<summary>///获取分页SQL语句，默认row_number为关健字，所有表不允许使⽤该字段名///</summary>///<param name="_pageSize">每页记录数</param>///<param name="_pageIndex">当前页数</param>///<param name="_columnSql">查询列</param>///<param name="_fromSql">from后sql</param>///<param name="_orderField">排序字段，多个则⽤“,”隔开</param>///<returns>分页SQL语句</returns>public static DataSet GetPagingData(int _pageSize, int _pageIndex, string _columnSql, string _fromSql, string _orderField){DataSet ds = null;//检查当前页数if (_pageIndex < 1){_pageIndex = 1;}//分页sql，拼接SQL字符串，加上ROW_NUMBER函数进⾏分页StringBuilder newSafeSql = new StringBuilder();newSafeSql.AppendFormat("SELECT ROW_NUMBER() OVER(ORDER BY {0}) as ROWID,", _orderField);newSafeSql.AppendFormat("{0} from {1}", _columnSql, _fromSql);//拼接成最终的SQL语句StringBuilder sbSql = new StringBuilder();sbSql.Append("SELECT * FROM (");sbSql.Append(newSafeSql.ToString());sbSql.Append(") AS T");sbSql.AppendFormat(" WHERE ROWID between {0} and {1}", ((_pageIndex - 1) * _pageSize) + 1, _pageIndex * _pageSize);// 总条数sqlsbSql.AppendFormat(";select count(1) Total from " + _fromSql);sbSql.ToString();ds = DbHelper.GetDataSet(sbSql.ToString());return ds;}#region根据枚举返回数据库连接字符串///<summary>///获取分页SQL语句，默认row_number为关健字，所有表不允许使⽤该字段名///</summary>///<param name="_pageSize">每页记录数</param>///<param name="_pageIndex">当前页数</param>///<param name="_columnSql">查询列</param>///<param name="_fromSql">from后sql</param>///<param name="_orderField">排序字段，多个则⽤“,”隔开</param>///<returns>分页SQL语句</returns>public static DataSet GetPagingData(int _pageSize, int _pageIndex, string _columnSql, string _fromSql, string _orderField, DataBaseName dataBaseName){DataSet ds = null;//检查当前页数if (_pageIndex < 1){_pageIndex = 1;}//分页sql，拼接SQL字符串，加上ROW_NUMBER函数进⾏分页StringBuilder newSafeSql = new StringBuilder();newSafeSql.AppendFormat("SELECT ROW_NUMBER() OVER(ORDER BY {0}) as ROWID,", _orderField);newSafeSql.AppendFormat("{0} from {1}", _columnSql, _fromSql);//拼接成最终的SQL语句StringBuilder sbSql = new StringBuilder();sbSql.Append("SELECT * FROM (");sbSql.Append(newSafeSql.ToString());sbSql.Append(") AS T");sbSql.AppendFormat(" WHERE ROWID between {0} and {1}", ((_pageIndex - 1) * _pageSize) + 1, _pageIndex * _pageSize);// 总条数sqlsbSql.AppendFormat(";select count(1) Total from " + _fromSql);sbSql.ToString();ds = DbHelper.GetDataSet(sbSql.ToString(),dataBaseName);return ds;}#endregion#region根据枚举返回数据库连接字符串///<summary>///获取分页SQL语句，默认row_number为关健字，所有表不允许使⽤该字段名///</summary>///<param name="_pageSize">每页记录数</param>///<param name="_pageIndex">当前页数</param>///<param name="_columnSql">查询列</param>///<param name="_fromSql">from后sql</param>///<param name="_orderField">排序字段，多个则⽤“,”隔开</param>///<returns>分页SQL语句</returns>public static DataSet GetPagingData(int _pageSize, int _pageIndex, string _columnSql, string _fromSql,SqlParameter[] param, string _orderField, DataBaseName dataBaseName) {DataSet ds = null;//检查当前页数。
