需要的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",
"",
"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(), 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 > )
{
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 > )
{
total = records % pageSize == ? records / pageSize : records / pageSize + ;
}
else
{
total = ;
}
if (isAsc)
{
var temp = Context.Set<TEntity>()
.OrderBy<TEntity, Tkey>(orderbyLambda)
.Skip(pageSize * (pageIndex - ))
.Take(pageSize);
return temp.AsQueryable();
}
else
{
var temp = Context.Set<TEntity>()
.OrderByDescending<TEntity, Tkey>(orderbyLambda)
.Skip(pageSize * (pageIndex - ))
.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 > )
{
total = records % pageSize == ? records / pageSize : records / pageSize + ;
}
else
{
total = ;
}
if (isAsc)
{
var temp = Context.Set<TEntity>().Where(whereLambda)
.OrderBy<TEntity, Tkey>(orderbyLambda)
.Skip(pageSize * (pageIndex - ))
.Take(pageSize);
return temp.AsQueryable();
}
else
{
//降序
var temp = Context.Set<TEntity>().Where(whereLambda)
.OrderByDescending<TEntity, Tkey>(orderbyLambda)
.Skip(pageSize * (pageIndex - ))
.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 = ;
pageSize = ;
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);
}
}
}