DB Connection as an application variable

[GayanR]

Hi,

Im new to ASP.NET and have the following problem.

Im registering the DB Connection (Not the connection string) as an Application variable in the Global.asax and reuse it in all the pages.

But, If I get an exception, whole applications stops because of the connection. I have to rebuild the project or restart IIS.

Is there a better way to pass the connection around pages without causing a overhead for the server?

Pls help!!!!

Thanks.

[cholan]

Hi,

First of all what is the error message u receive... can you post that ... so that we try out why that error is raised :)

In the mean time try this out.

Instead of using Global.asax file...

you can follow the method im using

Step1:
In your web.config file create the parameter required for connection string.

That is, Open your web.config file, create a key similar to the one below
<appSettings>
<add key="sqlConnection" value="server=servername;uid=userid;pwd=password;database=databasename"/>
</appSettings>

Step2:
Inside your application, you can create class file when neccessary or while accessing the database, do the following code.

string ConStr = ConfigurationSettings.AppSettings["sqlConnection"];
SqlConnection(ConStr)

this will create connectionstring.


THE REASON WHY I USE WEB.CONFIG FILE IS, MY CONNECTIONSTRING PARAMETERS CAN BE CHANGED AT ANY POINT( IF THE db SERVER IS CHANGE TO NEW LOCATION) without changing a single line of code.

[GayanR]

Thanks Cholan.

I will try your solution and let you know.

But, Is there anyway to store the connection object somewhere?:(

Is it a bad habbit to store the connection object as an application variable and re-use it?

Thanks again for the help.

[cholan]

Hi,

Its not good to store the connection object in Application or Session objects. see the article below.


the URL is
http://msdn.microsoft.com/library/default.asp?URL=/library/en-us/dnasp/html/ASPtips.asp


Tip 5: Do Not Cache Database Connections in the Application or Session Objects
Caching ADO Connections is usually a bad strategy. If one Connection object is stored in the Application object and used on all pages, then all pages will contend for use of this connection. If the Connection object is stored in the ASP Session object, then a database connection will be created for every user. This defeats the benefits of connection pooling and puts unnecessarily high stress on both the Web server and the database.

Instead of caching database connections, create and destroy ADO objects on every ASP page that uses ADO. This is efficient because IIS has database connection pooling built in. More accurately, IIS automatically enables OLEDB and ODBC connection pooling. This ensures that creating and destroying connections on each page will be efficient.

Since connected recordsets store a reference to a database connection, it follows that you should not cache connected recordsets in the Application or Session objects. However, you can safely cache disconnected recordsets, which don't hold a reference to their data connection. To disconnect a recordset, take the following two steps:

Set rs = Server.CreateObject("ADODB.RecordSet")
rs.CursorLocation = adUseClient ' step 1

' Populate the recordset with data
rs.Open strQuery, strProv

' Now disconnect the recordset from the data provider and data source
rs.ActiveConnection = Nothing ' step 2

More information about connection pooling can be found in the ADO and SQL Server references.

[GayanR]

Thanks.

It works.

Thanks a lot for the help.