Exception while SQL ExecuteNonQuery: Is it possible to avoid a rollback?

Question

A stream is written to a var-binary field as follows:

string connectionString = "connection String";
using (SqlConnection conn = new SqlConnection(connectionString)) {
    conn.Open();

    using (SqlCommand cmd = new SqlCommand("update TableX set Data=@data where id = @id")) {
        cmd.Parameters.AddWithValue("@Id", ParameterDirection.Input);
        cmd.Parameters.AddWithValue("@Data", streamToStore);
        cmd.Connection = conn;

        try {
            int rows = cmd.ExecuteNonQuery();

        } catch (Exception ex) {
            // store the so far written data anyway!
        }

    }
}

When the exception occurs, all so far written data is gone. So there seems to be some kind of rollback in the case that an exception occurs. Is there any way to store the so far written data anyway? It will be resumed later, so I don't care if it is not complete for now.

Quick Example: The stream I'm using is a network stream. The data is fetched while it is written to the database. Let's say the stream is of size 1000 bytes. If 900 bytes are already received (and somehow buffered automatically somewhere in the database) and then a Connection-Issue (IOException) happens, all data is lost. Nothing will be stored to the database. But I want to have those 900 bytes stored to the var-binary field (and will resume the rest of it later).

Answer 1

A few things:

• I'm fairly certain that SqlServer does not support a notion of "partial column updates". The update will either happen fully or it won't.

• Instead of passing the stream directly as a parameter, you could copy as much of the stream as possible into a byte array or MemoryStream and then pass that. This would give you control over the error handling. The only reason this wouldn't work is if (1) the .NET sql client is able to stream the bytes to the database without gathering them all in memory first and (2) you depend on this optimization.

• Another potential workaround would be to do your writes in batches. Instead of trying to write the entire stream at once, create a loop where you read the next N bytes of the stream, and issue a query that attempts to append those bytes to the existing column. This will incur more round-trips to the database, but should get you the partial updates you want.

• I'm assuming that the "error" in question occurs on a call to Stream.Read(), which interrupts the process by which the data is pulled from your stream into SQL. If this is true, then another potential solution could be to wrap your stream in another Stream implementation that catches relevant errors and just ends the stream there. Something like:

class ErrorSuppressingStream : Stream
{
    private readonly Stream inner;

    public bool TerminatedWithError { get; private set; }

    public ErrorSuppressingStream(Stream inner) { this.inner = inner; }

    public override int Read(byte[] buffer, int offset, int count)
    {
        if (this.TerminatedWithError) { return 0; }

        try { return this.inner.Read(buffer, offset, count); }
        catch (XXXException ex)
        {
            this.TerminatedWithError = true;
            return 0;
        }
    }

    // more overrides here
}

This would allow you to still stream bytes to the database, but would treat a read error as end of stream instead of throwing.