protected override SqlBulkCopy CreateBulkCopy(IRelationalConnection connection) { var bulk = new SqlBulkCopy((SqlConnection)connection.DbConnection, this.SqlBulkCopyOptions, (SqlTransaction)connection.CurrentTransaction.GetDbTransaction()); bulk.BulkCopyTimeout = connection.CommandTimeout ?? 60; bulk.DestinationTableName = _bulkTable; InboundColumns.ForEach(p => bulk.ColumnMappings.Add(p.ColumnName, p.ColumnName)); _setup?.Invoke(bulk); return(bulk); }
protected override SqlCommand PrepareStatement(IRelationalConnection connection) { var columnNames = InboundColumns.Select(p => $"[{p.ColumnName}]"); var commandText = $"Select Top 0 {string.Join(", ", columnNames)} into {_bulkTable} FROM {_targetTableName}"; var command = connection.DbConnection.CreateCommand(); command.Transaction = connection.CurrentTransaction.GetDbTransaction(); command.CommandText = commandText; command.CommandTimeout = connection.CommandTimeout ?? 60; return((SqlCommand)command); }
protected override SqlCommand CommitStatement(IRelationalConnection connection) { var writeColumns = string.Join(" AND ", InboundColumns.Select(p => $"t.[{p.ColumnName}] = tmp.[{p.ColumnName}]")); var commandText = $"SET NOCOUNT ON;\r\n" + $"DELETE t FROM {_targetTableName} t \r\n" + $"INNER JOIN {_bulkTable} tmp ON {writeColumns};\r\n" + $"Select @@ROWCOUNT;"; var command = connection.DbConnection.CreateCommand(); command.CommandText = commandText; command.Transaction = connection.CurrentTransaction.GetDbTransaction(); command.CommandTimeout = connection.CommandTimeout ?? 60; return((SqlCommand)command); }
protected override SqlCommand CommitStatement(IRelationalConnection connection) { if (!OutboundColumns.Any()) { return(null); } var writeColumns = string.Join(", ", InboundColumns.Select(p => $"[{p.ColumnName}]")); var commandText = $"INSERT INTO {_targetTableName} ({writeColumns}) " + $"OUTPUT {string.Join(", ", OutboundColumns.Select(p => $"inserted.[{p.ColumnName}]"))} " + $"SELECT {writeColumns} FROM {_bulkTable}"; var command = connection.DbConnection.CreateCommand(); command.CommandText = commandText; command.Transaction = connection.CurrentTransaction.GetDbTransaction(); command.CommandTimeout = connection.CommandTimeout ?? 60; return((SqlCommand)command); }