C# and MySQL - Gentle Framework alternatives

Question

I'm playing around at the start of a personal project in C# and MySQL.

I am familiar with the use of the Gentle Framework (using MyGeneration to generate the classes, based on the data model). Here's what I like about Gentle;

• Simple-to-use [class].Retrieve(id) / [object].Persist() semantics with strong-typing of fields;
• I start with the DB data model, and choose when to generate new code files;
• MyGeneration allows for some 'manual code sections' which are kept across generations...
• ...and partial classes allow me to add permanent code in parallel files, e.g. simple read-only properties (like 'FullName' from FirstName and Surname members for a Person object) - or I could use inheritance;
• I find it a tolerable and quick way to create a DAL, and add certain Business-Object-Layer-like facilities to it.

Unfortunately, to query efficiently, I end up using queries / SqlCommands a fair bit, and relies on weakly typed references to column names etc., and appears to risk sidestepping the object broker and therefore caching advantages. In any event, Gentle is no longer being developed, and it seems like a good time to consider alternatives.

So, what should I consider?

• Generation of strongly-typed ADO Datasets is possible, but it seems like it will be difficult to add to it (e.g. that 'FullName' virtual column) in a way that will persist after updates to the table structure with regeneration of the dataset.
• NHibernate seems to have lots of fans... but my first looks into it seem to suggest that the XML data definition is king, not the existing data-model in the DB. It also looks quite heavy on dependencies;
• The SubSonic demo appears to suggest it generates files, and in the demo of WebAppProjects, looks like it might generate files in a way that I could add to, or inherit from;
• The MySql Connector.Net tools appear not to support the dataset generation for Linq (e.g. via drag-and-drop), and I suspect that this is a key need for strongly-typed data access.

Your thoughts will be gratefully appreciated! Thank you in advance...

Answer 1

I use a bit of SQL to generate strongly typed objects out of tables, it's based on one built by Cade Bryant, but I've made some tweaks. The code it generates is not 100% compilable but it saves a lot of boiler plate work and the gaps are easy to fill (i would make all the properties fully fledged properties if i were you, or bear the wrath of jon skeet!)

http://NotifyURL.com/sql

Answer 2

link textI would go for Subsonic, mature DAL generator and improves productivity by great margin.

We have used it with both MySQL and SQL Server - no headaches. Generates classes for Tables, Stored procedures, column names. So every time we find ourselves doing Somthing Dot Intellisense Move Arrow keys and semicolon.

Any time your schema changes, you can regenerate those classes and you are home. Also, you can extend them by creating partial classes.

It supports almost all of the SQL Semantics - Joins, loading Collection by primary key, adding WHERE clause, Order by, Count, Top, Calling stored procedures, views and so on and Intuitive syntax is big plus.

To give you some glimpse- For Books table[BookID-PK, title, AuthorID], It generates several types of methods.

• Insert method which takes Title, AuthorID
• Nullable columns are optional parameters a.k.a C# Nullable type ?
• Update method wich takes BookID, AuthorID, Title
• Load Book by Primary key (Useful when displaying detail page)
• BookCollection and Book Entities, Just call BookCollection.Load and you have list of books ready to bind to any databound control

Here's quick link.

Thanks, Maulik Modi

Answer 3

Thanks to both Filip and Snorkpete for your suggestions - your comments and links proved helpful.

I will probably try SubSonic first; it looks like something I will understand and be able to get going with quickly (today should answer that), and I was surprised to see that it is indirectly supported by MS as they employ the guy who writes it. T4 also looks very interesting.

The Entity Relationship Model also looks interesting, and the link to MyDirect may be helpful in the future. The only down side here is one of expectation; MS have screwed-up their approach in the past by making them easy to create the initial design with drag-and-drop, then much harder later to modify or keep up-to-date.

Anyway, thank you both again, and I'll try to keep this question updated.

Nij

Answer 4

Assuming that the .Net 3.5 Framework is an option for being used, then you can take a look at Microsoft's Entity Framework (released with .Net 3.5 Service Pack 1).

The Entity Framework allows the generation of DAL classes based on your database schema, but the maintenance of these classes are hidden behind an XML file that can quickly and easily be updated to account for schema changes by a simple command from the Visual Studio IDE.

I am working on a project where we use the Entity Framework with MySQL with few problems. The main disadvantage to this option is that the official .Net connector provided by MySQL does not yet support the Entity Framework - there is a paid alternative known as MyDirect.Net

Answer 5

I had some experience with Gentle and I do have to admit that it was pretty inefficient with queries. I would suggest looking into NHibernate, since it has a rich community. It is true that XML definitions are preferred, but there are ways of doing the mappings using class-level attributes.

SubSonic (especially the 3.0 version) looks very promising with its use of T4 templates. That should give you more control over code generation. It can do LINQ too.

Don't invest in LINQ-to-SQL, since the rumors are that is going to be discontinued.