public async Task Deposit(IEnumerable <Token> tokens, CancellationToken cancellationToken) { const int PrimaryKeyViolation = 2627; var events = from token in tokens from @event in token.UncommittedEvents select new EventDescriptor <string>(token.Id, @event); var reader = new EventDataDataReader <string>(eventStoreConfiguration, events); // TODO: once projections are moved outside of the database, the option to use triggers can be removed to improve performance using (var connection = eventStoreConfiguration.CreateConnection()) using (var bulkInsert = new SqlBulkCopy(connection.ConnectionString, UseInternalTransaction | FireTriggers)) { // TODO: this might require additional tuning at scale bulkInsert.BatchSize = 1000; bulkInsert.DestinationTableName = eventStoreConfiguration.TableName.Delimit(); bulkInsert.EnableStreaming = true; try { await bulkInsert.WriteToServerAsync(reader, cancellationToken); } catch (SqlException ex) when(ex.Errors.Cast <SqlError>().Any(e => e.Number == PrimaryKeyViolation)) { // if there are any duplicates, we've already committed the batch at least once } } }
public DataTable GetSchemaTable() { if (schema != null) { return(schema); } var schemaTable = new DataTable(); var baseSchemaName = configuration.TableName.Delimit(SchemaName); var baseTableName = configuration.TableName.Delimit(ObjectName); var builder = new SqlConnectionStringBuilder(); using (var connection = configuration.CreateConnection()) { builder.ConnectionString = connection.ConnectionString; } var baseCatalogName = builder.InitialCatalog; var baseServerName = builder.DataSource; var keyType = typeof(TKey); // REF: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getschematable(v=vs.110).aspx schemaTable.Columns.Add("AllowDBNull", typeof(bool)); schemaTable.Columns.Add("BaseCatalogName", typeof(string)); schemaTable.Columns.Add("BaseColumnName", typeof(string)); schemaTable.Columns.Add("BaseSchemaName", typeof(string)); schemaTable.Columns.Add("BaseServerName", typeof(string)); schemaTable.Columns.Add("BaseTableName", typeof(string)); schemaTable.Columns.Add("ColumnName", typeof(string)); schemaTable.Columns.Add("ColumnOrdinal", typeof(int)); schemaTable.Columns.Add("ColumnSize", typeof(int)); schemaTable.Columns.Add("DataTypeName", typeof(string)); schemaTable.Columns.Add("IsAliased", typeof(bool)); schemaTable.Columns.Add("IsAutoIncrement", typeof(bool)); schemaTable.Columns.Add("IsColumnSet", typeof(bool)); schemaTable.Columns.Add("IsExpression", typeof(bool)); schemaTable.Columns.Add("IsHidden", typeof(bool)); schemaTable.Columns.Add("IsIdentity", typeof(bool)); schemaTable.Columns.Add("IsKey", typeof(bool)); schemaTable.Columns.Add("IsLong", typeof(bool)); schemaTable.Columns.Add("IsReadOnly ", typeof(bool)); schemaTable.Columns.Add("IsRowVersion", typeof(bool)); schemaTable.Columns.Add("IsUnique", typeof(bool)); schemaTable.Columns.Add("NonVersionedProviderType", typeof(SqlDbType)); schemaTable.Columns.Add("NumericPrecision", typeof(byte)); schemaTable.Columns.Add("NumericScale", typeof(byte)); schemaTable.Columns.Add("ProviderSpecificDataType", typeof(string)); schemaTable.Columns.Add("ProviderType", typeof(string)); schemaTable.Columns.Add("UdtAssemblyQualifiedName", typeof(string)); schemaTable.Columns.Add("XmlSchemaCollectionDatabase", typeof(string)); schemaTable.Columns.Add("XmlSchemaCollectionName", typeof(string)); schemaTable.Columns.Add("XmlSchemaCollectionOwningSchema", typeof(string)); switch (Type.GetTypeCode(keyType)) { case TypeCode.Int16: schemaTable.Rows.Add(new object[] { false, baseCatalogName, "AggregateId", baseSchemaName, baseServerName, baseTableName, "AggregateId", 0, 2, "SMALLINT", false, false, false, false, false, false, false, false, false, false, false, SqlDbType.SmallInt, 0, 0, "SMALLINT", typeof(short), null, null, null, null }); break; case TypeCode.UInt16: schemaTable.Rows.Add(new object[] { false, baseCatalogName, "AggregateId", baseSchemaName, baseServerName, baseTableName, "AggregateId", 0, 2, "SMALLINT", false, false, false, false, false, false, false, false, false, false, false, SqlDbType.SmallInt, 0, 0, "SMALLINT", typeof(ushort), null, null, null, null }); break; case TypeCode.Int32: schemaTable.Rows.Add(new object[] { false, baseCatalogName, "AggregateId", baseSchemaName, baseServerName, baseTableName, "AggregateId", 0, 4, "INT", false, false, false, false, false, false, false, false, false, false, false, SqlDbType.Int, 0, 0, "INT", typeof(int), null, null, null, null }); break; case TypeCode.UInt32: schemaTable.Rows.Add(new object[] { false, baseCatalogName, "AggregateId", baseSchemaName, baseServerName, baseTableName, "AggregateId", 0, 4, "INT", false, false, false, false, false, false, false, false, false, false, false, SqlDbType.Int, 0, 0, "INT", typeof(uint), null, null, null, null }); break; case TypeCode.Int64: schemaTable.Rows.Add(new object[] { false, baseCatalogName, "AggregateId", baseSchemaName, baseServerName, baseTableName, "AggregateId", 0, 8, "BIGINT", false, false, false, false, false, false, false, false, false, false, false, SqlDbType.BigInt, 0, 0, "BIGINT", typeof(long), null, null, null, null }); break; case TypeCode.UInt64: schemaTable.Rows.Add(new object[] { false, baseCatalogName, "AggregateId", baseSchemaName, baseServerName, baseTableName, "AggregateId", 0, 8, "BIGINT", false, false, false, false, false, false, false, false, false, false, false, SqlDbType.BigInt, 0, 0, "BIGINT", typeof(ulong), null, null, null, null }); break; case TypeCode.String: schemaTable.Rows.Add(new object[] { false, baseCatalogName, "AggregateId", baseSchemaName, baseServerName, baseTableName, "AggregateId", 0, 128, "NVARCHAR(128)", false, false, false, false, false, false, false, false, false, false, false, SqlDbType.NVarChar, byte.MaxValue, byte.MaxValue, "NVARCHAR(128)", typeof(string), null, null, null, null }); break; case TypeCode.Object: if (typeof(Guid).Equals(keyType)) { schemaTable.Rows.Add(new object[] { false, baseCatalogName, "AggregateId", baseSchemaName, baseServerName, baseTableName, "AggregateId", 0, 16, "UNIQUEIDENTIFIER", false, false, false, false, false, false, false, false, false, false, false, SqlDbType.UniqueIdentifier, 0, 0, "UNIQUEIDENTIFIER", typeof(Guid), null, null, null, null }); break; } goto default; default: throw new InvalidOperationException($"The type {typeof( TKey ).Name} cannot be used for a key column."); } schemaTable.Rows.Add(new object[] { false, baseCatalogName, "Version", baseSchemaName, baseServerName, baseTableName, "Version", 1, 4, "INT", false, false, false, false, false, false, false, false, false, false, false, SqlDbType.Int, 0, 0, "INT", typeof(int), null, null, null, null }); schemaTable.Rows.Add(new object[] { false, baseCatalogName, "Sequence", baseSchemaName, baseServerName, baseTableName, "Sequence", 2, 4, "INT", false, false, false, false, false, false, false, false, false, false, false, SqlDbType.Int, 0, 0, "INT", typeof(int), null, null, null, null }); schemaTable.Rows.Add(new object[] { false, baseCatalogName, "RecordedOn", baseSchemaName, baseServerName, baseTableName, "RecordedOn", 3, 8, "DATETIME2", false, false, false, false, false, false, false, false, false, false, false, SqlDbType.DateTime2, 7, byte.MaxValue, "DATETIME2", typeof(DateTime), null, null, null, null }); schemaTable.Rows.Add(new object[] { false, baseCatalogName, "Type", baseSchemaName, baseServerName, baseTableName, "Type", 4, 256, "NVARCHAR(256)", false, false, false, false, false, false, false, false, false, false, false, SqlDbType.NVarChar, byte.MaxValue, byte.MaxValue, "NVARCHAR(256)", typeof(string), null, null, null, null }); schemaTable.Rows.Add(new object[] { false, baseCatalogName, "Revision", baseSchemaName, baseServerName, baseTableName, "Revision", 5, 4, "INT", false, false, false, false, false, false, false, false, false, false, false, SqlDbType.Int, 0, 0, "INT", typeof(int), null, null, null, null }); schemaTable.Rows.Add(new object[] { false, baseCatalogName, "Message", baseSchemaName, baseServerName, baseTableName, "Message", 6, int.MaxValue, "VARBINARY(MAX)", false, false, false, false, false, false, false, false, false, false, false, SqlDbType.VarBinary, byte.MaxValue, byte.MaxValue, "VARBINARY(MAX)", typeof(byte[]), null, null, null, null }); return(schema = schemaTable); }