public static EditTableMetadata GetStandardMetadata(DbColumn[] columns, bool isMemoryOptimized = false)
{
// Create column metadata providers
var columnMetas = columns.Select((c, i) =>
{
var ecm = new EditColumnMetadata
{
EscapedName = c.ColumnName,
Ordinal = i
};
return(ecm);
}).ToArray();
// Create column wrappers
var columnWrappers = columns.Select(c => new DbColumnWrapper(c)).ToArray();
// Create the table metadata
EditTableMetadata editTableMetadata = new EditTableMetadata
{
Columns = columnMetas,
EscapedMultipartName = "tbl",
IsMemoryOptimized = isMemoryOptimized
};
editTableMetadata.Extend(columnWrappers);
return(editTableMetadata);
}
public void EmptyDataTest()
{
EditColumnMetadata[] metas = new EditColumnMetadata[0];
DbColumnWrapper[] cols = new DbColumnWrapper[0];
EditColumnMetadata[] filteredData = EditTableMetadata.FilterColumnMetadata(metas, cols);
ValidateFilteredData(filteredData, cols);
}
private EditColumnMetadata[] CreateMetadataColumns(string[] colNames)
{
EditColumnMetadata[] metas = new EditColumnMetadata[colNames.Length];
for (int i = 0; i < metas.Length; i++)
{
metas[i] = new EditColumnMetadata {
EscapedName = colNames[i], Ordinal = i
};
}
return(metas);
}
/// <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);
// Process the cells and columns
List <string> declareColumns = new List <string>();
List <SqlParameter> inParameters = new List <SqlParameter>();
List <string> setComponents = new List <string>();
List <string> outClauseColumns = new List <string>();
List <string> selectColumns = new List <string>();
for (int i = 0; i < AssociatedObjectMetadata.Columns.Length; i++)
{
EditColumnMetadata metadata = AssociatedObjectMetadata.Columns[i];
// Add the output columns regardless of whether the column is read only
declareColumns.Add($"{metadata.EscapedName} {ToSqlScript.FormatColumnType(metadata.DbColumn, useSemanticEquivalent: true)}");
outClauseColumns.Add($"inserted.{metadata.EscapedName}");
selectColumns.Add(metadata.EscapedName);
// If we have a new value for the column, proccess it now
CellUpdate cellUpdate;
if (cellUpdates.TryGetValue(i, out cellUpdate))
{
string paramName = $"@Value{RowId}_{i}";
setComponents.Add($"{metadata.EscapedName} = {paramName}");
inParameters.Add(new SqlParameter(paramName, AssociatedResultSet.Columns[i].SqlDbType)
{
Value = cellUpdate.Value
});
}
}
// Put everything together into a single query
// Step 1) Build a temp table for inserting output values into
string tempTableName = $"@Update{RowId}Output";
string declareStatement = string.Format(DeclareStatement, tempTableName, string.Join(", ", declareColumns));
// Step 2) Build the update statement
WhereClause whereClause = GetWhereClause(true);
string updateStatementFormat = AssociatedObjectMetadata.IsMemoryOptimized
? UpdateOutputMemOptimized
: UpdateOutput;
string updateStatement = string.Format(updateStatementFormat,
AssociatedObjectMetadata.EscapedMultipartName,
string.Join(", ", setComponents),
string.Join(", ", outClauseColumns),
tempTableName,
whereClause.CommandText);
string validateScript = string.Format(CultureInfo.InvariantCulture, validateUpdateOnlyOneRow,
AssociatedObjectMetadata.EscapedMultipartName,
whereClause.CommandText);
// 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(validateScript);
query.AppendLine(updateStatement);
query.AppendLine(selectStatement);
query.Append("END");
// Build the command
DbCommand command = connection.CreateCommand();
command.CommandText = query.ToString();
command.CommandType = CommandType.Text;
command.Parameters.AddRange(inParameters.ToArray());
command.Parameters.AddRange(whereClause.Parameters.ToArray());
return(command);
}
/// <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);
}
public TestDbColumnsWithTableMetadata(bool isMemoryOptimized, bool identityCol, int defaultCols, int nullableCols)
{
List <DbColumn> dbColumns = new List <DbColumn>();
List <DbColumnWrapper> columnWrappers = new List <DbColumnWrapper>();
List <EditColumnMetadata> columnMetadatas = new List <EditColumnMetadata>();
int startingOrdinal = 0;
// Add the identity column at the front of the table
if (identityCol)
{
const string colName = "id";
DbColumn dbColumn = new TestDbColumn(colName)
{
IsKey = true,
IsIdentity = true,
IsAutoIncrement = true
};
EditColumnMetadata columnMetadata = new EditColumnMetadata
{
EscapedName = colName,
Ordinal = startingOrdinal,
DefaultValue = null
};
dbColumns.Add(dbColumn);
columnWrappers.Add(new DbColumnWrapper(dbColumn));
columnMetadatas.Add(columnMetadata);
startingOrdinal++;
}
// Add each column to the table
for (int i = startingOrdinal; i < 3 + startingOrdinal; i++)
{
string colName = $"col{i}";
DbColumn dbColumn;
EditColumnMetadata columnMetadata;
if (i < defaultCols + startingOrdinal)
{
// This column will have a default value
dbColumn = new TestDbColumn(colName)
{
AllowDBNull = false
};
columnMetadata = new EditColumnMetadata
{
EscapedName = colName,
Ordinal = i,
DefaultValue = DefaultValue
};
}
else if (i < nullableCols + defaultCols + startingOrdinal)
{
// This column will be nullable
dbColumn = new TestDbColumn(colName)
{
AllowDBNull = true
};
columnMetadata = new EditColumnMetadata
{
EscapedName = colName,
Ordinal = i,
DefaultValue = null
};
}
else
{
// This column doesn't have a default value or is nullable
dbColumn = new TestDbColumn(colName)
{
AllowDBNull = false
};
columnMetadata = new EditColumnMetadata
{
EscapedName = colName,
Ordinal = i,
DefaultValue = null
};
}
dbColumns.Add(dbColumn);
columnWrappers.Add(new DbColumnWrapper(dbColumn));
columnMetadatas.Add(columnMetadata);
}
// Put together the table metadata
EditTableMetadata editTableMetadata = new EditTableMetadata
{
Columns = columnMetadatas.ToArray(),
EscapedMultipartName = TableName,
IsMemoryOptimized = isMemoryOptimized
};
editTableMetadata.Extend(columnWrappers.ToArray());
DbColumns = dbColumns.ToArray();
TableMetadata = editTableMetadata;
}