461 lines
18 KiB
C#
461 lines
18 KiB
C#
using System;
|
||
using System.Collections.Generic;
|
||
using System.Data;
|
||
using System.Linq;
|
||
using VolPro.Core.UserManager;
|
||
using VolPro.Entity.DomainModels;
|
||
|
||
namespace VolPro.Core.Generic
|
||
{
|
||
/// <summary>
|
||
/// 通用数据库字段与明细数据校验扩展
|
||
/// </summary>
|
||
public static class GenericDbValidationExtensions
|
||
{
|
||
/// <summary>
|
||
/// 字段校验：必填、长度、类型
|
||
/// </summary>
|
||
public static string ValidateColumns<T>(this T provider,
|
||
Dictionary<string, object> mainData,
|
||
List<TableColumnField> columns)
|
||
where T : GenericDbProviderBase
|
||
{
|
||
foreach (var col in columns)
|
||
{
|
||
string colName = col.ColumnName;
|
||
string displayName = string.IsNullOrEmpty(col.ColumnCnName) ? colName : col.ColumnCnName;
|
||
|
||
mainData.TryGetValue(colName, out object val);
|
||
bool hasValue = val != null && !(val is string s && string.IsNullOrWhiteSpace(s));
|
||
if (col.IsKey==1)
|
||
{
|
||
continue;
|
||
}
|
||
// 必填校验（IsNull 为 0 时字段必填）
|
||
if ((col.IsNull ?? 1) == 0 && !hasValue)
|
||
{
|
||
return $"【{displayName}】不能为空";
|
||
}
|
||
|
||
if (!hasValue)
|
||
{
|
||
continue;
|
||
}
|
||
|
||
string type = (col.ColumnType ?? "").Trim().ToLower();
|
||
|
||
// 最大长度校验（只判断字符串类型）
|
||
if (type == "string" && (col.Maxlength ?? 0) > 0)
|
||
{
|
||
string str = val.ToString();
|
||
if (col.Maxlength.HasValue && str.Length > col.Maxlength.Value)
|
||
{
|
||
return $"【{displayName}】长度不能超过{col.Maxlength}个字符";
|
||
}
|
||
}
|
||
|
||
// 类型匹配校验
|
||
if (!provider.CheckAndConvertValue(mainData, colName, val, type, displayName, out string errorMsg))
|
||
{
|
||
return errorMsg;
|
||
}
|
||
}
|
||
|
||
return null;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 处理字段类型转换与校验
|
||
/// </summary>
|
||
public static bool CheckAndConvertValue<T>(this T provider,
|
||
Dictionary<string, object> mainData,
|
||
string fieldName,
|
||
object val,
|
||
string type,
|
||
string displayName,
|
||
out string error)
|
||
where T : GenericDbProviderBase
|
||
{
|
||
error = null;
|
||
if (string.IsNullOrEmpty(type))
|
||
{
|
||
return true;
|
||
}
|
||
|
||
try
|
||
{
|
||
object converted = val;
|
||
string str = val as string ?? val.ToString();
|
||
|
||
switch (type)
|
||
{
|
||
case "int":
|
||
case "short":
|
||
case "byte":
|
||
if (!(val is int || val is short || val is byte))
|
||
{
|
||
if (!int.TryParse(str, out int iv))
|
||
{
|
||
error = $"【{displayName}】必须是整数";
|
||
return false;
|
||
}
|
||
converted = iv;
|
||
}
|
||
break;
|
||
case "long":
|
||
case "bigint":
|
||
if (!(val is long))
|
||
{
|
||
if (!long.TryParse(str, out long lv))
|
||
{
|
||
error = $"【{displayName}】必须是整数";
|
||
mainData[fieldName] = lv;
|
||
return false;
|
||
}
|
||
converted = lv;
|
||
}
|
||
break;
|
||
case "decimal":
|
||
if (!(val is decimal))
|
||
{
|
||
if (!decimal.TryParse(str, out decimal dv))
|
||
{
|
||
error = $"【{displayName}】必须是数字";
|
||
return false;
|
||
}
|
||
converted = dv;
|
||
}
|
||
break;
|
||
case "float":
|
||
if (!(val is float || val is double))
|
||
{
|
||
if (!double.TryParse(str, out double fv))
|
||
{
|
||
error = $"【{displayName}】必须是数字";
|
||
mainData[fieldName] = fv;
|
||
return false;
|
||
}
|
||
converted = fv;
|
||
}
|
||
break;
|
||
case "datetime":
|
||
case "date":
|
||
case "time":
|
||
if (!(val is DateTime))
|
||
{
|
||
if (!DateTime.TryParse(str, out DateTime dt))
|
||
{
|
||
error = $"【{displayName}】日期格式不正确";
|
||
return false;
|
||
}
|
||
converted = dt;
|
||
}
|
||
break;
|
||
case "bool":
|
||
if (!(val is bool))
|
||
{
|
||
string b = str.ToLower();
|
||
if (b == "1" || b == "true" || b == "是")
|
||
{
|
||
converted = true;
|
||
}
|
||
else if (b == "0" || b == "false" || b == "否")
|
||
{
|
||
converted = false;
|
||
}
|
||
else
|
||
{
|
||
error = $"【{displayName}】必须是布尔值";
|
||
return false;
|
||
}
|
||
}
|
||
break;
|
||
case "guid":
|
||
if (!(val is Guid))
|
||
{
|
||
if (!Guid.TryParse(str, out Guid g))
|
||
{
|
||
error = $"【{displayName}】不是有效的Guid";
|
||
return false;
|
||
}
|
||
converted = g;
|
||
}
|
||
break;
|
||
default:
|
||
break;
|
||
}
|
||
|
||
mainData[fieldName] = converted;
|
||
return true;
|
||
}
|
||
catch
|
||
{
|
||
error = $"【{displayName}】数据格式不正确";
|
||
return false;
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 根据表字段配置获取对应的 DbType，避免 PgSql 等数据库将参数推断为 text 导致类型不匹配
|
||
/// </summary>
|
||
public static DbType? GetDbType(this TableColumnField col)
|
||
{
|
||
if (col?.ColumnType == null) return null;
|
||
string type = col.ColumnType.Trim().ToLower();
|
||
switch (type)
|
||
{
|
||
case "int":
|
||
case "short":
|
||
case "byte":
|
||
return DbType.Int32;
|
||
case "long":
|
||
case "bigint":
|
||
return DbType.Int64;
|
||
case "string":
|
||
return DbType.String;
|
||
case "guid":
|
||
case "uniqueidentifier":
|
||
return DbType.Guid;
|
||
case "datetime":
|
||
case "date":
|
||
case "time":
|
||
return DbType.DateTime;
|
||
case "decimal":
|
||
return DbType.Decimal;
|
||
case "float":
|
||
case "double":
|
||
return DbType.Double;
|
||
case "bool":
|
||
return DbType.Boolean;
|
||
default:
|
||
return null;
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 结合列元数据与运行时值确定参数 DbType（与 SugarParameter / Dapper 一致）
|
||
/// </summary>
|
||
internal static DbType? EffectiveDapperDbType(object value, TableColumnField col)
|
||
{
|
||
if (IsGuidColumn(col) && value is Guid) return DbType.Guid;
|
||
var fromCol = col?.GetDbType();
|
||
if (fromCol != null) return fromCol;
|
||
return InferDbTypeFromClrValue(value);
|
||
}
|
||
|
||
private static DbType? InferDbTypeFromClrValue(object value)
|
||
{
|
||
if (value == null || value is DBNull) return null;
|
||
var tc = Type.GetTypeCode(value.GetType());
|
||
switch (tc)
|
||
{
|
||
case TypeCode.Boolean: return DbType.Boolean;
|
||
case TypeCode.Byte: return DbType.Byte;
|
||
case TypeCode.Int16: return DbType.Int16;
|
||
case TypeCode.Int32: return DbType.Int32;
|
||
case TypeCode.Int64: return DbType.Int64;
|
||
case TypeCode.Single: return DbType.Single;
|
||
case TypeCode.Double: return DbType.Double;
|
||
case TypeCode.Decimal: return DbType.Decimal;
|
||
case TypeCode.DateTime: return DbType.DateTime;
|
||
case TypeCode.String: return DbType.String;
|
||
default:
|
||
return value is Guid ? DbType.Guid : (DbType?)null;
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 列是否为 Guid / uniqueidentifier 语义
|
||
/// </summary>
|
||
internal static bool IsGuidColumn(TableColumnField col)
|
||
{
|
||
if (col == null) return false;
|
||
if (col.GetDbType() == DbType.Guid) return true;
|
||
string t = (col.ColumnType ?? "").Trim().ToLowerInvariant();
|
||
return t == "guid" || t == "uniqueidentifier";
|
||
}
|
||
|
||
/// <summary>
|
||
/// 判断值是否等于0（用于判断主键是否有意义的值）
|
||
/// </summary>
|
||
public static bool IsZero<T>(this T provider, object value)
|
||
where T : GenericDbProviderBase
|
||
{
|
||
if (value == null) return true;
|
||
|
||
switch (Type.GetTypeCode(value.GetType()))
|
||
{
|
||
case TypeCode.Byte:
|
||
case TypeCode.Int16:
|
||
case TypeCode.Int32:
|
||
case TypeCode.Int64:
|
||
case TypeCode.Decimal:
|
||
case TypeCode.Double:
|
||
case TypeCode.Single:
|
||
return Convert.ToDecimal(value) == 0;
|
||
case TypeCode.String:
|
||
if (decimal.TryParse((string)value, out decimal d))
|
||
{
|
||
return d == 0;
|
||
}
|
||
break;
|
||
}
|
||
return false;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Add 时，对所有明细表做字段校验，且忽略外键字段（与主表主键同名字段）的必填要求
|
||
/// </summary>
|
||
public static string ValidateAllDetails<T>(this T provider, SaveModel saveModel, List<TableColumnField> mainTableColumns, TableColumnField mainKeyColumn)
|
||
where T : GenericDbProviderBase
|
||
{
|
||
// 单明细表
|
||
if (saveModel.DetailData != null && saveModel.DetailData.Count > 0)
|
||
{
|
||
string msg = provider.ValidateDetailList(provider.TableInfo.DetailName, saveModel.DetailData, mainKeyColumn);
|
||
if (!string.IsNullOrEmpty(msg)) return msg;
|
||
}
|
||
|
||
// 多明细
|
||
if (saveModel.Details != null && saveModel.Details.Count > 0)
|
||
{
|
||
foreach (var item in saveModel.Details)
|
||
{
|
||
if (item?.Data == null || item.Data.Count == 0) continue;
|
||
string msg = provider.ValidateDetailList(item.Table, item.Data, mainKeyColumn);
|
||
if (!string.IsNullOrEmpty(msg)) return msg;
|
||
}
|
||
}
|
||
|
||
return null;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Add 时，校验明细表的所有数据行（忽略外键必填）
|
||
/// </summary>
|
||
public static string ValidateDetailList<T>(this T provider, string detailTableName, List<Dictionary<string, object>> rows, TableColumnField mainKeyColumn)
|
||
where T : GenericDbProviderBase
|
||
{
|
||
if (string.IsNullOrEmpty(detailTableName) || rows == null || rows.Count == 0) return null;
|
||
|
||
var detailColumns = TableColumnContext.Data
|
||
.Where(x => x.TableName == detailTableName && x.ReferenceField == 0 && x.IsKey != 1)
|
||
.ToList();
|
||
if (detailColumns == null || detailColumns.Count == 0) return null;
|
||
|
||
// 找到外键字段：与主表主键同名
|
||
var foreignCol = detailColumns.FirstOrDefault(c => c.ColumnName.Equals(mainKeyColumn.ColumnName, StringComparison.OrdinalIgnoreCase));
|
||
|
||
List<string> ingroCols = new List<string>();
|
||
if (foreignCol != null)
|
||
{
|
||
ingroCols.Add(foreignCol.ColumnName);
|
||
}
|
||
// 忽略外键必填，把外键字段的 IsNull 视为可空（1），即不参与必填校验
|
||
List<TableColumnField> validateColumns = detailColumns.Where(c => !ingroCols.Contains(c.ColumnName))
|
||
.ToList();
|
||
|
||
for (int i = 0; i < rows.Count; i++)
|
||
{
|
||
var row = rows[i];
|
||
string msg = provider.ValidateColumns(row, validateColumns);
|
||
if (!string.IsNullOrEmpty(msg))
|
||
{
|
||
return "第[{$ts}]行".TranslatorFormat(i + 1) + "," + msg;
|
||
}
|
||
}
|
||
return null;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Update 时，对所有明细表做字段校验（只校验提交的字段，并忽略外键必填）
|
||
/// </summary>
|
||
public static string ValidateAllDetailsForUpdate<T>(this T provider, SaveModel saveModel, TableColumnField mainKeyColumn)
|
||
where T : GenericDbProviderBase
|
||
{
|
||
// 单明细表
|
||
if (saveModel.DetailData != null && saveModel.DetailData.Count > 0)
|
||
{
|
||
string msg = provider.ValidateDetailListForUpdate(provider.TableInfo.DetailName, saveModel.DetailData, mainKeyColumn);
|
||
if (!string.IsNullOrEmpty(msg)) return msg;
|
||
}
|
||
|
||
// 多明细
|
||
if (saveModel.Details != null && saveModel.Details.Count > 0)
|
||
{
|
||
foreach (var item in saveModel.Details)
|
||
{
|
||
if (item?.Data == null || item.Data.Count == 0) continue;
|
||
string msg = provider.ValidateDetailListForUpdate(item.Table, item.Data, mainKeyColumn);
|
||
if (!string.IsNullOrEmpty(msg)) return msg;
|
||
}
|
||
}
|
||
|
||
return null;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Update 时，校验某一张明细表的所有数据行（只校验提交的字段，并忽略外键必填）
|
||
/// </summary>
|
||
public static string ValidateDetailListForUpdate<T>(this T provider, string detailTableName, List<Dictionary<string, object>> rows, TableColumnField mainKeyColumn)
|
||
where T : GenericDbProviderBase
|
||
{
|
||
if (string.IsNullOrEmpty(detailTableName) || rows == null || rows.Count == 0) return null;
|
||
|
||
var detailColumns = TableColumnContext.Data
|
||
.Where(x => x.TableName == detailTableName && x.ReferenceField == 0)
|
||
.ToList();
|
||
if (detailColumns == null || detailColumns.Count == 0) return null;
|
||
|
||
// 找到外键字段：与主表主键同名、同类型
|
||
var foreignCol = detailColumns.FirstOrDefault(c =>
|
||
c.ColumnName.Equals(mainKeyColumn.ColumnName, StringComparison.OrdinalIgnoreCase)
|
||
&& string.Equals(c.ColumnType, mainKeyColumn.ColumnType, StringComparison.OrdinalIgnoreCase));
|
||
|
||
foreach (var row in rows)
|
||
{
|
||
if (row == null) continue;
|
||
|
||
// 只校验提交的字段
|
||
var submitColumns = detailColumns
|
||
.Where(c => row.ContainsKey(c.ColumnName))
|
||
.ToList();
|
||
|
||
if (submitColumns.Count == 0) continue;
|
||
|
||
// 忽略外键必填：将外键列视为可空
|
||
if (foreignCol != null && submitColumns.Any(c => c.ColumnName.Equals(foreignCol.ColumnName, StringComparison.OrdinalIgnoreCase)))
|
||
{
|
||
submitColumns = submitColumns
|
||
.Select(c =>
|
||
{
|
||
if (!c.ColumnName.Equals(foreignCol.ColumnName, StringComparison.OrdinalIgnoreCase))
|
||
{
|
||
return c;
|
||
}
|
||
return new TableColumnField
|
||
{
|
||
ColumnName = c.ColumnName,
|
||
ColumnCnName = c.ColumnCnName,
|
||
ColumnType = c.ColumnType,
|
||
TableName = c.TableName,
|
||
IsDisplay = c.IsDisplay,
|
||
ReferenceField = c.ReferenceField,
|
||
Maxlength = c.Maxlength,
|
||
IsKey = c.IsKey,
|
||
IsNull = 1
|
||
};
|
||
})
|
||
.ToList();
|
||
}
|
||
|
||
string msg = provider.ValidateColumns(row, submitColumns);
|
||
if (!string.IsNullOrEmpty(msg)) return msg;
|
||
}
|
||
|
||
return null;
|
||
}
|
||
}
|
||
}
|
||
|