Esempio n. 1
0
        /// <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));
        }
Esempio n. 2
0
        /// <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);
        }
Esempio n. 3
0
        [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);
        }
Esempio n. 4
0
        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);
        }
Esempio n. 5
0
        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));
        }
Esempio n. 6
0
        public void DecodeMultipartIdentifierTest(string input, string[] output)
        {
            // If: I decode the input
            string[] decoded = SqlScriptFormatter.DecodeMultipartIdenfitier(input);

            // Then: The output should match what was expected
            Assert.Equal(output, decoded);
        }
Esempio n. 7
0
        public void FormatMultipartIdentifierArrayTest(string expectedOutput, string[] splits)
        {
            // If: I attempt to format a value as a multipart identifier
            string output = SqlScriptFormatter.FormatMultipartIdentifier(splits);

            // Then: The output should match the expected output
            Assert.Equal(expectedOutput, output);
        }
Esempio n. 8
0
 public static string[] GetEditTargetName(EditInitializeParams initParams)
 {
     // Step 1) Look up the SMO metadata
     if (initParams.SchemaName != null)
     {
         return(new [] { initParams.SchemaName, initParams.ObjectName });
     }
     return(SqlScriptFormatter.DecodeMultipartIdenfitier(initParams.ObjectName));
 }
Esempio n. 9
0
        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));
        }
Esempio n. 10
0
        /// <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);
        }
Esempio n. 11
0
        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));
        }
Esempio n. 12
0
        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));
        }
Esempio n. 13
0
        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);
        }
Esempio n. 14
0
        [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);
        }
Esempio n. 15
0
        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));
        }
Esempio n. 16
0
        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));
        }
Esempio n. 17
0
        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));
        }
Esempio n. 18
0
        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));
        }
Esempio n. 19
0
        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));
        }
        /// <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);
        }
Esempio n. 22
0
 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 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,
            });
        }
        /// <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,
            });
        }
Esempio n. 25
0
 public void UnescapeTest(string input, string output)
 {
     Assert.Equal(output, SqlScriptFormatter.UnwrapLiteral(input));
 }
Esempio n. 26
0
 public void DecodeMultipartIdentifierFailTest(string input)
 {
     // If: I decode an invalid input
     // Then: It should throw an exception
     Assert.Throws <FormatException>(() => SqlScriptFormatter.DecodeMultipartIdenfitier(input));
 }
        /// <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()
            });
        }
Esempio n. 28
0
 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));
 }