public void Ctor_WithNonNullFunctionName_UpdatesFunctionNameProperty()
{
var expected = "GETDATE";
var function = new SqlFunction("GETDATE");
Assert.Equal(expected, function.FunctionName);
}
protected override void BuildFunction(StringBuilder sb, SqlFunction func)
{
func = ConvertFunctionParameters(func);
switch (func.Name)
{
case "CASE" : func = ConvertCase(func.SystemType, func.Parameters, 0); break;
case "Coalesce" :
if (func.Parameters.Length > 2)
{
var parms = new ISqlExpression[func.Parameters.Length - 1];
Array.Copy(func.Parameters, 1, parms, 0, parms.Length);
BuildFunction(sb, new SqlFunction(func.SystemType, func.Name, func.Parameters[0],
new SqlFunction(func.SystemType, func.Name, parms)));
return;
}
var sc = new SqlQuery.SearchCondition();
sc.Conditions.Add(new SqlQuery.Condition(false, new SqlQuery.Predicate.IsNull(func.Parameters[0], false)));
func = new SqlFunction(func.SystemType, "IIF", sc, func.Parameters[1], func.Parameters[0]);
break;
}
base.BuildFunction(sb, func);
}
public void Ctor_WithArguments_UpdatesArgumentsProperty()
{
var expected = new[] { (SqlColumn)"Id" };
var function = new SqlFunction("Count", expected);
Assert.NotNull(function.Arguments);
Assert.Same(expected, function.Arguments);
}
public ISqlExpression ConvertConvertFunction(SqlFunction func)
{
switch (Type.GetTypeCode(func.SystemType.ToUnderlying()))
{
case TypeCode.DateTime :
if (func.Name == "Convert")
{
var type1 = func.Parameters[1].SystemType.ToUnderlying();
if (IsTimeDataType(func.Parameters[0]))
{
if (type1 == typeof(DateTime) || type1 == typeof(DateTimeOffset))
return new SqlExpression(
func.SystemType, "Cast(Convert(Char, {0}, 114) as DateTime)", Precedence.Primary, func.Parameters[1]);
if (func.Parameters[1].SystemType == typeof(string))
return func.Parameters[1];
return new SqlExpression(
func.SystemType, "Convert(Char, {0}, 114)", Precedence.Primary, func.Parameters[1]);
}
if (type1 == typeof(DateTime) || type1 == typeof(DateTimeOffset))
{
if (IsDateDataType(func.Parameters[0], "Datetime"))
return new SqlExpression(
func.SystemType, "Cast(Floor(Cast({0} as Float)) as DateTime)", Precedence.Primary, func.Parameters[1]);
}
if (func.Parameters.Length == 2 && func.Parameters[0] is SqlDataType && func.Parameters[0] == SqlDataType.DateTime)
return new SqlFunction(func.SystemType, func.Name, func.Precedence, func.Parameters[0], func.Parameters[1], new SqlValue(120));
}
break;
}
return func;
}
public string chucVuTheoCaNhan(string maToChuc, string tenCaNhan)
{
SqlFunction sqlFun = new SqlFunction(HttpContext.Current.Session.GetConnectionString2());
return(sqlFun.GetOneStringField(@"SELECT chucVu FROM dbo.tblDMCaNhan WHERE maToChucpr_sd=N'" + maToChuc + "' AND tenCaNhan=N'" + tenCaNhan + "'"));
}
public SqlFunctionCallExpression(Type type, SqlFunction function, IReadOnlyList<Expression> arguments)
: base(type)
{
this.Function = function;
this.Arguments = arguments;
}
public SqlFunctionCallExpression(Type type, SqlFunction function, params Expression[] arguments)
: this(type, function, arguments.ToReadOnlyList())
{
}
protected override void BuildFunction(SqlFunction func)
{
func = ConvertFunctionParameters(func);
base.BuildFunction(func);
}
public override ISqlExpression ConvertExpression(ISqlExpression expr)
{
expr = base.ConvertExpression(expr);
if (expr is SqlBinaryExpression)
{
SqlBinaryExpression be = (SqlBinaryExpression)expr;
switch (be.Operation)
{
case "%": return(new SqlFunction(be.SystemType, "Mod", be.Expr1, be.Expr2));
case "&": return(new SqlFunction(be.SystemType, "Bin_And", be.Expr1, be.Expr2));
case "|": return(new SqlFunction(be.SystemType, "Bin_Or", be.Expr1, be.Expr2));
case "^": return(new SqlFunction(be.SystemType, "Bin_Xor", be.Expr1, be.Expr2));
case "+": return(be.SystemType == typeof(string)? new SqlBinaryExpression(be.SystemType, be.Expr1, "||", be.Expr2, be.Precedence): expr);
}
}
else if (expr is SqlFunction)
{
SqlFunction func = (SqlFunction)expr;
switch (func.Name)
{
case "Convert":
if (TypeHelper.GetUnderlyingType(func.SystemType) == typeof(bool))
{
ISqlExpression ex = AlternativeConvertToBoolean(func, 1);
if (ex != null)
{
return(ex);
}
}
return(new SqlExpression(func.SystemType, "Cast({0} as {1})", Precedence.Primary, FloorBeforeConvert(func), func.Parameters[0]));
case "DateAdd":
switch ((Sql.DateParts)((SqlValue)func.Parameters[0]).Value)
{
case Sql.DateParts.Quarter:
return(new SqlFunction(func.SystemType, func.Name, new SqlValue(Sql.DateParts.Month), Mul(func.Parameters[1], 3), func.Parameters[2]));
case Sql.DateParts.DayOfYear:
case Sql.DateParts.WeekDay:
return(new SqlFunction(func.SystemType, func.Name, new SqlValue(Sql.DateParts.Day), func.Parameters[1], func.Parameters[2]));
case Sql.DateParts.Week:
return(new SqlFunction(func.SystemType, func.Name, new SqlValue(Sql.DateParts.Day), Mul(func.Parameters[1], 7), func.Parameters[2]));
}
break;
}
}
else if (expr is SqlExpression)
{
SqlExpression e = (SqlExpression)expr;
if (e.Expr.StartsWith("Extract(Quarter"))
{
return(Inc(Div(Dec(new SqlExpression(e.SystemType, "Extract(Month from {0})", e.Parameters)), 3)));
}
if (e.Expr.StartsWith("Extract(YearDay"))
{
return(Inc(new SqlExpression(e.SystemType, e.Expr.Replace("Extract(YearDay", "Extract(yearDay"), e.Parameters)));
}
if (e.Expr.StartsWith("Extract(WeekDay"))
{
return(Inc(new SqlExpression(e.SystemType, e.Expr.Replace("Extract(WeekDay", "Extract(weekDay"), e.Parameters)));
}
}
return(expr);
}
protected override IBuildContext BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo)
{
var sequence = builder.BuildSequence(new BuildInfo(buildInfo, methodCall.Arguments[0])
{
CreateSubQuery = true
});
if (sequence.SelectQuery.Select.IsDistinct ||
sequence.SelectQuery.Select.TakeValue != null ||
sequence.SelectQuery.Select.SkipValue != null ||
!sequence.SelectQuery.GroupBy.IsEmpty)
{
sequence = new SubQueryContext(sequence);
}
if (sequence.SelectQuery.OrderBy.Items.Count > 0)
{
if (sequence.SelectQuery.Select.TakeValue == null && sequence.SelectQuery.Select.SkipValue == null)
{
sequence.SelectQuery.OrderBy.Items.Clear();
}
else
{
sequence = new SubQueryContext(sequence);
}
}
var context = new AggregationContext(buildInfo.Parent, sequence, methodCall);
var attr = GetAggregateDefinition(methodCall, builder.MappingSchema);
ISqlExpression sqlExpression = null;
if (attr != null)
{
sqlExpression = attr.GetExpression(builder.MappingSchema, sequence.SelectQuery, methodCall, e =>
{
var ex = e.Unwrap();
if (ex is LambdaExpression l)
{
var p = sequence.Parent;
var ctx = new ExpressionContext(buildInfo.Parent, sequence, l);
var res = builder.ConvertToSql(ctx, l.Body, true);
builder.ReplaceParent(ctx, p);
return(res);
}
return(builder.ConvertToSql(context, ex, true));
});
}
var methodName = methodCall.Method.Name.Replace("Async", "");
if (sqlExpression == null)
{
var sql = sequence.ConvertToSql(null, 0, ConvertFlags.Field).Select(_ => _.Sql).ToArray();
if (sql.Length == 1 && sql[0] is SelectQuery query)
{
if (query.Select.Columns.Count == 1)
{
var join = query.OuterApply();
context.SelectQuery.From.Tables[0].Joins.Add(join.JoinedTable);
sql[0] = query.Select.Columns[0];
}
}
if (attr != null)
{
sqlExpression = attr.GetExpression(methodCall.Method, sql);
}
else
{
sqlExpression = new SqlFunction(methodCall.Type, methodName, true, sql);
}
}
if (sqlExpression == null)
{
throw new LinqToDBException("Invalid Aggregate function implementation");
}
context.Sql = context.SelectQuery;
context.FieldIndex = context.SelectQuery.Select.Add(sqlExpression, methodName);
return(context);
}
public override ISqlExpression ConvertExpression(ISqlExpression expr)
{
expr = base.ConvertExpression(expr);
if (expr is SqlBinaryExpression)
{
var be = (SqlBinaryExpression)expr;
switch (be.Operation)
{
case "%": return new SqlFunction(be.SystemType, "Mod", be.Expr1, be.Expr2);
case "&": return new SqlFunction(be.SystemType, "BitAnd", be.Expr1, be.Expr2);
case "|": return new SqlFunction(be.SystemType, "BitOr", be.Expr1, be.Expr2);
case "^": return new SqlFunction(be.SystemType, "BitXor", be.Expr1, be.Expr2);
case "+": return be.SystemType == typeof(string)? new SqlBinaryExpression(be.SystemType, be.Expr1, "||", be.Expr2, be.Precedence): expr;
}
}
else if (expr is SqlFunction)
{
var func = (SqlFunction)expr;
switch (func.Name)
{
case "Coalesce" : return new SqlFunction(func.SystemType, "Nvl", func.Parameters);
case "Convert" :
{
var par0 = func.Parameters[0];
var par1 = func.Parameters[1];
switch (Type.GetTypeCode(TypeHelper.GetUnderlyingType(func.SystemType)))
{
case TypeCode.String : return new SqlFunction(func.SystemType, "To_Char", func.Parameters[1]);
case TypeCode.Boolean :
{
var ex = AlternativeConvertToBoolean(func, 1);
if (ex != null)
return ex;
break;
}
case TypeCode.UInt64:
if (TypeHelper.IsFloatType(func.Parameters[1].SystemType))
par1 = new SqlFunction(func.SystemType, "Floor", func.Parameters[1]);
break;
case TypeCode.DateTime :
if (IsDateDataType(func.Parameters[0], "Date"))
{
if (func.Parameters[1].SystemType == typeof(string))
{
return new SqlFunction(
func.SystemType,
"Date",
new SqlFunction(func.SystemType, "To_Date", func.Parameters[1], new SqlValue("%Y-%m-%d")));
}
return new SqlFunction(func.SystemType, "Date", func.Parameters[1]);
}
if (IsTimeDataType(func.Parameters[0]))
return new SqlExpression(func.SystemType, "Cast(Extend({0}, hour to second) as Char(8))", Precedence.Primary, func.Parameters[1]);
return new SqlFunction(func.SystemType, "To_Date", func.Parameters[1]);
default:
if (TypeHelper.GetUnderlyingType(func.SystemType) == typeof(DateTimeOffset))
goto case TypeCode.DateTime;
break;
}
return new SqlExpression(func.SystemType, "Cast({0} as {1})", Precedence.Primary, par1, par0);
}
case "Quarter" : return Inc(Div(Dec(new SqlFunction(func.SystemType, "Month", func.Parameters)), 3));
case "WeekDay" : return Inc(new SqlFunction(func.SystemType, "weekDay", func.Parameters));
case "DayOfYear":
return
Inc(Sub<int>(
new SqlFunction(null, "Mdy",
new SqlFunction(null, "Month", func.Parameters),
new SqlFunction(null, "Day", func.Parameters),
new SqlFunction(null, "Year", func.Parameters)),
new SqlFunction(null, "Mdy",
new SqlValue(1),
new SqlValue(1),
new SqlFunction(null, "Year", func.Parameters))));
case "Week" :
return
new SqlExpression(
func.SystemType,
"((Extend({0}, year to day) - (Mdy(12, 31 - WeekDay(Mdy(1, 1, year({0}))), Year({0}) - 1) + Interval(1) day to day)) / 7 + Interval(1) day to day)::char(10)::int",
func.Parameters);
case "Hour" :
case "Minute" :
case "Second" : return new SqlExpression(func.SystemType, string.Format("({{0}}::datetime {0} to {0})::char(3)::int", func.Name), func.Parameters);
}
}
return expr;
}
protected override void BuildFunction(SqlFunction func)
{
switch (func.Name)
{
case "Coalesce":
if (func.Parameters.Length > 2)
{
var parms = new ISqlExpression[func.Parameters.Length - 1];
Array.Copy(func.Parameters, 1, parms, 0, parms.Length);
BuildFunction(new SqlFunction(func.SystemType, func.Name, func.Parameters[0],
new SqlFunction(func.SystemType, func.Name, parms)));
return;
}
var sc = new SelectQuery.SearchCondition();
sc.Conditions.Add(new SelectQuery.Condition(false, new SelectQuery.Predicate.IsNull(func.Parameters[0], false)));
func = new SqlFunction(func.SystemType, "Iif", sc, func.Parameters[1], func.Parameters[0]);
break;
case "CASE": func = ConvertCase(func.SystemType, func.Parameters, 0); break;
case "CharIndex":
func = func.Parameters.Length == 2?
new SqlFunction(func.SystemType, "InStr", new SqlValue(1), func.Parameters[1], func.Parameters[0], new SqlValue(1)):
new SqlFunction(func.SystemType, "InStr", func.Parameters[2], func.Parameters[1], func.Parameters[0], new SqlValue(1));
break;
case "Convert":
switch (func.SystemType.ToUnderlying().GetTypeCodeEx())
{
case TypeCode.String: func = new SqlFunction(func.SystemType, "CStr", func.Parameters[1]); break;
case TypeCode.DateTime:
if (IsDateDataType(func.Parameters[0], "Date"))
{
func = new SqlFunction(func.SystemType, "DateValue", func.Parameters[1]);
}
else if (IsTimeDataType(func.Parameters[0]))
{
func = new SqlFunction(func.SystemType, "TimeValue", func.Parameters[1]);
}
else
{
func = new SqlFunction(func.SystemType, "CDate", func.Parameters[1]);
}
break;
default:
if (func.SystemType == typeof(DateTime))
{
goto case TypeCode.DateTime;
}
BuildExpression(func.Parameters[1]);
return;
}
break;
}
base.BuildFunction(func);
}
/// <summary>
/// 获取播放量最大的视频,类型,地域,IP
/// </summary>
private void getMVP()
{
string sqltype = @"select top 1 videoType,countNum from UserAccessType where videoType!='' order by countNum desc";
DataSet dstype = new DataSet();
dstype = SqlFunction.Sql_DataAdapterToDS(sqltype);
MVPtype = dstype.Tables[0].Rows[0]["videoType"].ToString();
try {
string[] temp = MVPtype.Split('_');
MVPtype = temp[temp.Length - 1];
} catch {
}
MVPtypec = dstype.Tables[0].Rows[0]["countNum"].ToString();
h2_typec.InnerText = MVPtypec;
lab_type.InnerText = MVPtype;
string sqladdr = @"select top 1 addr,count(*)as c1 from UserAccessRecord group by addr order by c1 desc";
DataSet dsaddr = new DataSet();
dsaddr = SqlFunction.Sql_DataAdapterToDS(sqladdr);
MVPaddr = dsaddr.Tables[0].Rows[0]["addr"].ToString();
MVPaddrc = dsaddr.Tables[0].Rows[0]["c1"].ToString();
h2_addrc.InnerText = MVPaddrc;
lab_addr.InnerText = MVPaddr;
string sqlip = @"select top 1 account,pwd,COUNT(account)as c1 from UserAccessRecord group by account,pwd order by c1 desc";
DataSet dsip = new DataSet();
dsip = SqlFunction.Sql_DataAdapterToDS(sqlip);
MVPacc = dsip.Tables[0].Rows[0]["account"].ToString();
MVPpwd = dsip.Tables[0].Rows[0]["pwd"].ToString();
MVPipc = dsip.Tables[0].Rows[0]["c1"].ToString();
if (MVPacc == "")
{
MVPacc = "null";
}
if (MVPipc == "")
{
MVPipc = "null";
}
if (MVPpwd == "")
{
MVPpwd = "null";
}
h2_ipc.InnerText = MVPipc;
lab_ip.InnerText = MVPacc.ToLower();
lab_ip2.InnerText = MVPpwd;
string sqltitle = @"select top 1 (select title from Video where id = vid)as title ,count(*) as c
from UserAccessRecord group by vid order by c desc";
DataSet dstitle = new DataSet();
dstitle = SqlFunction.Sql_DataAdapterToDS(sqltitle);
MVPtitle = dstitle.Tables[0].Rows[0]["title"].ToString();
MVPtitlec = dstitle.Tables[0].Rows[0]["c"].ToString();
h2_titlec.InnerText = MVPtitlec;
if (MVPtitle.Length > 23)
{
lab_t.Attributes.Add("font-size", "14px");
}
else
{
lab_t.Attributes.Add("font-size", "16px");
}
lab_t.InnerText = MVPtitle;
}
public override ISqlExpression ConvertExpression(ISqlExpression expr)
{
expr = base.ConvertExpression(expr);
if (expr is SqlBinaryExpression)
{
var be = (SqlBinaryExpression)expr;
switch (be.Operation)
{
case "%": return(new SqlFunction(be.SystemType, "Mod", be.Expr1, be.Expr2));
case "&": return(new SqlFunction(be.SystemType, "BitAnd", be.Expr1, be.Expr2));
case "|": return(new SqlFunction(be.SystemType, "BitOr", be.Expr1, be.Expr2));
case "^": return(new SqlFunction(be.SystemType, "BitXor", be.Expr1, be.Expr2));
case "+": return(be.SystemType == typeof(string)? new SqlBinaryExpression(be.SystemType, be.Expr1, "||", be.Expr2, be.Precedence): expr);
}
}
else if (expr is SqlFunction)
{
var func = (SqlFunction)expr;
switch (func.Name)
{
case "Coalesce": return(new SqlFunction(func.SystemType, "Nvl", func.Parameters));
case "Convert":
{
var par0 = func.Parameters[0];
var par1 = func.Parameters[1];
switch (Type.GetTypeCode(TypeHelper.GetUnderlyingType(func.SystemType)))
{
case TypeCode.String: return(new SqlFunction(func.SystemType, "To_Char", func.Parameters[1]));
case TypeCode.Boolean:
{
var ex = AlternativeConvertToBoolean(func, 1);
if (ex != null)
{
return(ex);
}
break;
}
case TypeCode.UInt64:
if (TypeHelper.IsFloatType(func.Parameters[1].SystemType))
{
par1 = new SqlFunction(func.SystemType, "Floor", func.Parameters[1]);
}
break;
case TypeCode.DateTime:
if (IsDateDataType(func.Parameters[0], "Date"))
{
if (func.Parameters[1].SystemType == typeof(string))
{
return(new SqlFunction(
func.SystemType,
"Date",
new SqlFunction(func.SystemType, "To_Date", func.Parameters[1], new SqlValue("%Y-%m-%d"))));
}
return(new SqlFunction(func.SystemType, "Date", func.Parameters[1]));
}
if (IsTimeDataType(func.Parameters[0]))
{
return(new SqlExpression(func.SystemType, "Cast(Extend({0}, hour to second) as Char(8))", Precedence.Primary, func.Parameters[1]));
}
return(new SqlFunction(func.SystemType, "To_Date", func.Parameters[1]));
default:
if (TypeHelper.GetUnderlyingType(func.SystemType) == typeof(DateTimeOffset))
{
goto case TypeCode.DateTime;
}
break;
}
return(new SqlExpression(func.SystemType, "Cast({0} as {1})", Precedence.Primary, par1, par0));
}
case "Quarter": return(Inc(Div(Dec(new SqlFunction(func.SystemType, "Month", func.Parameters)), 3)));
case "WeekDay": return(Inc(new SqlFunction(func.SystemType, "weekDay", func.Parameters)));
case "DayOfYear":
return
(Inc(Sub <int>(
new SqlFunction(null, "Mdy",
new SqlFunction(null, "Month", func.Parameters),
new SqlFunction(null, "Day", func.Parameters),
new SqlFunction(null, "Year", func.Parameters)),
new SqlFunction(null, "Mdy",
new SqlValue(1),
new SqlValue(1),
new SqlFunction(null, "Year", func.Parameters)))));
case "Week":
return
(new SqlExpression(
func.SystemType,
"((Extend({0}, year to day) - (Mdy(12, 31 - WeekDay(Mdy(1, 1, year({0}))), Year({0}) - 1) + Interval(1) day to day)) / 7 + Interval(1) day to day)::char(10)::int",
func.Parameters));
case "Hour":
case "Minute":
case "Second": return(new SqlExpression(func.SystemType, string.Format("({{0}}::datetime {0} to {0})::char(3)::int", func.Name), func.Parameters));
}
}
return(expr);
}
public T2 Exists(ISelectQuery subQuery)
{
Search.Conditions.AddLast(new Condition(false, new FuncLike(SqlFunction.CreateExists(subQuery))));
return(GetNext());
}
/// <inheritdoc />
public IEnumerable <IDbTableFieldEntity> GetFields(IDbTableEntity table)
{
//create SQL statement
IConstantSqlExpressionsFactory constantFactory = new SqlServerConstantExpressionFactory();
ISqlObject owner = new SqlServerSource("INFORMATION_SCHEMA");
ISqlExpression tableName = constantFactory.Create(table.Name);
ISqlExpression tableSchema = constantFactory.Create(table.Schema);
ISqlFunction tableID = new SqlFunction("OBJECT_ID").AddArgument(constantFactory.Create(table.SchemaQualifiedName));
ISqlObject columnsView = new SqlServerSource(owner, "COLUMNS");
ISqlObject extendedPropertiesView = new SqlServerSource(new SqlServerSource("sys"), "extended_properties");
ISqlObject keyColumnUsageView = new SqlServerSource(owner, "KEY_COLUMN_USAGE");
ISqlObject tableConstraintsView = new SqlServerSource(owner, "TABLE_CONSTRAINTS");
ISqlObject referentialConstraintsView = new SqlServerSource(owner, "REFERENTIAL_CONSTRAINTS");
ISqlObject keyColumnUsageView2 = new SqlServerSource(owner, "KEY_COLUMN_USAGE", "kcu");
ISqlObject nameField = new SqlServerField(columnsView, "COLUMN_NAME", "Name");
ISqlObject indexField = new SqlServerField(columnsView, "ORDINAL_POSITION", "Index");
ISqlObject typeField = new SqlServerField(columnsView, "DATA_TYPE", "TypeName");
ISqlObject descriptionField = new SqlServerField(extendedPropertiesView, "value", "Description");
ISqlObject maxLengthField = new SqlServerField(columnsView, "CHARACTER_MAXIMUM_LENGTH", "MaxLength");
ISqlObject defaultField = new SqlServerField(columnsView, "COLUMN_DEFAULT", "DefaultExpression");
ISqlObject isNullableField = new SqlServerField(columnsView, "IS_NULLABLE", "IsNullable");
ISqlObject isIdentityField = new SqlServerField(new SqlFunction("COLUMNPROPERTY").AddArgument(tableID).AddArgument(nameField, false).AddArgument(constantFactory.Create("IsIdentity")), "IsIdentity");
ISqlObject constraintNameField = new SqlServerField(tableConstraintsView, "CONSTRAINT_NAME", "ConstraintName");
ISqlObject constraintTypeField = new SqlServerField(tableConstraintsView, "CONSTRAINT_TYPE", "ConstraintType");
ISqlObject referenceTableField = new SqlServerField(keyColumnUsageView2, "TABLE_NAME", "ReferenceTable");
ISqlObject referenceFieldField = new SqlServerField(keyColumnUsageView2, "COLUMN_NAME", "ReferenceField");
ISqlSelectStatement statement = new SqlServerSelectStatement();
statement.SelectClause.AddExpressions(nameField, indexField, typeField, descriptionField, maxLengthField, defaultField, isNullableField, isIdentityField, constraintNameField, constraintTypeField, referenceTableField, referenceFieldField);
statement.FromClause.SetSource(columnsView);
statement.FromClause.LeftOuterJoin(extendedPropertiesView, new SqlServerField(extendedPropertiesView, "class", null).Equal(constantFactory.Create(1)).And(new SqlServerField(extendedPropertiesView, "major_id", null).Equal(tableID)).And(new SqlServerField(extendedPropertiesView, "minor_id", null).Equal(new SqlFunction("COLUMNPROPERTY").AddArgument(tableID).AddArgument(nameField, false).AddArgument(constantFactory.Create("ColumnId")))));
statement.FromClause.LeftOuterJoin(keyColumnUsageView, new SqlServerField(columnsView, "COLUMN_NAME", null).Equal(new SqlServerField(keyColumnUsageView, "COLUMN_NAME", null)).And(new SqlServerField(keyColumnUsageView, "TABLE_NAME", null).Equal(tableName)).And(new SqlServerField(keyColumnUsageView, "TABLE_SCHEMA", null).Equal(tableSchema)));
statement.FromClause.LeftOuterJoin(tableConstraintsView, new SqlServerField(keyColumnUsageView, "CONSTRAINT_NAME", null).Equal(new SqlServerField(tableConstraintsView, "CONSTRAINT_NAME", null)).And(new SqlServerField(tableConstraintsView, "TABLE_NAME", null).Equal(tableName)).And(new SqlServerField(tableConstraintsView, "TABLE_SCHEMA", null).Equal(tableSchema)));
statement.FromClause.LeftOuterJoin(referentialConstraintsView, new SqlServerField(tableConstraintsView, "CONSTRAINT_NAME", null).Equal(new SqlServerField(referentialConstraintsView, "CONSTRAINT_NAME", null)));
statement.FromClause.LeftOuterJoin(keyColumnUsageView2, new SqlServerField(referentialConstraintsView, "UNIQUE_CONSTRAINT_NAME", null).Equal(new SqlServerField(keyColumnUsageView2, "CONSTRAINT_NAME", null)).And(new SqlServerField(keyColumnUsageView, "ORDINAL_POSITION", null).Equal(new SqlServerField(keyColumnUsageView2, "ORDINAL_POSITION", null))));
statement.WhereClause.Condition = new SqlServerField(columnsView, "TABLE_NAME", null).Equal(tableName).And(new SqlServerField(columnsView, "TABLE_SCHEMA", null).Equal(tableSchema));
statement.OrderClause.AddExpression(indexField, SqlOrder.Asc);
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = table.Database.Source.Name;
builder.InitialCatalog = table.Database.Name;
if (!(builder.IntegratedSecurity = table.Database.Source.DbCredential.IntegratedSecurity))
{
builder.UserID = table.Database.Source.DbCredential.UserName;
builder.Password = table.Database.Source.DbCredential.Password;
}
//read all fields data
ICollection <FieldItem> items = new List <FieldItem>();
IList <ISqlExpression> selectFields = new List <ISqlExpression>(statement.SelectClause.Expressions);
using (IDbConnection connection = new SqlConnection(builder.ConnectionString)) {
using (IDataReader reader = SqlHelper.ExecuteSelect(connection, statement)) {
int nameIndex = selectFields.IndexOf(nameField);
int indexIndex = selectFields.IndexOf(indexField);
int typeIndex = selectFields.IndexOf(typeField);
int descriptionIndex = selectFields.IndexOf(descriptionField);
int maxLengthIndex = selectFields.IndexOf(maxLengthField);
int defaultIndex = selectFields.IndexOf(defaultField);
int isNullableIndex = selectFields.IndexOf(isNullableField);
int isIdentityIndex = selectFields.IndexOf(isIdentityField);
int constraintNameIndex = selectFields.IndexOf(constraintNameField);
int constraintTypeIndex = selectFields.IndexOf(constraintTypeField);
int referenceTableIndex = selectFields.IndexOf(referenceTableField);
int referenceFieldIndex = selectFields.IndexOf(referenceFieldField);
while (reader.Read())
{
items.Add(new FieldItem {
Name = reader.GetString(nameIndex),
Index = reader.GetInt32(indexIndex),
Type = reader.GetString(typeIndex),
Description = reader.IsDBNull(descriptionIndex) ? null : reader.GetString(descriptionIndex),
MaxLength = reader.IsDBNull(maxLengthIndex) ? 0 : reader.GetInt32(maxLengthIndex),
HasDefault = !reader.IsDBNull(defaultIndex),
IsNullable = reader.GetString(isNullableIndex).Equals("YES", StringComparison.OrdinalIgnoreCase),
IsIdentity = reader.GetInt32(isIdentityIndex) == 1,
ConstraintName = reader.IsDBNull(constraintNameIndex) ? null : reader.GetString(constraintNameIndex),
ConstraintTypeName = reader.IsDBNull(constraintTypeIndex) ? null : reader.GetString(constraintTypeIndex),
ReferenceTable = reader.IsDBNull(referenceTableIndex) ? null : reader.GetString(referenceTableIndex),
ReferenceField = reader.IsDBNull(referenceFieldIndex) ? null : reader.GetString(referenceFieldIndex),
});
}
}
}
//inject unique index data
FieldItem field = null;
foreach (UniqueIndexColumnItem item in this.GetUniqueIndexColumns(table))
{
field = items.Where((obj) => string.Equals(obj.Name, item.ColumnName, StringComparison.OrdinalIgnoreCase)).First();
if (field.ConstraintName == null)
{
field.ConstraintName = item.IndexName;
field.ConstraintTypeName = "UNIQUE";
}
else
{
field = items.AddItem((FieldItem)field.Clone());
field.ConstraintName = item.IndexName;
field.ConstraintTypeName = "UNIQUE";
}
}
//get read-only data
ISqlObject tableSource = new SqlServerSource(new SqlServerSource(table.Schema), table.Name);
statement = new SqlServerSelectStatement();
statement.SelectClause.AddExpressions(new SqlAllField(tableSource));
statement.FromClause.Source = tableSource;
using (IDbConnection connection = new SqlConnection(builder.ConnectionString)) {
using (DataTable schema = SqlHelper.ExecuteSchema(connection, statement)) {
using (DataColumn columnNameColumn = schema.Columns["ColumnName"]) {
using (DataColumn isReadOnlyColumn = schema.Columns["IsReadOnly"]) {
foreach (DataRow row in schema.Rows)
{
if (row.IsNull(columnNameColumn))
{
continue;
}
this.m_isReadOnlyCache[((string)row[columnNameColumn]).ToLower()] = (bool)row[isReadOnlyColumn];
}
}
}
}
}
//analyze fields
IDictionary <string, DbConstraintEntity> constraints = new Dictionary <string, DbConstraintEntity>();
this.CreateFileds(table, constraints, items.Where((obj) => string.IsNullOrWhiteSpace(obj.ReferenceField)));
this.CreateFileds(table, constraints, items.Where((obj) => !string.IsNullOrWhiteSpace(obj.ReferenceField)));
return(new List <IDbTableFieldEntity>(g_fieldsCache[table].Values));
}
ISqlExpression ConvertEnumerable(MethodCallExpression call)
{
if (call.IsAggregate(Builder.MappingSchema))
{
if (call.Arguments[0].NodeType == ExpressionType.Call)
{
var arg = (MethodCallExpression)call.Arguments[0];
if (arg.Method.Name == "Select")
{
var arg0 = arg.Arguments[0].SkipPathThrough();
if (arg0.NodeType != ExpressionType.Call)
{
var l = (LambdaExpression)arg.Arguments[1].Unwrap();
var largs = l.Type.GetGenericArguments();
if (largs.Length == 2)
{
var p = Element.Parent;
var ctx = new ExpressionContext(Parent, Element, l);
var sql = Builder.ConvertToSql(ctx, l.Body, true);
Builder.ReplaceParent(ctx, p);
return(new SqlFunction(call.Type, call.Method.Name, true, sql));
}
}
}
}
}
var rootArgument = call.Arguments[0].SkipMethodChain(Builder.MappingSchema);
if (rootArgument.NodeType == ExpressionType.Call)
{
var ctx = Builder.GetSubQuery(this, call);
if (Builder.DataContext.SqlProviderFlags.IsSubQueryColumnSupported)
{
return(ctx.SelectQuery);
}
var join = ctx.SelectQuery.CrossApply();
SelectQuery.From.Tables[0].Joins.Add(join.JoinedTable);
return(ctx.SelectQuery.Select.Columns[0]);
}
var args = new ISqlExpression[call.Arguments.Count - 1];
if (CountBuilder.MethodNames.Contains(call.Method.Name))
{
if (args.Length > 0)
{
throw new InvalidOperationException();
}
return(SqlFunction.CreateCount(call.Type, SelectQuery));
}
var attribute =
Builder.MappingSchema.GetAttribute <Sql.ExpressionAttribute>(call.Method.DeclaringType !, call.Method,
c => c.Configuration);
if (attribute != null)
{
var expr = attribute.GetExpression(Builder.DataContext, SelectQuery, call, (e, descriptor) =>
{
var ex = e.Unwrap();
if (ex is LambdaExpression l)
{
var p = Element.Parent;
var ctx = new ExpressionContext(Parent, Element, l);
var res = Builder.ConvertToSql(ctx, l.Body, true, descriptor);
Builder.ReplaceParent(ctx, p);
return(res);
}
if (rootArgument == e && typeof(IGrouping <,>).IsSameOrParentOf(ex.Type))
{
return(Element.ConvertToSql(null, 0, ConvertFlags.Field)
.Select(_ => _.Sql)
.FirstOrDefault());
}
if (typeof(IGrouping <,>).IsSameOrParentOf(Builder.GetRootObject(ex).Type))
{
return(ConvertToSql(ex, 0, ConvertFlags.Field)
.Select(_ => _.Sql)
.FirstOrDefault());
}
return(Builder.ConvertToExtensionSql(Element, ex, descriptor));
});
if (expr != null)
{
return(expr);
}
}
if (call.Arguments.Count > 1)
{
for (var i = 1; i < call.Arguments.Count; i++)
{
var ex = call.Arguments[i].Unwrap();
if (ex is LambdaExpression l)
{
var p = Element.Parent;
var ctx = new ExpressionContext(Parent, Element, l);
args[i - 1] = Builder.ConvertToSql(ctx, l.Body, true);
Builder.ReplaceParent(ctx, p);
}
else
{
args[i - 1] = Builder.ConvertToSql(Element, ex, true);
}
}
}
else
{
args = Element.ConvertToSql(null, 0, ConvertFlags.Field).Select(_ => _.Sql).ToArray();
}
if (attribute != null)
{
return(attribute.GetExpression(call.Method, args));
}
return(new SqlFunction(call.Type, call.Method.Name, true, args));
}
//this is for Tests.Linq.Common.CoalesceLike test
protected override void BuildFunction(SqlFunction func)
{
func = ConvertFunctionParameters(func);
switch (func.Name)
{
case "CASE": func = ConvertCase(func.SystemType, func.Parameters, 0);
break;
}
base.BuildFunction(func);
}
/// <summary>
/// Visits the specified <see cref="SqlFunction"/>.
/// </summary>
/// <param name="expression">
/// The expression to visit.
/// </param>
public virtual void Visit(SqlFunction expression)
{
}
protected override ISqlExpression ConvertFunction(SqlFunction func)
{
func = ConvertFunctionParameters(func, false);
return(base.ConvertFunction(func));
}
protected void gridVanBan_OnRebind(object sender, EventArgs e)
{
try
{
string _pageName = Path.GetFileNameWithoutExtension(Page.AppRelativeVirtualPath);
String _Query = "";
switch (_pageName)
{
case "bcnctienkhathi":
_Query = " AND maLoaiVBanpr_sd IN (SELECT maLoaiVBanpr FROM dbo.tblDMLoaiVanBan WHERE maLoaiVBanpr_cha = N'I')";
break;
case "thamdinhbcnctienkhathi":
_Query = " AND maLoaiVBanpr_sd IN (SELECT maLoaiVBanpr FROM dbo.tblDMLoaiVanBan WHERE maLoaiVBanpr_cha = N'II')";
break;
case "quyetdinhpheduyetchutruongdautu":
_Query = " AND maLoaiVBanpr_sd IN (SELECT maLoaiVBanpr FROM dbo.tblDMLoaiVanBan WHERE maLoaiVBanpr_cha = N'III')";
break;
case "lapbaocaonghiencuukhathi":
_Query = " AND maLoaiVBanpr_sd IN (SELECT maLoaiVBanpr FROM dbo.tblDMLoaiVanBan WHERE maLoaiVBanpr_cha = N'IV')";
break;
case "thamdinhnghiencuubaocaokhathi":
_Query = " AND maLoaiVBanpr_sd IN (SELECT maLoaiVBanpr FROM dbo.tblDMLoaiVanBan WHERE maLoaiVBanpr_cha = N'V')";
break;
case "quyetdinhpheduyetduan":
_Query = " AND maLoaiVBanpr_sd IN (SELECT maLoaiVBanpr FROM dbo.tblDMLoaiVanBan WHERE maLoaiVBanpr_cha = N'VIII')";
break;
case "giayphepxaydung":
_Query = " AND maLoaiVBanpr_sd IN (SELECT maLoaiVBanpr FROM dbo.tblDMLoaiVanBan WHERE maLoaiVBanpr_cha = N'XII')";
break;
case "qdpdkehoachdthaucbdt":
_Query = " AND maLoaiVBanpr_sd IN (SELECT maLoaiVBanpr FROM dbo.tblDMLoaiVanBan WHERE maLoaiVBanpr_cha = N'XIII')";
break;
case "qdpddckehoachdthaucbdt":
_Query = " AND maLoaiVBanpr_sd IN (SELECT maLoaiVBanpr FROM dbo.tblDMLoaiVanBan WHERE maLoaiVBanpr_cha = N'XIV')";
break;
case "qdpdkehoachdthauthdt":
_Query = " AND maLoaiVBanpr_sd IN (SELECT maLoaiVBanpr FROM dbo.tblDMLoaiVanBan WHERE maLoaiVBanpr_cha = N'XVIII')";
break;
case "qdpddckehoachdthauthdt":
_Query = " AND maLoaiVBanpr_sd IN (SELECT maLoaiVBanpr FROM dbo.tblDMLoaiVanBan WHERE maLoaiVBanpr_cha = N'XIX')";
break;
case "bckinhtekythuat":
_Query = " AND maLoaiVBanpr_sd IN (SELECT maLoaiVBanpr FROM dbo.tblDMLoaiVanBan WHERE maLoaiVBanpr_cha = N'XXXV')";
break;
default:
break;
}
SqlFunction _sqlfun = new SqlFunction(HttpContext.Current.Session.GetConnectionString2());
String qr = "SELECT maNguoiKy=tenNguoiKy, *,(SELECT tblDMLoaiVanBan.tenLoaiVBan FROM tblDMLoaiVanBan WHERE tblDMLoaiVanBan.maLoaiVBanpr=maLoaiVBanpr_sd) loaiVB,(SELECT tblDMToChuc.tenToChuc FROM tblDMToChuc WHERE tblDMToChuc.maToChucpr=tblVanBanDA.maToChucpr_phathanh) coQuanBanHanh,CASE WHEN LEN(isnull(tenFile,'')) > 0 THEN (N'<a style=\"color:Blue;text-decoration:none\" onclick=\"xemVB('+CHAR(39)+tenFile+CHAR(39)+')\" href=\"#\">'+ISNULL(replace(ltrim(right(tenFile,CHARINDEX('/',REVERSE(tenFile),0))),'/',''),'')+'</a>' + N' <a style=\"color:Blue;\" onclick=\"xoaDinhKemVanBan('+CHAR(39)+tenFile+CHAR(39)+','+CHAR(39)+CONVERT(NVARCHAR(50),sttVBDApr)+CHAR(39)+')\" href=\"#\">Xóa</a>') ELSE N'<a style=\"color:Blue;\" onclick=dinKemFileVanBan(\"'+CONVERT(NVARCHAR(50),sttVBDApr)+N'\") href=\"#\">Đính kèm file</a>' end as dinhKem, (SELECT tenDuAn FROM dbo.tblDuAn WHERE sttDuAnpr = tblVanBanDA.sttDuAnpr_sd) AS tenDuAn, sttDuAnpr_sd FROM tblVanBanDA WHERE maDonVipr_sd=N'" + HttpContext.Current.Session.GetDonVi().maDonVi + "'" + _Query + " order by sttVBDApr";
gridVanBan.DataSource = _sqlfun.GetData(qr);
gridVanBan.DataBind();
}
catch
{
gridVanBan.DataSource = null;
gridVanBan.DataBind();
}
}
/// <summary>
/// Visits the specified <see cref="SqlFunction"/>.
/// </summary>
/// <param name="expression">
/// The expression to visit.
/// </param>
public virtual void Visit(SqlFunction expression)
{
}
protected override void BuildFunction(StringBuilder sb, SqlFunction func)
{
func = ConvertFunctionParameters(func);
base.BuildFunction(sb, func);
}
internal protected abstract string CompatibleFunctionName(SqlFunction function);
ISqlExpression ConvertEnumerable(MethodCallExpression call)
{
if (AggregationBuilder.MethodNames.Contains(call.Method.Name))
{
if (call.Arguments[0].NodeType == ExpressionType.Call)
{
var arg = (MethodCallExpression)call.Arguments[0];
if (arg.Method.Name == "Select")
{
if (arg.Arguments[0].NodeType != ExpressionType.Call)
{
var l = (LambdaExpression)arg.Arguments[1].Unwrap();
var largs = l.Type.GetGenericArgumentsEx();
if (largs.Length == 2)
{
var p = _element.Parent;
var ctx = new ExpressionContext(Parent, _element, l);
var sql = Builder.ConvertToSql(ctx, l.Body, true);
Builder.ReplaceParent(ctx, p);
return(new SqlFunction(call.Type, call.Method.Name, sql));
}
}
}
}
}
if (call.Arguments[0].NodeType == ExpressionType.Call)
{
var ctx = Builder.GetSubQuery(this, call);
if (Builder.DataContextInfo.SqlProviderFlags.IsSubQueryColumnSupported)
{
return(ctx.SelectQuery);
}
var join = ctx.SelectQuery.CrossApply();
SelectQuery.From.Tables[0].Joins.Add(join.JoinedTable);
return(ctx.SelectQuery.Select.Columns[0]);
}
var args = new ISqlExpression[call.Arguments.Count - 1];
if (CountBuilder.MethodNames.Contains(call.Method.Name))
{
if (args.Length > 0)
{
throw new InvalidOperationException();
}
return(SqlFunction.CreateCount(call.Type, SelectQuery));
}
if (call.Arguments.Count > 1)
{
for (var i = 1; i < call.Arguments.Count; i++)
{
var ex = call.Arguments[i].Unwrap();
if (ex is LambdaExpression)
{
var l = (LambdaExpression)ex;
var p = _element.Parent;
var ctx = new ExpressionContext(Parent, _element, l);
args[i - 1] = Builder.ConvertToSql(ctx, l.Body, true);
Builder.ReplaceParent(ctx, p);
}
else
{
throw new NotImplementedException();
}
}
}
else
{
args = _element.ConvertToSql(null, 0, ConvertFlags.Field).Select(_ => _.Sql).ToArray();
}
return(new SqlFunction(call.Type, call.Method.Name, args));
}
protected override IBuildContext BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo)
{
var returnType = methodCall.Method.ReturnType;
var sequence = builder.BuildSequence(new BuildInfo(buildInfo, methodCall.Arguments[0])
{
CreateSubQuery = true
});
// if (sequence.SelectQuery != buildInfo.SelectQuery)
// {
// if (sequence is JoinBuilder.GroupJoinSubQueryContext)
// {
// var ctx = new CountContext(buildInfo.Parent, sequence, returnType)
// {
// SelectQuery =
// sequence.SelectQuery
// //((JoinBuilder.GroupJoinSubQueryContext)sequence).GetCounter(methodCall)
// };
//
// ctx.Sql = ctx.SelectQuery;
// ctx.FieldIndex = ctx.SelectQuery.Select.Add(SqlFunction.CreateCount(returnType, ctx.SelectQuery), "cnt");
//
// return ctx;
// }
//
// if (sequence is GroupByBuilder.GroupByContext)
// {
// return new CountContext(buildInfo.Parent, sequence, returnType)
// {
// Sql = SqlFunction.CreateCount(returnType, sequence.SelectQuery),
// FieldIndex = -1
// };
// }
// }
if (sequence.SelectQuery.Select.IsDistinct ||
sequence.SelectQuery.Select.TakeValue != null ||
sequence.SelectQuery.Select.SkipValue != null)
{
sequence.ConvertToIndex(null, 0, ConvertFlags.Key);
sequence = new SubQueryContext(sequence);
}
else if (!sequence.SelectQuery.GroupBy.IsEmpty)
{
if (!builder.DataContext.SqlProviderFlags.IsSybaseBuggyGroupBy)
{
sequence.SelectQuery.Select.Add(new SqlValue(0));
}
else
{
foreach (var item in sequence.SelectQuery.GroupBy.Items)
{
sequence.SelectQuery.Select.Add(item);
}
}
sequence = new SubQueryContext(sequence);
}
if (sequence.SelectQuery.OrderBy.Items.Count > 0)
{
if (sequence.SelectQuery.Select.TakeValue == null && sequence.SelectQuery.Select.SkipValue == null)
{
sequence.SelectQuery.OrderBy.Items.Clear();
}
else
{
sequence = new SubQueryContext(sequence);
}
}
var context = new CountContext(buildInfo.Parent, sequence, returnType);
context.Sql = context.SelectQuery;
context.FieldIndex = context.SelectQuery.Select.Add(SqlFunction.CreateCount(returnType, context.SelectQuery), "cnt");
return(context);
}
public SqlFunctionCallExpression(Type type, SqlFunction function, params Expression[] arguments)
: this(type, function, arguments.ToReadOnlyList())
{
}
public DataTable danhMucToChuc()
{
SqlFunction sqlFun = new SqlFunction(HttpContext.Current.Session.GetConnectionString2());
return(sqlFun.GetData(@"SELECT '' maToChucpr,'' tenToChuc union all SELECT maToChucpr,tenToChuc FROM dbo.tblDMToChuc WHERE ngungSD=0 and maDonVipr_sd='" + HttpContext.Current.Session.GetDonVi().maDonVi + "'"));
}
public SqlFunctionCallExpression(Type type, SqlFunction function, IEnumerable<Expression> arguments)
: this(type, function, arguments.ToReadOnlyList())
{
}
protected override void BuildPredicate(ISqlPredicate predicate)
{
var exprExpr = predicate as SelectQuery.Predicate.ExprExpr;
if (exprExpr != null)
{
var leftType = exprExpr.Expr1.SystemType;
var rightType = exprExpr.Expr2.SystemType;
if (IsDateTime(leftType) || IsDateTime(rightType))
{
var l = new SqlFunction(leftType, "$Convert$", SqlDataType.GetDataType(leftType),
SqlDataType.GetDataType(leftType), exprExpr.Expr1);
var r = new SqlFunction(rightType, "$Convert$", SqlDataType.GetDataType(rightType),
SqlDataType.GetDataType(rightType), exprExpr.Expr2);
exprExpr.Expr1 = l;
exprExpr.Expr2 = r;
}
}
base.BuildPredicate(predicate);
}
public SqlFunctionCallExpression(Type type, SqlFunction function, IEnumerable <Expression> arguments)
: this(type, function, arguments.ToReadOnlyList())
{
}
public DataTable danhSachDoiTuongTheoToChuc(string maToChuc)
{
SqlFunction sqlFun = new SqlFunction(HttpContext.Current.Session.GetConnectionString2());
return(sqlFun.GetData(@"SELECT '' chucVu,'' tenCaNhan union all SELECT chucVu=ISNULL(chucVu,''),tenCaNhan FROM dbo.tblDMCaNhan WHERE maToChucpr_sd=N'" + maToChuc + "'"));
}
public void ExpressionType_ReturnsFunction()
{
var function = new SqlFunction("GETDATE");
Assert.Equal(SqlExpressionType.Function, function.ExpressionType);
}
public SqlFunctionCallExpression(Type type, SqlFunction function, IReadOnlyList <Expression> arguments)
: base(type)
{
this.Function = function;
this.Arguments = arguments;
}
public T2 Exists(SelectQuery subQuery)
{
Search.Conditions.Add(new SqlCondition(false, new SqlPredicate.FuncLike(SqlFunction.CreateExists(subQuery))));
return(GetNext());
}
public T2 Some(SelectQuery subQuery)
{
return(Expr(SqlFunction.CreateSome(subQuery)));
}
protected override void BuildFunction(StringBuilder sb, SqlFunction func)
{
func = ConvertFunctionParameters(func);
base.BuildFunction(sb, func);
}
public T2 Any(SelectQuery subQuery)
{
return(Expr(SqlFunction.CreateAny(subQuery)));
}
protected override void BuildFunction(SqlFunction func)
{
func = ConvertFunctionParameters(func);
base.BuildFunction(func);
}
public override ISqlExpression GetSubQuery(IBuildContext context)
{
if (_subQuerySql == null)
{
var args = _methodCall.Method.GetGenericArguments();
var param = Expression.Parameter(args[0], "param");
var expr = _methodCall.Arguments[1];
var condition = Expression.Lambda(Expression.Equal(param, expr), param);
IBuildContext ctx = new ExpressionContext(Parent, Sequence, condition);
ctx = Builder.GetContext(ctx, expr) ?? ctx;
Builder.ReplaceParent(ctx, this);
SqlQuery.Condition cond;
if (Sequence.SqlQuery != SqlQuery &&
(ctx.IsExpression(expr, 0, RequestFor.Field).Result ||
ctx.IsExpression(expr, 0, RequestFor.Expression).Result))
{
Sequence.ConvertToIndex(null, 0, ConvertFlags.All);
var ex = Builder.ConvertToSql(ctx, _methodCall.Arguments[1]);
cond = new SqlQuery.Condition(false, new SqlQuery.Predicate.InSubQuery(ex, false, SqlQuery));
}
else
{
var sequence = Builder.BuildWhere(Parent, Sequence, condition, true);
cond = new SqlQuery.Condition(false, new SqlQuery.Predicate.FuncLike(SqlFunction.CreateExists(sequence.SqlQuery)));
}
_subQuerySql = new SqlQuery.SearchCondition(cond);
}
return(_subQuerySql);
}
protected override void BuildFunction(SqlFunction func)
{
switch (func.Name)
{
case "Coalesce" :
if (func.Parameters.Length > 2)
{
var parms = new ISqlExpression[func.Parameters.Length - 1];
Array.Copy(func.Parameters, 1, parms, 0, parms.Length);
BuildFunction(new SqlFunction(func.SystemType, func.Name, func.Parameters[0],
new SqlFunction(func.SystemType, func.Name, parms)));
return;
}
var sc = new SelectQuery.SearchCondition();
sc.Conditions.Add(new SelectQuery.Condition(false, new SelectQuery.Predicate.IsNull(func.Parameters[0], false)));
func = new SqlFunction(func.SystemType, "Iif", sc, func.Parameters[1], func.Parameters[0]);
break;
case "CASE" : func = ConvertCase(func.SystemType, func.Parameters, 0); break;
case "CharIndex" :
func = func.Parameters.Length == 2?
new SqlFunction(func.SystemType, "InStr", new SqlValue(1), func.Parameters[1], func.Parameters[0], new SqlValue(1)):
new SqlFunction(func.SystemType, "InStr", func.Parameters[2], func.Parameters[1], func.Parameters[0], new SqlValue(1));
break;
case "Convert" :
switch (func.SystemType.ToUnderlying().GetTypeCodeEx())
{
case TypeCode.String : func = new SqlFunction(func.SystemType, "CStr", func.Parameters[1]); break;
case TypeCode.DateTime :
if (IsDateDataType(func.Parameters[0], "Date"))
func = new SqlFunction(func.SystemType, "DateValue", func.Parameters[1]);
else if (IsTimeDataType(func.Parameters[0]))
func = new SqlFunction(func.SystemType, "TimeValue", func.Parameters[1]);
else
func = new SqlFunction(func.SystemType, "CDate", func.Parameters[1]);
break;
default:
if (func.SystemType == typeof(DateTime))
goto case TypeCode.DateTime;
BuildExpression(func.Parameters[1]);
return;
}
break;
}
base.BuildFunction(func);
}
public override ISqlExpression ConvertExpressionImpl(ISqlExpression expression, ConvertVisitor visitor,
EvaluationContext context)
{
expression = base.ConvertExpressionImpl(expression, visitor, context);
if (expression is SqlBinaryExpression be)
{
switch (be.Operation)
{
case "%": return(new SqlFunction(be.SystemType, "Mod", be.Expr1, be.Expr2));
case "&": return(new SqlFunction(be.SystemType, "BitAnd", be.Expr1, be.Expr2));
case "|": return(new SqlFunction(be.SystemType, "BitOr", be.Expr1, be.Expr2));
case "^": return(new SqlFunction(be.SystemType, "BitXor", be.Expr1, be.Expr2));
case "+": return(be.SystemType == typeof(string) ? new SqlBinaryExpression(be.SystemType, be.Expr1, "||", be.Expr2, be.Precedence) : expression);
}
}
else if (expression is SqlFunction func)
{
switch (func.Name)
{
case "Coalesce": return(ConvertCoalesceToBinaryFunc(func, "Nvl"));
case "Convert":
{
var par0 = func.Parameters[0];
var par1 = func.Parameters[1];
var isNull = par1 is SqlValue sqlValue && sqlValue.Value == null;
if (!isNull)
{
switch (Type.GetTypeCode(func.SystemType.ToUnderlying()))
{
case TypeCode.String: return(new SqlFunction(func.SystemType, "To_Char", func.Parameters[1]));
case TypeCode.Boolean:
{
var ex = AlternativeConvertToBoolean(func, 1);
if (ex != null)
{
return(ex);
}
break;
}
case TypeCode.UInt64:
if (func.Parameters[1].SystemType !.IsFloatType())
{
par1 = new SqlFunction(func.SystemType, "Floor", func.Parameters[1]);
}
break;
case TypeCode.DateTime:
if (IsDateDataType(func.Parameters[0], "Date"))
{
if (func.Parameters[1].SystemType == typeof(string))
{
return(new SqlFunction(
func.SystemType,
"Date",
new SqlFunction(func.SystemType, "To_Date", func.Parameters[1], new SqlValue("%Y-%m-%d"))));
}
return(new SqlFunction(func.SystemType, "Date", func.Parameters[1]));
}
if (IsTimeDataType(func.Parameters[0]))
{
return(new SqlExpression(func.SystemType, "Cast(Extend({0}, hour to second) as Char(8))", Precedence.Primary, func.Parameters[1]));
}
return(new SqlFunction(func.SystemType, "To_Date", func.Parameters[1]));
default:
if (func.SystemType.ToUnderlying() == typeof(DateTimeOffset))
{
goto case TypeCode.DateTime;
}
break;
}
}
return(new SqlExpression(func.SystemType, "Cast({0} as {1})", Precedence.Primary, par1, par0));
}
}
}
return(expression);
}