protected override void WriteArguments(SqlWriter writer) { if (isMax) { writer.WriteKeyword("MAX"); } else { writer.WriteScript(extension, WhitespacePadding.None); } }
internal void WriteNonGlobalInternal(SqlWriter writer) { if (!quote) { writer.Write(Value); } else { base.WriteToInternal(writer, false); } }
public override void WriteTo(SqlWriter writer) { writer.WriteScript(tableNameQualified, WhitespacePadding.None); writer.Write('.'); writer.WriteScript(columnName, WhitespacePadding.None); writer.Write('.'); base.WriteTo(writer); }
public override void WriteTo(SqlWriter writer) { WriteCommentsTo(writer); writer.WriteKeyword("OPTION "); writer.Write('('); writer.WriteScriptSequence(options, WhitespacePadding.None, w => w.Write(", ")); writer.Write(')'); }
public override void WriteTo(SqlWriter writer) { base.WriteTo(writer); writer.WriteKeyword("DEFAULT "); writer.Write('('); writer.WriteScript(defaultValue, WhitespacePadding.None); writer.Write(')'); }
public static Statement CreateAddColumnStatement(string tableName, Column column) { var writer = new SqlWriter(); writer.Alter.Table.QuotedName(tableName).Add.Flush(); WriteColumnDefinition(writer, column); return new Statement(writer.ToString(), Statement.StatementType.Text, Statement.ResultType.None); }
public override void WriteTo(SqlWriter writer) { WriteCommentsTo(writer); writer.WriteFunction("UPDATE"); writer.Write('('); writer.WriteScript(columnName, WhitespacePadding.None); writer.Write(')'); }
/// <summary> /// Initializes a new instance of the <see cref="ProcedureGenerator"/> class. /// </summary> /// <param name="table">The table.</param> public ProcedureGenerator(DatabaseTable table) : base(table) { SqlWriter = new SqlWriter(table, SqlType.Oracle); SqlWriter.InStoredProcedure = true; PackageName = "PACK_" + TableName; }
protected internal override void WriteToInternal(SqlWriter writer, bool isPartOfQualifiedName) { Debug.Assert(!isPartOfQualifiedName); if (global) { writer.WriteKeyword("GLOBAL "); } WriteNonGlobalInternal(writer); }
public override void WriteTo(SqlWriter writer) { writer.WriteFunction("COUNT"); writer.Write('('); writer.WriteDuplicateRestriction(restriction, WhitespacePadding.SpaceAfter); writer.WriteScript(expression, WhitespacePadding.None); writer.Write(')'); }
protected internal override void WriteToInternal(SqlWriter writer, bool isPartOfQualifiedName) { if (IsWildcard) { writer.Write(Value); } else { base.WriteToInternal(writer, isPartOfQualifiedName); } }
public override void WriteTo(SqlWriter writer) { WriteCommentsTo(writer); writer.WriteScript(valueExpression, WhitespacePadding.None); base.WriteTo(writer); writer.WriteKeyword(" IN "); writer.WriteScript(tuple, WhitespacePadding.None); }
public override void WriteTo(SqlWriter writer) { base.WriteTo(writer); writer.WriteKeyword("CHECK "); writer.WriteScript(replication, WhitespacePadding.SpaceAfter); writer.WriteScript(predicate, WhitespacePadding.None); writer.Write(')'); }
protected virtual void WriteToInternal(SqlWriter writer) { writer.WriteScript(function, WhitespacePadding.None); if (functions.Count > 0) { writer.Write('.'); writer.WriteScriptSequence(functions, WhitespacePadding.None, w => w.Write('.')); } }
public override void WriteTo(SqlWriter writer) { writer.WriteKeyword("FOR UPDATE"); if (columns.Count > 0) { writer.WriteKeyword(" OF "); writer.WriteScriptSequence(columns, WhitespacePadding.None, w => w.Write(", ")); } }
public override void WriteTo(SqlWriter writer) { writer.WriteScript(serverName, WhitespacePadding.None, null, w => w.Write('.')); writer.WriteScript(databaseName, WhitespacePadding.None, null, w => w.Write('.')); writer.WriteScript(schemaName, WhitespacePadding.None, null, w => w.Write('.')); writer.WriteScript(tableName, WhitespacePadding.None); base.WriteTo(writer); }
public override void WriteTo(SqlWriter writer) { base.WriteTo(writer); writer.WriteKeyword("REFERENCES "); writer.WriteScript(tableName, WhitespacePadding.None); writer.WriteScript(refColumnName, WhitespacePadding.SpaceBefore); writer.WriteScriptSequence(keyActions, WhitespacePadding.SpaceBefore, null); }
protected internal override void WriteToInternal(SqlWriter writer, bool isPartOfQualifiedName) { if (isPartOfQualifiedName || (!IsBuiltinType)) { base.WriteToInternal(writer, isPartOfQualifiedName); } else { writer.WriteType(Value); } }
/// <summary> /// We delegate the writing of the fragment to the appropriate type. /// </summary> /// <param name="writer"></param> /// <param name="sqlGenerator"></param> public void WriteSql(SqlWriter writer, SqlGenerator sqlGenerator) { if (null != _sqlFragments) { foreach (object o in _sqlFragments) { string str = (o as String); if (null != str) { writer.Write(str); } else { ISqlFragment sqlFragment = (o as ISqlFragment); if (null != sqlFragment) { sqlFragment.WriteSql(writer, sqlGenerator); } else { throw new InvalidOperationException(); } } } } }
public override void WriteTo(SqlWriter writer) { WriteCommentsTo(writer); writer.WriteKeyword("CREATE XML SCHEMA COLLECTION "); writer.WriteScript(xmlSchemaCollectionName, WhitespacePadding.None); writer.WriteKeyword(" AS "); writer.WriteScript(expression, WhitespacePadding.None); }
public override void WriteTo(SqlWriter writer) { writer.WriteKeyword("IDENTITY"); writer.Write('('); writer.WriteScript(typeName, WhitespacePadding.None); writer.WriteScript(seed, WhitespacePadding.None, w => w.Write(", "), null); writer.WriteScript(increment, WhitespacePadding.None, w => w.Write(", "), null); writer.Write(')'); }
public override void WriteTo(SqlWriter writer) { writer.WriteFunction("CAST"); writer.Write('('); writer.WriteScript(expression, WhitespacePadding.None); writer.WriteKeyword(" AS "); writer.WriteScript(typeName, WhitespacePadding.None); writer.Write(')'); }
public override sealed void WriteTo(SqlWriter writer) { writer.WriteScript(parameterName, WhitespacePadding.SpaceAfter); writer.WriteScript(parameterTypeName, WhitespacePadding.None); WriteParameterQualifiers(writer); if (readOnly) { writer.WriteKeyword(" READONLY"); } }
public AnyStatement(Identifier identifier, Sequence<Expression> expressions) { using (StringWriter stringWriter = new StringWriter()) { SqlWriter statementWriter = new SqlWriter(stringWriter, DatabaseEngine.Unknown); statementWriter.WriteIdentifier(identifier.Value); statementWriter.WriteScriptSequence(expressions, WhitespacePadding.SpaceBefore, null); statementText = stringWriter.ToString(); } }
public override void WriteTo(SqlWriter writer) { WriteCommentsTo(writer); writer.Write('('); using (writer.Indent()) { writer.WriteScript(expression, WhitespacePadding.None); } writer.Write(')'); }
public override void WriteTo(SqlWriter writer) { WriteCommentsTo(writer); writer.WriteScript(functionCall, WhitespacePadding.None); writer.WriteKeyword(" OVER "); writer.Write('('); writer.WriteScript(rankingArguments, WhitespacePadding.None); writer.Write(')'); }
public ProcedureGenerator(DatabaseTable table) : base(table) { SqlWriter = new SqlWriter(table, SqlType.Db2); SqlWriter.InStoredProcedure = true; SqlWriter.FormatParameter = x => { return "p_" + x; }; FormatParameter = SqlWriter.FormatParameter; }
/// <summary> /// Write out the SKIP part of sql select statement /// It basically writes SKIP (X). /// </summary> /// <param name="writer"></param> /// <param name="sqlGenerator"></param> public void WriteSql(SqlWriter writer, SqlGenerator sqlGenerator) { writer.Write("SKIP ("); this.SkipCount.WriteSql(writer, sqlGenerator); writer.Write(")"); writer.Write(" "); }
public void WriteStartUpdate_WhenDisposed_ThrowsObjectDisposed() { StringBuilder builder = new StringBuilder(); SqlWriter writer = new SqlWriter(builder); writer.Dispose(); AssertEx.Throws<ObjectDisposedException>(() => writer.WriteStartUpdate()); }
public override void WriteTo(SqlWriter writer) { // we ignore the fill factor when computing a hash, so that differences due to fill factor only are ignored if (writer.Mode != SqlWriterMode.ForHashing) { writer.WriteKeyword("WITH FILLFACTOR"); writer.Write('='); writer.WriteScript(fillfactor, WhitespacePadding.None); } }
public void WriteTable_WhenInsertStatement_WritesTable() { StringBuilder builder = new StringBuilder(); using (SqlWriter writer = new SqlWriter(builder)) { writer.WriteStartInsert(); writer.WriteStartInto(); writer.WriteTable("Users"); Assert.AreEqual(SqlWriterState.StartInto, writer.WriteState); } Assert.AreEqual("INSERT INTO [Users]", builder.ToString()); }
public void TestGeneratedSqlForInsert() { //arrange var table = LoadCategoriesFromNorthwind(); var writer = new SqlWriter(table, SqlType.SqlServer); var sql = writer.InsertSql(); int identity; //run generated sql using (var con = _factory.CreateConnection()) { con.ConnectionString = _connectionString; con.Open(); using (var transaction = con.BeginTransaction()) { using (var cmd = con.CreateCommand()) { cmd.CommandText = sql; cmd.Transaction = transaction; string identityParameterName = "Id"; foreach (var column in table.Columns) { var par = cmd.CreateParameter(); par.ParameterName = writer.ParameterName(column.Name); if (column.IsAutoNumber) { //get the name of the identity parameter identityParameterName = par.ParameterName; par.Direction = ParameterDirection.Output; par.DbType = DbType.Int32; } else { object value = DummyDataCreator.CreateData(column); par.Value = value ?? DBNull.Value; } cmd.Parameters.Add(par); } cmd.ExecuteNonQuery(); identity = (int)cmd.Parameters[identityParameterName].Value; } //explicit rollback. If we errored, implicit rollback. transaction.Rollback(); } } //assert Assert.AreNotEqual(0, identity); }
public void WriteUnion_WhenSelectWithJoin_WritesUnionStatement() { StringBuilder builder = new StringBuilder(); using (SqlWriter writer = new SqlWriter(builder)) { writer.WriteStartSelect(); writer.WriteColumn("u", "Id", null); writer.WriteColumn("u", "Name", null); writer.WriteColumn("p", "Age", null); writer.WriteStartFrom(); writer.WriteTable("Users", "u"); writer.WriteStartJoin(SqlJoinType.Inner); writer.WriteTable("Profiles", "p"); writer.WriteStartOn(); writer.WriteColumn("u", "Id", null); writer.WriteOperator(SqlBinaryOperator.Equal); writer.WriteColumn("p", "UserId", null); //writer.WriteStartWhere(); //writer.WriteColumn("u", "Age", null); //writer.WriteOperator(SqlBinaryOperator.LessThanOrEqual); //writer.WriteValue(18); writer.WriteEndSelect(); Assert.AreEqual(SqlWriterState.EndSelect, writer.WriteState); writer.WriteUnion(); Assert.AreEqual(SqlWriterState.Union, writer.WriteState); writer.WriteStartSelect(); writer.WriteColumn("u", "Id", null); writer.WriteColumn("u", "Name", null); writer.WriteColumn("p", "Age", null); writer.WriteStartFrom(); writer.WriteTable("Users", "u"); writer.WriteStartJoin(SqlJoinType.Inner); writer.WriteTable("Profiles", "p"); writer.WriteStartOn(); writer.WriteColumn("u", "Id", null); writer.WriteOperator(SqlBinaryOperator.Equal); writer.WriteColumn("p", "UserId", null); writer.WriteStartWhere(); writer.WriteColumn("p", "Age", null); writer.WriteOperator(SqlBinaryOperator.GreaterThanOrEqual); writer.WriteValue(30); writer.WriteEndSelect(); } Assert.AreEqual("SELECT [u].[Id], [u].[Name], [p].[Age] FROM [Users] [u] INNER JOIN [Profiles] [p] ON ([u].[Id] = [p].[UserId]) UNION SELECT [u].[Id], [u].[Name], [p].[Age] FROM [Users] [u] INNER JOIN [Profiles] [p] ON ([u].[Id] = [p].[UserId]) WHERE ([p].[Age] >= 30)", builder.ToString()); }
/// <inheritdoc/> protected override void WriteFragmentForSelect(SqlWriter writer, ISqlFragment fragment) { var select = (SelectFragment)fragment; writer.Enter(delegate() { if (select.Skip > 0) { var rownumber = select.Members.Select(a => a.OutputName).Unique("RowIndex");; if (select.Take > 0) { WriteFragmentForSelectTake(writer, select, rownumber, delegate() { WriteFragmentForSelectSkip(writer, select, rownumber, delegate() { WriteFragmentForSelectRowNumber(writer, select, rownumber); }); }); } else { WriteFragmentForSelectSkip(writer, select, rownumber, delegate() { WriteFragmentForSelectRowNumber(writer, select, rownumber); }, true); } } else { if (select.Take > 0) { var alias = fragment.Context.GetDataSourceAlias(); writer.WriteLine("SELECT "); writer.Write(alias); writer.WriteLine(".* FROM ("); WriteFragmentForSelectSimple(writer, select); writer.WriteLine(); writer.Write(") "); writer.Write(alias); writer.Write(" WHERE ROWNUM <= "); writer.Write(select.Take); } else { WriteFragmentForSelectSimple(writer, select); } } }, select); }
/// <inheritdoc/> protected override void WriteFragmentForUpdate(SqlWriter writer, ISqlFragment fragment) { var update = (UpdateFragment)fragment; writer.Enter(delegate() { writer.Write("UPDATE "); if (update.Sources.Contains(update.Target) || update.Sources.OfType <InheritFragment>().Any(a => a.Tables.Contains(update.Target))) { update.Sources.ForEach(() => writer.WriteLine(), source => WriteFragmentForSource(writer, source)); } else { update.Target.WriteSql(writer); writer.Write(" AS "); writer.Write(update.Target.AliasName); if (update.Sources.Any()) { writer.Write(" CROSS JOIN "); update.Sources.ForEach(() => writer.WriteLine(), source => WriteFragmentForSource(writer, source)); } } writer.WriteLine(); writer.Write("SET "); var target = update.Target; var values = update.Values.ToArray(); for (int i = 0; i < update.Members.Count; i++) { var member = update.Members[i]; var value = values[i]; if (i > 0) { writer.Write(", "); } member.WriteSql(writer); writer.Write(" = "); value.WriteSql(writer); } if (update.Where != null) { writer.WriteLine(); writer.Write("WHERE "); update.Where.WriteSql(writer); } }, update); }
public override bool Delete(object toDelete) { try { List <SqlStringBuilder> sqls = SqlWriter.GetDeleteStatements(toDelete, GetBaseType(toDelete.GetType())); sqls.Each(sql => Execute(sql)); return(true); } catch (Exception ex) { string value = toDelete == null ? "null" : toDelete.ToString(); Logger.AddEntry("Exception occurred on delete of {0}: {1}", ex, value, ex.Message); return(false); } }
public string BuildTableUpdate(DatabaseTable databaseTable) { StringBuilder sb = new StringBuilder(); var sqlWriter = new SqlWriter(databaseTable, m_sqlType); try { sb.Append(sqlWriter.UpdateSql()); } catch (Exception exception) { Debug.WriteLine(exception.Message); } return(sb.ToString()); }
public string BuildTableInsert(DatabaseTable databaseTable) { StringBuilder sb = new StringBuilder(); var sqlWriter = new SqlWriter(databaseTable, m_sqlType); try { sb.Append(sqlWriter.InsertSqlWithoutOutputParameter()); } catch (Exception exception) { Debug.WriteLine(exception.Message); } return(sb.ToString()); }
/// <inheritdoc/> protected override void WriteFragmentForSourceCondition(SqlWriter writer, ISourceFragment source) { if (source.Condition != null) { if (source is InheritFragment && source.Join.Value == EJoinType.InnerJoin) { writer.Write(" AND "); } else { writer.Write(" ON "); } source.Condition.WriteSql(writer); } }
public void WriteColumn_WhenSelectStatement_WritesColumns() { StringBuilder builder = new StringBuilder(); using (SqlWriter writer = new SqlWriter(builder)) { writer.WriteStartSelect(); writer.WriteColumn("Id"); writer.WriteColumn("Name", "n"); writer.WriteColumn("Users", "Email", "e"); Assert.AreEqual(SqlWriterState.Select, writer.WriteState); } Assert.AreEqual("SELECT [Id], [Name] AS [n], [Users].[Email] AS [e]", builder.ToString()); }
public void TestGeneratedSqlForInsert() { //arrange var table = LoadCountryFromSakila(); var writer = new SqlWriter(table, SqlType.MySql); //MySQL can only use output parameters with sprocs. var sql = writer.InsertSqlWithoutOutputParameter(); Console.WriteLine(sql); int identity; //run generated sql using (var con = _factory.CreateConnection()) { con.ConnectionString = _connectionString; con.Open(); using (var transaction = con.BeginTransaction()) { using (var cmd = con.CreateCommand()) { cmd.CommandText = sql; cmd.Transaction = transaction; foreach (var column in table.Columns) { if (column.IsAutoNumber) { continue; } var par = cmd.CreateParameter(); par.ParameterName = writer.ParameterName(column.Name); object value = DummyDataCreator.CreateData(column); par.Value = value ?? DBNull.Value; cmd.Parameters.Add(par); } identity = Convert.ToInt32(cmd.ExecuteScalar()); //if using a sproc //identity = (int)cmd.Parameters[identityParameterName].Value; } //explicit rollback. If we errored, implicit rollback. transaction.Rollback(); } } //assert Assert.AreNotEqual(0, identity); }
public override void Visit(DbConstantExpression expression) { if (generateParameters) { var parameter = CreateParameter(expression.Value, expression.ResultType); commandText.Append(parameter.ParameterName); } else { using (var writer = new SqlWriter(commandText)) { var sqlGenerator = new SqlGenerator(); sqlGenerator.WriteSql(writer, expression.Accept(sqlGenerator)); } } }
//写入带精度的数据类型。 private void WriteDbDataTypeForScale(SqlWriter writer, ColumnMetadata column, Type type, string name) { var precision = column.GetProperty <PrecisionAttribute>(); if (precision != null) { writer.Write(name); writer.Write('('); writer.Write(precision.Scale); writer.Write(')'); } else { WriteDbDataType(writer, type); } }
/// <inheritdoc/> protected override void WriteFragmentForBlock(SqlWriter writer, ISqlFragment fragment) { writer.WriteLine("BEGIN"); var block = (BlockFragment)fragment; block.ForEach(writer.WriteLine, a => { a.WriteSql(writer); if (a.HasTerminator) { writer.Write(';'); } }); writer.WriteLine(); writer.Write("END;"); }
public void WriteSortOrder_WithoutWriteAscendingSortOrder_WritesSortOrder() { var sortOrders = new[] { SqlSortOrder.Ascending, SqlSortOrder.Descending }; var expected = new[] { "", "DESC" }; for (int index = 0; index < sortOrders.Length; index++) { var builder = new StringBuilder(); using (var writer = new SqlWriter(builder)) { writer.WriteSortOrder(sortOrders[index]); } Assert.Equal(expected[index], builder.ToString()); } }
public void WriteOperator_WritesOperator() { var operators = new[] { SqlBinaryOperator.And, SqlBinaryOperator.Equal, SqlBinaryOperator.GreaterThan, SqlBinaryOperator.GreaterThanOrEqual, SqlBinaryOperator.LessThan, SqlBinaryOperator.LessThanOrEqual, SqlBinaryOperator.Like, SqlBinaryOperator.NotEqual, SqlBinaryOperator.Or, SqlBinaryOperator.In }; var expected = new[] { "AND", "=", ">", ">=", "<", "<=", "LIKE", "<>", "OR", "IN" }; for (int index = 0; index < operators.Length; index++) { var builder = new StringBuilder(); using (var writer = new SqlWriter(builder)) { writer.WriteOperator(operators[index]); } Assert.Equal(expected[index], builder.ToString()); } }
/// <summary> /// Initializes a new instance of the <see cref="InsertWriter" /> class. The databaseTable must have dataTypes (call DataReader.DataTypes()). Use this with <see cref="InsertWriter.WriteInsert(IDataRecord)"/> /// </summary> /// <param name="databaseTable">The database table.</param> /// <param name="sqlType">Type of the SQL.</param> /// <exception cref="System.ArgumentNullException">databaseTable</exception> public InsertWriter(DatabaseTable databaseTable, SqlType sqlType) { if (databaseTable == null) { throw new ArgumentNullException("databaseTable"); } _databaseTable = databaseTable; PrepareTypes(); _sqlType = sqlType; _sqlWriter = new SqlWriter(_databaseTable, sqlType); _converter = new Converter(sqlType, _dateTypes); PrepareTemplate(); }
public DataTable Read() { var sqlType = FindSqlType(); var originSql = new SqlWriter(_databaseTable, sqlType); var selectAll = originSql.SelectPageSql(); var dt = new DataTable(_databaseTable.Name) { Locale = CultureInfo.InvariantCulture }; var dbFactory = DbProviderFactories.GetFactory(_providerName); using (var con = dbFactory.CreateConnection()) { con.ConnectionString = _connectionString; using (var cmd = con.CreateCommand()) { cmd.CommandText = selectAll; cmd.CommandTimeout = 6000; var p = cmd.CreateParameter(); var parameterName = "currentPage"; if (sqlType == SqlType.SqlServerCe) { parameterName = "offset"; } p.ParameterName = parameterName; p.Value = 1; if (sqlType == SqlType.SqlServerCe) { p.Value = 0; } cmd.Parameters.Add(p); var ps = cmd.CreateParameter(); ps.ParameterName = "pageSize"; ps.Value = _pageSize; cmd.Parameters.Add(ps); using (var da = dbFactory.CreateDataAdapter()) { da.SelectCommand = cmd; da.Fill(dt); } } } return(dt); }
/// <summary> /// Write out the TOP part of sql select statement /// It basically writes LIMIT (X). /// </summary> /// <param name="writer"></param> /// <param name="sqlGenerator"></param> public void WriteSql(SqlWriter writer, SqlGenerator sqlGenerator) { writer.Write(" LIMIT "); this.TopCount.WriteSql(writer, sqlGenerator); if (this.WithTies) { throw new NotSupportedException("WITH TIES"); } //writer.Write(" "); //if (this.WithTies) //{ // writer.Write("WITH TIES "); //} }
private void WriteFragmentForSelectRowNumber(SqlWriter writer, SelectFragment select, string rownumber) { writer.Write("SELECT"); if (select.Distinct) { writer.Write(" DISTINCT"); } writer.WriteLine(); WriteFragmentForSelectMembers(writer, select.Members); writer.Write(", ROW_NUMBER() OVER ( "); WriteFragmentForOrderBy(writer, select.Sorts); writer.Write(" ) "); writer.Write(rownumber); WriteFragmentForFrom(writer, select.Sources); WriteFragmentForWhere(writer, select.Where); WriteFragmentForGroupBy(writer, select.GroupBys); }
/// <inheritdoc/> protected override void WriteFragmentForCreateTemporaryTable(SqlWriter writer, ISqlFragment fragment) { var content = (CreateTempTableFragment)fragment; if (content.IsVariable) { throw new NotSupportedException(Res.NotSupportedWriteTableVariable); } var create = (CreateTempTableFragment)fragment; writer.Write($"CREATE GLOBAL TEMPORARY TABLE "); create.Table.WriteSql(writer); writer.WriteLine("("); create.Members.ForEach(() => writer.WriteLine(", "), m => m.WriteSql(writer)); writer.Write(')'); }
private void WriteFragmentForObjectExsit(SqlWriter writer, ISqlFragment fragment) { var exist = (ObjectExsitFragment)fragment; writer.Write("SELECT COUNT(1) FROM RDB$RELATIONS "); switch (exist.Kind) { case EDatabaseObject.Table: writer.Write("WHERE RDB$VIEW_BLR IS NULL AND (RDB$SYSTEM_FLAG IS NULL OR RDB$SYSTEM_FLAG = 0)"); break; case EDatabaseObject.View: writer.Write("WHERE RDB$VIEW_BLR IS NOT NULL AND (RDB$SYSTEM_FLAG IS NULL OR RDB$SYSTEM_FLAG = 0)"); break; default: throw new NotSupportedException(string.Format(Res.NotSupportedWriteDatabaseObject, exist.Kind)); } writer.Write(" AND RDB$RELATION_NAME='"); writer.Write(exist.Name.Name); writer.Write('\''); }
private void WriteFragmentForObjectExsit(SqlWriter writer, ISqlFragment fragment) { var exist = (ObjectExsitFragment)fragment; writer.Write("SELECT CASE WHEN EXISTS ("); switch (exist.Kind) { case EDatabaseObject.Table: writer.Write("SELECT 1 FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_TYPE='TABLE' AND t.TABLE_NAME='"); writer.Write(exist.Name.Name); writer.Write('\''); break; default: throw new NotSupportedException(string.Format(Res.NotSupportedWriteDatabaseObject, exist.Kind)); } writer.Write(") THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END"); }
/// <inheritdoc/> protected override void WriteFragmentForRenameObject(SqlWriter writer, ISqlFragment fragment) { var content = (RenameObjectFragment)fragment; writer.Write("ALTER "); switch (content.Kind) { case EDatabaseObject.Table: writer.Write("TABLE "); break; case EDatabaseObject.View: writer.Write("VIEW "); break; default: throw new NotSupportedException(string.Format(Res.NotSupportedWriteDatabaseObject, content.Kind)); } content.Name.WriteSql(writer); writer.Write(" RENAME TO "); WriteDbName(writer, content.NewName); }
private void WriteFragmentForObjectExsit(SqlWriter writer, ISqlFragment fragment) { var exist = (ObjectExsitFragment)fragment; writer.Write("SELECT COUNT(1) FROM MSysObjects Where "); switch (exist.Kind) { case EDatabaseObject.Table: writer.Write("Type=1"); break; case EDatabaseObject.View: writer.Write("Type=5"); break; default: throw new NotSupportedException(string.Format(Res.NotSupportedWriteDatabaseObject, exist.Kind)); } writer.Write(" AND Name='"); writer.Write(exist.Name.Name); writer.Write('\''); }
private void WriteFragmentForCreateRelation(SqlWriter writer, ISqlFragment fragment) { var relation = (CreateRelationFragment)fragment; writer.Write("ALTER TABLE "); relation.Foreign.WriteSql(writer); writer.Write(" ADD CONSTRAINT "); WriteDbName(writer, relation.Name); writer.Write(" FOREIGN KEY("); relation.ForeignKeys.ForEach(() => writer.Write(','), a => WriteDbName(writer, a)); writer.Write(") REFERENCES "); relation.Principal.WriteSql(writer); writer.Write(" ("); relation.PrincipalKeys.ForEach(() => writer.Write(','), a => WriteDbName(writer, a)); writer.Write(')'); }
/// <inheritdoc/> protected override void WriteFragmentForInsertValue(SqlWriter writer, ISqlFragment fragment) { var insert = (InsertValueFragment)fragment; writer.Write("INSERT INTO "); insert.Target.WriteSql(writer); writer.WriteLine(); writer.Write('('); insert.Members.ForEach( () => writer.Write(","), column => this.WriteDbName(writer, column.OutputName)); writer.WriteLine(")"); writer.Write("VALUES"); writer.Write('('); insert.Values.ForEach(() => writer.Write(","), val => val.WriteSql(writer)); writer.Write(')'); }
public void TestGeneratedSqlForInsert() { //arrange var table = LoadRegionsFromHr(); var writer = new SqlWriter(table, SqlType.Oracle); var sql = writer.InsertSql(); //run generated sql using (var con = _factory.CreateConnection()) { con.ConnectionString = ConnectionString; con.Open(); using (var transaction = con.BeginTransaction()) { using (var cmd = con.CreateCommand()) { cmd.CommandText = sql; cmd.Transaction = transaction; foreach (var column in table.Columns) { var par = cmd.CreateParameter(); par.ParameterName = writer.ParameterName(column.Name); if (column.IsAutoNumber) { //we could be using sequences here par.Direction = ParameterDirection.Output; par.DbType = DbType.Int32; } else { object value = DummyDataCreator.CreateData(column); par.Value = value ?? DBNull.Value; } cmd.Parameters.Add(par); } cmd.ExecuteNonQuery(); } //explicit rollback. If we errored, implicit rollback. transaction.Rollback(); } } //assert }
static void WriteColumns(SqlWriter writer, IEnumerable <string> columns, bool separateLines = false) { var separator = (string)null; foreach (var column in columns) { if (separator != null) { writer.Write(separator); if (separateLines) { writer.WriteLine(); } } writer.Write(column); separator = ", "; } }
public void Read(Func <IDataRecord, bool> processRecord) { if (processRecord == null) { return; } var sqlType = FindSqlType(_providerName); var originSql = new SqlWriter(_databaseTable, sqlType); var sql = originSql.SelectAllSql(); var dbFactory = DbProviderFactories.GetFactory(_providerName); using (var connection = dbFactory.CreateConnection()) { connection.ConnectionString = _connectionString; RunCommand(connection, processRecord, sql); } }