Guidance needed ASP.Net app connection string

Question

Net 2.0 / Visual Studio 2005 / ASP.Net web app

I read many posts and comments on this site but either some are too confusing to follow for me or others don't meet my requirement.

So I have a 3 tier application - UI, BLL, DAL. In my DAL, I created DataSets by draggin storedprocedures from the Server Explorer. So the connection string got hardcoded into the dataset file.

I would appreciate someone explaining simple step-by-step what I must to so that when I pass connection string in the UI (main projects) Settings designer down to the BLL and thenn down to the DAL.

Answer 1

The way that we do it is to include the connection string in our web.config file (app.config works as well). Example:

<add key="ConnectionString" value="Data Source=localhost;Initial Catalog=MyDatabase;User ID=username;Password=abc123"/>

Then, in the DAL we use something like:

using (var connection = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"])) {
    using (var command = new SqlDataAdapter("MyStoredProc", connection)) {
        command.SelectCommand.CommandType = CommandType.StoredProcedure;
        var categories = new DataSet();
        command.Fill(categories);
        return categories;
    }
}

You can use ConfigurationManager.AppSettings["Whatever"] to pull the value specified by "Whatever" from the .config file. Very handy and makes it nice for switching between dev, test, and production environments.

Answer 2

I would avoid drag and drop coding if at all possible. If you play too much with magic then you will get burned.

The connection string should only reside in one place in your application. If all of your tiers are concerned with the connection string then that is a problem. You DAL should be the only tier concerned with the connection string.

A nice way to have the DAL get the connection string is through IoC (Inversion of Control). So that you don't have to pass the connection string in through all the constructors.

DataSets are somewhat dated. If you are working on a new application you might consider using an ORM such as NHibernate or Linq

Answer 3

I am guessing the designer creates you a tableAdapter when you drag the sp over. There is a Connection property on the adapter that you can set for the adapter to use to fill the datatable. I agree with the other guys that you will want that connection info in a configuration file or other place you store configuration. I am also not a fan of the drag and drop into the designer but this should work to manage the database connection.

Answer 4

There's a special section of web.config you can add called connectionStrings. You add to web.config:

  <connectionStrings>
    <add name="oracle" connectionString="Data Source=server; User ID=user; Password=password;"/>
  </connectionStrings>

Then in your C# code:

string connString = ConfigurationManager.ConnectionStrings["oracle"].ConnectionString;

I know it's a little bit more complicated than the first example, but it helps keep the config file organized, specially as this file gets more and more complicated you'll learn to value this connectionStrings section.

Good luck!

Ricardo.

Answer 5

Finally, my question is: Is there a way to override the connection string that is set in the DAL project Settings designer at runtime? ..so that the datasets in the DAL can use this connection string for the table adapters.

I read in another post that if you have the property with the same name (as in the DLL) in the main project's Settings, then the sub-project (DLL) picks up the value of that property over-riding the value set for it in it's own settings.

Any example how to do this?

Answer 6

So no one knows the answer? Or is there no solution for me?

Answer 7

Hi,

This is a very interesting scenario and I've been through this before since I use dataset sometimes in my multi-tier apps. So here's the solution.

I assume apart from UI all are class-lib projects for BLL, DAL. In the DAL you'd have the dataset designer which puts in the connection string under the app.config of the DAL class-lib prj. Now if you need to refer the BAL above the DAL which is again a class-lib prj, copy the connectionStrings section from the app.config of DAL and paste it to connectionStrings section of the app.Config of BAL classlib prj. Here you must notice that the connection string name is not just 'myDataBaseConnStr'. It'd be with detailed namespace reference, like MyApp.DataLayer.DataSet.myDataBaseConnStr. Same process you should perform with UI layer app.config or web.config. Once you've done this successfully you can change in the connectionString in root config file of your UI layer app [web.config or app.config]. This way it will always pickup the connectionString from UI layer since that's be the executing AppDomain at runtime. So you can change the connection string there and have it reflected in underlying BAL and DAL.

Thanks.

Answer 8

s_ruchit, You are truly the best one on this forum, who answered my question correctly and accurately! Thank you ^ infinity times.

(is this documented anywhere on msdn website? or in help documentation?)