需要的dll(
EntityFramework.6.0.0
Oracle.ManagedDataAccess.12.1.2400
System.Configuration.dll
EmitMapper.1.0.0
)
调用
//连接数据库 static private void DataBaseConnect() { string connection = string.Format("User Id={0};Password={1};Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST={2})(PORT={3}))(CONNECT_DATA=(SERVICE_NAME={4})))", "zhzepp", "citms", "192.168.10.113", "1521", "ORCL"); RepositoryManager.AddConnection("Program", connection); }
例如查询
static void Main(string[] args) { DataBaseConnect(); using (IRepository<PUNISH_ILLEGALVEHICLE_REALModel> repository = RepositoryManager.CreateRepository<PUNISH_ILLEGALVEHICLE_REALModel>()) { var list = repository.GetModels().ToList(); } }
测试实体
using DataBaseTest.DataBase; using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; using System.Linq; using System.Text; using System.Threading.Tasks; namespace DataBaseTest { [Table("PUNISH_ILLEGALVEHICLE_REAL")] public class PUNISH_ILLEGALVEHICLE_REALModel : BaseEntity { /// <summary> /// 选项ID /// </summary> [Column("PROCESSID"), MaxLength(50), Key] public string processid { get; set; } [Column("SPOTTINGNAME")] public string Spottingname { get; set; } [Column("PLATENO")] public string plateno { get; set; } } }
数据库帮助类
BaseEntity
using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; using System.Linq; using System.Text; using System.Threading.Tasks; namespace DataBaseTest.DataBase { public abstract class BaseEntity { /// <summary> /// 新增调用 /// </summary> public virtual void Create() { } /// <summary> /// 新增调用 /// </summary> public virtual void CreateApp() { } /// <summary> /// 编辑调用 /// </summary> /// <param name="keyValue">主键值</param> public virtual void Modify(string keyValue) { } /// <summary> /// 删除调用 /// </summary> /// <param name="keyValue">主键值</param> public virtual void Remove(string keyValue) { } /// <summary> /// 编辑调用 /// </summary> /// <param name="keyValue">主键值</param> public virtual void Modify(int keyValue) { } /// <summary> /// 删除调用 /// </summary> /// <param name="keyValue">主键值</param> public virtual void Remove(int keyValue) { } } }
EFRepository
using Oracle.ManagedDataAccess.Client; using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.Common; using System.Data.Entity; using System.Data.Entity.Infrastructure; using System.Data.SqlClient; using System.Linq; using System.Linq.Expressions; using System.Text; using System.Threading.Tasks; namespace DataBaseTest.DataBase { [DbConfigurationType(typeof(OracleConfiguration))] public class EFRepository<TEntity> : IRepository<TEntity> where TEntity : BaseEntity { #region 属性 /// <summary> /// 数据库上下文 /// </summary> private ProgramContext<TEntity> Context { get; set; } #endregion #region 构造函数 /// <summary> /// 构造函数 /// </summary> public EFRepository() { Context = new ProgramContext<TEntity>(); } public EFRepository(string connectionString) { Context = new ProgramContext<TEntity>(connectionString); } #endregion #region 公共方法 /// <summary> /// 删除对象 /// </summary> /// <param name="item"></param> public bool Delete(string keyValue) { if (string.IsNullOrEmpty(keyValue)) return false; var entity = Find(keyValue); if (entity == null) return false; Context.Models.Remove(Find(keyValue)); return false; } /// <summary> /// 按ID获取对象 /// </summary> /// <param name="id"></param> /// <returns></returns> public TEntity Find(object id) { return Context.Models.Find(id); } /// <summary> /// 获取所有对象 /// </summary> /// <returns></returns> public IQueryable<TEntity> GetModels() { return Context.Models; } /// <summary> /// 根据条件进行查询 /// </summary> /// <typeparam name="Tkey"></typeparam> /// <param name="whereLambda"></param> /// <param name="orderbyLambda"></param> /// <param name="isAsc"></param> /// <returns></returns> public IQueryable<TEntity> FindList<Tkey>(Expression<Func<TEntity, bool>> whereLambda, Func<TEntity, Tkey> orderbyLambda, bool isAsc) { if (isAsc) { var temp = Context.Set<TEntity>().Where(whereLambda) .OrderBy<TEntity, Tkey>(orderbyLambda); return temp.AsQueryable(); } else { var temp = Context.Set<TEntity>().Where(whereLambda) .OrderByDescending<TEntity, Tkey>(orderbyLambda); return temp.AsQueryable(); } } /// <summary> /// 分页查询 + 排序 /// </summary> /// <typeparam name="Tkey"></typeparam> /// <param name="pagination"></param> /// <param name="orderbyLambda"></param> /// <returns></returns> public IQueryable<TEntity> FindList<Tkey>(Pagination pagination, Func<TEntity, Tkey> orderbyLambda,out int records,out int total) { records = pagination.records;//总记录数 total = pagination.total;//总页数 var data = FindList<Tkey>(pagination.pageIndex, pagination.pageSize, out records, out total, orderbyLambda, pagination.isAsc); return data; } /// <summary> /// 分页查询 + 条件查询 + 排序 /// </summary> /// <typeparam name="Tkey"></typeparam> /// <param name="pagination"></param> /// <param name="orderbyLambda"></param> /// <returns></returns> public IQueryable<TEntity> FindList<Tkey>(Pagination pagination, Expression<Func<TEntity, bool>> whereLambda, Func<TEntity, Tkey> orderbyLambda,out int records, out int total) { records = pagination.records;//总记录数 total = pagination.total;//总页数 var data = FindList<Tkey>(pagination.pageIndex, pagination.pageSize, out records, out total, whereLambda, orderbyLambda, pagination.isAsc); return data; } /// <summary> /// 更新对象 /// </summary> /// <param name="item"></param> public bool Update(string keyValue, TEntity item) { if (item == null || string.IsNullOrEmpty(keyValue)) return false; var entityToUpdate = Context.Models.Find(keyValue); if (entityToUpdate == null) return false; EmitMapper.ObjectMapperManager.DefaultInstance.GetMapper<TEntity, TEntity>() .Map(item, entityToUpdate); return true; } /// <summary> /// 执行sql /// </summary> /// <param name="sql"></param> /// <param name="parameters"></param> /// <returns></returns> public int ExecuteSql(string sql, params object[] parameters) { return Context.Database.ExecuteSqlCommand(sql, parameters); } public int QueryCountSql(string sql, params object[] parameters) { try { return Context.Database.SqlQuery<int>(sql, parameters).FirstOrDefault(); } catch (Exception ex) { throw ex; } } public DataTable FindDataTable(string sql) { OracleConnection conn = new OracleConnection(); conn.ConnectionString = Context.Database.Connection.ConnectionString; if (conn.State != ConnectionState.Open) { conn.Open(); } OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = sql; OracleDataAdapter adapter = new OracleDataAdapter(cmd); DataTable table = new DataTable(); adapter.Fill(table); conn.Close();//连接需要关闭 conn.Dispose(); return table; } public DataTable SqlQueryForDataTatable(string sql, DbParameter[] parameters) { OracleConnection conn = new OracleConnection(); conn.ConnectionString = Context.Database.Connection.ConnectionString; if (conn.State != ConnectionState.Open) { conn.Open(); } OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = sql; if (parameters != null && parameters.Length > 0) { foreach (var item in parameters) { cmd.Parameters.Add(item); } } OracleDataAdapter adapter = new OracleDataAdapter(cmd); DataTable table = new DataTable(); adapter.Fill(table); return table; } /// <summary> /// sql对象查询 /// </summary> /// <param name="sql"></param> /// <param name="parameters"></param> /// <returns></returns> public IEnumerable<TEntity> QuerySql(string sql, params object[] parameters) { try { return Context.Database.SqlQuery<TEntity>(sql, parameters).ToList(); } catch (Exception ex) { throw ex; } } /// <summary> /// 删除所有 /// </summary> public void DeleteAll() { Context.Models.RemoveRange(Context.Models); } /// <summary> /// 插入或更新 /// </summary> /// <param name="entity"></param> public void InsertOrUpdate(string keyValue, TEntity entity) { if (!string.IsNullOrEmpty(keyValue)) { entity.Modify(keyValue); Update(keyValue, entity); } else { entity.Create(); Context.Models.Add(entity); } } /// <summary> /// 批量插入 /// </summary> /// <param name="list"></param> public void InsertALL(List<TEntity> list) { Context.Models.AddRange(list); Context.SaveChanges(); } /// <summary> /// 保存修改 /// </summary> public void SaveChanges() { Context.SaveChanges(); } /// <summary> /// 销毁方法 /// </summary> public void Dispose() { Context.SaveChanges(); Context.Dispose(); } #endregion #region 查询调用方法 /// <summary> /// 分页查询 + 排序 /// </summary> /// <typeparam name="Tkey">泛型</typeparam> /// <param name="pageSize">每页大小</param> /// <param name="pageIndex">当前页码</param> /// <param name="total">总数量</param> /// <param name="orderbyLambda">排序条件</param> /// <param name="isAsc">是否升序</param> /// <returns>IQueryable 泛型集合</returns> public IQueryable<TEntity> FindList<Tkey>(int pageIndex, int pageSize, out int records, out int total, Func<TEntity, Tkey> orderbyLambda, bool isAsc) { records = Context.Set<TEntity>().Count(); if (records > 0) { total = records % pageSize == 0 ? records / pageSize : records / pageSize + 1; } else { total = 0; } if (isAsc) { var temp = Context.Set<TEntity>() .OrderBy<TEntity, Tkey>(orderbyLambda) .Skip(pageSize * (pageIndex - 1)) .Take(pageSize); return temp.AsQueryable(); } else { var temp = Context.Set<TEntity>() .OrderByDescending<TEntity, Tkey>(orderbyLambda) .Skip(pageSize * (pageIndex - 1)) .Take(pageSize); return temp.AsQueryable(); } } /// <summary> /// 分页查询 + 条件查询 + 排序 /// </summary> /// <typeparam name="Tkey">泛型</typeparam> /// <param name="pageSize">每页大小</param> /// <param name="pageIndex">当前页码</param> /// <param name="total">总数量</param> /// <param name="whereLambda">查询条件</param> /// <param name="orderbyLambda">排序条件</param> /// <param name="isAsc">是否升序</param> /// <returns>IQueryable 泛型集合</returns> public IQueryable<TEntity> FindList<Tkey>(int pageIndex, int pageSize, out int records, out int total, Expression<Func<TEntity, bool>> whereLambda, Func<TEntity, Tkey> orderbyLambda, bool isAsc) { records = Context.Set<TEntity>().Where(whereLambda).Count(); if (records > 0) { total = records % pageSize == 0 ? records / pageSize : records / pageSize + 1; } else { total = 0; } if (isAsc) { var temp = Context.Set<TEntity>().Where(whereLambda) .OrderBy<TEntity, Tkey>(orderbyLambda) .Skip(pageSize * (pageIndex - 1)) .Take(pageSize); return temp.AsQueryable(); } else { //降序 var temp = Context.Set<TEntity>().Where(whereLambda) .OrderByDescending<TEntity, Tkey>(orderbyLambda) .Skip(pageSize * (pageIndex - 1)) .Take(pageSize); return temp.AsQueryable(); } } #endregion } /// <summary> /// 通过代码配置数据库,避免需要修改app.config /// </summary> public class OracleConfiguration : DbConfiguration { public OracleConfiguration() { SetProviderServices("Oracle.ManagedDataAccess.Client", Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices.Instance); SetProviderFactory("Oracle.ManagedDataAccess.Client", OracleClientFactory.Instance); SetDefaultConnectionFactory(new Oracle.ManagedDataAccess.EntityFramework.OracleConnectionFactory()); } } }
IRepository
using System; using System.Collections.Generic; using System.Data; using System.Data.Common; using System.Data.Entity.Infrastructure; using System.Linq; using System.Linq.Expressions; using System.Text; using System.Threading.Tasks; namespace DataBaseTest.DataBase { public interface IRepository<T> : IDisposable where T : BaseEntity { /// <summary> /// 删除实体 /// </summary> /// <param name="item"></param> bool Delete(string keyValue); /// <summary> /// 删除所有 /// </summary> void DeleteAll(); /// <summary> /// 获取实体集合(延时结果集) /// </summary> /// <returns></returns> IQueryable<T> GetModels(); /// <summary> /// 根据主键获得实体 /// </summary> /// <param name="id"></param> /// <returns></returns> T Find(object id); /// <summary> /// 执行SQL /// </summary> /// <param name="sql"></param> /// <param name="param"></param> /// <returns></returns> int ExecuteSql(string sql, params object[] parameters); /// <summary> /// SQL查询 /// </summary> /// <param name="sql"></param> /// <param name="parameters"></param> /// <returns></returns> IEnumerable<T> QuerySql(string sql, params object[] parameters); /// <summary> /// 手动保存 /// </summary> void SaveChanges(); /// <summary> /// 添加或更新 /// </summary> /// <param name="entity"></param> void InsertOrUpdate(string keyValue, T entity); /// <summary> ///批量插入 /// </summary> /// <param name="list"></param> void InsertALL(List<T> list); /// <summary> /// 根据条件查询 /// </summary> IQueryable<T> FindList<Tkey>(Expression<Func<T, bool>> whereLambda, Func<T, Tkey> orderbyLambda, bool isAsc); /// <summary> /// 分页查询 + 条件查询 + 排序 /// </summary> IQueryable<T> FindList<Tkey>(Pagination pagination, Expression<Func<T, bool>> whereLambda, Func<T, Tkey> orderbyLambda,out int records, out int total); /// <summary> /// 分页查询 + 排序 /// </summary> IQueryable<T> FindList<Tkey>(Pagination pagination, Func<T, Tkey> orderbyLambda, out int records, out int total); /// <summary> /// 获取统计个数 /// </summary> /// <param name="sql"></param> /// <param name="parameters"></param> /// <returns></returns> int QueryCountSql(string sql, params object[] parameters); DataTable FindDataTable(string sql); DataTable SqlQueryForDataTatable(string sql, DbParameter[] parameters); } }
Pagination
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace DataBaseTest.DataBase { public class Pagination { public Pagination() { pageIndex = 1; pageSize = 30; isAsc = false; } /// <summary> /// 每页条数 /// </summary> public int pageSize { get; set; } /// <summary> /// 当前页 /// </summary> public int pageIndex { get; set; } /// <summary> /// 排序列 /// </summary> public string sidx { get; set; } /// <summary> /// 是否升序排列 /// </summary> public bool isAsc { get; set; } /// <summary> /// 总记录数 /// </summary> public int records { get; set; } /// <summary> /// 总页数 /// </summary> public int total { get; set; } } }
ProgramContext
using Oracle.ManagedDataAccess.Client; using System; using System.Collections.Generic; using System.Configuration; using System.Data.Common; using System.Data.Entity; using System.Linq; using System.Text; using System.Threading.Tasks; namespace DataBaseTest.DataBase { public class ProgramContext<T> : DbContext where T : BaseEntity { #region 构造函数 public ProgramContext() : this(CreateConnection("Program")) { } public ProgramContext(DbConnection conn) : base(conn, false) { } public ProgramContext(string connectionString) : this(Create(connectionString)) { } #endregion #region 内部方法 protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.HasDefaultSchema(UserName.ToUpper()); base.OnModelCreating(modelBuilder); } protected static OracleConnection CreateConnection(string connectionName) { OracleConnectionStringBuilder oracleBuilder = new OracleConnectionStringBuilder(); oracleBuilder.ConnectionString = ConfigurationManager.ConnectionStrings[connectionName].ConnectionString; UserName = oracleBuilder.UserID; OracleConnection conn = new OracleConnection(oracleBuilder.ConnectionString); return conn; } protected static OracleConnection Create(string connectionString) { OracleConnectionStringBuilder oracleBuilder = new OracleConnectionStringBuilder(); oracleBuilder.ConnectionString = connectionString; UserName = oracleBuilder.UserID; OracleConnection conn = new OracleConnection(oracleBuilder.ConnectionString); return conn; } #endregion #region 属性 public DbSet<T> Models { get; set; } private static string UserName { get; set; } #endregion } }
RepositoryManager
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace DataBaseTest.DataBase { public class RepositoryManager { /// <summary> /// 静态构造函数 /// </summary> static RepositoryManager() { Connections = new Dictionary<string, string>(); } /// <summary> /// 连接信息字典 /// </summary> private static Dictionary<string, string> Connections { get; set; } /// <summary> /// 默认创建方式 /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> public static IRepository<T> CreateRepository<T>() where T : BaseEntity { try { return new EFRepository<T>(Connections["Program"]); } catch (KeyNotFoundException) { return new EFRepository<T>(); } } /// <summary> /// 指定连接信息创建方式 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="connectionString"></param> /// <returns></returns> public static IRepository<T> CreateRepository<T>(string name) where T : BaseEntity { try { return new EFRepository<T>(Connections[name]); } catch (KeyNotFoundException) { return new EFRepository<T>(); } } /// <summary> /// 添加链接信息 /// </summary> /// <param name="name"></param> /// <param name="connectionString"></param> public static void AddConnection(string name, string connectionString) { if (Connections.ContainsKey(name)) Connections[name] = connectionString; else Connections.Add(name, connectionString); } } }