您的当前位置:首页ASP.NET一个数据访问层的封装

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

2022-11-03 来源:乌哈旅游
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

第⼆步、封装数据库访问层,该层实现执⾏存储过程或SQL语句返回 DataTable、SqlDataReader、受影响⾏数:

namespace Ant.DAL{

///

/// 存储过程返回值 ///

public class ProcResultValue {

///

/// @Result 字符串返回值 ///

public string ResultValueStr { get; set; } ///

/// Return 任意类型返回值 ///

public int ReturnValueInt { get; set; } ///

/// 存储过程异常实体信息返回 ///

public string ReturnValue {

get {

if (ReturnValueInt <= 0) return ResultValueStr; else

return \"\"; } }

}

///

/// 数据库基础操作类 ///

public class Database {

// 测试⽤Sql连接字符串

private static string SqlConn_Debug = System.Configuration.ConfigurationManager.ConnectionStrings[\"ConnDB\"].ToString();///

/// 数据库基础操作类 构造函数 /// public Database() { }

///

/// 获得数据库连接数据 ///

/// 数据库连接信息 protected SqlConnection GetConn() {

SqlConnection ConnDB = new SqlConnection(SqlConn_Debug);

ConnDB.StateChange += new StateChangeEventHandler(ConnDB_StateChange); ConnDB.Open(); return ConnDB; }

///

/// 数据库链接状态改变事件 ///

protected void ConnDB_StateChange(object sender, StateChangeEventArgs e) {

if (e.CurrentState == ConnectionState.Closed) {

((SqlConnection)sender).Dispose(); // 释放资源 GC.Collect(); // 释放资源 } }

///

/// 获得对应表序列的新ID ///

/// 表名 /// 表ID列名

/// 查询条件(注意:不添加'where') /// 返回:序列的新ID

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; }

///

/// 通过存储过程,获得数据集 DataReader ///

/// 要查询的表名 /// 要查询的字段

/// 查询条件(注意:不要加 \"where\")

/// 排序规则(注意:不要加 \"order by\",且不能为空) /// 页⼤⼩ 为0时,则不分页 /// 页索引 /// 返回:记录集 SqlDataReader

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); }

///

/// 通过存储过程及⾃定义参数,获得数据集 DataReader ///

/// 存储过程名

/// 存储过程参数集 /// 返回:记录集 SqlDataReader

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); }

///

/// 通过存储过程,获得数据集 DataTable ///

/// 要查询的表名 /// 要查询的字段

/// 查询条件(注意:不要加 \"where\")

/// 排序规则(注意:不要加 \"order by\",且不能为空) /// 页⼤⼩ 为0时,则不分页 /// 页索引 /// 返回:记录集 DataTable

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; }

///

/// 通过指定的存储过程名称,获取数据集 DataTable ///

/// 存储过程名

/// 存储过程参数集 /// 返回:记录集 DataTable

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; }

///

/// 执⾏SQL查询语句,获取数据集 DataReader ///

/// 要执⾏的SQL语句 /// 返回:记录集 DataReader

protected SqlDataReader GetDataReader(string sqlTxt) {

SqlCommand Cmd = new SqlCommand(sqlTxt, GetConn()); Cmd.CommandType = CommandType.Text;

return Cmd.ExecuteReader(CommandBehavior.CloseConnection); }

///

/// 执⾏SQL查询语句,获取数据集 DataTable ///

/// 要执⾏的SQL语句 /// 返回:记录集 DataTable 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; }

///

/// 通过存储过程,获得数据集 总数 ///

/// 要查询的表名 /// 查询条件 /// 返回:记录集数量

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; }

///

/// 执⾏SQL查询语句,并返回数据集长度 ///

/// 要执⾏的SQL语句 /// 返回:数据集长度 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; }

///

/// 执⾏查询语句,并返回第⼀⾏第⼀列数据 ///

/// 要执⾏的查询语句 /// 返回查询结果集的第⼀⾏第⼀列数据 protected object GetOnlyData(string SelectTxt) {

SqlCommand Cmd = new SqlCommand(SelectTxt, GetConn()); object Result = Cmd.ExecuteScalar(); Cmd.Connection.Close(); return Result; }

///

/// 执⾏语句,并返回受影响的⾏数 ///

/// 要执⾏的 增、删、改 语句 /// 返回受影响的⾏数

protected int RunSqlCommand(string CmdTxt) {

SqlCommand Cmd = new SqlCommand(CmdTxt, GetConn()); int ExecuteCount = Cmd.ExecuteNonQuery(); Cmd.Connection.Close(); Cmd.Dispose();

return ExecuteCount; }

///

/// 执⾏存储过程,并返回存储过程执⾏结果(字符串) ///

/// 存储过程名称 /// 存储过程参数集

/// 该存储过程是否有返回值 /// 存储过程返回值

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; }

///

/// 执⾏函数,并返回函数执⾏结果 ///

/// 函数名称 /// 函数参数集 /// 返回值类型 /// 存储过程返回值

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; } }}

第三步、上⾯对数据库访问封装⽅法有⼀个DataParameters传参对象,你没有想错,这个对象是⾃⼰封装的类,调⽤起来更加⽅便,请看下⾯代码

namespace Ant.DAL{

///

/// 数据库[存储过程、函数]参数类 ///

public class DataParameters {

private Hashtable HT = new Hashtable(); // 存储过程参数表 ///

/// 数据库[存储过程、函数]参数类 构造函数 ///

public DataParameters() { }

///

/// 数据库[存储过程、函数] 参数表 ///

public Hashtable Parameters {

get {

return HT; } }

///

/// 添加数据库[存储过程、函数]的参数 ///

/// 参数名称 /// 参数类型 /// 参数值

public void Add(string ParaName, SqlDbType ParaType, object ParaValue) {

HT.Add(ParaName, new object[] { ParaType, ParaValue }); } }}

第四步、调⽤

///

/// (查询调⽤)根据条件查询系统操作对象数据 ///

/// 查询字段

/// 查询条件 注:不要加 Where /// 排序 注:不要加 Order By /// 页⼤⼩ ⼤于等于 0 /// 页码 ⼤于等于 1 /// 返回:SqlDataReader

public SqlDataReader GetData(string sqlField, string sqlWhere, string orderBy, int pageSize, int pageIndex) {

return base.GetDataReader(\"Base_Action\", sqlField, sqlWhere, orderBy, pageSize, pageIndex); }

///

/// (存储过程增删改调⽤)设置单条系统操作对象 ///

/// 操作标识 /// 主键ID

/// 类型ID(系统菜单ID、公共页⾯对象ID) /// 名称:按钮/链接ID /// 描述:按钮/连接名称 /// 返回:操作结果 空/异常信息

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); }

因篇幅问题不能全部显示,请点此查看更多更全内容