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
smaguireAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russ SuterSenior Software DeveloperCommented:
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.
smaguireAuthor Commented:
I have a desktop application, AJAX is recommended only for web app...
SriVaddadiCommented:
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

it_saigeDeveloperCommented:
Another thing you may want to consider is a loading/startup form that displays messages to the user.

-saige-
smaguireAuthor Commented:
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
SriVaddadiCommented:
You should be using DispatcherTimer class.

Here is the link

IAsyncResult is an old pattern. You could use it if you are using an old .net framework. Latest and recommended pattern is TAP (Task based asynchronous pattern)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
smaguireAuthor Commented:
Thank you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.