SQL Azure with linq connect

listonic · Post by **listonic** » Fri 28 Jan 2011 15:41

I am trying to use linq connection along with SQL AZURE (http://www.microsoft.com/en-us/sqlazure/default.aspx)

I came across following problem.

I have an application which is using linq connection to connect to local db server. Everything woks fine. Hovewer, when switching to sql azure, the connection to database sometimes fails.

According to this article:
http://blogs.msdn.com/b/sqlazure/archiv ... 11247.aspx
a database connection created by application can be killed in some circumnstances (node failure, connection timeout etc.) The problem is that those problems do not occur when using local database server. My application is not prepared for cases when database commands fail.

Does linq connect or any other of your products (dotconnecto for sql server ? ) can help here ? Do you have any form of retry mechanism which can deal with connection failure ?

Regards
Piotr Wójcicki

listonic · Post by **listonic** » Mon 31 Jan 2011 14:55

Does anybody from Devart Team care to reply ?

Regards
Piotr Wójcicki

StanislavK · Post by **StanislavK** » Mon 31 Jan 2011 17:14

The DataContext class has no specific mechanisms of handling connection failures. However, you can implement the approach similar to the one proposed in the article you've referred. To avoid changing code in each place where you call the SubmitChanges method, you can add the [YourDataContext].SubmitChanges method where this handling mechanism is implemented. For example, this can be something like

Code: Select all

partial class MyDataContext {

  public void SubmitChanges() {

    bool submitted = false;
    int maxRetries = 3;
    
    int retry = 0;
    while (!submitted && retry < maxRetries) {
      try {

        base.SubmitChanges();
        submitted = true;

      }
      catch (Exception ex) {

        // A check that the exception was 
        // caused by the failed connection.
        if (...) 
          retry++;
        else throw ex;

      }
    }
  }

}

As for the reply delays, we have the two business days response policy, but do our best to answer as soon as possible.

Update. I've corrected a misprint in the code: in the catch block, the retry variable should be incremented instead of maxRetries.

listonic · Post by **listonic** » Tue 01 Feb 2011 15:54

Thanks for your reply, it is helpful.

Regards
Piotr Wójcicki