private void ExtendResultMetadata(List <DbColumn[]> columnSchemas, List <ResultSet> results)
{
if (columnSchemas.Count != results.Count)
{
return;
}
for (int i = 0; i < results.Count; i++)
{
ResultSet result = results[i];
DbColumn[] columnSchema = columnSchemas[i];
if (result.Columns.Length > columnSchema.Length)
{
throw new InvalidOperationException("Did not receive enough metadata columns.");
}
for (int j = 0; j < result.Columns.Length; j++)
{
DbColumnWrapper resultCol = result.Columns[j];
DbColumn schemaCol = columnSchema[j];
if (!string.Equals(resultCol.DataTypeName, schemaCol.DataTypeName, StringComparison.OrdinalIgnoreCase) ||
(!string.Equals(resultCol.ColumnName, schemaCol.ColumnName) &&
!string.IsNullOrEmpty(schemaCol.ColumnName) &&
!string.Equals(resultCol, SR.QueryServiceColumnNull)))
{
throw new InvalidOperationException("Inconsistent column metadata.");
}
result.Columns[j] = new DbColumnWrapper(schemaCol);
}
}
}
/// <summary>
/// Filters out metadata that is not present in the result set, and matches metadata ordering to resultset.
/// </summary>
public static EditColumnMetadata[] FilterColumnMetadata(EditColumnMetadata[] metaColumns, DbColumnWrapper[] resultColumns)
{
if (metaColumns.Length == 0)
{
return(metaColumns);
}
bool escapeColName = FromSqlScript.IsIdentifierBracketed(metaColumns[0].EscapedName);
Dictionary <string, int> columnNameOrdinalMap = new Dictionary <string, int>(capacity: resultColumns.Length);
for (int i = 0; i < resultColumns.Length; i++)
{
DbColumnWrapper column = resultColumns[i];
string columnName = column.ColumnName;
if (escapeColName && !FromSqlScript.IsIdentifierBracketed(columnName))
{
columnName = ToSqlScript.FormatIdentifier(columnName);
}
columnNameOrdinalMap.Add(columnName, column.ColumnOrdinal ?? i);
}
HashSet <string> resultColumnNames = columnNameOrdinalMap.Keys.ToHashSet();
metaColumns = Array.FindAll(metaColumns, column => resultColumnNames.Contains(column.EscapedName));
foreach (EditColumnMetadata metaCol in metaColumns)
{
metaCol.Ordinal = columnNameOrdinalMap[metaCol.EscapedName];
}
Array.Sort(metaColumns, (x, y) => (Comparer <int> .Default).Compare(x.Ordinal, y.Ordinal));
return(metaColumns);
}
/// <summary>
/// Generates the INSERT INTO statement that will apply the row creation
/// </summary>
/// <returns>INSERT INTO statement</returns>
public override string GetScript()
{
// Process the cells and columns
List <string> inColumns = new List <string>();
List <string> inValues = new List <string>();
for (int i = 0; i < AssociatedObjectMetadata.Columns.Length; i++)
{
DbColumnWrapper column = AssociatedResultSet.Columns[i];
CellUpdate cell = newCells[i];
// Continue if we're not inserting a value for this column
if (!IsCellValueProvided(column, cell, DefaultValues[i]))
{
continue;
}
// Column is provided
inColumns.Add(AssociatedObjectMetadata.Columns[i].EscapedName);
inValues.Add(ToSqlScript.FormatValue(cell.AsDbCellValue, column));
}
// Build the insert statement
return(inValues.Count > 0
? string.Format(InsertScriptValuesStatement,
AssociatedObjectMetadata.EscapedMultipartName,
string.Join(", ", inColumns),
string.Join(", ", inValues))
: string.Format(InsertScriptDefaultStatement, AssociatedObjectMetadata.EscapedMultipartName));
}
/// <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));
}
public void DataTypeAndPropertiesTest()
{
// check that data types array contains items
var serverDataTypes = DbColumnWrapper.AllServerDataTypes;
Assert.True(serverDataTypes.Count > 0);
// check default constructor doesn't throw
Assert.NotNull(new DbColumnWrapper());
// check various properties are either null or not null
var column = new TestColumn();
var wrapper = new DbColumnWrapper(column);
Assert.NotNull(wrapper.DataType);
Assert.Null(wrapper.AllowDBNull);
Assert.Null(wrapper.BaseCatalogName);
Assert.Null(wrapper.BaseColumnName);
Assert.Null(wrapper.BaseServerName);
Assert.Null(wrapper.BaseTableName);
Assert.Null(wrapper.ColumnOrdinal);
Assert.Null(wrapper.ColumnSize);
Assert.Null(wrapper.IsAliased);
Assert.Null(wrapper.IsAutoIncrement);
Assert.Null(wrapper.IsExpression);
Assert.Null(wrapper.IsHidden);
Assert.Null(wrapper.IsIdentity);
Assert.Null(wrapper.IsKey);
Assert.Null(wrapper.IsReadOnly);
Assert.Null(wrapper.IsUnique);
Assert.Null(wrapper.NumericPrecision);
Assert.Null(wrapper.NumericScale);
Assert.Null(wrapper.UdtAssemblyQualifiedName);
Assert.Null(wrapper.DataTypeName);
}
public void DbColumnConstructorTests()
{
// check that various constructor parameters initial the wrapper correctly
var w1 = new DbColumnWrapper(new TestColumn("varchar", int.MaxValue, "Microsoft SQL Server 2005 XML Showplan"));
Assert.True(w1.IsXml);
var w2 = new DbColumnWrapper(new TestColumn("binary"));
Assert.True(w2.IsBytes);
var w3 = new DbColumnWrapper(new TestColumn("varbinary", int.MaxValue));
Assert.True(w3.IsBytes);
var w4 = new DbColumnWrapper(new TestColumn("sql_variant"));
Assert.True(w4.IsSqlVariant);
var w5 = new DbColumnWrapper(new TestColumn("my_udt"));
Assert.True(w5.IsUdt);
var w6 = new DbColumnWrapper(new TestColumn("my_hieracrchy", null, null, "MICROSOFT.SQLSERVER.TYPES.SQLHIERARCHYID"));
Assert.True(w6.IsUdt);
}
public void DateTime2Test(int precision)
{
// Setup: Create some test values
// NOTE: We are doing these here instead of InlineData because DateTime values can't be written as constant expressions
DateTime[] testValues =
{
DateTime.Now, DateTime.UtcNow, DateTime.MinValue, DateTime.MaxValue
};
// Setup: Create a DATETIME column
DbColumnWrapper col = new DbColumnWrapper(new TestDbColumn
{
DataTypeName = "DaTeTiMe2",
NumericScale = precision
});
foreach (DateTime value in testValues)
{
string displayValue = VerifyReadWrite(sizeof(long) + 1, value, (writer, val) => writer.WriteDateTime(val),
(reader, rowId) => reader.ReadDateTime(0, rowId, col));
// Make sure the display value has a time string with variable number of milliseconds
Assert.True(Regex.IsMatch(displayValue, @"^[\d]{4}-[\d]{2}-[\d]{2} [\d]{2}:[\d]{2}:[\d]{2}"));
if (precision > 0)
{
Assert.True(Regex.IsMatch(displayValue, $@"\.[\d]{{{precision}}}$"));
}
}
}
public void TimeSpanTooLargeTest(string value)
{
// If: I create a cell update for a timespan column and provide a value that is over 24hrs
// Then: It should throw an exception
DbColumnWrapper col = GetWrapper <TimeSpan>("time");
Assert.Throws <InvalidOperationException>(() => new CellUpdate(col, value));
}
public void EmptyDataTest()
{
EditColumnMetadata[] metas = new EditColumnMetadata[0];
DbColumnWrapper[] cols = new DbColumnWrapper[0];
EditColumnMetadata[] filteredData = EditTableMetadata.FilterColumnMetadata(metas, cols);
ValidateFilteredData(filteredData, cols);
}
public void ByteArrayInvalidFormatTest()
{
// If: I attempt to create a CellUpdate for a binary column
// Then: It should throw an exception
DbColumnWrapper col = GetWrapper <byte[]>("binary");
Assert.Throws <FormatException>(() => new CellUpdate(col, "this is totally invalid"));
}
public void BoolInvalidFormatTest()
{
// If: I create a CellUpdate for a bool column and provide an invalid numeric value
// Then: It should throw an exception
DbColumnWrapper col = GetWrapper <bool>("bit");
Assert.Throws <ArgumentOutOfRangeException>(() => new CellUpdate(col, "12345"));
}
public void StringTooLongTest(string value)
{
// If: I attempt to create a CellUpdate to set it to a large string
// Then: I should get an exception thrown
DbColumnWrapper col = GetWrapper <string>("nvarchar", false, 6);
Assert.Throws <FormatException>(() => new CellUpdate(col, value));
}
private DbColumnWrapper[] CreateColumnWrappers(string[] colNames)
{
DbColumnWrapper[] cols = new DbColumnWrapper[colNames.Length];
for (int i = 0; i < cols.Length; i++)
{
cols[i] = new DbColumnWrapper(new TestDbColumn(colNames[i], i));
}
return(cols);
}
/// <summary>
/// Constructs a new cell update based on the the string value provided and the column
/// for the cell.
/// </summary>
/// <param name="column">Column the cell will be under</param>
/// <param name="valueAsString">The string from the client to convert to an object</param>
public CellUpdate(DbColumnWrapper column, string valueAsString)
{
Validate.IsNotNull(nameof(column), column);
Validate.IsNotNull(nameof(valueAsString), valueAsString);
// Store the state that won't be changed
Column = column;
Type columnType = column.DataType;
// Check for null
if (valueAsString == NullString)
{
ProcessNullValue();
}
else if (columnType == typeof(byte[]))
{
// Binary columns need special attention
ProcessBinaryCell(valueAsString);
}
else if (columnType == typeof(string))
{
// Special case for strings because the string value should stay the same as provided
// If user typed 'NULL' they mean NULL as text
Value = valueAsString == TextNullString ? NullString : valueAsString;
ValueAsString = valueAsString;
}
else if (columnType == typeof(Guid))
{
Value = Guid.Parse(valueAsString);
ValueAsString = Value.ToString();
}
else if (columnType == typeof(TimeSpan))
{
ProcessTimespanColumn(valueAsString);
}
else if (columnType == typeof(DateTimeOffset))
{
Value = DateTimeOffset.Parse(valueAsString, CultureInfo.CurrentCulture);
ValueAsString = Value.ToString();
}
else if (columnType == typeof(bool))
{
ProcessBooleanCell(valueAsString);
}
// @TODO: Microsoft.SqlServer.Types.SqlHierarchyId
else
{
// Attempt to go straight to the destination type, if we know what it is, otherwise
// leave it as a string
Value = columnType != null
? Convert.ChangeType(valueAsString, columnType, CultureInfo.CurrentCulture)
: valueAsString;
ValueAsString = Value.ToString();
}
}
private IList <DbColumnWrapper> MapColumns(ColumnInfo[] columns)
{
List <DbColumnWrapper> columnWrappers = new List <DbColumnWrapper>();
foreach (ColumnInfo column in columns)
{
DbColumnWrapper wrapper = new DbColumnWrapper(column);
columnWrappers.Add(wrapper);
}
return(columnWrappers);
}
public void ByteArrayTest(string strValue, byte[] expectedValue, string expectedString)
{
// If: I attempt to create a CellUpdate for a binary column
DbColumnWrapper col = GetWrapper <byte[]>("binary");
CellUpdate cu = new CellUpdate(col, strValue);
// Then: The value should be a binary and should match the expected data
Assert.IsType <byte[]>(cu.Value);
Assert.Equal(expectedValue, cu.Value);
Assert.Equal(expectedString, cu.ValueAsString);
Assert.Equal(col, cu.Column);
}
public void NullTextStringTest()
{
// If: I attempt to create a CellUpdate with the text 'NULL' (with mixed case)
DbColumnWrapper col = GetWrapper <string>("ntext");
CellUpdate cu = new CellUpdate(col, "'NULL'");
// Then: The value should be NULL
Assert.IsType <string>(cu.Value);
Assert.Equal("NULL", cu.Value);
Assert.Equal("'NULL'", cu.ValueAsString);
Assert.Equal(col, cu.Column);
}
public void BoolTest(string input, bool output, string outputString)
{
// If: I attempt to create a CellUpdate for a boolean column
DbColumnWrapper col = GetWrapper <bool>("bit");
CellUpdate cu = new CellUpdate(col, input);
// Then: The value should match what was expected
Assert.IsType <bool>(cu.Value);
Assert.Equal(output, cu.Value);
Assert.Equal(outputString, cu.ValueAsString);
Assert.Equal(col, cu.Column);
}
/// <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);
}
public void RoundTripTest(DbColumnWrapper col, object obj)
{
// Setup: Figure out the test string
string testString = obj.ToString();
// If: I attempt to create a CellUpdate
CellUpdate cu = new CellUpdate(col, testString);
// Then: The value and type should match what we put in
Assert.IsType(col.DataType, cu.Value);
Assert.Equal(obj, cu.Value);
Assert.Equal(testString, cu.ValueAsString);
Assert.Equal(col, cu.Column);
}
public void NullStringAllowedTest()
{
// If: I attempt to create a CellUpdate to set it to NULL
const string nullString = "NULL";
DbColumnWrapper col = GetWrapper <string>("ntext");
CellUpdate cu = new CellUpdate(col, nullString);
// Then: The value should be a DBNull and the string value should be the same as what
// was given
Assert.IsType <DBNull>(cu.Value);
Assert.Equal(DBNull.Value, cu.Value);
Assert.Equal(nullString, cu.ValueAsString);
Assert.Equal(col, cu.Column);
}
/// <summary>
/// Performs validation of column ID and if column can be updated.
/// </summary>
/// <exception cref="ArgumentOutOfRangeException">
/// If <paramref name="columnId"/> is less than 0 or greater than the number of columns
/// in the row
/// </exception>
/// <exception cref="InvalidOperationException">If the column is not updatable</exception>
/// <param name="columnId">Ordinal of the column to update</param>
protected void ValidateColumnIsUpdatable(int columnId)
{
// Sanity check that the column ID is within the range of columns
if (columnId >= AssociatedResultSet.Columns.Length || columnId < 0)
{
throw new ArgumentOutOfRangeException(nameof(columnId), SR.EditDataColumnIdOutOfRange);
}
DbColumnWrapper column = AssociatedResultSet.Columns[columnId];
if (!column.IsUpdatable)
{
throw new InvalidOperationException(SR.EditDataColumnCannotBeEdited);
}
}
/// <summary>
/// Extracts extended column properties from the database columns from SQL Client
/// </summary>
/// <param name="dbColumn">The column information provided by SQL Client</param>
public void Extend(DbColumnWrapper dbColumn)
{
Validate.IsNotNull(nameof(dbColumn), dbColumn);
DbColumn = dbColumn;
// A column is trustworthy for uniqueness if it can be updated or it has an identity
// property. If both of these are false (eg, timestamp) we can't trust it to uniquely
// identify a row in the table
IsTrustworthyForUniqueness = dbColumn.IsUpdatable || dbColumn.IsIdentity.HasTrue();
// A key column is determined by whether it is a key
IsKey = dbColumn.IsKey;
// A column is calculated if it is identity, computed, or otherwise not updatable
IsCalculated = IsIdentity || IsComputed || !dbColumn.IsUpdatable;
// Mark the column as extended
HasExtendedProperties = true;
}
public void DateTime2InvalidScaleTest()
{
// Setup: Create some test values
// NOTE: We are doing these here instead of InlineData because DateTime values can't be written as constant expressions
DateTime[] testValues =
{
DateTime.Now, DateTime.UtcNow, DateTime.MinValue, DateTime.MaxValue
};
// Setup: Create a DATETIME2 column
DbColumnWrapper col = new DbColumnWrapper(new TestDbColumn("col", "DaTeTiMe2", 255));
foreach (DateTime value in testValues)
{
string displayValue = VerifyReadWrite(sizeof(long) + 1, value, (writer, val) => writer.WriteDateTime(val), reader => reader.ReadDateTime(0, col));
// Make sure the display value has a time string with 7 milliseconds
Assert.True(Regex.IsMatch(displayValue, @"^[\d]{4}-[\d]{2}-[\d]{2} [\d]{2}:[\d]{2}:[\d]{2}\.[\d]{7}$"));
}
}
/// <summary>
/// Reads a DateTime from the file at the offset provided
/// </summary>
/// <param name="offset">Offset into the file to read the DateTime from</param>
/// <param name="rowId">Internal ID of the row that will be stored in the cell</param>
/// <param name="col">Column metadata, used for determining what precision to output</param>
/// <returns>A DateTime</returns>
internal FileStreamReadResult ReadDateTime(long offset, long rowId, DbColumnWrapper col)
{
return(ReadCellHelper(offset, rowId, length =>
{
long ticks = BitConverter.ToInt64(buffer, 0);
return new DateTime(ticks);
}, null, dt =>
{
// Switch based on the type of column
string formatString;
if (col.DataTypeName.Equals("DATE", StringComparison.OrdinalIgnoreCase))
{
// DATE columns should only show the date
formatString = DateFormatString;
}
else if (col.DataTypeName.StartsWith("DATETIME", StringComparison.OrdinalIgnoreCase))
{
// DATETIME and DATETIME2 columns should show date, time, and a variable number
// of milliseconds (for DATETIME, it is fixed at 3, but returned as null)
// If for some strange reason a scale > 7 is sent, we will cap it at 7 to avoid
// an exception from invalid date/time formatting
int scale = Math.Min(col.NumericScale ?? 3, 7);
formatString = $"{DateFormatString} {TimeFormatString}";
if (scale > 0)
{
string millisecondString = new string('f', scale);
formatString += $".{millisecondString}";
}
}
else
{
// For anything else that returns as a CLR DateTime, just show date and time
formatString = $"{DateFormatString} {TimeFormatString}";
}
return dt.ToString(formatString);
}));
}
public void DateTest()
{
// Setup: Create some test values
// NOTE: We are doing these here instead of InlineData because DateTime values can't be written as constant expressions
DateTime[] testValues =
{
DateTime.Now, DateTime.UtcNow, DateTime.MinValue, DateTime.MaxValue
};
// Setup: Create a DATE column
DbColumnWrapper col = new DbColumnWrapper(new TestDbColumn {
DataTypeName = "DaTe"
});
foreach (DateTime value in testValues)
{
string displayValue = VerifyReadWrite(sizeof(long) + 1, value, (writer, val) => writer.WriteDateTime(val),
(reader, rowId) => reader.ReadDateTime(0, rowId, col));
// Make sure the display value does not have a time string
Assert.True(Regex.IsMatch(displayValue, @"^[\d]{4}-[\d]{2}-[\d]{2}$"));
}
}
/// <summary>
/// Verifies the column and cell, ensuring a column that needs a value has one.
/// </summary>
/// <param name="column">Column that will be inserted into</param>
/// <param name="cell">Current cell value for this row</param>
/// <param name="defaultCell">Default value for the column in this row</param>
/// <exception cref="InvalidOperationException">
/// Thrown if the column needs a value but it is not provided
/// </exception>
/// <returns>
/// <c>true</c> If the column has a value provided
/// <c>false</c> If the column does not have a value provided (column is read-only, has default, etc)
/// </returns>
private static bool IsCellValueProvided(DbColumnWrapper column, CellUpdate cell, string defaultCell)
{
// Skip columns that cannot be updated
if (!column.IsUpdatable)
{
return(false);
}
// Make sure a value was provided for the cell
if (cell == null)
{
// If the column is not nullable and there is not default defined, then fail
if (!column.AllowDBNull.HasTrue() && defaultCell == 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
return(false);
}
return(true);
}
/// <summary>
/// Writes an entire row to the file stream
/// </summary>
/// <param name="reader">A primed reader</param>
/// <returns>Number of bytes used to write the row</returns>
public int WriteRow(StorageDataReader reader)
{
// Read the values in from the db
object[] values = new object[reader.Columns.Length];
if (!reader.HasLongColumns)
{
// get all record values in one shot if there are no extra long fields
reader.GetValues(values);
}
// Loop over all the columns and write the values to the temp file
int rowBytes = 0;
for (int i = 0; i < reader.Columns.Length; i++)
{
DbColumnWrapper ci = reader.Columns[i];
if (reader.HasLongColumns)
{
if (reader.IsDBNull(i))
{
// Need special case for DBNull because
// reader.GetValue doesn't return DBNull in case of SqlXml and CLR type
values[i] = DBNull.Value;
}
else
{
if (!ci.IsLong)
{
// not a long field
values[i] = reader.GetValue(i);
}
else
{
// this is a long field
if (ci.IsBytes)
{
values[i] = reader.GetBytesWithMaxCapacity(i, maxCharsToStore);
}
else if (ci.IsChars)
{
Debug.Assert(maxCharsToStore > 0);
values[i] = reader.GetCharsWithMaxCapacity(i,
ci.IsXml ? maxXmlCharsToStore : maxCharsToStore);
}
else if (ci.IsXml)
{
Debug.Assert(maxXmlCharsToStore > 0);
values[i] = reader.GetXmlWithMaxCapacity(i, maxXmlCharsToStore);
}
else
{
// we should never get here
Debug.Assert(false);
}
}
}
}
// Get true type of the object
Type tVal = values[i].GetType();
// Write the object to a file
if (tVal == typeof(DBNull))
{
rowBytes += WriteNull();
}
else
{
if (ci.IsSqlVariant)
{
// serialize type information as a string before the value
string val = tVal.ToString();
rowBytes += WriteString(val);
}
// Use the appropriate writing method for the type
Func <object, int> writeMethod;
if (writeMethods.TryGetValue(tVal, out writeMethod))
{
rowBytes += writeMethod(values[i]);
}
else
{
rowBytes += WriteString(values[i].ToString());
}
}
}
// Flush the buffer after every row
FlushBuffer();
return(rowBytes);
}
/// <summary>
/// Constructs a new cell update based on the the string value provided and the column
/// for the cell.
/// </summary>
/// <param name="column">Column the cell will be under</param>
/// <param name="valueAsString">The string from the client to convert to an object</param>
public CellUpdate(DbColumnWrapper column, string valueAsString)
{
Validate.IsNotNull(nameof(column), column);
Validate.IsNotNull(nameof(valueAsString), valueAsString);
// Store the state that won't be changed
try
{
Column = column;
Type columnType = column.DataType;
// Check for null
if (valueAsString == NullString)
{
ProcessNullValue();
}
else if (columnType == typeof(byte[]))
{
// Binary columns need special attention
ProcessBinaryCell(valueAsString);
}
else if (columnType == typeof(string))
{
ProcessTextCell(valueAsString);
}
else if (columnType == typeof(Guid))
{
Value = Guid.Parse(valueAsString);
ValueAsString = Value.ToString();
}
else if (columnType == typeof(TimeSpan))
{
ProcessTimespanColumn(valueAsString);
}
else if (columnType == typeof(DateTimeOffset))
{
Value = DateTimeOffset.Parse(valueAsString, CultureInfo.CurrentCulture);
ValueAsString = Value.ToString();
}
else if (columnType == typeof(bool))
{
ProcessBooleanCell(valueAsString);
}
// @TODO: Microsoft.SqlServer.Types.SqlHierarchyId
else
{
// Attempt to go straight to the destination type, if we know what it is, otherwise
// leave it as a string
Value = columnType != null
? Convert.ChangeType(valueAsString, columnType, CultureInfo.CurrentCulture)
: valueAsString;
ValueAsString = Value.ToString();
}
}
catch (FormatException fe)
{
// Pretty up the exception so the user can learn a bit from it
// NOTE: Other formatting errors raised by helpers are InvalidOperationException to
// avoid being prettied here
throw new FormatException(SR.EditDataInvalidFormat(column.ColumnName, ToSqlScript.FormatColumnType(column)), fe);
}
}
/// <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()
});
}