int IEntityOperator.UpdateValues(SchemaModel.Column keyColumn, SchemaModel.Table table, Dictionary <string, object> values)
{
var keyValue = values.Get(keyColumn.Name);
if (keyValue == null)
{
throw new InvalidOperationException("字典未传入主键");
}
var updateSql = "UPDATE {0} SET {1} WHERE {2}";
var tableName = _sqlBuilder.GetTableName(table);
var sqlParameters = new Dictionary <string, object>();
var setts = new List <string>();
var alias = string.Empty;
foreach (var key in values.Keys)
{
if (key == keyColumn.Name)
{
continue;
}
alias = ParserUtils.GenerateAlias(key);
var set = string.Format("[{0}] = @{1}", key, alias);
sqlParameters.Add(alias, values.Get(key));
setts.Add(set);
}
alias = ParserUtils.GenerateAlias(keyColumn.Name);
var condition = string.Format("[{0}] = @{1}", keyColumn.Name, alias);
sqlParameters.Add(alias, keyValue);
updateSql = string.Format(updateSql, tableName, string.Join(",", setts), condition);
return(_sqlExecutor.ExecuteNonQuery(updateSql, sqlParameters));
}
protected override void BuildSelectSql()
{
var columns = _context.Columns;
var conditions = _context.Conditions;
var joins = _context.Joins;
var fromBuilder = new StringBuilder("FROM ");
var selectBuilder = new StringBuilder("SELECT ");
if (_context.Distinct)
{
selectBuilder.Append(" DISTINCT ");
}
else if (_context.Take > 0)
{
selectBuilder.Append(" TOP " + _context.Take + " ");
}
var whereBuilder = new StringBuilder();
var sortBuilder = new StringBuilder();
var sqlBuilder = new StringBuilder();
if (joins != null && joins.Count > 0)
{
var firstJoin = joins.Values.FirstOrDefault();
var leftColumn = firstJoin.Left;
var leftTable = leftColumn.Table;
fromBuilder.Append(GetTableName(leftTable));
fromBuilder.AppendFormat(" [{0}]", leftTable.Alias);
if (_context.NoLockTables.Contains(leftTable.Name))
{
fromBuilder.Append(" WITH (NOLOCK) ");
}
fromBuilder.Append(GenJoinType(firstJoin.JoinType));
var rightColumn = firstJoin.Right;
var rightTable = rightColumn.Table;
fromBuilder.Append(GetTableName(rightTable));
fromBuilder.AppendFormat(" [{0}]", rightTable.Alias);
if (_context.NoLockTables.Contains(rightTable.Name))
{
fromBuilder.Append(" WITH (NOLOCK) ");
}
fromBuilder.Append(" ON ");
fromBuilder.AppendFormat("[{0}].[{1}]", leftTable.Alias, leftColumn.Name);
fromBuilder.AppendFormat(" = ");
fromBuilder.AppendFormat("[{0}].[{1}]" + Environment.NewLine, rightTable.Alias, rightColumn.Name);
foreach (var join in joins.Values.Skip(1))
{
leftColumn = join.Left;
leftTable = leftColumn.Table;
fromBuilder.Append(GenJoinType(join.JoinType));
rightColumn = join.Right;
rightTable = rightColumn.Table;
fromBuilder.Append(GetTableName(rightTable));
fromBuilder.AppendFormat(" [{0}]", rightTable.Alias);
if (_context.NoLockTables.Contains(rightTable.Name))
{
fromBuilder.Append(" WITH (NOLOCK) ");
}
fromBuilder.Append(" ON ");
fromBuilder.AppendFormat("[{0}].[{1}]", leftTable.Alias, leftColumn.Name);
fromBuilder.AppendFormat(" = ");
fromBuilder.AppendFormat("[{0}].[{1}]" + Environment.NewLine, rightTable.Alias, rightColumn.Name);
}
selectBuilder.Append(FormatSelectString(columns));
if (conditions.Any())
{
whereBuilder.Append(BuildWhere(conditions));
}
if (_context.Skip == -1 || _context.Take == -1)
{
sortBuilder.Append(FormatSortColumns());
}
sqlBuilder.Clear();
sqlBuilder.AppendFormat("{0} {1} {2} {3}", selectBuilder.ToString(), fromBuilder.ToString(), whereBuilder.ToString(), sortBuilder.ToString());
}
else
{
fromBuilder = new StringBuilder("FROM ");
var table = columns.FirstOrDefault().Table;
fromBuilder.Append(GetTableName(table));
tableName = ParserUtils.GenerateAlias(table.Name);
fromBuilder.AppendFormat(" [{0}]", tableName);
if (_context.NoLockTables.Contains(table.Name))
{
fromBuilder.Append(" WITH (NOLOCK) ");
}
selectBuilder.Append(FormatSelectString(columns));
if (conditions.Any())
{
whereBuilder.Append(BuildWhere(conditions));
}
if (_context.Take == -1 || _context.Skip == -1)
{
sortBuilder.Append(FormatSortColumns());
}
sqlBuilder.Clear();
sqlBuilder.AppendFormat("{0} {1} {2} {3}", selectBuilder.ToString(), fromBuilder.ToString(), whereBuilder.ToString(), sortBuilder.ToString());
}
var sql = sqlBuilder.ToString();
if (_context.Pager)
{
sqlBuilder.Clear();
var fields = new List <string>();
foreach (var item in _context.Columns)
{
fields.Add(string.Format("[_indexTable].[{0}]", item.Alias ?? item.MemberInfo.Name));
}
sqlBuilder.AppendFormat("SELECT {0} FROM ({1}) _indexTable where [_indexTable].[#index] BETWEEN {2} AND {3}", string.Join(",", fields), sql, _context.Skip, _context.Take);
sql = sqlBuilder.ToString();
}
_result.CommandText = sql;
}
public override string ParserConverter(Column column)
{
var converter = string.Empty;
if (column.Converters.Any())
{
converter = "{0}";
}
while (column.Converters.Count > 0)
{
var columnConverter = column.Converters.Pop();
var memberInfo = columnConverter.MemberInfo;
var args = columnConverter.Parameters;
var paramName = "@" + ParserUtils.GenerateAlias("param");
switch (memberInfo.MemberType)
{
case MemberTypes.Property:
if (TypeHelper.IsNullableType(memberInfo.DeclaringType) && memberInfo.Name == "Value")
{
continue;
}
if (memberInfo.DeclaringType == ReflectorConsts.DateTimeNullableType || memberInfo.DeclaringType == ReflectorConsts.DateTimeType)
{
switch (memberInfo.Name)
{
case "Date":
converter = string.Format(converter, "CONVERT(DATE,{0},211)");
break;
case "Value":
break;
default:
throw new Exception("不支持");
}
continue;
}
else if (memberInfo.DeclaringType == ReflectorConsts.TimeSpanType)
{
var unit = string.Empty;
switch (memberInfo.Name)
{
case "TotalDays":
unit = "DAY";
break;
case "TotalHours":
unit = "HOUR";
break;
case "TotalMilliseconds":
unit = "MILLISECOND";
break;
case "TotalMinutes":
unit = "MINUTE";
break;
case "TotalSeconds":
unit = "SECOND";
break;
default:
throw new Exception("不支持");
}
converter = FormatConverter(columnConverter.IsInstanceColumn, converter, "DATEDIFF(" + unit + ",{1},{0})", paramName);
_result.Parameters.Add(paramName, args[0]);
continue;
}
throw new Exception("不支持");
case MemberTypes.Method:
if (memberInfo.DeclaringType == ReflectorConsts.StringType)
{
switch (memberInfo.Name)
{
case "Contains":
converter = FormatConverter(columnConverter.IsInstanceColumn, converter, "CHARINDEX({0},{1})>0", paramName);
_result.Parameters.Add(paramName, args[0]);
break;
case "StartsWith":
converter = FormatConverter(columnConverter.IsInstanceColumn, converter, "CHARINDEX({0},{1})=1", paramName);
_result.Parameters.Add(paramName, args[0]);
break;
case "Substring":
if (columnConverter.Parameters.Count == 1)
{
if (columnConverter.IsInstanceColumn)
{
converter = string.Format(converter, "SUBSTRING({0}," + (Convert.ToInt32(columnConverter.Parameters[0]) + 1) + ",LEN({0})+1-" + columnConverter.Parameters[0] + ")");
}
else
{
throw new Exception("不支持");
}
}
else if (columnConverter.Parameters.Count == 2)
{
if (columnConverter.IsInstanceColumn)
{
converter = string.Format(converter, "SUBSTRING({0}," + (Convert.ToInt32(columnConverter.Parameters[0]) + 1) + "," + columnConverter.Parameters[1] + ")");
}
else
{
throw new Exception("不支持");
}
}
else
{
throw new Exception("不支持");
}
break;
default:
throw new Exception("不支持");
}
continue;
}
else if (memberInfo.DeclaringType == ReflectorConsts.DateTimeType || memberInfo.DeclaringType == ReflectorConsts.DateTimeNullableType)
{
switch (memberInfo.Name)
{
case "AddDays":
converter = FormatConverter(columnConverter.IsInstanceColumn, converter, "DATEADD(DAY,{0},{1})", paramName);
_result.Parameters.Add(paramName, args[0]);
break;
case "AddHours":
converter = FormatConverter(columnConverter.IsInstanceColumn, converter, "DATEADD(HOUR,{0},{1})", paramName);
_result.Parameters.Add(paramName, args[0]);
break;
case "AddYears":
converter = FormatConverter(columnConverter.IsInstanceColumn, converter, "DATEADD(YEAR,{0},{1})", paramName);
_result.Parameters.Add(paramName, args[0]);
break;
case "AddMonths":
converter = FormatConverter(columnConverter.IsInstanceColumn, converter, "DATEADD(MONTH,{0},{1})", paramName);
_result.Parameters.Add(paramName, args[0]);
break;
case "AddSeconds":
converter = FormatConverter(columnConverter.IsInstanceColumn, converter, "DATEADD(SECOND,{0},{1})", paramName);
_result.Parameters.Add(paramName, args[0]);
break;
case "AddMilliseconds":
converter = FormatConverter(columnConverter.IsInstanceColumn, converter, "DATEADD(MILLISECOND,{0},{1})", paramName);
_result.Parameters.Add(paramName, args[0]);
break;
case "AddMinutes":
converter = FormatConverter(columnConverter.IsInstanceColumn, converter, "DATEADD(MINUTE,{0},{1})", paramName);
_result.Parameters.Add(paramName, args[0]);
break;
default:
throw new Exception("不支持");
}
continue;
}
else if (memberInfo.DeclaringType == ReflectorConsts.EnumerableType)
{
switch (memberInfo.Name)
{
case "Contains":
if (columnConverter.IsInstanceColumn)
{
throw new Exception("不支持");
}
converter = string.Format(converter, "{0} in (" + string.Join(",", (IEnumerable <int>)args[0]) + ")");
break;
default:
throw new Exception("不支持");
}
continue;
}
else
{
throw new Exception("不支持");
}
default:
throw new Exception();
}
}
return(converter);
}
private void BuildPageSql()
{
var columns = _context.Columns;
var conditions = _context.Conditions;
var joins = _context.Joins;
var builder = new StringBuilder("FROM ");
var builder2 = new StringBuilder("SELECT ");
if (_context.Distinct)
{
builder2.Append("DISTINCT ");
}
var builder3 = new StringBuilder();
var builder4 = new StringBuilder();
var builder5 = new StringBuilder();
if ((joins != null) && (joins.Count > 0))
{
builder.Append(BuildJoinSql());
var str2 = @"{str2},{" + @"ROW_NUMBER() OVER({base.FormatSortColumns()}) #index" + "}";
builder2.AppendLine(str2);
if (conditions.Any())
{
builder3.Append(BuildWhere(conditions));
}
builder5.Clear();
object[] args = { builder2.ToString(), builder.ToString(), builder3.ToString(), builder4.ToString() };
builder5.AppendFormat("{0} {1} {2} {3}", args);
}
else
{
builder = new StringBuilder("FROM ");
var leftTable = columns.FirstOrDefault().Table;
builder.Append(GetTableName(leftTable));
tableName = ParserUtils.GenerateAlias(leftTable.Name);
builder.AppendFormat(" [{0}]", tableName);
if (_context.NoLockTables.Contains(leftTable.Name))
{
builder.Append(" WITH (NOLOCK) ");
}
var str3 = FormatSelectString(columns);
if (!_context.Distinct)
{
str3 = @"{str3},{" + @"ROW_NUMBER() OVER({base.FormatSortColumns()}) #index" + @"}";
}
builder2.Append(str3);
if (conditions.Any())
{
builder3.Append(BuildWhere(conditions));
}
builder5.Clear();
object[] objArray2 = { builder2.ToString(), builder.ToString(), builder3.ToString(), builder4.ToString() };
builder5.AppendFormat("{0} {1} {2} {3}", objArray2);
}
var str = builder5.ToString();
builder5.Clear();
builder2.Clear();
builder3.Clear();
builder.Clear();
if (_context.Distinct)
{
tableName = ParserUtils.GenerateAlias("table");
builder.AppendFormat("FROM ({0}) {1}", str, tableName);
builder2.Append("SELECT ");
var values = new List <string>();
foreach (var column in columns)
{
values.Add(@"[{base.tableName}].[{column.Alias ?? column.MemberInfo.Name}]");
}
values.Add(@"ROW_NUMBER() OVER({base.FormatSortColumns()}) #index");
builder2.Append(string.Join(",", values));
builder5.AppendFormat("{0} {1}", builder2, builder);
str = builder5.ToString();
builder5.Clear();
builder.Clear();
}
if (_context.Pager)
{
builder.AppendFormat("FROM ({0}) [_indexTable]", str);
var list4 = new List <string>();
foreach (var column2 in _context.Columns)
{
list4.Add(@"[_indexTable].[{column2.Alias ?? column2.MemberInfo.Name}]");
}
builder5.Append("SELECT ");
object[] objArray3 = { string.Join(",", list4), str, _context.Skip, _context.Take };
builder5.AppendFormat("{0} FROM ({1}) _indexTable where [_indexTable].[#index] BETWEEN {2} AND {3}",
objArray3);
str = builder5.ToString();
}
_result.CommandText = str;
}
public override string ParseConverter(Column column)
{
var format = string.Empty;
if (column.Converters.Any())
{
format = "{0}";
}
while (column.Converters.Count > 0)
{
string name;
var converter = column.Converters.Pop();
var memberInfo = converter.MemberInfo;
var parameters = converter.Parameters;
var param = "@" + ParserUtils.GenerateAlias("param");
var memberType = memberInfo.MemberType;
if (memberType == MemberTypes.Method)
{
goto Label_01E3;
}
if (memberType != MemberTypes.Property)
{
throw new Exception();
}
if (TypeHelper.IsNullableType(memberInfo.DeclaringType) && (memberInfo.Name == "Value"))
{
continue;
}
if ((memberInfo.DeclaringType == ReflectorConsts.DateTimeNullableType) ||
(memberInfo.DeclaringType == ReflectorConsts.DateTimeType))
{
name = memberInfo.Name;
if (name != "Date")
{
if (name != "Value")
{
throw new Exception("不支持");
}
}
else
{
format = string.Format(format, "CONVERT(DATE,{0},211)");
}
continue;
}
if (!(memberInfo.DeclaringType == ReflectorConsts.TimeSpanType))
{
throw new Exception("不支持");
}
var str4 = string.Empty;
name = memberInfo.Name;
if (name != "TotalDays")
{
if (name != "TotalHours")
{
if (name == "TotalMilliseconds")
{
goto Label_0173;
}
if (name == "TotalMinutes")
{
goto Label_017C;
}
if (name != "TotalSeconds")
{
throw new Exception("不支持");
}
goto Label_0185;
}
}
else
{
str4 = "DAY";
format = FormatConverter(converter.IsInstanceColumn, format, "DATEDIFF(" + str4 + ",{1},{0})", param);
_result.Parameters.Add(param, parameters[0]);
continue;
}
str4 = "HOUR";
format = FormatConverter(converter.IsInstanceColumn, format, "DATEDIFF(" + str4 + ",{1},{0})", param);
_result.Parameters.Add(param, parameters[0]);
continue;
Label_0173:
str4 = "MILLISECOND";
format = FormatConverter(converter.IsInstanceColumn, format, "DATEDIFF(" + str4 + ",{1},{0})", param);
_result.Parameters.Add(param, parameters[0]);
continue;
Label_017C:
str4 = "MINUTE";
format = FormatConverter(converter.IsInstanceColumn, format, "DATEDIFF(" + str4 + ",{1},{0})", param);
_result.Parameters.Add(param, parameters[0]);
continue;
Label_0185:
str4 = "SECOND";
format = FormatConverter(converter.IsInstanceColumn, format, "DATEDIFF(" + str4 + ",{1},{0})", param);
_result.Parameters.Add(param, parameters[0]);
continue;
Label_01E3:
if (!(memberInfo.DeclaringType == ReflectorConsts.StringType))
{
goto Label_039F;
}
name = memberInfo.Name;
if (name != "Contains")
{
if (name != "StartsWith")
{
if (name != "Substring")
{
throw new Exception("不支持");
}
goto Label_0295;
}
}
else
{
format = FormatConverter(converter.IsInstanceColumn, format, "CHARINDEX({0},{1})>0", param);
_result.Parameters.Add(param, parameters[0]);
continue;
}
format = FormatConverter(converter.IsInstanceColumn, format, "CHARINDEX({0},{1})=1", param);
_result.Parameters.Add(param, parameters[0]);
continue;
Label_0295:
if (converter.Parameters.Count == 1)
{
if (!converter.IsInstanceColumn)
{
throw new Exception("不支持");
}
format = string.Format(format,
string.Concat("SUBSTRING({0},", Convert.ToInt32(converter.Parameters[0]) + 1, ",LEN({0})+1-",
converter.Parameters[0], ")"));
continue;
}
if (converter.Parameters.Count != 2)
{
throw new Exception("不支持");
}
if (converter.IsInstanceColumn)
{
format = string.Format(format,
string.Concat("SUBSTRING({0},", Convert.ToInt32(converter.Parameters[0]) + 1, ",",
converter.Parameters[1], ")"));
continue;
}
throw new Exception("不支持");
Label_039F:
if (!(memberInfo.DeclaringType == ReflectorConsts.DateTimeType) &&
!(memberInfo.DeclaringType == ReflectorConsts.DateTimeNullableType))
{
goto Label_064A;
}
name = memberInfo.Name;
switch (name)
{
case "AddHours":
format = FormatConverter(converter.IsInstanceColumn, format, "DATEADD(HOUR,{0},{1})", param);
_result.Parameters.Add(param, parameters[0]);
continue;
break;
case "AddDays":
throw new Exception("不支持");
break;
case "AddMonths":
format = FormatConverter(converter.IsInstanceColumn, format, "DATEADD(MONTH,{0},{1})", param);
_result.Parameters.Add(param, parameters[0]);
continue;
break;
case "AddMinutes":
format = FormatConverter(converter.IsInstanceColumn, format, "DATEADD(MINUTE,{0},{1})", param);
_result.Parameters.Add(param, parameters[0]);
continue;
break;
case "AddYears":
format = FormatConverter(converter.IsInstanceColumn, format, "DATEADD(YEAR,{0},{1})", param);
_result.Parameters.Add(param, parameters[0]);
continue;
break;
case "AddMilliseconds":
format = FormatConverter(converter.IsInstanceColumn, format, "DATEADD(MILLISECOND,{0},{1})",
param);
_result.Parameters.Add(param, parameters[0]);
continue;
break;
case "AddSeconds":
format = FormatConverter(converter.IsInstanceColumn, format, "DATEADD(SECOND,{0},{1})", param);
_result.Parameters.Add(param, parameters[0]);
continue;
break;
default:
throw new Exception("不支持");
}
format = FormatConverter(converter.IsInstanceColumn, format, "DATEADD(DAY,{0},{1})", param);
_result.Parameters.Add(param, parameters[0]);
continue;
format = FormatConverter(converter.IsInstanceColumn, format, "DATEADD(HOUR,{0},{1})", param);
_result.Parameters.Add(param, parameters[0]);
continue;
format = FormatConverter(converter.IsInstanceColumn, format, "DATEADD(YEAR,{0},{1})", param);
_result.Parameters.Add(param, parameters[0]);
continue;
format = FormatConverter(converter.IsInstanceColumn, format, "DATEADD(MONTH,{0},{1})", param);
_result.Parameters.Add(param, parameters[0]);
continue;
format = FormatConverter(converter.IsInstanceColumn, format, "DATEADD(SECOND,{0},{1})", param);
_result.Parameters.Add(param, parameters[0]);
continue;
format = FormatConverter(converter.IsInstanceColumn, format, "DATEADD(MILLISECOND,{0},{1})", param);
_result.Parameters.Add(param, parameters[0]);
continue;
format = FormatConverter(converter.IsInstanceColumn, format, "DATEADD(MINUTE,{0},{1})", param);
_result.Parameters.Add(param, parameters[0]);
continue;
throw new Exception("不支持");
Label_064A:
if (memberInfo.DeclaringType.IsGenericType)
{
if (memberInfo.Name != "Contains")
{
throw new Exception("不支持");
}
if (converter.IsInstanceColumn)
{
throw new Exception("不支持");
}
var obj2 = parameters[0];
var declaringType = memberInfo.DeclaringType;
if (!declaringType.IsGenericType)
{
throw new Exception();
}
var collectionObject = (IEnumerable)obj2;
var processor1 =
new EnumerableContainsMethodProcessor(declaringType.GetGenericArguments().Last(),
collectionObject, format);
processor1.Process();
format = processor1.Result.ToString();
}
else
{
if (memberInfo.DeclaringType == ReflectorConsts.EnumerableType)
{
if (memberInfo.Name != "Contains")
{
throw new Exception("不支持");
}
if (converter.IsInstanceColumn)
{
throw new Exception("不支持");
}
var obj3 = parameters[0];
var type = obj3.GetType();
Type elementType = null;
var enumerable2 = (IEnumerable)obj3;
if (type.IsArray)
{
var array1 = (Array)obj3;
elementType = type.GetElementType();
}
else
{
if (!type.IsGenericType)
{
throw new Exception();
}
elementType = type.GetGenericArguments().Last();
}
var processor2 = new EnumerableContainsMethodProcessor(
elementType, enumerable2, format);
processor2.Process();
format = processor2.Result.ToString();
}
else
{
if (!(memberInfo.DeclaringType == ReflectorConsts.QueryableType))
{
throw new Exception("不支持");
}
if (memberInfo.Name != "Contains")
{
throw new Exception("暂时对延迟加载支持不好");
}
if (converter.IsInstanceColumn)
{
throw new Exception("不支持");
}
var list = (IQueryable)parameters[0];
var type4 = list.GetType();
if (!type4.IsGenericType)
{
throw new Exception();
}
var type5 = type4.GetGenericArguments().Last();
var processor3 = new QueryableContainsMethodProcessor(list, type5,
format);
processor3.Process();
format = processor3.Result.ToString();
}
}
}
return(format);
}
protected override void BuildSelectSql()
{
if (_context.Pager)
{
BuildPageSql();
}
else
{
var columns = _context.Columns;
var conditions = _context.Conditions;
var joins = _context.Joins;
var builder = new StringBuilder("FROM ");
builder.AppendLine();
var builder2 = new StringBuilder("SELECT ");
builder2.AppendLine();
if (_context.Distinct)
{
builder2.Append(" DISTINCT ");
}
else if (_context.Take > 0)
{
builder2.Append(" TOP " + _context.Take + " ");
}
var builder3 = new StringBuilder();
var builder4 = new StringBuilder();
var builder5 = new StringBuilder();
if ((joins != null) && (joins.Count > 0))
{
builder.Append(BuildJoinSql());
builder2.Append(FormatSelectString(columns));
if (conditions.Any())
{
builder3.Append(BuildWhere(conditions));
}
if ((_context.Skip == -1) || (_context.Take == -1))
{
builder4.Append(FormatSortColumns());
}
builder5.Clear();
object[] args = { builder2.ToString(), builder.ToString(), builder3.ToString(), builder4.ToString() };
builder5.AppendFormat("{0} {1} {2} {3}", args);
}
else
{
builder = new StringBuilder("FROM ");
var leftTable = columns.FirstOrDefault().Table;
builder.Append(GetTableName(leftTable));
tableName = ParserUtils.GenerateAlias(leftTable.Name);
builder.AppendFormat(" [{0}]", tableName);
if (_context.NoLockTables.Contains(leftTable.Name))
{
builder.Append(" WITH (NOLOCK) ");
}
builder2.Append(FormatSelectString(columns));
if (conditions.Any())
{
builder3.Append(BuildWhere(conditions));
}
if ((_context.Take == -1) || (_context.Skip == -1))
{
builder4.Append(FormatSortColumns());
}
builder5.Clear();
object[] objArray2 =
{
builder2.ToString(), builder.ToString(), builder3.ToString(),
builder4.ToString()
};
builder5.AppendFormat("{0} {1} {2} {3}", objArray2);
}
var str = builder5.ToString();
_result.CommandText = str;
}
}
public override string ParserConverter(Column column)
{
var converter = string.Empty;
if (column.Converters.Any())
{
converter = "{0}";
}
while (column.Converters.Count > 0)
{
var columnConverter = column.Converters.Pop();
var memberInfo = columnConverter.MemberInfo;
var args = columnConverter.Parameters;
var paramName = "@" + ParserUtils.GenerateAlias("param");
switch (memberInfo.MemberType)
{
case MemberTypes.Property:
if (TypeHelper.IsNullableType(memberInfo.DeclaringType) && memberInfo.Name == "Value")
{
continue;
}
if (memberInfo.DeclaringType == ReflectorConsts.DateTimeNullableType || memberInfo.DeclaringType == ReflectorConsts.DateTimeType)
{
converter = string.Format(converter, "DATE({0})");
continue;
}
else if (memberInfo.DeclaringType == ReflectorConsts.TimeSpanType)
{
var unit = 1;
switch (memberInfo.Name)
{
case "TotalDays":
unit = 1;
break;
case "TotalHours":
unit = 12;
break;
case "TotalMilliseconds":
unit = 12 * 60 * 60 * 1000;
break;
case "TotalMinutes":
unit = 12 * 60;
break;
case "TotalSeconds":
unit = 12 * 60 * 60;
break;
default:
throw new Exception("不支持");
}
converter = FormatConverter(columnConverter.IsInstanceColumn, converter, "((JULIANDAY({0}) - JULIANDAY({1}))*" + unit + ")", paramName);
//converter = FormatConverter(columnConverter.IsInstanceColumn, converter, "DATEDIFF(" + unit + ",{1},{0})", paramName);
_result.Parameters.Add(paramName, args[0]);
continue;
}
throw new Exception("不支持");
case MemberTypes.Method:
if (memberInfo.DeclaringType == ReflectorConsts.StringType)
{
switch (memberInfo.Name)
{
case "Contains":
converter = FormatConverter(columnConverter.IsInstanceColumn, converter, "CHARINDEX({0},{1})>0", paramName);
_result.Parameters.Add(paramName, args[0]);
break;
case "StartsWith":
converter = FormatConverter(columnConverter.IsInstanceColumn, converter, "CHARINDEX({0},{1})=1", paramName);
_result.Parameters.Add(paramName, args[0]);
break;
case "Substring":
if (columnConverter.Parameters.Count == 1)
{
if (columnConverter.IsInstanceColumn)
{
converter = string.Format(converter, "SUBSTR({0}," + (Convert.ToInt32(columnConverter.Parameters[0]) + 1) + ")");
}
else
{
throw new Exception("不支持");
}
}
else if (columnConverter.Parameters.Count == 2)
{
if (columnConverter.IsInstanceColumn)
{
converter = string.Format(converter, "SUBSTR({0}," + (Convert.ToInt32(columnConverter.Parameters[0]) + 1) + "," + columnConverter.Parameters[1] + ")");
}
else
{
throw new Exception("不支持");
}
}
else
{
throw new Exception("不支持");
}
break;
default:
throw new Exception("不支持");
}
continue;
}
else if (memberInfo.DeclaringType == ReflectorConsts.DateTimeType || memberInfo.DeclaringType == ReflectorConsts.DateTimeNullableType)
{
var num = args[0].ToString();
switch (memberInfo.Name)
{
case "AddDays":
converter = FormatDateConverter(columnConverter, converter, "DAYS", num);
break;
case "AddHours":
converter = FormatDateConverter(columnConverter, converter, "HOURS", num);
break;
case "AddYears":
converter = FormatDateConverter(columnConverter, converter, "YEARS", num);
break;
case "AddMonths":
converter = FormatDateConverter(columnConverter, converter, "MONTHS", num);
break;
case "AddSeconds":
converter = FormatDateConverter(columnConverter, converter, "SECONDS", num);
break;
case "AddMilliseconds":
converter = FormatDateConverter(columnConverter, converter, string.Format("0.{0} SECONDS", num), string.Empty);
break;
case "AddMinutes":
converter = FormatDateConverter(columnConverter, converter, "MINUTES", num);
break;
default:
throw new Exception("不支持");
}
continue;
}
else if (memberInfo.DeclaringType == ReflectorConsts.EnumerableType)
{
switch (memberInfo.Name)
{
case "Contains":
if (columnConverter.IsInstanceColumn)
{
throw new Exception("不支持");
}
converter = string.Format(converter, "{0} in (" + string.Join(",", (IEnumerable <int>)args[0]) + ")");
break;
default:
throw new Exception("不支持");
}
continue;
}
else
{
throw new Exception("不支持");
}
default:
throw new Exception();
}
}
return(converter);
}
protected override void BuildSelectSql()
{
if (_context.NoLockTables.Any())
{
throw new Exception("暂不支持NOLock");
}
var columns = _context.Columns;
var conditions = _context.Conditions;
var joins = _context.Joins;
var fromBuilder = new StringBuilder("FROM ");
var selectBuilder = new StringBuilder("SELECT ");
if (_context.Distinct)
{
selectBuilder.Append(" DISTINCT ");
}
var whereBuilder = new StringBuilder();
var sortBuilder = new StringBuilder();
var sqlBuilder = new StringBuilder();
if (joins != null && joins.Count > 0)
{
var firstJoin = joins.Values.FirstOrDefault();
var leftColumn = firstJoin.Left;
var leftTable = leftColumn.Table;
fromBuilder.Append(GetTableName(leftTable));
fromBuilder.AppendFormat(" [{0}]", leftTable.Alias);
if (_context.NoLockTables.Contains(leftTable.Name))
{
fromBuilder.Append(" WITH (NOLOCK) ");
}
fromBuilder.Append(GenJoinType(firstJoin.JoinType));
var rightColumn = firstJoin.Right;
var rightTable = rightColumn.Table;
fromBuilder.Append(GetTableName(rightTable));
fromBuilder.AppendFormat(" [{0}]", rightTable.Alias);
if (_context.NoLockTables.Contains(rightTable.Name))
{
fromBuilder.Append(" WITH (NOLOCK) ");
}
fromBuilder.Append(" ON ");
fromBuilder.AppendFormat("[{0}].[{1}]", leftTable.Alias, leftColumn.Name);
fromBuilder.AppendFormat(" = ");
fromBuilder.AppendFormat("[{0}].[{1}]" + Environment.NewLine, rightTable.Alias, rightColumn.Name);
foreach (var join in joins.Values.Skip(1))
{
leftColumn = join.Left;
leftTable = leftColumn.Table;
fromBuilder.Append(GenJoinType(join.JoinType));
rightColumn = join.Right;
rightTable = rightColumn.Table;
fromBuilder.Append(GetTableName(rightTable));
fromBuilder.AppendFormat(" [{0}]", rightTable.Alias);
if (_context.NoLockTables.Contains(rightTable.Name))
{
fromBuilder.Append(" WITH (NOLOCK) ");
}
fromBuilder.Append(" ON ");
fromBuilder.AppendFormat("[{0}].[{1}]", leftTable.Alias, leftColumn.Name);
fromBuilder.AppendFormat(" = ");
fromBuilder.AppendFormat("[{0}].[{1}]" + Environment.NewLine, rightTable.Alias, rightColumn.Name);
}
selectBuilder.Append(FormatSelectString(columns));
if (conditions.Any())
{
whereBuilder.Append(BuildWhere(conditions));
}
if (_context.Skip == -1 || _context.Take == -1)
{
sortBuilder.Append(FormatSortColumns());
}
sqlBuilder.Clear();
sqlBuilder.AppendFormat("{0} {1} {2} {3}", selectBuilder.ToString(), fromBuilder.ToString(), whereBuilder.ToString(), sortBuilder.ToString());
}
else
{
fromBuilder = new StringBuilder("FROM ");
var table = columns.FirstOrDefault().Table;
fromBuilder.Append(GetTableName(table));
tableName = ParserUtils.GenerateAlias(table.Name);
fromBuilder.AppendFormat(" [{0}]", tableName);
if (_context.NoLockTables.Contains(table.Name))
{
fromBuilder.Append(" WITH (NOLOCK) ");
}
selectBuilder.Append(FormatSelectString(columns));
if (conditions.Any())
{
whereBuilder.Append(BuildWhere(conditions));
}
if (_context.Take == -1 || _context.Skip == -1)
{
sortBuilder.Append(FormatSortColumns());
}
sqlBuilder.Clear();
sqlBuilder.AppendFormat("{0} {1} {2} {3}", selectBuilder.ToString(), fromBuilder.ToString(), whereBuilder.ToString(), sortBuilder.ToString());
}
if (_context.Pager)
{
sqlBuilder.AppendFormat(" LIMIT {0},{1}", _context.Skip, _context.Take);
}
else if (_context.Take > 0)
{
selectBuilder.Append(" LIMIT " + _context.Take + " ");
}
var sql = sqlBuilder.ToString();
_result.CommandText = sql;
}