/// <summary>
/// Generates the INSERT INTO statement that will apply the row creation
/// </summary>
/// <returns>INSERT INTO statement</returns>
public override string GetScript()
{
// Process all the cells, and generate the values
List <string> values = new List <string>();
for (int i = 0; i < AssociatedResultSet.Columns.Length; i++)
{
DbColumnWrapper column = AssociatedResultSet.Columns[i];
CellUpdate cell = newCells[i];
// Skip columns that cannot be updated
if (!column.IsUpdatable)
{
continue;
}
// If we're missing a cell, then we cannot continue
if (cell == null)
{
throw new InvalidOperationException(SR.EditDataCreateScriptMissingValue);
}
// Format the value and add it to the list
values.Add(SqlScriptFormatter.FormatValue(cell.Value, column));
}
string joinedValues = string.Join(", ", values);
// Get the start clause
string start = GetTableClause();
// Put the whole #! together
return(string.Format(InsertCompleteScript, start, joinedValues));
}
/// <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 = $"= {SqlScriptFormatter.FormatValue(cellData, col.DbColumn)}";
}
}
}
string completeComponent = $"({col.EscapedName} {cellDataClause})";
output.ClauseComponents.Add(completeComponent);
}
return(output);
}
public void NullTest()
{
// If: I attempt to format a db cell that contains null
// Then: I should get the null string back
string formattedString = SqlScriptFormatter.FormatValue(new DbCellValue(), new FormatterTestDbColumn(null));
Assert.Equal(SqlScriptFormatter.NullString, formattedString);
}
public void UnsupportedColumnTest()
{
// If: I attempt to format an unsupported datatype
// Then: It should throw
DbColumn column = new FormatterTestDbColumn("unsupported");
Assert.Throws <ArgumentOutOfRangeException>(() => SqlScriptFormatter.FormatValue(new DbCellValue(), column));
}
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 = SqlScriptFormatter.FormatValue(cell, column);
// Then: The output string should match the output string
Assert.Equal("N'test string'", output);
}
public void FloatTest()
{
// Setup: Build a column and cell for the approx numeric type column
DbColumn column = new FormatterTestDbColumn("REAL");
DbCellValue cell = new DbCellValue {
RawObject = (float)3.14159
};
// If: I attempt to format a approx numeric type column
string output = SqlScriptFormatter.FormatValue(cell, column);
// Then: The output string should be able to be converted back into a double
Assert.Equal(cell.RawObject, float.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 = SqlScriptFormatter.FormatValue(cell, column);
// Then: The output string should be able to be converted back into a long
Assert.Equal(cell.RawObject, long.Parse(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 = SqlScriptFormatter.FormatValue(cell, column);
// Then: The output string should be quoted and escaped properly
Assert.Equal(expectedOutput, 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 = SqlScriptFormatter.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 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 = SqlScriptFormatter.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 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 = SqlScriptFormatter.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 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 = SqlScriptFormatter.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 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 = SqlScriptFormatter.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 = SqlScriptFormatter.FormatIdentifier(cellUpdate.Column.ColumnName);
string formattedValue = SqlScriptFormatter.FormatValue(cellUpdate.Value, cellUpdate.Column);
return($"{formattedColumnName} = {formattedValue}");
});
string setClause = string.Join(", ", setComponents);
// Get the where clause
string whereClause = GetWhereClause(false).CommandText;
// Get the start of the statement
string statementStart = GetStatementStart();
// Put the whole #! together
return(string.Format(UpdateScript, statementStart, setClause, whereClause));
}
public void NullDbColumnTest()
{
// If: I attempt to format a null db column
// Then: It should throw
Assert.Throws <ArgumentNullException>(() => SqlScriptFormatter.FormatValue(new DbCellValue(), null));
}
/// <summary>
/// Generates an INSERT script that will insert this row
/// </summary>
/// <param name="forCommand">
/// If <c>true</c> the script will be generated with an OUTPUT clause for returning all
/// values in the inserted row (including computed values). The script will also generate
/// parameters for inserting the values.
/// If <c>false</c> the script will not have an OUTPUT clause and will have the values
/// directly inserted into the script (with proper escaping, of course).
/// </param>
/// <returns>A script build result object with the script text and any parameters</returns>
/// <exception cref="InvalidOperationException">
/// Thrown if there are columns that are not readonly, do not have default values, and were
/// not assigned values.
/// </exception>
private ScriptBuildResult BuildInsertScript(bool forCommand)
{
// Process all the columns in this table
List <string> inValues = new List <string>();
List <string> inColumns = new List <string>();
List <string> outColumns = new List <string>();
List <SqlParameter> sqlParameters = new List <SqlParameter>();
for (int i = 0; i < AssociatedObjectMetadata.Columns.Length; i++)
{
DbColumnWrapper column = AssociatedResultSet.Columns[i];
CellUpdate cell = newCells[i];
// Add an out column if we're doing this for a command
if (forCommand)
{
outColumns.Add($"inserted.{SqlScriptFormatter.FormatIdentifier(column.ColumnName)}");
}
// Skip columns that cannot be updated
if (!column.IsUpdatable)
{
continue;
}
// Make sure a value was provided for the cell
if (cell == null)
{
// If the column is not nullable and there is no default defined, then fail
if (!column.AllowDBNull.HasTrue() && DefaultValues[i] == null)
{
throw new InvalidOperationException(SR.EditDataCreateScriptMissingValue(column.ColumnName));
}
// There is a default value (or omitting the value is fine), so trust the db will apply it correctly
continue;
}
// Add the input values
if (forCommand)
{
// Since this script is for command use, add parameter for the input value to the list
string paramName = $"@Value{RowId}_{i}";
inValues.Add(paramName);
SqlParameter param = new SqlParameter(paramName, cell.Column.SqlDbType)
{
Value = cell.Value
};
sqlParameters.Add(param);
}
else
{
// This script isn't for command use, add the value, formatted for insertion
inValues.Add(SqlScriptFormatter.FormatValue(cell.Value, column));
}
// Add the column to the in columns
inColumns.Add(SqlScriptFormatter.FormatIdentifier(column.ColumnName));
}
// Begin the script (ie, INSERT INTO blah)
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.AppendFormat(InsertScriptStart, AssociatedObjectMetadata.EscapedMultipartName);
// Add the input columns (if there are any)
if (inColumns.Count > 0)
{
string joinedInColumns = string.Join(", ", inColumns);
queryBuilder.AppendFormat(InsertScriptColumns, joinedInColumns);
}
// Add the output columns (this will be empty if we are not building for command)
if (outColumns.Count > 0)
{
string joinedOutColumns = string.Join(", ", outColumns);
queryBuilder.AppendFormat(InsertScriptOut, joinedOutColumns);
}
// Add the input values (if there any) or use the default values
if (inValues.Count > 0)
{
string joinedInValues = string.Join(", ", inValues);
queryBuilder.AppendFormat(InsertScriptValues, joinedInValues);
}
else
{
queryBuilder.AppendFormat(InsertScriptDefault);
}
return(new ScriptBuildResult
{
ScriptText = queryBuilder.ToString(),
ScriptParameters = sqlParameters.ToArray()
});
}
