LINQ to SQL, check if connection to connection string is valid

Question

I'm using LINQ to SQL (dbml) and i'd like to check if the connection to the connection string is valid before going further.. for example, if the IP in the connection string is incorrect (or the database goes down), the program crashes and would like to make a check for this. I tried this:

if (DataContext.DatabaseExists())
  MessageBox.Show("Connection Exists");

That works if the connection is valid, but if the connection is invalid, it freezes at the if statement. What else can I try or do?

<add name="NflDataEntities" connectionString="metadata=res://*/NflData.csdl|res://*/NflData.ssdl|res://*/NflData.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=10.10.***.***;initial catalog=NflData;persist security info=True;user id=*****;password=****;MultipleActiveResultSets=True;App=EntityFramework&quot;"
      providerName="System.Data.EntityClient" />

score 2 · Accepted Answer · answered May 07 '14 at 17:02

2

This is by design. It "freezes" because it's scanning the network looking for the database server. If it's not found then it returns false, but that can take some time.

There are other options for verifying the server, like this one here. You'll need to use the SqlConnectionStringBuilder to parse the connection string and get the DataSource. Armed with that information you can run that command and grab its response. You'll need to redirect the standard output though and then parse the output. In short, not really a very good option.

answered May 07 '14 at 17:02

Mike Perrenoud

66,820
29
157
232

o i c.. i'll try letting it sit and see if it returns false.. so is there no way around this? – user1189352 May 07 '14 at 17:03
1

You might want to try `context.CommandTimeout = 10` to cause the failure to return more quickly. Apparently this property moved about with different EF versions, see http://stackoverflow.com/questions/6232633/entity-framework-timeouts for the various options. – Steve May 07 '14 at 17:08
@Steve tried changing the timeout to 1 but that didn't make the check any faster unfortunately. thanks for the suggestion though – user1189352 May 07 '14 at 17:10
let me take that back, it did make it faster than 30 seconds, just slower than i would like unfortunately but i guess that's the extent it can go. thx – user1189352 May 07 '14 at 17:21

score 1 · Answer 2 · answered May 07 '14 at 17:03

1

The problem may be that the request to the DB has a timeout that must tick down before it returns. Try setting the timeout in your connection string temporarily to a very short time (a second or two) and that may speed it up.

answered May 07 '14 at 17:03

Nathan A

11,059
4
47
63

okay let me see if i can try and find that in the connection string right now – user1189352 May 07 '14 at 17:04
i edited the OP to show the connection string, but i don't see anywhere i can set up a time out? – user1189352 May 07 '14 at 17:05
1

I believe the default timeout is 30 seconds if none is specified. Try using `SqlConnectionStringBuilder` to create your temp string, and set the timeout there. – Nathan A May 07 '14 at 17:08
1

@user1189352 Take a look at this: http://stackoverflow.com/questions/6232633/entity-framework-timeouts – Steve May 07 '14 at 17:09
That link talks about some bug with specifying a timeout in the connection string. Might be fixed or not, IDK. – Steve May 07 '14 at 17:09

score 1 · Answer 3 · answered May 07 '14 at 17:58

You can run an async method while showing a message that the system is working so the UI won't freeze while waiting for the Sql Response:

ShowMessage("Loading"); // Running on UI thread
        Task.Factory.StartNew(() =>
        {
            try
            {
                return DataContext.DatabaseExists();
            }
            catch (SqlException)
            {
                return false;
            }
        }).ContinueWith(isValid =>
        {
            if (isValid.Result)
                ShowMessage("Success");
            else
                ShowMessage("Failure");
        });

LINQ to SQL, check if connection to connection string is valid

3 Answers3