DatabaseProviderFactory: create database specifying the connection string

Topics: Data Access Application Block
Jun 4, 2014 at 5:55 PM
I'm using Enterprise Library's Data Access Application Block to access SQL Server database.

I access database logic by using stored procedures, so parameter discovery (built in on SqlDatabase) is a plus to me.

Currently I do something like this to connect to the database and execute something:
DatabaseProviderFactory dbFactory = new DatabaseProviderFactory();
            SqlDatabase db = dbFactory.CreateDefault() as SqlDatabase;

            object[] parameters = { 1, "Test", true };

            using (DbCommand cmd = db.GetStoredProcCommand("MyStoredProcedure", parameters)
                using (IDataReader reader = db.ExecuteReader(cmd))
                    DataTable dt = new DataTable();

                    //  DO STUFF
In config file, I have the following configuration:
<?xml version="1.0" encoding="utf-8"?>
    <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=" requirePermission="true" />
  <dataConfiguration defaultDatabase="MyDb" />
    <add name="MyDb" connectionString="Application Name=MyApp;User ID=myUserID;Password=myUserPassword;Server=myServer;Database=myDb;Connect Timeout=30" providerName="System.Data.SqlClient" />
But now I need to specify the username and password of the connection string on demand - by other terms, username and password are provided to my DAL and the connection must be made on demand with those credentials instead of the credentials configured in config file.

Also, we don't want to add new connection strings to web.config file as new users show up. What I'd like to have is read the current connection string from config file, change the username and password (until here I'm able to do this) and then create a new instance of SqlDatabase with this dynamic connection string.

Is there any way of using SqlDatabase and allow this behavior? The only things I found out was specifying the connection string name that is present on the config file (not the connection string it self).
Jun 5, 2014 at 4:06 AM
If you have different credentials that are being passed each time then you can instantiate a SqlDatabase with the appropriate connection string. To do that you can use a SqlConnectionStringBuilder. Here's an example:
private string GetConnectionString(string databaseName, string userName, string password)
    string connectionString = ConfigurationManager.ConnectionStrings[databaseName].ConnectionString;

    DbConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder(connectionString);
    connectionStringBuilder.Add("User Id", userName);
    connectionStringBuilder.Add("Password", password);

    return connectionStringBuilder.ConnectionString;

private SqlDatabase GetSqlDatabase(string databaseName, string userName, string password)
    return new SqlDatabase(GetConnectionString(databaseName, userName, password));

SqlDatabase db = GetSqlDatabase("MyDb", "scott", "tiger");
You could cache the database instance if you are going to create the same database name with the same credentials.

You could also set the database with the DatabaseFactory.SetDatabases method and then use the DatabaseFactory.CreateDatabase() method.

Randy Levy
Enterprise Library support engineer
Support How-to
Jun 5, 2014 at 10:05 AM
Hello Randy, thank you for your help, creating a new instance of SqlDatabase providing the connectionstring was actually what I needed.

How can I cache the database instances?

And DatabaseFactory.SetDatabases() will create the connectionstring on my config file or will it only cache the connection?