public static Statement CreateStatement(Update update, IEntityMapping mapping) { var writer = new SqlWriter(); var parameters = new Dictionary <string, object>(); writer.Update.QuotedName(update.Table.Name).Set. ParameterAssignmentList(x => x.Comma.Flush(), update.Assignment.ToDictionary(x => x.Key, x => parameters.AddWithUniquelyNamedKey(x.Value))); var where = WhereWriter <TEntity> .CreateStatement(update.Where, mapping); writer.Where.Write(where.Text); parameters.AddRange(where.Parameters); return(new Statement(writer.ToString(), Statement.StatementType.Text, Statement.ResultType.None, parameters)); }
public static Statement CreateAddNonClusteredIndexStatement(string tableName, params Index.Column[] columns) { var indexName = string.Format("IX_{0}_{1}", tableName, string.Join("_", columns.Select(x => x.Name))); var writer = SqlWriter.CreateWriter().Create.NonClustered.Index.QuotedName(indexName).On.QuotedName(tableName).OpenBlock.Trim(); var first = true; foreach (var column in columns) { if (!first) { writer.Trim().Comma.Flush(); } writer.QuotedName(column.Name).Do(column.Descending, x => x.Descending.Flush(), x => x.Ascending.Flush()).Flush(); first = false; } writer.Trim().CloseBlock.Flush(); return(new Statement(writer.ToString(), Statement.StatementType.Text, Statement.ResultType.None)); }
public static Statement CreateSharedColumnsStatement(Select select, Table target = null) { var sourceTables = select.GetSourceTables().Select(x => x.From.Table.Name).ToList(); if (target != null) { sourceTables.Add(target.Name); } var targetName = target != null ? target.Name : sourceTables.First(); var statement = CreateColumnsIntersectionStatement(sourceTables); var writer = WriteSelectColumns(SqlWriter.CreateWriter(), sourceTables). Join.OpenBlock.Trim().Write(statement.Text).Trim().CloseBlock.SubQueryAlias.On. SubQueryColumn(System.Columns.Name).Equal.Write(System.Columns.Aliased.Name).And. Write(System.Columns.Aliased.ObjectId).Equal.ObjectId(targetName); return(new Statement(writer.ToString(), Statement.StatementType.Text, Statement.ResultType.Multiple)); }
public static Statement CreateStatement(Sync sync, IEntityMapping mapping) { var writer = new SqlWriter(); IDictionary <string, object> parameters = new Dictionary <string, object>(); var fields = sync.Target.Projection.Zip(sync.Source.Projection, (t, s) => new { Target = t.Projection, Source = s.Projection }) .Where(x => !sync.ExcludedFields.Any(y => (y.HasKey ? y.Key : y.Name) == (x.Target.Field.HasKey ? x.Target.Field.Key : x.Target.Field.Name))).ToList(); writer.Update.QuotedName(sync.Target.From.Alias).Set .ExpressionList(x => x.Comma.Flush(), fields, (f, s) => s. Write(ProjectionWriter <TEntity> .CreateStatement(f.Target, mapping).MergeParameters(parameters).Text).Equal. Write(ProjectionWriter <TEntity> .CreateStatement(f.Source, mapping).MergeParameters(parameters).Text)) .From.QuotedName(sync.Target.From.Table.Name).QuotedName(sync.Target.From.Alias).Inner.Join .QuotedName(sync.Source.From.Table.Name).QuotedName(sync.Source.From.Alias) .On.Write(ProjectionWriter <TEntity> .CreateStatement(sync.TargetKey, mapping).MergeParameters(parameters).Text).Equal .Write(ProjectionWriter <TEntity> .CreateStatement(sync.SourceKey, mapping).MergeParameters(parameters).Text) .Do(sync.Source.HasWhere, x => x.And.Write(WhereWriter <TEntity> .CreateStatement(sync.Source.Where, mapping).MergeParameters(parameters).Text)) .Do(sync.Target.HasWhere, x => x.Where.Write(WhereWriter <TEntity> .CreateStatement(sync.Target.Where, mapping).MergeParameters(parameters).Text)); return(new Statement(writer.ToString(), Statement.StatementType.Text, Statement.ResultType.None, parameters)); }
public static Statement CreateGetIndexesStatement(string tableName) { var writer = new SqlWriter(); writer.Select. Write(System.Indexes.Aliased.Name).Trim().Comma. Write(System.Indexes.Aliased.Type).Trim().Comma. Write(System.Indexes.Aliased.IsUnique).Trim().Comma. Write(System.Indexes.Aliased.IsPrimaryKey).Trim().Comma. Write(System.Columns.Aliased.Name).As.QuotedName(SqlWriter.Aliases.ColumnName).Trim().Comma. Write(System.IndexColumns.Aliased.IsDescendingKey). From.Write(System.Indexes.TableName).Write(System.Indexes.TableAlias). Join.Write(System.IndexColumns.TableName).Write(System.IndexColumns.TableAlias).On. Write(System.Indexes.Aliased.ObjectId).Equal.Write(System.IndexColumns.Aliased.ObjectId).And. Write(System.Indexes.Aliased.IndexId).Equal.Write(System.IndexColumns.Aliased.IndexId). Join.Write(System.Columns.TableName).Write(System.Columns.TableAlias).On. Write(System.IndexColumns.Aliased.ObjectId).Equal.Write(System.Columns.Aliased.ObjectId).And. Write(System.IndexColumns.Aliased.ColumnId).Equal.Write(System.Columns.Aliased.ColumnId). Where.Write(System.Indexes.Aliased.ObjectId).Equal.ObjectId(tableName). OrderBy.Write(System.Indexes.Aliased.Name).Flush(); return(new Statement(writer.ToString(), Statement.StatementType.Text, Statement.ResultType.Multiple)); }
private static string BuildOrderBy(IEnumerable <OrderBy> orderBy, IEntityMapping mapping, IDictionary <string, object> parameters) { if (orderBy == null || !orderBy.Any()) { return(""); } var writer = new SqlWriter(); orderBy.Select((x, i) => new { Last = i == orderBy.Count() - 1, OrderBy = x }).ToList().ForEach(x => { if (x.OrderBy.Type == OrderBy.SourceType.Operator) { writer.Case.When.Write(BuildOperators(x.OrderBy.Operator, mapping, parameters)).Then .Value(1, SqlDbType.Int).Else.Value(0, SqlDbType.Int).End.Flush(); } else { writer.Write(BuildProjection(x.OrderBy.Projection, mapping, parameters)); } writer.Do(x.OrderBy.Order == Order.Descending, y => y.Descending.Flush(), y => y.Ascending.Flush()).Do(!x.Last, y => y.Trim().Comma.Flush()); }); return(writer.ToString()); }
public static Statement CreateTableCreateStatement(string tableName, params Column[] columns) { var writer = SqlWriter.CreateWriter().Create.Table.QuotedName(tableName).OpenBlock.Trim(); var first = true; foreach (var column in columns) { if (!first) { writer.Trim().Comma.Flush(); } WriteColumnDefinition(writer, column); first = false; } var primaryKey = columns.FirstOrDefault(x => x.Key != Column.KeyType.None); if (primaryKey != null) { writer.Trim().Comma.PrimaryKeyConstraint( tableName, primaryKey.Name, primaryKey.Key == Column.KeyType.ClusteredPrimaryKey); } writer.Trim().CloseBlock.Flush(); return(new Statement(writer.ToString(), Statement.StatementType.Text, Statement.ResultType.None)); }
public static Statement CreateStatement(Select select, IEntityMapping mapping, IEnumerable <string> projectionOverride = null, bool noLock = false) { var sql = new SqlWriter(); var parameters = new Dictionary <string, object>(); var projection = projectionOverride ?? BuildProjection(select, mapping, parameters); var whereClause = BuildWhereClause(select, mapping, parameters); var orderByClause = select.HasOrderBy ? BuildOrderBy(select.OrderBy, mapping, parameters) : null; Action <SqlWriter> writeProjection = x => x.Do(projection != null, y => y.FieldList(z => z.Comma.Flush(), projection), y => y.Wildcard.Flush()); if (select.Any) { sql.Select.Cast().Trim().OpenBlock.Trim().Case.When.Exists.OpenBlock.Trim(); } if (select.From.IsTable || select.HasConditions) { sql.Select.Flush(); if (select.First || select.FirstOrDefault) { sql.Top(1); } else if (select.Single) { sql.Top(2); } else if (select.HasTop && !select.HasStart) { sql.Do(select.TopType == Select.TopValueType.Count, x => x.Top(select.Top), x => x.TopPercent(select.Top)); } if (select.Count) { sql.CountWildcard.Flush(); } else { sql.Do(writeProjection); } sql.From.Flush(); if (select.HasStart) { sql.OpenBlock.Trim().Select.Do(writeProjection).Trim().Comma. RowNumber().Over.OpenBlock.Trim().OrderBy. Do(select.HasOrderBy, x => x.Write(orderByClause), x => x.Do(projection != null, writeProjection, y => y.QuotedName(mapping.Key.GetColumnName()))).Trim(). CloseBlock.As.RowNumberAlias.From.Flush(); } if (select.HasDuplicates) { var duplicateProjection = BuildProjection(select.Duplicates.Distinct, mapping, parameters); sql.OpenBlock.Trim().Select.Do(writeProjection).Trim().Comma. RowNumber().Over.OpenBlock.Trim().Partition.By.Write(duplicateProjection).OrderBy.Write(BuildOrderBy(select.Duplicates.OrderBy, mapping, parameters)). Trim().CloseBlock.As.PartitionAlias.From.Flush(); } else if (select.HasDistinct) { var distinctProjection = BuildProjections(select.Distinct.Select(x => x.Projection), mapping, parameters); sql.OpenBlock.Trim().Select.Do(writeProjection).Trim().Comma. RowNumber().Over.OpenBlock.Trim().Partition.By.ExpressionList(z => z.Comma.Flush(), distinctProjection).OrderBy. Write(BuildOrderBy(select.Distinct.Any(x => x.HasOrder) ? select.Distinct.Where(x => x.HasOrder).Select(x => x.Order) : select.Distinct.Select(x => new OrderBy { Type = OrderBy.SourceType.Projection, Projection = x.Projection, Order = Order.Ascending }), mapping, parameters)).Trim(). CloseBlock.As.PartitionAlias.From.Flush(); } } switch (select.From.Type) { case Data.DataType.Table: sql.QuotedName(select.From.Table.Name).Write(select.From.Alias); if (noLock) { sql.With(x => x.NoLock.Flush()); } break; case Data.DataType.Query: var first = true; if (select.HasConditions) { sql.OpenBlock.Trim().Flush(); } foreach (var subQuery in select.From.Queries.Select(x => CreateStatement(x, mapping, projection, noLock))) { sql.Do(!first, x => x.Union.Flush()).Write(subQuery.Text).Flush(); parameters.AddRange(subQuery.Parameters); first = false; } if (select.HasConditions) { sql.Trim().CloseBlock.As.Write(select.From.Alias).Flush(); } break; } if (select.From.IsTable || select.HasConditions) { if (select.HasWhere || select.HasSetOperations) { sql.Where.Write(whereClause).Flush(); } if (select.Randomize) { sql.OrderBy.NewId(); } // The reason why we dont do an order by if there is a start is because the order by is // already specified in the row number definition. So we dont need to specify it again. else if (select.HasOrderBy && !select.HasStart && !select.HasDistinct) { sql.OrderBy.Write(orderByClause); } if (select.HasDuplicates) { sql.Trim().CloseBlock.As.Write(select.From.Alias).Where.PartitionAlias.GreaterThan.Value(1, SqlDbType.Int).Flush(); } else if (select.HasDistinct) { sql.Trim().CloseBlock.As.Write(select.From.Alias).Where.PartitionAlias.Equal.Value(1, SqlDbType.Int).Flush(); } if (select.HasStart) { sql.Trim().CloseBlock.As.Write(select.From.Alias).Where.RowNumberAlias.Flush(); if (select.HasTop && select.HasStart) { sql.Between(select.Start, select.Start + (select.Top - 1)); } else { sql.GreaterThanOrEqual.Value(select.Start, SqlDbType.Int); } } } if (select.Any) { sql.Trim().CloseBlock.Then.Value(1, SqlDbType.Bit).Else.Value(0, SqlDbType.Bit).End.As.Write(DataTypes.Bit.SqlName).Trim().CloseBlock.Flush(); } return(new Statement(sql.ToString(), Statement.StatementType.Text, GetResultType(select), parameters)); }
public static SqlWriter WriteSelectColumns(SqlWriter writer, IEnumerable <string> tables = null) { return(writer.Select. Write(System.Columns.Aliased.Name).Trim().Comma. Write(System.Columns.Aliased.SystemTypeId).Trim().Comma. Cast(x => x.Case.When.Write(System.Columns.Aliased.SystemTypeId).In. OpenBlock.Trim().Write(DataTypes.NChar.SqlId).Trim().Comma.Write(DataTypes.NVarChar.SqlId).Trim().Comma.Write(DataTypes.NText.SqlId).Trim().CloseBlock. Then.Write(System.Columns.Aliased.MaxLength).Divide.Value(2, SqlDbType.Int).Else.Write(System.Columns.Aliased.MaxLength).End.Flush(), typeof(short), null, null, null). As.QuotedName(System.Columns.MaxLength).Trim().Comma. Write(System.Columns.Aliased.IsNullable).Trim().Comma. Write(System.Columns.Aliased.IsIdentity).Trim().Comma. Cast(x => x.Trim().OpenBlock.Trim(). Case.ObjectDefinition(y => y.Write(System.Columns.Aliased.DefaultObjectId).Flush()). When.GetDateColumnDefault.Then.True. When.NewIdColumnDefault.Then.True. When.NewSequentialIdColumnDefault.Then.True. Else.False.End.Trim(). CloseBlock.Flush(), typeof(bool), null, null, null).As.QuotedName(SqlWriter.Aliases.IsAutoGenerated).Trim().Comma. Case.ObjectDefinition(y => y.Write(System.Columns.Aliased.DefaultObjectId).Flush()). When.GetDateColumnDefault.Then.Null. When.NewIdColumnDefault.Then.Null. When.NewSequentialIdColumnDefault.Then.Null. Else.Replace(x => x.Replace(y => y.ObjectDefinition(z => z.Write(System.Columns.Aliased.DefaultObjectId).Flush()), y => y.QuotedString("("), y => y.QuotedString("")), x => x.QuotedString(")"), x => x.QuotedString("")).End. As.QuotedName(SqlWriter.Aliases.DefaultValue).Trim().Comma. IsNull(x => x.Write(System.Indexes.Aliased.IsPrimaryKey).Flush(), x => x.False.Flush()).As.QuotedName(System.Indexes.IsPrimaryKey).Trim().Comma. Cast(x => x.Trim().OpenBlock.Trim(). Case.Write(System.Indexes.Aliased.Type). When.Value(1, SqlDbType.Int).Then.True. Else.False.End.Trim().CloseBlock.Flush(), typeof(bool), null, null, null).As.QuotedName(SqlWriter.Aliases.IsPrimaryKeyClustered).Trim().Comma. Write(System.Columns.Aliased.Precision).Trim().Comma. Write(System.Columns.Aliased.Scale).Trim().Comma. Write(System.ComputedColumns.Aliased.Definition).As.QuotedName(SqlWriter.Aliases.Computation).Trim().Comma. Write(System.ComputedColumns.Aliased.IsPersisted).As.QuotedName(SqlWriter.Aliases.PersistedComputation). Do(tables != null && tables.Any(), sql => sql.Trim().Comma.Cast(z => z.Case.When.Write(System.Columns.Aliased.SystemTypeId).LessThan.OpenBlock.Trim(). Select.Max(x => x.QuotedName(System.Columns.SystemTypeId)).From.Write(System.Columns.TableName). Where.QuotedName(System.Columns.Name).Equal.SubQueryColumn(System.Columns.Name).And. QuotedName(System.Columns.SystemTypeId).In.OpenBlock.Trim().ExpressionList(x => x.Comma.Flush(), x => x.DataTypeId(DataTypes.Char.SqlId), x => x.DataTypeId(DataTypes.VarChar.SqlId), x => x.DataTypeId(DataTypes.Text.SqlId), x => x.DataTypeId(DataTypes.NChar.SqlId), x => x.DataTypeId(DataTypes.NVarChar.SqlId), x => x.DataTypeId(DataTypes.NText.SqlId)).Trim().CloseBlock.And. QuotedName(System.Columns.ObjectId).In.OpenBlock.Trim().ExpressionList(x => x.Comma.Flush(), tables.Select <string, Action <SqlWriter> >(x => y => y.ObjectId(x))).Trim().CloseBlock.Trim().CloseBlock. Then.True.Else.False.End.Flush(), typeof(bool), null, null, null).As.QuotedName(SqlWriter.Aliases.IsNarrowing)). From.OpenBlock.OpenBlock.OpenBlock.Write(System.Columns.TableName).Write(System.Columns.TableAlias). Left.Join.Write(System.IndexColumns.TableName).Write(System.IndexColumns.TableAlias).On. Write(System.Columns.Aliased.ColumnId).Equal.Write(System.IndexColumns.Aliased.ColumnId).And. Write(System.Columns.Aliased.ObjectId).Equal.Write(System.IndexColumns.Aliased.ObjectId).CloseBlock. Left.Join.Write(System.Indexes.TableName).Write(System.Indexes.TableAlias).On. Write(System.Indexes.Aliased.IndexId).Equal.Write(System.IndexColumns.Aliased.IndexId).And. Write(System.Indexes.Aliased.ObjectId).Equal.Write(System.IndexColumns.Aliased.ObjectId).CloseBlock. Left.Join.Write(System.ComputedColumns.TableName).Write(System.ComputedColumns.TableAlias).On. Write(System.Columns.Aliased.ColumnId).Equal.Write(System.ComputedColumns.Aliased.ColumnId).And. Write(System.Columns.Aliased.ObjectId).Equal.Write(System.ComputedColumns.Aliased.ObjectId).CloseBlock); }
public static Statement CreateRemoveNonClusteredIndexStatement(string tableName, string indexName) { var writer = SqlWriter.CreateWriter().IfIndexExists(tableName, indexName).Drop.Index.QuotedName(indexName).On.QuotedName(tableName); return(new Statement(writer.ToString(), Statement.StatementType.Text, Statement.ResultType.None)); }
public static Statement CreateRemoveColumnStatement(string tableName, string columnName) { var writer = SqlWriter.CreateWriter().IfColumnExists(tableName, columnName).Alter.Table.QuotedName(tableName).Drop.Column.QuotedName(columnName); return(new Statement(writer.ToString(), Statement.StatementType.Text, Statement.ResultType.None)); }
private static void WriteColumnDefinition(SqlWriter writer, Column column) { writer.ColumnDefinition(column.Name, column.Type, column.SqlType, column.Length, column.Precision, column.Scale, column.Key != Column.KeyType.None, column.IsIdentity, column.IsNullable, column.IsAutoGenerated, column.Computation, column.ComputationPersisted, column.DefaultValue); }
public static Statement CreateDeleteTableStatement(string tableName) { var writer = SqlWriter.CreateWriter().If.TableExists(tableName).Drop.Table.QuotedName(tableName); return(new Statement(writer.ToString(), Statement.StatementType.Text, Statement.ResultType.None)); }
public static Statement CreateProcedureExistsStatement(string name) { var writer = SqlWriter.CreateWriter().Select.ProcedureExistsValue(name); return(new Statement(writer.ToString(), Statement.StatementType.Text, Statement.ResultType.Scalar)); }