Griffin DAL Generator

A tool to generate Data Access Layer classes such as repositories and query objects.

Download Order Boss motivator

 

Features

 

Why use a code generator when OR/Ms and Data Mappers exist?

Performance

Both ORMs and Data Mappers have one thing in common when they present benchmarks. They compare the results to using ADO.NET only.

This code generator generates the most efficient code possible while still conforming to established patterns and practices.

Control

Many ORMs and mappers hide ADO.NET in an underlying layer and take control of everything that happens. The result of that is that the mapper or the ORM might be easier to use, but you have no idea of what's really happening (lazy loading, inefficient queries etc).

With generated code you are still in control and can adapt it exactly in the way that your current use case require.

Error handling

Have you tried to interpret the exceptions that Entity Framework throws? It can take quite some time to understand whats going on and why a specific exception is thrown.

All operations generated by this generator throws exceptions that include the SQL query and all data parameters that was used. The actual ADO.NET exception is never hidden and is always included as the inner exception.

SQL

Aren't you more comfortable writing a SQL query joins, grouping etc than doing the same with LINQ? With this generator you still get type safety and can keep focusing on what you are good at.

What do this generator create?

The purpose is not to generate 100% ready-to-use code, but to take care of 95% of the bootstrapping. You still have to fine tune some queries, especially if you use * instead of column names.

  • Clean code - Easy to read & maintain
  • Detailed error messages - Easy to fix issues
  • Performance - A lot faster than using a complete ORM.

Our "Query" definition

Queries represent a query in the Command/Query Separation (CQS) pattern. The queries generated by this tool is based on the DotNetCqs specification which means that the classes is not coupled to a specific implementation of the CQS pattern.

You can execute the classes directly or use a CQS execution library like the one in Griffin.Framework. You can for instance start by executing all queries in-process and at a later stage in another process/server with very small code changes (basically configuration).

Generation

This application has a built in query editor which you use to construct and test run your queries. The result is displayed directly in the application.

All parameters like @firstName is automatically detected and used as a parameter in the query class. So if you for instance write:

SELECT *
FROM Users
JOIN Customers ON (Users.CustomerId = Customers.Id)
WHERE FirstName LIKE @firstName

... we'll create a query class like:

public class GetUsersByName : Query<IList<GetUsersByNameResult>>
{
    public String FirstName { get; set; }
}

Generated classes consists of

  • Query class
  • Query result class
  • Query handler class
  • Integration tests - never worry that something breaks

Our "Repository" definition

The repository pattern is a well established pattern which reduces complexity and coupling by allowing the business code to use an abstraction that represents the data layer.

Generation

This tool can create repository implementations both for plain ADO.NET and for the data mapper in Griffin.Framework. You can choose between synchronous and asynchronous repositories.

To get started you only need to pick a table in your database:

 

.. and the tool will generate:

  • Repository interface
  • Repository implementation
  • Entity class
  • QueryOptions - for paging and sorting
  • Exceptions - To better understand what went wrong (compared to plain ADO.NET)
  • Integration tests - never worry that something breaks
  • CommandExtensions - Methods for smoother ADO.NET usage

Additional features

Here is a couple of features that might make your life easier:

Optimization suggestion

We always recommend using the int indexer on the DataReader as it give you a performance increase on busy systems and large data sets.

Thus we give you the column names as a suggestion to your own query in the resulting class:

// We strongly suggest that you use named columns instead
// as we use indexers to map the result set, any schema changes
// will otherwise break this class (the int indexer is much faster than the string indexer).
//
// This works unless you've used SQL column aliases
// SELECT Id, UserName, FirstName, CustomerId, Id, Name
cmd.CommandText = @"SELECT *
    FROM Users
    JOIN Customers ON (Users.CustomerId = Customers.Id)
    WHERE FirstName LIKE @firstName";

Adapting the mapper to nullable columns

We do not generate nullable primitives in the entities (as they are OK in db classes but not in business entities). Instead we assign a default value to nullable properties.

In the below example we've changed the CustomerId

public void Map(IDataRecord record, GetUserListResult dto)
{
    dto.Id = (Int32)record[0];
    dto.UserName = (string)record[1];
    dto.FirstName = (string)record[2];
    dto.CustomerId = record[3] is DBNull.Value ? -1 : (Int32)record[3];
    dto.Id1 = (Int32)record[4];
    dto.Name = (string)record[5];
}

Repository methods

Writing own repository methods are dead easy thanks to the generated map methods:

public IList<User> FindAll()
{
	var cmd = _transaction.Connection.CreateCommand();
	try
	{
		cmd.Transaction = _transaction;
		cmd.CommandText = @"SELECT Id, UserName, FirstName, CustomerId
							FROM Users";
		return MapCollection(cmd);
	}
	catch (Exception exception)
	{
		throw cmd.CreateDataException(exception);
	}
	finally
	{
		cmd.Dispose();
	}
}

Detailed exceptions

Never again spend time on trying to reproduce errors. All methods will include the exact command that failed:

System.Data.DataException: Incorrect syntax near '&'.
Query:
	UPDATE Users SET
		UserName & @UserName, 
		FirstName = @FirstName, 
		CustomerId = @CustomerId
	WHERE Id = @Id
Parameters:
	Id: 1
	UserName: hello
	FirstName: Da Griffin Framework!
	CustomerId: 42

Repository implemented using Griffin.Framework

Generates a complete implementation of repository using Griffin.Framework.