/// <summary>
/// Filters out metadata that is not present in the result set, and matches metadata ordering to resultset.
/// </summary>
public static EditColumnMetadata[] FilterColumnMetadata(EditColumnMetadata[] metaColumns, DbColumnWrapper[] resultColumns)
{
if (metaColumns.Length == 0)
{
return(metaColumns);
}
bool escapeColName = FromSqlScript.IsIdentifierBracketed(metaColumns[0].EscapedName);
Dictionary <string, int> columnNameOrdinalMap = new Dictionary <string, int>(capacity: resultColumns.Length);
for (int i = 0; i < resultColumns.Length; i++)
{
DbColumnWrapper column = resultColumns[i];
string columnName = column.ColumnName;
if (escapeColName && !FromSqlScript.IsIdentifierBracketed(columnName))
{
columnName = ToSqlScript.FormatIdentifier(columnName);
}
columnNameOrdinalMap.Add(columnName, column.ColumnOrdinal ?? i);
}
HashSet <string> resultColumnNames = columnNameOrdinalMap.Keys.ToHashSet();
metaColumns = Array.FindAll(metaColumns, column => resultColumnNames.Contains(column.EscapedName));
foreach (EditColumnMetadata metaCol in metaColumns)
{
metaCol.Ordinal = columnNameOrdinalMap[metaCol.EscapedName];
}
Array.Sort(metaColumns, (x, y) => (Comparer <int> .Default).Compare(x.Ordinal, y.Ordinal));
return(metaColumns);
}
/// <summary>
/// Generates the INSERT INTO statement that will apply the row creation
/// </summary>
/// <returns>INSERT INTO statement</returns>
public override string GetScript()
{
// Process the cells and columns
List <string> inColumns = new List <string>();
List <string> inValues = new List <string>();
for (int i = 0; i < AssociatedObjectMetadata.Columns.Length; i++)
{
DbColumnWrapper column = AssociatedResultSet.Columns[i];
CellUpdate cell = newCells[i];
// Continue if we're not inserting a value for this column
if (!IsCellValueProvided(column, cell, DefaultValues[i]))
{
continue;
}
// Column is provided
inColumns.Add(AssociatedObjectMetadata.Columns[i].EscapedName);
inValues.Add(ToSqlScript.FormatValue(cell.AsDbCellValue, column));
}
// Build the insert statement
return(inValues.Count > 0
? string.Format(InsertScriptValuesStatement,
AssociatedObjectMetadata.EscapedMultipartName,
string.Join(", ", inColumns),
string.Join(", ", inValues))
: string.Format(InsertScriptDefaultStatement, AssociatedObjectMetadata.EscapedMultipartName));
}
/// <summary>
/// Generates a WHERE clause that uses the key columns of the table to uniquely identity
/// the row that will be updated.
/// </summary>
/// <param name="parameterize">
/// Whether or not to generate a parameterized where clause. If <c>true</c> verbatim values
/// will be replaced with paremeters (like @Param12). The parameters must be added to the
/// SqlCommand used to execute the commit.
/// </param>
/// <returns>A <see cref="WhereClause"/> object</returns>
protected WhereClause GetWhereClause(bool parameterize)
{
WhereClause output = new WhereClause();
if (!AssociatedObjectMetadata.KeyColumns.Any())
{
throw new InvalidOperationException(SR.EditDataColumnNoKeyColumns);
}
IList <DbCellValue> row = AssociatedResultSet.GetRow(RowId);
foreach (EditColumnMetadata col in AssociatedObjectMetadata.KeyColumns)
{
// Put together a clause for the value of the cell
DbCellValue cellData = row[col.Ordinal];
string cellDataClause;
if (cellData.IsNull)
{
cellDataClause = "IS NULL";
}
else
{
if (cellData.RawObject is byte[] ||
col.DbColumn.DataTypeName.Equals("TEXT", StringComparison.OrdinalIgnoreCase) ||
col.DbColumn.DataTypeName.Equals("NTEXT", StringComparison.OrdinalIgnoreCase))
{
// Special cases for byte[] and TEXT/NTEXT types
cellDataClause = "IS NOT NULL";
}
else
{
// General case is to just use the value from the cell
if (parameterize)
{
// Add a parameter and parameterized clause component
// NOTE: We include the row ID to make sure the parameter is unique if
// we execute multiple row edits at once.
string paramName = $"@Param{RowId}{col.Ordinal}";
cellDataClause = $"= {paramName}";
SqlParameter parameter = new SqlParameter(paramName, col.DbColumn.SqlDbType)
{
Value = cellData.RawObject
};
output.Parameters.Add(parameter);
}
else
{
// Add the clause component with the formatted value
cellDataClause = $"= {ToSqlScript.FormatValue(cellData, col.DbColumn)}";
}
}
}
string completeComponent = $"({col.EscapedName} {cellDataClause})";
output.ClauseComponents.Add(completeComponent);
}
return(output);
}
public void FormatColumnType(bool useSemanticEquivalent, DbColumn input, string expectedOutput)
{
// If: I supply the input columns
string output = ToSqlScript.FormatColumnType(input, useSemanticEquivalent);
// Then: The output should match the expected output
Assert.Equal(expectedOutput, output);
}
public void NullDbCellTest()
{
// If: I attempt to format a null db cell
// Then: It should throw
DbColumn column = new FormatterTestDbColumn(null);
Assert.Throws <ArgumentNullException>(() => ToSqlScript.FormatValue(null, column));
}
public void UnsupportedColumnTest()
{
// If: I attempt to format an unsupported datatype
// Then: It should throw
DbColumn column = new FormatterTestDbColumn("unsupported");
Assert.Throws <ArgumentOutOfRangeException>(() => ToSqlScript.FormatValue(new DbCellValue(), column));
}
[InlineData("t]]est", "[t]]]]est]")] // Multiple escape characters
public void FormatIdentifierTest(string value, string expectedOutput)
{
// If: I attempt to format a value as an identifier
string output = ToSqlScript.FormatIdentifier(value);
// Then: The output should match the expected output
Assert.Equal(expectedOutput, output);
}
public void FormatMultipartIdentifierArrayTest(string expectedOutput, string[] splits)
{
// If: I attempt to format a value as a multipart identifier
string output = ToSqlScript.FormatMultipartIdentifier(splits);
// Then: The output should match the expected output
Assert.Equal(expectedOutput, output);
}
public void NullTest()
{
// If: I attempt to format a db cell that contains null
// Then: I should get the null string back
DbColumn column = new FormatterTestDbColumn(null);
string formattedString = ToSqlScript.FormatValue(new DbCellValue(), new FormatterTestDbColumn(null));
Assert.Equal(ToSqlScript.NullString, formattedString);
}
public void DoubleTest()
{
// Setup: Build a column and cell for the approx numeric type column
DbColumn column = new FormatterTestDbColumn("FLOAT");
DbCellValue cell = new DbCellValue {
RawObject = 3.14159d
};
// If: I attempt to format a approx numeric type column
string output = ToSqlScript.FormatValue(cell, column);
// Then: The output string should be able to be converted back into a double
Assert.Equal(cell.RawObject, double.Parse(output));
}
public void IntegerNumericTest(string dataType)
{
// Setup: Build a column and cell for the integer type column
DbColumn column = new FormatterTestDbColumn(dataType);
DbCellValue cell = new DbCellValue {
RawObject = (long)123
};
// If: I attempt to format an integer type column
string output = ToSqlScript.FormatValue(cell, column);
// Then: The output string should be able to be converted back into a long
Assert.Equal(cell.RawObject, long.Parse(output));
}
public void StringTypeTest(string datatype)
{
// Setup: Build a column and cell for the string type column
DbColumn column = new FormatterTestDbColumn(datatype);
DbCellValue cell = new DbCellValue {
RawObject = "test string"
};
// If: I attempt to format a string type column
string output = ToSqlScript.FormatValue(cell, column);
// Then: The output string should match the output string
Assert.Equal("N'test string'", output);
}
[InlineData("'", "N''''")] // Test with escaped character
public void StringFormattingTest(string input, string expectedOutput)
{
// Setup: Build a column and cell for the string type column
// NOTE: We're using VARCHAR because it's very general purpose.
DbColumn column = new FormatterTestDbColumn("VARCHAR");
DbCellValue cell = new DbCellValue {
RawObject = input
};
// If: I attempt to format a string type column
string output = ToSqlScript.FormatValue(cell, column);
// Then: The output string should be quoted and escaped properly
Assert.Equal(expectedOutput, output);
}
public void DecimalTest(string dataType, string regex, int?precision, int?scale)
{
// Setup: Build a column and cell for the decimal type column
DbColumn column = new FormatterTestDbColumn(dataType, precision, scale);
DbCellValue cell = new DbCellValue {
RawObject = 123.45m
};
// If: I attempt to format a decimal type column
string output = ToSqlScript.FormatValue(cell, column);
// Then: It should match a something like CAST(123.45 AS MONEY)
Regex castRegex = new Regex($@"CAST\([\d\.]+ AS {regex}", RegexOptions.IgnoreCase);
Assert.True(castRegex.IsMatch(output));
}
public void GuidTest()
{
// Setup: Build a column and cell for the string type column
DbColumn column = new FormatterTestDbColumn("UNIQUEIDENTIFIER");
DbCellValue cell = new DbCellValue {
RawObject = Guid.NewGuid()
};
// If: I attempt to format a string type column
string output = ToSqlScript.FormatValue(cell, column);
// Then: The output string should match the output string
Regex regex = new Regex(@"N'[0-9A-F]{8}(-[0-9A-F]{4}){3}-[0-9A-F]{12}'", RegexOptions.IgnoreCase);
Assert.True(regex.IsMatch(output));
}
public void TimeTest()
{
// Setup: Build a column and cell for the time type column
DbColumn column = new FormatterTestDbColumn("TIME");
DbCellValue cell = new DbCellValue {
RawObject = TimeSpan.FromHours(12)
};
// If: I attempt to format a time type column
string output = ToSqlScript.FormatValue(cell, column);
// Then: The output string should be able to be converted back into a timespan
Regex dateTimeRegex = new Regex("N'(.*)'");
TimeSpan outputDateTime;
Assert.True(TimeSpan.TryParse(dateTimeRegex.Match(output).Groups[1].Value, out outputDateTime));
}
public void BinaryTest(string datatype)
{
// Setup: Build a column and cell for the string type column
DbColumn column = new FormatterTestDbColumn(datatype);
DbCellValue cell = new DbCellValue
{
RawObject = new byte[] { 0x42, 0x45, 0x4e, 0x49, 0x53, 0x43, 0x4f, 0x4f, 0x4c }
};
// If: I attempt to format a string type column
string output = ToSqlScript.FormatValue(cell, column);
// Then: The output string should match the output string
Regex regex = new Regex("0x[0-9A-F]+", RegexOptions.IgnoreCase);
Assert.True(regex.IsMatch(output));
}
public void DateTimeOffsetTest()
{
// Setup: Build a column and cell for the datetime offset type column
DbColumn column = new FormatterTestDbColumn("DATETIMEOFFSET");
DbCellValue cell = new DbCellValue {
RawObject = DateTimeOffset.Now
};
// If: I attempt to format a datetime offset type column
string output = ToSqlScript.FormatValue(cell, column);
// Then: The output string should be able to be converted back into a datetime offset
Regex dateTimeRegex = new Regex("N'(.*)'");
DateTimeOffset outputDateTime;
Assert.True(DateTimeOffset.TryParse(dateTimeRegex.Match(output).Groups[1].Value, out outputDateTime));
}
/// <summary>
/// Constructs an update statement to change the associated row.
/// </summary>
/// <returns>An UPDATE statement</returns>
public override string GetScript()
{
// Build the "SET" portion of the statement
var setComponents = cellUpdates.Values.Select(cellUpdate =>
{
string formattedColumnName = ToSqlScript.FormatIdentifier(cellUpdate.Column.ColumnName);
string formattedValue = ToSqlScript.FormatValue(cellUpdate.Value, cellUpdate.Column);
return($"{formattedColumnName} = {formattedValue}");
});
string setClause = string.Join(", ", setComponents);
// Put everything together into a single query
string whereClause = GetWhereClause(false).CommandText;
string updateStatementFormat = AssociatedObjectMetadata.IsMemoryOptimized
? UpdateScriptMemOptimized
: UpdateScript;
return(string.Format(updateStatementFormat,
AssociatedObjectMetadata.EscapedMultipartName,
setClause,
whereClause
));
}
/// <summary>
/// Generates a edit-ready metadata object using SMO
/// </summary>
/// <param name="connection">Connection to use for getting metadata</param>
/// <param name="objectName">Name of the object to return metadata for</param>
/// <param name="objectType">Type of the object to return metadata for</param>
/// <returns>Metadata about the object requested</returns>
public TableMetadata GetObjectMetadata(DbConnection connection, string schemaName, string objectName, string objectType)
{
// Get a connection to the database for SMO purposes
SqlConnection sqlConn = connection as SqlConnection;
if (sqlConn == null)
{
// It's not actually a SqlConnection, so let's try a reliable SQL connection
ReliableSqlConnection reliableConn = connection as ReliableSqlConnection;
if (reliableConn == null)
{
// If we don't have connection we can use with SMO, just give up on using SMO
return(null);
}
// We have a reliable connection, use the underlying connection
sqlConn = reliableConn.GetUnderlyingConnection();
}
// Connect with SMO and get the metadata for the table
ServerConnection serverConnection;
if (sqlConn.AccessToken == null)
{
serverConnection = new ServerConnection(sqlConn);
}
else
{
serverConnection = new ServerConnection(sqlConn, new AzureAccessToken(sqlConn.AccessToken));
}
Server server = new Server(serverConnection);
Database database = server.Databases[sqlConn.Database];
TableViewTableTypeBase smoResult;
switch (objectType.ToLowerInvariant())
{
case "table":
Table table = string.IsNullOrEmpty(schemaName) ? new Table(database, objectName) : new Table(database, objectName, schemaName);
table.Refresh();
smoResult = table;
break;
case "view":
View view = string.IsNullOrEmpty(schemaName) ? new View(database, objectName) : new View(database, objectName, schemaName);
view.Refresh();
smoResult = view;
break;
default:
throw new ArgumentOutOfRangeException(nameof(objectType), SR.EditDataUnsupportedObjectType(objectType));
}
if (smoResult == null)
{
throw new ArgumentOutOfRangeException(nameof(objectName), SR.EditDataObjectMetadataNotFound);
}
// Generate the edit column metadata
List <ColumnMetadata> editColumns = new List <ColumnMetadata>();
for (int i = 0; i < smoResult.Columns.Count; i++)
{
Column smoColumn = smoResult.Columns[i];
// The default value may be escaped
string defaultValue = smoColumn.DefaultConstraint == null
? null
: FromSqlScript.UnwrapLiteral(smoColumn.DefaultConstraint.Text);
ColumnMetadata column = new ColumnMetadata
{
DefaultValue = defaultValue,
EscapedName = ToSqlScript.FormatIdentifier(smoColumn.Name),
Ordinal = i
};
editColumns.Add(column);
}
// Only tables can be memory-optimized
Table smoTable = smoResult as Table;
bool isMemoryOptimized = smoTable != null && smoTable.IsMemoryOptimized;
// Escape the parts of the name
string[] objectNameParts = { smoResult.Schema, smoResult.Name };
string escapedMultipartName = ToSqlScript.FormatMultipartIdentifier(objectNameParts);
return(new TableMetadata
{
Columns = editColumns.ToArray(),
EscapedMultipartName = escapedMultipartName,
IsMemoryOptimized = isMemoryOptimized,
});
}
/// <summary>
/// Generates a edit-ready metadata object using SMO
/// </summary>
/// <param name="connection">Connection to use for getting metadata</param>
/// <param name="objectNamedParts">Split and unwrapped name parts</param>
/// <param name="objectType">Type of the object to return metadata for</param>
/// <returns>Metadata about the object requested</returns>
public EditTableMetadata GetObjectMetadata(DbConnection connection, string[] objectNamedParts, string objectType)
{
Validate.IsNotNull(nameof(objectNamedParts), objectNamedParts);
if (objectNamedParts.Length <= 0)
{
throw new ArgumentNullException(nameof(objectNamedParts), SR.EditDataMetadataObjectNameRequired);
}
if (objectNamedParts.Length > 2)
{
throw new InvalidOperationException(SR.EditDataMetadataTooManyIdentifiers);
}
// Get a connection to the database for SMO purposes
SqlConnection sqlConn = connection as SqlConnection;
if (sqlConn == null)
{
// It's not actually a SqlConnection, so let's try a reliable SQL connection
ReliableSqlConnection reliableConn = connection as ReliableSqlConnection;
if (reliableConn == null)
{
// If we don't have connection we can use with SMO, just give up on using SMO
return(null);
}
// We have a reliable connection, use the underlying connection
sqlConn = reliableConn.GetUnderlyingConnection();
}
// Connect with SMO and get the metadata for the table
ServerConnection serverConnection;
if (sqlConn.AccessToken == null)
{
serverConnection = new ServerConnection(sqlConn);
}
else
{
serverConnection = new ServerConnection(sqlConn, new AzureAccessToken(sqlConn.AccessToken));
}
Server server = new Server(serverConnection);
Database db = new Database(server, sqlConn.Database);
TableViewTableTypeBase smoResult;
switch (objectType.ToLowerInvariant())
{
case "table":
smoResult = objectNamedParts.Length == 1
? new Table(db, objectNamedParts[0]) // No schema provided
: new Table(db, objectNamedParts[1], objectNamedParts[0]); // Schema provided
break;
case "view":
smoResult = objectNamedParts.Length == 1
? new View(db, objectNamedParts[0]) // No schema provided
: new View(db, objectNamedParts[1], objectNamedParts[0]); // Schema provided
break;
default:
throw new ArgumentOutOfRangeException(nameof(objectType), SR.EditDataUnsupportedObjectType(objectType));
}
// A bug in SMO makes it necessary to call refresh to attain certain properties (such as IsMemoryOptimized)
smoResult.Refresh();
if (smoResult.State != SqlSmoState.Existing)
{
throw new ArgumentOutOfRangeException(nameof(objectNamedParts), SR.EditDataObjectNotFound);
}
// Generate the edit column metadata
List <EditColumnMetadata> editColumns = new List <EditColumnMetadata>();
for (int i = 0; i < smoResult.Columns.Count; i++)
{
Column smoColumn = smoResult.Columns[i];
string defaultValue = null;
try
{
// The default value may be escaped
defaultValue = smoColumn.DefaultConstraint == null
? null
: FromSqlScript.UnwrapLiteral(smoColumn.DefaultConstraint.Text);
}
catch (PropertyCannotBeRetrievedException)
{
// This exception will be thrown when the user doesn't have view definition privilege,
// we can ignore it and use null as the default value;
}
EditColumnMetadata column = new EditColumnMetadata
{
DefaultValue = defaultValue,
EscapedName = ToSqlScript.FormatIdentifier(smoColumn.Name),
Ordinal = i,
IsHierarchyId = smoColumn.DataType.SqlDataType == SqlDataType.HierarchyId,
};
editColumns.Add(column);
}
// Only tables can be memory-optimized
Table smoTable = smoResult as Table;
bool isMemoryOptimized = false;
// TODO: Remove IsSupported check once SMO fixes broken IsMemoryOptimized scenario (TFS #10871823)
if (smoTable != null)
{
isMemoryOptimized = smoTable.IsSupportedProperty("IsMemoryOptimized") && smoTable.IsMemoryOptimized;
}
// Escape the parts of the name
string[] objectNameParts = { smoResult.Schema, smoResult.Name };
string escapedMultipartName = ToSqlScript.FormatMultipartIdentifier(objectNameParts);
return(new EditTableMetadata
{
Columns = editColumns.ToArray(),
EscapedMultipartName = escapedMultipartName,
IsMemoryOptimized = isMemoryOptimized
});
}
/// <summary>
/// Constructs a new cell update based on the the string value provided and the column
/// for the cell.
/// </summary>
/// <param name="column">Column the cell will be under</param>
/// <param name="valueAsString">The string from the client to convert to an object</param>
public CellUpdate(DbColumnWrapper column, string valueAsString)
{
Validate.IsNotNull(nameof(column), column);
Validate.IsNotNull(nameof(valueAsString), valueAsString);
// Store the state that won't be changed
try
{
Column = column;
Type columnType = column.DataType;
// Check for null
if (valueAsString == NullString)
{
ProcessNullValue();
}
else if (columnType == typeof(byte[]))
{
// Binary columns need special attention
ProcessBinaryCell(valueAsString);
}
else if (columnType == typeof(string))
{
ProcessTextCell(valueAsString);
}
else if (columnType == typeof(Guid))
{
Value = Guid.Parse(valueAsString);
ValueAsString = Value.ToString();
}
else if (columnType == typeof(TimeSpan))
{
ProcessTimespanColumn(valueAsString);
}
else if (columnType == typeof(DateTimeOffset))
{
Value = DateTimeOffset.Parse(valueAsString, CultureInfo.CurrentCulture);
ValueAsString = Value.ToString();
}
else if (columnType == typeof(bool))
{
ProcessBooleanCell(valueAsString);
}
// @TODO: Microsoft.SqlServer.Types.SqlHierarchyId
else
{
// Attempt to go straight to the destination type, if we know what it is, otherwise
// leave it as a string
Value = columnType != null
? Convert.ChangeType(valueAsString, columnType, CultureInfo.CurrentCulture)
: valueAsString;
ValueAsString = Value.ToString();
}
}
catch (FormatException fe)
{
// Pretty up the exception so the user can learn a bit from it
// NOTE: Other formatting errors raised by helpers are InvalidOperationException to
// avoid being prettied here
throw new FormatException(SR.EditDataInvalidFormat(column.ColumnName, ToSqlScript.FormatColumnType(column)), fe);
}
}
public void NullDbColumnTest()
{
// If: I attempt to format a null db column
// Then: It should throw
Assert.Throws <ArgumentNullException>(() => ToSqlScript.FormatValue(new DbCellValue(), null));
}
/// <summary>
/// Generates a command that can be executed to update a row -- and return the contents of
/// the updated row.
/// </summary>
/// <param name="connection">The connection the command should be associated with</param>
/// <returns>Command to update the row</returns>
public override DbCommand GetCommand(DbConnection connection)
{
Validate.IsNotNull(nameof(connection), connection);
// Process the cells and columns
List <string> declareColumns = new List <string>();
List <SqlParameter> inParameters = new List <SqlParameter>();
List <string> setComponents = new List <string>();
List <string> outClauseColumns = new List <string>();
List <string> selectColumns = new List <string>();
for (int i = 0; i < AssociatedObjectMetadata.Columns.Length; i++)
{
EditColumnMetadata metadata = AssociatedObjectMetadata.Columns[i];
// Add the output columns regardless of whether the column is read only
declareColumns.Add($"{metadata.EscapedName} {ToSqlScript.FormatColumnType(metadata.DbColumn, useSemanticEquivalent: true)}");
outClauseColumns.Add($"inserted.{metadata.EscapedName}");
selectColumns.Add(metadata.EscapedName);
// If we have a new value for the column, proccess it now
CellUpdate cellUpdate;
if (cellUpdates.TryGetValue(i, out cellUpdate))
{
string paramName = $"@Value{RowId}_{i}";
setComponents.Add($"{metadata.EscapedName} = {paramName}");
inParameters.Add(new SqlParameter(paramName, AssociatedResultSet.Columns[i].SqlDbType)
{
Value = cellUpdate.Value
});
}
}
// Put everything together into a single query
// Step 1) Build a temp table for inserting output values into
string tempTableName = $"@Update{RowId}Output";
string declareStatement = string.Format(DeclareStatement, tempTableName, string.Join(", ", declareColumns));
// Step 2) Build the update statement
WhereClause whereClause = GetWhereClause(true);
string updateStatementFormat = AssociatedObjectMetadata.IsMemoryOptimized
? UpdateOutputMemOptimized
: UpdateOutput;
string updateStatement = string.Format(updateStatementFormat,
AssociatedObjectMetadata.EscapedMultipartName,
string.Join(", ", setComponents),
string.Join(", ", outClauseColumns),
tempTableName,
whereClause.CommandText);
string validateScript = string.Format(CultureInfo.InvariantCulture, validateUpdateOnlyOneRow,
AssociatedObjectMetadata.EscapedMultipartName,
whereClause.CommandText);
// Step 3) Build the select statement
string selectStatement = string.Format(SelectStatement, string.Join(", ", selectColumns), tempTableName);
// Step 4) Put it all together into a results object
StringBuilder query = new StringBuilder();
query.AppendLine(declareStatement);
query.AppendLine(validateScript);
query.AppendLine(updateStatement);
query.AppendLine(selectStatement);
query.Append("END");
// Build the command
DbCommand command = connection.CreateCommand();
command.CommandText = query.ToString();
command.CommandType = CommandType.Text;
command.Parameters.AddRange(inParameters.ToArray());
command.Parameters.AddRange(whereClause.Parameters.ToArray());
return(command);
}
/// <summary>
/// Generates a command that can be executed to insert a new row -- and return the newly
/// inserted row.
/// </summary>
/// <param name="connection">The connection the command should be associated with</param>
/// <returns>Command to insert the new row</returns>
public override DbCommand GetCommand(DbConnection connection)
{
Validate.IsNotNull(nameof(connection), connection);
// Process the cells and columns
List <string> declareColumns = new List <string>();
List <string> inColumnNames = new List <string>();
List <string> outClauseColumnNames = new List <string>();
List <string> inValues = new List <string>();
List <SqlParameter> inParameters = new List <SqlParameter>();
List <string> selectColumns = new List <string>();
for (int i = 0; i < AssociatedObjectMetadata.Columns.Length; i++)
{
DbColumnWrapper column = AssociatedResultSet.Columns[i];
EditColumnMetadata metadata = AssociatedObjectMetadata.Columns[i];
CellUpdate cell = newCells[i];
// Add the output columns regardless of whether the column is read only
outClauseColumnNames.Add($"inserted.{metadata.EscapedName}");
declareColumns.Add($"{metadata.EscapedName} {ToSqlScript.FormatColumnType(column, useSemanticEquivalent: true)}");
selectColumns.Add(metadata.EscapedName);
// Continue if we're not inserting a value for this column
if (!IsCellValueProvided(column, cell, DefaultValues[i]))
{
continue;
}
// Add the input column
inColumnNames.Add(metadata.EscapedName);
// Add the input values as parameters
string paramName = $"@Value{RowId}_{i}";
inValues.Add(paramName);
inParameters.Add(new SqlParameter(paramName, column.SqlDbType)
{
Value = cell.Value
});
}
// Put everything together into a single query
// Step 1) Build a temp table for inserting output values into
string tempTableName = $"@Insert{RowId}Output";
string declareStatement = string.Format(DeclareStatement, tempTableName, string.Join(", ", declareColumns));
// Step 2) Build the insert statement
string joinedOutClauseNames = string.Join(", ", outClauseColumnNames);
string insertStatement = inValues.Count > 0
? string.Format(InsertOutputValuesStatement,
AssociatedObjectMetadata.EscapedMultipartName,
string.Join(", ", inColumnNames),
joinedOutClauseNames,
tempTableName,
string.Join(", ", inValues))
: string.Format(InsertOutputDefaultStatement,
AssociatedObjectMetadata.EscapedMultipartName,
joinedOutClauseNames,
tempTableName);
// Step 3) Build the select statement
string selectStatement = string.Format(SelectStatement, string.Join(", ", selectColumns), tempTableName);
// Step 4) Put it all together into a results object
StringBuilder query = new StringBuilder();
query.AppendLine(declareStatement);
query.AppendLine(insertStatement);
query.Append(selectStatement);
// Build the command
DbCommand command = connection.CreateCommand();
command.CommandText = query.ToString();
command.CommandType = CommandType.Text;
command.Parameters.AddRange(inParameters.ToArray());
return(command);
}
public void FormatIdentifierNull()
{
// If: I attempt to format null as an identifier
// Then: I should get an exception thrown
Assert.Throws <ArgumentNullException>(() => ToSqlScript.FormatIdentifier(null));
}
internal void UpdateColumnInformationWithMetadata(DbColumnWrapper[] columns)
{
if (columns == null || this.objectMetadata == null)
{
return;
}
foreach (DbColumnWrapper col in columns)
{
var columnMetadata = objectMetadata.Columns.FirstOrDefault(cm => { return(cm.EscapedName == ToSqlScript.FormatIdentifier(col.ColumnName)); });
col.IsHierarchyId = columnMetadata != null && columnMetadata.IsHierarchyId;
}
}