Opening and Closing DB connection in application events, Any suggestions !

[hndlogic]

Hello

I need to ask that if I add a module in my web project. And declare a connection object like this:

Public CN as SqlConnection

And then I open that connection in Application_BeginRequest and close it in Application_EndRequest event in my global.asax. And use this CN object in all my database functions with every page.

Is this method right or it will raise some error ?

[Cstick]

No, you don't want to do that. Also, it may be wise to not use a module for your I/O.

Variables declared public in a module behave exactly the same as public shared variables in classes. What happens is that these variables will be shared among all the users of the application.

A simple way to test this is to create a page that sets a public datetime variable in a module on the initial Page_Load. The page should also have a button which when clicked displays the variable's value. Browse to the page with browser 1, such as Internet Explorer, and click the button to display the time. Then browse to the page with browser 2, Firefox, and click the button. Now click the button on browser 1 again and you'll see that IE and FF are both displaying the later time.

In the case of a SqlConnection, you'll find that 2 users attempt to use the connection simultaneously or the connection is opened multiple times, hence exceptions.

The overhead of creating a SqlConnection is so small you'll never notice, s don't worry about creating them as many times as necessary. If you're concerned about being clean and tidy, be sure to dispose of all the IDisposable objects as you can when you're finished with them.

[hndlogic]

Thanks. And if I do like this,

In my Module
------------------

Private CN as SqlConnection

Public Sub OpenCN()
'open the CN here
End Sub

Public Sub CloseCN()
CN.Close
CN = Nothing
End Sub

Public Function GetCN() as SqlConnection
GetCN = CN
End Sub



Now I will call OpenCN in my Application_BeginRequest and CloseCN in my Application_EndRequest event and GetCN in my other page function whereever I need the Connection object.

Will it work OK ?

[Cstick]

No, that is still doing the same thing, except the variable is private. And forget about opening and closing the connection in application, session or any other events. Create, Open, Close and Dispose of them as you need them.

There are few cases in which you'd want to persist a connection. Are you doing something unusual?

If you are just trying to reduce code, reuse code, or something similar then I suggest you look at the Enterprise Library DAAB, here (http://msdn2.microsoft.com/en-us/library/ms954827.aspx).