Autogenerating Data Access Layer Code

This forum is where we'll mirror posts that are of value to the community so they may be more easily found.
User avatar
mazhar
Master Yoda
Master Yoda
Posts: 5084
Joined: Wed Jul 09, 2008 8:21 am
Contact:

Autogenerating Data Access Layer Code

Post by mazhar » Mon Jan 19, 2009 7:53 am

Here are the DAL Code Templates for MyGeneration template based code generator. The package contains three template files and one XML type mapping file. You can download the mygeneration_1309_20081006.exe from following link
http://sourceforge.net/project/showfile ... _id=249524. You need .NET 3.5 in order to run MyGeneration. After installing the MyGeneration you need to provide the database connection settings. Here is connection string that worked for me

Code: Select all

Provider=SQLOLEDB.1;Persist Security Info=False;User ID=userid;Password=password;Data Source=MAZHAR\SQLEXPRESS
You can see the screen capture for connection dialog below.

Now locate the MyGeneration's installation folder and replace the DbTargets.xml file under Settings folder with the one available with templates. This will adjust the target types for output code.

Now finally its time to have some talk about code templates. The Able's code pattern out puts a class for each table that encapsulates the persistence logic, one collection class for that object and a loader class. There is one template for each of the above three classes I mentioned.

MG-ItemTemplate.csgen :- DataObject
MG-ItemCollectionTemplate.csgen :- Collection
MG-ItemDatasourceTemplate.csgen :- Loader Class

You have to run these three templates on desired table to achieve the complete code.

From the File menu open all templates one by one. In all these three templates you will found the following two statements

Code: Select all

string tableprefix = "custom_";
IDatabase iDatabase = MyMeta.Databases["ac7stable"];
you can change the tableprefix value to your own prefix so that code for only custom tables will be generated. Similarly in second line you will have to specify your database name.

Now after making these changes run each template and copy the generated code from output tab and save it to your files. You can also adjust the namespace value in the template by just changing the namespace in template.

TIP: As .NET 2.0 supports partial classes means you can write a single class in more then one file by using partial key word. The templates makes use of this feature. When saving better save generated code in some different file and your manually written code in some different file. In this way you will not lose work when you regenerate the code.

User avatar
AbleMods
Master Yoda
Master Yoda
Posts: 5170
Joined: Wed Sep 26, 2007 5:47 am
Location: Fort Myers, Florida USA

Re: Autogenerating Data Access Layer Code

Post by AbleMods » Thu Feb 19, 2009 4:17 pm

Holy cow this makes data class generation easy!
Joe Payne
AbleCommerce Custom Programming and Modules http://www.AbleMods.com/
AbleCommerce Hosting http://www.AbleModsHosting.com/
Precise Fishing and Hunting Time Tables http://www.Solunar.com

User avatar
AbleMods
Master Yoda
Master Yoda
Posts: 5170
Joined: Wed Sep 26, 2007 5:47 am
Location: Fort Myers, Florida USA

Re: Autogenerating Data Access Layer Code

Post by AbleMods » Thu Feb 19, 2009 4:18 pm

Hey how do you make it generate VB code?
Joe Payne
AbleCommerce Custom Programming and Modules http://www.AbleMods.com/
AbleCommerce Hosting http://www.AbleModsHosting.com/
Precise Fishing and Hunting Time Tables http://www.Solunar.com

User avatar
jmestep
AbleCommerce Angel
Posts: 8164
Joined: Sun Feb 29, 2004 8:04 pm
Location: Dayton, OH
Contact:

Re: Autogenerating Data Access Layer Code

Post by jmestep » Thu Feb 19, 2009 6:08 pm

There is a little bit in the help files.
DotNetScript Engine
Using the DotNetScript scripting engine, templates can currently be written in two different languages: C# and VB.Net.
Language Reference
Below are the languages and links to thier respective online references. Language Help File Online Reference
C# ms-help://MS.NETFrameworkSDKv1.1/csref/html/vcoriCProgrammersReference.htm http://msdn.microsoft.com/.../vcoriCPro ... erence.asp
VB.Net ms-help://MS.NETFrameworkSDKv1.1/vblr7net/html/vaconProgrammingWithVBNET.htm http://msdn.microsoft.com/.../vcoriCPro ... erence.asp
Judy Estep
Web Developer
jestep@web2market.com
http://www.web2market.com
708-653-3100 x209
New search report plugin for business intelligence:
http://www.web2market.com/Search-Report ... -P154.aspx

User avatar
AbleMods
Master Yoda
Master Yoda
Posts: 5170
Joined: Wed Sep 26, 2007 5:47 am
Location: Fort Myers, Florida USA

Re: Autogenerating Data Access Layer Code

Post by AbleMods » Thu Feb 19, 2009 7:57 pm

Awww man you have write all new templates to get it in VB......

Hey mazhar how's your supply of pepsi?
Joe Payne
AbleCommerce Custom Programming and Modules http://www.AbleMods.com/
AbleCommerce Hosting http://www.AbleModsHosting.com/
Precise Fishing and Hunting Time Tables http://www.Solunar.com

User avatar
mazhar
Master Yoda
Master Yoda
Posts: 5084
Joined: Wed Jul 09, 2008 8:21 am
Contact:

Re: Autogenerating Data Access Layer Code

Post by mazhar » Fri Feb 20, 2009 5:31 am

SolunarServices wrote:Awww man you have write all new templates to get it in VB......

Hey mazhar how's your supply of pepsi?
:D
Well Joe I think instead of writing a whole new template in VB.NET what about using some C# to VB code converter. There is an open source .NET IDE project named SharpDevelop. That IDE do support C# to VB.NET conversion. You can find it here
http://www.icsharpcode.net/OpenSource/SD/Default.aspx
Once you got it Installed you can open your C# code file in it and then trigger the command to convert it in VB.NET. Check the screen captures.

User avatar
AbleMods
Master Yoda
Master Yoda
Posts: 5170
Joined: Wed Sep 26, 2007 5:47 am
Location: Fort Myers, Florida USA

Re: Autogenerating Data Access Layer Code

Post by AbleMods » Fri Feb 20, 2009 6:56 am

Good lord, I didn't even think of just converting the generated code :oops:

Thanks :)
Joe Payne
AbleCommerce Custom Programming and Modules http://www.AbleMods.com/
AbleCommerce Hosting http://www.AbleModsHosting.com/
Precise Fishing and Hunting Time Tables http://www.Solunar.com

User avatar
AbleMods
Master Yoda
Master Yoda
Posts: 5170
Joined: Wed Sep 26, 2007 5:47 am
Location: Fort Myers, Florida USA

Re: Autogenerating Data Access Layer Code

Post by AbleMods » Fri Feb 20, 2009 9:50 am

Hmm interesting issue converting from C# to VB...

When interface is implemented on a class, I have to specifically mark the required functions as implementing that interface.

For example

Code: Select all

        Public Overridable Function Delete() As Boolean Implements CommerceBuilder.Common.IPersistable.Delete
        Public Overridable Function Save() As SaveResult Implements CommerceBuilder.Common.IPersistable.Save
        Public Property IsDirty() As Boolean Implements CommerceBuilder.Common.IPersistable.IsDirty
However in C# this does not appear necessary.

So when I convert the C# code to VB, the specific function implements don't get added and intellisense throws an error on the class Implements line.

No biggie to modify, but a pain nonetheless because I'll have to modify the VB version every time I re-generate the C# class file.

Now the bigger question would be, could I convert the source templates to VB, make the necessary template changes and then the generator would do it all and the end result is entirely VB?
Joe Payne
AbleCommerce Custom Programming and Modules http://www.AbleMods.com/
AbleCommerce Hosting http://www.AbleModsHosting.com/
Precise Fishing and Hunting Time Tables http://www.Solunar.com

User avatar
mazhar
Master Yoda
Master Yoda
Posts: 5084
Joined: Wed Jul 09, 2008 8:21 am
Contact:

Re: Autogenerating Data Access Layer Code

Post by mazhar » Fri Feb 20, 2009 10:19 am

Yes you can. In fact when you open the templates with MyGeneration IDE then you will see two portions of code either with bluish color or with white. The white portions are just like static text they will be outputted for each database table. The code portions in blue are in fact the code that generates the code in turn. So in order to make template out VB code it would be better that you create C# output file using the template then convert the code to VB.NET using SharpDevelp and remove any error if found. Now create a copy of template and start editing it. You can replace the static code blocks with the VB version which you already have in the form of converted code. Most of the dynamic code i wrote is either outputting the parameters or types. So most probably all you need to change in dynamic code will be to adjust the parameters list to follow the VB syntax. Check the screen captures

User avatar
AbleMods
Master Yoda
Master Yoda
Posts: 5170
Joined: Wed Sep 26, 2007 5:47 am
Location: Fort Myers, Florida USA

Re: Autogenerating Data Access Layer Code

Post by AbleMods » Fri Feb 20, 2009 10:50 am

Yup - seeing that route now.

The dynamic portions of the template would remain the same. I basically would have to create all the static stuff in VB and make all new templates.

Since I already have a complete example of how templates work in C# and a working version of a custom data class in VB, it's just the time involved to get it built. I'll play with it some more this weekend.

I've added some more to the DataSource template. Now it generates all the LoadforStore() and CountforStore() methods along with the ones you had built. I tend to use those often in my code.

Once I've played with it for a while, I'll upload my revised templates so others can benefit.
Joe Payne
AbleCommerce Custom Programming and Modules http://www.AbleMods.com/
AbleCommerce Hosting http://www.AbleModsHosting.com/
Precise Fishing and Hunting Time Tables http://www.Solunar.com

User avatar
AbleMods
Master Yoda
Master Yoda
Posts: 5170
Joined: Wed Sep 26, 2007 5:47 am
Location: Fort Myers, Florida USA

Re: Autogenerating Data Access Layer Code

Post by AbleMods » Fri Feb 20, 2009 10:57 am

For reference purposes, it took me 3-4 hours of coding and troubleshooting to build my first able-style dataclass design. Much of that time was the learning curve asssociated with data classes.

My most recent hand-coded data class took around 2 hours to completely set up. That was done by copying an existing one and changing all the field names, get/set property names etc. This was a poor route to take as I spent the majority of time debugging changes that were missed, typos etc.

This generator program does all of that 2 hours work in about 8 seconds.

Nice :D
Joe Payne
AbleCommerce Custom Programming and Modules http://www.AbleMods.com/
AbleCommerce Hosting http://www.AbleModsHosting.com/
Precise Fishing and Hunting Time Tables http://www.Solunar.com

User avatar
mazhar
Master Yoda
Master Yoda
Posts: 5084
Joined: Wed Jul 09, 2008 8:21 am
Contact:

Re: Autogenerating Data Access Layer Code

Post by mazhar » Fri Feb 20, 2009 11:00 am

sounds great!

User avatar
AbleMods
Master Yoda
Master Yoda
Posts: 5170
Joined: Wed Sep 26, 2007 5:47 am
Location: Fort Myers, Florida USA

Re: Autogenerating Data Access Layer Code

Post by AbleMods » Fri Feb 20, 2009 3:43 pm

Hey this is kinda fun to play with - almost a game trying to see how close you can get the template to zero modifications after creation.

I figured out how to get it to prompt for values that can be used in the template. Now I can specify the name of the class ahead of time so the final code is a simple copy-paste.

Mazhar you must have put some serious time into the object template - there's a lot of translation in there.
Joe Payne
AbleCommerce Custom Programming and Modules http://www.AbleMods.com/
AbleCommerce Hosting http://www.AbleModsHosting.com/
Precise Fishing and Hunting Time Tables http://www.Solunar.com

User avatar
AbleMods
Master Yoda
Master Yoda
Posts: 5170
Joined: Wed Sep 26, 2007 5:47 am
Location: Fort Myers, Florida USA

Re: Autogenerating Data Access Layer Code

Post by AbleMods » Sun Mar 01, 2009 11:54 am

Hey Mazhar, I'm almost done with a solid VB template set, will be uploading it soon.

However I've run into a data class/type problem. When a table has any DateTime fields in it, they need to be created with .Net property types of Nullable(Of DateTime) instead of just DateTime. Otherwise SQL throws an exception when the datetime property is written to the table. You can get around it by forcing some sort of initial value in the .New() constructor, but I like how Able code allows an unassigned date value to be written out as Null to the SQL table.

Any suggestions on how to put an IF statement in the generator language - I need to test for the column data type. If the SQL column datatype is DateTime, the generator needs to output Nullable(of DateTime) instead of just DateTime. Or at least ADD the nullable code part...
Joe Payne
AbleCommerce Custom Programming and Modules http://www.AbleMods.com/
AbleCommerce Hosting http://www.AbleModsHosting.com/
Precise Fishing and Hunting Time Tables http://www.Solunar.com

User avatar
mazhar
Master Yoda
Master Yoda
Posts: 5084
Joined: Wed Jul 09, 2008 8:21 am
Contact:

Re: Autogenerating Data Access Layer Code

Post by mazhar » Mon Mar 02, 2009 6:38 am

This case is already handled in the LoadReader method defined in template. Check the screen capture

User avatar
jmestep
AbleCommerce Angel
Posts: 8164
Joined: Sun Feb 29, 2004 8:04 pm
Location: Dayton, OH
Contact:

Re: Autogenerating Data Access Layer Code

Post by jmestep » Fri Mar 20, 2009 2:49 pm

For Able build 7.0.2, we should use CommerceBuilder.Dat instead of Microsoft.Practices.EnterpriseLibrary.Data, shouldn't we?
I just used the templates for the first time. What a time saver! It was good to create them the hard way earlier so that I could get more familiar with the errors I would be running across and what was going on, but this is great.
Judy Estep
Web Developer
jestep@web2market.com
http://www.web2market.com
708-653-3100 x209
New search report plugin for business intelligence:
http://www.web2market.com/Search-Report ... -P154.aspx

User avatar
AbleMods
Master Yoda
Master Yoda
Posts: 5170
Joined: Wed Sep 26, 2007 5:47 am
Location: Fort Myers, Florida USA

Re: Autogenerating Data Access Layer Code

Post by AbleMods » Fri Mar 20, 2009 10:45 pm

jmestep wrote:For Able build 7.0.2, we should use CommerceBuilder.Dat instead of Microsoft.Practices.EnterpriseLibrary.Data, shouldn't we?
I just used the templates for the first time. What a time saver! It was good to create them the hard way earlier so that I could get more familiar with the errors I would be running across and what was going on, but this is great.
I tried the other day and Intellisense threw an error - there's not enough in CommerceBuilder.Data to fully replace an import of of Microsoft.Practices.EnterpriseLibrary.Data so far as I can tell.

Maybe I didn't do it right /shrug.

This weekend I'm working on new templates that detect a StoreId field - if found, the template automatically adds the appropriate methods for .LoadforStore() and modifies the existing methods/classes to accomodate for StoreId.
Joe Payne
AbleCommerce Custom Programming and Modules http://www.AbleMods.com/
AbleCommerce Hosting http://www.AbleModsHosting.com/
Precise Fishing and Hunting Time Tables http://www.Solunar.com

User avatar
AbleMods
Master Yoda
Master Yoda
Posts: 5170
Joined: Wed Sep 26, 2007 5:47 am
Location: Fort Myers, Florida USA

Re: Autogenerating Data Access Layer Code

Post by AbleMods » Wed Apr 15, 2009 8:43 pm

Ok new challenge....

Let's say you have an external MS Access database with one table in it...

Couldn't you use this same generator to make the exact same DAL setup we're all used to, only it's pulling data from an MS Access table instead of a SQL table?

I'm playing with the idea now, but I'm running into a wall. The generator wants a "database target mapping", however none of the defaults from the dbtargets.xml seem to work for .Net.

Thoughts? I don't think I've ever used .Net 2.0 to build a strongly-typed dataset from an Access table. So I'm not sure what type-mappings to use in the dbtargets.xml file for translation.......
Joe Payne
AbleCommerce Custom Programming and Modules http://www.AbleMods.com/
AbleCommerce Hosting http://www.AbleModsHosting.com/
Precise Fishing and Hunting Time Tables http://www.Solunar.com

User avatar
mazhar
Master Yoda
Master Yoda
Posts: 5084
Joined: Wed Jul 09, 2008 8:21 am
Contact:

Re: Autogenerating Data Access Layer Code

Post by mazhar » Thu Apr 16, 2009 4:57 am

There should be MS Access type mapping file. Once you connect MyGen to MS Access DB try to check mapping dialog some where in GUI or you can directly edit its XML file. Once you find it then you can check MSDN for VB.NET and MS Access type mappings. I my self used some type mapping table from MSDN, I forgot its link but you can Google to find out something similar. I hopefully things will be quite same for both.

User avatar
AbleMods
Master Yoda
Master Yoda
Posts: 5170
Joined: Wed Sep 26, 2007 5:47 am
Location: Fort Myers, Florida USA

Re: Autogenerating Data Access Layer Code

Post by AbleMods » Sun Aug 30, 2009 6:33 am

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 :wink:
Joe Payne
AbleCommerce Custom Programming and Modules http://www.AbleMods.com/
AbleCommerce Hosting http://www.AbleModsHosting.com/
Precise Fishing and Hunting Time Tables http://www.Solunar.com

sdf
Ensign (ENS)
Ensign (ENS)
Posts: 10
Joined: Wed May 26, 2010 1:55 pm

Re: Autogenerating Data Access Layer Code

Post by sdf » Tue Sep 07, 2010 11:54 am

Are these templates still valid for the current source code? I would assume yes. Thanks.

User avatar
AbleMods
Master Yoda
Master Yoda
Posts: 5170
Joined: Wed Sep 26, 2007 5:47 am
Location: Fort Myers, Florida USA

Re: Autogenerating Data Access Layer Code

Post by AbleMods » Tue Sep 07, 2010 1:43 pm

Yes. However, these templates are for new data classes - they do not replace the existing CommerceBuilder API data classes.
Joe Payne
AbleCommerce Custom Programming and Modules http://www.AbleMods.com/
AbleCommerce Hosting http://www.AbleModsHosting.com/
Precise Fishing and Hunting Time Tables http://www.Solunar.com

mike92117
Lieutenant (LT)
Lieutenant (LT)
Posts: 64
Joined: Sat Nov 07, 2009 6:41 pm

Re: Autogenerating Data Access Layer Code

Post by mike92117 » Sat Oct 30, 2010 4:25 pm

I'm looking for a little guidance. I have been using EntitySpaces since its release and I notice your DAL seems to be based on that (EntitySpaces.plugin reference). What I'm looking to do is customizations mostly with newly created tables but I need to use business logic that joins existing tables (Orders, customers, etc.). The usual EntitySpaces output includes several ES assemblies (e.g, EntitySpaces.Core.dll, EntitySpaces.Interfaces.dll, EntitySpaces.Loader.dll, etc.). And the ES templates create 1 generated file and 3 custom files (entity, collection, query). It looks like your templates generate gross output that you cut/paste to files of your own construction. At least that is what it looks like after playing with this for an hour or so.

I'm trying to work out which is a better way for me to go for customizations I need (which include scheduled tasks for pulling out new orders, moving them to a warehouse, reformatting for EDI transactions, etc.). I'm very familiar with the ES DAL. I'm not too familiar with the best practice or work flow for using your DAL. For example, do I simply create the CS files from the output, then add these to the App_Code folder? Is that the recommended approach rather than compiling to an assembly?


User avatar
AbleMods
Master Yoda
Master Yoda
Posts: 5170
Joined: Wed Sep 26, 2007 5:47 am
Location: Fort Myers, Florida USA

Re: Autogenerating Data Access Layer Code

Post by AbleMods » Mon Nov 01, 2010 8:23 am

mike92117 wrote:For example, do I simply create the CS files from the output, then add these to the App_Code folder? Is that the recommended approach rather than compiling to an assembly?
It's really up to you as to how you deploy. When I have a small project (i.e. 1 table DAL), generally I toss the class files into App_Code folder and move on. It's quick and simple and easily identified if you name it well.

But when I have multiple tables, generally the project is more complex. It's far easier for me to compile and deploy a single DLL. That way I'm not fighting version differences on many different files. Plus the Namespace organization helps keeps my functionality grouped in a logical way.

Very few (if any) generators are going to be able to produce all of your business logic. Since most every project I do is unique in nature, I keep business logic out of the generators. It just isn't going to be the same every time, so it's easier to manage it by writing the unique stuff separately instead of chasing down weird bugs after code is generation.
But again, if your business logic is consistent across the board, then by all means leverage the generator and add it to the .Generated.cs files as needed.

I like how Able organizes the code. Putting the custom business logic in ac_Order.cs and leaving the generated stuff in ac_Order.Generated.cs leaves you free to regenerate the base class at-will should the SQL table require a last minute field definition change.
Joe Payne
AbleCommerce Custom Programming and Modules http://www.AbleMods.com/
AbleCommerce Hosting http://www.AbleModsHosting.com/
Precise Fishing and Hunting Time Tables http://www.Solunar.com

Post Reply