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.
sweeperq
Commodore (COMO)
Commodore (COMO)
Posts: 497
Joined: Tue Jan 03, 2006 2:45 pm

Re: Autogenerating Data Access Layer Code

Post by sweeperq » Tue Mar 08, 2011 3:59 pm

Very useful information. I was up and running with MyGeneration in just a few minutes (after I found an older version...the current version 1.3.1.1 doesn't list any database drivers in the configuration). I do have a few questions though:

1. The standard table naming convention for AC is "ac_PluralEntityName". Example: ac_OrderNotes

The templates provided do not seem to address converting plural table names to singular entity names (e.g. OrderNote). Are you manually handling this afterwards, just stripping the trailing "s" off, doing something more complex?

2. The generated code seems to only handle simple properties (int, bool, string, datetime). It does not seem to be taking foreign key retraints into account and wiring up the related AC objects.

Example:
custom_CustomerNotes (int CustomerNoteId, int CustomerId, int UserId, DateTime CreatedDate, nvarchar(MAX) Comment, tinyint NoteTypeId) with FKs for CustomerId and UserId

Are you manually handling this in custom partial classes? Using the example above, would I create a partial class for CustomerNote and manually add User properties for Customer and User?

Code: Select all

public partial class CustomerNote
{
	protected User _Customer;
	public User Customer
	{
		get
		{
			if ((this._Customer == null && this._CustomerId > 0) || (this._Customer != null && this._Customer.UserId != this._CustomerId))
			{
				_Customer = UsersDataSource.Load(this._CustomerId);
			}
			return _Customer;
		}
		set
		{
			this._Customer = value;
			if (this._Customer != null)
			{
				this._CustomerId = this._Customer.UserId;
			}
			else
			{
				this._CustomerId = 0;
			}
		}
	}
}
3. It doesn't appear that you can extend the CommerceBuilder entities with partial classes because the are enclosed in a library. Does that seem correct? So basically we will have to work with any custom entities we create from the custom side of the relationship (as done above)?

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 » Wed Mar 09, 2011 4:24 am

1- Its a long time since I wrote these templates. I am not sure If I did code to make pleural table names singular. Yes you are right about ablecommerce table name convention. But when creating your own tables prefix them with something different lic c_ and also keep singular names.

2- Yes templates don't generate foreign key properties. You can do them manually.
3- Yeah you can't. Partial classes can't be placed into different assemblies. So you can't extend ablecommerce entities via partial classes.

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 » Wed Mar 09, 2011 4:43 am

I am wondering if I get some time some day I will try to sum up with some extended active record templates targeting SubSonic.
http://forums.subsonicproject.com/Download

Subsonic 2.2 is data access layer provider that can emit code using their Active Record based pattern very close to AbleCommerce. Their query tool is very powerful and easy to understand. I have a feel that If I spend some time extending their Active Record templates I can create something very close to our current model that will be more powerful due to their query tools. Use of Subsonic can eliminate a lot of issues related to knowledge of SQL for a normal user trying to do some codes. For example see the difference

SQL+DAL Code

Code: Select all

ProductCollection products = new ProductCollection();
//BUILD QUERY
StringBuilder sb = new StringBuilder();
sb.Append("SELECT * FROM ac_Products WHERE ManufacturerId = 33 ORDER BY ProductId ASC");
Database database = Token.Instance.Database;
DbCommand command = database.GetSqlCommand(sb.ToString());
using(IDataReader dr = database.ExecuteReader(command))
{
while(dr.Read())
{
Product product = new Product();
Product.Load(product,dr);
products.Add(product);
}
dr.close();
}
return products;
SubSonic 2.2 Way

Code: Select all

ProductCollection products = new ProductCollection()
   .Where("ManufacturerId", 33).OrderByAsc("ProductId").Load();
Now you can see which one is easy to understand and requires less effort.

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 Mar 09, 2011 6:57 am

I use the code generator all the time with similar results....

1. You have to handle the plural spellings manually. I modified mine to add/substract the 's' when necessary, but it it doesn't always work ie. if the table name ends with a 'y'. Quick to fix though once you've done a few.

2. FK are always handled in separate partial classes in able's code, so I follow their convention. Sure you could add your custom code to the generated file, but it works better if you keep pure-generated separate from custom functionality. That way any change to the table means you just regen and make a simple copy/paste of generated code....no hunting for custom stuff because it's always in a separate file.

3. The code generator is slick when it works, a major pain when it won't generate - error messages are mostly worthless.

All in all though, it's a huge time saver and forces you to design the proper way which is from the table level up. If you know your modification design well enough to lay the table definition out first, you know it pretty well........
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

sweeperq
Commodore (COMO)
Commodore (COMO)
Posts: 497
Joined: Tue Jan 03, 2006 2:45 pm

Re: Autogenerating Data Access Layer Code

Post by sweeperq » Wed Mar 09, 2011 8:43 am

Thanks for the replies. I've used SubSonic 2.0.3 on projects in the past and was pleased with how it worked once I was up and running. I wouldn't waste too much time with it though...I'd still be hamstrung by the exact same issues I face with MyGeneration (with the exception of plural and singular case). And while the SQL + DAL code is indeed much more verbose, it doesn't require us to add additional unsupported binaries to the projects.

I did pretty much what I outlined above:
  • Created my custom table: wd_CustomerNotes
  • Ran the MyGeneration templates on the custom table
  • Modified the generated code for singular case (will be updating templates to handle this step for me)
  • Dropped generated classes in App_Code/CustomDAL/Generated
  • Created partial classes for custom code such as FK entity relationships and additional properties/methods, then put them in App_Code/CustomDAL/Custom
  • Happily utilize the classes in my code

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 » Wed Mar 09, 2011 8:55 am

Actually SubSonic 2.2 and is based around Microsoft Enterprise Library which we already use in AbleCommerce. So the only new DLL will be SubSonic.Core it self. As I said in my previous comment SubSonic has Active Record templates but they need some more love to be more friendly with AbleCommerce. For example they do generate classes to bind using Object Data sources but do have controller names instead of datasources. Secondly they don't expose any load methods for foreign keys which we normally need. Lastly their entity object needs to be altered to have behave like AbleCommerce objects with same standard methods.

Post Reply