CODEWITHSUNDEEP
linqconnectiondatacontextconnection-pooling
LinQer-Dude
LinQer-Dude

Reputation:

Detect a DB Connection error using Linq?

Is it possible to detect an error in a connection before we get an application "Transport-level error" using Linq?

Lets say that I have a SQL server called SQLServer1, and I am running an application that connects to that server using LinQ to get some information.

I decide to change the server Name to SQLServer2. The next time my application run I will get an error like this:

"A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)"

Because LinQ doens't know that there was a change!

How can I catch this during runtime? Is it possible to extend the DataContext class to check somehow the connection before getting the data?

Thanks!

Upvotes: 1

Views: 2205

Answers (2)

Adam
Adam

Reputation: 21

Here's how I've extended the class. I use a singleton pattern for calling the data context, which results in one data context object being instantiated per page request regardless of the number of times the singleton is called. On the first call to the singleton per request, I run DataContext.DatabaseExists(), and if it is false, I gracefully handle the error. Now whenever any of my code attempts to do anything with the datacontext, it prompts my users with a "sorry, the database is down" message.

ASP.NET C# code:

namespace dal
{
    public partial class DataModelDataContext
    {
        public static DataModelDataContext DataContext
        {
              get
              {
                  if (System.Web.HttpContext.Current.Items["ModelDataContext"] == null)
                  {
                      DataModelDataContext datacontext = new DataModelDataContext();
                      //Once per page request, of the datacontext is requested, check to see if the database connection is valid.
                      if (!datacontext.DatabaseExists())
                      {
                           System.Web.HttpContext.Current.Response.Redirect("DatabaseIsDown.html");
                      }
                      System.Web.HttpContext.Current.Items["ModelDataContext"] = datacontext;

                  }
                  return (DataModelDataContext)System.Web.HttpContext.Current.Items["ModelDataContext"];
              }
        }
    }
}

Upvotes: 2

jjxtra
jjxtra

Reputation: 21140

try
{
   // run linq query here
}
catch (Exception e)
{
   // take appropriate action to handle the error like updating the connection string, etc.
}

Upvotes: 0

Related Questions