This article describes one approach on how to interface a data access layer to allow for multiple data access platforms including a stubbed data access layer. I’ll show how to link up a stubbed layer and an MS SQL layer which both utilize the same interface contract.
Sounds Great! What Are We Doing Again?
It sounds a bit more complicated than it really is. Sometimes, especially when you are working with a large project, or one that is very critical (core to the business), you may want to take some additional considerations in your data access architecture. In enterprise applications, or in applications where you may foresee wholesale changes in the data access in the future, you may want to create an interface for any data access to follow in case you decide to switch from SQL to Oracle for example.
Another good use is when you want to “stub” the data access layer. Stubbing is simply when you return fake hardcoded data that emulates what you would expect the data access layer to really return. At first, it sounds like stubbing doesn’t really do anything. But there are many purposes, stubbing allows you to split work between developers (or teams) between layers; allowing one developer to work on the business logic or presentation layer, while another team works on the data access layer. Stubbing is also useful for unit tests when you do not want to actually build up/tear down the database and simply emulate what you would expect to return from the database.
Not 100% convinced? Well, don’t feel alone, it took me a little while to accept there was any real return on developing stubbed layers; but trust me, there is. Hopefully, this will become more clear further down the artice.
The Solution
First, we need to set up the solution. Our solution is going to use several projects to logically (and potentially physically) seperate code into layers/tiers. The projects we will be working with our as follows:
- P2.Business
- P2.DataAccess
- P2.DataAccess.MSSQL
- P2.DataAccess.Stub
- P2.Common.Entities
There are other projects within this solution, but for the purpose of this article they’re not referenced.
A Note About Business Entities
For the sake of brevity, I’m not going to go into the details of the business entities, simply note that when the proceeding code references a data type from Common.Entities this simply means there is a business entity created to frame the data into an object oriented view.
For more information on business entities you can view the following posts: Common Reusable Business Entities Part 1, Common Business Entities Part 2, and Common Business Entities Part 3.
Creating the Data Access Interface
First, we need to create an interface (contract) to tell our real data access layers how we want them to interact. In this demonstration, I will have a solution called “P2”. P2 will have the following projects:
The project P2.DataAccess is going to house all of our interface knowledge; P2.DataAccess.MSSQL and P2.DataAccess.Stub will implement the interfaces within P2.DataAccess. We’ll create an interface for working with our “products” first called IProductData.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using P2.Common.Entities.Ecommerce;
namespace P2.DataAccess
{
public interface IProductData
{
bool SaveProduct(ref Product product);
bool DeleteProduct(Product product);
Product GetProductByID(int id);
Product[] GetProducts();
Product[] SearchProducts(string search);
Product[] SearchProducts(bool posSearch, string search, bool posCategory, Category[] categories);
}
}
This interface describes what the product data access code will be able to do, in this case, it will be able to save a product, delete a product, and retrieve products in a variety of ways. Each data access, regardless of platform, will need to implement this interface and address these concerns.
Creating the Stubbed Data Access Layer
Now, let’s create a “real” data access layer which implements this interface. Because the interface resides in a different project, make sure that the P2.DataAccess.Stub project references P2.DataAccess. Next, create a file called ProductData.cs as follows:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using P2.Common.Entities.Ecommerce;
using P2.Common.Entities.General;
namespace P2.DataAccess.Stub
{
public class ProductData : IProductData
{
#region IProductData Members
public bool SaveProduct(ref Product product)
{
return true;
}
public bool DeleteProduct(Product product)
{
return true;
}
public Product GetProductByID(int id)
{
var product = new Product(id)
{
Name = "Cosmic Uamou",
Summary = "A handpainted original from Tokyocube.",
Description = "Tokyocube are very pleased to present a beautiful new custom series from Ayako Takagi called Cosmic Uamou. The series is made up of 7 hand-painted originals, each with its own unique colours and design. The series is based on the Buuts Buuts Uamou, and each figure is signed and exclusively available through Tokyocube.",
Price = 12.50,
Weight = .25,
Width = 2,
Height = 6,
Length = 2,
SKU = "CA001",
LargeImage = new Image("1_l.jpg", "Cosmic Uamou", "Cosmic Uamou - Front"),
MediumImage = new Image("1_m.jpg", "Cosmic Uamou", "Cosmic Uamou - Front"),
Thumbnail = new Image("1_t.jpg", "Cosmic Uamou", "Cosmic Uamou - Front"),
};
return product;
}
public Product[] GetProducts()
{
Product[] products = new Product[3];
products[0] = new Product(1)
{
Name = "Cosmic Uamou",
Summary = "A handpainted original from Tokyocube.",
Description = "Tokyocube are very pleased to present a beautiful new custom series from Ayako Takagi called Cosmic Uamou. The series is made up of 7 hand-painted originals, each with its own unique colours and design. The series is based on the Buuts Buuts Uamou, and each figure is signed and exclusively available through Tokyocube.",
Price = 12.50,
Weight = .25,
Width = 2,
Height = 6,
Length = 2,
SKU = "CA001",
LargeImage = new Image("1_l.jpg", "Cosmic Uamou", "Cosmic Uamou - Front"),
MediumImage = new Image("1_m.jpg", "Cosmic Uamou", "Cosmic Uamou - Front"),
Thumbnail = new Image("1_t.jpg", "Cosmic Uamou", "Cosmic Uamou - Front"),
};
products[1] = new Product(2)
{
Name = "Mermaid",
Summary = "Designed by Keiichi Tanaami.",
Description = "Designed by Keiichi Tanaami, and uniquely produced in polystone, the Mermaid is a very special piece showing the potential of current production techniques with its superb detailing and immense quality. Produced to a run of just 200 worldwide, Keiichi Tanaami's Mermaid stands 19 cm tall.",
Price = 10.50,
Weight = .25,
Width = 2,
Height = 6,
Length = 2,
SKU = "CA002",
LargeImage = new Image("2_l.jpg", "Mermaid", "Mermaid - Front"),
MediumImage = new Image("2_m.jpg", "Mermaid", "Mermaid - Front"),
Thumbnail = new Image("2_t.jpg", "Mermaid", "Mermaid - Front"),
};
products[2] = new Product(3)
{
Name = "Kaiju Blue",
Summary = "Designed by Sun Guts.",
Description = "Kaiju Blue release a new collection of art toys from Japanese designers Sun Guts, including variations of the Uraname and Suiko figures. All produced in Japan, and available as a limited run.",
Price = 12.50,
Weight = .25,
Width = 2,
Height = 6,
Length = 2,
SKU = "CA003",
LargeImage = new Image("3_l.jpg", "Kaiju Blue", "Kaiju Blue - Front"),
MediumImage = new Image("3_m.jpg", "Kaiju Blue", "Kaiju Blue - Front"),
Thumbnail = new Image("3_t.jpg", "Kaiju Blue", "Kaiju Blue - Front"),
};
return products;
}
public Product[] SearchProducts(string search)
{
Product[] products = new Product[0];
products[0] = new Product(1);
return products;
}
public Product[] SearchProducts(bool posSearch, string search, bool posCategory, Category[] categories)
{
Product[] products = new Product[0];
products[0] = new Product(1);
return products;
}
#endregion
}
}
There are a couple of things to notice in this code example. First, I am including the mapping of data to an entity in my data access layer; some people may do this in the business logic layer and return datatables/datasets or some other generic DTO (data transfer object) from the data layer. Because my objects have very little if any business logic, they are essentially DTO’s. Second, everything is hardcoded with actual values and there is no actual database interaction – this is what makes this a “stub”. The results are always the same, and can be unit tested against with certainty of the expected result. Finally, on a non-technical note, my products all have pretty unique names and descriptions. I found these while looking at e-commerce websites and got a kick out of their products. Here are a few of their product images:
Article Intermission
Visit Tokyo Cube for more…
Creating the MSSQL Data Access Layer
Let’s now create the MSSQL layer. This project will also reference the main P2.DataAccess project and implement the same interface as before:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using P2.Common.Entities.Ecommerce;
using P2.Common.Utilities;
using System.Data;
namespace P2.DataAccess.MSSQL
{
public class ProductData : IProductData
{
#region Mappers
//TODO: Removed for brevity.
#endregion
#region IInventoryData Members
public bool SaveProduct(ref Product product)
{
const string sql = "SaveProduct";
var parameters = new SqlHelperParameter[]
{
new SqlHelperParameter("Product", product.ID),
new SqlHelperParameter("Name", product.Name)
};
//Create product
object id = null;
var idColumn = "ProductID";
var result = SqlHelper.ExecuteNonQuery(ConnectionManager.Databases.P2_Ecommerce, sql, parameters, ref id, idColumn);
product.ID = Convert.ToInt32(id);
return result;
}
public bool DeleteProduct(Product product)
{
const string sql = "DeleteProduct";
var parameters = new SqlHelperParameter[]
{
new SqlHelperParameter("Product", product.ID),
};
var result = SqlHelper.ExecuteNonQuery(ConnectionManager.Databases.P2_Ecommerce, sql, parameters);
return result;
}
public Product GetProductByID(int id)
{
const string sql = "GetProductByID";
var parameters = new SqlHelperParameter[]
{
new SqlHelperParameter("ProductID", id)
};
DataRow row = null;
var data = SqlHelper.RetrieveDataTable(ConnectionManager.Databases.P2_Ecommerce, sql, parameters);
if (data.Rows.Count > 0)
{
row = data.Rows[0];
}
return MapDataToProduct(row);
}
public Product[] GetProducts()
{
const string sql = "GetProducts";
var data = SqlHelper.RetrieveDataTable(ConnectionManager.Databases.P2_Ecommerce, sql);
return MapDataToProducts();
}
public Product[] SearchProducts(string search)
{
const string sql = "SearchProducts";
var parameters = new SqlHelperParameter[]
{
new SqlHelperParameter("Search", search)
};
var data = SqlHelper.RetrieveDataTable(ConnectionManager.Databases.P2_Ecommerce, sql, parameters);
return MapDataToProducts(data);
}
public Product[] SearchProducts(bool posSearch, string search, bool posCategory, Category[] categories)
{
//TODO: Leaving out for brevity.
throw new NotImplementedException();
}
#endregion
}
}
Note: I like to use a SQL Helper class that I’ve written, therefore the code above does not look like normal ADO.NET. The actual code for the data access is not as important as the concept that we’ve seperated two versions of the data access layers which are tied together by a common interface.
Now, Let’s Tie It All Together in the Business Layer
The final piece to the puzzle is the business layer, where we want to give it the power to decide which data layer to execute. We can do this several ways but one that I have found really useful is by specifying the desired data access in the business logic constructor. We can do this in the following way:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using P2.DataAccess;
using P2.Common.Entities.Ecommerce;
namespace P2.Business.Ecommerce
{
public class ProductLogic
{
private IProductData _data;
public ProductLogic() : this(new P2.DataAccess.Stub.ProductData())
{
}
public ProductLogic(IProductData data)
{
_data = data;
}
public Product[] GetProducts()
{
return _data.GetProducts();
}
}
}
Notice that the constructor allows the caller to specify what type of Data Access to use. An example of this call could be:
var logic = new ProductLogic(new P2.DataAccess.Stub.ProductData());
var products = logic.GetProducts();
This call would get products from the data access stub. In a later post, I will demonstrate how to default a data access layer with a blank constructor using reflection. Normal usage of code could use the parameterless constructor and specify the default, while unit testing can specifically call the stub to avoid build up/tear down of the database.
And there you have it, the business logic now has total control of calling down to the desired data access and the data access layer becomes platform independent by implementing a common interface. Hope this was helpful, happy coding!
Nicholas Barger's Blog 