Link to home
Start Free TrialLog in
Avatar of smaguire
smaguireFlag for Canada

asked on

Best Practice Handling SQL Server Availability in C#

Hi,
I am looking for the best practice to handle sql server availability from within a windows form application using C# and MS SQL Server 2012

currently when my application starts, I have a DGV which is populated from a stored procedure, everything works but I am trying to bulletproof my app.  
For example: When I change my Data Source name to something that doesn't exist, I get an exception after few seconds and my main form won't display for that whole time making the end user wondering what is going on before seeing the exception, what do you guys do to handle unavailable sql server? or unplugged Ethernet connection?

The answer I am looking for is to have some kind of a Watchdog timer that will either start retrying when there is a problem or keeps constantly checking in the background and will display some banner to the user once connection is down

here is my SP code:
public DataTable GetProducts()
        {
                ....

            try
            {
                using (SqlConnection conn = new SqlConnection(m_ConnectionString))
                {
                    conn.Open();
                    cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "Stored Procedure Name";
                    cmd.CommandTimeout = SQL_TIMEOUT;

                    // Create a data adapter
                    da = new SqlDataAdapter();
                    da.SelectCommand = cmd;

                    // Fill data table
                    dt = new DataTable();
                    da.Fill(dt);
                }

                return dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show("The following error has occured: " + ex.Message, "GetProducts", MessageBoxButtons.OK, MessageBoxIcon.Error);
                // Exit the application gracefully
                System.Environment.Exit(0);
                return dt;
            }

        }

Open in new window


Does the timer have to be in its own thread? basically I would like the user to still load the main form with empty grid and controls and a timer that is trying to establish connection in the background.

Please let me know
Thanks
Avatar of Russ Suter
Russ Suter

I would use AJAX to accomplish this. Load the basic page layout right away then use an AJAX call to retrieve the data asynchronously. While loading the data you can show a "Loading..." message. If the AJAX call times out you can display a polite "data not available" message to the user.
Avatar of smaguire

ASKER

I have a desktop application, AJAX is recommended only for web app...
Depending on the version of the frame work you are using,  you could either use Task (Task parallel library) or Background Worker to load the data asynchronously.

Check this link https://msdn.microsoft.com/en-us/library/dd537609(v=vs.110).aspx
SOLUTION
Avatar of it_saige
it_saige
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SriVaddadi - I am going through your link...

Saige - It's an idea as well

I found the following code that attempts to open socket connection to the SQL server port and will return either true or false:
http://www.codeproject.com/Articles/612751/Check-that-Sql-Server-exists-prior-to-connection

it works fine and definitely fixes one of the issues I am having.

the second half of my question is how to incorporate that piece of code (in the link I provided) into a timer that runs on a separate thread?

I am almost there. Thanks
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you