public string ScriptAddFilterColumn(DmColumn filterColumn) { var quotedColumnName = new ObjectNameParser(filterColumn.ColumnName.ToLowerInvariant(), "`", "`"); string str = string.Concat("Add new filter column, ", quotedColumnName.UnquotedString, ", to Tracking Table ", trackingName.QuotedString); return(MySqlBuilder.WrapScriptTextWithComments(this.AddFilterColumnCommandText(filterColumn), str)); }
public string ScriptAddFilterColumn(DmColumn filterColumn) { var quotedColumnName = new ObjectNameParser(filterColumn.ColumnName, "[", "]"); string str = string.Concat("Add new filter column, ", quotedColumnName.FullUnquotedString, ", to Tracking Table ", trackingName.FullQuotedString); return(SqliteBuilder.WrapScriptTextWithComments(this.AddFilterColumnCommandText(filterColumn), str)); }
internal static (ObjectNameParser tableName, ObjectNameParser trackingName) GetParsers(DmTable tableDescription) { string tableAndPrefixName = String.IsNullOrWhiteSpace(tableDescription.Prefix) ? tableDescription.TableName : $"{tableDescription.Prefix}.{tableDescription.TableName}"; var originalTableName = new ObjectNameParser(tableAndPrefixName, "[", "]"); var trackingTableName = new ObjectNameParser($"{tableAndPrefixName}_tracking", "[", "]"); return(originalTableName, trackingTableName); }
internal static (ObjectNameParser tableName, ObjectNameParser trackingName) GetParsers(DmTable tableDescription) { string tableAndPrefixName = tableDescription.TableName; var originalTableName = new ObjectNameParser(tableAndPrefixName.ToLowerInvariant(), "`", "`"); var trackingTableName = new ObjectNameParser($"{tableAndPrefixName.ToLowerInvariant()}_tracking", "`", "`"); return(originalTableName, trackingTableName); }
public string ScriptAddFilterColumn(DmColumn filterColumn) { var quotedColumnName = new ObjectNameParser(filterColumn.ColumnName, "\"", "\""); var str = string.Concat("Add new filter column, ", quotedColumnName.UnquotedString, ", to Tracking Table ", _trackingName.QuotedString); return(PostgreSqlBuilder.WrapScriptTextWithComments(AddFilterColumnCommandText(filterColumn), str)); }
private string AddFilterColumnCommandText(DmColumn col) { var quotedColumnName = new ObjectNameParser(col.ColumnName, "[", "]").QuotedString; var quotedColumnType = new ObjectNameParser(col.GetSqlDbTypeString(), "[", "]").QuotedString; quotedColumnType += col.GetSqlTypePrecisionString(); return(string.Concat("ALTER TABLE ", quotedColumnName, " ADD ", quotedColumnType)); }
public static string GetUnquotedSqlSchemaName(ObjectNameParser parser) { if (string.IsNullOrEmpty(parser.SchemaName)) { return("dbo"); } return(parser.SchemaName); }
internal static MySqlParameter[] DeriveParameters(this MySqlConnection connection, MySqlCommand cmd, bool includeReturnValueParameter = false, MySqlTransaction transaction = null) { if (cmd == null) { throw new ArgumentNullException("SqlCommand"); } var textParser = new ObjectNameParser(cmd.CommandText); // Hack to check for schema name in the spName string schemaName = "dbo"; string spName = textParser.UnquotedString; int firstDot = spName.IndexOf('.'); if (firstDot > 0) { schemaName = cmd.CommandText.Substring(0, firstDot); spName = spName.Substring(firstDot + 1); } var alreadyOpened = connection.State == ConnectionState.Open; if (!alreadyOpened) { connection.Open(); } try { MySqlCommandBuilder.DeriveParameters(cmd); } finally { if (!alreadyOpened) { connection.Close(); } } if (!includeReturnValueParameter && cmd.Parameters.Count > 0) { cmd.Parameters.RemoveAt(0); } MySqlParameter[] discoveredParameters = new MySqlParameter[cmd.Parameters.Count]; cmd.Parameters.CopyTo(discoveredParameters, 0); // Init the parameters with a DBNull value foreach (MySqlParameter discoveredParameter in discoveredParameters) { discoveredParameter.Value = DBNull.Value; } return(discoveredParameters); }
private string AddFilterColumnCommandText(DmColumn col) { var quotedColumnName = new ObjectNameParser(col.ColumnName, "`", "`").FullQuotedString; var columnTypeString = this.mySqlDbMetadata.TryGetOwnerDbTypeString(col.OriginalDbType, col.DbType, false, false, col.MaxLength, this.tableDescription.OriginalProvider, MySqlSyncProvider.ProviderType); var columnPrecisionString = this.mySqlDbMetadata.TryGetOwnerDbTypePrecision(col.OriginalDbType, col.DbType, false, false, col.MaxLength, col.Precision, col.Scale, this.tableDescription.OriginalProvider, MySqlSyncProvider.ProviderType); var columnType = $"{columnTypeString} {columnPrecisionString}"; return(string.Concat("ALTER TABLE ", quotedColumnName, " ADD ", columnType)); }
//------------------------------------------------------------------ // Select Row command //------------------------------------------------------------------ private MySqlCommand BuildSelectRowCommand() { MySqlCommand sqlCommand = new MySqlCommand(); this.AddPkColumnParametersToCommand(sqlCommand); MySqlParameter sqlParameter = new MySqlParameter(); sqlParameter.ParameterName = "sync_scope_id"; sqlParameter.MySqlDbType = MySqlDbType.Guid; sqlParameter.Size = 36; sqlCommand.Parameters.Add(sqlParameter); StringBuilder stringBuilder = new StringBuilder("SELECT "); stringBuilder.AppendLine(); StringBuilder stringBuilder1 = new StringBuilder(); string empty = string.Empty; foreach (var pkColumn in this.tableDescription.PrimaryKey.Columns) { ObjectNameParser pkColumnName = new ObjectNameParser(pkColumn.ColumnName, "`", "`"); stringBuilder.AppendLine($"\t`side`.{pkColumnName.FullQuotedString}, "); stringBuilder1.Append($"{empty}`side`.{pkColumnName.FullQuotedString} = {MYSQL_PREFIX_PARAMETER}{pkColumnName.FullUnquotedString}"); empty = " AND "; } foreach (DmColumn mutableColumn in this.tableDescription.MutableColumns) { ObjectNameParser nonPkColumnName = new ObjectNameParser(mutableColumn.ColumnName, "`", "`"); stringBuilder.AppendLine($"\t`base`.{nonPkColumnName.FullQuotedString}, "); } stringBuilder.AppendLine("\t`side`.`sync_row_is_tombstone`,"); stringBuilder.AppendLine("\t`side`.`create_scope_id`,"); stringBuilder.AppendLine("\t`side`.`create_timestamp`,"); stringBuilder.AppendLine("\t`side`.`update_scope_id`,"); stringBuilder.AppendLine("\t`side`.`update_timestamp`"); stringBuilder.AppendLine($"FROM {tableName.FullQuotedString} `base`"); stringBuilder.AppendLine($"RIGHT JOIN {trackingName.FullQuotedString} `side` ON"); string str = string.Empty; foreach (var pkColumn in this.tableDescription.PrimaryKey.Columns) { ObjectNameParser pkColumnName = new ObjectNameParser(pkColumn.ColumnName, "`", "`"); stringBuilder.Append($"{str}`base`.{pkColumnName.FullQuotedString} = `side`.{pkColumnName.FullQuotedString}"); str = " AND "; } stringBuilder.AppendLine(); stringBuilder.Append("WHERE "); stringBuilder.Append(stringBuilder1.ToString()); stringBuilder.Append(";"); sqlCommand.CommandText = stringBuilder.ToString(); return(sqlCommand); }
//------------------------------------------------------------------ // Update command //------------------------------------------------------------------ private Tuple <NpgsqlCommand, string> BuildUpdateCommand() { var sqlCommand = new NpgsqlCommand(); var stringBuilder = new StringBuilder(); AddColumnParametersToCommand(sqlCommand); var sqlParameter = new NpgsqlParameter(); sqlParameter.ParameterName = "sync_force_write"; sqlParameter.NpgsqlDbType = NpgsqlDbType.Integer; sqlCommand.Parameters.Add(sqlParameter); var sqlParameter1 = new NpgsqlParameter(); sqlParameter1.ParameterName = "sync_min_timestamp"; sqlParameter1.NpgsqlDbType = NpgsqlDbType.Bigint; sqlCommand.Parameters.Add(sqlParameter1); var whereArgs = new List <string>(); foreach (var pkColumn in _tableDescription.PrimaryKey.Columns) { var pk = new ObjectNameParser(pkColumn.ColumnName, "\"", "\""); whereArgs.Add( $"{pk.QuotedObjectName} = {PgsqlPrefixParameter}{pk.UnquotedString.ToLowerInvariant()}"); } stringBuilder.AppendLine("ts := 0;"); stringBuilder.AppendLine( $"SELECT \"timestamp\" FROM {_trackingName.QuotedObjectName} WHERE {string.Join(" AND ", whereArgs.Select(a => $"{_trackingName.QuotedObjectName}.{a}"))} LIMIT 1 INTO ts;"); stringBuilder.AppendLine($"UPDATE {_tableName.QuotedString}"); stringBuilder.Append( $"SET {PostgreSqlManagementUtils.CommaSeparatedUpdateFromParameters(_tableDescription)}"); stringBuilder.Append($"WHERE {string.Join(" AND ", whereArgs)}"); stringBuilder.AppendLine($" AND (ts <= sync_min_timestamp OR sync_force_write = 1);"); stringBuilder.AppendLine("GET DIAGNOSTICS rows = ROW_COUNT;"); stringBuilder.AppendLine("RETURN rows;"); stringBuilder.AppendLine(); // Can't rely on rows count since MySql will return 0 if an update don't update any columns //stringBuilder.AppendLine($"/* Since the update 'could' potentially returns 0 as row affected count when we make a double update with the same values, to be sure, make a fake update on metadatas time column */ //stringBuilder.AppendLine($"UPDATE {trackingName.QuotedObjectName} "); //stringBuilder.AppendLine($"SET \"timestamp\" = {MySqlObjectNames.TimestampValue}"); //stringBuilder.AppendLine($"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.PrimaryKey.Columns, "")} AND (ts <= sync_min_timestamp OR sync_force_write = 1);"); sqlCommand.CommandText = stringBuilder.ToString(); return(Tuple.Create(sqlCommand, "integer")); }
//------------------------------------------------------------------ // Insert Metadata command //------------------------------------------------------------------ private MySqlCommand BuildInsertMetadataCommand() { StringBuilder stringBuilderArguments = new StringBuilder(); StringBuilder stringBuilderParameters = new StringBuilder(); MySqlCommand sqlCommand = new MySqlCommand(); StringBuilder stringBuilder = new StringBuilder(); this.AddPkColumnParametersToCommand(sqlCommand); MySqlParameter sqlParameter = new MySqlParameter("sync_scope_id", MySqlDbType.Guid); sqlParameter.Size = 36; sqlCommand.Parameters.Add(sqlParameter); MySqlParameter sqlParameter1 = new MySqlParameter("sync_row_is_tombstone", MySqlDbType.Int32); sqlCommand.Parameters.Add(sqlParameter1); MySqlParameter sqlParameter3 = new MySqlParameter("create_timestamp", MySqlDbType.Int64); sqlCommand.Parameters.Add(sqlParameter3); MySqlParameter sqlParameter4 = new MySqlParameter("update_timestamp", MySqlDbType.Int64); sqlCommand.Parameters.Add(sqlParameter4); stringBuilder.AppendLine($"\tINSERT INTO {trackingName.QuotedString}"); string empty = string.Empty; foreach (var pkColumn in this.tableDescription.PrimaryKey.Columns) { ObjectNameParser columnName = new ObjectNameParser(pkColumn.ColumnName, "`", "`"); stringBuilderArguments.Append(string.Concat(empty, columnName.QuotedString.ToLowerInvariant())); stringBuilderParameters.Append(string.Concat(empty, $"in{columnName.UnquotedString.ToLowerInvariant()}")); empty = ", "; } stringBuilder.Append($"\t({stringBuilderArguments.ToString()}, "); stringBuilder.AppendLine($"\t`create_scope_id`, `create_timestamp`, `update_scope_id`, `update_timestamp`,"); stringBuilder.AppendLine($"\t`sync_row_is_tombstone`, `timestamp`, `last_change_datetime`)"); stringBuilder.Append($"\tVALUES ({stringBuilderParameters.ToString()}, "); stringBuilder.AppendLine($"\tcreate_scope_id, create_timestamp, update_scope_id, update_timestamp, "); stringBuilder.AppendLine($"\tsync_row_is_tombstone, {MySqlObjectNames.TimestampValue}, now())"); stringBuilder.AppendLine($"\tON DUPLICATE KEY UPDATE"); stringBuilder.AppendLine($"\t `create_scope_id` = create_scope_id, "); stringBuilder.AppendLine($"\t `create_timestamp` = create_timestamp, "); stringBuilder.AppendLine($"\t `update_scope_id` = update_scope_id, "); stringBuilder.AppendLine($"\t `update_timestamp` = update_timestamp, "); stringBuilder.AppendLine($"\t `sync_row_is_tombstone` = sync_row_is_tombstone, "); stringBuilder.AppendLine($"\t `timestamp` = {MySqlObjectNames.TimestampValue}, "); stringBuilder.AppendLine($"\t `last_change_datetime` = now(); "); stringBuilder.AppendLine(); sqlCommand.CommandText = stringBuilder.ToString(); return(sqlCommand); }
public static void DropTriggerIfExists(SqlConnection connection, SqlTransaction transaction, int commandTimeout, string quotedTriggerName) { ObjectNameParser objectNameParser = new ObjectNameParser(quotedTriggerName); using (SqlCommand sqlCommand = new SqlCommand(string.Format(CultureInfo.InvariantCulture, "IF EXISTS (SELECT tr.name FROM sys.triggers tr JOIN sys.tables t ON tr.parent_id = t.object_id JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE tr.name = @triggerName and s.name = @schemaName) DROP TRIGGER {0}", quotedTriggerName), connection, transaction)) { sqlCommand.CommandTimeout = commandTimeout; sqlCommand.Parameters.AddWithValue("@triggerName", objectNameParser.ObjectName); sqlCommand.Parameters.AddWithValue("@schemaName", SqlManagementUtils.GetUnquotedSqlSchemaName(objectNameParser)); sqlCommand.ExecuteNonQuery(); } }
public static void DropProcedureIfExists(SqlConnection connection, SqlTransaction transaction, int commandTimout, string quotedProcedureName) { ObjectNameParser objectNameParser = new ObjectNameParser(quotedProcedureName); using (SqlCommand sqlCommand = new SqlCommand(string.Format(CultureInfo.InvariantCulture, "IF EXISTS (SELECT * FROM sys.procedures p JOIN sys.schemas s ON s.schema_id = p.schema_id WHERE p.name = @procName AND s.name = @schemaName) DROP PROCEDURE {0}", quotedProcedureName), connection, transaction)) { sqlCommand.CommandTimeout = commandTimout; sqlCommand.Parameters.AddWithValue("@procName", objectNameParser.ObjectName); sqlCommand.Parameters.AddWithValue("@schemaName", SqlManagementUtils.GetUnquotedSqlSchemaName(objectNameParser)); sqlCommand.ExecuteNonQuery(); } }
private void SetInsertRowParameters(DbCommand command) { DbParameter p; foreach (DmColumn column in this.TableDescription.Columns.Where(c => !c.IsReadOnly)) { ObjectNameParser quotedColumn = new ObjectNameParser(column.ColumnName); p = command.CreateParameter(); p.ParameterName = $"{MySqlBuilderProcedure.MYSQL_PREFIX_PARAMETER}{quotedColumn.ObjectNameNormalized}"; p.DbType = column.DbType; p.SourceColumn = column.ColumnName; command.Parameters.Add(p); } }
internal static string CommaSeparatedUpdateFromParameters(DmTable table, string fromPrefix = "") { StringBuilder stringBuilder = new StringBuilder(); string strFromPrefix = (string.IsNullOrEmpty(fromPrefix) ? string.Empty : string.Concat(fromPrefix, ".")); string strSeparator = ""; foreach (DmColumn mutableColumn in table.MutableColumns) { ObjectNameParser quotedColumn = new ObjectNameParser(mutableColumn.ColumnName, "`", "`"); stringBuilder.AppendLine($"{strSeparator} {strFromPrefix}{quotedColumn.FullQuotedString} = {MySqlBuilderProcedure.MYSQL_PREFIX_PARAMETER}{quotedColumn.FullUnquotedString}"); strSeparator = ", "; } return(stringBuilder.ToString()); }
private void SetInsertRowParameters(DbCommand command) { DbParameter p; foreach (DmColumn column in this.TableDescription.Columns.Where(c => !c.IsReadOnly)) { ObjectNameParser quotedColumn = new ObjectNameParser(column.ColumnName); p = command.CreateParameter(); p.ParameterName = $"@{quotedColumn.ObjectNameNormalized}"; p.DbType = GetValidDbType(column.DbType); p.SourceColumn = column.ColumnName; command.Parameters.Add(p); } }
internal static object CommaSeparatedUpdateFromParametersStoredProcedure(DmTable table, string fromPrefix = "") { StringBuilder stringBuilder = new StringBuilder(); string strFromPrefix = (string.IsNullOrEmpty(fromPrefix) ? string.Empty : string.Concat(fromPrefix, ".")); string strSeparator = ""; foreach (DmColumn column in table.NonPkColumns.Where(c => !c.ReadOnly)) { ObjectNameParser quotedColumn = new ObjectNameParser(column.ColumnName); stringBuilder.AppendLine($"{strSeparator} {strFromPrefix}{quotedColumn.UnquotedString} = {quotedColumn.UnquotedString}0"); strSeparator = ", "; } return(stringBuilder.ToString()); }
private void SetInsertRowParameters(DbCommand command) { DbParameter p; foreach (DmColumn column in this.TableDescription.Columns.Where(c => !c.ReadOnly)) { ObjectNameParser quotedColumn = new ObjectNameParser(column.ColumnName); p = command.CreateParameter(); p.ParameterName = $"@{quotedColumn.UnquotedStringWithUnderScore}"; p.DbType = column.DbType; p.SourceColumn = column.ColumnName; command.Parameters.Add(p); } }
internal static string CommaSeparatedUpdateFromParameters(DmTable table, string fromPrefix = "") { StringBuilder stringBuilder = new StringBuilder(); string strFromPrefix = (string.IsNullOrEmpty(fromPrefix) ? string.Empty : string.Concat(fromPrefix, ".")); string strSeparator = ""; foreach (DmColumn mutableColumn in table.MutableColumnsAndNotAutoInc) { ObjectNameParser quotedColumn = new ObjectNameParser(mutableColumn.ColumnName); stringBuilder.AppendLine($"{strSeparator} {strFromPrefix}{quotedColumn.FullQuotedString} = @{quotedColumn.FullUnquotedString}"); strSeparator = ", "; } return(stringBuilder.ToString()); }
public static void DropTriggerIfExists(MySqlConnection connection, MySqlTransaction transaction, string quotedTriggerName) { ObjectNameParser objectNameParser = new ObjectNameParser(quotedTriggerName, "`", "`"); using (DbCommand dbCommand = connection.CreateCommand()) { dbCommand.CommandText = $"drop trigger {objectNameParser.ObjectName}"; if (transaction != null) { dbCommand.Transaction = transaction; } dbCommand.ExecuteNonQuery(); } }
public static void DropTableIfExists(SqliteConnection connection, SqliteTransaction transaction, string quotedTableName) { ObjectNameParser objectNameParser = new ObjectNameParser(quotedTableName); using (DbCommand dbCommand = connection.CreateCommand()) { dbCommand.CommandText = $"drop table if exist {objectNameParser.ObjectName}"; if (transaction != null) { dbCommand.Transaction = transaction; } dbCommand.ExecuteNonQuery(); } }
internal static DmTable RelationsForTable(NpgsqlConnection connection, NpgsqlTransaction transaction, string tableName) { var commandRelations = @"select con.constraint_name as ""foreignKey"", att2.attname as ""columnName"", cl.relname as ""referenceTableName"", att.attname as ""referenceColumnName"", con.child_table as ""tableName"", con.child_schema from (select unnest(con1.conkey) as ""parent"", unnest(con1.confkey) as ""child"", con1.conname as constraint_name, con1.confrelid, con1.conrelid, cl.relname as child_table, ns.nspname as child_schema from pg_class cl join pg_namespace ns on cl.relnamespace = ns.oid join pg_constraint con1 on con1.conrelid = cl.oid where con1.contype = 'f' ) con join pg_attribute att on att.attrelid = con.confrelid and att.attnum = con.child join pg_class cl on cl.oid = con.confrelid join pg_attribute att2 on att2.attrelid = con.conrelid and att2.attnum = con.parent WHERE con.child_table = @tableName AND con.child_schema = current_schema()"; ObjectNameParser tableNameParser = new ObjectNameParser(tableName, "\"", "\""); DmTable dmTable = new DmTable(tableNameParser.UnquotedStringWithUnderScore); using (var sqlCommand = new NpgsqlCommand(commandRelations, connection, transaction)) { sqlCommand.Parameters.AddWithValue("@tableName", tableNameParser.ObjectName); using (var reader = sqlCommand.ExecuteReader()) { dmTable.Fill(reader); } } return(dmTable); }
public string CreateTableCommandText() { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.AppendLine($"CREATE TABLE {trackingName.QuotedString} ("); // Adding the primary key foreach (DmColumn pkColumn in TableDescription.PrimaryKey.Columns) { var quotedColumnName = new ObjectNameParser(pkColumn.ColumnName, "[", "]").QuotedString; var quotedColumnType = new ObjectNameParser(pkColumn.GetSqlDbTypeString(), "[", "]").QuotedString; quotedColumnType += pkColumn.GetSqlTypePrecisionString(); var nullableColumn = pkColumn.AllowDBNull ? "NULL" : "NOT NULL"; stringBuilder.AppendLine($"{quotedColumnName} {quotedColumnType} {nullableColumn}, "); } // adding the tracking columns stringBuilder.AppendLine($"[create_scope_id] [uniqueidentifier] NULL, "); stringBuilder.AppendLine($"[update_scope_id] [uniqueidentifier] NULL, "); stringBuilder.AppendLine($"[create_timestamp] [bigint] NULL, "); stringBuilder.AppendLine($"[update_timestamp] [bigint] NULL, "); stringBuilder.AppendLine($"[timestamp] [timestamp] NULL, "); stringBuilder.AppendLine($"[sync_row_is_tombstone] [bit] NOT NULL default(0), "); stringBuilder.AppendLine($"[last_change_datetime] [datetime] NULL, "); // adding the filter columns if (this.FilterColumns != null) { foreach (DmColumn filterColumn in this.FilterColumns) { var isPk = TableDescription.PrimaryKey.Columns.Any(dm => TableDescription.IsEqual(dm.ColumnName, filterColumn.ColumnName)); if (isPk) { continue; } var quotedColumnName = new ObjectNameParser(filterColumn.ColumnName, "[", "]").QuotedString; var quotedColumnType = new ObjectNameParser(filterColumn.GetSqlDbTypeString(), "[", "]").QuotedString; quotedColumnType += filterColumn.GetSqlTypePrecisionString(); var nullableColumn = filterColumn.AllowDBNull ? "NULL" : "NOT NULL"; stringBuilder.AppendLine($"{quotedColumnName} {quotedColumnType} {nullableColumn}, "); } } stringBuilder.Append(")"); return(stringBuilder.ToString()); }
private SqlCommand BuildForeignKeyConstraintsCommand(DmRelation foreignKey) { var sqlCommand = new SqlCommand(); string childTable = foreignKey.ChildTable.TableName; string childSchema = foreignKey.ChildTable.Schema; string childFullName = String.IsNullOrEmpty(childSchema) ? childTable : $"{childSchema}.{childTable}"; var childTableName = new ObjectNameParser(childFullName); string parentTable = foreignKey.ParentTable.TableName; string parentSchema = foreignKey.ParentTable.Schema; string parentFullName = String.IsNullOrEmpty(parentSchema) ? parentTable : $"{parentSchema}.{parentTable}"; var parentTableName = new ObjectNameParser(parentFullName); var relationName = NormalizeRelationName(foreignKey.RelationName); StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append("ALTER TABLE "); stringBuilder.AppendLine(childTableName.FullQuotedString); stringBuilder.Append("ADD CONSTRAINT "); stringBuilder.AppendLine(relationName); stringBuilder.Append("FOREIGN KEY ("); string empty = string.Empty; foreach (var childColumn in foreignKey.ChildColumns) { var childColumnName = new ObjectNameParser(childColumn.ColumnName); stringBuilder.Append($"{empty} {childColumnName.FullQuotedString}"); empty = ", "; } stringBuilder.AppendLine(" )"); stringBuilder.Append("REFERENCES "); stringBuilder.Append(parentTableName.FullQuotedString).Append(" ("); empty = string.Empty; foreach (var parentdColumn in foreignKey.ParentColumns) { var parentColumnName = new ObjectNameParser(parentdColumn.ColumnName); stringBuilder.Append($"{empty} {parentColumnName.FullQuotedString}"); empty = ", "; } stringBuilder.Append(" ) "); sqlCommand.CommandText = stringBuilder.ToString(); return(sqlCommand); }
public static bool ProcedureExists(SqlConnection connection, SqlTransaction transaction, string quotedProcedureName) { bool flag; ObjectNameParser objectNameParser = new ObjectNameParser(quotedProcedureName); using (SqlCommand sqlCommand = new SqlCommand("IF EXISTS (SELECT * FROM sys.procedures p JOIN sys.schemas s ON s.schema_id = p.schema_id WHERE p.name = @procName AND s.name = @schemaName) SELECT 1 ELSE SELECT 0", connection)) { sqlCommand.Parameters.AddWithValue("@procName", objectNameParser.ObjectName); sqlCommand.Parameters.AddWithValue("@schemaName", SqlManagementUtils.GetUnquotedSqlSchemaName(objectNameParser)); if (transaction != null) { sqlCommand.Transaction = transaction; } flag = (int)sqlCommand.ExecuteScalar() != 0; } return(flag); }
private NpgsqlCommand BuildForeignKeyConstraintsCommand(DmRelation foreignKey) { var sqlCommand = new NpgsqlCommand(); var childTable = foreignKey.ChildTable; var childTableName = new ObjectNameParser(childTable.TableName.ToLowerInvariant(), "\"", "\""); var parentTable = foreignKey.ParentTable; var parentTableName = new ObjectNameParser(parentTable.TableName.ToLowerInvariant(), "\"", "\""); var relationName = foreignKey.RelationName.Length > 50 ? foreignKey.RelationName.Substring(0, 50) : foreignKey.RelationName; var stringBuilder = new StringBuilder(); stringBuilder.Append("ALTER TABLE "); stringBuilder.AppendLine(parentTableName.QuotedString); stringBuilder.Append("ADD CONSTRAINT "); stringBuilder.AppendLine(relationName); stringBuilder.Append("FOREIGN KEY ("); var empty = string.Empty; foreach (var parentdColumn in foreignKey.ParentColumns) { var parentColumnName = new ObjectNameParser(parentdColumn.ColumnName.ToLowerInvariant(), "\"", "\""); stringBuilder.Append($"{empty} {parentColumnName.QuotedString}"); empty = ", "; } stringBuilder.AppendLine(" )"); stringBuilder.Append("REFERENCES "); stringBuilder.Append(childTableName.QuotedString).Append(" ("); empty = string.Empty; foreach (var childColumn in foreignKey.ChildColumns) { var childColumnName = new ObjectNameParser(childColumn.ColumnName.ToLowerInvariant(), "\"", "\""); stringBuilder.Append($"{empty} {childColumnName.QuotedString}"); } stringBuilder.Append(" ) "); sqlCommand.CommandText = stringBuilder.ToString(); return(sqlCommand); }
private void SetInsertMetadataParameters(DbCommand command) { DbParameter p; foreach (var column in TableDescription.PrimaryKey.Columns.Where(c => !c.ReadOnly)) { var quotedColumn = new ObjectNameParser(column.ColumnName); p = command.CreateParameter(); p.ParameterName = $"@in_{quotedColumn.UnquotedStringWithUnderScore.ToLowerInvariant()}"; p.DbType = column.DbType; p.SourceColumn = column.ColumnName; command.Parameters.Add(p); } /* * p = command.CreateParameter(); * p.ParameterName = "@create_scope_id"; * p.DbType = DbType.Guid; * command.Parameters.Add(p); * * p = command.CreateParameter(); * p.ParameterName = "@update_scope_id"; * p.DbType = DbType.Guid; * command.Parameters.Add(p);*/ p = command.CreateParameter(); p.ParameterName = "@sync_scope_id"; p.DbType = DbType.Guid; command.Parameters.Add(p); p = command.CreateParameter(); p.ParameterName = "@sync_row_is_tombstone"; p.DbType = DbType.Int32; command.Parameters.Add(p); p = command.CreateParameter(); p.ParameterName = "@create_timestamp"; p.DbType = DbType.Int64; command.Parameters.Add(p); p = command.CreateParameter(); p.ParameterName = "@update_timestamp"; p.DbType = DbType.Int64; command.Parameters.Add(p); }
private MySqlCommand BuildForeignKeyConstraintsCommand(DmRelation foreignKey) { var sqlCommand = new MySqlCommand(); var childTable = foreignKey.ChildTable; var childTableName = new ObjectNameParser(childTable.TableName, "`", "`"); var parentTable = foreignKey.ParentTable; var parentTableName = new ObjectNameParser(parentTable.TableName, "`", "`");; var relationName = NormalizeRelationName(foreignKey.RelationName); DmColumn[] foreignKeyColumns = foreignKey.ChildColumns; DmColumn[] referencesColumns = foreignKey.ParentColumns; var stringBuilder = new StringBuilder(); stringBuilder.Append("ALTER TABLE "); stringBuilder.AppendLine(childTableName.FullQuotedString); stringBuilder.Append("ADD CONSTRAINT "); stringBuilder.AppendLine($"`{relationName}`"); stringBuilder.Append("FOREIGN KEY ("); string empty = string.Empty; foreach (var foreignKeyColumn in foreignKeyColumns) { var foreignKeyColumnName = new ObjectNameParser(foreignKeyColumn.ColumnName, "`", "`"); stringBuilder.Append($"{empty} {foreignKeyColumnName.FullQuotedString}"); empty = ", "; } stringBuilder.AppendLine(" )"); stringBuilder.Append("REFERENCES "); stringBuilder.Append(parentTableName.FullQuotedString).Append(" ("); empty = string.Empty; foreach (var referencesColumn in referencesColumns) { var referencesColumnName = new ObjectNameParser(referencesColumn.ColumnName, "`", "`"); stringBuilder.Append($"{empty} {referencesColumnName.FullQuotedString}"); empty = ", "; } stringBuilder.Append(" ) "); sqlCommand.CommandText = stringBuilder.ToString(); return(sqlCommand); }
public static void DropTableIfExists(MySqlConnection connection, MySqlTransaction transaction, string quotedTableName) { ObjectNameParser objectNameParser = new ObjectNameParser(quotedTableName, "`", "`"); using (MySqlCommand dbCommand = connection.CreateCommand()) { dbCommand.CommandText = $"select * from information_schema.TABLES where table_schema = schema() and table_name = @tableName"; dbCommand.Parameters.AddWithValue("@tableName", objectNameParser.ObjectName); if (transaction != null) { dbCommand.Transaction = transaction; } dbCommand.ExecuteNonQuery(); } }