Use Enterprise Library with MS Access?

Topics: General discussion
Jan 14, 2007 at 5:54 PM
Can the libray data access be used against ms access? Client is not yet ready to migrate to sql server. I'd like to use the abstraction afforded with this design pattern so when they do decide to upgrade it will take minimal effort.

Thanks,
Bill
Jan 15, 2007 at 2:24 PM
Yes.

There is a GenericDatabase Class in the DAAB that works with any database using OLE-DB and ODBC. When you set-up your connection string, just specify System.Data.Odbc as the provider for Microsoft Access.

<connectionStrings>
<add name="ConnectionString" connectionString="..." providerName="System.Data.Odbc" />
</connectionStrings>


If you set it as your default database and call

Database database = DatabaseFactory.CreateDatabase()

The DAAB will see that you are using ODBC and automatically grab the GenericDatabase Class to communicate with it.

Regards,

Dave

_______________________

David Hayden
Microsoft MVP C#
http://www.davidhayden.com/
Jan 27, 2007 at 9:53 PM
Dave,
Thanks for the reply. I've been trying several things but keep getting exceptions.
Here is a snippet and the excdeption it throws.
Thanks for your help.

<connectionStrings>
<add name="fooConnectionString" connectionString="Data Source=C:\webs\App_Data\foo.mdb;Persist Security Info=True;" providerName="System.Data.Odbc"/>
</connectionStrings>

Database db = DatabaseFactory.CreateDatabase("fooConnectionString");

DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);

DataSet ds = db.ExecuteDataSet(dbCommand);

System.Data.Odbc.OdbcException was unhandled by user code
Message="ERROR IM002MicrosoftODBC Driver Manager Data source name not found and no default driver specified"
Jan 28, 2007 at 3:00 PM
Some improvement. I can get the oledb to connect and return results but the odbc connection still fails.
<add name="odbcConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|foo.mdb;Persist Security Info=True;" providerName="System.Data.Odbc"/>
<add name="oledbBooksConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|foo.mdb" providerName="System.Data.OleDb"/>

Is there a strong argument to use one over the other?

Thanks,
Bill
Jan 28, 2007 at 4:28 PM
The OLEDB is the preferred way for performance reasons.

I wrote up an example of using the DAAB and Microsoft Access using both ODBC and OLEDB. I show it using Microsoft Access 2007 which will provide a slightly different connection string, but the article should help:

Using Enterprise Library DAAB and Microsoft Access 2007 via ODBC and OLEDB

The code is the same. Setting the connection string and provider appropriately is the tricky part.

Regards,

Dave

_________________________

David Hayden
Microsoft MVP C#
Jul 28, 2007 at 6:45 AM
GenericDatabase database doesn't support parameterize query, so it isn't suitable for multi database project, is there any way?
Jul 30, 2007 at 2:31 PM
Hi Nima,

What do you mean by the GenericDatabase not supporting parameters? What it does not support is parameter discovery, but parameterized queries do work.

Here's a sample:

Database db = DatabaseFactory.CreateDatabase("test");

DbCommand command = db.GetSqlStringCommand("select count(*) from orders where shipcountry = ?");
db.AddInParameter(command, "foo", System.Data.DbType.String, "France");

int rows = (int)db.ExecuteScalar(command);

Regards,
Fernando
Jul 30, 2007 at 5:01 PM
Hi Fernando,

Thank you so much
I forgot that MSAccess use '?' for parameters
Sep 17, 2007 at 5:05 AM
Does anyone know to use the UpdateDataSet for MS Access Database from DAAB.. as shown in the Data Acces Walkthrough examples:

EG: (Change code below to use for Access Database)
Dim insertCommand As DbCommand = db.GetStoredProcCommand("AddProduct")
db.AddInParameter(insertCommand, "ProductName", DbType.String, "ProductName", DataRowVersion.Current)
db.AddInParameter(insertCommand, "CategoryID", DbType.Int32, "CategoryID", DataRowVersion.Current)
db.AddInParameter(insertCommand, "UnitPrice", DbType.Currency, "UnitPrice", DataRowVersion.Current)

Dim deleteCommand As DbCommand = db.GetStoredProcCommand("DeleteProduct")
db.AddInParameter(deleteCommand, "ProductID", DbType.Int32, "ProductID", DataRowVersion.Current)

Dim updateCommand As DbCommand = db.GetStoredProcCommand("UpdateProduct")
db.AddInParameter(updateCommand, "ProductID", DbType.Int32, "ProductID", DataRowVersion.Current)
db.AddInParameter(updateCommand, "ProductName", DbType.String, "ProductName", DataRowVersion.Current)
db.AddInParameter(updateCommand, "LastUpdate", DbType.DateTime, "LastUpdate", DataRowVersion.Current)

db.UpdateDataSet(productsDataSet, "Products", insertCommand, updateCommand, _
deleteCommand, Microsoft.Practices.EnterpriseLibrary.DataAccess.UpdateBehavior.Standard)



Sep 17, 2007 at 2:02 PM
Hi,

Are you facing any particular issue with the walkthrough code?

Fernando
Sep 17, 2007 at 2:34 PM
I understand the walkthrough code from a SQL database point of view, but I want to utlise a access data base instead. I have used the code discussed above to connect the access database (via odbc) and fill a data set. I am having troubles with SQL update / insert / delete commands to update the access data base with data from the dataset:

eg:
Dim db As Database
db = DatabaseFactory.CreateDatabase
Dim dbupdatecommand As DbCommand

StrSQLUpdate="Update AccountType SET Description = @Description WHERE AAHOST = @AA_HOST"
dbupdatecommand = db.GetSqlStringCommand(StrSQLUpdate)
db.AddInParameter(dbupdatecommand, "Description",DbType.String, "Description",DataRowVersion.Default)
db.AddInParameter(dbupdatecommand, "AAHOST", DbType.String, "AAHOST", DataRowVersion.Default)

db.UpdateDataSet(ds, strTableName, Nothing, dbupdatecommand, Nothing, UpdateBehavior.Standard)

Is this possible with the Entlib ? how can this be done?
Sep 17, 2007 at 2:53 PM
Hi,

EntLib's DAAB is really a wrapper on top of ADO.NET, so you will still be constrained to what ADO.NET can do. In this case, as indicated in the previous posts in this thread, named parameters are not supported so you should use '?' parameters.

Fernando


ageau wrote:
I understand the walkthrough code from a SQL database point of view, but I want to utlise a access data base instead. I have used the code discussed above to connect the access database (via odbc) and fill a data set. I am having troubles with SQL update / insert / delete commands to update the access data base with data from the dataset:

eg:
Dim db As Database
db = DatabaseFactory.CreateDatabase
Dim dbupdatecommand As DbCommand

StrSQLUpdate="Update AccountType SET Description = @Description WHERE AAHOST = @AA_HOST"
dbupdatecommand = db.GetSqlStringCommand(StrSQLUpdate)
db.AddInParameter(dbupdatecommand, "Description",DbType.String, "Description",DataRowVersion.Default)
db.AddInParameter(dbupdatecommand, "AAHOST", DbType.String, "AAHOST", DataRowVersion.Default)

db.UpdateDataSet(ds, strTableName, Nothing, dbupdatecommand, Nothing, UpdateBehavior.Standard)

Is this possible with the Entlib ? how can this be done?

Sep 18, 2007 at 12:17 AM
Edited Sep 18, 2007 at 12:17 AM
Thanks fsimonazzi, using the ? for parameters worked: I found this fourm useful with regards to the syntax:
http://www.vbforums.com/showthread.php?p=2958010#post2958010

Named parameters in Microsoft database are any that have a name starting with "@". Other databases have different prefixes. Unnamed parameters are those represented by a "?". SqlClient will accept only named parameters. The SQL Server provider for OleDb will accept only unnamed parameters. The Jet provider for OleDb, which is used for Access, accepts either, but VS will only accept unnamed parameters in the Query Builder.

When you use named parameters it doesn't matter what order they are added because they are identified by name. When you use unnamed parameters the order is critical because the position of the parameters is the only way to identify them. You MUST add the parameters to your OleDbCommand in the same order as they appear in the SQL code. For example, this is correct:
vb.net Code:
myCommand.CommandText = "UPDATE Person SET FirstName = ?, LastName = ? WHERE ID = ?"myCommand.Parameters.Add("FirstName", OleDbType.VarChar, 30, "FirstName")myCommand.Parameters.Add("LastName", OleDbType.VarChar, 30, "LastName")myCommand.Parameters.Add("ID", OleDbType.Integer, 4, "ID") because the parameters are added in the correct order.

This is incorrect:
vb.net Code:
myCommand.CommandText = "UPDATE Person SET FirstName = ?, LastName = ? WHERE ID = ?"myCommand.Parameters.Add("ID", OleDbType.Integer, 4, "ID")myCommand.Parameters.Add("FirstName", OleDbType.VarChar, 30, "FirstName")myCommand.Parameters.Add("LastName", OleDbType.VarChar, 30, "LastName")
Oct 15, 2007 at 2:04 AM
Hello there,

I am trying to utilize the EndLib 1.1 tool to connect to an MS Access database. Not finding a GenericDatabase class there I assume you guys are talking EndLib 2.0, right?

Does anyone know of a way to access MS Access without extending the 1.1 v?

Btw.: I am not really experienced in db programming and will have to use Endlib 1.1 because the customer uses the .NET 1.1 framework. Thus I try not to write any code inside the DAAB that might mess up the whole best practise foundation DAAB ships.

Thank you for your help!