internal FromManyChainer(Chainer prev, TableArgument firstTable, TableArgument secondTable, TableArgument[] otherTables) : base(prev) { CheckNullAndThrow(Arg(() => firstTable, firstTable)); CheckNullAndThrow(Arg(() => secondTable, secondTable)); CheckNullAndThrow(Arg(() => otherTables, otherTables)); if (otherTables.Where(table => table == null).Any()) { Throw(QueryTalkExceptionType.ArgumentNull, "table = null"); } Build = (buildContext, buildArgs) => { StringBuilder sql = Text.GenerateSql(200); ProcessTable(buildContext, buildArgs, firstTable, sql); ProcessTable(buildContext, buildArgs, secondTable, sql); Array.ForEach(otherTables, table => { ProcessTable(buildContext, buildArgs, table, sql); }); TryThrow(buildContext); return(sql.ToString()); }; }
internal EndRankingChainer(Chainer prev) : base(prev) { Build = (buildContext, buildArgs) => { StringBuilder sql = new StringBuilder(); Chainer node = GetPrev <RankingChainer>(); while (node != this) { var append = node.Build(buildContext, buildArgs); TryThrow(buildContext); if (append != null) { sql.Append(append); } node = node.Next; } return(Text.GenerateSql(200) .Append(sql.TrimEnd()) .Append(Text.RightBracket) .ToString()); }; }
internal EndTableChainer(Chainer prev) : base(prev) { var beginTable = prev.GetPrev <BeginTableChainer>(); if (!beginTable.HasColumns) { Throw(QueryTalkExceptionType.InvalidTableDesign, String.Format("table = {0}", beginTable.TableName)); } Build = (buildContext, buildArgs) => { var sql = Text.GenerateSql(100) .NewLine(Text.RightBracket) .Terminate(); if (beginTable.IsFill) { BeginTableChainer.Fill(sql, beginTable.TableName, beginTable.DataView); } return(sql.ToString()); }; }
internal GroupByChainer(Chainer prev, GroupingArgument[] columns) : base(prev) { Query.Clause.GroupBy = this; CheckNullOrEmptyAndThrow(Argc(() => columns, columns)); Query.AddArguments(columns); Build = (buildContext, buildArgs) => { var sql = Text.GenerateSql(100) .NewLine(Text.GroupBy).S() .Append(GroupingArgument.Concatenate(columns, buildContext, buildArgs, false)); if (IsWithCube == true) { sql.NewLine(Text.WithCube); } else if (IsWithCube == false) { sql.NewLine(Text.WithRollup); } TryThrow(buildContext); return(sql.ToString()); }; }
private static void TranslateGraphDeclareTable(DbNode node, Designer root, out StringBuilder nodeTableVar) { var nodeTableVarName = GetTableVarName(node.Index); nodeTableVar = Text.GenerateSql(200) .Append(Text.Declare).S() .Append(nodeTableVarName).Append(Text._As_).Append(Text.Table).S() .Append(Text.LeftBracket); // add columns of all keys // note: // We add all keys in order to assure that all join or where relations will be properly built. bool first = true; foreach (var column in node.Map.GetKeys()) { var nullableDef = column.IsNullable ? Text.Null : Text.NotNull; if (!first) { nodeTableVar.AppendComma().S(); } nodeTableVar.Append(column.Name.Sql).S().Append(column.DataType.Build()).S().Append(nullableDef); first = false; } nodeTableVar.Append(Text.RightBracket) .Terminate(); root.TryAddVariableOrThrow(new Variable(new DataType(DT.TableVariable), nodeTableVarName), Text.Method.DesignTable, false); }
internal DropTempTableIndexChainer(Chainer prev, string table, string index) : base(prev) { CheckNullAndThrow(Arg(() => table, table)); CheckNullAndThrow(Arg(() => index, index)); if (!GetRoot().TempTableExists(table)) { Throw(QueryTalkExceptionType.UnknownTempTable, String.Format("temp table = {0}{1} index = {2}", table, Environment.NewLine, index)); } if (Common.CheckIdentifier(index) != IdentifierValidity.RegularIdentifier) { Throw(QueryTalkExceptionType.InvalidIndexName, ArgVal(() => index, index)); } Build = (buildContext, buildArgs) => { return(Text.GenerateSql(70) .NewLine(Text.DropIndex).S() .Append(index).S() .Append(Text.On).S() .Append(table) .Terminate() .ToString()); }; }
internal ColumnAsChainer(DbColumn column, string alias) : base(null, alias) { if (!CheckNull(Arg(() => column, column))) { return; } if (chainException != null) { return; } _columnName = alias; Build = (buildContext, buildArgs) => { var sql = Text.GenerateSql(20) .Append(column.Build(buildContext, buildArgs)) .Append(Text._As_) .Append(Filter.Delimit(Name)) .ToString(); TryThrow(buildContext); return(sql); }; }
// initalize by value private void Initialize(DeclareArgument value) { var root = GetRoot(); chainMethod = Text.Method.Declare; CheckAndThrow(); var dataType = Mapping.ClrMapping[value.ArgType].DefaultDataType; var variable = new Variable(0, _variableName, dataType, IdentifierType.SqlVariable); root.TryAddVariableOrThrow(variable, chainMethod, false); Build = (buildContext, buildArgs) => { var sql = Text.GenerateSql(50) .NewLine(Text.Declare).S() .Append(_variableName).S() .Append(Text.As).S() .Append(dataType.Build()) .Terminate(); sql.NewLine(Text.Set).S() .Append(_variableName) .Append(Text._Equal_) .Append(value.Build(buildContext, buildArgs)); sql.Terminate(); return(sql.ToString()); }; }
internal string BaseBuildMethod(BuildContext buildContext, BuildArgs buildArgs) { string tableSql = TableArgument.Build(buildContext, buildArgs); TryThrow(buildContext); StringBuilder sql = Text.GenerateSql(30) .NewLine(Keyword); // subquery: if (TableArgument.Original is View) { sql.S().Append(Text.LeftBracket) .Append(tableSql) .NewLine(Text.RightBracket); } // table: else { sql.S().Append(tableSql); } // table with alias if (_alias != null) { return(sql .S().Append(Text.As) .S().Append(Filter.Delimit(_alias.Name)) .ToString()); } else { return(sql.ToString()); } }
private void Initialize(NonSelectColumnArgument column, DataType dataTypeDef) { IsNullable = null; CheckNullAndThrow(Arg(() => column, column)); TryThrow(dataTypeDef.Exception); Prev.GetPrev <BeginTableChainer>().SetHasColumns(); Build = (buildContext, buildArgs) => { var sql = Text.GenerateSql(300); sql.AppendLine(); sql.Append(Text.TwoSpaces); if (!(Prev is BeginTableChainer && !((BeginTableChainer)Prev).IsDesignedByType)) { sql.Append(Text.Comma); } sql.Append(column.Build(buildContext, buildArgs)).S() .Append(dataTypeDef.Build()); TryThrow(column.Exception); if (Identity != null) { sql.S().Append(Text.Identity) .EncloseLeft() .Append(Identity.Seed) .AppendComma() .Append(Identity.Increment) .EncloseRight(); } if (IsNullable == true) { sql.S().Append(Text.Null); } else if (IsNullable == false) { sql.S().Append(Text.NotNull); } if (Check != null) { sql.S().Append(Text.Check) .EncloseLeft().Append(Check.Build(buildContext, buildArgs)).EncloseRight(); TryThrow(buildContext); } if (Default != null) { sql.S().Append(Text.Default) .EncloseLeft().Append(Default.Build(buildContext, buildArgs)).EncloseRight(); TryThrow(buildContext); } return(sql.ToString()); }; }
private void _Body(Column[] columns, bool isDistinct) { IsCollect = false; UseStringAsValue = false; Query.Clause.Select = this; if (columns == null) { _columns = new Column[] { Designer.Null }; } else if (columns.Length == 0) { _columns = new Column[] { Wall.Text.Asterisk }; _isEmpty = true; } else { _columns = columns; } // null column correction for (int i = 0; i < _columns.Length; ++i) { if (_columns[i] == null) { _columns[i] = Designer.Null; } } if (isDistinct) { chainKeyword = Text.SelectDistinct; chainMethod = Text.Method.SelectDistinct; IsDistinct = isDistinct; } else { chainKeyword = Text.Select; chainMethod = Text.Method.Select; } Query.AddArguments(_columns); Build = (buildContext, buildArgs) => { var sql = Text.GenerateSql(200) .NewLine(Keyword).S() .Append(BuildTop(buildContext)); TryAddAsteriskColumns(); sql.Append(Column.Concatenate(_columns, buildContext, buildArgs, _variables)); TryThrow(buildContext); return(sql.ToString()); }; }
internal static string BuildDropNoCheck(string table) { return(Text.GenerateSql(100) .AppendFormat("IF OBJECT_ID(N'tempdb..{0}')", table).S() .Append(Text.IsNotNull).S() .Append(Text.DropTempTable).S().Append(table) .Terminate() .ToString()); }
internal UpdateChainer(Chainer prev, string alias) : base(prev) { Query.SetUpdate(this); alias = base.TryGetTableAlias(alias); var aliasDelimited = Filter.Delimit(alias); Build = (buildContext, buildArgs) => { var sql = Text.GenerateSql(200); var select = GetPrev <SelectChainer>(); TakeTop(select); select.SkipBuild = true; sql.NewLine(BuildTop(buildContext, buildArgs)) .Append(aliasDelimited).S(); var columnsObject = Prev.GetPrev <ColumnsChainer>(); columnsObject.Build(buildContext, buildArgs); columnsObject.SkipBuild = true; var values = ProcessValueArrayInliner(buildContext, buildArgs, select.Columns); if (values == null) { values = select.Columns; } var columns = ProcessValueArrayInliner(buildContext, buildArgs, Columns); if (columns == null) { columns = Columns; } sql.NewLine(Text.Set).S(); var i = 0; foreach (var column in columns) { if (i > 0) { sql.NewLineIndent(Text.Comma).S(); } sql.Append(aliasDelimited) .Append(Text.Dot) .Append(column.Build(buildContext, buildArgs)) .Append(Text._Equal_) .Append(values[i].Build(buildContext, buildArgs)).S(); ++i; } OutputChainer.TryAppendOutput(this, sql, buildContext, buildArgs); return(sql.ToString()); }; }
internal CaseExpressionChainer(Chainer prev) : base(prev) { Build = (buildContext, buildArgs) => { return(Text.GenerateSql(200) .Append(BuildCase(buildContext, buildArgs)).S() .Append(Text.End) .ToString()); }; }
internal static View ConvertScalar <T>(T data, Type clrType) { var type = typeof(T); var sql = Text.GenerateSql(50) .Append(Text.Select).S(); AppendScalarColumn(sql, data, clrType); ViewColumnInfo column = new ViewColumnInfo(Text.SingleColumnName, type, clrType); var view = new View(sql.ToString(), clrType, new ViewColumnInfo[] { column }, 1); return(view); }
private static void AppendScalarColumn(StringBuilder sql, object value, Type clrType) { AppendColumn(sql, Text.GenerateSql(100) .Append(Text.Cast) .Append(Text.LeftBracket) .Append(Mapping.BuildUnchecked(value ?? Designer.Null)) .Append(Text._As_) .Append(Mapping.ClrMapping[clrType].DefaultDataType.Build()) .Append(Text.RightBracket) .ToString(), Text.SingleColumnName); }
internal static View ConvertDataTable(DataTable dataTable) { int rowCount = 0; if (dataTable == null) { throw new QueryTalkException("ViewConverter.ToView", QueryTalkExceptionType.ArgumentNull, "dataTable = null", Text.Method.ToView); } if (dataTable.Columns.Count == 0) { throw new QueryTalkException("ViewConverter.ToView", QueryTalkExceptionType.InvalidDataTable, "type = DataTable", Text.Method.ToView); } var columns = new List <ViewColumnInfo>(); var rows = dataTable.AsEnumerable(); int numberOfColumns = dataTable.Columns.Count; var sqlOuter = Text.GenerateSql(500); var sqlEmpty = Text.GenerateSql(200) .NewLineIndent(Text.Select).S(); int i = 0; i = _BuildDataTableOuterSelect(dataTable, columns, sqlOuter, i); numberOfColumns = i; if (numberOfColumns == 0) { ThrowInvalidDataClassException(typeof(DataTable)); } var sqlInner = Text.GenerateSql(500); bool isEmpty = rows.Count() == 0; bool firstRow = true; if (!isEmpty) { _BuildDataTableValues(ref rowCount, columns, rows, sqlInner, ref firstRow); } else { _BuildDataTableEmpty(dataTable, sqlEmpty); sqlInner.Append(sqlEmpty); } return(Finalizer(typeof(DataTable), sqlOuter, sqlInner, columns.ToArray(), rowCount, isEmpty)); }
internal CollateChainer(DbColumn column, string collation) : base(null) { CheckNullAndThrow(Arg(() => collation, collation)); Build = (buildContext, buildArgs) => { return(Text.GenerateSql(50) .Append(column.Build(buildContext, buildArgs)).S() .Append(Text.Collate).S() .Append(collation) .ToString()); }; }
private string DropTempTables() { var sql = Text.GenerateSql(200); Executable.Arguments.ForEach(argument => { if (argument.DT == DT.TempTable) { sql.NewLine(DropTempTableChainer.BuildDropNoCheck(argument.ParamName)); } }); return((Executable.Arguments.Count > 0) ? sql.ToString() : null); }
private string BuildStoredProc() { return(Text.GenerateSql(1000) .NewLine(Text.Exec).S() .Append(Text.Reserved.ReturnValueOuterParam).Append(Text._Equal_) .Append(_body) .Append(BuildStoredProcArguments()) .NewLine(Text.Set).S() .Append(Text.Reserved.ReturnValueInnerParam) .Append(Text._Equal_) .Append(Text.Reserved.ReturnValueOuterParam) .TerminateSingle() .ToString()); }
internal void BuildProc(ParameterArgument[] arguments) { Arguments = arguments; var root = Mapper.GetRoot(); DbMapping.CreateParams(root, this); string parameters = null; if (arguments != null && arguments.Length > 0) { var i = 0; parameters = String.Join(",", root.AllParams .Select(p => { var s = p.Name; if (arguments[i] == null) { arguments[i] = Designer.Null; } if (arguments[i].IsArgumentOutput) { s = String.Format("{0} {1}", s, Text.Output); } ++i; return(s); } )); } string sql = Text.GenerateSql(100) .NewLine(Text.Exec).S() .Append(Text.Reserved.ReturnValueOuterParam).Append(Text._Equal_) .Append(Map.Name.Sql).S() .Append(parameters).Terminate() .NewLine(Text.Set).S() // SET @_ri = @_ro; .Append(Text.Reserved.ReturnValueInnerParam) .Append(Text._Equal_) .Append(Text.Reserved.ReturnValueOuterParam) .Terminate() .ToString(); Mapper.SetSql(sql); }
internal static string GetParamDeclaration(ParameterArgument argument, bool check, bool outer) { var dt = argument.DT; var dbt = argument.DataType; if (dt.IsNameType()) { var sql = Text.GenerateSql(500) .Append(argument.NameTypeSql); if (!outer && dt == DT.Udtt) { sql.S().Append(Text.Readonly); } return(sql.ToString()); } if (dbt != null) { if (dbt.Precision != 0) { if (check) { CriticalCheckArgumentSize(argument); } return(String.Format("{0}({1},{2})", Mapping.SqlMapping[dt].Sql, dbt.Precision, dbt.Scale)); } else if (dbt.Length != 0) { if (check) { CriticalCheckArgumentSize(argument); } return(String.Format("{0}({1})", Mapping.SqlMapping[dt].Sql, dbt.Length)); } // if precision/size is not declared, infer it from the value else { return(Mapping.SqlMapping[dt].SqlByValue(argument.Value)); } } else { return(Mapping.SqlMapping[dt].SqlByValue(argument.Value)); } }
internal OrderedChainer(System.String identifier, SortOrder sortOrder) : base(null) { CheckNull(Arg(() => identifier, identifier)); Build = (buildContext, buildArgs) => { var sql = Text.GenerateSql(100) .Append(Filter.DelimitMultiPartOrParam(identifier, IdentifierType.ColumnOrParam, out chainException)).S() .Append(sortOrder.ToUpperCase()) .ToString(); buildContext.TryTakeException(chainException); return(sql); }; }
internal DropTempTableChainer(Chainer prev, string table) : base(prev) { CheckNullAndThrow(Arg(() => table, table)); Build = (buildContext, buildArgs) => { var sql = Text.GenerateSql(100) .NewLine(BuildDrop(table, GetRoot(), out chainException)) .ToString(); TryThrow(); return(sql); }; }
private Func <BuildContext, BuildArgs, string> BuildViewMethod(View view, bool sign) { return((buildContext, buildArgs) => { var sql = Text.GenerateSql(50) .NewLine(Text.If).S().Append(sign ? null : Text.Not_) .Append(Text.Exists).S() .Append(Text.LeftBracket) .Append(view.Build(buildContext, buildArgs)) .Append(Text.RightBracket) .NewLine(Text.Begin) .ToString(); TryThrow(buildContext); return sql; }); }
// unpivot internal PivotChainer(Chainer prev, NonSelectColumnArgument valueColumn, NonSelectColumnArgument rotatingColumn, string[] rotatedColumns) : base(prev, true) { _method = Text.Method.Unpivot; CheckNullAndThrow(Arg(() => valueColumn, valueColumn)); CheckNullAndThrow(Arg(() => rotatingColumn, rotatingColumn)); CheckNullOrEmptyAndThrow(Argc(() => rotatedColumns, rotatedColumns)); Array.ForEach(rotatedColumns, column => { if (column == null) { Throw(QueryTalkExceptionType.ArgumentNull, "rotatedColumn = null"); } }); Build = (buildContext, buildArgs) => { // build IN values List <string> columns = new List <string>(); Array.ForEach(rotatedColumns, column => { columns.Add(Filter.Delimit(column)); }); var sql = Text.GenerateSql(300) .NewLine(Text.Unpivot).S() .Append(Text.LeftBracket) .Append(valueColumn.Build(buildContext, buildArgs)).S() .Append(Text.For).S() .Append(rotatingColumn.Build(buildContext, buildArgs)).S() .NewLineIndent(Text.In).S() .Append(Text.LeftBracket) .Append(String.Join(Text.Comma, columns)) .Append(Text.RightBracket).Append(Text.RightBracket) .Append(Text._As_).Append(Filter.Delimit(Alias.Name)) .ToString(); TryThrow(buildContext); return(sql); }; }
private string BuildExecProc(BuildContext buildContext, BuildArgs buildArgs) { // assign the current node of the build context buildContext.Current = _innerObject; if (ReturnVariable != null) { return(Text.GenerateSql(1000) .Append(Executable.Build(buildContext, buildArgs)) .NewLine(Text.Set).S() .Append(ReturnVariable).Append(Text._Equal_).Append(Text.Reserved.ReturnValueOuterParam) .Terminate() .ToString()); } else { return(Executable.Build(buildContext, buildArgs)); } }
internal Expression BuildRKPredicate(object[] rkValues, int index, bool parameterization = true) { StringBuilder builder = null; int i = 0; foreach (var column in SortedRKColumns) { string expressionStr; if (rkValues[i] == null) { expressionStr = String.Format("([{0}].{1} IS NULL)", index, column.Name); } else { if (parameterization) { expressionStr = String.Format("([{0}].{1} = @o{2})", index, column.Name, i + 1); } else { var value = rkValues[i]; expressionStr = String.Format("([{0}].{1} = {2})", index, column.Name, Mapping.Build(value, column.DataType)); } } if (builder == null) { builder = Text.GenerateSql(200); } else { builder.Append(Text._And_); } builder.Append(expressionStr); ++i; } return(builder.ToString().E()); }
internal CollateChainer(Chainer prev, string collation) : base(prev) { CheckNullAndThrow(Arg(() => collation, collation)); Build = (buildContext, buildArgs) => { if (prev is IColumnName) { _columnName = ((IColumnName)prev).ColumnName; } return(Text.GenerateSql(50) .Append(prev.Build(buildContext, buildArgs)).S() .Append(Text.Collate).S() .Append(collation) .ToString()); }; }
// Wrap SQL code with EXECUTE AS/REVERT wrapper. internal void Impersonate(string userToImpersonate) { if (userToImpersonate == null) { return; } _userToImpersonate = userToImpersonate; var sql = Text.GenerateSql(1000) .Append(Text.ExecuteAsUser).Append(Text._Equal_) .Append(Filter.DelimitQuoteNonAsterix(_userToImpersonate)) .Terminate() .NewLine(_sql) .NewLine(Text.Revert) .Terminate(); _sql = sql.ToString(); }