Пример #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));
        }
Пример #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);
        }
Пример #3
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);
        }
Пример #4
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));
        }
Пример #5
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);
        }
Пример #6
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));
        }
Пример #7
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));
        }
Пример #8
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);
        }
Пример #9
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));
        }
Пример #10
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));
        }
Пример #11
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));
        }
Пример #12
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));
        }
Пример #13
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));
        }
        /// <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));
        }
Пример #15
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 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()
            });
        }