Global connection variable

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi



I would like to open a db connection and then pass the same to all my sql
commands across various pages. Is there a way to implement this?



Thanks



Regards
 
The most common way to do this is to declare the connectionstring in
Web.config's appSettings section, and then create a Connection each place
you use it. To declare the connectionstring in Web.config, do the following:

<configuration>
<appSettings>
<add key="connectionstring" value="the connectionstring you will be
using"/>
</appSettings>
</configuration>

(NOTE: The key can be whatever you want, but it is best to keep it simple.
You can also create multiple <add/> elements, people sometimes create one
for things like their email server)

To access this value programmatically, use the following:

System.Configuration.ConfigurationManager.AppSettings("connectionstring")

This will return a String variable, which you might use to create a
connection like the following:

Dim myconnection As New
SqlConnection(System.Configuration.ConfigurationManager.AppSettings("connectionstring"))

Keep in mind that some constructors give you the option of simply specifying
a connectionstring, so you don't even need to create a Connection variable
in some cases. Hopefully this will get you started. Good Luck!
 
Hi

I would like to open a db connection and then pass the same to all my sql
commands across various pages. Is there a way to implement this?
Click to expand...

You can't do that since an SqlDataReader has the restriction of only
one open datareader at a time against the same SqlConnection.
So you need to have one connection for every page hit. (unless you
want to create your own lock logic and risc that users are being cued
up and everything goes DOWN in speed)
What you can do is to create one SqlConnection in the OnInit and then
do a dispose on that SqlConnection in the UnLoad of your page!
Then you'd inherit from your own WebPageClass which in turn inherit
from WebPage in ASP.NET!
Then you'd create a protected get only property in that middle class
which would make that SqlConnection accessible for you from "all over"
the place!

But...!
Obviously you should never though think about something as tiny as a
SqlConnection in your PAGE logic, your PAGE should use a "controller"
layer (read up on MVC, Model View Controller) which in turn could be
accessing its data over an analog TELEPHONE for all you care in your
View layer (ASP.NET)
If you sprangle your page with stuff like SqlConnection you can bet
your life on that the customer is gonna come demanding that you port t
to "oracle" or "xml, object based databases" or "something other new
and shiny technology"...
So to force encapsulation, singularity, coherence and in general terms
an otherwise nice day ;)
Read up on MVC and create a Facade class which you make accessible
through a Singleton which hides away stuff like "SqlCommand" and
"SqlConnection" etc...
Then if you'd want to be REALLY advanced you could also depend upon
stuff like an IDbConnection and create a Factory Method and an
Abstract Factory etc to create your IDbConnection...

Though the PERFECT solution is obviously to ditch everything I've said
and go out and use an ORM tool like for instance nHibernate or
something...

PS!
I never thought I'd have so much to say about such a small question...

..t
 
I would like to open a db connection and then pass the same to all my sql
commands across various pages. Is there a way to implement this?
Click to expand...

This is one of the worst things you could do in terms of performance.
Consider using a DAL (database abstraction layer) instead, which will fetch
connections from the connection pool as and when needed, and dispose of them
thereby returning them to the pool as soon as they are no longer needed.

http://msdn2.microsoft.com/en-us/library/aa480458.aspx
 
John,
It is generally unwise to attempt to pass around some sort of global
SqlConnection.
What you want to do is make the connection string available for each page,
and open the connection just before it is used. Then, call Close or Dispose
and allow the connection to go back to the ADO.Net Connection Pool, which is
specifically designed for this purpose.
Peter

Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short urls & more: http://ittyurl.net
 
Back
Top