Querying Highlights


Creating a new 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, Update, and Insert.

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".

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

When building a DAL, 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;
}

Of course if you are using a dependency injection library, such as Ninject or Castle Windsor, you can easily make your DI container responsible for constructing IDataMapper objects.

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.

Generated Queries

To take advantage of the more complex query features, including generating queries on the fly, and Linq, you will have to use the Query<T>() function, which returns a QueryBuilder<T> object. The Query<T>() overload will provide you with many features using a fluent / chaining interface.

NOTE: In addition to the custom Linq querying capabilities provided by the Query<T>() function, a more standard System.Linq.IQueryable<T>implementation is provide via the Queryable<T>() function.
See: IQueryable<T>

Here are is a list of some of the fluent methods provided by the Query<T>() QueryBuilder:
  • BuildQuery() - Outputs the current query to a string without executing the query
  • Graph() - Instructs the QueryBuilder to try to load all children with defined relationships (this is the equivalent to calling the "QueryToGraph" method
    // Loads entire graph (parent Orders and all defined child relationships)
    return db.Query<Order>()
        .Table("OrdersView")
        .Graph()
        .ToList();

  • Graph(params ... childrenToLoad) - Allows you to specify exactly which related children to try to load if you do not want to load them all. (Note: the parent object is included by default). Child properties can be passed in as expressions.
Example:
    // Specifically loads parent object and only the OrderItems child (all other defined child relationships will be ignored)
    return db.Query<Order>()
        .Table("OrdersView")
        .Graph(o => o.OrderItems)
        .ToList();
  • Join<TLeft, TRight>(...) - Allows you to manually specify which children to load, and how to load them (ie LEFT, INNER, etc). This adds JOIN statements to your generated query.
  • OrderBy / ThenBy / OrderByDescending / ThenByDescending - Adds an order by statement using Linq syntax
  • Where - Adds a where statement using Linq syntax. (Note: this method automatically adds parameters for any columns included in the statement)
  • Page(int pageNumber, int pageSize) - Adds paging (only Sql Server 2005 and higher is supported at this time)
  • Skip / Take - Adds paging support using Linq syntax
  • ToList() - Executes the query


Linq Support

Query:
        // Using linq syntax
        public List<Person> GetPeopleByAge(int minimumAge, int maximumAge)
        {
                using (var db = CreateDB())
                {
                        var results = from p in db.Query<Person>().Table("v_People")
                                           where p.Age >= minimumAge && p.Age <= maximumAge
                                           orderby p.Age descending
                                           select p;

                        return results.ToList();
                }
        }

        // Using linq extension methods
        public List<Person> GetCompanyByName(string name)
        {
                using (var db = CreateDB())
                {
                        return db.Query<Company>().Table("v_companies").Graph()
                                .Where(c => c.Name.StartsWith(name) && c.Name.EndsWith("LLC"))
                                .OrderBy(c => c.Name);
                }
        }

Manually entering WHERE and ORDERBY clauses

Sometimes when building a query, you will want to manually enter a string for the WHERE or ORDER clause. This is convenient in cases where you want to filter a field that does not exist on the entity you are querying, or if you want to use a special keyword, or a more complex piece of SQL code.

                using (var db = CreateDB())
                {
                        return db.Query<Company>().Table("v_companies").Graph()
                                .Where("TimeStamp < GETDATE()")
                                .OrderBy("RAND()");
                }


Appending additional where clauses

You can append addition where clauses using the "AndWhere" or the "OrWhere" functions. Both of these functions have an overload that takes a lambda, and one that takes a constant string. One example of the AndWhere / OrWhere functions would be useful is when you want to combine a custom where clause to a lambda where clause:

db.Query<Person>()
                .Where(p => p.Name == "Bob") // Lambda 
                .AndWhere("([Name] = 'Robert')") // Constant string
                .OrderBy(p => p.Name).BuildQuery();

// Generates this WHERE clause: "WHERE ([t0].[Name] = @P0) AND ([Name] = 'Robert')"


NOTE: You can see more examples in the Marr.Data.Tests.WhereBuilder.cs unit test class.

Paging

Paging is currently supported for SQL Server 2005 and higher.

Paging using Linq syntax:
        using (var db = CreateDB())
        {
                int pageNumber = 1;
                int pageSize = 20;

                return db.Query<Company>()
                        .OrderBy(c => c.Name)
                        .Skip(pageNumber - 1)
                        .Take(pageSize)
                        .ToList();
        }


Paging using custom Page method:
        using (var db = CreateDB())
        {
                int pageNumber = 1;
                int pageSize = 20;

                return db.Query<Company>()
                        .OrderBy(c => c.Name)
                        .Page(pageNumber, pageSize)
                        .ToList();
        }

Generating query text without running query

You can generate query text for manual modification or execution:
        public List<Person> GetCompanyByName(string name)
        {
                using (var db = CreateDB())
                {
                        string sql = db.Query<Company>()
                                .Where(c => c.Name.StartsWith(name) && c.Name.EndsWith("LLC"))
                                .OrderBy(c => c.Name)
                                .BuildQuery();

                        // At this point the parameters have already been added..
                        // You can manually execute the query text, or you can do whatever you want with the query text
                        sql += ";UPDATE ...;"
                        db.ExecuteNonQuery(sql);
                }
        }



Custom join functionality

The following example shows how to generate a query with a join. Note that this Join method has its own custom syntax (it is different from the Linq join), so you cannot use it in a query with Linq syntax. (In other words, to use this Join method, you have to use chaining methods, like in the example below).
        // ---------- Joining two tables in a generated query -------------
        // This example shows how to generate a query with a LEFT JOIN.
        // Order is the "left" table in the join, and OrderItem is the "right" table being joined.
        // The JoinType, child property and a lambda representing the "ON" in the join statement are passed in as parameters to the Join function.
        List<Order> notFree = db.Query<Order>()
                        .Join<Order, OrderItem>(
                                JoinType.Left,                                // Left join
                                o => o.OrderItems,                       // Child property to join
                                (o, oi) => o.ID == oi.OrderID)        // Join ON condition
                        .Where<OrderItem>(oi => oi.Price > 0).ToList();



IQueryable<T> Support

The IDataMapper interface now provides a System.Linq.IQueryable<T> implementation by calling the "Queryable<T>()" function.
Ex:
    ...
    var context = db.Queryable<User>();
    var user = context.Where(u => u.UserName == "jdoe").FirstOrDefault();
    ...


Accessing the DataReader directly

There are a few different ways to access a datareader directly.

1) Use the ExecuteReader method, and specify the object creation logic:
List<Person> people = db.ExecuteReader("SELECT * FROM tblPerson",
                r => new Person { ID = r.GetInt32(0), Name = r.GetString(1) }
).ToList();


2) or for a single result:
Person person = db.ExecuteReader("SELECT * FROM tblPerson WHERE ID = 1",
                r => new Person { ID = r.GetInt32(0), Name = r.GetString(1) }
).FirstOrDefault();


3) Or if you want to populate an already instantiated result set (a dictionary, for example):
            Dictionary<string, int> people = new Dictionary<string, int>();
            db.ExecuteReader("SELECT PersonName FROM tblPerson", r => { people.Add(r.GetString(1), r.GetInt32(0)); });


You can utilize a data reader when using the query builder:
            List<Person> people = new List<Person>();

            db.Query<Person>()
                .QueryText("sql...")
                .DataReader(r =>
                {
                    while (r.Read())
                    {
                        Person p = new Person();
                        p.ID = r.GetInt32(r.GetOrdinal("ID"));
                        p.Name = r.GetString(r.GetOrdinal("Name"));
                        p.Age = r.GetInt32(r.GetOrdinal("Age"));
                        p.IsHappy = r.GetBoolean(r.GetOrdinal("IsHappy"));
                        p.BirthDate = r.GetDateTime(r.GetOrdinal("BirthDate"));
                        people.Add(p);
                    }
                });


Text Queries

If you already have a query written, and you want to query only the root level object (without loading any related child objects in the graph), you can call the Query<T>(string sql) method:
        public List<Person> GetAllPeople()
        {
                using (var db = CreateDB())
                {
                        return db.Query<Person>("SELECT * FROM Person");
                }
        }


If you want to load all objects in the graph from a view (or multi-table select statement), you can use to QueryToGraph<T>(string sql) method:
        public List<Person> GetAllPeople()
        {
                using (var db = CreateDB())
                {
                        return db.QueryToGraph<Person>("SELECT * FROM Person");
                }
        }

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 db.ExecuteNonQuery() 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();
                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 dataMapper.QueryToGraph method is called.

    public class ProductInfo
    {
        [Column(IsPrimaryKey = true, IsAutoIncrement = 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 CategoryInfo entities to fill the collection. This feature gives you the ability to load a list of objects, of which each parent item may have multiple child relationships, 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 a query with an unnormalized result set.

public List<ProductInfo> ListProducts(string category)
{
	using (var db = CreateDB())
	{
		db.AddParameter("Category", category);
		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.

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 to this rule is that one-to-one relationship entites with no child relationships do not have to have a PK specified.

A Customer / Order Example

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

"vCustomerOrders":

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


The corresponding object graph is shown below. Note that since some of the tables have the same column names. Duplicated column names must be renamed in the view (using the "AS" keyword). Then the object graph can use the alias in the "AltName" property. The AltName is only used within the "QueryToGraph" method. This allows you to directly query an object from its table using the defined column "Name", and also reuse the same object as part of an object graph using the AltName.
Also note that all entities (entities with Relationship attributes) must have a primary key defined for grouping purposes.

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; }
}


// 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.QueryToGraph<Customer>(sql);
	}
}

Last edited Jan 29, 2015 at 6:07 AM by jmarr, version 11

Comments

No comments yet.