While working on the search pages solution (read about it here) I realized I need to be able to save data connections in a database.
I’m using a database to manage everything that has to do with the application itself (membership, roles, search page info, etc…) but the actual data comes from other databases.
The Data Application Block supports using different connection as long as these connections are defined in the web.config file.
Because I didn’t want to make changes to the config file too often, and I also wanted users to be able to add new data connections without my help, having these connections in a database will be great.
After looking through the data block’s code, I realized I need to create a new configuration source.
I figured someone has already done this, and sure enough, a quick google search later I found out that such a solution exists right there in the enterprise library’s Quick Starts folder.
I quickly added the SqlConfiguration project to my solution and thought my problems were solved.
Unfortunately, it just didn’t work.
I also didn’t really want to save the entire connections section as on row in a table, that will make it harder to maintain. So I decided to create an implementation just for the connections section and inherit the rest for the SqlConfiguration.
The DataConnections table:
ConnectionId
ConnectionName
ConnectionString
ProviderName
you can see the columns are the same as the connection attributes in web config.
The Configuration Source:
namespace IshaiHachlili.RapidBackOffice.Configuration.ConnectionsSqlConfigurationSource
{
public class ConnectionsSqlConfigurationSource : IConfigurationSource
{
private string defaultConnectionString = String.Empty;
private const string GetConfig = "EntLib_GetConfig";
private const string SetConfig = "EntLib_SetConfig";
private const string RefreshSection = "EntLib_UpdateSectionDate";
private const string RemoveSection = "EntLib_RemoveSection";
public ConnectionsSqlConfigurationSource()
{
if (ConfigurationManager.ConnectionStrings.Count > 0)
{
defaultConnectionString = ConfigurationManager.ConnectionStrings[0].ConnectionString;
}
}
public System.Configuration.ConfigurationSection GetSection(string sectionName)
{
if (sectionName != "connectionStrings")
{
IConfigurationSource source = new SqlConfigurationSource(defaultConnectionString, GetConfig, SetConfig, RefreshSection, RemoveSection);
return source.GetSection(sectionName);
}
ConnectionStringsSection section = new ConnectionStringsSection();
//System.Configuration.ConnectionStringsSection
//Get the connections from the DL
ConnectionsDL dl = new ConnectionsDL();
IDataReader dr = dl.GetDataConnections();
while (dr.Read())
{
string connectionName = dr["ConnectionName"].ToString();
string connectionString = dr["ConnectionString"].ToString();
string providerName = dr["ProviderName"].ToString();
section.ConnectionStrings.Add(new ConnectionStringSettings(connectionName, connectionString, providerName));
}
return section;
}
}
public class ConnectionsDL
{
public IDataReader GetDataConnections()
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetStoredProcCommand("GetDataConnections");
IDataReader dr = db.ExecuteReader(cmd);
return dr;
}
}
}
The GetDataConnections stored procedure simply returns all the connections in the DataConnections table.
Because I'm using the CreateDatabase() method with no DB name, the default database will be used. This is the management database for my application and the only connection that will be saved in the web.config.
Using the new configuration source in the data layer
protected Database getDatabase(string DBName)
{
if (String.IsNullOrEmpty(DBName))
return DatabaseFactory.CreateDatabase();
IConfigurationSource source = new ConnectionsSqlConfigurationSource();
DatabaseProviderFactory factory = new DatabaseProviderFactory(source);
Database db = factory.Create(DBName);
return db;
}
I added this method to a base class that all the classes in my data layer inherit from. now instead of using the DatabaseFactory.CreateDatabase() I use getDatabase(DBName).
As you can see, when there's no DBName, I still use the DatabaeFactory method, there's no reason to get the connections from the database when using the default connection that's saved in the web.config.
You can also add caching to avoid calling the database everytime you need to get the connections list.
Subscribe