ASP.net declaring a SQL connection across multiple pages/ascx files

I'm wondering what the other ASP.net guys around here do in terms of defining SQL connections.

In classic ASP I'd make a headers file and have the connections there, and do an include.

It doesnt seem to be so simple now, I've just been declaring the connection in each page separately, which is really a mess. (I'm just starting out asp.net though so just working on getting stuff working at all)

I tried declaring it in my master page, though all my ascx web part files can't use the connections in them that way (or is there a way to get it to work?)
I tried caching the connections but for some reason caching on my web parts page only works some of the time.
I could declare them as session variables I guess? But there would be a better way wouldnt there.

interested in tips

 

thats an easy one, create an object in your App_Code folder which holds ur db object, you can make it static if you want to retrieve it from ur pages without object instansiation.

 

My connection string is in my web.config file.

I don't put anything database related in the App_Code folder. Only code relating to that part of the project.

I have a Business layer which is the layer between the website and the database.

 

One method I've seen is to create a class of type Page , and then have each aspx page inherit from it. Page contains common attributes like the database connection (which is static), and from there can be accessed by all the children.

 

The problem is if you change the connection string you have to recompile the project. (same goes for mordy's suggestion of App_Code)

If you store it in the web.config file, (.config files cannot be accessed at all so you can create your own config files) then all you need to do is open the web.config file in notepad to change the connection string.

 

noob question:

how do you reference it in a document if you put it in web.config?


Dim conn As New SqlConnection("Server=SERVER;" & _
"Database=Database1;User ID=user;Password=pw")


Dim conn2 As New SqlConnection("Server=SERVER;" & _
"Database=Database1;User ID=user;Password=pw")


Dim conn3 As New SqlConnection("Server=SERVER;" & _
"Database=Database1;User ID=user;Password=pw")



Dim BWFConn As New SqlConnection("Server=SERVER;" & _
"Database=Database2;User ID=user;Password=pw")



Dim WAYConn As New SqlConnection("Server=SERVER;" & _
"Database=Database3;User ID=user;Password=pw")




thats how I declare them, then I have

Dim SignOutCommand As New SqlCommand("Statement", WAYConn)

Dim SignoutReader As SqlDataReader = SignOutCommand.ExecuteReader()


While SignoutReader.Read()

blah blah blah maybe use one of the other conns

end while



thats how I'm doing my SQL now - because its similar to the ways I'm familiar with. I'm sure theres lokts of better ways, I just wanna get down and dirty with the code, open multiple connections at once (ie loop through one, then do something to another - alot of this can be done with SQL joins/subquerys but theres some stuff that can't)

 

I agree with Elyzion. Use the web.config for storing connection strings as that is what the web.config is used for; application settings.

The other benefit of the web.config is that it can change without need a rebuild of your code base.

What i do is create is a class library project used only for connection to the database and related SQL commands. If you do not like creating many instances of it you can implement the singleton pattern.

 

You can store your connection strings in the web.config appSettings section or the ConnectionString settings.

Have a look at the System.Configuration name space

To get a value when using the appSettings configSection you can do this
string val =System.Configuration.ConfigurationManager.AppSettings.Get("<name>");

 

No, wrong.

if you dont put your business layer code in the App_Code folder then its not automatically compiled and available to intellisense.

the code to retrieve the connection string in the business layer is

Code:

ConnectionStringSettingsCollection connectionStrings = ConfigurationManager.ConnectionStrings;
string connString="";
//find the connection string
foreach (ConnectionStringSettings connection in connectionStrings)
{
       if (connection.Name == "MyConnectionStringName")
               connString = connection.ConnectionString;
}

 

That is what project references are for.
You put your database connection in your data layer or what ever layer you want and you reference that project to your web project therefore you get your IDE intelli-sense and the related assemblies will get copied to your website's bin directory.

 

also wrong.

The project is always compiled automatically. How do u plan on setting up the Pages in such a way that they dont recompile when you change the web.config ... im so confused??

are you perhaps talking about ASP.NET 1.1 and im talking about ASP.NET 2.0??

 

I dont see the difference between putting it in a folder called "DataLayer" in the App_Code folder or creating a completely different assembly

 

A change in the web.config does not need the app to recompile unless you changed the key name value of the config setting which needs a source code change.

I do not know what you mean when you talk about setting up the web pages.
The aspx pages will have a reference to the class library project that connects to the database. This project will retrieve the string value from the web.config and construct the SQLConnection object.

If you database changes or the credential changes, you change the string value in your web.config. At no time do you need to change your source code for a rebuild.

 

i never said not to write it in the web.config ... whats your point

 

You are right is saying that there is no code difference in putting the code in the app_code or as a separate project. This is more an architectural decision.
Each person will have their own preference and/or coding guidelines so there is no right or right.

 

My point is connection strings should not be embedded in your source code. There are reasons why Microsoft have a connectionString configSection in the web.config.

Connection strings do change, whether you are promoting your code from test,to staging to production things will change. Database instances, service accounts etc. You do not want to be rebuilding your code base every time because you need to update your connection strings. That is why you should put your connection strings in the web.config, after all it is an application/configuration setting that is dependant on the environment that it is to be deployed to.

 

When you publish your Web Site project, your App_Code get compiled down to a single or multiple .dll files.

You cannot just open a .dll file in notepad and edit a connection string.

Wrong. I can make a video if you like.

Edit: I made a video, just getting windows movie maker to compress it and edit out the username/password in the connection string.

 

Sorry, i may have confused myself and others.

I never said that the connection string shouldnt be in the web.config, i even posted some code to read it out the connection string from the web.config

I think the issue here is that if you want to have an sql connection over multiple pages, then you have to use either derived Page class or a class in ur App_Code or even an external class.

Does that cover it?

 

A data access class?

 

I would create a singleton data access class and package it into its own class library. That way you get a clear separation of data logic from your business and presentation. Not only that it means your data access library is now reusable for future projects as it is decoupled from other tiers.