/// <summary>
/// Generates the INSERT INTO statement that will apply the row creation
/// </summary>
/// <returns>INSERT INTO statement</returns>
public override string GetScript()
{
// Process all the cells, and generate the values
List <string> values = new List <string>();
for (int i = 0; i < AssociatedResultSet.Columns.Length; i++)
{
DbColumnWrapper column = AssociatedResultSet.Columns[i];
CellUpdate cell = newCells[i];
// Skip columns that cannot be updated
if (!column.IsUpdatable)
{
continue;
}
// If we're missing a cell, then we cannot continue
if (cell == null)
{
throw new InvalidOperationException(SR.EditDataCreateScriptMissingValue);
}
// Format the value and add it to the list
values.Add(SqlScriptFormatter.FormatValue(cell.Value, column));
}
string joinedValues = string.Join(", ", values);
// Get the start clause
string start = GetTableClause();
// Put the whole #! together
return(string.Format(InsertCompleteScript, start, joinedValues));
}
/// <summary>
/// Sets the value of the cell in the associated row. If <paramref name="newValue"/> is
/// identical to the original value, this will remove the cell update from the row update.
/// </summary>
/// <param name="columnId">Ordinal of the columns that will be set</param>
/// <param name="newValue">String representation of the value the user input</param>
/// <returns>
/// The string representation of the new value (after conversion to target object) if the
/// a change is made. <c>null</c> is returned if the cell is reverted to it's original value.
/// </returns>
public override EditUpdateCellResult SetCell(int columnId, string newValue)
{
// Validate the value and convert to object
ValidateColumnIsUpdatable(columnId);
CellUpdate update = new CellUpdate(AssociatedResultSet.Columns[columnId], newValue);
// If the value is the same as the old value, we shouldn't make changes
// NOTE: We must use .Equals in order to ignore object to object comparisons
if (update.Value.Equals(associatedRow[columnId].RawObject))
{
// Remove any pending change and stop processing this (we don't care if we fail to remove something)
CellUpdate cu;
cellUpdates.TryRemove(columnId, out cu);
return(new EditUpdateCellResult
{
IsRowDirty = cellUpdates.Count > 0,
Cell = new EditCell(associatedRow[columnId], false)
});
}
// The change is real, so set it
cellUpdates.AddOrUpdate(columnId, update, (i, cu) => update);
return(new EditUpdateCellResult
{
IsRowDirty = true,
Cell = update.AsEditCell
});
}
/// <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 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>
/// Sets the value of a cell in the row to be added
/// </summary>
/// <param name="columnId">Ordinal of the column to set in the row</param>
/// <param name="newValue">String representation from the client of the value to add</param>
/// <returns>
/// The updated value as a string of the object generated from <paramref name="newValue"/>
/// </returns>
public override EditUpdateCellResult SetCell(int columnId, string newValue)
{
// Validate the column and the value and convert to object
ValidateColumnIsUpdatable(columnId);
CellUpdate update = new CellUpdate(AssociatedResultSet.Columns[columnId], newValue);
// Add the cell update to the
newCells[columnId] = update;
// Put together a result of the change
return(new EditUpdateCellResult
{
IsRowDirty = true, // Row creates will always be dirty
Cell = update.AsEditCell
});
}
private EditCell GetEditCell(CellUpdate cell, int index)
{
DbCellValue dbCell;
if (cell == null)
{
// Cell hasn't been provided by user yet, attempt to use the default value
dbCell = new DbCellValue
{
DisplayValue = DefaultValues[index] ?? string.Empty,
IsNull = false, // TODO: This doesn't properly consider null defaults
RawObject = null,
RowId = RowId
};
}
else
{
// Cell has been provided by user, so use that
dbCell = cell.AsDbCellValue;
}
return(new EditCell(dbCell, isDirty: true));
}
/// <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>
/// 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 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 the cells and columns
List <string> declareColumns = new List <string>();
List <string> inColumnNames = new List <string>();
List <string> outClauseColumnNames = new List <string>();
List <string> inValues = new List <string>();
List <SqlParameter> inParameters = new List <SqlParameter>();
List <string> selectColumns = new List <string>();
for (int i = 0; i < AssociatedObjectMetadata.Columns.Length; i++)
{
DbColumnWrapper column = AssociatedResultSet.Columns[i];
EditColumnMetadata metadata = AssociatedObjectMetadata.Columns[i];
CellUpdate cell = newCells[i];
// Add the output columns regardless of whether the column is read only
outClauseColumnNames.Add($"inserted.{metadata.EscapedName}");
declareColumns.Add($"{metadata.EscapedName} {ToSqlScript.FormatColumnType(column, useSemanticEquivalent: true)}");
selectColumns.Add(metadata.EscapedName);
// Continue if we're not inserting a value for this column
if (!IsCellValueProvided(column, cell, DefaultValues[i]))
{
continue;
}
// Add the input column
inColumnNames.Add(metadata.EscapedName);
// Add the input values as parameters
string paramName = $"@Value{RowId}_{i}";
inValues.Add(paramName);
inParameters.Add(new SqlParameter(paramName, column.SqlDbType)
{
Value = cell.Value
});
}
// Put everything together into a single query
// Step 1) Build a temp table for inserting output values into
string tempTableName = $"@Insert{RowId}Output";
string declareStatement = string.Format(DeclareStatement, tempTableName, string.Join(", ", declareColumns));
// Step 2) Build the insert statement
string joinedOutClauseNames = string.Join(", ", outClauseColumnNames);
string insertStatement = inValues.Count > 0
? string.Format(InsertOutputValuesStatement,
AssociatedObjectMetadata.EscapedMultipartName,
string.Join(", ", inColumnNames),
joinedOutClauseNames,
tempTableName,
string.Join(", ", inValues))
: string.Format(InsertOutputDefaultStatement,
AssociatedObjectMetadata.EscapedMultipartName,
joinedOutClauseNames,
tempTableName);
// Step 3) Build the select statement
string selectStatement = string.Format(SelectStatement, string.Join(", ", selectColumns), tempTableName);
// Step 4) Put it all together into a results object
StringBuilder query = new StringBuilder();
query.AppendLine(declareStatement);
query.AppendLine(insertStatement);
query.Append(selectStatement);
// Build the command
DbCommand command = connection.CreateCommand();
command.CommandText = query.ToString();
command.CommandType = CommandType.Text;
command.Parameters.AddRange(inParameters.ToArray());
return(command);
}
