/// <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() }); }