Browse > Home / Tools / The search for a C# DAL code generator that actually works. Part 2 - the requirements.

The search for a C# DAL code generator that actually works. Part 2 - the requirements.


Now that I have outlined the reasons why current DAL tools do not satisfy me, I will list my requirements for the ideal DAL code gen tool.

The tool should:

  • Generate the DAL for the whole database in one hit, not table by table, generating all non-system tables and views out of the box.
  • Have a simple pleasant configuration file experience.
    Ideally, the minimum configuration required would be a connection string to the database, and a directory path to which files are generated.
  • Be able to support the addition of custom query objects and custom criteria clauses, and to do so in an easy manner.
  • Create the DAL in a separate dll, so that the source code of my business rules layer contains only business rules code.
  • Be a command line tool, so I can run it as part of my build process, and via cruise control, regenerating the DAL every night and on-demand during the day, keeping it in synchronised with the underlying database schema.
  • Create objects that do not hold any database locks.
    Therefore all objects returned from the DAL are to be disconnected.
  • Be designed with concurrency in mind from the ground up.
    No joke, the vast majority of systems that I have encountered over the last two decades have not taken concurrency into account.
    Consider this scenario: Alice and Bob both read row A from a table in the database.
    Alice modifies her copy of row A and saves it back to the database.
    Bob then changes his copy and successfully saves it back.
    Congratulations, your database is now corrupt.
    This is incorrect behaviour, as Bob should have received a concurrency exception when he attempted to save his stale row.
    The DAL tool should support this out of the box.
    This is plumbing - the programmer should not have to worry about it.
    Concurrency handling is especially important now that we live a disconnected data set world.
  • Uses nullable types. I do not want to explicitly check for nulls before I get or set a field.
    The tool should do it.
  • Not use events.
  • Be designed with database transactions in mind from the ground up, and should work with implicit transactions via TransactionScope, or explicit BYO transactions.
  • Generate clean, understandable code that is easy to follow and debug.
  • Generate deterministic predictable code, so that small schema changes do not cause large diffs.
  • Use the referential integrity of the database itself to automatically generate accessor methods on objects that allow you to walk the database tree.
    These accessors to parent and child objects are to be lazy populated, ie when the accessor is invoked..
  • Have a simple object model using the factory pattern.
    TRow : RowBase , List<TRow>, TFactory: FactoryBase.
  • At buildmasters, we have a very strong test ethic.
    Even generated code needs to be tested.
    The tool should auto generate unit tests to code cover the generated DAL code.
    The unit tests should perform the following tasks in one database transaction:

    • Insert a row into every table, populating each field, read the rows back, and assert the row counts and field values.
    • Update the inserted rows, changing the values of each field, read the rows back, and assert the row counts and field values.
    • Delete the inserted rows, and assert the row counts.
    • Rollback the transaction [unit tests should leave no footprint in the database].
  • Have the following persistance patterns:
    1. Successfully persisting a row object invalidates the object, so it cannot be saved again, and returns the latest version of the row in a new object.
    2. Persisting an unmodified row object does nothing.
    3. If a table does not have a primary key, its row objects cannot to be persisted.
      This is a classic case of bad database design. Every table needs a primary key.
    4. A standard persistance mode that follows the rules of database integrity:
      A new memory row object can only be inserted, not updated, based on primary key.
      A row object that is read from the database can only be updated or deleted, based on primary key and concurrency cookie, and only if the concurrency cookie is still valid.
    5. An ETL persistence mode:
      Persisting a row object involves trying an update, and failing that, an insert is attempted, all based on primary key.
      The concurrency flag is ignored.
      This mode of operation is used in batch ETL type tasks where you just want to transfer or inject a whole bunch of rows in to a table, whether that table is in the same database or not, and maybe on a different connection.
  • Provide a DAL wrapper around legacy stored procedures.
    Im not a big fan of TSQL [see my post here ].
    I have seen many instances of whole systems that have been written in TSQL, where the business rules have been buried away in unmaintainable several-thousand-line TSQL stored procs, as opposed to having the business rules in a proper SOA/business tier written in a high level language like C#.
    It is a pain to debug TSQL and it does not scale.
    But sometimes you will inherit such a mess, so at least the DAL tool can hide it behind a nice C# interface.

In part 3 of this article, I will outline a tool that meets these requirements.