[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 = SqlScriptFormatter.FormatIdentifier(value); // Then: The output should match the expected output Assert.Equal(expectedOutput, output); }
private string GetTableClause() { // Get all the columns that will be provided var inColumns = from c in AssociatedResultSet.Columns where c.IsUpdatable select SqlScriptFormatter.FormatIdentifier(c.ColumnName); // Package it into a single INSERT statement starter string inColumnsJoined = string.Join(", ", inColumns); return(string.Format(InsertStart, AssociatedObjectMetadata.EscapedMultipartName, inColumnsJoined)); }
/// <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 all the columns. Add the column to the output columns, add updateable // columns to the input parameters List <string> outColumns = new List <string>(); List <string> inColumns = new List <string>(); DbCommand command = connection.CreateCommand(); for (int i = 0; i < AssociatedResultSet.Columns.Length; i++) { DbColumnWrapper column = AssociatedResultSet.Columns[i]; CellUpdate cell = newCells[i]; // Add the column to the output outColumns.Add($"inserted.{SqlScriptFormatter.FormatIdentifier(column.ColumnName)}"); // 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); } // Create a parameter for the value and add it to the command // Add the parameterization to the list and add it to the command string paramName = $"@Value{RowId}{i}"; inColumns.Add(paramName); SqlParameter param = new SqlParameter(paramName, cell.Column.SqlDbType) { Value = cell.Value }; command.Parameters.Add(param); } string joinedInColumns = string.Join(", ", inColumns); string joinedOutColumns = string.Join(", ", outColumns); // Get the start clause string start = GetTableClause(); // Put the whole #! together command.CommandText = string.Format(InsertCompleteOutput, start, joinedOutColumns, joinedInColumns); command.CommandType = CommandType.Text; return(command); }
/// <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)); }
/// <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); DbCommand command = connection.CreateCommand(); // Build the "SET" portion of the statement List <string> setComponents = new List <string>(); foreach (var updateElement in cellUpdates) { string formattedColumnName = SqlScriptFormatter.FormatIdentifier(updateElement.Value.Column.ColumnName); string paramName = $"@Value{RowId}_{updateElement.Key}"; setComponents.Add($"{formattedColumnName} = {paramName}"); SqlParameter parameter = new SqlParameter(paramName, updateElement.Value.Column.SqlDbType) { Value = updateElement.Value.Value }; command.Parameters.Add(parameter); } string setComponentsJoined = string.Join(", ", setComponents); // Build the "OUTPUT" portion of the statement var outColumns = from c in AssociatedResultSet.Columns let formatted = SqlScriptFormatter.FormatIdentifier(c.ColumnName) select $"inserted.{formatted}"; string outColumnsJoined = string.Join(", ", outColumns); // Get the where clause WhereClause where = GetWhereClause(true); command.Parameters.AddRange(where.Parameters.ToArray()); // Get the start of the statement string statementStart = GetStatementStart(); // Put the whole #! together command.CommandText = string.Format(UpdateScriptOutput, statementStart, setComponentsJoined, outColumnsJoined, where.CommandText); command.CommandType = CommandType.Text; return(command); }
/// <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 Server server = new Server(new ServerConnection(sqlConn)); 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 : SqlScriptFormatter.UnwrapLiteral(smoColumn.DefaultConstraint.Text); ColumnMetadata column = new ColumnMetadata { DefaultValue = defaultValue, EscapedName = SqlScriptFormatter.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 = SqlScriptFormatter.FormatMultipartIdentifier(objectNameParts); return(new TableMetadata { Columns = editColumns.ToArray(), EscapedMultipartName = escapedMultipartName, IsMemoryOptimized = isMemoryOptimized, }); }
public void FormatIdentifierNull() { // If: I attempt to format null as an identifier // Then: I should get an exception thrown Assert.Throws <ArgumentNullException>(() => SqlScriptFormatter.FormatIdentifier(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() }); }
/// <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 Server server = new Server(new ServerConnection(sqlConn)); 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]; // The default value may be escaped string defaultValue = smoColumn.DefaultConstraint == null ? null : SqlScriptFormatter.UnwrapLiteral(smoColumn.DefaultConstraint.Text); EditColumnMetadata column = new EditColumnMetadata { DefaultValue = defaultValue, EscapedName = SqlScriptFormatter.FormatIdentifier(smoColumn.Name), Ordinal = i, }; 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 = SqlScriptFormatter.FormatMultipartIdentifier(objectNameParts); return(new EditTableMetadata { Columns = editColumns.ToArray(), EscapedMultipartName = escapedMultipartName, IsMemoryOptimized = isMemoryOptimized, }); }