BusterWood.Mapper

.NET composable mapping library for objects and data (System.Data).

Sort of a replacement for Dapper (150K) and Automapper (350K) but Mapper is much smaller at around 100K.

Performance is "good" as Mapper uses the DLR to create and JIT compile methods to do the mapping, and these methods are cached.

Copying an object

Mapper contains an extension method for all objects called Copy<T>() which returns a shallow copy of the original object. The type being cloned must have a parameterless contructor, then all public properties and fields are copied.

Mapper can also clone sequences of object via the CopyAll<T>() extension which takes a IEnumerable<T> and returns an IEnumerable<T>.

To allow for customized copying the following overloads of CopyAll<T>() take an extra action to be performed on each copy:

• CopyAll<T>(Func<T, T>) calls the supplied function for each copied object
• CopyAll<T>(Func<T, T, int>) calls the supplied function for each mapped object passing the zero based index of the object

Copying between different types

You can copy an object of one type to another type using the Copy<TFrom,TTo>() extension method. The type being mapped to must have a parameterless contructor, then all readable public properties (and fields) of the source type are copied to properties (or fields) of the target type.

Mapper can also copy sequences of objects via the CopyAll<TFrom,TTo>() extension which takes a IEnumerable<TFrom> and returns an IEnumerable<TTo>. CopyAll<TFrom,TTo>() has overloads that allow the mapping to be customized:

• CopyAll<TFrom,TTo>(Func<TFrom,TTo>) calls the supplied function for each mapped object
• CopyAll<TFrom,TTo>(Func<TFrom,TTo, int>) calls the supplied function for each mapped object passing the zero based index of the object

Type compatibility

When copying data types must be compatible in some sense, the following lists the type compatibility rules:

Source Type	Target Type
Any numeric type or enum	Any numeric type or any enum
Nullable<T> where T is any numeric type or enum	any numeric type or any enum. default(T) is used as the value if value is null
Nullable<T> where T is any numeric type or enum	Nullable<T> where T is any numeric type or enum
any type other	type must match, be assignable, or have an explicit static cast

Name compatibility

For Copy, CopyAll and all the data mappings, the following rules apply when looking for the destination field or property to map to:

1. the source name (case insensitive)
2. if the name ends with 'ID' then try the name without 'ID' (case insensitive)
3. if the name does not end with 'ID' then try the name with 'Id' suffix added (case insensitive)
4. the above names with underscores removed (case insensitive)
5. the above names with the target class name prefix removed (case insensitive)

Note that the rules are following in the above sequence, and that rules 2 & 3 only apply when the data type of the field being mapped is a primative type, and enum, or a nullable of those types.

For example, if the source name is ORDER_ID then the following names would be considered (shown in perference order):

1. ORDER_ID
2. ORDER_
3. ORDERID
4. ORDER
5. ID (* this will be considered when mapping from a DbDataReader to a type called Order)

Note: name comparison is case insensitive.

ADO.NET DbConnection extensions

Mapper adds Query() and Execute() extension methods, as well as ...Async() variants.

The Query(string sql, object parameters = null) extension executes the supplied SQL (with optional parameters) and returns a DbDataReader.

The Execute(string sql, object parameters = null) extension executes the supplied SQL (with optional parameters) but just returns the number of rows affected.

Calling stored procedures

The QueryProc(string procName, object parameters = null) extension calls the named stored procedure (with optional parameters) and returns a DbDataReader.

The ExecuteProc(string procName, object parameters = null) extension calls the named stored procedure (with optional parameters) but just returns the number of rows affected.

How are these methods different from Query and Execute? You don't need to specify the full command syntax, for example:

var order = connection.Query("EXEC dbo.GetOrderById @id=@id", new { id=1 }).SingleOrDefault<Order>();

var order = connection.QueryProc("dbo.GetOrderById", new { id=1 }).SingleOrDefault<Order>();

Automatic connection open and close

As a conveniance, if Query() and Execute() are called on a closed connection then Mapper will open the connection, use it, and close/dispose the connection afterwards.

ADO.NET DbCommand methods

Mapper adds AddParameters(object parameters) extension method to System.Data.Common.DbCommand. AddParameters will add a DbDataParameter to the commands Parameters collection for each readable public property (and field) of parameters, setting the type and value.

ADO.NET DbDataReader extensions

Mapper adds the following extension methods to DbDataReader (as returned by Query() and Execute()) to read and map the data:

• Single<T>(this DbDataReader reader, ...) reads one and only one T
• SingleOrDefault<T>(this DbDataReader reader, ...) reads one or zero T
• ToDictionary<TKey, TValue>(this DbDataReader reader, ...) reads TValue items and creates a hash table with a unique TKey for each TValue
• ToList<T>(this DbDataReader reader, ...) reads a list of T
• ToLookup<TKey, TValue>(this DbDataReader reader, ...) reads TValue items but groups the items by key

Additional ...Async() extension methods also exist.

T can be:

• a class with a parameterless constructor - in which case public set-able fields and properties are mapped
• a struct - again, public set-able fields and properties are mapped
• a single value, e.g. long, string or an enumeration
• a Nullable<T> of primative value (e.g. 'int') or an enumeration

Note that the above methods take an optional Action<DbDataReader,T> parameter that allow you to add custom mapping between the current record of the data reader and the mapped version of T.

The above extension methods Close() the reader after reading the last result. This means that a reader with one result will be closed (disposed) automatically, but reading multiple results is still possible, for example:

[Test]
public void can_read_mutliple_results()
{
    using (var cnn = new SqlConnection(mapperTest))
    {
         var rs = cnn.Query("select * from dbo.Currency where id <= 4; select * from dbo.Currency where id > 4;");
         var small = rs.ToList<Currency>(); // read first result
         var big = rs.ToList<Currency>();  // read second result
         Assert.IsTrue(rs.IsClosed);
         Assert.AreEqual(4, small.Count);
         Assert.AreEqual(6, big.Count);
    }
}

ADO.NET SqlDataRecord methods

Mapper adds a ToSqlTable<T>() extension method to IEnumerable<T> that convert it into a IEnumerable<SqlDataRecord> such that it can be passed as a table valued parameter to SQL Server.

Currency[] currencies = ....;
var type = new SqlTableType("dbo.CurrencyType", new SqlMetaData("ID", SqlDbType.Int),  new SqlMetaData("NAME", SqlDbType.VarChar, 50));
var table = orders.ToSqlTable(type);
connection.ExecuteProc("dbo.UpdateCurrencies", new { rows = table }); // stored proc that takes a @rows parameter

Primative type, enums and strings can be converted directly, for example:

int[] orderIds = { 1, 2, 3}
var type = new SqlTableType("dbo.IdType", new SqlMetaData("ID", SqlDbType.Int));
var ids = orderIds.ToSqlTable(type);
var loaed = connection.QueryProc("dbo.GetCurrencies", new { ids }).ToList<Currency>(); // stored proc that takes a @ids parameter

Examples

Query returning a list:

List<Order> list = connection.Query("select * from dbo.[Order] where order_id = @OrderId", new { OrderId = 123 })
	.ToList<Order>();

Asynchronously query returning a list:

List<Order> list = await connection.QueryAsync("select * from dbo.[Order] where order_id = @OrderId", new { OrderId = 123 })
	.ToListAsync<Order>();

Query returning a dictionary for a unqiue key:

Dictionary<int, Order> byId = connection.Query("select * from dbo.[Order] where status = @Status", new { Status = 1 })
	.ToDictionary<Order>(order => order.Id);

Asynchronously query returning a dictionary for a unqiue key::

Dictionary<int, Order> byId = await connection.QueryAsync("select * from dbo.[Order] where status = @Status", new { Status = 1 })
	.ToDictionaryAsync<Order>(order => order.Id);

Query returning HashLookup for a non-unqiue key:

HashLookup<int, Order> byStatus = connection.Query("select * from dbo.[Order] where order_date > @OrderDate", new { OrderDate = new DateTime(2016, 8, 1) })
	.ToLookup<Order>(order => order.Status);

Asynchronously query returning HashLookup for a non-unqiue key:

HashLookup<int, Order> byStatus = await connection.QueryAsync("select * from dbo.[Order] where order_date > @OrderDate", new { OrderDate = new DateTime(2016, 8, 1) })
	.ToLookupAsync<Order>(order => order.Status);

Query returning exactly one row:

Order order = connection.Query("select * from dbo.[Order] where order_id = @OrderId", new { OrderId = 123 })
	.Single<Order>();

Asynchronously query returning exactly one row:

Order order = await connection.QueryAsync("select * from dbo.[Order] where order_id = @OrderId", new { OrderId = 123 })
	.SingleAsync<Order>();

Query returning exactly one row of a primative type:

int count = connection.Query("select count(*) from dbo.[Order] where order_type = @orderType", new { orderType = 3 })
	.Single<int>();

Query returning exactly zero or one rows:

Order order = connection.Query("select * from dbo.[Order] where order_id = @OrderId", new { OrderId = 123 })
	.SingleOrDefault<Order>();

Asynchronously query returning zero or one rows:

Order order = await connection.QueryAsync("select * from dbo.[Order] where order_id = @OrderId", new { OrderId = 123 })
	.SingleOrDefaultAsync<Order>();

Query returning zero or one rows of a enum:

OrderType? orderType = connection.Query("select order_type_id from dbo.[Order] where order_id = @OrderId", new { OrderId = 123 })
	.SingleOrDefault<OrderType?>();

Call a stored procedure that does not return results set(s)

int rowsChanged = connection.ExecuteProc("update_user_name", new { id=123, name="fred" });

Asynchronously call a stored procedure that does not return results set(s)

int rowsChanged = await connection.ExecuteProcAsync("update_user_name", new { id=123, name="fred" });