Dynamic connectionstring without web.config

Topics: Building and extending application blocks, Enterprise Library Core
Dec 3, 2012 at 4:33 PM

Hi,
I am using EntLib in VS2010 and I need to pass a connectionstring dynamically and connect to a stored procedure.
How can I do that?
 
This is what I tried but I get an error: public class CustomDatabaseFactory { private string serverName; private string connectionString; private string databaseName; public CustomDatabaseFactory(string ServerName, string DatabaseName) { this.serverName = ServerName; this.databaseName = DatabaseName; } static readonly DbProviderFactory dbProviderFactory = DbProviderFactories.GetFactory("System.Data.SqlClient"); public Database CreateDatabase(string connectionString) { Database db = null; //database mydb = new EnterpriseLibrary.Data.Sql.SqlDatabase("connection string here"); db = new GenericDatabase(connectionString, dbProviderFactory); return db; } public Database GetDatabase(string connectionString) { DbConnectionStringBuilder builder = new DbConnectionStringBuilder(); Database db = null; db = new GenericDatabase(connectionString, dbProviderFactory); return db; } public DataTable PMHistory(int Id, string connectionString) { string spName = "stp_pm_History"; DataTable dt = new DataTable(); Database db = DatabaseFactory.CreateDatabase(connectionString); object[] parameters = { Id }; DbCommand command = db.GetStoredProcCommand(spName, parameters); return dt; } } public partial class _Default : System.Web.UI.Page { protected ExportFile createFile; protected void Page_Load(object sender, EventArgs e) { try { string connectionString = "Data Source=SQLDEV01;Initial Catalog=test;Persist Security Info=True;User ID=rrrrr;Password=xxxxx"; CustomDatabaseFactory getData = new CustomDatabaseFactory("", ""); getData.PMHistory(1, connectionString); //FALLS HERE } catch (Exception ex) { Response.Write(ex.ToString()); } } } System.Configuration.ConfigurationErrorsException: The requested database Data Source=SQLDEV01;Initial Catalog=test;Persist Security Info=True;User ID=rrrrr;Password=xxxxx is not defined in configuration. at Microsoft.Practices.EnterpriseLibrary.Data.DatabaseConfigurationView.ValidateConnectionStringSettings Thanks for the help

Dec 3, 2012 at 8:58 PM

Is the dynamic connection string reused in your application or is it a one off?

If it is reused in the application then you can programmatically configure the Data Access Block at startup using the Fluent API.  If it just a one off why not just "new-up" a SqlDatabase (or a GenericDatabase)?

The latter would look like this:

        private DataTable PMHistory(int Id, string connectionString)
        {
            DataTable dt = new DataTable();
            Database db = new SqlDatabase(connectionString);

            var cmd = db.GetSqlStringCommand("select * from [Log]");
            var ds = db.ExecuteDataSet(cmd);
            Response.Write("Records: " + ds.Tables[0].Rows.Count);

            return ds.Tables[0];
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            try
            {
                string connectionString = @"data source=.\SQLEXPRESS;Integrated Security=SSPI;User Instance=false;Database=LoggingDefault";

                PMHistory(1, connectionString);
                PMHistory();
            }
            catch (Exception ex)
            {
                Response.Write(ex.ToString());
            }
        }

Or you could register the connection at startup (in the global.asax):

    public class Global : System.Web.HttpApplication
    {

        void Application_Start(object sender, EventArgs e)
        {
            var builder = new ConfigurationSourceBuilder();

            builder.ConfigureData()
                   .ForDatabaseNamed("MyDatabase")
                     .ThatIs.ASqlDatabase()
                     .WithConnectionString(@"data source=.\SQLEXPRESS;Integrated Security=SSPI;User Instance=false;Database=LoggingDefault")
                     .AsDefault();

            var configSource = new DictionaryConfigurationSource();
            builder.UpdateConfigurationWithReplace(configSource);
            EnterpriseLibraryContainer.Current
              = EnterpriseLibraryContainer.CreateDefaultContainer(configSource);
        }
    }

Then the data access could could look something like this:

        private DataTable PMHistory()
        {
            DataTable dt = new DataTable();
            Database db = DatabaseFactory.CreateDatabase("MyDatabase");

            var cmd = db.GetSqlStringCommand("select * from [Log]");
            var ds = db.ExecuteDataSet(cmd);
            Response.Write("Records: " + ds.Tables[0].Rows.Count);

            return ds.Tables[0];
        }

The method DatabaseFactory.CreateDatabase(string) does not take a connection string but a database name.

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com