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}__"); } }