public CStoredProcedure Convert(CTable table)
{
//http://michaeljswart.com/2011/09/mythbusting-concurrent-updateinsert-solutions/
var storedProcedure = new CStoredProcedure(DataStoreTypes.SqlServer)
{
Schema = new CSchema {
SchemaName = $"{table.Schema.SchemaName}Api"
},
StoredProcedureName = $"{table.TableName}AddUpdate",
ParameterSetName = table.TableName,
ResultSetName = $"{table.TableName}AddUpdateResult",
ReturnsMultipleRows = true,
DataOperationIs = COperationIs.Add | COperationIs.Update | COperationIs.CRUD
};
table.InsertStoredProcedure = storedProcedure;
storedProcedure.Parameter.AddRange(GetParameters(table));
var primaryKeyCol = table.Column.FirstOrDefault(c => c.IsPrimaryKey);
var insertUpdateColumnList = GetInsertUpdateColumnList(table);
var insertUpdateColumnMappings = GetInsertUpdateColumnMappings(table);
var insertUpdateColumnValues = GetInsertUpateColumnValues(table);
var redefines = GetRedefines(table);
var pkCompares = GetPrimaryKeyCompares(table);
var rowVersionColumn = table.GetRowVersionColumn();
var insertCondition = rowVersionColumn != null
? $" AND NEW_T.[{primaryKeyCol.ColumnName}] = 0 AND NEW_T.[{rowVersionColumn.ColumnName}] IS NULL "
: string.Empty;
var updateCondition = rowVersionColumn != null
? $" AND (NEW_T.[{rowVersionColumn.ColumnName}] IS NULL OR T.[{rowVersionColumn.ColumnName}] = NEW_T.[{rowVersionColumn.ColumnName}]) "
: string.Empty;
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine("SET NOCOUNT ON;");
stringBuilder.AppendLine(
"SET XACT_ABORT ON;"); //http://michaeljswart.com/2015/10/dont-abandon-your-transactions/
stringBuilder.AppendLine();
//create table variable to hold output
stringBuilder.Append($@"DECLARE @Output TABLE (Action VARCHAR(20),
[{primaryKeyCol.ColumnName}] {
SqlMapper.DbTypeToSqlDataTypeOption(primaryKeyCol.ColumnType)
}");
if (primaryKeyCol.ColumnLength > 0)
{
stringBuilder.Append($"({primaryKeyCol.ColumnLength})");
}
var rowVersionCol = string.Empty;
var insertedRowVersionCol = string.Empty;
if (rowVersionColumn != null)
{
rowVersionCol = $", {rowVersionColumn.ColumnName}";
insertedRowVersionCol =
$", inserted.[{rowVersionColumn.ColumnName}] as '[{rowVersionColumn.ColumnName}]'";
stringBuilder.Append($", [{rowVersionColumn.ColumnName}] binary(8)");
}
stringBuilder.AppendLine($");");
stringBuilder.AppendLine();
stringBuilder.AppendLine(
$@"MERGE [{table.Schema.SchemaName}].[{table.TableName}] WITH (HOLDLOCK) AS T
USING (SELECT {redefines} ) as NEW_T
ON {pkCompares}
WHEN MATCHED {updateCondition} THEN
UPDATE
SET
{insertUpdateColumnMappings}
WHEN NOT MATCHED {insertCondition} THEN
INSERT
(
{insertUpdateColumnList}
)
VALUES
(
{insertUpdateColumnValues}
)
OUTPUT
$action, inserted.[{primaryKeyCol.ColumnName}] as '[{primaryKeyCol.ColumnName}]' {
insertedRowVersionCol
} INTO @Output;
--TODO: Use OUTPUT to insert original values into Audit tables
SELECT Action, [{primaryKeyCol.ColumnName}] {rowVersionCol} FROM @Output;");
/*
* var identityCol = table.Column.FirstOrDefault(c => c.IsIdentity);
* if (identityCol != null)
* {
* stringBuilder.AppendLine();
* stringBuilder.AppendLine($"SELECT CAST(SCOPE_IDENTITY() as {SqlMapper.DbTypeToSqlDbType( identityCol.ColumnType)})");
*
* }
*/
storedProcedure.StoredProcedureBody = stringBuilder.ToString();
storedProcedure.ResultSet.Add(new CColumn(storedProcedure)
{
ColumnName = "Action",
ColumnTypeRaw = "varchar",
ColumnSqlDbType = SqlDbType.VarChar,
ColumnType = DbType.AnsiString,
ColumnLength = 20
});
storedProcedure.ResultSet.Add(new CColumn(storedProcedure)
{
ColumnName = primaryKeyCol.ColumnName,
ColumnTypeRaw = primaryKeyCol.ColumnTypeRaw,
ColumnSqlDbType = primaryKeyCol.ColumnSqlDbType,
ColumnType = primaryKeyCol.ColumnType,
ColumnLength = primaryKeyCol.ColumnLength
});
if (rowVersionColumn != null)
{
storedProcedure.ResultSet.Add(new CColumn(storedProcedure)
{
ColumnName = rowVersionColumn.ColumnName,
ColumnTypeRaw = rowVersionColumn.ColumnTypeRaw,
ColumnType = rowVersionColumn.ColumnType,
ColumnSqlDbType = rowVersionColumn.ColumnSqlDbType,
ColumnLength = rowVersionColumn.ColumnLength
});
}
return(storedProcedure);
}
