ASP.NET 一个数据访问层的封装

CREATE PROCEDURE [dbo].[P_Pagination] ????@tblName varchar(5000), -- 表名 ???@strGetFields varchar(1000) = ‘*‘, -- 需要返回的列 ???@strWhere varchar(1500) = ‘‘, -- 查询条件(注意: 不要加 where) ???@OrderSql varchar(255) = ‘‘, -- 排序语句(注意: 不要加 order by) ???@PageSize int = 0, -- 页尺寸 ???@PageIndex int = 1, -- 页码 ???@doCount bit = 0 -- 返回记录总数,非 0 值则返回ASBEGIN ???declare @strSQL varchar(5000) -- 主语句 ???if @doCount <> 0 ???begin ???????if @strWhere <> ‘‘ ???????????set @strSQL = ‘select count(*) as Total from ‘ + @tblName + ‘ where ‘ + @strWhere ???????else ???????????set @strSQL = ‘select count(*) as Total from ‘ + @tblName + ‘‘ ???end ???--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况 ???else ???begin ???????if @PageSize = 0 --返回所有记录集 ???????begin ???????????if @strWhere <> ‘‘ ???????????????set @strSQL = ‘select ‘ + @strGetFields + ‘ from ‘ + @tblName + ‘ where ‘ + @strWhere + ‘ order by ‘ + @OrderSql ???????????else ???????????????set @strSQL = ‘select ‘ + @strGetFields + ‘ from ‘ + @tblName + ‘ order by ‘ + @OrderSql ???????end ???????else ????????begin ???????????if @PageIndex = 1 ???????????begin ???????????????if @strWhere <> ‘‘ ???????????????????set @strSQL = ‘select top ‘ + str(@PageSize) + ‘ ‘ + @strGetFields + ‘ from ‘ + @tblName + ‘ where ‘ + @strWhere + ‘ order by ‘ + @OrderSql ???????????????else ???????????????????set @strSQL = ‘select top ‘ + str(@PageSize) + ‘ ‘ + @strGetFields + ‘ from ‘ + @tblName + ‘ order by ‘ + @OrderSql ???????????????--如果是第一页就执行以上代码，这样会加快执行速度 ???????????end ???????????else ???????????begin ???????????????--以下代码赋予了@strSQL以真正执行的SQL代码 ???????????????if @strWhere = ‘‘ ???????????????????set @strSQL = ‘select top ‘ + str(@PageSize) + ‘ tblTmp.* from ‘ ???????????????????????+ ‘(select ROW_NUMBER() OVER(order by ‘ + @OrderSql + ‘) AS RowNum,‘ + @strGetFields + ‘ from ‘ + @tblName + ‘) tblTmp where tblTmp.[RowNum] > ‘ ???????????????????????????+ ‘(select max([RowNum]) from ‘ ???????????????????????????????+ ‘(select top ‘ + str((@PageIndex - 1) * @PageSize) + ‘ ROW_NUMBER() OVER(order by ‘ + @OrderSql + ‘) AS RowNum from ‘ + @tblName + ‘) as tblTmp1)‘ ???????????????else ???????????????????set @strSQL = ‘select top ‘ + str(@PageSize) + ‘ tblTmp.* from ‘ ???????????????????????+ ‘(select ROW_NUMBER() OVER(order by ‘ + @OrderSql + ‘) AS RowNum,‘ + @strGetFields + ‘ from ‘ + @tblName + ‘ where ‘ + @strWhere + ‘) tblTmp where tblTmp.[RowNum] > ‘ ???????????????????????????+ ‘(select max([RowNum]) from ‘ ???????????????????????????????+ ‘(select top ‘ + str((@PageIndex - 1) * @PageSize) + ‘ ROW_NUMBER() OVER(order by ‘ + @OrderSql + ‘) AS RowNum from ‘ + @tblName + ‘ where ‘ + @strWhere + ‘) as tblTmp1)‘ ???????????end ???????end ???end ???exec (@strSQL);END

namespace Ant.DAL{ ???/// <summary> ???/// 存储过程返回值 ???/// </summary> ???public class ProcResultValue ???{ ???????/// <summary> ???????/// @Result 字符串返回值 ???????/// </summary> ???????public string ResultValueStr { get; set; } ???????/// <summary> ???????/// Return 任意类型返回值 ???????/// </summary> ???????public int ReturnValueInt { get; set; } ???????/// <summary> ???????/// 存储过程异常实体信息返回 ???????/// </summary> ???????public string ReturnValue ???????{ ???????????get ???????????{ ???????????????if (ReturnValueInt <= 0) ???????????????????return ResultValueStr; ???????????????else ???????????????????return ""; ???????????} ???????} ???} ???/// <summary> ???/// 数据库基础操作类 ???/// </summary> ???public class Database ???{ ???????// 测试用Sql连接字符串 ???????private static string SqlConn_Debug = System.Configuration.ConfigurationManager.ConnectionStrings["ConnDB"].ToString();/// <summary> ???????/// 数据库基础操作类 构造函数 ???????/// </summary> ???????public Database() ???????{ ???????} ???????/// <summary> ???????/// 获得数据库连接数据 ???????/// </summary> ???????/// <returns>数据库连接信息</returns> ???????protected SqlConnection GetConn() ???????{ ???????????SqlConnection ConnDB = new SqlConnection(SqlConn_Debug); ???????????ConnDB.StateChange += new StateChangeEventHandler(ConnDB_StateChange); ???????????ConnDB.Open(); ???????????return ConnDB; ???????} ???????/// <summary> ???????/// 数据库链接状态改变事件 ???????/// </summary> ???????protected void ConnDB_StateChange(object sender, StateChangeEventArgs e) ???????{ ???????????if (e.CurrentState == ConnectionState.Closed) ???????????{ ???????????????((SqlConnection)sender).Dispose(); // 释放资源 ???????????????GC.Collect(); // 释放资源 ???????????} ???????} ???????/// <summary> ???????/// 获得对应表序列的新ID ???????/// </summary> ???????/// <param name="TableName">表名</param> ???????/// <param name="TableCol">表ID列名</param> ???????/// <param name="TableWhere">查询条件(注意：不添加‘where‘)</param> ???????/// <returns>返回：序列的新ID</returns> ???????protected int GetSequence(string TableName, string TableCol, string tableWhere = "") ???????{ ???????????DataParameters DP = new DataParameters(); ???????????DP.Add("@TableName", SqlDbType.VarChar, TableName); ???????????DP.Add("@TableCol", SqlDbType.VarChar, TableCol); ???????????DP.Add("@tableWhere", SqlDbType.VarChar, tableWhere); ???????????return ExeProc("P_Sequence", DP, false).ReturnValueInt; ???????} ???????/// <summary> ???????/// 通过存储过程，获得数据集 DataReader ???????/// </summary> ???????/// <param name="tblName">要查询的表名</param> ???????/// <param name="strGetFields">要查询的字段</param> ???????/// <param name="strWhere">查询条件（注意：不要加 "where"）</param> ???????/// <param name="OrderSql">排序规则（注意：不要加 "order by"，且不能为空）</param> ???????/// <param name="PageSize">页大小 为0时，则不分页</param> ???????/// <param name="PageIndex">页索引</param> ???????/// <returns>返回：记录集 SqlDataReader</returns> ???????protected SqlDataReader GetDataReader(string tblName, string strGetFields, string strWhere, string OrderSql, int PageSize, int PageIndex) ???????{ ???????????SqlCommand Cmd = new SqlCommand("P_Pagination", GetConn()); ???????????Cmd.CommandType = CommandType.StoredProcedure; ???????????Cmd.Parameters.Add("@tblName", SqlDbType.VarChar).Value = tblName; ???????????Cmd.Parameters.Add("@strGetFields", SqlDbType.VarChar).Value = strGetFields; ???????????Cmd.Parameters.Add("@strWhere", SqlDbType.VarChar).Value = strWhere; ???????????Cmd.Parameters.Add("@OrderSql", SqlDbType.VarChar).Value = OrderSql; ???????????Cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize; ???????????Cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex; ???????????Cmd.Parameters.Add("@doCount", SqlDbType.Bit).Value = false; ???????????return Cmd.ExecuteReader(CommandBehavior.CloseConnection); ???????} ???????/// <summary> ???????/// 通过存储过程及自定义参数，获得数据集 DataReader ???????/// </summary> ???????/// <param name="ProcName">存储过程名</param> ???????/// <param name="DataParas">存储过程参数集</param> ???????/// <returns>返回：记录集 SqlDataReader</returns> ???????protected SqlDataReader GetDataReader(string ProcName, DataParameters DataParas) ???????{ ???????????SqlCommand Cmd = new SqlCommand(ProcName, GetConn()); ???????????Cmd.CommandType = CommandType.StoredProcedure; ???????????// 遍历 存储过程参数集 ???????????foreach (System.Collections.DictionaryEntry obj in DataParas.Parameters) ???????????{ ???????????????Cmd.Parameters.Add(obj.Key.ToString(), (SqlDbType)((object[])obj.Value)[0]).Value = ((object[])obj.Value)[1]; ???????????} ???????????return Cmd.ExecuteReader(CommandBehavior.CloseConnection); ???????} ???????/// <summary> ???????/// 通过存储过程，获得数据集 DataTable ???????/// </summary> ???????/// <param name="tblName">要查询的表名</param> ???????/// <param name="strGetFields">要查询的字段</param> ???????/// <param name="strWhere">查询条件（注意：不要加 "where"）</param> ???????/// <param name="OrderSql">排序规则（注意：不要加 "order by"，且不能为空）</param> ???????/// <param name="PageSize">页大小 为0时，则不分页</param> ???????/// <param name="PageIndex">页索引</param> ???????/// <returns>返回：记录集 DataTable</returns> ???????protected DataTable GetDataTable(string tblName, string strGetFields, string strWhere, string OrderSql, int PageSize, int PageIndex) ???????{ ???????????SqlCommand Cmd = new SqlCommand("P_Pagination", GetConn()); ???????????Cmd.CommandType = CommandType.StoredProcedure; ???????????Cmd.Parameters.Add("@tblName", SqlDbType.VarChar).Value = tblName; ???????????Cmd.Parameters.Add("@strGetFields", SqlDbType.VarChar).Value = strGetFields; ???????????Cmd.Parameters.Add("@strWhere", SqlDbType.VarChar).Value = strWhere; ???????????Cmd.Parameters.Add("@OrderSql", SqlDbType.VarChar).Value = OrderSql; ???????????Cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize; ???????????Cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex; ???????????Cmd.Parameters.Add("@doCount", SqlDbType.Bit).Value = false; ???????????SqlDataAdapter DA = new SqlDataAdapter(Cmd); ???????????DataTable DT = new DataTable(); ???????????DA.Fill(DT); ???????????Cmd.Connection.Close(); ???????????return DT; ???????} ???????/// <summary> ???????/// 通过指定的存储过程名称，获取数据集 DataTable ???????/// </summary> ???????/// <param name="ProcName">存储过程名</param> ???????/// <param name="DataParas">存储过程参数集</param> ???????/// <returns>返回：记录集 DataTable</returns> ???????protected DataTable GetDataTable(string ProcName, DataParameters DataParas = null) ???????{ ???????????SqlCommand Cmd = new SqlCommand(ProcName, GetConn()); ???????????Cmd.CommandType = CommandType.StoredProcedure; ???????????// 遍历 存储过程参数集 ???????????if(DataParas != null) ???????????{ ???????????????foreach (System.Collections.DictionaryEntry obj in DataParas.Parameters) ???????????????{ ???????????????????Cmd.Parameters.Add(obj.Key.ToString(), (SqlDbType)((object[])obj.Value)[0]).Value = ((object[])obj.Value)[1]; ???????????????} ???????????} ???????????SqlDataAdapter DA = new SqlDataAdapter(Cmd); ???????????DataTable DT = new DataTable(); ???????????DA.Fill(DT); ???????????Cmd.Connection.Close(); ???????????return DT; ???????} ???????/// <summary> ???????/// 执行SQL查询语句，获取数据集 DataReader ???????/// </summary> ???????/// <param name="sqlTxt">要执行的SQL语句</param> ???????/// <returns>返回：记录集 DataReader</returns> ???????protected SqlDataReader GetDataReader(string sqlTxt) ???????{ ???????????SqlCommand Cmd = new SqlCommand(sqlTxt, GetConn()); ???????????Cmd.CommandType = CommandType.Text; ???????????return Cmd.ExecuteReader(CommandBehavior.CloseConnection); ???????} ???????/// <summary> ???????/// 执行SQL查询语句，获取数据集 DataTable ???????/// </summary> ???????/// <param name="sqlTxt">要执行的SQL语句</param> ???????/// <returns>返回：记录集 DataTable</returns> ???????protected DataTable GetDataTable(string sqlTxt) ???????{ ???????????SqlCommand Cmd = new SqlCommand(sqlTxt, GetConn()); ???????????Cmd.CommandType = CommandType.Text; ???????????SqlDataAdapter DA = new SqlDataAdapter(Cmd); ???????????DataTable DT = new DataTable(); ???????????DA.Fill(DT); ???????????Cmd.Connection.Close(); ???????????return DT; ???????} ???????/// <summary> ???????/// 通过存储过程，获得数据集 总数 ???????/// </summary> ???????/// <param name="tblName">要查询的表名</param> ???????/// <param name="strWhere">查询条件</param> ???????/// <returns>返回：记录集数量</returns> ???????protected int GetDataCount(string tblName, string strWhere = "") ???????{ ???????????SqlCommand Cmd = new SqlCommand("P_Pagination", GetConn()); ???????????Cmd.CommandType = CommandType.StoredProcedure; ???????????Cmd.Parameters.Add("@tblName", SqlDbType.VarChar).Value = tblName; ???????????Cmd.Parameters.Add("@strGetFields", SqlDbType.VarChar).Value = "*"; ???????????Cmd.Parameters.Add("@strWhere", SqlDbType.VarChar).Value = strWhere; ???????????Cmd.Parameters.Add("@OrderSql", SqlDbType.VarChar).Value = ""; ???????????Cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = 0; ???????????Cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = 1; ???????????Cmd.Parameters.Add("@doCount", SqlDbType.Bit).Value = true; ???????????int Result = (int)Cmd.ExecuteScalar(); ???????????Cmd.Connection.Close(); ???????????return Result; ???????} ???????/// <summary> ???????/// 执行SQL查询语句，并返回数据集长度 ???????/// </summary> ???????/// <param name="sqlTxt">要执行的SQL语句</param> ???????/// <returns>返回:数据集长度</returns> ???????protected int GetDataCount(string sqlTxt) ???????{ ???????????SqlCommand Cmd = new SqlCommand(sqlTxt, GetConn()); ???????????Cmd.CommandType = CommandType.Text; ???????????SqlDataAdapter DA = new SqlDataAdapter(Cmd); ???????????DataTable DT = new DataTable(); ???????????DA.Fill(DT); ???????????Cmd.Connection.Close(); ???????????return DT.Rows.Count; ???????} ???????/// <summary> ???????/// 执行查询语句，并返回第一行第一列数据 ???????/// </summary> ???????/// <param name="SelectTxt">要执行的查询语句</param> ???????/// <returns>返回查询结果集的第一行第一列数据</returns> ???????protected object GetOnlyData(string SelectTxt) ???????{ ???????????SqlCommand Cmd = new SqlCommand(SelectTxt, GetConn()); ???????????object Result = Cmd.ExecuteScalar(); ???????????Cmd.Connection.Close(); ???????????return Result; ???????} ???????/// <summary> ???????/// 执行语句，并返回受影响的行数 ???????/// </summary> ???????/// <param name="CmdTxt">要执行的 增、删、改 语句</param> ???????/// <returns>返回受影响的行数</returns> ???????protected int RunSqlCommand(string CmdTxt) ???????{ ???????????SqlCommand Cmd = new SqlCommand(CmdTxt, GetConn()); ???????????int ExecuteCount = Cmd.ExecuteNonQuery(); ???????????Cmd.Connection.Close(); ???????????Cmd.Dispose(); ???????????return ExecuteCount; ???????} ???????/// <summary> ???????/// 执行存储过程，并返回存储过程执行结果（字符串） ???????/// </summary> ???????/// <param name="ProcName">存储过程名称</param> ???????/// <param name="DataParas">存储过程参数集</param> ???????/// <param name="HasResult">该存储过程是否有返回值</param> ???????/// <returns>存储过程返回值</returns> ???????protected ProcResultValue ExeProc(string ProcName, DataParameters DataParas, bool HasResult) ???????{ ???????????// 此处预留异常处理Try catch， 由Application获取并跳转异常页面。 ???????????// 返回值 ???????????ProcResultValue Result = new ProcResultValue(); ???????????// 创建 Command ???????????SqlCommand Cmd = new SqlCommand(ProcName, GetConn()); ???????????Cmd.CommandType = CommandType.StoredProcedure; ???????????????????????// 遍历 存储过程参数集 ???????????foreach (System.Collections.DictionaryEntry obj in DataParas.Parameters) ???????????{ ???????????????Cmd.Parameters.Add(obj.Key.ToString(), (SqlDbType)((object[])obj.Value)[0]).Value = ((object[])obj.Value)[1]; ???????????} ???????????// 创建返回参数 ???????????if (HasResult) ???????????{ ???????????????Cmd.Parameters.Add("@Result", SqlDbType.NVarChar, -1); ???????????????Cmd.Parameters["@Result"].Direction = ParameterDirection.Output; ???????????} ???????????//存储过程默认返回值 ?存储过程：Return ???????????Cmd.Parameters.Add("@Return", SqlDbType.Int); ???????????Cmd.Parameters["@Return"].Direction = ParameterDirection.ReturnValue; ???????????// 执行存储过程 ???????????Cmd.ExecuteNonQuery(); ???????????// 获得返回值 ???????????if (HasResult) ???????????????Result.ResultValueStr = Cmd.Parameters["@Result"].Value.ToString(); ???????????Result.ReturnValueInt = (Cmd.Parameters["@Return"].Value is int ? (int)Cmd.Parameters["@Return"].Value : -1); ???????????// 关闭数据库链接 ???????????Cmd.Connection.Close(); ???????????// 在这里执行一些存储过程catch异常的操作 ???????????if(Result.ReturnValueInt == -1) ???????????{ ???????????} ???????????// 返回执行结果 ???????????return Result; ???????} ???????/// <summary> ???????/// 执行函数，并返回函数执行结果 ???????/// </summary> ???????/// <param name="FuncName">函数名称</param> ???????/// <param name="DataParas">函数参数集</param> ???????/// <param name="ResultType">返回值类型</param> ???????/// <returns>存储过程返回值</returns> ???????protected object ExeFunc(string FuncName, DataParameters DataParas, SqlDbType ResultType) ???????{ ???????????// 返回值 ???????????object Result = null; ???????????// 创建 Command ???????????SqlCommand Cmd = new SqlCommand(FuncName, GetConn()); ???????????Cmd.CommandType = CommandType.StoredProcedure; ???????????// 遍历 存储过程参数集 ???????????foreach (System.Collections.DictionaryEntry obj in DataParas.Parameters) ???????????{ ???????????????Cmd.Parameters.Add(obj.Key.ToString(), (SqlDbType)((object[])obj.Value)[0]).Value = ((object[])obj.Value)[1]; ???????????} ???????????// 创建返回参数 ???????????Cmd.Parameters.Add("@Return", ResultType, -1); ???????????Cmd.Parameters["@Return"].Direction = ParameterDirection.ReturnValue; ???????????// 执行存储过程 ???????????Cmd.ExecuteScalar(); ???????????// 获得返回值 ???????????Result = Cmd.Parameters["@Return"].Value; ???????????// 关闭数据库链接 ???????????Cmd.Connection.Close(); ???????????// 返回执行结果 ???????????return Result; ???????} ???}}

namespace Ant.DAL{ ???/// <summary> ???/// 数据库[存储过程、函数]参数类 ???/// </summary> ???public class DataParameters ???{ ???????private Hashtable HT = new Hashtable(); // 存储过程参数表 ???????/// <summary> ???????/// 数据库[存储过程、函数]参数类 构造函数 ???????/// </summary> ???????public DataParameters() ???????{ ???????} ???????/// <summary> ???????/// 数据库[存储过程、函数] 参数表 ???????/// </summary> ???????public Hashtable Parameters ???????{ ???????????get ???????????{ ???????????????return HT; ???????????} ???????} ???????/// <summary> ???????/// 添加数据库[存储过程、函数]的参数 ???????/// </summary> ???????/// <param name="ParaName">参数名称</param> ???????/// <param name="ParaType">参数类型</param> ???????/// <param name="ParaValue">参数值</param> ???????public void Add(string ParaName, SqlDbType ParaType, object ParaValue) ???????{ ???????????HT.Add(ParaName, new object[] { ParaType, ParaValue }); ???????} ???}}

 ???????/// <summary> ???????/// （查询调用）根据条件查询系统操作对象数据 ???????/// </summary> ???????/// <param name="sqlField">查询字段</param> ???????/// <param name="sqlWhere">查询条件 注：不要加 Where</param> ???????/// <param name="orderBy">排序 注：不要加 Order By</param> ???????/// <param name="pageSize">页大小 大于等于 0</param> ???????/// <param name="pageIndex">页码 大于等于 1</param> ???????/// <returns>返回：SqlDataReader</returns> ???????public SqlDataReader GetData(string sqlField, string sqlWhere, string orderBy, int pageSize, int pageIndex) ???????{ ???????????return base.GetDataReader("Base_Action", sqlField, sqlWhere, orderBy, pageSize, pageIndex); ???????} ???????/// <summary> ???????/// （存储过程增删改调用）设置单条系统操作对象 ???????/// </summary> ???????/// <param name="editSign">操作标识</param> ???????/// <param name="ID">主键ID</param> ???????/// <param name="Menu">类型ID（系统菜单ID、公共页面对象ID）</param> ???????/// <param name="Name">名称：按钮/链接ID</param> ???????/// <param name="Text">描述：按钮/连接名称</param> ???????/// <returns>返回：操作结果　空／异常信息</returns> ???????public ProcResultValue SetActionInfo(SetActionInfo_EditSign editSign, long ID, long Menu, string Name, string Text) ???????{ ???????????DataParameters DP = new DataParameters(); ???????????DP.Add("@EditSign", System.Data.SqlDbType.Int, (int)editSign); ???????????DP.Add("@ID", System.Data.SqlDbType.BigInt, ID); ???????????DP.Add("@Menu", System.Data.SqlDbType.BigInt, Menu); ???????????DP.Add("@Name", System.Data.SqlDbType.VarChar, Name); ???????????DP.Add("@Text", System.Data.SqlDbType.NVarChar, Text); ???????????return base.ExeProc("P_Base_Action", DP, true); ???????} ???????