C # and MySQL variants - alternatives
I am playing at the beginning of a personal project in C # and MySQL.
I am familiar with using the Gentle Framework (using MyGeneration to generate classes based on a data model). That's what I like about Gentle;
- Easy-to-use semantics [class] .Retrieve (id) / [object] .Persist () with strong field typing;
- I start with the DB data model and choose when to create new code files;
- MyGeneration allows you to use some of the "manual code sections" that are stored across generations ...
- ... and partial classes allow me to add persistent code to parallel files eg. simple readable properties (for example, "FullName" from FirstName and Surname for Person) - or I could use inheritance;
- I find it a portable and fast way to create a DAL and add certain Business-Object-Layer type objects to it.
Unfortunately, for an efficient query, I end up using queries / SqlCommands fair bit and rely on weakly typed references to column names etc. and seems to run the risk of bypassing the object broker and therefore taking advantage of caching. In any case, Gentle is no longer under development and it seems like a good time to consider alternatives.
So what should I consider?
- It is possible to create ADO integer datasets, but it looks like it will be difficult to add to it (eg virtual column "FullName") that will persist after refreshing the table structure with regenerating dataset.
- NHibernate seems to have a lot of fans ... but it seems to me that XML data definition is king, not the existing data model in the DB. It also looks pretty heavy on dependencies;
- The SubSonic demo assumes that it generates files, and the WebAppProjects demo looks like it can generate files in a way that I can add or inherit from;
- The MySql Connector.Net utilities don't seem to support generating dataset for Linq (e.g. via drag-and-drop), and I suspect this is a key need for strongly typed data access.
Your thoughts will be greatly appreciated! Thanks in advance...
source to share
link text I would like to use Subsonic, a mature DAL generator, and improve performance by a wide margin.
We've used it with both MySQL and SQL Server - no headaches. Creates classes for tables, stored procedures, column names. So every time we find ourselves doing Somthing Dot Intellisense Move Arrow keys and semicolon.
When you change your schema, you can restore these classes and you are at home. Alternatively, you can extend them by creating partial classes.
It supports almost all SQL semantics - joins, loads an assembly by primary key, adds a WHERE clause, orders, counts, vertex, calls stored procedures, views, etc., and the intuitive syntax is a big plus.
To give you a little look at the book table [BookID-PK, title, AuthorID], it generates several types of methods.
- Insert method that accepts Title, AuthorID
- Optional columns are optional Parameters aka C # Nullable type?
- An update method that takes BookID, AuthorID, Title
- Download book by master key (useful when displaying a detailed page)
- BookCollection and Book Entities, just call BookCollection.Load and you have a list of books ready to be bound to any file bound control.
Here's a quick link.
Thanks, Maulik Modi
source to share
I've had some experience with Gentle and I have to admit it was pretty inefficient with queries. I would suggest looking into NHibernate as it has a rich community. It is true that XML definitions are preferred, but there are ways to do the mappings using class-level attributes.
SubSonic (especially version 3.0) looks very promising with its T4 . This should give you more control over your code generation. It can do LINQ as well.
Don't invest in LINQ-to-SQL as rumors that this will be phased out.
source to share
Assuming the .Net 3.5 Framework is the option to use, you can have a look at Microsoft Entity Framework (released with .Net 3.5 SP1).
The Entity Framework allows you to create DAL classes based on your database schema, but the maintenance of those classes is hidden behind an XML file that can be quickly and easily updated to reflect schema changes using a simple Visual Studio IDE command.
I am working on a project where we are using Entity Framework with MySQL with little problems. The main disadvantage of this option is that the official .Net connector provided by MySQL does not yet support Entity Framework - there is a paid alternative known as MyDirect.Net
source to share
Thanks to Filip and Snorkpete for your suggestions - your comments and links were helpful.
I'll probably try SubSonic first; it looks like something I will understand and be able to get started quickly (this should be the answer today) and I was surprised to see that this is indirectly supported by MS as they use the guy who writes it. The T4 also looks very interesting.
The entity relationship model looks interesting as well, and the reference to MyDirect might be useful in the future. The only downside here is one of the expectations; MS have been screwing up their approach lately, making it easier to create an initial design using drag-and-drop and then much more difficult to modify or maintain a modern one.
Anyway, thanks to both of you and I'll try to keep this question updated.
Nij
source to share
I use a bit of SQL to create strongly typed objects from tables based on one created by Cade Bryant, but I did some tweaking. The code it generates doesn't compile 100%, but it saves a lot of boilerplate work and the gaps are easily filled in (I would make all properties full properties if I were you, or bear the wrath of jon skeet!)
source to share