类别：程序开发
日期：2021-01-29 浏览：1948 评论：0
1、IBaseDao
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace SysFrameDbUntity
{
public interface IBaseDao<T> where T : class //限制class
{
#region 查询普通实现方案(基于Lambda表达式的Where查询)
/// <summary>
/// 获取所有Entity
/// </summary>
/// <param name="exp">Lambda条件的where</param>
/// <returns></returns>
IEnumerable<T> GetEntities(Func<T, bool> exp);
/// <summary>
/// 根据条件查找
/// </summary>
/// <param name="exp">lambda查询条件where</param>
/// <returns></returns>
T GetEntity(Func<T, bool> exp);
#endregion 查询普通实现方案(基于Lambda表达式的Where查询)
#region 查询Sql语句外接接口的查询实现
/// <summary>
/// 获取所有Entity(立即执行请使用ToList()
/// </summary>
/// <param name="CommandText">Sql语句</param>
/// <param name="objParams">可变参数</param>
/// <returns></returns>
IEnumerable<T> GetEntities(string CommandText);
#endregion 查询Sql语句外接接口的查询实现
/// <summary>
/// 插入Entity
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
bool Insert(T entity);
/// <summary>
/// 更新Entity
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
bool Update(T entity);
/// <summary>
/// 删除Entity
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
bool Delete(T entity);
}
}2、BaseEFDao
using SysFrameModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Core.Objects;
using System.Data.Entity.Infrastructure;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
namespace SysFrameDbUntity
{
/// <summary>
/// 公共接口
/// </summary>
public class BaseEFDao<T> : IBaseDao<T> where T : class,new() //限制T为class
{
private DbContext GetDbContext()
{
return new SysFrameEntities();
}
/// <summary>
/// 获取所有Entity
/// </summary>
/// <param name="exp">Lambda条件的where</param>
/// <returns></returns>
public virtual IEnumerable<T> GetEntities(Func<T, bool> exp)
{
using (DbContext Entities = GetDbContext())
{
//AsNoTracking不记录数据变化状况
ObjectContext context = ((IObjectContextAdapter)Entities).ObjectContext;
return context.CreateObjectSet<T>().AsNoTracking().Where(exp).ToList();
}
}
/// <summary>
/// 获取所有Entity(立即执行请使用ToList()
/// </summary>
/// <param name="CommandText">Sql语句</param>
/// <param name="objParams">可变参数</param>
/// <returns></returns>
public virtual IEnumerable<T> GetEntities()
{
using (DbContext Entities = GetDbContext())
{
ObjectContext context = ((IObjectContextAdapter)Entities).ObjectContext;
return context.ExecuteStoreQuery<T>("select * from " + typeof(T).Name).ToList();
}
}
/// <summary>
/// 根据条件查找
/// </summary>
/// <param name="exp">lambda查询条件where</param>
/// <returns></returns>
public virtual T GetEntity(Func<T, bool> exp)
{
using (DbContext Entities = GetDbContext())
{
ObjectContext context = ((IObjectContextAdapter)Entities).ObjectContext;
return context.CreateObjectSet<T>().Where(exp).SingleOrDefault();
}
}
/// <summary>
/// 获取所有Entity(立即执行请使用ToList()
/// </summary>
/// <param name="CommandText">Sql语句</param>
/// <param name="objParams">可变参数</param>
/// <returns></returns>
public virtual IEnumerable<T> GetEntities(string CommandText)
{
using (DbContext Entities = GetDbContext())
{
ObjectContext context = ((IObjectContextAdapter)Entities).ObjectContext;
return context.ExecuteStoreQuery<T>("select * from " + typeof(T).Name + " where " + CommandText).ToList();
}
}
/// <summary>
/// 插入Entity
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public virtual bool Insert(T entity)
{
using (DbContext Entities = GetDbContext())
{
ObjectContext context = ((IObjectContextAdapter)Entities).ObjectContext;
var obj = context.CreateObjectSet<T>();
obj.AddObject(entity);
return Entities.SaveChanges() > 0;
}
}
/// <summary>
/// 同时插入多个实体。
/// </summary>
/// <param name="entities"></param>
/// <returns></returns>
public virtual bool Insert(IEnumerable<T> entities)
{
using (DbContext Entities = GetDbContext())
{
foreach (var entity in entities)
{
ObjectContext context = ((IObjectContextAdapter)Entities).ObjectContext;
var obj = context.CreateObjectSet<T>();
obj.AddObject(entity);
}
return Entities.SaveChanges() > 0;
}
}
/// <summary>
/// 更新Entity(注意这里使用的傻瓜式更新,可能性能略低)
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public virtual bool Update(T entity)
{
using (DbContext Entities = GetDbContext())
{
ObjectContext context = ((IObjectContextAdapter)Entities).ObjectContext;
var obj = context.CreateObjectSet<T>();
obj.Attach(entity);
context.ObjectStateManager.ChangeObjectState(entity, System.Data.Entity.EntityState.Modified);
return context.SaveChanges() > 0;
}
}
/// <summary>
/// 删除Entity
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public virtual bool Delete(T entity)
{
using (DbContext Entities = GetDbContext())
{
ObjectContext context = ((IObjectContextAdapter)Entities).ObjectContext;
var obj = context.CreateObjectSet<T>();
if (entity != null)
{
obj.Attach(entity);
context.ObjectStateManager.ChangeObjectState(entity, System.Data.Entity.EntityState.Deleted);
obj.DeleteObject(entity);
return context.SaveChanges() > 0;
}
return false;
}
}
/// <summary>
/// 批量删除Entity
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public virtual bool Delete(Func<T, bool> exp)
{
using (DbContext Entities = GetDbContext())
{
ObjectContext context = ((IObjectContextAdapter)Entities).ObjectContext;
var q = context.CreateObjectSet<T>().Where(exp);
foreach (var item in q)
{
context.DeleteObject(item);
}
return context.SaveChanges() >= 0;
}
}
/// <summary>
/// 根据条件查找
/// </summary>
/// <param name="CommandText">Sql语句</param>
/// <param name="objParams">可变参数</param>
/// <returns></returns>
public virtual DataTable GetDTByCommand(string CommandText)
{
using (DbContext Entities = GetDbContext())
{
string connectionString = Entities.Database.Connection.ConnectionString;
return SqlQueryForDataTatable(connectionString, CommandText);
}
}
public virtual DataSet GetDSByCommand(string CommandText)
{
using (DbContext Entities = GetDbContext())
{
string connectionString = Entities.Database.Connection.ConnectionString;
return SqlQueryForDataSet(connectionString, CommandText);
}
}
/// <summary>
/// EF SQL 语句返回 dataTable
/// </summary>
/// <param name="db"></param>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public DataTable SqlQueryForDataTatable(string strCon,
string sql)
{
SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = strCon;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable table = new DataTable();
adapter.Fill(table);
conn.Close();
return table;
}
public DataSet SqlQueryForDataSet(string strCon, string sql)
{
SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = strCon;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
conn.Close();
return ds;
}
//执行SQL语句
public void ExsSql(string sql)
{
using (DbContext Entities = GetDbContext())
{
string connectionString = Entities.Database.Connection.ConnectionString;
SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = connectionString;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
conn.Close();
}
}
public int _ExsSql(string sql)
{
int ret = 0;
using (DbContext Entities = GetDbContext())
{
string connectionString = Entities.Database.Connection.ConnectionString;
SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = connectionString;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlTransaction tran = conn.BeginTransaction();
try
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Transaction = tran;
ret = cmd.ExecuteNonQuery();
tran.Commit();
conn.Close();
}
catch
{
tran.Rollback();
ret = 0;
}
}
return ret;
}
public object ExsSclarSql(string sql)
{
using (DbContext Entities = GetDbContext())
{
string connectionString = Entities.Database.Connection.ConnectionString;
SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = connectionString;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand(sql, conn);
var result = cmd.ExecuteScalar();
conn.Close();
return result;
}
}
/// <summary>
/// 数据分页
/// </summary>
/// <param name="viewName">表名</param>
/// <param name="fieldName">字段</param>
/// <param name="pageSize">默认20</param>
/// <param name="pageNo">页数</param>
/// <param name="orderString">排序</param>
/// <param name="whereString">可选</param>
/// <param name="recordTotal">总数</param>
/// <returns></returns>
public DataTable GetDataPager(string viewName, string fieldName, int pageSize, int pageNo, string orderString, string whereString, ref int recordTotal)
{
using (DbContext Entities = GetDbContext())
{
DataSet ds = new DataSet();
string connectionString = Entities.Database.Connection.ConnectionString;
SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = connectionString;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "usp_DataPager";
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] paras = new SqlParameter[7];
paras[0] = new SqlParameter("viewName", viewName);
paras[1] = new SqlParameter("fieldName", fieldName);
paras[2] = new SqlParameter("pageSize", pageSize);
paras[3] = new SqlParameter("pageNo", pageNo);
paras[4] = new SqlParameter("orderString", orderString);
if (whereString.Trim() == "")
{
whereString = " 1=1 ";
}
paras[5] = new SqlParameter("whereString", whereString);
paras[5].Size = Int32.MaxValue;
paras[6] = new SqlParameter("recordTotal", recordTotal);
paras[6].Direction = ParameterDirection.Output;
cmd.Parameters.AddRange(paras);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds);
recordTotal = Int32.Parse(paras[6].Value == null ? "0" : paras[6].Value.ToString());
return ds.Tables[0];
}
catch (Exception ex)
{
throw ex;
}
finally { conn.Close(); }
}
}
/// <summary>
/// 执行存储过程返回集合数据
/// </summary>
/// <param name="ProName">存储过程名称</param>
/// <param name="sqlPara">存储过程参数</param>
/// <returns></returns>
public DataTable GetProData(string ProName, SqlParameter [] sqlPara)
{
using (DbContext Entities = GetDbContext())
{
DataSet ds = new DataSet();
string connectionString = Entities.Database.Connection.ConnectionString;
SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = connectionString;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = ProName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(sqlPara);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds);
return ds.Tables[0];
}
catch (Exception ex)
{
throw ex;
}
finally { conn.Close(); }
}
}
}
}3、调用方式
//外部创建常用函数
public class JH_Auth_FormBaseB : BaseEFDao<JH_Auth_FormBase>
{
//内部创建函数
}
本文标题：C# 通过EF创建类似DAL层？
本文链接：https://vtzw.com/post/567.html
版权声明：本文不使用任何协议授权，您可以任何形式自由转载或使用。
您阅读本篇文章共花了： 
发表评论 / 取消回复