CODEWITHSUNDEEP
c#sqlasp.netsql-servermultiple-databases
Hemal
Hemal

Reputation: 3760

Multiple database single application with ASP.NET

I created an application with Visual Studio and ASP.NET 4.0 with MSSQL 2012 Express as backend.

Initially there was no planning of multiple databases, so I programmed the app accordingly with single connection string for a single database.

Now, client's requirements has changed and he is asking for separate database for each year, DATABASE2015 for year 2015, DATABASE2016 for year 2016 and so on.

I will migrate structure and data from previous year database to new year database and thats fine.

But how will I connect to different database for different user as per their year selection at login time?

Also, I am having a single connectionstring in web.config and I have referenced that connection string throughout my project in both code side and html side for asp.net controls.

Please advise.

Upvotes: 1

Views: 6580

Answers (1)

Awais Mahmood
Awais Mahmood

Reputation: 1336

As Jon P said, what your client is asking would be a nightmare for you to manage and extensive multiple year reporting would be very difficult. But since you have asked a solution, so I think I can guide you somewhere about how to do that.

In your web.config file you could make your connection string as,

<add name="YourConnStr" connectionString="Data Source=.;Initial Catalog={0};UID=sa;PWD=password;"
  providerName="System.Data.SqlClient" />

Then on every user login as per the selected year on login screen you would be validating the user name and password from some database table. In that table, you could make a column for storing the database name, and on successful login, store that name in a session variable.

Or you might make another table with a foreign key from Users table. In your new table you could store the database names per user per year.

In your DAL, while getting the connection string for SqlConnection, you could do as,

var db = System.Web.HttpContext.Current.Session["dbName"].ToString(); // from session variable
var connection = System.Configuration.ConfigurationManager.ConnectionStrings["YourConnStr"].ConnectionString;
return new SqlConnection(string.Format(connection, db));

Upvotes: 1

Related Questions