more than one page needs connection to DB

[Carlos]

Hi all,

I am currently working on a project that needs to
maintain the connection to the DB throughout the
pages that the user navigates. I have already validated
my DB connection and some operations on the start-up page, but I am not sure
how to make the same persist
in the code behind for the other pages using C#. I appreciate any help.

Thanks,

Carlos.

 

[DalePres]

You can create the connection in your Session_Start event in global.asax. I
am curious, though, why you need to maintain the connection throughout the
session. That will severely limit the scalability of your application.

DalePres
MCAD, MCDBA, MCSE

 

[DalePres]

And one thing I left out of my last reply. After creating the connection,
you will have to save it to a session variable.

This won't work if you use a StateServer or SQL Server for session state
because only serializable objects can be maintained in state in those two
environments.

DalePres

 

[Steve C. Orr [MVP, MCSD]]

Store your database connection string in your web.config file, like this:

<configuration>
<appSettings>

<add key="DSN" value="Server=(local);Database=DBName;UID=sa;PWD="/><add
key="OtherConnectionString"
value="Server=(local);Database=DBName2;UID=sa;PWD="/>

</appSettings>
</configuration>

This is a nice way to manage it. You can change the connection string easily
without recompiling the app or restarting IIS or anything, and the change
goes into effect immediately.

Then in your code behinds you can get the connection string like this:
Dim sConn As String = ConfigurationSettings.AppSettings("DSN")

You'll want to put that line on every page that does data access. Open the
connection just before you need it and close it as soon as you are done with
it. Automatic connection pooling in ADO.NET makes this a very efficient
technique.

Do not store an open connection across pages. This will severely limit the
scalability of your web site and inhibit the automatic connection pooling
from doing its job efficiently.

 

[Eliyahu Goldin]

I understood Carlos couldn't use web.config since he gets user name and
password from the user on the start-up page.

And probably he doesn't mean keeping open connections across the pages.
Rather connection strings. Once the user is validated, the connection string
can be stored in a session variable.

Eliyahu

 

[Joerg Jooss]

Hi all,

I am currently working on a project that needs to
maintain the connection to the DB throughout the
pages that the user navigates.

Why? This will never scale. That's why connection pools exist...

Cheers,

 

[Carlos]

Thank you so much all for your time to answer this question.

Carlos.