private static string GetUpdateSqlPartial(object obj, SqlExpressionArgs args, TypeMappingDescription typeCache,
List <string> keyFieldList, List <SqlParameter> parameterList)
{
StringBuilder fieldString = new StringBuilder();
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(',');
}
Dictionary <PropertyMappingDescription, object> propertyList =
GetPropertyValueList(obj, includeFieldList, excludeFieldList);
foreach (var property in propertyList)
{
object parameterValue = property.Value;
if (parameterValue == null)
{
parameterValue = System.DBNull.Value;
}
//在 GetPropertyValueList 方法中已经转成JSON字符串了
//if (property.Key.Json)
//{
// parameterValue = JsonHelper.Serializer(parameterValue);
//}
fieldString.Append("[" + property.Key.Column + "]");
fieldString.Append("=");
string parameterName = String.Format("@{0}", property.Key.Column);
fieldString.Append(parameterName);
fieldString.Append(",");
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);
}
}
fieldString.Remove(fieldString.Length - 1, 1);
return(fieldString.ToString());
}
private static string GetInsertSqlPartial(object obj, SqlExpressionArgs args, TypeMappingDescription typeCache,
List <SqlParameter> parameterList)
{
StringBuilder fieldString = new StringBuilder();
StringBuilder valueString = new StringBuilder();
fieldString.Append("(");
valueString.Append("(");
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(',');
}
Dictionary <PropertyMappingDescription, object> propertyList =
GetPropertyValueList(obj, includeFieldList, excludeFieldList);
foreach (var property in propertyList)
{
if (property.Value == null)
{
continue;
}
fieldString.Append("[" + property.Key.Column + "]");
fieldString.Append(",");
string parameterName = String.Format("@{0}", property.Key.Column);
valueString.Append(parameterName);
valueString.Append(",");
if ((from c in parameterList
where c.ParameterName == parameterName
select c).Count() == 0)
{
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = parameterName;
parameter.Value = property.Value;
parameterList.Add(parameter);
}
}
fieldString.Remove(fieldString.Length - 1, 1);
valueString.Remove(valueString.Length - 1, 1);
fieldString.Append(") VALUES").Append(valueString).Append(")");
return(fieldString.ToString());
}
/// <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 SqlExpression GetSqlExpression(object obj, SqlExpressionType type)
{
SqlExpressionArgs args = new SqlExpressionArgs(type);
return(GetSqlExpression(obj, args));
}