-
Notifications
You must be signed in to change notification settings - Fork 1
Home
#Getting Started with Fluent DML#
##What is it?##
Fluent DML is a fluent API for building SQL insert, update, upserts, and delete commands. It was created to ease refactoring in the read model denormalizers of an nCQRS project. It gives you nice, refactor-safe "SQL" like this:
var cmd = db.Update<Customer>()
.Set(c => c.Billing.City, "Houston")
.Set(c => c.Billing.State, "Texas")
.Where(c => c.CustomerId == myCustomerId)
.ToCommand();
var cmd2 = db.Update<Customer>()
.MapFrom(evnt)
.WithId(c => c.CustomerId)
##What it's not.## FluentDML is not a replacement for a complex ORM. It doesn't support a class mapped to multiple tables. It doesn't support a property mapped to multiple columns, except for components. It doesn't handle any encoding or other custom user types. It only supports the most trivial scenarios.
As the name suggests, FluentDML is not a query tool. It won't generate SELECT statements.
##How?##
To use FluentDML, follow these steps:
- Instantiate a map maker
- Build a map
- Instantiate a dialect
- Use the dialect to build standard ADO.NET commands
- Give the command an open connection and execute it.
###Default Map Maker### The map maker builds a map of property paths to column names.
Let's take these two classes:
public class Customer
{
public Guid CustomerId { get; set; }
public string Name { get; set; }
public Address Billing { get; set; }
}
public class Address
{
public string City { get; set; }
public string State { get; set; }
}
A typical class map, as generated by the DefaultMapMaker, of the Customer class would have these entries (property path -> table.column):
- CustomerId -> Customer.CustomerId
- Name -> Customer.Name
- Billing.City -> Customer.Billing_City
- Billing.State -> Customer.Billing_State
In this example, the Billing address is a component of the Customer class. It is not necessary to map the Address class.
To use the DefaultMapMaker:
using FluentDML.Mapping;
var mapMaker = new DefaultMapMaker(typeof(Customer));
var map = mapMaker.MakeMap();
DefaultMapMaker has several useful constructors:
public Map MakeMap(Assembly assembly);
public Map MakeMap(Assembly assembly, Predicate<Type> filter);
public Map MakeMap(params Type[] types);
public Map MakeMap(IEnumerable<Type> types);
####NHibernate Map Maker####
Fluent DML also includes a map maker for simple NHibernate-mapped classes. Rather than reflecting the type as with DefaultMapMaker, the NHibernateMapMaker builds maps from the NHibernate configuration.
A typical NHibernate mapping for the Customer class might look like this:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="FluentDML.Tests"
namespace="FluentDML.Tests">
<class name="Customer">
<id name="CustomerId">
<generator class="guid.comb" />
</id>
<property name="Name" />
<component name="Billing" class="Address">
<property name="City" column="Billing_City" />
<property name="State" column="Billing_State" />
</component>
</class>
</hibernate-mapping>
The resulting FluentDML map would be identical to the one shown previously, but it would be generated from NHibernate's mapping. It supports only the most trivial mappings and classes.
To use the NHibernateMapMaker:
using FluentDML.NHibernateAdapter;
var cfg = GetMyNHibernateConfiguration();
var mapMaker = new NHibernateMapMaker(cfg);
var map = mapMaker.MakeMap();
Unlike in NHibernate, Fluent DML dialects don't control the SQL syntax. Rather, they instantiate provider-specific instances for inserts, updates, and deletes. For example, the MsSqlDialect returns instances of MsSqlInsert, MsSqlUpdate, and MsSqlDelete. These three classes actually construct the SQL syntax.
To build a dialect, you need to provide a Map:
using FluentDML.Mapping;
using FluentDML.Dialect;
var mapMaker = new DefaultMapMaker();
var map = mapMaker.MakeMap();
var db = new MsSqlDialect(map);
Dialects should be treated as singletons. Dialects should be thread-safe once built.
A traditional update uses this syntax: var cmd = db.Update() .Set(c => c.Billing.City, "Houston") .Set(c => c.Billing.State, "Texas") .Where(c => c.CustomerId == myCustomerId) .ToCommand();
Calling ToCommand() returns a standard ADO.NET command with all the necessary parameters set. You may do something like this:
public class CustomerMovedEvent
{
public Guid EventSourceId { get; set; }
public string BillingCity { get; set; }
public string BillingState { get; set; }
}
IDialect db;
CustomerMovedEvent evnt;
var cmd = db.Update<Customer>()
.Set(c => c.Billing.City, evnt.BillingCity)
.Set(c => c.Billing.State, evnt.BillingState)
.Where(c => c.CustomerId == evnt.EventSourceId)
.ToCommand();
using (var conn = OpenConnection())
{
cmd.Connection = conn;
cmd.ExecuteNonQuery();
conn.Close();
}
With some extension methods, we can shorten this code considerably:
IDialect db;
CustomerMovedEvent evnt;
var cmd = db.Update<Customer>()
.Set(c => c.Billing.City, evnt.Billing.City)
.Set(c => c.Billing.State, evnt.Billing.State)
.Where(c => c.CustomerId = evnt.EventSourceId)
.ExecuteInReadModel();
The extension methods would look like this:
public static long ExecuteInReadModel<T>(this IUpdateWhere<T> update)
{
return update.ToCommand().ExecuteInReadModel();
}
public static long ExecuteInReadModel(this IDbCommand command)
{
long rowsAffected;
var connStr = ConfigurationManager.ConnectionStrings["ReadModel"].ConnectionString;
using (var conn = new SqlConnection(connStr))
{
conn.Open();
command.Connection = conn;
rowsAffected = command.ExecuteNonQuery();
conn.Close();
}
return rowsAffected;
}
Fluent DML also supports AutoMapper-mapped classes. For example, the previous example could simply be written as:
db.Update<Customer>()
.MapFrom(evnt)
.WithId(c => c.CustomerId)
.ExecuteInReadModel();
The AutoMapper mapping looks like this:
Mapper.CreateMap<Address, Address>();
Mapper.CreateMap<CustomerMovedEvent, Customer>()
.ForMember(c => c.CustomerId, mo => mo.MapFrom(e => e.EventSourceId))
.ForMember(c => c.Name, mo => mo.Ignore())
.ForMember(c => c.Billing, mo => mo.MapFrom(e => new Address() {
City = e.BillingCity
City = e.BillingState
}));
Mapper.AssertConfigurationIsValid();
Notice that we've had to map Address to itself. This is necessary for all components. We have to explicitly map the Billing address because, while AutoMapper can flatten a model, it can't unflatten it.
A traditional insert uses this syntax: var cmd = db.Insert() .Set(c => c.CustomerId, myCustomerId) .Set(c => c.Name, "Jason") .Set(c => c.Billing.City, "Houston") .Set(c => c.Billing.State, "Texas") .ToCommand();
We can also do AutoMapper-style inserts:
public class CustomerCreatedEvent
{
public Guid EventSourceId { get; set; }
public string Name { get; set; }
}
Mapper.CreateMap<CustomerCreatedEvent, Customer>()
.ForMember(c => c.CustomerId, mo => mo.MapFrom(e => e.EventSourceId))
.ForMember(c => c.Billing, mo => mo.Ignore());
Mapper.AssertConfigurationIsValid();
CustomerCreatedEvent evnt;
db.Insert<Customer>()
.MapFrom(evnt)
.ExecuteInReadModel();
In this case, we don't have to map Address to itself. We're ignoring the billing address property. The Name property on Customer is automatically matched with the Name property on Customer.
Fluent DML also supports upserts. An upsert allows you to attempt to update a row. If no rows were updated, a new row is inserted using the provided data. Upserts look like this:
public class CustomerCreatedEvent
{
public Guid EventSourceId { get; set; }
public string Name { get; set; }
}
Mapper.CreateMap<CustomerCreatedEvent, Customer>()
.ForMember(c => c.CustomerId, mo => mo.MapFrom(e => e.EventSourceId))
.ForMember(c => c.Billing, mo => mo.Ignore());
Mapper.AssertConfigurationIsValid();
CustomerCreatedEvent evnt;
db.Upsert<Customer>()
.MapFrom(evnt)
.ExecuteInReadModel();
The syntax for a delete is similar to an explicit update.
var command = db.Delete<Customer>()
.Where(c => c.CustomerId == id)
.ToCommand();