[RESOLVED] How to check connection before instantiating context in LINQ to Entities,

[wolf99]

Hi Folks

I've been using LINQ to Entites with the graphical Entity Designer taking care of creating all ORM stuff for my DB.

Thus to use it I have something like the following that runs when my program loads.

Dim myContext As myDataEntities
'...
Try
    myContext =  New myDataEntites
Catch ex As Exception
    MessageBox.Show("Could not talk to DB")
    Me.Close()
    Exit Sub
End Try

The problem is that if the connection is failing for some reason (e.g. I have no connection to the server the DB is on) it takes quiite a long while for anything at all to happen.

Is there a better way I can confirm access to the DB before trying to create the context? (The DB is on a remote server that may be connected via proxy over ethernet)

I did take a look at the generated code in the myData.Designer.vb from my .edmx, but couldn't really work out where it is that the connection to the DB is actually attempted.

Thanks

[wolf99]

So I'm currently thinking that I could just try pinging the SQL server and if the ping fails then cancel the form, while this is still slow-ish, its nowhere near as slow as waiting for an exception on the context creation, by a long shot.

'
        Dim pingMe As New Ping()
        Dim result = pingMe.Send(IPAddress.Parse("192.168.4.11"))
 
        If Not result.Status = IPStatus.Success Then
            MessageBox.Show("Uh-Oh!")
            Me.Close()
            Exit Sub
        End If

This is in the Form1.Load event, but for some reason the form still shows... Surely Me.Close() Exit Sub should cancel the form (and so the application)??

I have tried End instead of Exit Sub, but no difference... Anyone know how I should cancel the form correctly? And is there a better way of testing for server connection?
Thanks

[jmcilhinney]

You can't close a form in the Load event handler because it hasn't even opened yet. If you don't want to show a form then don't call its Show method. Once you call Show, it's gonna get shown.

What you're doing is misguided though. The issue is that fact that the default timeout on an ADO.NET connection is 30 seconds. If you don't want to use a 30 second timeout then don't. You have no direct access to the SqlConnection that your EF context uses under the hood but you can simply set the timeout in the connection string. This code:

Dim builder As New SqlConnectionStringBuilder With {.DataSource = "MyServer",
                                                    .InitialCatalog = "MyDatabase",
                                                    .IntegratedSecurity = True,
                                                    .ConnectTimeout = 15}

MessageBox.Show(builder.ConnectionString)

display this connection string:

Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=True;Connect Timeout=15

You just do something similar in your EF connection string in your config file.

[wolf99]

This is the thing though, *I* dont call the show method, the user does when they start the application (or the VB project startup code does, I guess). The form is the only form in my project.

I think one of the problems I had before was that I also already have code in the form.formClosing handler which references objects that never get instantiated if the connection fails, so that was causing another exception. As Visual Studio's response to unhandled exceptions in the form load and closing handlers seems to be to just skip the rest of the handler code and then show the form instead of actually reporting an exception (which is really annoying), it wasn't obvious what was going on.

I've got around this by setting a boolean variable when I catch the exception and then checking for it in the formClosing handler.

[jmcilhinney]

This is the thing though, *I* dont call the show method, the user does when they start the application (or the VB project startup code does, I guess). The form is the only form in my project.

So are you saying that you want the application to close without displaying the main form? In that case you handle the Startup event of the application and set e.Cancel to True. If you provide the appropriate information then you can get the appropriate solution. Follow the CodeBank link in my signature and check out my WinForms Login thread for an example. It prompts the user to login at startup and exits the app without displaying a form if they don't/can't.