private async Task <int> DeleteOneAsync(DataGateKey gkey, JToken jToken)
{
var tableMeta = gkey.MainTable;
IDictionary <string, object> psin = jToken.ToDictionary();
var fields = tableMeta.PrimaryKeys.Select(pk => pk.Name)
.Intersect(psin.Select(kv => kv.Key),
StringComparer.OrdinalIgnoreCase).ToList();
gkey.DataGate.OnRemove(gkey, psin);
var ps = fields.Select(f =>
{
var ff = tableMeta.GetField(f);
var psKey = psin.Keys.First(key => key.Equals(f, StringComparison.OrdinalIgnoreCase));
return(DB.CreateParameter(ff.DbName, psin[psKey]));
}).ToList();
string filter = CreateKeyFilter(tableMeta);
string sql = $"delete from {tableMeta.FixDbName} where {filter}";
int r = await DB.ExecNonQueryAsync(sql, ps.ToArray());
if (r > 1)
{
DB.RollbackTrans();
throw new InvalidOperationException("错误操作，根据ID删除的记录数过多");
}
gkey.DataGate.OnRemoved(gkey, psin);
return(r);
}
public void OnChanged(DataGateKey gkey, IDictionary <string, object> param)
{
foreach (var dg in _dataGates.OfType <ISubmitedDataGate>())
{
dg.OnChanged(gkey, param);
}
}
/// <summary>
/// 构造第i条join子句
/// </summary>
/// <param name="models"></param>
/// <param name="idx"></param>
/// <returns></returns>
private string BuildTableJoins(DataGateKey gkey, int idx)
{
var models = gkey.TableJoins;
var db = GetTempDB(gkey);
string modelRightName = models[idx].Name;
for (var i = 0; i < idx; i++)
{
var modelLeft = models[i].Table;
var modelRight = models[idx].Table;
var join = models[idx - 1].JoinFlag == ">" ? " left join"
: (models[idx - 1].JoinFlag == "=" ? " inner join" : "");
var joinField = modelRight.Fields.FirstOrDefault(f => (f.ForeignKey ?? "").StartsWith((models[i].Alias ?? modelLeft.Name) + "."));
string rightNames = models[idx].Alias == null ? modelRight.FixDbName :
modelRight.FixDbName + " " + models[idx].Alias;
if (joinField == null)
{
joinField = modelLeft.Fields.FirstOrDefault(f => (f.ForeignKey ?? "").StartsWith((models[idx].Alias ?? modelRight.Name) + "."));
if (joinField != null)
{
return($"{join} {rightNames} on" +
$" {AddDotFix(db, joinField.ForeignKey)}={models[i].Alias ?? modelLeft.FixDbName}.{joinField.FixDbName}");
}
}
else
{
return($"{join} {rightNames} on" +
$" {AddDotFix(db, joinField.ForeignKey)}={models[idx].Alias ?? modelRight.FixDbName}.{joinField.FixDbName}");
}
}
throw new ArgumentException($"找不到模型{modelRightName}中的{nameof(FieldMeta.ForeignKey)}定义。");
}
/// <summary>
/// 获取Key对应定义的元数据,返回给客户端
/// </summary>
/// <param name="key">客户端提供的Key</param>
/// <returns></returns>
public IEnumerable <FieldMeta> Metadata(string key)
{
DataGateKey gkey = GetDataGate(key);
//应付Model定义中有>号或=号隔开的多个名称
return(gkey.TableJoins[0].Table.Fields);
}
/// <summary>
/// 如果有Sql语句，直接根据Sql生成分页
/// </summary>
/// <param name="gkey"></param>
/// <param name="parameters"></param>
/// <returns></returns>
private IPager BuildSqlPager(DataGateKey gkey, IDictionary <string, object> parameters)
{
var mainModel = GetMainTable(gkey);
string filter = FormatFilter(gkey.Filter, mainModel);
if (!filter.IsEmpty())
{
filter = " where " + filter;
}
string sql = $"{gkey.Sql}{filter}";
int pageSize = CommOp.ToInt(GetValueRemoveKey(parameters, "pageSize"));
if (pageSize <= 0)
{
pageSize = Consts.DefaultPageSize;
}
DBPagerInfo pager = new DBPagerInfo
{
Query = sql,
KeyId = mainModel.PrimaryKey.FixDbName,
PageIndex = Math.Max(1, CommOp.ToInt(GetValueRemoveKey(parameters, "pageIndex"))) - 1,
PageSize = pageSize,
OrderBy = gkey.OrderBy,
};
return(pager);
}
async Task <IDictionary <string, object> > GetObjectAsync(DataGateKey gkey, IDictionary <string, object> parameters)
{
var result = await GetArrayAsync(gkey, parameters);
if (result is DataTable)
{
DataTable dt = result as DataTable;
if (dt.Rows.Count == 0)
{
return(null);
}
var dr = dt.Rows[0];
Dictionary <string, object> dict = new Dictionary <string, object>();
foreach (DataColumn dc in dt.Columns)
{
dict.Add(dc.ColumnName, dr[dc]);
}
return(dict);
}
else
{
JArray jarr = result as JArray;
if (jarr.Count == 0)
{
return(null);
}
return(jarr[0].ToDictionary());
}
}
/// <summary>
/// 获取分页的数据
/// </summary>
/// <param name="gkey">查询Key名称</param>
/// <param name="parameters">查询参数</param>
/// <returns>{ total: 总数, data:DataTable}</returns>
private async Task <PageResult> GetPageAsync(DataGateKey gkey, object obj)
{
if (!(obj is IDictionary <string, object> parameters))
{
parameters = CommOp.ToStrObjDict(obj);
}
if (gkey.TableJoins.Count > 1)
{
return(await GetMasterDetailPageAsync(gkey, parameters));
}
var tableMeta = gkey.MainTable;
CreateFilterStr(gkey, tableMeta, parameters);
CreateOrderStr(gkey, tableMeta, parameters);
var ps = DB.GetParameter(parameters);
IPager pager = BuildPager(gkey, parameters);
using (var dr = await DB.DBComm.ExecPageReaderAsync(pager, ps.ToArray()))
{
DataTable dt = new DataTable();
dt.Load(dr);
ReNameColumns(tableMeta, dt);
return(new PageResult
{
Total = pager.RecordCount,
Data = dt
});
}
}
public void OnQuery(DataGateKey gkey, IDictionary <string, object> param)
{
foreach (var dg in _dataGates.OfType <IQueryDataGate>())
{
dg.OnQuery(gkey, param);
}
}
//构造不分页的主从表查询语句
private string BuildMasterDetailSql(DataGateKey gkey)
{
var tableMetas = gkey.TableJoins.Select(m => m.Table);
string orderBy = FormatOrderBy(gkey);
if (!orderBy.IsEmpty())
{
orderBy = " order by " + orderBy;
}
string filter = FormatFilter(gkey.Filter, tableMetas.ToArray());
if (!filter.IsEmpty())
{
filter = " where " + filter;
}
string sql = $"{filter}{orderBy}";
if (gkey.Sql.IsEmpty())
{
return($"select {gkey.QueryFieldsTerm} from {gkey.JoinSubTerm}{sql}");
}
else if (sql.IsEmpty())
{
return(gkey.Sql);
}
else
{
return($"select * from ({gkey.Sql})c {sql}");
}
}
private async Task DeleteManyAsync(DataGateKey gkey, JArray removed)
{
foreach (var jToken in removed)
{
await DeleteOneAsync(gkey, jToken);
}
}
//单表不分页sql
private string BuildSql(DataGateKey gkey)
{
if (!gkey.Sql.IsEmpty())
{
return(gkey.Sql);
}
var tableMeta = GetMainTable(gkey);
string filter = FormatFilter(gkey.Filter, tableMeta);
if (!filter.IsEmpty())
{
filter = " where " + filter;
}
string orderby = FormatOrderBy(gkey);
if (!orderby.IsEmpty())
{
orderby = " order by " + orderby;
}
string sql = $"select {gkey.QueryFieldsTerm} from {tableMeta.FixDbName}{filter}{orderby}";
return(sql);
}
public void OnSubmit(DataGateKey gkey, DataSubmitRequest request)
{
foreach (var dg in _dataGates.OfType <IGlobalSubmitDataGate>())
{
dg.OnSubmit(gkey, request);
}
}
private string FormatQueryFields(DataGateKey gkey)
{
return(String.Join(",", gkey.QueryFields.Split(',').Select(f =>
{
return String.Join(".", f.Split('.').Select(s => GetTempDB(gkey).AddFix(s.Trim())));
})));
}
public void OnExport(DataGateKey gkey, DataTable dt)
{
foreach (var dg in _dataGates.OfType <IExportDataGate>())
{
dg.OnExport(gkey, dt);
}
}
/// <summary>
/// 顺序执行删除前的钩子
/// </summary>
/// <param name="gkey"></param>
/// <param name="ps"></param>
public void OnRemove(DataGateKey gkey, IDictionary <string, object> ps)
{
foreach (var dg in _dataGates.OfType <ISubmitDataGate>())
{
dg.OnRemove(gkey, ps);
}
}
//单表不分页sql
// TODO: 处理没有考虑单表的虚拟表情况
private string BuildSql(DataGateKey gkey)
{
var tableMeta = gkey.MainTable;
if (tableMeta == null)
{
return(gkey.Sql);
}
string filter = FormatFilter(gkey.Filter, tableMeta);
if (!filter.IsEmpty())
{
filter = " where " + filter;
}
string orderby = FormatOrderBy(gkey);
if (!orderby.IsEmpty())
{
orderby = " order by " + orderby;
}
string sql = $"{filter }{ orderby}";
if (gkey.Sql.IsEmpty())
{
return($"select {gkey.QueryFieldsTerm} from {tableMeta.FixDbName}{sql}");
}
else if (sql.IsEmpty())
{
return(gkey.Sql);
}
return($"SELECT * FROM ({gkey.Sql}) {tableMeta.DbName} {sql}");
}
//构造分页主从表查询分页对象
private IPager BuildMasterDetailPager(DataGateKey gkey, IDictionary <string, object> parameters)
{
var mainModel = gkey.TableJoins[0].Table;
var tableMetas = gkey.TableJoins.Select(m =>
{
return(m.Table);
});
int pageSize = CommOp.ToInt(GetValueRemoveKey(parameters, "pageSize"));
if (pageSize <= 0)
{
pageSize = Consts.DefaultPageSize;
}
return(new MasterDetailPagerInfo
{
TablesAndJoins = gkey.JoinSubTerm,
Fields = gkey.QueryFieldsTerm,
OrderBy = gkey.OrderBy,
Filter = FormatFilter(gkey.Filter, tableMetas.ToArray()),
KeyId = $"{gkey.TableJoins[0].Alias ?? mainModel.FixDbName}.{mainModel.PrimaryKey.FixDbName}",
PageIndex = Math.Max(1, CommOp.ToInt(GetValueRemoveKey(parameters, "pageIndex"))) - 1,
PageSize = pageSize,
});
}
//单表的分页
private IPager BuildPager(DataGateKey gkey, IDictionary <string, object> parameters)
{
var tableMeta = gkey.MainTable;
string filter = FormatFilter(gkey.Filter, tableMeta);
if (!filter.IsEmpty())
{
filter = " where " + filter;
}
string sql = gkey.Sql;
if (sql.IsEmpty())
{
sql = $"select {gkey.QueryFieldsTerm} from {tableMeta.FixDbName}{filter}";
}
int pageSize = CommOp.ToInt(GetValueRemoveKey(parameters, "pageSize"));
if (pageSize <= 0)
{
pageSize = Consts.DefaultPageSize;
}
DBPagerInfo pager = new DBPagerInfo
{
Query = sql,
KeyId = $"{gkey.TableJoins[0].Alias ?? tableMeta.FixDbName}.{tableMeta.PrimaryKey.FixDbName}",
PageIndex = Math.Max(1, CommOp.ToInt(GetValueRemoveKey(parameters, "pageIndex"))) - 1,
PageSize = pageSize,
OrderBy = gkey.OrderBy,
};
return(pager);
}
/// <summary>
/// 构建DataGateKye, 生成其中的某些默认值
/// </summary>
/// <param name="key"></param>
public void BuildKey(DataGateKey key)
{
if (key.Model.IsEmpty())
{
return;
}
key.TableJoins = GetJoinInfos(key).ToList();
var mainModel = key.TableJoins[0].Table;
//string tableJoins = mainModel.FixDbName;
string tableJoins = key.TableJoins[0].Alias == null ? mainModel.FixDbName :
mainModel.FixDbName + " " + key.TableJoins[0].Alias;
for (var i = 1; i < key.TableJoins.Count; i++)
{
string joins = BuildTableJoins(key, i);
tableJoins += (joins + Environment.NewLine);
}
;
key.JoinSubTerm = tableJoins;
//暂不考虑修改数据时生成表查询字段和语句
if (key.OpType != DataOpType.Save)
{
key.QueryFieldsTerm = BuildQueryFields(key);
}
}
private async Task UpdateManyAsync(DataGateKey gkey, JArray changed)
{
foreach (var jToken in changed)
{
await UpdateAsync(gkey, jToken);
}
}
/// <summary>
/// 分析DataKey中Model属性的字符串，分离出主从各表信息用于
/// 下一步生成表连接子句
/// </summary>
/// <param name="key"></param>
/// <returns></returns>
IEnumerable <JoinInfo> GetJoinInfos(DataGateKey key)
{
if (key.Model.IsEmpty())
{
return(null);
}
key.Model = Regex.Replace(key.Model, "\\s+", " ");
string[] models = key.Model.Replace(">", ">%").Replace("=", "=%").Split('%');
return(models.Select(n =>
{
n = n.Trim();
string joinFlag = null;
string alias = null;
string name = null;
if (n.EndsWith(">") || n.EndsWith("="))
{
joinFlag = n.Last().ToString();
n = n.Substring(0, n.Length - 1);
}
var arr = n.Split(' ');
name = arr[0];
if (arr.Length > 1)
{
alias = arr[1];
}
TableMeta tm = null;
//如果Models.json中没有定义表模型，则找Keys.json中
//的QueryFields属性生成一个表模型并放到模型字典中
if (!_tableMetas.ContainsKey(name))
{
if (key.QueryFields.IsEmpty())
{
throw new Exception($"在解析[Key:{key.Key}]的过程中，没有找到[Model:{name}]的定义：在*Models.json中定义的模型，" +
$"或者{nameof(DataGateKey.QueryFields)}中定义的字段列表");
}
tm = CreateTableMeta(key.Model, new JValue(key.QueryFields));
_tableMetas.Add(tm.Name, tm);
}
else
{
tm = _tableMetas[name];
}
var db = GetTempDB(key);
TranslateModelNames(db, tm);
var joinInfo = new JoinInfo
{
Name = name,
Table = tm,
Alias = alias ??
//当模型名和数据库实际名称不对应时，（是手动配置指定的），则用模型名作查询的别名
(tm.DbName == db.GetDbObjName(tm.Name) ? null : tm.Name),
JoinFlag = joinFlag
};
return joinInfo;
}));
}
public void OnResult(DataGateKey gkey, object result)
{
foreach (var dg in _dataGates.OfType <IQueryDataGate>())
{
dg.OnResult(gkey, result);
}
}
private TableMeta GetMainTable(DataGateKey gkey)
{
if (gkey.TableJoins.IsEmpty())
{
return(null);
}
return(gkey.TableJoins[0].Table);
}
private async Task <IEnumerable <string> > InsertManyAsync(DataGateKey gkey, JArray added)
{
List <string> ids = new List <string>();
foreach (var jToken in added)
{
ids.Add(await InsertOneAsync(gkey, jToken));
}
return(ids);
}
private DBHelper GetTempDB(DataGateKey key)
{
string connName = key.ConnName ?? "Default";
if (!_tempDict.ContainsKey(connName))
{
_tempDict[connName] = DBFactory.CreateDBHelper(connName);
}
return(_tempDict[connName]);
}
public void OnAdd(DataGateKey gkey, IDictionary <string, object> ps)
{
foreach (string key in new string[] { "account", "email", "tel" })
{
string accountKey = GetLUKey(ps, key);
if (!accountKey.IsEmpty())
{
ps[accountKey] = CommOp.ToStr(ps[accountKey]).ToLower();
}
}
}
/// <summary>
/// 通用导出成Excel v0.2.0+
/// </summary>
/// <param name="key"></param>
/// <param name="param"></param>
/// <returns></returns>
public async Task <Stream> GetExcelStreamAsync(string key, Dictionary <string, object> param)
{
DataGateKey gkey = GetDataGate(key);
var result = await QueryForExportAsync(gkey, param);
var defaultGate = Consts.Get <DefaultExportGate>();
DataTable dt = defaultGate.OnExport(gkey, result);
gkey.DataGate.OnExport(gkey, dt);
return(ExcelHelper.ExportByEPPlus(dt));
}
private async Task RecurDelete(DataGateKey gkey, DataSubmitRequest request)
{
//先删子表
foreach (var detail in request.Details)
{
var dkey = GetDataGate(detail.Key);
await RecurDelete(dkey, detail);
}
if (!request.Removed.IsEmpty())
{
await DeleteManyAsync(gkey, request.Removed);
}
}
//在v.0.2.0以后，有可能更新多条，可以带Filter条件更新多条
private async Task <int> UpdateAsync(DataGateKey gkey, JToken jToken)
{
var tableMeta = gkey.MainTable;
IDictionary <string, object> psin = jToken.ToDictionary();
gkey.DataGate.OnChange(gkey, psin);
List <string> fields = tableMeta.Fields.Where(f => !f.IsArray && f.ForeignField.IsEmpty())
.Select(f => f.Name).Intersect(psin.Select(kv => kv.Key),
StringComparer.OrdinalIgnoreCase).ToList();
var ps = fields.Select(f =>
{
var ff = tableMeta.GetField(f);
var psKey = psin.Keys.First(key => key.Equals(f, StringComparison.OrdinalIgnoreCase));
return(DB.CreateParameter(ff.DbName, psin[psKey]));
}).ToArray();
string strFields = String.Join(",", fields.Select(f =>
{
f = f.Trim();
//主键或集合字段不进入更新语句
var ff = tableMeta.GetField(f);
if (ff != null && (ff.IsArray || ff.PrimaryKey))
{
return(null);
}
//外来表字段pass掉
if (ff != null && !ff.ForeignField.IsEmpty())
{
return(null);
}
var p = ps.First(p1 => p1.ParameterName.Equals(ff.DbName, StringComparison.OrdinalIgnoreCase));
return(ff.FixDbName + "=@" + p);
}).Where(f => f != null));
//根据是否带筛选条件决定是根据主键更新单条还是根据条件有可能更新多条
string filter = FormatFilter(gkey.Filter, tableMeta) ?? CreateKeyFilter(tableMeta);
string sql = $"update {tableMeta.FixDbName} set {strFields} where {filter}";
int r = await DB.ExecNonQueryAsync(sql, ps);
if (r > 1)
{
DB.RollbackTrans();
throw new InvalidOperationException("错误操作，根据主键更新的记录数过多");
}
gkey.DataGate.OnChanged(gkey, psin);
return(r);
}
private async Task <JArray> GetMasterDetailArrayAsync(DataGateKey gkey, IDictionary <string, object> parameters)
{
var tableMeta = gkey.TableJoins[0].Table;
CreateFilterStr(gkey, tableMeta, parameters);
CreateOrderStr(gkey, tableMeta, parameters);
var ps = DB.GetParameter(parameters);
string sql = BuildMasterDetailSql(gkey);
DataTable dt = await DB.ExecDataTableAsync(sql, ps.ToArray());
var data = CreateMasterArray(tableMeta, dt);
return(data);
}