New Sections Added Below!

• A Customer / Order Example
• What's the Big Difference From Other ORMs?

Introduction

Database access is a large part of almost every application we work on. ORMs provide huge time saving benefits. The key is finding one that meets your needs in terms of ease of use, configuration style and features.

My goals in creating Marr DataMapper were:

• To create a data access library that internalizes the ADO.NET framework internals.
• To make the mapper as database agnostic as possible.
• To have the ability to load entity graphs from a single query
• To still have complete control over the SQL / stored procedures.

Download

Marr DataMapper is now available on NuGet!

~ or ~

Download the latest version from CodePlex:
http://marrdatamapper.codeplex.com/

Feature Overview

• NEW in 2.6 - AutoQuery and AutoQueryToGraph use a new fluent querying syntax
• NEW in 2.4 - Pluggable IMapStrategy allows you to create your own convention for mapping columns and relationships
• NEW in 2.3! - QGen filter now supports StartsWith, EndsWith and Contains
• NEW in 2.0! - Basic CRUD query generation with a lambda expression filtering syntax is now included!
• Attribute based
• No special base classes to inherit
• Uses a fast reflection library that employs caching techniques (http://www.codeproject.com//KB/library/FastReflection by Renaud Bédard - More info here: http://theinstructionlimit.com/?p=76)
• Can load entity graphs utilizing one-to-many, one-to-one and inheritance relationships
• Can automatically create and load parameters for updates / inserts
• Parameters can be manually added using a fluent interface (no longer will you have to use more than one line to add a parameter with the proper settings)
• Built in support for enums via "type converters". Enums can be converted to strings or ints and back automatically
• A pluggable architecture allows you to create your own converters for any data type that will automatically massage data values as they go into or out of the database
• Support for datasets, data readers and transactions
• Basic CRUD query generation with a lambda expression filtering syntax

Using the code

Mapping Entities

Entities are mapped to SQL statements via the ColumnAttribute found in the Marr.Data.Mapping namespace. The ColumnAttribute contains any metadata needed to map the field or property to the database query.

If the SQL column or SP parameter name is the same as the property name, then you can simply add the Column attribute without specifying a name. If they differ, you can manually specify a column name.

[Column]
private int ID { get; set; }

[Column]
public string Name { get; set; } 

[Column("DESC")]
public string Description { get; set; } 

Notice that Column attributes can be added to fields or properties of all scopes (private, public, etc).

By default, the DataMapper will attempt to automatically detect the correct parameter data type given the .NET CLR data type and the current database provider being used, but you can also specify a specific datatype.

Instantiating a DataMapper

The DataMapper class is at the heart of the project. This is the class you will access directly to work with the database. It contains a host of database access methods such as Query, Find, Update, and Insert.

(Note that the DataMapper implements the IDataMapper. This is to facilitate mocking out the IDataMapper object in your data layer for unit testing).

The DataMapper has a few constructor overloads:

public DataMapper(string providerName, string connectionString){ ... }
public DataMapper(DbProviderFactory dbProviderFactory, string connectionString) { ... }

The first constructor allows you to enter a provider name and a connection string. For example, if you are working with a SQL Server database, you would enter a provider name of "System.Data.SqlClient". This constructor is ideal to use because the providerName can easily be stored in a config file. Then switching data providers can potentially be as easy as changing a single config entry.

The second constructor allows you to manually pass in a DbProviderFactory instance in code. If you were using SQL Server, you would pass in System.Data.SqlClient.SqlClientFactory.Instance.

When building a data access class, I usually make a base class with a protected method that is responsible for instantiating a DataMapper with the correct database provider. Ex:

protected IDataMapper CreateDB()
{
    DataMapper db = new DataMapper(System.Data.SqlClient.SqlClientFactory.Instance, 
        "Data Source=mydb...");
    db.SqlMode = SqlModes.Text; // Default
    return db;
}

After an entity class has been decorated with Column attributes, then querying, updating or inserting is a fairly trivial task.

You simply need to instantiate an IDataMapper, and then call the appropriate method. Note that the DataMapper implements IDisposable in order to handle closing and disposing the underlying database connection and other ADO.NET bits. It is recommended to always instantiate the DataMapper with the using keyword to ensure that the database connection is properly closed. Note that the following code uses the previously mentioned CreateDB method to do the work of instantiating the DataMapper with the appropriate database provider.

Querying with Automatic Query Generation

As of v2.0, basic query generation is now supported. The new QGen methods on the IDataMapper are:

• AutoQuery
• AutoQueryToGraph
• AutoUpdate
• AutoInsert
• AutoDelete

These methods employ a lambda expression based filtering mechanism for creating the where clause.

AutoInsert:

        public void AddPerson(Person person)
        {
                using (var db = CreateDB())
                {
                        db.AutoInsert<Person>("dbo.People");                        
                }
        }

AutoUpdate:

        public void UpdatePerson(Person person)
        {
                using (var db = CreateDB())
                {
                        db.AutoUpdate<Person>("dbo.People", p => p.ID == person.ID);                        
                }
        }

AutoDelete:

        public void DeleteByName(string firstName, string lastName)
        {
                using (var db = CreateDB())
                {
                        db.AutoDelete<Person>("dbo.People", p => p.FirstName == firstName && p.LastName == lastName);
                }
        }

AutoQuery:

        public List<Person> GetPeopleByAge(int minimumAge, int maximumAge)
        {
                using (var db = CreateDB())
                {
                    return db.AutoQuery<Person>("dbo.People", p => p.Age >= minimumAge && p.Age <= maximumAge);
                }
        }

Querying with Customized Queries

The following methods on the IDataMapper allow you to specify your own custom query:

• Update
• Insert
• Find (a query that returns a single object)
• Query (a query that returns a list of objects)
• QueryToGraph (a query that returns a list of object graphs)
• GetDataSet
• GetDatatable
• ExecuteNonQuery
• ExecuteScalar

        public List<Category> ListAllCategories()
        {
                using (var db = CreateDB())
                {
                    return db.Query<Category>("SELECT * FROM Category");
                }
        }

Updating and Inserting works very similiarly. Note that the Insert and Update methods automatically create the parameters based on the [Column] attributes. Their names will match the property name, or the name provided in the ColumnAttribute.

        public void SaveProduct(Product product)
        {
            using (var db = CreateDB())
            {
                if (product.ID == 0)
                {
                    string sql = "INSERT INTO Product (Name, Description, Price, CategoryID, ImageFileName, NewItem, IsSplash) VALUES (@Name,@Description,@Price,@CategoryID,@ImageFileName,@NewItem,@IsSplash)";
               db.Insert<Product>(product, sql);
                }
                else
                {
                    string sql = "UPDATE Product SET Name=@Name, Description=@Description, Price=@Price, CategoryID=@CategoryID, ImageFileName=@ImageFileName, NewItem=@NewItem, IsSplash=@IsSplash WHERE ID=@ID";
                    db.Update<Product>(product, sql);
                }
            }
        }

You can also manually add parameters and then call ExecuteNonQuery method instead of using the Insert / Update methods. Note the fluent interface on the AddParameter method that allows you to easily customize all aspects of your parameter on a single line. In the sample below, we are assigning the ID as an output parameter, and assigning the value back to the Product entity model. (You can accomplish the same thing using the Update/Insert methods using settings in the ID's ColumnAttribute that will automatically cause the ID property to be populated with the return or output value.)

        public void InsertProduct(Product product)
        {
            using (var db = CreateDB())
            {
                var pID = db.AddParameter("ID", 0).Output().Parameter;
                db.AddParameter("Name", product.Name).Size(20);
                db.AddParameter("Description", product.Description).Size(200);
                ...
                db.SqlMode = SqlModes.StoredProcedure;
                db.ExecuteNonQuery("spInsertProduct");
                product.ID = (int)pID.Value;
            }
        }

Projecting Views Into Object Graphs

Marr DataMapper can also project a database view into an object graph. This feature is implemented by decorating your object graph with Relationship attributes. A relationship can be "many to many" or "one to many". For example, this Product entity has a list of Category entities. A single view that joins the Product and Category tables is used to populate this object graph. A Relationship attribute decorates the Categories property, and then the QueryToGraph method is called.

    public class ProductInfo
    {
        [Column(IsPrimaryKey = true)]
        public int ID { get; set; }
        [Column(Size=50)]
        public string Name { get; set; }
        [Column(Size=200)]
        public string Description { get; set; }
        [Column]
        public decimal Price { get; set; }
        [Column]
        public int? CategoryID { get; set; }

        [Relationship]
        public List<Review> Reviews{ get; set; }
    }

    public class Review
    {
        [Column("ReviewID", IsPrimaryKey = true)]
        public int ID { get; set; }
        [Column]
        public DateTime ReviewDate { get; set; }
        [Column]
        public string ReviewText { get; set; }

        [Relationship]
        public SiteUser Reviewer { get; set; }
    }

    public class SiteUser
    {
        [Column]
        public string UserName { get; set; }
    }

The Relationship attribute sees that the property is of type ICollection, so it infers that it is a one-to-many relationship. It also sees that it needs to instantiate Review entities to fill the Reviews property. This feature gives you the ability to load a list of fully populated objects graphs all from one unnormalized query / view! In the following example, we are returning all the products under a given category name. Each product can contain multiple reviews (a one-to-many relationship), and each review has a related SiteUser (a one-to-one relationship). This entire object graph is populated in this single call to QueryViewToObjectGraph.

public List<ProductInfo> ListProducts(string category)
{
    using (var db = CreateDB())
    {
        db.AddParameter("Category", category);
        // NOTE: Must sort by each parent entity PK to group properly
        string sql = "SELECT * FROM V_Product WHERE Category=@Category ORDER BY ID, ReviewID";
         return db.QueryToGraph<ProductInfo>(sql);
        }
}

One-to-one relationships are also supported via the RelationshipAttribute. If the property is not an ICollection, it will instantiate the single entity.

Relationships can be nested within the graph into multiple levels of depth.

Aside from adding the Relationship attributes, there are a few things you need to do to ensure that your Relationships will work properly.

First, every entity in the relationship hierarchy must have one or more Columns designated as PrimaryKeys *. This is how the DataMapper keeps track of grouping and creating the entities properly within the object graph.
* The only exception is that one-to-one relationship entites with no children do not have to have a PK marked.

Second, the QueryViewToObjectGraph functionality expects your query results to be sorted by the designated PrimaryKey columns of each parent entity in the graph, starting with the parent node.

For example, if you have an object graph with a parent Order that has a list of OrderItem objects:

public class Order
{
    [Column(IsPrimaryKey=true)]
    public int ID { get; set; }

    [Relationship]
    public List<OrderItem> Items { get; set; }
}

public class OrderItem
{
    [Column("OrderID")]
    public int ID{ get; set; }

    [Column]
    public int ProductID { get; set; }
}

Note that the first step (assigning primary keys to parent entities) is done.

Then the query has to sort by these keys, in order of the graph's hierarchy:

SELECT * FROM V_Orders ORDER BY ID

In this case, the only parent entity is Order, so we only have to sort by its primary key, which is "ID".

A Customer / Order Example

So for example, if you have an object graph with a parent `Customer`that has a list of `Order `objects, you could populate this graph with a single view.

SELECT c.ID, c.Name as Customer_Name, c.UpdatedBy as Customer_UpdatedBy, c.UpdatedOn as Customer_UpdatedOn,
o.ID as Order_ID, o.OrderDate, o.CustomerID as o.Order_CustomerID, o.UpdatedBy as Order_UpdatedBy, o.UpdatedOn as Order_UpdatedOn
oi.ID as OrderItem.ID, oi.OrderID as OrderItem_OrderID, oi.ProductID, oi.UpdatedBy as OrderItem_UpdatedBy, oi.UpdatedOn as OrderItem_UpdatedOn
FROM Customer c 
LEFT JOIN Order o ON c.ID = o.CustomerID
LEFT JOIN Orderitem oi ON o.ID = oi.OrderID

public class Customer
{
	[Column("ID", IsPrimaryKey = true)]
	public int ID { get; set; }

	[Column("Name", AltName = "Customer_Name")]
	public string { get; set; }

	[Column("UpdatedBy", AltName = "Customer_UpdatedBy")]
	public string UpdatedBy { get; set; }

	[Column("UpdatedOn", AltName = "Customer_UpdatedOn")]
	public DateTime UpdatedOn { get; set; }

	[Relationship]
	public List<order /> Orders { get; set; }
}

public class Order
{
	[Column("ID", AltName = "Order_ID", IsPrimaryKey=true)]
	public int ID { get; set; }

	[Column("OrderDate")]
	public DateTime OrderDate { get; set; }

	[Column("CustomerID", AltName = "Order_CustomerID")]
	public int CustomerID { get; set; }

	[Column("UpdatedBy", AltName = "Order_UpdatedBy")]
	public string UpdatedBy { get; set; }

	[Column("UpdatedOn", AltName = "Order_UpdatedOn")]
	public DateTime UpdatedOn { get; set; }

	[Relationship]
	public List<orderitem /> Items { get; set; }
}

public class OrderItem
{
	[Column("ID", AltName = "OrderItem_ID")]
	public int ID{ get; set; }

	[Column("OrderID", AltName = "OrderItem_OrderID")] // Foreign key
	public int OrderID { get; set; }

	[Column("ProductID")]
	public int ProductID { get; set; }

	[Column("UpdatedBy", AltName = "OrderItem_UpdatedBy")]
	public string UpdatedBy { get; set; }

	[Column("UpdatedOn", AltName = "OrderItem_UpdatedOn")]
	public DateTime UpdatedOn { get; set; }
}
</orderitem /></order />

// Returns a list of fully populated Customer object graphs (including orders with order items)
public List<customer /> ListCustomerAndOrders(DateTime minOrderDate, DateTime maxOrderDate)
{
	using (var db = CreateDB())
	{
		string sql = "SELECT * FROM v_Customer_Orders WHERE o.OrderDate BETWEEN @minDate AND @maxDate ORDER BY c.ID, o.Order_ID";
		db.AddParameter("minDate", minOrderDate);
		db.AddParameter("maxDate", maxOrderdate);
		return db.QueryViewToObjectGraph<customer />(sql);
	}
}
</customer /></customer />

public interface IConverter 
{ 
    object FromDB(ColumnMap map, object dbValue); 
    object ToDB(object clrValue); 
    Type DbType { get; } 
}

// Register a BooleanYNConverter 
MapRepository.Instance.RegisterTypeConverter(typeof(bool), new Converters.BooleanYNConverter());

// Register an EnumStringConverter
MapRepository.Instance.RegisterTypeConverter(typeof(Enum), new Converters.EnumStringConverter());