Link to home
Start Free TrialLog in
Avatar of Harsh08
Harsh08

asked on

SQL Connection

What is the efficient/professional way to connect to the sql database (2012) from vb.net 2012?
Pls mention which reference to add to the project.
Thnx...
ASKER CERTIFIED SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You can create a single instance of connection using sqlconnection and then use the same instance across your application. You can even use webservice to do update and select if you want do it from from end application.

http://programmers.stackexchange.com/questions/50985/best-practices-for-connecting-from-asp-net-to-sql-server

http://bytes.com/topic/visual-basic-net/answers/887522-best-approach-connection-string-sql-server-2005-vb-net
Because ADO.NET always works with a copy of the data in memory, the old methods used for classic ADO not not hold anymore. There is no need to keep a connection opened all the time, so no need to use a Public variable to hold the connection.

Using a single instance and reusing it across the application is not the recommended way of working with a Connection object in .NET. A Public variable is active for all the life of the application. If left opened, you might be using up a connection for long periods of inactivity. If the user goes away for instance, the connection could remain opened for nothing, possibly preventing other users from connecting to the server because you used up the number of licenses available.

Also, is there is a bug with the Connection object, it can be anywhere in the application. This could make debugging a lot more difficult.

It is recommended to use local Connection variables, that variables that you declare in each method that need one, Open it just before you need it, and Close it as soon as you can. This has many advantages over a global instance.

If you have a problem in a method, you have to look only there because you used a local variable.

ADO.NET uses a mechanism called Connection pooling. When you Close a Connection object in the code, the physical connection stays opened on the server for a certain amount of time, 5 minutes in most cases. If you create and use a new Connection object during that period, with the exact same ConnectionString, ADO.NET reuses the same physical connection. But if the user does something else or goes away, the physical connection closes on the server after the 5 minutes, and will be reopened only when you need it again.

This frees the connection and makes it available for other users when not in use.

Most programmers I know code a Public method that creates and returns a connection, something like the following:

Public Function CreateConnection () As SqlConnection
   Try
        Dim con As New SqlConnection (<Your ConnectionString>)
        con.Open
        Return con
   Catch ex As SqlException
       'Code to handle possible errors while trying to connect
   End Try
End Function

Open in new window

Whenever you need a Connection in a method, you call that function:

Public Sub GetData ()
    Dim con As SqlConnection = CreateConnection()
    'Create and Execute your query
    con.Close  'Close and Dispose are functionally equivalent for a SqlConnection according to the documentation
End Sub

Open in new window

Working that way insures that you always create the connection the same way, with the exact same ConnectionString, so that your connections will always be considered by the connection pooling feature. It also makes things easier, because you can use local variables without having to trap exceptions and open the connection in each method that needs one.