User selects a database at runtime

Topics: Data Access Application Block
Jul 25, 2008 at 9:19 PM

I am trying to support the following use case.  When a user starts my Windows Form application they can select a target MS Access database to modify.

The following code snippet uses the default database in the app.config

string sql = "select * from customer";

Database db = DatabaseFactory.CreateDatabase();

using (DbCommand command = db.GetSqlStringCommand(sql))
{
    using (IDataReader reader = db.ExecuteReader(command))
    {
        //Do something here
    }
}

Do I have to do something like this instead...

string sql = "select * from customer";

System.Data.Common.DbProviderFactory factory = System.Data.Common.DbProviderFactories.GetFactory("Microsoft.Jet.OLEDB.4.0");

Database db = new GenericDatabase("some connection string provided by the user", factory);

using (DbCommand command = db.GetSqlStringCommand(sql))
{
    using (IDataReader reader = db.ExecuteReader(command))
    {
        //Do something here
    }
}


Am I missing something here?  Why can't I pass a connection string to the DatabaseFactory.CreateDatabase() method?





Jul 26, 2008 at 2:23 AM
Are you supporting multiple different database platforms? i.e, SQL Server , Oracle, etc?  I have seen implementations, where depending on the user, the connection string will be different and the connection string is stored in a configuration database.  If only one platform, you can use <mshelp:link tabindex="0" keywords="T:Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase">Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase</mshelp:link> or one of the siblings directly. Or if you can store the provider and map it to the correct implementation of Database.


Jul 27, 2008 at 9:16 PM
No, I am only supporting MS Access databases.  The user needs to select a target database to modify but the application does not know what database the user will select until run-time.  It would be nice just to send a connection string to an overloaded CreateDatabase() method.
Jul 28, 2008 at 11:25 PM

Hi,

Just providing a connection string wouldn't be enough; EntLib requires the provider name too to determine both the ADO.NET provider factory to use and the Database type that provides the customized behavior.

 It's far from ideal, but you can use a DatabaseProviderFactory with a dictionary configuration source like this:

            DictionaryConfigurationSource source = new DictionaryConfigurationSource();

            ConnectionStringsSection section = new ConnectionStringsSection();

            section.ConnectionStrings.Add(

                new ConnectionStringSettings(

                    "connection string",

                    @"Data Source=.\sqlexpress;Initial Catalog=Northwind;Integrated Security=True",

                    "System.Data.SqlClient"));

            source.Add("connectionStrings", section);

 

            DatabaseProviderFactory factory = new DatabaseProviderFactory(source);

 

            Database db = factory.Create("connection string");

Of course, this isn't much better than what you had initially (although you could use OleDbFactory.Instance to get the DbProviderFactory instead of going through the DbProviderFactories class and get rid of some clutter.)

Fernando<!--EndFragment-->

Jul 29, 2008 at 12:04 AM

Sorry for hijacking this thread,

Could you please tell me, for using Data Application Block with SQL CE, do i need to first create any tables in SDF file and then start using Data Application Block. Actually I am trying to use Caching Application Block with SQL CE as Backing Store. Can you please tell me how can i acheive this without configuration file and what are the DLLs required for this.

Thanks,

venkatesh