How can I reconnect to SQLServer when disconnected?

Question

I have coded my own tiny static DAL class for .NET Compact Framework and local SQL Server that does just what I need; GetTable(SQL,params), Exec(SQL,params), ExecScalar(SQL,Params) etc. I wanted to do everything by SQL so that's how I rolled, if there are better ways to handle SQL Connection in C# -except ORM- please let me know. Here's how I give public access to SQLConnection object under my DAL Class:

    public static SqlConnection Conn()
    {
        if (ConnStr == "")
            Defaults();
        try
        {

            if (conn == null)
                conn = new SqlConnection(ConnStr);
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
                Execute("SET DATEFORMAT DMY",null);
            }
            return conn;
        }
        catch
        {
            System.Windows.Forms.MessageBox.Show("Unable to connect to DB");
        }
        return null;
    }

The application I'm coding is being used on mobile Windows CE 5.0. The problem is, when user gets disconnected from wireless network then reconnects, if (conn.State != ConnectionState.Open) line doesn't get executed and application gives a general database exception, nothing else. How do I fix this?

Edit: Turns out exception is raised by functions such as GetTable(). I can handle it using try-catch in each function, but unfortunately in Compact Framework every sql error is named as "SqlException". How do I differ connection problems and query problems?

Answer 1

Can you use the System.Net.NetworkInformation.NetworkInterface class to check if the network is available?

You can check for a network connection in .NET 2.0 using GetIsNetworkAvailable():

System.Net.NetworkInformation.NetworkInterface.GetIsNetworkAvailable()

To monitor changes in IP address or changes in network availability use the events from the NetworkChange class:

System.Net.NetworkInformation.NetworkChange.NetworkAvailabilityChanged
System.Net.NetworkInformation.NetworkChange.NetworkAddressChanged

As per previous SO answer: C# - How do I check for a network connection

Answer 2

You could catch the SqlException and check the ErrorCode property to see if it was an error related to a loss of connection (i.e. error message would be something like "cannot connect to server" and that has a specific error code).

Answer 3

If you are dealing with mobile applications you will never be able to rely on network access.

A better approach is to do all of you access to a local database (SQL CE, Sqlite), then synchronize the changes (.Net Synchronization Services will help with that) with the main database.

Whether that will work with the amount of data for your application is another story.

Answer 4

I have fixed it by using new SqlConnection for each query instead of sharing one static SqlConnection for everything.