private static void GetPropertyList(Type objType, List <PropertyMappingDescription> list,
string[] includeFieldList, string[] excludeFieldList)
{
if (objType == null || list == null)
{
return;
}
// Type objType = obj.GetType();
TypeMappingDescription typeCache = TypeMappingCache.Get(objType);
List <PropertyMappingDescription> properties =
(from c in typeCache.PropertyList
where c.CanRead && c.NotMapped == false && c.IsRelation == false
select c).ToList();
List <PropertyMappingDescription> propertyList;
if (includeFieldList.Length > 0)
{
propertyList = (from c in properties
where includeFieldList.Contains(c.Name)
select c).ToList();
}
else if (excludeFieldList.Length > 0)
{
propertyList = (from c in properties
where excludeFieldList.Contains(c.Name) == false
select c).ToList();
}
else
{
propertyList = properties.ToList();
}
foreach (PropertyMappingDescription property in propertyList)
{
if (property.IsPartial)
{
GetPropertyList(property.PropertyInfo.PropertyType,
list, includeFieldList, excludeFieldList);
}
else
{
list.Add(property);
}
}
}
public static List <T> Select <T>(DataSet ds) where T : class
{
if (ds == null)
{
return(null);
}
TypeMappingDescription typeCacheCodon = TypeMappingCache.Get(typeof(T));
if (ds.Tables.Contains(typeCacheCodon.Table) == false)
{
throw new ArgumentOutOfRangeException("数据源里没有指定的表：" + typeCacheCodon.Table);
}
return(Select <T>(ds.Tables[typeCacheCodon.Table]));
}
private static void GetPropertyValueList(object obj, Dictionary <PropertyMappingDescription, object> list,
string[] includeFieldList, string[] excludeFieldList)
{
if (obj == null || list == null)
{
return;
}
Type objType = obj.GetType();
TypeMappingDescription typeCache = TypeMappingCache.Get(objType);
List <PropertyMappingDescription> properties =
(from c in typeCache.PropertyList
where c.CanRead && c.NotMapped == false && c.IsRelation == false
select c).ToList();
List <PropertyMappingDescription> propertyList;
if (includeFieldList.Length > 0)
{
propertyList = (from c in properties
where includeFieldList.Contains(c.Name)
select c).ToList();
}
else if (excludeFieldList.Length > 0)
{
propertyList = (from c in properties
where excludeFieldList.Contains(c.Name) == false
select c).ToList();
}
else
{
propertyList = properties.ToList();
}
foreach (PropertyMappingDescription property in propertyList)
{
//先看有没有同名的 Property 已经被添加过了，如果有，忽略后面同名的
//如站点参数设置，本身有 SiteId，后面每一个 Partial 都有自己的 SiteId
var sameProperty = from c in list.Keys where c.Name == property.Name select c;
if (sameProperty.Count() > 0)
{
continue;
}
object parameterValue = property.PropertyInfo.GetValue(obj, null);
if (property.IsPartial)
{
GetPropertyValueList(parameterValue, list, includeFieldList, excludeFieldList);
}
else
{
if (property.Convert != null)
{
parameterValue = property.Convert.CovertTo(parameterValue);
}
if (property.Json && parameterValue != null && parameterValue != DBNull.Value)
{
parameterValue = JsonHelper.Serializer(parameterValue);
}
list.Add(property, parameterValue);
}
}
}
/// <summary>
/// 即使是 select 语句，也必须提供一个初始化过的obj
/// 主要原因是需要从中取出 sql 的 where 部分
/// </summary>
/// <param name="obj"></param>
/// <param name="args"></param>
/// <returns></returns>
public static SqlExpression GetSqlExpression(object obj, SqlExpressionArgs args)
{
if (obj == null || args == null)
{
throw new ArgumentNullException();
}
Type objType = obj.GetType();
TypeMappingDescription typeCache = TypeMappingCache.Get(objType);
string tableName = typeCache.Table;
if (String.IsNullOrEmpty(args.Table) == false)
{
tableName = args.Table;
}
StringBuilder sql = new StringBuilder();
//用于SELECT 且需要分页时，统计总行数
StringBuilder sqlCount = null;
List <SqlParameter> parameterList = new List <SqlParameter>();
if (args.Type == SqlExpressionType.Insert)
{
sql.Append("INSERT INTO [" + tableName + "]");
sql.Append(" " + GetInsertSqlPartial(obj, args, typeCache, parameterList));
}
else if (args.Type == SqlExpressionType.Select ||
args.Type == SqlExpressionType.Update || args.Type == SqlExpressionType.Delete)
{
List <string> keyFieldList = null;
if (args.GenerateWhere)
{
//KEY可以通过args指定也可以通过KeyAttribute在对象上标记
if (String.IsNullOrEmpty(args.KeyFields) == false)
{
keyFieldList = args.KeyFields.Split(',').ToList();
}
else
{
keyFieldList = new List <string>();
}
if (keyFieldList.Count == 0)
{
var keyProperties = (from c in typeCache.PropertyList
where c.CanRead && c.Key
select c.Name).ToList();
foreach (string item in keyProperties)
{
keyFieldList.Add(item);
}
}
}
if (args.Type == SqlExpressionType.Select)
{
if (args.PagingArgs != null)
{
sqlCount = new StringBuilder();
sqlCount.Append("SELECT Count(*) ");
}
//生成 select 不支持关联属性，分部属性
sql.Append("SELECT ");
string[] includeFieldList = new string[0];
if (String.IsNullOrEmpty(args.IncludeFields) == false)
{
includeFieldList = args.IncludeFields.Split(',');
}
string[] excludeFieldList = new string[0];
if (String.IsNullOrEmpty(args.ExcludeFields) == false)
{
excludeFieldList = args.ExcludeFields.Split(',');
}
List <PropertyMappingDescription> selectPropertyList =
GetPropertyList(objType, includeFieldList, excludeFieldList);
if (selectPropertyList.Count == 0)
{
sql.Append("'DATA' as Column1");
}
else
{
foreach (var item in selectPropertyList)
{
sql.Append("[" + item.Column + "]");
sql.Append(",");
}
sql.Remove(sql.Length - 1, 1);
}
sql.Append(" FROM [" + tableName + "]");
if (sqlCount != null)
{
sqlCount.Append(" FROM [" + tableName + "]");
}
}
else if (args.Type == SqlExpressionType.Update)
{
sql.Append("UPDATE [" + tableName + "] SET");
sql.Append(" " + GetUpdateSqlPartial(obj, args, typeCache, keyFieldList, parameterList));
}
else
{
sql.Append("DELETE FROM [" + tableName + "]");
}
StringBuilder sqlWhere = new StringBuilder();
List <PropertyMappingDescription> whereProperties = null;
if (args.GenerateWhere)
{
whereProperties = (from c in typeCache.PropertyList
where keyFieldList.Contains(c.Name)
select c).ToList();
#region whereProperties
if (whereProperties.Count > 0)
{
sqlWhere.Append(" WHERE ");
foreach (PropertyMappingDescription property in whereProperties)
{
object parameterValue = property.PropertyInfo.GetValue(obj, null);
if (property.Convert != null)
{
parameterValue = property.Convert.CovertTo(parameterValue);
}
if (parameterValue == null)
{
parameterValue = System.DBNull.Value;
}
if (property.Json && parameterValue != null && parameterValue != DBNull.Value)
{
parameterValue = JsonHelper.Serializer(parameterValue);
}
sqlWhere.Append("[" + property.Column + "]");
sqlWhere.Append("=");
string parameterName = String.Format("@{0}", property.Column);
sqlWhere.Append(parameterName);
sqlWhere.Append(" AND ");
if ((from c in parameterList
where c.ParameterName == parameterName
select c).Count() == 0)
{
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = parameterName;
parameter.Value = parameterValue;
parameterList.Add(parameter);
}
}
int andLength = " AND ".Length;
sqlWhere.Remove(sqlWhere.Length - andLength, andLength);
sql.Append(sqlWhere);
if (sqlCount != null)
{
sqlCount.Append(sqlWhere);
}
}
#endregion
}
//添加额外特别指定的 Where 条件
if (args.AttachedWhere != null && args.AttachedWhere.Count > 0)
{
//GenerateWhere 为 true 或 false不能作为判断要不要加 where 的标准
//因为为 true 也有可能不添加
if (whereProperties == null || whereProperties.Count == 0)
{
sqlWhere.Append(" WHERE ");
}
foreach (var attachedWhereItem in args.AttachedWhere)
{
if (attachedWhereItem.Value != null)
{
sqlWhere.Append("[" + attachedWhereItem.Field + "]");
if (attachedWhereItem.Type == AttachedWhereType.Equal)
{
sqlWhere.Append(" = ");
}
else if (attachedWhereItem.Type == AttachedWhereType.Like)
{
sqlWhere.Append(" LIKE ");
}
else
{
Debug.Assert(false, "attachedWhereItem.Type 不支持");
}
string parameterName = String.Format("@{0}", attachedWhereItem.Field);
sqlWhere.Append(parameterName);
sqlWhere.Append(" AND ");
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = parameterName;
if (attachedWhereItem.Type == AttachedWhereType.Equal)
{
parameter.Value = attachedWhereItem.Value;
}
else if (attachedWhereItem.Type == AttachedWhereType.Like)
{
parameter.Value = "%" + attachedWhereItem.Value + "%";
}
parameterList.Add(parameter);
}
else if (attachedWhereItem.ValueArray != null && attachedWhereItem.ValueArray.Length > 0)
{
sqlWhere.Append(" ( ");
for (int i = 0; i < attachedWhereItem.ValueArray.Length; i++)
{
sqlWhere.Append("[" + attachedWhereItem.Field + "]");
if (attachedWhereItem.Type == AttachedWhereType.Equal)
{
sqlWhere.Append(" = ");
}
else if (attachedWhereItem.Type == AttachedWhereType.Like)
{
sqlWhere.Append(" LIKE ");
}
else
{
Debug.Assert(false, "attachedWhereItem.Type 不支持");
}
string parameterName = String.Format("@{0}", attachedWhereItem.Field + i.ToString());
sqlWhere.Append(parameterName);
if (i < attachedWhereItem.ValueArray.Length - 1)
{
sqlWhere.Append(" OR ");
}
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = parameterName;
if (attachedWhereItem.Type == AttachedWhereType.Equal)
{
parameter.Value = attachedWhereItem.ValueArray[i];
}
else if (attachedWhereItem.Type == AttachedWhereType.Like)
{
parameter.Value = "%" + attachedWhereItem.ValueArray[i] + "%";
}
parameterList.Add(parameter);
}
sqlWhere.Append(" ) ");
sqlWhere.Append(" AND ");
}
}
int andLength = " AND ".Length;
sqlWhere.Remove(sqlWhere.Length - andLength, andLength);
sql.Append(sqlWhere);
if (sqlCount != null)
{
sqlCount.Append(sqlWhere);
}
}
if (args.Type == SqlExpressionType.Select)
{
List <PropertyMappingDescription> orderByProperties = (from c in typeCache.PropertyList
where c.CanRead && c.OrderBy != null
select c).ToList();
Debug.Assert(orderByProperties.Count <= 1, "设置了多个 OrderByAttribute");
//分页
if (args.PagingArgs != null)
{
Debug.Assert(orderByProperties.Count == 1, "设置了 PagingArgs 的情况下必须指定一个 OrderByAttribute");
if (orderByProperties.Count == 1)
{
int startRowNum = (args.PagingArgs.Page - 1) * args.PagingArgs.PageSize + 1;
int endRowNum = startRowNum - 1 + args.PagingArgs.PageSize;
PropertyMappingDescription orderByPropery = orderByProperties[0];
sql.Replace("SELECT ",
"SELECT ROW_NUMBER() OVER ( ORDER BY " + orderByPropery.Column
+ (orderByPropery.OrderBy.OrderBy == OrderBy.ASC ? " ASC" : " DESC") + " ) AS rownum, ");
sql.Insert(0, "SELECT * FROM ( ");
sql.Append(" ) AS temp WHERE temp.rownum BETWEEN " + startRowNum + " AND " + endRowNum);
sql.Append(";");
sql.Append(sqlCount);
}
}
else
{
if (orderByProperties.Count > 0)
{
PropertyMappingDescription orderByPropery = orderByProperties[0];
sql.Append(" ORDER BY [");
sql.Append(orderByPropery.Column);
sql.Append("] ");
if (orderByPropery.OrderBy.OrderBy == OrderBy.ASC)
{
sql.Append(" ASC");
}
else
{
sql.Append(" DESC");
}
}
}
}
}
else
{
throw new NotImplementedException("不支持的SqlPairType");
}
SqlExpression sqlExpression = new SqlExpression();
sqlExpression.Sql = sql.ToString();
sqlExpression.ParameterList = parameterList;
return(sqlExpression);
}
public static object Select(DataRow dr, Type type, Dictionary <string, string> fieldPair)
{
if (dr == null)
{
return(null);
}
if (type == null)
{
throw new ArgumentNullException("没有指定type");
}
object resultObj = Activator.CreateInstance(type);
DataTable dt = dr.Table;
if (dt.Columns.Count == 0)
{
return(resultObj);
}
TypeMappingDescription typeCache = TypeMappingCache.Get(type);
Dictionary <string, object> dataDictionary = GetDataDictionary(dr);
//List<PropertyMappingDescription> propertyList = (from c in typeCache.PropertyList
// where c.CanWrite && c.NotMapped == false
// select c).ToList();
//SELECT 的时候不考虑 NotMapped ，因为数据源可能是 JOIN 出来的，并不是对应着一张物理表
//只有在插入数据时需要考虑 NotMapped 的情况
List <PropertyMappingDescription> propertyList = (from c in typeCache.PropertyList
where c.CanWrite
select c).ToList();
foreach (PropertyMappingDescription property in propertyList)
{
string columnName = property.Column;
//fieldPair：额外指定一个列对应关系，优先级高于对象属性名和ColumnAttribute
if (fieldPair != null && fieldPair.Keys.Contains(property.Name))
{
columnName = fieldPair[property.Name];
}
//SELECT 不考虑 NotMapped，有的就选出来
//if (property.NotMapped)
// continue;
if (property.IsRelation)
{
#region IsRelation
DataSet ds = dr.Table.DataSet;
if (ds == null)
{
throw new ArgumentOutOfRangeException("数据源里不存在其它表");
}
if (ds.Tables.Contains(property.Relation.Table) == false)
{
throw new ArgumentOutOfRangeException("数据源里找不到对应的表："
+ property.Relation.Table);
}
DataTable relationTable = ds.Tables[property.Relation.Table];
string filterExpression = property.Relation.FilterExpression;
foreach (Match match in _fieldPatternRegex.Matches(filterExpression))
{
string fieldPattern = match.Value;
string field = _fieldRegex.Match(fieldPattern).Value;
if (dataDictionary.Keys.Contains(field) == false)
{
continue;
//throw new ArgumentOutOfRangeException("数据源里找不到对应的数据列：" + field);
}
object value = dataDictionary[field];
if (value is System.DBNull)
{
value = null;
}
//此处不需要调用Covert，对于数据查询用原生数据就好
//Covert的主要作用是把比如 ImageId 直接转成 Image 对像
//而做表关联查询的时候自然还需要用原来的 ImageId
if (value == null)
{
value = String.Empty;
}
filterExpression = filterExpression.Replace(
fieldPattern, String.Format("'{0}'", value.ToString()));
}
DataRow[] dataRowList = relationTable.Select(filterExpression);
if (dataRowList.Length > 0)
{
Type targetlistType = property.PropertyInfo.PropertyType.GetInterface("IList");
if (targetlistType != null)
{
IList list = (IList)Activator.CreateInstance(property.PropertyInfo.PropertyType);
foreach (DataRow item in dataRowList)
{
object value = Select(item, property.Relation.ElementType);
if (value != null)
{
list.Add(value);
}
}
property.PropertyInfo.SetValue(resultObj, list, null);
}
else
{
if (dataRowList.Length != 1)
{
throw new InvalidOperationException("关联表返回的结果大于1条，而对象非集合类型");
}
object value = Select(dataRowList[0], property.PropertyInfo.PropertyType);
property.PropertyInfo.SetValue(resultObj, value, null);
}
}
#endregion
}
else if (property.IsPartial)
{
object value = Select(dr, property.PropertyInfo.PropertyType, property.Partial.FieldDictionary);
property.PropertyInfo.SetValue(resultObj, value, null);
}
else
{
if (dataDictionary.Keys.Contains(columnName) == false)
{
// Debug.Assert(false, "数据源里找不到对应的数据列");
//throw new ArgumentOutOfRangeException("数据源里找不到对应的数据列："
// + typeCache.Type.FullName + "." + property.Name);
continue;
}
object value = dataDictionary[columnName];
if (value is System.DBNull)
{
value = null;
}
if (property.Convert != null)
{
value = property.Convert.CovertFrom(value);
}
if (property.Json && value != null)
{
value = JsonHelper.Deserialize(value.ToString(), property.PropertyInfo.PropertyType);
}
//如果 value 是 DateTime 类型，虽然 value.ToString() 看不到毫秒
//但是如果直接设置到对象的 DateTime 属性上，这个毫秒会一直带着
//而其它地方如果用 SELECT 语句单独取出字段中的时间，然后用 DateTime.Parse() 去转
//会丢失掉毫秒，造成日期时间的比对因为毫秒的丢失而不一致
property.PropertyInfo.SetValue(resultObj, value, null);
}
}
return(resultObj);
}