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