Browse > Home / Code Generation / The search for a C# DAL code generator that actually works. Part 3: the solution.

The search for a C# DAL code generator that actually works. Part 3: the solution.

September 4, 2008

In part 2 of this series, I outlined my requirements for an ideal DAL generator.

Well, not having found any tool out there that meets my criteria, I ended up writing the tool myself, meeting all but one of my objectives [I dont handle stored procedures yet].
 
Sql Server 2005 is the target DBMS for now, using VS2005 as the compiler.

Lets walk through how to use the tool.

  • From the command line, the tool is invoked like this.
mycodegentool.exe config.xml


  • The simplest config.xml file is
<CodeGen>
 <Directory>D:\development\codegenExample\myDatabase</Directory>
 <ConnectionString>Data Source=.;Initial Catalog=myDatabase;
    Integrated Security=SSPI;</ConnectionString>
 <ConcurrencyColumn>update_date</ConcurrencyColumn>
<CodeGen>


This will give you a DAL dll with every table and view of the database in it, and handle concurrency for any table that that has an update_date field.


  • To get a unit test dll as well, add this xml fragment to the config file.
<UnitTest>
 <Directory>D:\development\codegenExample\myDatabase\CodeGenUnitTest</Directory>
</UnitTest>


This takes care of the plumbing. Now reference the DAL dll from your business layer dll, and happy porcelain coding.

Here are a few use case scenarios in actually using the DAL from your business layer.

  • Load up a table.
ProductsFactory factory = new ProductsFactory();
List rows = factory.LoadAll( connection ) ;
Console.WriteLine( rows.Count ) ;


  • Quicker way to see have many rows in a table.
ProductsFactory factory = new ProductsFactory();
int rowcount = factory.GetRowCount( connection ) ;


  • Load up a specific row from a table using the primary key.
int id = 17 ;
ProductsFactory factory = new ProductsFactory();
Products product = factory.LoadByPrimaryKey( connection, id ) ;


  • Load up a set of rows from a table based on custom search criteria.
bool discontinued = true ;
ProductsFactory factory = new ProductsFactory();
List rows = factory.LoadByDiscontinued( connection, discontinued ) ;


To add custom search criteria to a table or view, add this xml fragment to the config file.


<Tables>
 <Table Class="Products" >
  <Methods>
   <Method Name="LoadByDiscontinued" where="LoadByDiscontinued.sql">
     <Parameters>
      <Parameter Name="discontinued" Type="bool?" />
     </Parameters>
    </Method>
  </Methods>
 </Table>
<Tables>


where the contents of the sql file are


where discontinued = @discontinued order by productname


  • Get a row’s parent row.
Here we are walking the employee hierarchary using ootb generated methods - cool.


EmployeesFactory factory = new EmployeesFactory();

// robert - the pleb
int? employeeId = 7;
Employees robert = factory.LoadByPrimaryKey( connection, employeeId);
Assert.AreEqual( robert.FirstName, "Robert" ) ;

// robert reports to steven
Employees steven = robert.parentEmployeesByReportsTo( connection ) ;
Assert.AreEqual(steven.FirstName, "Steven");

// steven reports to andrew
Employees andy = steven.parentEmployeesByReportsTo( connection);
Assert.AreEqual(andy.FirstName, "Andrew");

// andrew is the big cheese
Employees nevilleNobody = andy.parentEmployeesByReportsTo(connection);
Assert.IsNull(nevilleNobody);


  • Get a row’s children rows.
Here we start with an order, then go up to get its customer, then go down to get all the orders of this customer - powerful pattern in a few lines of code.


OrdersFactory factory = new OrdersFactory();
int orderId = 10289 ;

// get an order
Orders order = factory.LoadByPrimaryKey( connection, orderId);

// the customer for this order
Customers customer = order.parentCustomersByCustomerID( connection) ;

// all the orders of this customer
List orderList = customer.childOrdersByCustomerID( connection ) ;


  • Load up from a view.
Same as loading from a table.


Alphabetical_list_of_productsFactory factory = new Alphabetical_list_of_productsFactory();
List rows = factory.LoadAll( connection ) ;


  • Load up from a custom query.
Same as loading from a table, with some extra config.


CustomerOrderFactory factory = new CustomerOrderFactory();
List rows = factory.LoadByRegion( connection, "Quebec" );
List rows2 = factory.LoadByCustomer( connection, "quick" ) ;


The config in this case would be

<Queries>
 <Query Class="CustomerOrder" CodeFile="CustomerOrder.cs" Select="CustomerOrder.sql" >
  <Methods>
   <Method Name="LoadByRegion" Where="LoadByRegion.sql">
    <Parameters>
     <Parameter Name="region" Type="string" />
   </Parameters>
  </Method>
 </Methods>
  <Method Name="LoadByCustomer" Where="LoadByCustomer.sql">
   <Parameters>
    <Parameter Name="customerId" Type="string" />
   </Parameters>
  </Method>
 </Query>
</Queries>


Here we have a customer order view, for display on a web page data grid for example.

The CustomerOrder.sql file contains

select * from customers c, orders o

whilst the LoadByCustomer method has the following criteria in the config sql file reference

where c.customerid = o.customerid
and c.customerid = @customerid
order by c.companyname, o.orderdate

and the LoadByRegion method’s where clause is

where c.customerid = o.customerid
and isnull(c.region, '') = isnull(@region, '')
order by c.companyname, o.orderdate


  • Insert a new row
OrdersFactory factory = new OrdersFactory();
Orders order = new Orders() ; // create a new row in memory
order.CustomerID = 123;
order.EmployeeID = 7 ;
order = factory.Save( order, connection ) ; // does the insert


  • Update an existing row
OrdersFactory factory = new OrdersFactory();
int orderId = 10289 ;
Orders order = factory.LoadByPrimaryKey( connection, orderId); // get an order
order.ShipPostalCode = "2565"
order = factory.Save( order, connection ) ; // does the update


  • Insert then update a row
OrdersFactory factory = new OrdersFactory();

Orders order = new Orders() ; // create a new row in memory
order.CustomerID = 123;
order.EmployeeID = 7 ;
order = factory.Save( order, connection ) ; // does the insert

order.ShipPostalCode = "2565"
order = factory.Save( order, connection ) ; // does the update


  • Delete an existing row
OrdersFactory factory = new OrdersFactory();

int orderId = 10289 ;
Orders order = factory.LoadByPrimaryKey( connection, orderId); // get an order

order.MarkForDelete = true ;
factory.Save( order, connection ) ; // does the delete


  • Negative use case - get a concurrency error.
TestTableFactory factory = new TestTableFactory();

TestTable alice = factory.LoadByPrimaryKey( connection, 12345 ); // get a row
TestTable bob = factory.LoadByPrimaryKey( connection, 12345 ); // get it again

alice.update_date = DateTime.Now ;
bob.update_date = DateTime.Now ;

alice = factory.Save( alice, connection ) ; // does the save ok
bob = factory.Save( bob, connection ) ; // will throw a concurrency exception


  • Negative use case - get a save error when saving a stale object
OrdersFactory factory = new OrdersFactory();

int orderId = 10289 ;
Orders order = factory.LoadByPrimaryKey( connection, orderId); // get an order

order.ShipPostalCode = "2565"
factory.Save( order, connection ) ; // does the update - ok

order.ShipPostalCode = "2000"
factory.Save( order, connection ) ; // will throw a stale row exception


  • Many users simultaneously inserting rows into a table.
In this example, a total of 5000 rows will be inserted into a table by 50 threads running in parallel. The table in question has a monotonically increasing non-identity integer ID primary key field, and therefore it is a contention hotspot.


In the first version of this use case, the inserts fail, as the code and sql that determine the next primary key value are not protected against concurrent threads. The test fails with the error “Violation of PRIMARY KEY constraint ‘PK_TestTable3′. Cannot insert duplicate key”.

protected void InsertLotsOfRowsInParallel()
{
 using ( MyThreadPoolManager tpm = new MyThreadPoolManager( 50, 5000 ))
 {
  for (int i = 1; i <= rowsToInsert; i++)
   tpm.Queue( InsertOneRow() ) ;
  tpm.WaitUntilAllFinished() ;
 }
}
// every invocation of this method runs in its own thread
protected void InsertOneRow()
{
 using (TransactionScope transaction =
         new TransactionScope(TransactionScopeOption.RequiresNew))
 {
  using (SqlConnection connection = new SqlConnection( _connectionString ) )
  {
   connection.Open();
   TestTable3Factory factory = new TestTable3Factory();
   TestTable3 obj = new TestTable3();
   // BAD: get the next available ID = max(ID) + 1
   obj.ID = select max(ID) + 1 from the table
   obj.update_date = DateTime.Now;
   // insert the row - this will fail with PRIMARY KEY constraint violation
   obj = factory.Save(obj, connection);
   transaction.Complete(); // commit - wont get here
  }
 }
}



In the correct version of this use case, the codegen tool has ootb capability to handle this common scenario. The tool will escalate the lock around the next primary key value and serialise access to the table, giving everyone a warm fuzzy feeling.



// every invocation of this method runs in its own thread
protected void InsertOneRow()
{
 using (TransactionScope transaction =
         new TransactionScope(TransactionScopeOption.RequiresNew))
 {
  using (SqlConnection connection = new SqlConnection( _connectionString ) )
  {
   connection.Open();
   TestTable3Factory factory = new TestTable3Factory();
   TestTable3 obj = new TestTable3();
   // GOOD: the tool will get the next available ID, the safe way
   obj.ID = factory.GetNextId( connection, TestTable3.Column_ID, true );
   obj.update_date = DateTime.Now;
   // insert the row - now it works
   obj = factory.Save(obj, connection);
   transaction.Complete(); // commit - aha
  }
 }
}


  • Here are a few .NET Reflector screen dumps of the DAL layer that the tool generates for Northwind.
northwind row

This is what the Orders row object looks like.


northwind factory

And the corresponding factory object for the Orders table.


northwind custom query

Here we have a custom query object that is a join between Customers and Orders tables.


northwind custom query factory

And the corresponding factory object.


northwind DAL code gen unit test

This screen shot shows some of the generated code involved in unit testing the DAL. Here, in the insert phase, each table is being populated and saved in referential integrity order, with pre and post row counts, object state, and field values being asserted.


  • One of the databases that I have recently codegened is the popular DotNetNuke database.
You can download my DAL for DNN here and also download the corresponding unit test dll here.
I recommend .NET Reflector to inspect the generated code.


Contact Buildmasters if you would like a DAL generated for your database.


  • This is the to do list for the tool
  • Make sure it compiles and works under Mono and Debian Linux.
  • Make sure it compiles and runs under VS2008.
  • Extend to target SqlServer 2008, MySql, Oracle databases.
  • Extend to handle stored procedures.


So far I have used the tool against 13 corporate, vendor and public databases, and it has my made my programming experience easier, faster, and more reliable.


I do not think about the DAL layer at all now - it just works.

Written by tubby taylor · Filed Under Code Generation 

Comments

2 Responses to “The search for a C# DAL code generator that actually works. Part 3: the solution.”

  1. tubby taylor on November 5th, 2008 1:59 pm

    Codegen now works on Windows Server 2008 / .NET 3.5 / Visual Studio 2008 / Sql Server 2008 platform.

    - tubby

  2. Manuel on February 4th, 2009 9:56 am

    Hello,

    I would like to know how can I get a copy of your DAL code generator.

    I’ve a lots of problems loading tables from a ODBC database into my dataset.
    My PC freezes for 30+ minutes for each table (and sometimes for each step). I need another option and I think this tool can help me with that problem.

    Can I download the tool from this site?

    Manu.

Got something to say?