Sql exception in winforms form handling best practice

Question

using c# vs2008 winforms.

My standard forms have a typical usage pattern of
Form load
Sqlconnection object open.

read/write datasets using sqldataadaptors
read many things using sql readers
write many data using stored procedures to write

close sql connection
close form

So whats best practice in handling exceptions in this type of usage pattern I enclose all read and write code to the sql server in try catch clauses to trap exceptions.

So what do i do when i trap an exception ?
Should i close and dispose all sql objects ?
Should i close the form and dispose the form.
Should i try to let the user continue on
Should i try to re-open my sqlconnection if the exception is that the sql connection broken ?
Given that i open and close connections on form load and close, as that it only in theory opens once and closes once with many reads and writes inbetween.

I'm just not sure what action i should be taking when i trap an exception, to prevent memory and resource leaks.

any advice appreciated

thanks

Mitch Wheat · Accepted Answer · 2011-08-18T10:00:31.943

2

It depends on the exception you receive and its severity. If it was error number 1205, you would want to retry, as that's the SQL Server deadlock error. e.g

catch (SqlException sqlEx)
{    
    if (sqlEx.Number == 1205)    
    {        
        // Deadlock occurred, retry...    
    }    
    else        
        throw;
}

For unrecoverable errors, you would want to clean up and dispose of connections etc.

Best practice is to use using blocks whenever an object implements IDisposable (such as a SqlConnection, SqlCommand, etc.)

To get a complete list of error codes, run this TSQL:

SELECT * FROM sysmessages

edited Aug 18 '11 at 10:00

answered Aug 18 '11 at 09:45

Mitch Wheat

295,962
43
465
541

Also SqlCommand is IDisposable. Always check the MSDN documentation for framework classes, to see if they implement IDisposable; if so, use a "using" block to make sure they are disposed in a timely fashion. – Polyfun Aug 18 '11 at 09:58
If you are the deadlock victim (error 1205), the whole transaction will be rolled back, so if you do a retry, make sure you retry the transaction from the beginning (execute all the statements in the transaction again). – Polyfun Aug 18 '11 at 10:00
http://stackoverflow.com/questions/320636/how-to-get-efficient-sql-server-deadlock-handling-in-c-with-ado – Mitch Wheat Aug 18 '11 at 10:02

score 0 · Answer 2 · answered Aug 18 '11 at 10:06

You may want to consider not keeping the SqlConnection open for the lifetime of your form. The form's lifetime is controlled by the user, therefore at the moment the lifetime of SqlConnections are also controlled by the user, which is not a good idea; locks on the database may be kept open until the SqlConnection closes. In a multi-user system this can increase contention for the locked records--you will potentially be blocking out other users whilst your forms have the connection open.

It is better to open and close the connection for particular actions on the form, e.g., when the user clicks a button. Because connection pooling is implemented automatically under the covers, you should not worry about the overhead in repeatedly opening and closing connections--this will be minimised by the connection pooling.

I dont recall ever coming across the problem of records being locked with a open sql connection. Could you provide more information on this. — Spooky2010, Sep 05 '11 at 06:40
In addition we have found keeping a sql connection open for the life of the form speeds up database access significantly when going over a VPN, which is why we do it — Spooky2010, Sep 05 '11 at 06:44

Sql exception in winforms form handling best practice

2 Answers2