c oracle帮助类,oracle通用帮助类

调用

//连接数据库

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

}

}

}