Re: Best Practices for using SqlConnection

[Krissy]

This is probably a dumb question:

In a web application, say a shopping site, is it best to have global
SqlConnection and data adapters that all pages share, or should each page
define it's own connection? How about DataSets?

I find it is easier to put a connection in the web config, that way you dont
have to declare it on each page!

Also, in the future, if you need to change your connection to another
database, you only need to change one instance of the connection, and not on
each page.

Example:

In the web.config under the <configuration> tags, I have:

<appSettings>
<add key="SQLConn" value="server=192.168.1.1;user
id=userl;password=pass;initial catalog=mydb" />
</appSettings>


I then have a library file, ( library.vb) which I have the following code:
(MyLib = the class name of the library)

' ******************** Database connection information
*********************
Public Const DBSource As MyLib.DBConn = MyLib.DBConn.SQLConn

Public Shared Function GetDBConn(ByVal MyConn As DBConn) As
SqlClient.SqlConnection
Return New
SqlClient.SqlConnection(ConfigurationSettings.AppSettings(MyConn.ToString))
End Function

'** You can put here the name of your connection(s) from the web.config
Public Enum DBConn
SQLConn
End Enum



And then, in my code, if I want to call the connection:

Dim sqlcmd As New SqlCommand("", MyLib.GetDBConn(MyLib.DBSource))

 

[Rick Spiewak]

I would recommend using the Application Block at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp
In addition, I agree with Krissy on putting the connection string in the
web.config file. If you look at the code in the application block, you'll
see that it creates and destroys the connection for each operation. This
seems to be quite fast in operation, I'm using it for all the SQL access in
my current application.

 

[PJ]

I definately recommend the MS Data Access Application Block. You may wish
to scale it down for your use. For example, each overloaded method takes a
SqlConnection object for later use. Most applications I work on only
require one connection string, so I will create the SqlConnection in the
final overloaded method in which it is used, as opposed to requiring the
calling code to create and manage the connection. In the OnStart event of
the application, I will set a public static property on the SqlHelper class
in the data access app block.

Also, I prefer to store the connection string encrypted using the DPAPI
(available on w2k+) in the machine.config. I believe this to be a bit more
secure, and my connection strings often differ from machine to machine.