【应用场景】
业务单据通常会在保存操作上配置编码唯一校验器，用于控制单据编号重复的问题，但是在WebApi高并发下，由于校验器的机制问题，使得校验结果具有一定的滞后性，因此仍有可能会产生重复单号的单据。此时，依赖数据库的唯一索引控制单据编号重复，是最高效，最佳的选择。
【案例演示】
采购订单，通过在数据库新建单据编号表，并给单据编号字段设置为主键，依托数据库的主键的数据唯一性检查，进行高并发下的单据编号重复控制。
【实现步骤】
<1>编写服务插件，代码如下。
using Kingdee.BOS;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Core.DynamicForm.PlugIn;
using Kingdee.BOS.Core.DynamicForm.PlugIn.Args;
using Kingdee.BOS.Core.Metadata.FieldElement;
using Kingdee.BOS.Core.Metadata.FormElement;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Util;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Transactions;
namespace Jac.XkDemo.BOS.App.PlugIn
{
/// <summary>
/// 【服务插件】高并发下控制单据编号重复问题(保存)
/// </summary>
[Description("【服务插件】高并发下控制单据编号重复问题(保存)"), HotUpdate]
public class BillNoUniqueSaveServicePlugIn : AbstractOperationServicePlugIn
{
public override void EndOperationTransaction(EndOperationTransactionArgs e)
{
base.EndOperationTransaction(e);
var billNoField = this.BusinessInfo.GetBillNoField() as BillNoField;
if (billNoField == null)
{
return;
}
var formId = this.BusinessInfo.GetForm().Id;
#region 先删
string ids;
if (this.BusinessInfo.GetForm().PkFieldType == EnumPkFieldType.STRING)
{
ids = string.Join(",", e.DataEntitys.Select(o => "'" + Convert.ToString(o[0]) + "'").ToArray());
}
else
{
ids = string.Join(",", e.DataEntitys.Select(o => Convert.ToString(o[0])).ToArray());
}
var sqlGetOldBillNo = string.Format("SELECT {2},{0} FROM {1} WHERE {2} IN ({3})", billNoField.FieldName, this.BusinessInfo.GetEntity(0).TableName, this.BusinessInfo.GetForm().PkFieldName, ids);
DynamicObjectCollection oldBillNoObjs;
using (var trans = new KDTransactionScope(TransactionScopeOption.Suppress))
{
oldBillNoObjs = DBUtils.ExecuteDynamicObject(Context, sqlGetOldBillNo);
}
if (oldBillNoObjs != null && oldBillNoObjs.Count > 0)
{
var dicOld = new Dictionary<string, string>();
foreach (var obj in oldBillNoObjs)
{
var billNo = Convert.ToString(obj[1]);
if (string.IsNullOrWhiteSpace(billNo))
{
continue;
}
dicOld[Convert.ToString(obj[0])] = Convert.ToString(obj[1]);
}
var needDeleteBillNos = new List<string>();
foreach (var dataEntity in e.DataEntitys)
{
var id = Convert.ToString(dataEntity[0]);
var billNo = Convert.ToString(dataEntity[billNoField.PropertyName]);
if (dicOld.ContainsKey(id) && dicOld[id] != billNo)
{
// 单号被修改，旧单号删除
needDeleteBillNos.Add(dicOld[id]);
}
}
if (needDeleteBillNos.Count > 0)
{
var sqlDeleteOldBillNo = string.Format("DELETE FROM T_JAC_BILLNO_UNIQUECHECK WHERE FOBJECTID='{0}' AND FBILLNO IN ({1})", formId, string.Join(",", needDeleteBillNos.Select(o => "'" + o + "'")));
DBUtils.Execute(Context, sqlDeleteOldBillNo);
}
}
#endregion
#region 再增
var sqlTemplate = "INSERT INTO T_JAC_BILLNO_UNIQUECHECK (FOBJECTID,FBILLNO) VALUES (@FOBJECTID,@FBILLNO)";
var sqlObjs = new List<SqlObject>();
foreach (var dataEntity in e.DataEntitys)
{
var billNo = dataEntity[billNoField.PropertyName] as string;
if (!string.IsNullOrWhiteSpace(billNo))
{
var sqlObj = new SqlObject(sqlTemplate, new SqlParam("@FOBJECTID", KDDbType.AnsiString, formId));
sqlObj.Param.Add(new SqlParam("@FBILLNO", KDDbType.String, billNo));
sqlObjs.Add(sqlObj);
}
}
if (sqlObjs.Count > 0)
{
DBUtils.ExecuteBatch(Context, sqlObjs);
}
#endregion
}
}
/// <summary>
/// 【服务插件】高并发下控制单据编号重复问题(删除)
/// </summary>
[Description("【服务插件】高并发下控制单据编号重复问题(删除)"), HotUpdate]
public class BillNoUniqueDeleteServicePlugIn : AbstractOperationServicePlugIn
{
public override void EndOperationTransaction(EndOperationTransactionArgs e)
{
base.EndOperationTransaction(e);
var billNoField = this.BusinessInfo.GetBillNoField() as BillNoField;
if (billNoField == null)
{
return;
}
var formId = this.BusinessInfo.GetForm().Id;
var sqlTemplate = "DELETE FROM T_JAC_BILLNO_UNIQUECHECK WHERE FOBJECTID=@FOBJECTID AND FBILLNO=@FBILLNO";
var sqlObjs = new List<SqlObject>();
foreach (var dataEntity in e.DataEntitys)
{
var billNo = dataEntity[billNoField.PropertyName] as string;
if (!string.IsNullOrWhiteSpace(billNo))
{
var sqlObj = new SqlObject(sqlTemplate, new SqlParam("@FOBJECTID", KDDbType.AnsiString, formId));
sqlObj.Param.Add(new SqlParam("@FBILLNO", KDDbType.String, billNo));
sqlObjs.Add(sqlObj);
}
}
if (sqlObjs.Count > 0)
{
DBUtils.ExecuteBatch(Context, sqlObjs);
}
}
}
}
/*
-- 创建单据编号唯一控制表
IF EXISTS(SELECT * FROM sys.objects WHERE name='T_JAC_BILLNO_UNIQUECHECK' AND type='U')
DROP TABLE T_JAC_BILLNO_UNIQUECHECK
CREATE TABLE T_JAC_BILLNO_UNIQUECHECK
(
FOBJECTID
VARCHAR(50)NOT NULL DEFAULT (''),
FBILLNO
NVARCHAR (50) NOT NULL DEFAULT ('')
)
ALTER TABLE T_JAC_BILLNO_UNIQUECHECK ADD CONSTRAINT [PK_T_JAC_BILLNO_UNIQUECHECK] PRIMARY KEY CLUSTERED (FOBJECTID,FBILLNO) ON [PRIMARY]
*/<2>拷贝插件组件到应用站点的WebSite\Bin目录下，重启IIS。
<3>登录数据中心所在数据库，执行以下SQL，创建单据编号唯一性控制表。
IF EXISTS(SELECT * FROM sys.objects WHERE name='T_JAC_BILLNO_UNIQUECHECK' AND type='U')
DROP TABLE T_JAC_BILLNO_UNIQUECHECK
CREATE TABLE T_JAC_BILLNO_UNIQUECHECK
(
FOBJECTID
VARCHAR(50)NOT NULL DEFAULT (''),
FBILLNO
NVARCHAR (50) NOT NULL DEFAULT ('')
)
ALTER TABLE T_JAC_BILLNO_UNIQUECHECK ADD CONSTRAINT [PK_T_JAC_BILLNO_UNIQUECHECK] PRIMARY KEY CLUSTERED (FOBJECTID,FBILLNO) ON [PRIMARY]<4>BOSIDE扩展采购订单，保存操作和删除操作分别注册服务插件，保存元数据，开发完毕。
【功能验证】
<1>登录业务站点，打开采购订单编辑界面，新增单据，录入一个已存在的单号，此时保存单据会报错。
<2>通过WebApi的方式创建单据，如果单号已存在，也会报错。
---------------------------------------------------------------------------------------------------------
【金蝶云星空BOS二次开发案例演示】https://vip.kingdee.com/article/94751030918525696