CODEWITHSUNDEEP
c#sqlexception
Spooky2010
Spooky2010

Reputation: 341

Sql exception in winforms form handling best practice

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

Upvotes: 0

Views: 1160

Answers (2)

Polyfun
Polyfun

Reputation: 9639

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.

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300699

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

Upvotes: 2

Related Questions