#region
|
|
using System;
|
using System.Data;
|
using System.Data.Common;
|
using CSFrameworkV5.Common;
|
using CSFrameworkV5.Core;
|
using CSFrameworkV5.Interfaces;
|
|
#endregion
|
|
namespace CSFrameworkV5.DataAccess
|
{
|
/// <summary>
|
/// 业务单据数据层基类
|
/// </summary>
|
public class dalBaseBusiness : dalBase
|
{
|
/// <summary>
|
/// true:提交数据前去左右空格、全角转半角，若设置此值会有性能损失！
|
/// </summary>
|
protected bool _ReplaceString = false;
|
|
/// <summary>
|
/// 主表主键
|
/// </summary>
|
protected string _SummaryKeyName = "";
|
|
/// <summary>
|
/// 主表表名
|
/// </summary>
|
protected string _SummaryTableName = "";
|
|
/// <summary>
|
/// 设置为受保护的构造器，不允许外部实例化
|
/// </summary>
|
/// <param name="loginer"></param>
|
protected dalBaseBusiness(Loginer loginer)
|
: base(loginer)
|
{
|
//
|
}
|
|
/// <summary>
|
/// 单据审核/批准功能
|
/// </summary>
|
/// <param name="keyValue">业务主键</param>
|
/// <param name="flagApp">审核标记:Y/N</param>
|
/// <param name="appUser">审核人</param>
|
/// <param name="appDate">审核日期</param>
|
public virtual bool ApprovalBusiness(QueryApproval P)
|
{
|
var sp = _Database.CreateSqlProc("usp_ApprovalBusiness");
|
sp.AddParam("TableName", DbType.String, 50, P.TableName);
|
sp.AddParam("KeyFieldName", DbType.String, 50, P.KeyFieldName);
|
sp.AddParam("KeyValue", DbType.String, 50, P.KeyValue);
|
sp.AddParam("FlagApp", DbType.String, 250, P.FlagApp);
|
sp.AddParam("AppUser", DbType.String, 250, _Loginer.Account);
|
sp.AddParam("AppNAME", DbType.String, 250, P.AppNAME);
|
var i = _Database.ExecuteCommand(sp.Command);
|
return true;
|
}
|
|
/// <summary>
|
/// 调用后台数据库执行SQL
|
/// </summary>
|
/// <param name="keyValue">业务主键</param>
|
/// <param name="flagApp">审核标记:Y/N</param>
|
/// <param name="appUser">审核人</param>
|
/// <param name="appDate">审核日期</param>
|
public virtual DataTable GetDataTable(string SQL)
|
{
|
return _Database.GetTable(SQL);
|
}
|
|
/// <summary>
|
/// 调用后台数据库执行SQL
|
/// </summary>
|
/// <param name="keyValue">业务主键</param>
|
/// <param name="flagApp">审核标记:Y/N</param>
|
/// <param name="appUser">审核人</param>
|
/// <param name="appDate">审核日期</param>
|
public virtual bool InstDataSet(DataTable SQL, string NAME)
|
{
|
return _Database.InstDataSet(SQL, NAME);
|
}
|
|
/// <summary>
|
/// 调用后台数据库执行SQL返回INT
|
/// </summary>
|
/// <param name="keyValue">业务主键</param>
|
/// <param name="flagApp">审核标记:Y/N</param>
|
/// <param name="appUser">审核人</param>
|
/// <param name="appDate">审核日期</param>
|
public virtual int GetExecute(string SQL)
|
{
|
return _Database.ExecuteSQL(SQL);
|
}
|
|
|
/// <summary>
|
/// 检查业务数据是否存在
|
/// </summary>
|
/// <param name="keyValue">业务单据主键</param>
|
/// <returns></returns>
|
public virtual bool CheckNoExists(string keyValue)
|
{
|
var sql =
|
$"SELECT COUNT(*) AS Total FROM {_SummaryTableName} WHERE {_SummaryKeyName}={_Database.ParamSymboName}KEY";
|
var cmd = _Database.CreateCommand(sql);
|
cmd.AddParam("KEY", DbType.String, keyValue);
|
var o = _Database.ExecuteScalar(cmd.Command);
|
return ConvertEx.ToInt(o) > 0;
|
}
|
|
/// <summary>
|
/// 根据表名获取该表的SQL命令生成器
|
/// </summary>
|
/// <param name="tableName">表名</param>
|
/// <returns></returns>
|
protected virtual IGenerateSqlCommand CreateSqlGenerator(
|
DataTable table)
|
{
|
return null;
|
}
|
|
/// <summary>
|
/// 获取业务单据流水号
|
/// </summary>
|
protected virtual string GetNumber(DbTransaction tran)
|
{
|
return string.Empty;
|
}
|
|
/// <summary>
|
/// 业务单据的保存数据方法
|
/// </summary>
|
public virtual SaveResult Update(DataSet data)
|
{
|
/***********************************************************************************
|
* 数据更新注意事项:
|
*
|
* 1. 参数:data 是业务数据所有更新的数据表. DataSet的第1张表为主表，
|
* 第2,3,...n张表为明细表或其它数据。
|
*
|
* 2. 主表的主键可能是系统自动生成或用户输入，若是用户输入，必须在保存前检查唯一性.
|
* 当_UpdateSummaryKeyMode=UpdateKeyMode.None,系统生成主键，通常是用户输入主键值.
|
*
|
* 3. 首先获取主表主键的值，再调用UpdateDetailKey()方法更新所有明细表的外键值.
|
*
|
************************************************************************************/
|
|
var mResult = SaveResult.CreateDefault(); //预设保存结果
|
|
var mGUID = string.Empty; //生成的GUID号码
|
var mDocNo = string.Empty; //生成的流水号
|
var mCurrentTable = string.Empty; //当前正在更新的表
|
|
_CurrentDataSet4Update = data; //临时存储当前数据集
|
|
//非用户手动事务模式，预设启用事务
|
if (_UserManualControlTrans == false) BeginTransaction();
|
|
if (_CurrentTrans == null)
|
throw new Exception("用户手动控制事务模式下，您没有启用事务！");
|
|
try
|
{
|
//枚举所有资料表，逐一提交
|
foreach (DataTable dt in data.Tables)
|
{
|
_CurrentTable4Update = dt; //临时存储当前数据表
|
|
if (dt.GetChanges() == null) continue; //仅处理有修改的资料表
|
|
if (_ReplaceString)
|
FieldFitString.FitString(dt); //去左右空格，将大写符号转小写
|
|
//根据表名获取SQL命令生成器，派生类必须重写CreateSqlGenerator方法
|
var gen = CreateSqlGenerator(dt);
|
if (gen == null)
|
throw new CustomException("创建SQL命令生成器失败！表名：" +
|
dt.TableName);
|
|
mCurrentTable = dt.TableName;
|
|
//若当前更新的是主表(模型的特性参数isSummaryTable=True),取主表的主键，用于设置明细表的外键
|
if (gen.IsSummary())
|
{
|
UpdateSummaryKey(_CurrentTrans, dt,
|
_UpdateSummaryKeyMode, gen.GetPrimaryFieldName(),
|
ref mGUID, gen.GetDocNoFieldName(),
|
ref mDocNo); //生成主键
|
mResult.GUID = mGUID;
|
mResult.DocNo = mDocNo;
|
}
|
else
|
{
|
//更新明细表的外键
|
if (_UpdateSummaryKeyMode ==
|
UpdateKeyMode.OnlyDocumentNo) //单号
|
UpdateDetailKey(dt, gen.GetForeignFieldName(),
|
mDocNo);
|
else if (_UpdateSummaryKeyMode ==
|
UpdateKeyMode.OnlyGuid) //GUID
|
UpdateDetailKey(dt, gen.GetForeignFieldName(),
|
mGUID);
|
}
|
|
var adp =
|
_Database.CreateDataAdapter(AdapterRowUpdatingEvent);
|
adp.UpdateCommand = gen.GetUpdateCommand(_CurrentTrans);
|
adp.InsertCommand = gen.GetInsertCommand(_CurrentTrans);
|
adp.DeleteCommand = gen.GetDeleteCommand(_CurrentTrans);
|
adp.AcceptChangesDuringUpdate =
|
false; //提交数据后保留原始数据，用于保存修改日志，对比原始值2017-02-26
|
adp.Update(dt);
|
adp.Dispose();
|
}
|
|
if (_UserManualControlTrans == false)
|
CommitTransaction(); //提交事务
|
}
|
catch (DBConcurrencyException ex)
|
{
|
if (_UserManualControlTrans == false)
|
RollbackTransaction(); //回滚事务
|
|
mResult.Description =
|
"并发操作！其他用户已更新了源数据！Event:DAL.Update()"; //保存结果设置异常消息
|
mResult.Result = 0;
|
throw new Exception(mResult.Description);
|
}
|
catch (Exception ex)
|
{
|
if (_UserManualControlTrans == false)
|
RollbackTransaction(); //回滚事务
|
|
mResult.Description =
|
"更新数据发生错误！Event:Update(),Table:" + mCurrentTable +
|
ex.Message;
|
mResult.Result = 0;
|
throw new Exception(mResult.Description);
|
}
|
|
return mResult; //返回保存结果
|
}
|
|
/// <summary>
|
/// 更新明细表的外键
|
/// </summary>
|
/// <param name="detail">明细表数据</param>
|
/// <param name="foreignFieldName">外键字段名称,对应主表的主键字段名称,如：PONO</param>
|
/// <param name="foreignKeyValue">外键值,对应主表的主键值，如：PO999999</param>
|
protected virtual void UpdateDetailKey(DataTable detail,
|
string foreignFieldName, string foreignKeyValue)
|
{
|
if (detail == null) throw new Exception("明细表不能为空(null)！");
|
|
if (string.IsNullOrEmpty(foreignFieldName))
|
throw new Exception("明细表没指定外键, 请检查表模型定义！");
|
|
foreach (DataRow row in detail.Rows)
|
//仅新增记录才需要更新外键，注意状态的使用
|
if (row.RowState == DataRowState.Added)
|
{
|
if (string.IsNullOrEmpty(foreignKeyValue))
|
throw new Exception("外键值为空！");
|
|
row[foreignFieldName] = foreignKeyValue;
|
}
|
}
|
|
/// <summary>
|
/// 更新主表主键
|
/// </summary>
|
/// <param name="tran">当前数据库事务</param>
|
/// <param name="summary">主表数据</param>
|
/// <param name="model">主键数据更新类型</param>
|
/// <param name="keyFieldName">主键字段名称,对应模型字段特性定义为isPrimaryKey=true的字段</param>
|
/// <param name="GUID">若mode=OnlyGuid,自动生成32位的GUID作为主键值</param>
|
/// <param name="docNoFieldName">单据号码的字段名,对应模型字段特性定义为docNoFieldName=true的字段</param>
|
/// <param name="docNo">若mode=OnlyDocumentNo,自动生成单据号码</param>
|
protected virtual void UpdateSummaryKey(DbTransaction tran,
|
DataTable summary, UpdateKeyMode mode,
|
string keyFieldName, ref string GUID, string docNoFieldName,
|
ref string docNo)
|
{
|
//主表取DataTable的第一条记录
|
var row = summary.Rows[0];
|
|
//如果未指定单号更新类型则取旧的单号.
|
if (row.RowState != DataRowState.Added ||
|
mode == UpdateKeyMode.None) //取旧的单号
|
{
|
if (summary.Columns[keyFieldName] != null)
|
GUID = row[keyFieldName].ToStringEx();
|
|
if (summary.Columns[docNoFieldName] != null)
|
docNo = row[docNoFieldName].ToStringEx();
|
|
return;
|
}
|
|
//新增记录,更新主键的值
|
if (row.RowState == DataRowState.Added)
|
{
|
//注意状态的使用,只有在新增状态下才更新单号
|
if (mode == UpdateKeyMode.OnlyGuid)
|
{
|
if (keyFieldName == "")
|
throw new Exception("没有设定主键,检查模型类的参数定义！");
|
|
GUID = Guid.NewGuid().ToStringEx().Replace("-", "");
|
row[keyFieldName] = GUID;
|
}
|
|
if (mode == UpdateKeyMode.OnlyDocumentNo)
|
{
|
if (docNoFieldName == "")
|
throw new Exception("没有设定单号主键,检查模型类的参数定义！");
|
|
docNo = GetNumber(tran); //调用模板方法获取单据号码，派生类必须重写GetNumber方法
|
row[docNoFieldName] = docNo;
|
}
|
}
|
}
|
}
|
|
/// <summary>
|
/// 更新主表的主键模式
|
/// </summary>
|
public enum UpdateKeyMode
|
{
|
/// <summary>
|
/// 未指定．单据号码由用户手工输入
|
/// </summary>
|
None,
|
|
/// <summary>
|
/// 自动生成流水号
|
/// </summary>
|
OnlyDocumentNo,
|
|
/// <summary>
|
/// 自动生成36位GUID(全球唯一标识)
|
/// </summary>
|
OnlyGuid
|
}
|
}
|
