public static void WriteTableSchema(DocumentMap mapping, string tableNameOverride, StringBuilder result)
{
var tableName = tableNameOverride ?? mapping.TableName;
result.AppendLine("CREATE TABLE [TestSchema].[" + tableName + "] (");
result.Append($" [Id] {GetDatabaseType(mapping.IdColumn)} NOT NULL CONSTRAINT [PK_{tableName}_Id] PRIMARY KEY CLUSTERED, ").AppendLine();
foreach (var column in mapping.WritableIndexedColumns())
{
result.AppendFormat(" [{0}] {1} {2}, ", column.ColumnName, GetDatabaseType(column).ToUpperInvariant(), IsNullable(column) ? "NULL" : "NOT NULL").AppendLine();
}
result.AppendFormat(" [JSON] NVARCHAR(MAX) NOT NULL").AppendLine();
result.AppendLine(")");
foreach (var unique in mapping.UniqueConstraints)
{
result.AppendFormat("ALTER TABLE [TestSchema].[{0}] ADD CONSTRAINT [UQ_{1}] UNIQUE({2})", tableName,
unique.ConstraintName,
string.Join(", ", unique.Columns.Select(ix => "[" + ix + "]"))).AppendLine();
}
foreach (var referencedDocumentMap in mapping.RelatedDocumentsMappings)
{
var refTblName = referencedDocumentMap.TableName;
var refSchemaName = referencedDocumentMap.SchemaName;
result.AppendLine($"IF NOT EXISTS (SELECT name from sys.tables WHERE name = '{refTblName}')");
result.AppendLine($" CREATE TABLE TestSchema.[{refTblName}] (");
result.AppendLine($" [{referencedDocumentMap.IdColumnName}] nvarchar(50) NOT NULL,");
result.AppendLine($" [{referencedDocumentMap.IdTableColumnName}] nvarchar(50) NOT NULL,");
result.AppendLine($" [{referencedDocumentMap.RelatedDocumentIdColumnName}] nvarchar(50) NOT NULL,");
result.AppendLine($" [{referencedDocumentMap.RelatedDocumentTableColumnName}] nvarchar(50) NOT NULL ");
result.AppendLine(" )");
}
}
protected virtual void ContributeParameter(IDbCommand command, ITypeHandlerRegistry typeHandlers, string name, object value, DocumentMap mapping = null)
{
if (value == null)
{
command.Parameters.Add(new SqlParameter(name, DBNull.Value));
return;
}
var typeHandler = typeHandlers.Resolve(value.GetType());
if (typeHandler != null)
{
var p = new SqlParameter(name, SqlDbType.NVarChar);
typeHandler.WriteDatabase(p, value);
command.Parameters.Add(p);
return;
}
if (value is TableValuedParameter tvp && command is SqlCommand sqlCommand)
{
var p = sqlCommand.Parameters.Add(name, SqlDbType.Structured);
p.Value = tvp.DataRecords;
p.TypeName = tvp.TypeName;
return;
}
if (value is IEnumerable && (value is string) == false && (value is byte[]) == false)
{
var inClauseNames = new List <string>();
var i = 0;
var inClauseValues = ((IEnumerable)value).Cast <object>().ToList();
ListExtender.ExtendListRepeatingLastValue(inClauseValues);
foreach (var inClauseValue in inClauseValues)
{
i++;
var inClauseName = name + "_" + i;
inClauseNames.Add(inClauseName);
ContributeParameter(command, typeHandlers, inClauseName, inClauseValue);
}
if (i == 0)
{
var inClauseName = name + "_" + i;
inClauseNames.Add(inClauseName);
ContributeParameter(command, typeHandlers, inClauseName, null);
}
var originalParameter = Regex.Escape("@" + name.TrimStart('@')) + @"(?=[^\w\$@#_]|$)";
var replacementParameters = "(" + string.Join(", ", inClauseNames.Select(x => "@" + x)) + ")";
command.CommandText = Regex.Replace(command.CommandText, originalParameter, match => replacementParameters, RegexOptions.IgnoreCase);
return;
}
var columnType = DatabaseTypeConverter.AsDbType(value.GetType());
if (columnType == null)
{
throw new InvalidOperationException($"Cannot map type '{value.GetType().FullName}' to a DbType. Consider providing a custom ITypeHandler.");
}
var param = new SqlParameter();
param.ParameterName = name;
param.DbType = columnType.Value;
param.Value = value;
if (columnType == DbType.String && value is string text)
{
var size = GetBestSizeBucket(text);
if (size > 0)
{
param.Size = size;
}
}
// To assist SQL's query plan caching, assign a parameter size for our
// common id lookups where possible.
if (mapping != null &&
mapping.IdColumn != null &&
mapping.IdColumn.MaxLength > 0 &&
columnType == DbType.String &&
string.Equals(name, mapping.IdColumn.ColumnName, StringComparison.OrdinalIgnoreCase))
{
if (mapping.IdColumn.MaxLength != null)
{
param.Size = mapping.IdColumn.MaxLength.Value;
}
}
if (columnType == DbType.String && mapping != null)
{
var indexed = mapping.WritableIndexedColumns();
var columnMap = indexed.FirstOrDefault(i => string.Equals(i.ColumnName, param.ParameterName, StringComparison.OrdinalIgnoreCase));
if (columnMap != null && columnMap.MaxLength != null)
{
param.Size = columnMap.MaxLength.Value;
}
}
command.Parameters.Add(param);
}
CommandParameterValues GetDocumentParameters(Func <DocumentMap, string> allocateId, object customAssignedId, CustomIdAssignmentBehavior?customIdAssignmentBehavior, object document, DocumentMap mapping, string prefix = null)
{
var id = mapping.IdColumn.PropertyHandler.Read(document);
if (customIdAssignmentBehavior == CustomIdAssignmentBehavior.ThrowIfIdAlreadySetToDifferentValue &&
customAssignedId != null && id != null && customAssignedId != id)
{
throw new ArgumentException("Do not pass a different Id when one is already set on the document");
}
if (mapping.IdColumn.Type == typeof(string) && string.IsNullOrWhiteSpace((string)id))
{
id = string.IsNullOrWhiteSpace(customAssignedId as string) ? allocateId(mapping) : customAssignedId;
mapping.IdColumn.PropertyHandler.Write(document, id);
}
var result = new CommandParameterValues
{
[$"{prefix}{mapping.IdColumn.ColumnName}"] = id
};
switch (mapping.JsonStorageFormat)
{
case JsonStorageFormat.TextOnly:
result[$"{prefix}{JsonVariableName}"] = serializer.SerializeText(document, mapping);
break;
case JsonStorageFormat.CompressedOnly:
result[$"{prefix}{JsonBlobVariableName}"] = serializer.SerializeCompressed(document, mapping);
break;
case JsonStorageFormat.MixedPreferCompressed:
result[$"{prefix}{JsonBlobVariableName}"] = serializer.SerializeCompressed(document, mapping);
result[$"{prefix}{JsonVariableName}"] = null;
break;
case JsonStorageFormat.MixedPreferText:
result[$"{prefix}{JsonVariableName}"] = serializer.SerializeText(document, mapping);
result[$"{prefix}{JsonBlobVariableName}"] = null;
break;
case JsonStorageFormat.NoJson:
// Nothing to serialize
break;
default:
throw new ArgumentOutOfRangeException();
}
foreach (var c in mapping.WritableIndexedColumns())
{
var value = c.PropertyHandler.Read(document);
if (value != null && value != DBNull.Value && value is string && c.MaxLength != null && c.MaxLength.Value > 0)
{
var attemptedLength = ((string)value).Length;
if (attemptedLength > c.MaxLength)
{
throw new StringTooLongException($"An attempt was made to store {attemptedLength} characters in the {mapping.TableName}.{c.ColumnName} column, which only allows {c.MaxLength} characters.");
}
}
else if (value != null && value != DBNull.Value && value is DateTime && value.Equals(DateTime.MinValue))
{
value = SqlDateTime.MinValue.Value;
}
result[$"{prefix}{c.ColumnName}"] = value;
}
return(result);
}
// TODO: includeDefaultModelColumns seems dumb. Use a NonQuery instead?
void AppendInsertStatement(StringBuilder sb, DocumentMap mapping, string tableName, string schemaName, string tableHint, int numberOfInstances, bool includeDefaultModelColumns)
{
var columns = new List <string>();
if (includeDefaultModelColumns)
{
columns.Add(mapping.IdColumn.ColumnName);
}
columns.AddRange(mapping.WritableIndexedColumns().Select(c => c.ColumnName));
if (includeDefaultModelColumns)
{
switch (mapping.JsonStorageFormat)
{
case JsonStorageFormat.TextOnly:
columns.Add(JsonVariableName);
break;
case JsonStorageFormat.CompressedOnly:
columns.Add(JsonBlobVariableName);
break;
case JsonStorageFormat.MixedPreferCompressed:
case JsonStorageFormat.MixedPreferText:
columns.Add(JsonBlobVariableName);
columns.Add(JsonVariableName);
break;
}
}
var columnNames = string.Join(", ", columns.Select(columnName => $"[{columnName}]"));
var actualTableName = tableName ?? mapping.TableName;
var actualSchemaName = schemaName ?? configuration.GetSchemaNameOrDefault(mapping);
sb.AppendLine($"INSERT INTO [{actualSchemaName}].[{actualTableName}] {tableHint} ({columnNames}) VALUES ");
void Append(string prefix)
{
var columnVariableNames = string.Join(", ", columns.Select(c => $"@{prefix}{c}"));
sb.AppendLine($"({columnVariableNames})");
}
if (numberOfInstances == 1)
{
Append("");
return;
}
for (var x = 0; x < numberOfInstances; x++)
{
if (x > 0)
{
sb.Append(",");
}
Append($"{x}__");
}
}