调用
//连接数据库
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 repository = RepositoryManager.CreateRepository())
{
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
{
///
/// 选项ID
///
[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
{
///
/// 新增调用
///
public virtual void Create()
{
}
///
/// 新增调用
///
public virtual void CreateApp()
{
}
///
/// 编辑调用
///
/// 主键值
public virtual void Modify(string keyValue)
{
}
///
/// 删除调用
///
/// 主键值
public virtual void Remove(string keyValue)
{
}
///
/// 编辑调用
///
/// 主键值
public virtual void Modify(int keyValue)
{
}
///
/// 删除调用
///
/// 主键值
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 : IRepository where TEntity : BaseEntity
{
#region 属性
///
/// 数据库上下文
///
private ProgramContext Context { get; set; }
#endregion
#region 构造函数
///
/// 构造函数
///
public EFRepository()
{
Context = new ProgramContext();
}
public EFRepository(string connectionString)
{
Context = new ProgramContext(connectionString);
}
#endregion
#region 公共方法
///
/// 删除对象
///
///
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;
}
///
/// 按ID获取对象
///
///
///
public TEntity Find(object id)
{
return Context.Models.Find(id);
}
///
/// 获取所有对象
///
///
public IQueryable GetModels()
{
return Context.Models;
}
///
/// 根据条件进行查询
///
///
///
///
///
///
public IQueryable FindList(Expression> whereLambda, Func orderbyLambda, bool isAsc)
{
if (isAsc)
{
var temp = Context.Set().Where(whereLambda)
.OrderBy(orderbyLambda);
return temp.AsQueryable();
}
else
{
var temp = Context.Set().Where(whereLambda)
.OrderByDescending(orderbyLambda);
return temp.AsQueryable();
}
}
///
/// 分页查询 + 排序
///
///
///
///
///
public IQueryable FindList(Pagination pagination, Func orderbyLambda,out int records,out int total)
{
records = pagination.records;//总记录数
total = pagination.total;//总页数
var data = FindList(pagination.pageIndex, pagination.pageSize, out records, out total, orderbyLambda, pagination.isAsc);
return data;
}
///
/// 分页查询 + 条件查询 + 排序
///
///
///
///
///
public IQueryable FindList(Pagination pagination, Expression> whereLambda, Func orderbyLambda,out int records, out int total)
{
records = pagination.records;//总记录数
total = pagination.total;//总页数
var data = FindList(pagination.pageIndex, pagination.pageSize, out records, out total, whereLambda, orderbyLambda, pagination.isAsc);
return data;
}
///
/// 更新对象
///
///
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()
.Map(item, entityToUpdate);
return true;
}
///
/// 执行sql
///
///
///
///
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(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;
}
///
/// sql对象查询
///
///
///
///
public IEnumerable QuerySql(string sql, params object[] parameters)
{
try
{
return Context.Database.SqlQuery(sql, parameters).ToList();
}
catch (Exception ex)
{
throw ex;
}
}
///
/// 删除所有
///
public void DeleteAll()
{
Context.Models.RemoveRange(Context.Models);
}
///
/// 插入或更新
///
///
public void InsertOrUpdate(string keyValue, TEntity entity)
{
if (!string.IsNullOrEmpty(keyValue))
{
entity.Modify(keyValue);
Update(keyValue, entity);
}
else
{
entity.Create();
Context.Models.Add(entity);
}
}
///
/// 批量插入
///
///
public void InsertALL(List list)
{
Context.Models.AddRange(list);
Context.SaveChanges();
}
///
/// 保存修改
///
public void SaveChanges()
{
Context.SaveChanges();
}
///
/// 销毁方法
///
public void Dispose()
{
Context.SaveChanges();
Context.Dispose();
}
#endregion
#region 查询调用方法
///
/// 分页查询 + 排序
///
/// 泛型
/// 每页大小
/// 当前页码
/// 总数量
/// 排序条件
/// 是否升序
/// IQueryable 泛型集合
public IQueryable FindList(int pageIndex, int pageSize, out int records, out int total, Func orderbyLambda, bool isAsc)
{
records = Context.Set().Count();
if (records > 0)
{
total = records % pageSize == 0 ? records / pageSize : records / pageSize + 1;
}
else
{
total = 0;
}
if (isAsc)
{
var temp = Context.Set()
.OrderBy(orderbyLambda)
.Skip(pageSize * (pageIndex - 1))
.Take(pageSize);
return temp.AsQueryable();
}
else
{
var temp = Context.Set()
.OrderByDescending(orderbyLambda)
.Skip(pageSize * (pageIndex - 1))
.Take(pageSize);
return temp.AsQueryable();
}
}
///
/// 分页查询 + 条件查询 + 排序
///
/// 泛型
/// 每页大小
/// 当前页码
/// 总数量
/// 查询条件
/// 排序条件
/// 是否升序
/// IQueryable 泛型集合
public IQueryable FindList(int pageIndex, int pageSize, out int records, out int total, Expression> whereLambda, Func orderbyLambda, bool isAsc)
{
records = Context.Set().Where(whereLambda).Count();
if (records > 0)
{
total = records % pageSize == 0 ? records / pageSize : records / pageSize + 1;
}
else
{
total = 0;
}
if (isAsc)
{
var temp = Context.Set().Where(whereLambda)
.OrderBy(orderbyLambda)
.Skip(pageSize * (pageIndex - 1))
.Take(pageSize);
return temp.AsQueryable();
}
else
{
//降序
var temp = Context.Set().Where(whereLambda)
.OrderByDescending(orderbyLambda)
.Skip(pageSize * (pageIndex - 1))
.Take(pageSize);
return temp.AsQueryable();
}
}
#endregion
}
///
/// 通过代码配置数据库，避免需要修改app.config
///
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 : IDisposable where T : BaseEntity
{
///
/// 删除实体
///
///
bool Delete(string keyValue);
///
/// 删除所有
///
void DeleteAll();
///
/// 获取实体集合(延时结果集)
///
///
IQueryable GetModels();
///
/// 根据主键获得实体
///
///
///
T Find(object id);
///
/// 执行SQL
///
///
///
///
int ExecuteSql(string sql, params object[] parameters);
///
/// SQL查询
///
///
///
///
IEnumerable QuerySql(string sql, params object[] parameters);
///
/// 手动保存
///
void SaveChanges();
///
/// 添加或更新
///
///
void InsertOrUpdate(string keyValue, T entity);
///
///批量插入
///
///
void InsertALL(List list);
///
/// 根据条件查询
///
IQueryable FindList(Expression> whereLambda, Func orderbyLambda, bool isAsc);
///
/// 分页查询 + 条件查询 + 排序
///
IQueryable FindList(Pagination pagination, Expression> whereLambda, Func orderbyLambda,out int records, out int total);
///
/// 分页查询 + 排序
///
IQueryable FindList(Pagination pagination, Func orderbyLambda, out int records, out int total);
///
/// 获取统计个数
///
///
///
///
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;
}
///
/// 每页条数
///
public int pageSize { get; set; }
///
/// 当前页
///
public int pageIndex { get; set; }
///
/// 排序列
///
public string sidx { get; set; }
///
/// 是否升序排列
///
public bool isAsc { get; set; }
///
/// 总记录数
///
public int records { get; set; }
///
/// 总页数
///
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 : 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 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
{
///
/// 静态构造函数
///
static RepositoryManager()
{
Connections = new Dictionary();
}
///
/// 连接信息字典
///
private static Dictionary Connections { get; set; }
///
/// 默认创建方式
///
///
///
public static IRepository CreateRepository() where T : BaseEntity
{
try
{
return new EFRepository(Connections["Program"]);
}
catch (KeyNotFoundException)
{
return new EFRepository();
}
}
///
/// 指定连接信息创建方式
///
///
///
///
public static IRepository CreateRepository(string name) where T : BaseEntity
{
try
{
return new EFRepository(Connections[name]);
}
catch (KeyNotFoundException)
{
return new EFRepository();
}
}
///
/// 添加链接信息
///
///
///
public static void AddConnection(string name, string connectionString)
{
if (Connections.ContainsKey(name))
Connections[name] = connectionString;
else
Connections.Add(name, connectionString);
}
}
}