using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.Common;
|
|
namespace CSFramework.DB
|
{
|
/// <summary>
|
/// CSFramework通用数据框架-对象实体，支持MsSQL,MySQL,Oracle三种数据库
|
/// </summary>
|
public class DbFramework
|
{
|
/// <summary>
|
/// 当前事务
|
/// </summary>
|
protected DbTransaction _CurrentTrans = null;
|
|
protected IDatabase _db;
|
|
/// <summary>
|
/// 构造器
|
/// </summary>
|
/// <param name="db">CSFramework.DB数据库底层通用接口</param>
|
public DbFramework(IDatabase db)
|
{
|
_db = db;
|
}
|
|
/// <summary>
|
/// 构造器
|
/// </summary>
|
/// <param name="db">CSFramework.DB数据库底层通用接口</param>
|
/// <param name="tran">事务</param>
|
public DbFramework(IDatabase db, DbTransaction tran = null)
|
{
|
_db = db;
|
_CurrentTrans = tran;
|
}
|
|
/// <summary>
|
/// 启动事务机制
|
/// </summary>
|
private void BeginTransaction()
|
{
|
if (_CurrentTrans == null)
|
_CurrentTrans = _db.TransBegin();
|
}
|
|
/// <summary>
|
/// 提交事务
|
/// </summary>
|
private void CommitTransaction()
|
{
|
try
|
{
|
if (_CurrentTrans != null)
|
{
|
_CurrentTrans.Commit();
|
_CurrentTrans.Dispose();
|
_CurrentTrans = null;
|
}
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 回滚事务
|
/// </summary>
|
private void RollbackTransaction()
|
{
|
try
|
{
|
if (_CurrentTrans != null)
|
{
|
_CurrentTrans.Rollback();
|
_CurrentTrans.Dispose();
|
_CurrentTrans = null;
|
}
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 查询数据，返回对象列表
|
/// </summary>
|
/// <typeparam name="T">对象类型</typeparam>
|
/// <param name="sql">查询语句</param>
|
/// <returns></returns>
|
public List<T> Select<T>(string sql) where T : new()
|
{
|
return _db.ExecuteReaderList<T>(sql);
|
}
|
|
/// <summary>
|
/// 获取当前模型所有数据
|
/// </summary>
|
/// <typeparam name="T"></typeparam>
|
/// <returns></returns>
|
public List<T> Select<T>() where T : new()
|
{
|
var sql = $"SELECT * FROM {typeof(T).Name}";
|
return _db.ExecuteReaderList<T>(sql);
|
}
|
|
public T Select<T>(string keyFieldName, object keyValue) where T : new()
|
{
|
var sql =
|
$"SELECT * FROM {typeof(T).Name} WHERE {keyFieldName}={_db.ParamSymboName + keyFieldName}";
|
var cmd = _db.CreateCommand(sql);
|
cmd.AddParam(keyFieldName).Value = keyValue;
|
return _db.ExecuteReader<T>(cmd.Command);
|
}
|
|
/// <summary>
|
/// 查询数据，返回对象列表
|
/// </summary>
|
/// <typeparam name="T">对象类型</typeparam>
|
/// <param name="cmd">命令</param>
|
/// <returns></returns>
|
public List<T> Select<T>(DbCommand cmd) where T : new()
|
{
|
return _db.ExecuteReaderList<T>(cmd);
|
}
|
|
/// <summary>
|
/// 提交单表DataTable
|
/// </summary>
|
/// <param name="dt">DataTable对象</param>
|
/// <param name="orm">DataTable对应的实体类</param>
|
/// <param name="PKs">主键字段数组</param>
|
/// <param name="ignoreFields">自增字段(或不能更新的字段)数组</param>
|
/// <param name="updateFields">指定更新的字段</param>
|
/// <returns></returns>
|
public int Update(DataTable dt, Type orm,
|
string[] PKs,
|
string[] ignoreFields = null,
|
string[] updateFields = null)
|
{
|
var count = 0;
|
try
|
{
|
BeginTransaction();
|
|
var generator = new DbCommandGenerator(_db);
|
var adp = _db.CreateDataAdapter();
|
|
adp.UpdateCommand =
|
generator.GetUpdateCommand(orm, PKs, ignoreFields,
|
updateFields);
|
adp.InsertCommand =
|
generator.GetInsertCommand(orm, ignoreFields);
|
adp.DeleteCommand = generator.GetDeleteCommand(orm, PKs);
|
|
adp.UpdateCommand.Transaction = _CurrentTrans;
|
adp.InsertCommand.Transaction = _CurrentTrans;
|
adp.DeleteCommand.Transaction = _CurrentTrans;
|
|
adp.UpdateCommand.Connection = _CurrentTrans.Connection;
|
adp.InsertCommand.Connection = _CurrentTrans.Connection;
|
adp.DeleteCommand.Connection = _CurrentTrans.Connection;
|
|
adp.AcceptChangesDuringUpdate = false;
|
count += adp.Update(dt);
|
adp.Dispose();
|
|
CommitTransaction();
|
|
return count;
|
}
|
catch (DBConcurrencyException ex)
|
{
|
RollbackTransaction(); //回滚事务
|
throw new Exception(
|
"并发操作！其他用户已更新了源数据！Event:DbDataUpdate.Update()\r\n\r\n" +
|
ex.Message);
|
}
|
catch (Exception ex)
|
{
|
RollbackTransaction(); //回滚事务
|
throw new Exception(
|
"更新数据发生错误！Event:DbDataUpdate。Update(),Table:" +
|
dt.TableName + "\r\n\r\n" + ex.Message);
|
}
|
}
|
|
/// <summary>
|
/// 提交Dataset多表数据
|
/// </summary>
|
/// <param name="ds">数据集</param>
|
/// <param name="orms">ORM与表结构参数定义</param>
|
/// <returns></returns>
|
public int Update(DataSet ds, List<DbDataUpdateORM> orms)
|
{
|
if (ds.Tables.Count != orms.Count)
|
throw new Exception("数据表与对象模型的数目不一致！");
|
|
var generator = new DbCommandGenerator(_db);
|
|
var mCurrentTable = "";
|
DbDataUpdateORM mORM;
|
var count = 0;
|
|
try
|
{
|
BeginTransaction();
|
|
foreach (DataTable dt in ds.Tables)
|
{
|
mCurrentTable = dt.TableName;
|
mORM = orms[ds.Tables.IndexOf(dt)];
|
|
var adp = _db.CreateDataAdapter();
|
adp.UpdateCommand =
|
generator.GetUpdateCommand(mORM.ObjectType, mORM.PKs,
|
mORM.IgnoreFields);
|
adp.InsertCommand =
|
generator.GetInsertCommand(mORM.ObjectType,
|
mORM.IgnoreFields);
|
adp.DeleteCommand =
|
generator.GetDeleteCommand(mORM.ObjectType, mORM.PKs);
|
|
adp.UpdateCommand.Transaction = _CurrentTrans;
|
adp.InsertCommand.Transaction = _CurrentTrans;
|
adp.DeleteCommand.Transaction = _CurrentTrans;
|
|
adp.UpdateCommand.Connection = _CurrentTrans.Connection;
|
adp.InsertCommand.Connection = _CurrentTrans.Connection;
|
adp.DeleteCommand.Connection = _CurrentTrans.Connection;
|
|
adp.AcceptChangesDuringUpdate = false;
|
count += adp.Update(dt);
|
adp.Dispose();
|
}
|
|
CommitTransaction();
|
|
return count;
|
}
|
catch (DBConcurrencyException ex)
|
{
|
RollbackTransaction(); //回滚事务
|
throw new Exception(
|
"并发操作！其他用户已更新了源数据！Event:DbDataUpdate.Update()\r\n\r\n" +
|
ex.Message);
|
}
|
catch (Exception ex)
|
{
|
RollbackTransaction(); //回滚事务
|
throw new Exception("更新数据发生错误！Event:Update(),Table:" +
|
mCurrentTable + "\r\n\r\n" + ex.Message);
|
}
|
}
|
|
/// <summary>
|
/// 新增对象
|
/// </summary>
|
/// <param name="obj">对象实例</param>
|
/// <param name="ignoreFields">自增字段(或不支持更新的字段)数组</param>
|
/// <returns></returns>
|
public bool AddObject<T>(T obj, params string[] ignoreFields)
|
where T : new()
|
{
|
var count = 0;
|
using (var conn = _db.CreateConnection())
|
{
|
try
|
{
|
var generator = new DbCommandGenerator(_db);
|
var cmd = generator.GetInsertCommand<T>(obj, ignoreFields);
|
cmd.Connection = conn;
|
cmd.Transaction = _CurrentTrans;
|
var i = _db.ExecuteCommand(cmd);
|
if (i > 0) count += i;
|
return count > 0;
|
}
|
catch (Exception ex)
|
{
|
throw new Exception("更新数据发生错误！Event:Add()" + "\r\n\r\n" +
|
ex.Message);
|
}
|
}
|
}
|
|
/// <summary>
|
/// 新增对象
|
/// </summary>
|
/// <param name="obj">对象实例</param>
|
/// <param name="ignoreFields">自增字段(或不支持更新的字段)数组</param>
|
/// <param name="isid">返回的最大自增字段值</param>
|
/// <returns></returns>
|
public bool AddObject<T>(T obj, string[] ignoreFields, out int isid)
|
where T : new()
|
{
|
isid = 0;
|
var count = 0;
|
using (var conn = _db.CreateConnection())
|
{
|
try
|
{
|
var generator = new DbCommandGenerator(_db);
|
var cmd = generator.GetInsertCommand<T>(obj, ignoreFields);
|
var tableName =
|
generator.GetTableName(obj.GetType()); //获取类对应的表名
|
cmd.Connection = conn;
|
cmd.Transaction = _CurrentTrans;
|
var i = _db.ExecuteCommand(cmd);
|
if (i > 0)
|
{
|
count += i;
|
isid = _db.GetMaxID(tableName); //获取最大的isid
|
}
|
|
return count > 0;
|
}
|
catch (Exception ex)
|
{
|
throw new Exception("更新数据发生错误！Event:Add()" + "\r\n\r\n" +
|
ex.Message);
|
}
|
}
|
}
|
|
/// <summary>
|
/// 新增对象列表
|
/// </summary>
|
/// <param name="list">对象实例列表</param>
|
/// <param name="ignoreFields">自增字段(或不支持更新的字段)数组</param>
|
/// <returns></returns>
|
public int AddList<T>(List<T> list, params string[] ignoreFields)
|
where T : new()
|
{
|
var generator = new DbCommandGenerator(_db);
|
var count = 0;
|
BeginTransaction();
|
try
|
{
|
foreach (var obj in list)
|
{
|
var cmd = generator.GetInsertCommand<T>(obj, ignoreFields);
|
cmd.Transaction = _CurrentTrans;
|
cmd.Connection = _CurrentTrans.Connection;
|
var i = _db.ExecuteCommand(cmd);
|
if (i > 0) count += i;
|
}
|
|
CommitTransaction();
|
return count;
|
}
|
catch (Exception ex)
|
{
|
RollbackTransaction();
|
throw new Exception("更新数据发生错误！Event:Add()" + "\r\n\r\n" +
|
ex.Message);
|
}
|
}
|
|
/// <summary>
|
/// 批量更新对象列表数据
|
/// </summary>
|
/// <param name="list">对象实例列表</param>
|
/// <param name="PKs">主键字段名数组</param>
|
/// <param name="ignoreFields">自增字段(或不支持更新的字段)数组</param>
|
/// <returns></returns>
|
public int UpdateList<T>(List<T> list,
|
string[] PKs = null,
|
string[] ignoreFields = null,
|
string[] updateFields = null) where T : new()
|
{
|
var generator = new DbCommandGenerator(_db);
|
var count = 0;
|
try
|
{
|
BeginTransaction();
|
foreach (var obj in list)
|
{
|
var cmd = generator.GetUpdateCommand<T>(obj, PKs,
|
ignoreFields, updateFields);
|
cmd.Transaction = _CurrentTrans;
|
cmd.Connection = _CurrentTrans.Connection;
|
var i = _db.ExecuteCommand(cmd);
|
if (i > 0) count += i;
|
}
|
|
CommitTransaction();
|
return count;
|
}
|
catch (Exception ex)
|
{
|
RollbackTransaction();
|
throw new Exception("更新数据发生错误！Event:Add()" + "\r\n\r\n" +
|
ex.Message);
|
}
|
}
|
|
|
/// <summary>
|
/// 修改对象,更新单个对象数据
|
/// </summary>
|
/// <param name="obj">对象实例</param>
|
/// <param name="PKs">主键字段名数组</param>
|
/// <param name="ignoreFields">自增字段(或不支持更新的字段)数组</param>
|
/// <returns></returns>
|
public bool UpdateObject<T>(T obj,
|
string[] PKs = null,
|
string[] ignoreFields = null,
|
string[] updateFields = null) where T : new()
|
{
|
var count = 0;
|
using (var conn = _db.CreateConnection())
|
{
|
try
|
{
|
var generator = new DbCommandGenerator(_db);
|
var cmd = generator.GetUpdateCommand<T>(obj, PKs,
|
ignoreFields, updateFields);
|
cmd.Connection = conn;
|
cmd.Transaction = _CurrentTrans;
|
var i = _db.ExecuteCommand(cmd);
|
if (i > 0) count += i;
|
return count > 0;
|
}
|
catch (Exception ex)
|
{
|
throw new Exception("更新数据发生错误！Event:Add()" + "\r\n\r\n" +
|
ex.Message);
|
}
|
}
|
}
|
|
/// <summary>
|
/// 批量删除数据
|
/// </summary>
|
/// <typeparam name="T"></typeparam>
|
/// <param name="list">对象实例列表</param>
|
/// <param name="PKs">主键字段名数组</param>
|
/// <returns></returns>
|
public int DeleteList<T>(List<T> list, params string[] PKs)
|
where T : new()
|
{
|
var generator = new DbCommandGenerator(_db);
|
var count = 0;
|
try
|
{
|
BeginTransaction();
|
foreach (var obj in list)
|
{
|
var cmd = generator.GetDeleteCommand<T>(obj, PKs);
|
cmd.Transaction = _CurrentTrans;
|
cmd.Connection = _CurrentTrans.Connection;
|
var i = _db.ExecuteCommand(cmd);
|
if (i > 0) count += i;
|
}
|
|
CommitTransaction();
|
return count;
|
}
|
catch (Exception ex)
|
{
|
RollbackTransaction();
|
throw new Exception("更新数据发生错误！Event:Add()" + "\r\n\r\n" +
|
ex.Message);
|
}
|
}
|
|
/// <summary>
|
/// 删除对象
|
/// </summary>
|
/// <param name="obj">对象实例</param>
|
/// <param name="PKs">主键字段名数组</param>
|
/// <returns></returns>
|
public bool DeleteObject<T>(T obj, params string[] PKs) where T : new()
|
{
|
var count = 0;
|
using (var conn = _db.CreateConnection())
|
{
|
try
|
{
|
var generator = new DbCommandGenerator(_db);
|
var cmd = generator.GetDeleteCommand<T>(obj, PKs);
|
cmd.Connection = conn;
|
cmd.Transaction = _CurrentTrans;
|
var i = _db.ExecuteCommand(cmd);
|
if (i > 0) count += i;
|
return count > 0;
|
}
|
catch (Exception ex)
|
{
|
throw new Exception("更新数据发生错误！Event:Add()" + "\r\n\r\n" +
|
ex.Message);
|
}
|
}
|
}
|
}
|
|
/// <summary>
|
/// 旧的名称:DbDataUpdate, 请使用DbFramework
|
/// </summary>
|
public class DbDataUpdate : DbFramework
|
{
|
/// <summary>
|
/// 构造器
|
/// </summary>
|
/// <param name="db">CSFramework.DB数据库底层通用接口</param>
|
public DbDataUpdate(IDatabase db) : base(db)
|
{
|
}
|
|
/// <summary>
|
/// 构造器
|
/// </summary>
|
/// <param name="db">CSFramework.DB数据库底层通用接口</param>
|
/// <param name="tran">事务</param>
|
public DbDataUpdate(IDatabase db, DbTransaction tran = null) : base(db,
|
tran)
|
{
|
}
|
}
|
}
|
