The search for a C# DAL code generator that actually works. Part 1 - the problem.
Besides developers, the database is your company’s greatest asset.
The data in your database, your ability to get at it, shape it, analyse it, report on it, turn it in to understandable meaningful information, is what gives your company competitive advantage.
I have been designing, using, optimising and programming against relational databases for over 20 years. DB2, Oracle, SqlServer, Sybase, Access, and MySql are some of the major relational databases engines that I have worked with, and DAO, ADO, ADO.NET, visual studio dataset designer, nHibernate, CSLA, codesmith, and LINQ are some of the tools and DAL technologies that I have used to get at the those databases.
In all that time, I have been frustrated with all of the above DAL methods of accessing data, for various reasons that I will discuss below.
I found that I was spending too much time stuffing around trying to placate or work around the DAL technology and not enough time just getting the job done.
Developers should spend their time writing the porcelain (the business rules that are specific to solving a business problem that cannot be generated by a tool) and very little time writing and working around with the plumbing (the repeatable monkey code infrastructure that can be generated by a tool).
Let us take the visual studio dataset designer for example.
The visual studio DAL tool is fairly good, but I have several issues with it which have caused me a great considerable frustration .
- For one, it does things one table at a time. This is very cumbersome when one has 100+ tables in a database that needs to be DALd.
- It is also very slow. When I right click on the designer, my pc freezes for several seconds until the context menu pops up. FWIW I have a high spec dev box.
- The generated code does not compile in SQLCLR safe mode.
Why can’t something I generate in visual studio work in SQLCLR safe mode?
Hello Microsoft. Visual studio and Sql Server are both your products.
The problem is that the designer inserts a bunch of delegates and events into the generated code, and events use synchronisation objects, which are forbidden in SQLCLR safe mode, and there is no way to prevent generation of these events every time you touch the designer.
Who actually uses those events anyway?
Am I the only person that thinks that events are overrated and over used.
For app support teams, events are a nightmare to debug.
One needs innate familiarity with the code to visualise the execution flow, even when using a debugger.
In my experience, events have been more trouble than their worth over the years.
- Null checking. The code generated does not use nullable types for some reason, so every time I get or set a property, I have to write code to first test for null, otherwise the dataset code will kindly throw an exception.
I just want to set a property – 1 line of code, not 5, please.
- Also, after your program is complied, you don’t know if or when the underlying database schema has changed. Guess when you find out – runtime!
I want the DAL layer to be in sync with the actual underlying database schema at all times.
If I had a command line interface to the visual studio dataset designer, I could regenerate the DAL layer through my continuous build process and have confidence in the accuracy of my DAL.
- Another annoyance is that sometimes the designer changes column orders, seemingly at random, and a small change in the designer causes illogical reordering in the designer.cs file, making diffs hard to read.
- The typed data adapter classes do not expose their contained SqlCommand objects publicly, so I cannot associate an explicit transaction to them.
The other data access methods don’t do the job for me either:
- CSLA: the version I used was based on XSLT templates – OMG -–enough said.
- nHibernate: getting closer, doesn’t handle my requirements see below.
- LINQ: ditto.
In part 2 of this article, I will outline my requirements for an ideal DAL code generator.
