Multiple database single application with ASP.NET

Question

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.

I know client requirements are important, but are you sure the client knows what they are talking about here? How are you planning on managing reports/queries that could span multiple years? E.g I want a sales report for the last 6 or 12 or 24 or 36 months? Don't blindly submit to a client request if it is a bad idea (or seems like a bad idea). — Jon P
– Jon P, Commented Oct 10, 2016 at 5:07
You are correct, but if i store all multiple year data in single database, what if i want to search for year 2017 data only and i have to look for all the records of, say, previous 10 years. To avoid that, i thought one solution might be to have separate database for each year. — Hemal
– Hemal, Commented Oct 11, 2016 at 4:43
There are many ways to optimise DataTables and queries with index's etc. One thing that could well help you here are Views — Jon P
– Jon P, Commented Oct 11, 2016 at 4:47

Awais Mahmood · Accepted Answer · 2016-10-10 05:49:15Z

1

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));

edited Oct 10, 2016 at 5:49

answered Oct 10, 2016 at 5:43

Awais Mahmood

1,3364 gold badges23 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Hemal Over a year ago

Thank you for your help. This looks like a possible solution. One problem i will be facing is, on many of aspx pages, i have asp.net data controls and those controls are connected to database through connectionstring parameter and i have assigend a connectionstring to all those controls. Now how can I assign that dynamic database connection string to those data controls on aspx pages?

Awais Mahmood Over a year ago

The proper way for any app development would be that any control or any input or anything that wants to communicate with your database, must call the DAL helper for all DB operations. CRUD, and connection string too. So, u should move ur conn str to one place in DAL and then everyone will get it from there

Hemal Over a year ago

Can you provide some code example of above code?I cant get it working.

Hemal Over a year ago

I placed your code in class file. When I access that function from webservice, cant get session value

Awais Mahmood Over a year ago

Let us continue this discussion in chat.

Collectives™ on Stack Overflow

Multiple database single application with ASP.NET

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related