Dynamic connection string handling with DAAB
I'm in the process of adding a data access layer to our ASP.Net 2.0 web application, which was written almost exclusively using inline SQL calls and copious use of copy / paste. Due to business issues, I can only re-distribute small chunks of the application in one go (can't fix and do a huge redesigned project), so I have to live with certain design decisions until things are loosely coupled enough to make big changes and test them right.
I decided to use the Enterprise Library data access box as a plumbing for the data access layer in the future, and one such prior design solution is causing me problems. We are currently getting the "master" connection string for our application from the admin database based on the user supplied account ID so that we can allow a single application installation to access multiple databases. My problem comes from trying to figure out a best practice for getting that connection string (or account id) into the DAL.
The previous implementation was to store the encrypted connection string in a cookie, so my current hack approach is to get the connection string from there and then use DAAB like this:
Public Shared Function GetResultsByKeywords(ByVal key1 As String, ByVal key2 As String, ByVal key3 As String, ByVal key4 As String) As DataTable
Dim db As SqlDatabase = New SqlDatabase(Connection.GetConnectString())
Dim dt As DataTable = New DataTable
Using dr As IDataReader = db.ExecuteReader("sel_eHELP_ITEMS", key1, key2, key3, key4)
dt.Load(dr)
End Using
Return dt
End Function
Where Connection.GetConnectString () retrieves the connection string from the cookie.
I know this is far from the best way to do it and I need to fix it, but I am struggling with the "right" way to do it. Perhaps create a base class that I can initialize with a connection string and inherit from all other classes, but I'm not sure how this would look and where I would first initialize an instance of that class. On every page before accessing any DAL functions?
Any guidance would be greatly appreciated. Thanks.
Update
Let me be clear. In most cases there will be 3 or 4 different connection strings for installation. "Account ID" is not a user ID and is used by multiple users to identify a specific database to connect to.
a source to share
Edit:
With your latest update, I still recommend looking into the configuration with DAAB http://davidhayden.com/blog/dave/archive/2006/01/23/2744.aspx
I am having a hard time finding / opening Ent Lib documents, but I would see if it has an equivalent to the ChangeDatabase method of the SqlConnection object. http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.changedatabase.aspx
Here's one way to do it. Since I don't think your DAL needs to be aware of your switch switch requirement, I would set your DAL with a ConnectionString as a constructor parameter. Use the wrapper method / property to hide / encapsulate this requirement and get the DAL instance you would like to use in your application.
(please excuse c #)
public class MyDAL
{
private string _connectionString;
public MyDAL(string connectionString)
{
_connectionString = connectionString;
}
public int MyDatabaseCall()
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = new SqlCommand("my sql", conn))
{
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
//...data access
return 0;
}
}
}
}
}
public class MyApp
{
public static Dictionary<string, MyDAL> myDatabases = new Dictionary<string, MyDAL>();
public string GetConnectionString(string database)
{
return System.Configuration.ConfigurationManager.ConnectionStrings[database].ConnectionString;
}
public void StartUp() // Global.asax Application_Start ?
{
myDatabases.Add("Database1", new MyDAL(GetConnectionString("Database1")));
myDatabases.Add("Database2", new MyDAL(GetConnectionString("Database2")));
myDatabases.Add("Database3", new MyDAL(GetConnectionString("Database3")));
}
public MyDAL DataAcccessLayer
{
get
{
string usersDB = FigureOutUsersDatabase();
return myDatabases[usersDB];
}
}
public void UseSomeData()
{
var myData = DataAcccessLayer.MyDatabaseCall();
//Do Stuff
}
}
Hope this helps, good luck! And be very careful what you put in the cookies.;)
a source to share