I’m working on a portal/e-shop solution where I’ll be using multiple databases and needs to change the Connection used by my DataContext object at runtime.
I use the HttpContext.Current.Items collection to store which connection is currently “active” - this is setup in the Application_AcquireRequestState event in Global.asax which fires for every request – and using the OnCreated method that is called as part of the DataContext’s constructors, I can easily change which connection to use at runtime.
The good thing about putting the “connection switch” in the OnCreated method is that no matter how I use the DataContext this code is always executed (I have a LinqUtil class which implement a request-scoped DataContext pattern for LINQ to SQL), and using the below approach I don’t have to worry about which Connection is used as this is transparent to any helper / DAL classes.
The code for the partial class which implements the OnCreated method for my DataContext looks (something!) like this:
1: public partial class GWportalDataContext : System.Data.Linq.DataContext
2: {
3: // In order to use multiple database connections we use the HttpContext.Current.Items collection to store which database we're currently using (per request)
4: partial void OnCreated()
5: {
6: switch (HttpContext.Current.Items[ConfigurationManager.AppSettings["WebsiteHttpContextItemKey"]].ToString())
7: {
8: case "GWDK":
9: this.Connection.ConnectionString = ConfigurationManager.ConnectionStrings["GWportalConnectionDK"].ConnectionString;
10: break;
11: case "GWNO":
12: this.Connection.ConnectionString = ConfigurationManager.ConnectionStrings["GWportalConnectionNO"].ConnectionString;
13: break;
14: default:
15: this.Connection.ConnectionString = ConfigurationManager.ConnectionStrings["GWportalConnectionDK"].ConnectionString;
16: break;
17: }
18: }
19: }