Ok, so I finally got around to finishing my MSAccess project. The goal was to use the same MyGeneration setup to build a clean-and-tidy DAL from an existing Microsoft Access table instead of a SQL table.
This was far easier than I expected. I overcomplicated it quite a bit thinking field types in Access would be vastly different than they are in SQL. As it turns out, they are but the translation work has already been done for me.
The first challenge was getting MyGeneration to "see" the MSAccess database. That turned out to be fairly easy - just go into File, Default Settings and set up a connection string to the Access db. Then modify the start of the template where the database name is specified and set it to the full filename of the MSAccess database i.e. "joedata.mdb"
The second challenge was getting MyGeneration to identify specific field types in the MSAccess table and make the correct dbTargetType in the generated code. By default, MyGeneration looks like it'll do it - Access is already configured in the DbTargets.xml file. However this translation setup doesn't follow the same Microsoft Enterprise data access style that Able uses. I wanted things consistent.
The solution to the second challenge was to add a new translation section to the DbTargets.xml file:
Code: Select all
<DbTarget From="ACCESS" To="ACDAL">
<Type From="Text" To="String" />
<Type From="Memo" To="String" />
<Type From="DateTime" To="DateTime" />
<Type From="Currency" To="Decimal" />
<Type From="Yes/No" To="Boolean" />
<Type From="OLE Object" To="Binary" />
<Type From="Hyperlink" To="String" />
<Type From="Double" To="Double" />
<Type From="Replication ID" To="AllDbType.Guid" />
<Type From="Long" To="Int32"/>
<Type From="Single" To="Single" />
<Type From="Decimal" To="Decimal" />
<Type From="Byte" To="Byte" />
<Type From="Integer" To="Int32" />
</DbTarget>
Once you've added the above XML to the DbTargets.Xml file, go back into Default Settings and set the database target mapping DbTarget to your new "ACDAL" target type.
Now that I've mapped the MSAccess field types to the data types I want in ASP.Net, the existing MyGeneration template builds a wonderful strongly-typed DAL for my MSAccess table.
Now the third challenge. Able uses Microsoft Enterprise DatabaseFactory to hook into the SQL db for the storefront. When the DAL goes to fetch the data, there's no reference to a connection string or anything. It's all been abstracted and the only thing you'll see in the .Load() method is:
Code: Select all
Dim _Database as Database = Token.Instance.Database
There's nothing here to say "Use the AbleCommerce database", so how am I going to tell the new MSAccess DAL to use my Access database file instead? Modify the ~/App_Data/Database.config file and add your full connection information to your MSAccess database. Like this:
Code: Select all
<add name="JoeSupplies" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\joesupplies.mdb" providerName="System.Data.OleDb"/>
Notice how the Source= value is set so |DataDirectory| is used instead of a physical file path. This makes the connection string work in both a developer environment and a live environment. ASP.Net will populate that part of the path during runtime.
Finally, the fourth challenge. I've got a connection string added to the store for the MSAccess database, but how do I tell the DAL to use it instead of connecting to the AC7 database? Easy as pie - change all the references of
Code: Select all
Dim database as Database = Token.Instance.Database
to
Code: Select all
Dim database As Database = DatabaseFactory.CreateDatabase("JoeSupplies")
And *poof* - all the remaining code aligns without error. Now everything that worked before using a SQL data source now works using a completely different MSAcccess data source. Plus I haven't affected anything with the existing connection to the AC7 database.
The end result is now I have a familiar and standardized data class to retrieve data from the MSAccess database. It's strongly-typed and has all the same methods I'm used to working with when using Able data classes.
So this is what Microsoft means when they talk about "abstracting" the data access layer