/// <summary>
/// 获取字段求和DbCommand 返回Total列
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="filter"></param>
/// <param name="subTableArg">表名称参数.如果TableAttr设置Name.则根据Name格式化</param>
/// <returns></returns>
internal DbCommand GetMaxDbCommand <T>(QueryFilter filter = null, string subTableArg = null)
{
DbCommand dbCommand = CreateDbCommand();
string sqlStr = "Select Max({1}) as Total From {0}";
string selectParams = null;
DtoMapping dtoDbMapping = DtoMappingHelper.GetDtoMapping <T>();
PiMap piMap = DtoMappingHelper.GetPiMapList <T>(filter).FirstOrDefault();
if (piMap == null)
{
throw new Exception("计算字段不能为空");
}
selectParams = piMap.FieldName;
if (filter != null && filter.ItemList.Count > 0)
{
sqlStr += filter.FilterSQLString;
}
if (filter != null && filter.FilterParameters.Count > 0)
{
for (int i = 0; i < filter.FilterParameters.Count; i++)
{
DbParameter dbParameter = dbCommand.CreateParameter();
dbParameter.Direction = ParameterDirection.Input;
dbParameter.ParameterName = filter.FilterParameters[i].ParameterName;
dbParameter.Value = filter.FilterParameters[i].ParameterValue;
dbParameter.DbType = filter.FilterParameters[i].ParameterDbType;
dbCommand.Parameters.Add(dbParameter);
}
}
dbCommand.CommandText = string.Format(sqlStr, dtoDbMapping.GetTableName(subTableArg), selectParams);
return(dbCommand);
}
/// <summary>
/// 添加Orderby
/// </summary>
/// <param name="sort">排序字段</param>
/// <param name="order">排序方向 asc desc</param>
/// <param name="expr">默认排序属性</param>
/// <param name="defaultOrder">默认排序方向</param>
/// <returns></returns>
public IQuery <T> AutoOrderBy(string sort, string order, Expression <Func <T, object> > expr = null, Sorting defaultOrder = Sorting.Asc)
{
if (!string.IsNullOrEmpty(sort))
{
List <PiMap> piMapList = DtoMappingHelper.GetPiMapList <T>();
if (piMapList != null && piMapList.Count > 0)
{
PiMap piMap = piMapList.FirstOrDefault(a => a.PiName.ToLower() == sort.ToLower() && a.IsIgnore == false);
if (piMap != null)
{
this.orderByClauseList.Add(new OrderByClause()
{
FieldName = piMap.FieldName,
Order = order.ToLower() == "desc" ? Sorting.Desc : Sorting.Asc
});
}
else
{
throw new Exception($"无效的排序属性{sort}");
}
}
}
else if (expr != null)
{
if (defaultOrder == Sorting.Asc)
{
OrderBy(expr);
}
else
{
OrderByDesc(expr);
}
}
return(this);
}
/// <summary>
/// 根据字段属性获取字段数据库类型
/// </summary>
/// <param name="piMap">字段属性Map</param>
/// <param name="isLastField">是否为最后一个字段</param>
/// <returns></returns>
private string CreateField(PiMap piMap, bool isLastField = false)
{
string fieldStr = "";
if (piMap.IsIgnore != true)
{
StringBuilder strBuilder = new StringBuilder();
FieldAttribute attr = piMap.FieldAttr;
if (string.IsNullOrEmpty(attr.FieldType))
{
throw new Exception($"自动创建表,需指定字段[{piMap.FieldName}]的FieldDbType属性.");
}
fieldStr = $"[{piMap.FieldName}] {attr.FieldType}";
strBuilder.Append($"[{piMap.FieldName}] ");
strBuilder.Append(attr.FieldType);
string fieldType = attr.FieldType.ToLower();
string[] charFieldTypeList = "char,nchar,varchar,nvarchar".Split(',');
if (charFieldTypeList.Contains(fieldType))
{
if (attr.Length > 0)
{
strBuilder.Append($"({attr.Length})");
}
else
{
strBuilder.Append($"(max)");
}
}
if (attr.IsPk == true)
{
if (attr.FieldType.ToLower() == "int")
{
strBuilder.Append(" identity(1,1) ");
}
strBuilder.Append(" primary key ");
}
strBuilder.Append(attr.Required == true ? " not null " : " null ");
if (!isLastField)
{
strBuilder.Append(",");
}
strBuilder.Append($" --{attr.DisplayText}\r\n");
fieldStr = strBuilder.ToString();
}
return(fieldStr);
}
/// <summary>
/// 根据字段属性获取字段数据库类型
/// </summary>
/// <param name="piMap">字段属性Map</param>
/// <param name="isLastField">是否为最后一个字段</param>
/// <returns></returns>
private string CreateField(PiMap piMap, bool isLastField = false)
{
string fieldStr = "";
if (piMap.IsIgnore != true)
{
StringBuilder strBuilder = new StringBuilder();
FieldAttribute attr = piMap.FieldAttr;
if (string.IsNullOrEmpty(attr.FieldType))
{
throw new Exception($"自动创建表,需指定字段[{piMap.FieldName}]的FieldType属性.");
}
strBuilder.Append($"{piMap.FieldName} ");
string fieldType = GetFieldType(attr.FieldType.ToLower(), attr.Length);
string[] charFieldTypeList = "char,nchar,varchar,nvarchar".Split(',');
if (charFieldTypeList.Contains(fieldType))
{
strBuilder.Append(" text ");
}
else
{
strBuilder.Append($" {fieldType} ");
}
if (attr.IsPk == true)
{
strBuilder.Append(" primary key ");
if (fieldType == "integer" || fieldType == "long")
{
strBuilder.Append(" autoincrement ");
}
}
strBuilder.Append(attr.Required == true ? " not null " : " null ");
if (!isLastField)
{
strBuilder.Append(",");
}
strBuilder.Append($" --{attr.DisplayText}\r\n");
fieldStr = strBuilder.ToString();
}
return(fieldStr);
}
/// <summary>
/// Get Parameter Value
/// </summary>
/// <param name="piMap"></param>
/// <param name="dto"></param>
/// <returns></returns>
private object GetParameterValue(PiMap piMap, object dto)
{
object dbValue = DBNull.Value;
object piValue = piMap.Pi.GetValue(dto);
if (piValue != null)
{
if (piMap.IsEnum)
{ //如果为枚举类型.转换为int
//目前暂不支持 字段类型为枚举支持
dbValue = (int)piValue;
}
else
{
dbValue = piValue;
}
}
return(dbValue);
}
/// <summary>
/// 添加查询条件
/// 参数类型只支持int guid string double float bool datetime
/// </summary>
/// <param name="collection"></param>
/// <param name="operandSettings"></param>
public IQuery <T> IQuery <T>(NameValueCollection collection, Dictionary <string, Operand> operandSettings = null) where T : class, new()
{
IQuery <T> query = IQuery <T>();
List <PiMap> piMapList = DtoMappingHelper.GetPiMapList <T>();
for (int i = 0; i < collection.AllKeys.Length; i++)
{
string queryItemKey = collection.AllKeys[i];
if (!string.IsNullOrEmpty(queryItemKey))
{
string queryItemVal = collection[queryItemKey];
Object itemValue = null;
if (!string.IsNullOrEmpty(queryItemVal))
{
PiMap piMap = piMapList.Where(p => queryItemKey.ToLower() == p.PiName.ToLower() ||
queryItemKey.ToLower() == ($"min{p.PiName}").ToLower() ||
queryItemKey.ToLower() == ($"max{p.PiName}").ToLower() ||
queryItemKey.ToLower() == ($"{p.PiName}s").ToLower()).FirstOrDefault();
if (piMap == null)
{
continue;
}
#region 处理匹配到的属性
bool rangeMin = queryItemKey.ToLower() == ($"min{piMap.PiName}").ToLower();
bool rangeMax = queryItemKey.ToLower() == ($"max{piMap.PiName}").ToLower();
bool isList = queryItemKey.ToLower() == ($"{piMap.PiName}s").ToLower();
Operand operand = Operand.Equal;
if ((operandSettings != null) && operandSettings.ContainsKey(queryItemKey))
{
operand = operandSettings[queryItemKey];
itemValue = queryItemVal;
}
else if (isList)
{
operand = Operand.Contains;
List <string> valueList;
if (queryItemVal.StartsWith("[") && queryItemVal.EndsWith("]"))
{
valueList = JsonSerializer.Deserialize <List <string> >(queryItemVal);
}
else
{
valueList = queryItemVal.Split(',')
.Where(a => !string.IsNullOrEmpty(a)).Select(a => a.Trim()).ToList();
}
if (valueList?.Count > 0)
{
itemValue = valueList;
}
else
{
continue;
}
}
else if (rangeMin)
{
operand = Operand.GreaterThanOrEqual;
if (piMap.PropertyType == typeof(DateTime) || piMap.PropertyType == typeof(DateTime?))
{
DateTime dt;
if (DateTime.TryParse(queryItemVal, out dt))
{
itemValue = dt.Date;
}
else
{
throw new Exception("日期格式错误.");
}
}
else
{
itemValue = queryItemVal;
}
}
else if (rangeMax)
{
if (piMap.PropertyType == typeof(DateTime) || piMap.PropertyType == typeof(DateTime?))
{
operand = Operand.LessThan;
DateTime dt;
if (DateTime.TryParse(queryItemVal, out dt))
{
itemValue = dt.Date.AddDays(1);
}
else
{
throw new Exception("日期格式错误.");
}
}
else
{
operand = Operand.LessThanOrEqual;
itemValue = queryItemVal;
}
}
else if (piMap.PropertyType == typeof(string))
{
operand = Operand.Like;
itemValue = queryItemVal;
}
else
{
itemValue = queryItemVal;
}
var queryExp = ExpressionHelper.CreateLambdaExpression <T>(operand, piMap.PiName, itemValue);
query.And(queryExp);
#endregion
}
}
}
return(query);
}
/// <summary>
/// Convert List To DataTable
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <returns></returns>
private DataTable EntityToDataTable <T>(List <T> list)
{
DataTable dt = new DataTable();
List <FieldModel> fields = GetTableFieldList <T>();
DtoMapping dtoDbMapping = DtoMappingHelper.GetDtoMapping <T>();
string tableName = dtoDbMapping.GetTableName();
List <PiMap> piMaps = DtoMappingHelper.GetPiMapList <T>();
List <PiMap> mappingPis = new List <PiMap>();
for (int i = 0; i < fields.Count; i++)
{
FieldModel field = fields[i];
PiMap piMap = piMaps.FirstOrDefault(a => a.FieldName.ToLower() == field.FieldName.ToLower());
if (piMap == null)
{
throw new Exception($"{dtoDbMapping.EntityType.FullName} 未定义与表 '{tableName}' 列名为 '{field.FieldName}' 映射的属性");
}
mappingPis.Add(piMap);
Type realType = piMap.PropertyType.GenericTypeArguments.Length > 0 ?
piMap.PropertyType.GenericTypeArguments[0] : piMap.PropertyType;
if (realType.IsEnum)
{
realType = typeof(int);
}
dt.Columns.Add(new DataColumn(field.FieldName, realType));
}
foreach (T dto in list)
{
DataRow dr = dt.NewRow();
for (int i = 0; i < mappingPis.Count; i++)
{
PiMap piMap = mappingPis[i];
object value = piMap.Pi.GetValue(dto);
if (piMap == dtoDbMapping.PkMap && !dtoDbMapping.IsAutoIncrementPk)
{ //是否需要生成Guid
if (value == null || (Guid)value == Guid.Empty)
{
value = Guid.NewGuid();
piMap.Pi.SetValue(dto, value);
}
}
if (piMap.IsEnum)
{
if (value != null)
{
value = (int)value;
}
}
dr[i] = value ?? DBNull.Value;
}
dt.Rows.Add(dr);
}
return(dt);
}
