实体对象模型与数据库对应实现
主要是解决实体对象模型与数据库之间的一一对应,在界面上新增实体对象模型,增加字段,则同步管理业务实体数据库表结构,主要的思路就是界面上修改了实体模型,同步执行修改数据库表结构的Sql语句(已经运行了一段时间的业务表,需要DBA实现修改数据库再修改实体模型),界面大概如下:
核心代码:
定义抽象类AutoBusinessDbServiceBase,界面增删改实体对象模型之后,同步执行Sql语句修改不同数据库的修改数据库表结构的Sql语句,定义抽象类屏蔽不同数据库之间的语句区别。
public abstract class AutoBusinessDbServiceBase : IAutoBusinessDbService
{
protected IUnitOfWork _unitOfWork;
public AutoBusinessDbServiceBase(IUnitOfWork unitOfWork)
{
_unitOfWork = unitOfWork;
}
public async Task<bool> CreateTable(SpriteObjectDto spriteObjectDto)
{
if (CheckTableExists(spriteObjectDto.Name))
{
throw new SpriteException("数据库表已经存在,请联系管理员!");
}
await DoCreateTable(spriteObjectDto);
return await Task.FromResult(true);
}
/// <summary>
/// 判断数据库表是否存在
/// </summary>
protected abstract bool CheckTableExists(string tableName);
/// <summary>
/// 执行创建表过程
/// </summary>
/// <param name="spriteObjectDto"></param>
/// <returns></returns>
protected abstract Task<bool> DoCreateTable(SpriteObjectDto spriteObjectDto);
public abstract Task<bool> AddObjectProperty(ObjectProperty objectProperty, string tableName);
public abstract Task<bool> ModifyObjectProperty(ObjectProperty objectProperty, string tableName);
public abstract Task<bool> DeleteObjectProperty(string propertyName, string tableName);
}
下面是Mysql数据库的实现,代码比较简单,节约篇幅,不贴代码了,代码地址:https://gitee.com/kuangqifu/sprite/blob/master/03_form/CK.Sprite.Form/CK.Sprite.Form.MySql/Domain/DesignTime/MysqlAutoBusinessDb.cs
运行时JObject编程
Newtonsoft.Json,对于这个组件应该不会陌生,用得比较多的是Json序列化与反序列化,他的核心是围绕JToken来实现的,他提供了对于Json对象的动态编程能力(当然还有其他的组件,但用得广泛的还是这个组件),对于自定义表单的实现,这个就尤其重要了,前端创建对象、编辑对象、查询参数等,都是以Json对象格式存储的,运行时,动态解析Json对象,拼接返回结果并返回给前端使用,都是围绕着动态Json编程实现的。
运行时默认常规方法实现
常规增删改查等Sql方法执行,完全可以内置实现,这里采用Dapper来实现的,开源项目实现了Mysql数据库的实现,参考地址:03_form/CK.Sprite.Form/CK.Sprite.Form.MySql/Repository/MysqlRuntimeRepository.cs · 况其富/sprite - Gitee.com,重点介绍部分方法:
新增业务实体
前端界面根据规则引擎获取用户新增的Json实体对象,最终会调用默认的创建数据库业务数据的方法,方法内部会根据之前文章介绍的SpriteObject对象进行数据过滤,并自动生成不同类型的Id字段值,动态添加新增审计日志,如果是树形结构,还会动态维护PId,Code等字段值,调用完成之后,并返回新创建的Id值,代码如下:
public async Task<JObject> DoDefaultCreateMethodAsync(SpriteObjectDto spriteObjectDto, JObject paramValues, string sqlMethodContent = "")
{
StringBuilder sbInsertFields = new StringBuilder();
StringBuilder sbInsertValues = new StringBuilder();
var newGuidId = Guid.NewGuid();
if (spriteObjectDto.KeyType == EKeyType.Guid)
{
sbInsertFields.Append($"{MysqlConsts.PreMark}Id{MysqlConsts.PostMark},");
sbInsertValues.Append($"'{newGuidId}',");
}
else
{
sbInsertFields.Append($"{MysqlConsts.PreMark}Id{MysqlConsts.PostMark},");
sbInsertValues.Append($"0,");
}
foreach (var paramValue in paramValues)
{
var field = paramValue.Key;
var findProperty = spriteObjectDto.ObjectPropertyDtos.FirstOrDefault(r => r.Name.ToLower() == field.ToLower());
if (findProperty != null)
{
if (findProperty.FieldType != EFieldType.String && findProperty.FieldType != EFieldType.Text)
{
if (string.IsNullOrEmpty(paramValue.Value.ToString()))
{
paramValues[field] = null;
}
}
sbInsertFields.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark},");
sbInsertValues.Append($"@{field},");
}
}
var tempParamValues = paramValues.DeepClone().ToObject<JObject>();
var nowTime = DateTime.Now;
if (spriteObjectDto.IsTree)
{
CreateTree(sbInsertFields, sbInsertValues, spriteObjectDto, tempParamValues);
}
if (spriteObjectDto.CreateAudit)
{
CreateAuditCreate(sbInsertFields, sbInsertValues, nowTime, tempParamValues);
}
if (spriteObjectDto.ModifyAudit)
{
CreateAuditUpdate(sbInsertFields, sbInsertValues, nowTime, tempParamValues);
}
var strInserSql = (string.IsNullOrEmpty(sqlMethodContent) ? SqlDefaultCreate : sqlMethodContent)
.Replace("#TableName#", spriteObjectDto.Name)
.Replace("#Fields#", sbInsertFields.ToString().TrimEnd(','))
.Replace("#Values#", sbInsertValues.ToString().TrimEnd(','));
JObject result = new JObject();
if (spriteObjectDto.KeyType == EKeyType.Guid)
{
await _unitOfWork.Connection.ExecuteAsync(strInserSql, tempParamValues.ToConventionalDotNetObject());
result.Add(new JProperty("result", newGuidId));
}
else
{
var resultId = await _unitOfWork.Connection.QueryFirstAsync<int>(strInserSql + "SELECT LAST_INSERT_ID();", tempParamValues.ToConventionalDotNetObject());
result.Add(new JProperty("result", resultId));
}
return result;
}
其他几种默认实现不单独介绍了,实现比较类似,可以直接阅读源码。另外介绍一下动态Where语句的实现。
Where语句可能会非常的复杂,很多时候直接写Sql语句的Where方法就很麻烦了,如果要让自定义表单自动完成Sql语句的封装,则需要一种不同的数据结构才能实现。动态Where的模型采用树结构实现,称为Sql表达式树,表达式枚举有三种,And、Or、Condition,核心还是根据Sql表达式树生成Where后面的Sql语句,并拼接Dapper执行参数。
模型定义:
public class ExpressSqlModel
{
public ESqlExpressType SqlExpressType { get; set; }
public string Field { get; set; }
public EConditionType ConditionType { get; set; }
public object Value { get; set; }
public List<ExpressSqlModel> Children { get; set; }
}
public class QueryWhereModel
{
/// <summary>
/// 查询字段名称
/// </summary>
public string Field { get; set; }
/// <summary>
/// 等于 = 1,不等于 = 2,Between = 3,In = 4,Like = 5,大于 = 6,大于等于 = 7,小于 = 8,小于等于 = 9,Null = 10,NotNull = 11,NotIn = 12
/// </summary>
public EConditionType ConditionType { get; set; }
/// <summary>
/// **传递集合时,直接传递数组**
/// </summary>
public object Value { get; set; }
}
/// <summary>
/// Sql 表达式树
/// </summary>
public enum ESqlExpressType
{
And = 1,
Or = 2,
Condition = 3
}
表达式核心方法:
public delegate string CreateSqlWhereDelegate(JObject sqlWhereParamValues, ExpressSqlModel expressSqlModel, ref int index);
public class ExpressSqlHelper
{
public static string CreateSqlWhere(ExpressSqlModel expressSqlModel, JObject sqlWhereParamValues, CreateSqlWhereDelegate createSqlWhereDelegate)
{
var sqlIndex = 1;
if (expressSqlModel.SqlExpressType == ESqlExpressType.Condition)
{
return createSqlWhereDelegate(sqlWhereParamValues, expressSqlModel, ref sqlIndex);
}
else
{
return $"({CreateComplexSql(expressSqlModel, sqlWhereParamValues, ref sqlIndex, createSqlWhereDelegate)})";
}
}
private static string CreateComplexSql(ExpressSqlModel expressSqlModel, JObject sqlWhereParamValues,ref int sqlIndex, CreateSqlWhereDelegate createSqlWhereDelegate)
{
string strResutl = "";
string endCondition = "";
if (expressSqlModel.SqlExpressType == ESqlExpressType.And)
{
endCondition = "AND";
}
else
{
endCondition = "OR";
}
int index = 1;
foreach (var childExpress in expressSqlModel.Children)
{
string tempCondition = index == expressSqlModel.Children.Count ? "" : $" {endCondition} ";
if (childExpress.SqlExpressType == ESqlExpressType.Condition)
{
if(childExpress.Value != null)
{
strResutl += $"{createSqlWhereDelegate(sqlWhereParamValues, childExpress, ref sqlIndex)}{ tempCondition }";
}
}
else
{
strResutl += $"({CreateComplexSql(childExpress, sqlWhereParamValues, ref sqlIndex, createSqlWhereDelegate)}){tempCondition}";
}
index++;
}
return strResutl;
}
public static string TestCreateConditionSql(JObject sqlWhereParamValues, ExpressSqlModel expressSqlModel, ref int index)
{
string preMark = "`";
string postMark = "`";
var conditionType = expressSqlModel.ConditionType;
var field = expressSqlModel.Field;
StringBuilder sbSqlWhere = new StringBuilder();
switch (conditionType)
{
case EConditionType.等于:
sbSqlWhere.Append($"{preMark}{field}{postMark}=@SW{index}_{field}");
sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
break;
case EConditionType.Like:
sbSqlWhere.Append($"{preMark}{field}{postMark} LIKE CONCAT('%',@SW{index}_{field},'%')");
sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
break;
case EConditionType.In:
sbSqlWhere.Append($"{preMark}{field}{postMark} IN @SW{index}_{field}");
sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
break;
case EConditionType.Between:
sbSqlWhere.Append($"{preMark}{field}{postMark} BETWEEN @SW{index}_{field}_1 AND @SW{index}_{field}_2");
var inValues = expressSqlModel.Value as ArrayList;
sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}_1", inValues[0]));
sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}_2", inValues[1]));
break;
case EConditionType.大于:
sbSqlWhere.Append($"{preMark}{field}{postMark}>@SW{index}_{field}");
sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
break;
case EConditionType.大于等于:
sbSqlWhere.Append($"{preMark}{field}{postMark}>=@SW{index}_{field}");
sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
break;
case EConditionType.小于:
sbSqlWhere.Append($"{preMark}{field}{postMark}<@SW{index}_{field}");
sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
break;
case EConditionType.小于等于:
sbSqlWhere.Append($"{preMark}{field}{postMark}<=@SW{index}_{field}");
sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
break;
case EConditionType.不等于:
sbSqlWhere.Append($"{preMark}{field}{postMark}<>@SW{index}_{field}");
sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
break;
case EConditionType.Null:
sbSqlWhere.Append($"{preMark}{field}{postMark} IS NULL");
break;
case EConditionType.NotNull:
sbSqlWhere.Append($"{preMark}{field}{postMark} IS NOT NULL");
break;
case EConditionType.NotIn:
sbSqlWhere.Append($"{preMark}{field}{postMark} NOT IN @SW{index}_{field}");
sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
break;
default:
break;
}
index++;
return sbSqlWhere.ToString();
}
public static JsonSerializer CreateCamelCaseJsonSerializer()
{
return new JsonSerializer { ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() };
}
}
运行时特殊方法执行实现
常规Sql方法不能满足所有的需求,对于复杂的语句,提供了自定义的功能,主要是自定义Sql执行,反射执行自定义添加的方法(还可执行自定义Rpc的调用)。代码不一一介绍了,参考:03_form/CK.Sprite.Form/CK.Sprite.Form.Core/Domain/RunTime/RuntimeService.cs · 况其富/sprite - Gitee.com
这篇文章介绍了自定义表单运行时方法的执行设计实现,有些设计思想还是可以拆分出来应用到我们现有的系统中,比如我们要实现动态Sql语句查询,则完全可以实现动态Where部分逻辑,由页面用户选择需要哪些查询字段和查询条件(比如=、!=、IN、Like等),我们可以动态生成Sql where表达式。这部分内容对于自定义表单实现,还是比较重要的,建议可以阅读源码。
对于动态Sql语句的支持,设计还是比较巧妙的,对于想要实现这快功能的朋友可以了解一下。
wike文档地址:文档预览 - Gitee.com
开源地址:况其富/sprite
体验地址:http://47.108.141.193:8031 (首次加载可能有点慢,用的阿里云最差的服务器)
自定义表单文章地址:spritekuang - 博客园
流程引擎文章地址:企业级工作流解决方案 - 随笔分类 - spritekuang - 博客园 (采用WWF开发,已过时,已改用Elsa实现,https://www.cnblogs.com/spritekuang/p/14970992.html )