private DbCommand CreateBulkDeleteCommand(DbConnection connection, DbTransaction transaction)
{
var procName = this.SqlObjectNames.GetStoredProcedureCommandName(DbStoredProcedureType.BulkDeleteRows);
StringBuilder stringBuilder = new StringBuilder(string.Concat("CREATE PROCEDURE ", procName));
string str = "\n\t";
var sqlParameterChangeTable = new SqlParameter("@changeTable", SqlDbType.Structured)
{
TypeName = this.SqlObjectNames.GetStoredProcedureCommandName(DbStoredProcedureType.BulkTableType)
};
stringBuilder.Append(string.Concat(str, SqlBuilderProcedure.CreateParameterDeclaration(sqlParameterChangeTable)));
stringBuilder.Append("\nAS\nBEGIN\n");
string joins = SqlManagementUtils.JoinTwoTablesOnClause(this.TableDescription.PrimaryKeys, "[changes]", "[base]");
stringBuilder.AppendLine($"DELETE {this.TableName.Schema().Quoted()}");
stringBuilder.AppendLine($"FROM {this.TableName.Quoted()} [base]");
stringBuilder.AppendLine($"JOIN @changeTable as [changes] ON {joins}");
stringBuilder.AppendLine("END");
var sqlCommand = new SqlCommand(stringBuilder.ToString(), (SqlConnection)connection, (SqlTransaction)transaction);
return(sqlCommand);
}
public SqlTableBuilder(SyncTable tableDescription, ParserName tableName, ParserName trackingTableName, SyncSetup setup) : base(tableDescription, tableName, trackingTableName, setup)
{
this.sqlBuilderProcedure = new SqlBuilderProcedure(tableDescription, tableName, trackingTableName, Setup);
this.sqlBuilderTable = new SqlBuilderTable(tableDescription, tableName, trackingTableName, Setup);
this.sqlBuilderTrackingTable = new SqlBuilderTrackingTable(tableDescription, tableName, trackingTableName, Setup);
this.sqlBuilderTrigger = new SqlBuilderTrigger(tableDescription, tableName, trackingTableName, Setup);
}
public SqlTableBuilder(SyncTable tableDescription, ParserName tableName, ParserName trackingTableName, SyncSetup setup, string scopeName)
: base(tableDescription, tableName, trackingTableName, setup, scopeName)
{
this.SqlObjectNames = new SqlObjectNames(tableDescription, tableName, trackingTableName, setup, scopeName);
this.SqlDbMetadata = new SqlDbMetadata();
this.sqlBuilderProcedure = new SqlBuilderProcedure(tableDescription, tableName, trackingTableName, Setup, scopeName);
this.sqlBuilderTable = new SqlBuilderTable(tableDescription, tableName, trackingTableName, Setup);
this.sqlBuilderTrackingTable = new SqlBuilderTrackingTable(tableDescription, tableName, trackingTableName, Setup);
this.sqlBuilderTrigger = new SqlBuilderTrigger(tableDescription, tableName, trackingTableName, Setup, scopeName);
}
private DbCommand CreateResetCommand(DbConnection connection, DbTransaction transaction)
{
var procName = this.SqlObjectNames.GetStoredProcedureCommandName(DbStoredProcedureType.Reset);
StringBuilder stringBuilder = new StringBuilder(string.Concat("CREATE PROCEDURE ", procName));
SqlParameter sqlParameter = new SqlParameter("@sync_row_count", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
string str = "\n\t";
stringBuilder.Append(string.Concat(str, SqlBuilderProcedure.CreateParameterDeclaration(sqlParameter)));
stringBuilder.Append("\nAS\nBEGIN\n");
stringBuilder.AppendLine($"DELETE FROM {this.TableName.Schema().Quoted().ToString()};");
stringBuilder.AppendLine(string.Concat("SET ", sqlParameter.ParameterName, " = @@ROWCOUNT;"));
stringBuilder.AppendLine("END");
var sqlCommand = new SqlCommand(stringBuilder.ToString(), (SqlConnection)connection, (SqlTransaction)transaction);
return(sqlCommand);
}
private DbCommand CreateBulkUpdateCommand(DbConnection connection, DbTransaction transaction)
{
var procName = this.SqlObjectNames.GetStoredProcedureCommandName(DbStoredProcedureType.BulkUpdateRows);
StringBuilder stringBuilder = new StringBuilder(string.Concat("CREATE PROCEDURE ", procName));
string str = "\n\t";
var sqlParameterChangeTable = new SqlParameter("@changeTable", SqlDbType.Structured)
{
TypeName = this.SqlObjectNames.GetStoredProcedureCommandName(DbStoredProcedureType.BulkTableType)
};
stringBuilder.Append(string.Concat(str, SqlBuilderProcedure.CreateParameterDeclaration(sqlParameterChangeTable)));
stringBuilder.Append("\nAS\nBEGIN\n");
var stringBuilderArguments = new StringBuilder();
var stringBuilderParameters = new StringBuilder();
string empty = string.Empty;
string str4 = SqlManagementUtils.JoinTwoTablesOnClause(this.TableDescription.PrimaryKeys, "[p]", "[t]");
string str5 = SqlManagementUtils.JoinTwoTablesOnClause(this.TableDescription.PrimaryKeys, "[changes]", "[base]");
string str6 = SqlManagementUtils.JoinTwoTablesOnClause(this.TableDescription.PrimaryKeys, "[t]", "[side]");
string str7 = SqlManagementUtils.JoinTwoTablesOnClause(this.TableDescription.PrimaryKeys, "[p]", "[side]");
// Check if we have auto inc column
if (this.TableDescription.HasAutoIncrementColumns)
{
stringBuilder.AppendLine();
stringBuilder.AppendLine($"SET IDENTITY_INSERT {this.TableName.Schema().Quoted().ToString()} ON;");
stringBuilder.AppendLine();
}
stringBuilder.AppendLine($"MERGE {this.TableName.Schema().Quoted().ToString()} AS [base]");
stringBuilder.AppendLine($"USING @changeTable as [changes] on {str5}");
var hasMutableColumns = this.TableDescription.GetMutableColumns(false).Any();
if (hasMutableColumns)
{
stringBuilder.AppendLine("WHEN MATCHED THEN");
foreach (var mutableColumn in this.TableDescription.Columns.Where(c => !c.IsReadOnly))
{
var columnName = ParserName.Parse(mutableColumn).Quoted().ToString();
stringBuilderArguments.Append(string.Concat(empty, columnName));
stringBuilderParameters.Append(string.Concat(empty, $"changes.{columnName}"));
empty = ", ";
}
stringBuilder.AppendLine();
stringBuilder.AppendLine($"\tUPDATE SET");
string strSeparator = "";
foreach (var mutableColumn in this.TableDescription.GetMutableColumns(false))
{
var columnName = ParserName.Parse(mutableColumn).Quoted().ToString();
stringBuilder.AppendLine($"\t{strSeparator}{columnName} = [changes].{columnName}");
strSeparator = ", ";
}
}
stringBuilder.AppendLine("WHEN NOT MATCHED BY TARGET THEN");
stringBuilderArguments = new StringBuilder();
stringBuilderParameters = new StringBuilder();
empty = string.Empty;
foreach (var mutableColumn in this.TableDescription.Columns.Where(c => !c.IsReadOnly))
{
var columnName = ParserName.Parse(mutableColumn).Quoted().ToString();
stringBuilderArguments.Append(string.Concat(empty, columnName));
stringBuilderParameters.Append(string.Concat(empty, $"[changes].{columnName}"));
empty = ", ";
}
stringBuilder.AppendLine();
stringBuilder.AppendLine($"\tINSERT");
stringBuilder.AppendLine($"\t({stringBuilderArguments.ToString()})");
stringBuilder.AppendLine($"\tVALUES ({stringBuilderParameters.ToString()});");
// Check if we have auto inc column
if (this.TableDescription.HasAutoIncrementColumns)
{
stringBuilder.AppendLine();
stringBuilder.AppendLine($"SET IDENTITY_INSERT {this.TableName.Schema().Quoted().ToString()} ON;");
stringBuilder.AppendLine();
}
stringBuilder.Append("\nEND");
var command = new SqlCommand(stringBuilder.ToString(), (SqlConnection)connection, (SqlTransaction)transaction);
return(command);
}
