Here is a quick example of a data repository pattern used with EF. Note the UpdateEntity method in AccountRepository purely returns the entity to be updated, this perhaps should be named more clearly.
This example was taken from Miguel Castro and uses MEF as it's DI container.
Here is an example of using WcfWebApi to produce a RESTful JSON data service. We have three areas to consider:
| Data Operation | HTTP Verb | Has Side Effects? | Idempotent? | Example Uri |
| Read All | GET | No | Yes | http://localhost:8000/contacts |
| Read Single | GET | No | Yes | http://localhost:8000/contacts/1 |
| Insert | POST | Yes | No | http://localhost:8000/contacts |
| Update | PUT | Yes | Yes | http://localhost:8000/contacts |
| Delete | DELETE | Yes | Yes | http://localhost:8000/contacts/3 |
| Http Code | Description | Examples |
| 200 | OK | Successful (read, update or delete) |
| 201 | CREATED | Successful (insert) |
| 400 | BAD REQUEST | Passing id as “X” when an integer is expected |
| 404 | NOT FOUND | Item not found (based on id) |
| 500 | SERVER ERROR | Exception thrown within service on server with the message contained within response body. |
[ServiceContract]
public interface IContactService
{
[OperationContract]
[WebGet(UriTemplate = "")]
HttpResponseMessage GetAll();
[OperationContract]
[WebGet(UriTemplate = "{id}")]
HttpResponseMessage Get(string id);
[OperationContract]
[WebInvoke(UriTemplate = "", Method = "POST")]
HttpResponseMessage Insert(Contact contact);
[OperationContract]
[WebInvoke(UriTemplate = "", Method = "PUT")]
HttpResponseMessage Update(Contact contact);
[OperationContract]
[WebInvoke(UriTemplate = "{id}", Method = "DELETE")]
HttpResponseMessage Delete(string id);
}
using System;
using System.Collections.Generic;
using System.Net;
using System.Net.Http;
using System.Text;
using Microsoft.ApplicationServer.Http.Dispatcher;
using RestDemo.Data;
using RestDemo.Service.Helpers;
using RestDemo.Service.Repositories;
namespace RestDemo.Service
{
public class ContactService : IContactService
{
private const string ID_MUST_BE_INTEGER = "Id must be an integer";
private const string ITEM_NOT_FOUND = "Item not found";
private readonly IContactRepository _repo;
public ContactService() : this(new ContactRepository())
{
}
public ContactService(IContactRepository repository)
{
_repo = repository;
}
#region IContactService Members
public HttpResponseMessage GetAll()
{
IEnumerable<Contact> contacts;
DataOperationResponse dataResponse = _repo.GetAll(out contacts);
HttpContent httpContent = GetHttpContent(contacts);
HttpResponseMessage httpResponse = SetupHttpResponse(dataResponse, httpContent);
return httpResponse;
}
public HttpResponseMessage Get(string id)
{
int intId;
if (!Int32.TryParse(id, out intId))
return new HttpResponseMessage(HttpStatusCode.BadRequest) { Content = new StringContent(ID_MUST_BE_INTEGER) };
Contact contact;
DataOperationResponse dataResponse = _repo.Get(intId, out contact);
HttpContent httpContent = GetHttpContent(contact);
HttpResponseMessage httpResponse = SetupHttpResponse(dataResponse, httpContent);
return httpResponse;
}
public HttpResponseMessage Insert(Contact contact)
{
DataOperationResponse dataResponse = _repo.Insert(contact);
HttpResponseMessage httpResponse = SetupHttpResponse(dataResponse);
return httpResponse;
}
public HttpResponseMessage Update(Contact contact)
{
DataOperationResponse dataResponse = _repo.Update(contact);
HttpResponseMessage httpResponse = SetupHttpResponse(dataResponse);
return httpResponse;
}
public HttpResponseMessage Delete(string id)
{
int intId;
if (!Int32.TryParse(id, out intId))
return new HttpResponseMessage(HttpStatusCode.BadRequest) { Content = new StringContent(ID_MUST_BE_INTEGER) };
DataOperationResponse dataResponse = _repo.Delete(intId);
HttpResponseMessage httpResponse = SetupHttpResponse(dataResponse);
return httpResponse;
}
#endregion
private static HttpContent GetHttpContent(int id)
{
HttpContent httpContent = new StringContent(Convertors.ToJson(id).ToString(), Encoding.UTF8, MediaTypes.JSON);
return httpContent;
}
private static HttpContent GetHttpContent(Contact contact)
{
HttpContent httpContent = contact
!= null
? new StringContent(Convertors.ToJson(contact).ToString(), Encoding.UTF8,
MediaTypes.JSON)
: null;
return httpContent;
}
private static HttpContent GetHttpContent(IEnumerable<Contact> contact)
{
HttpContent httpContent = contact
!= null
? new StringContent(Convertors.ToJson(contact).ToString(), Encoding.UTF8,
MediaTypes.JSON)
: null;
return httpContent;
}
private static HttpResponseMessage SetupHttpResponse(DataOperationResponse dataResponse, HttpContent content = null)
{
var response = new HttpResponseMessage();
switch (dataResponse.Status)
{
case DataOperationStatus.Success:
if (dataResponse.Type == OperationType.Insert)
{
response.StatusCode = HttpStatusCode.Created;
response.Content = GetHttpContent(dataResponse.InsertedId);
response.Content.Headers.Expires = new DateTimeOffset(DateTime.Now.AddSeconds(30));
}
else
{
response.StatusCode = HttpStatusCode.OK;
if (content != null)
response.Content = content;
}
break;
case DataOperationStatus.NotFound:
// REST best practise on delete is to return OK if resource
// cannot be found but here we are explicitly returning Not Found
response.StatusCode = HttpStatusCode.NotFound;
response.Content = new StringContent(ITEM_NOT_FOUND);
break;
case DataOperationStatus.Exception:
response.StatusCode = HttpStatusCode.InternalServerError;
response.Content = new StringContent(dataResponse.Exception.Message);
break;
}
return response;
}
/* You might also want set the ocation header:
* response.Headers.Location = new Uri("Contacts/" + item.Id, UriKind.Relative);
*/
}
}
public class Global : HttpApplication
{
protected void Application_Start(object sender, EventArgs e)
{
var webApiConfig = new WebApiConfiguration {EnableTestClient = true};
RouteTable.Routes.SetDefaultHttpConfiguration(webApiConfig);
RouteTable.Routes.MapServiceRoute<ContactService>("Contacts");
}
}
The source code also contains an example of hosting within a client application:
private const string CONTACTS_URI = "http://localhost:8010/contacts";
private static void Main(string[] args)
{
using (var host = new HttpServiceHost(typeof (ContactService), CONTACTS_URI))
{
host.Open();
ExitOnReadKey();
}
private static void ExitOnReadKey()
{
Console.WriteLine("Press any key to exit...");
Console.ReadLine();
}
}
private static void HttpGetAll()
{
var client = new HttpClient();
HttpResponseMessage response = client.Get(CONTACTS_URI);
Console.WriteLine("GET: {0} RESPONSE: {1} \n{2}\n", CONTACTS_URI, response.StatusCode, response.Content.ReadAsString());
}
private static void HttpPost()
{
var client = new HttpClient();
HttpContent jsonContactNew = new StringContent("{\"Id\":0,\"Name\":\"Edward\"}", Encoding.UTF8, MediaTypes.JSON);
HttpResponseMessage response = client.Post(CONTACTS_URI, jsonContactNew);
Console.WriteLine("POST (INSERT CONTACT): {0} RESPONSE: {1} \n{2}\n", CONTACTS_URI, response.StatusCode, response.Content.ReadAsString());
}
private static void HttpPut()
{
var client = new HttpClient();
HttpContent jsonContactUpdated = new StringContent("{\"Id\":2,\"Name\":\"Ziggy\"}", Encoding.UTF8, MediaTypes.JSON);
HttpResponseMessage response = client.Put(CONTACTS_URI, jsonContactUpdated);
Console.WriteLine("PUT (UPDATE CONTACT ID 2): {0} RESPONSE: {1}\n", CONTACTS_URI, response.StatusCode);
}
private static void HttpDelete()
{
var client = new HttpClient();
const string uri = CONTACTS_URI + "/3";
HttpResponseMessage response = client.Delete(uri);
Console.WriteLine("DELETE (CONTACT ID 3): {0} RESPONSE: {1} \n", uri, response.StatusCode);
}
private static void HttpGet()
{
var client = new HttpClient();
const string uri = CONTACTS_URI + "/1";
HttpResponseMessage response = client.Get(uri);
Console.WriteLine("GET: {0} RESPONSE: {1} \n{2}\n", uri, response.StatusCode, response.Content.ReadAsString());
}
// Some methods worth knowing:
// 1. When expecting JSON in response body you can set the following header in the request:
// client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue(MediaTypes.JSON));
//
// 2. When deserializing JSON single item within response body use the following:
// var contact = response.Content.ReadAs<Contact>();
//
// 3. When deserializing JSON collection within response body use the following:
// var contacts = response.Content.ReadAs<Contact[]>();
You can use Fiddler to monitor all HTTP requests and responses. The screenshot below shows Fiddler on the left hand side picking up all requests and responses from the WCF Web API Test Client (explained within Debugging below) on the right hand side.
Make sure you punch a hole in your firewall before testing (or change the port from 8010 to 80 in the source code).
On Windows 7 > Control Panel > Windows Firewall > Allow a Program or Feature through the Firewall
We are now ready to open Fiddler and use the Request Builder tab to enter your URI, set the HTTP verb (defaults to GET) and execute your request. On the left hand side you’ll see the HTTP response and headers, in this case the result was 200 (OK):
Then switch to the Inspectors tab to view the response body containing our data.
For PUT and POST requests you must add the following to the request headers:
Content-Type: application/json; charset=utf-8
We switched this new Microsoft Tool on in the Global.asax page with “EnableTestClient = true” which automatically routes:
In our case this would be http://localhost:8010/Contacts/Test:
Full source code: http://stevenhollidge.com/blog-source-code/RestDemo.zip
With Microsoft now firmly pushing Entity Framework (currently at version 4) as their preferred data access solution, it’s refreshing to see a couple of new players on the scene albeit perhaps not in direct competition.
Dapper (Dynamic Query Mapper) and PetaPoco are Mirco ORMs. An ORM is an Object Relational Mapper which sit between your business logic and database to abstract away the direct link between the two. Micro ORMs are a simplier and more efficient option to larger ORM frameworks such as EF4 and NHibernate.
In this demo (full source code downloadable from the links at the end of this article) I’ll be giving examples of how to use each of the frameworks against a SQL Server database for your CRUD (create/insert, read/select, update, delete) commands. I’m using the tempdb database so there' is no need to run any database scripts.
It’s important to note that for performance you still get a marginal benefit creating a hand coded DAL with SQLConnection, SQLCommand and all those SQLParameter objects like this:
public Customer Select()
{
var customer = null;
using (var connection = SqlHelper.GetOpenConnection())
{
using (var command = new SqlCommand(SqlHelper.ReadStatement, connection)
{ CommandType = CommandType.Text })
{
command.Parameters.Add("@Id", SqlDbType.Int);
command.Parameters[0].Value = SqlHelper.TestId;
var reader = command.ExecuteReader();
while (reader.Read())
{
customer = new Customer()
{
CustomerId = reader.GetString(0),
CompanyName = reader.GetString(1)
};
}
}
}
return customer;
}
But to hand code your own entire DAL is pretty time consuming and hard to maintain particularly when you’ve got commands with plenty of parameters. Now you could use T4 to generate your DAL based on your database schema and that’s probably still your best bet if performance is everything to your application. Or, for a minimal perf overhead (most LOB apps wouldn’t notice) you could use one of our new Micro ORMs!
To carry out the same task as the hand coded DAL above, Dapper requires only two lines of code:
var mapperConnection = SqlHelper.GetOpenConnection();
mapperConnection.Query<Customer>(SqlHelper.ReadStatement, new { Id = SqlHelper.TestId });
Note: This is using the (customer) typed Query, you can also use a dynamic version that omits the generic parameter of Customer to return a Dynamic Object. Dynamic Objects were introduced in .NET Framework 4.
For PetaPoco only three lines of code are required:
var petaPoco = new PetaPoco.Database(SqlHelper.ConnectionString, "System.Data.SqlClient");
petaPoco.OpenSharedConnection();
var customer = petaPoco.Fetch<Customer>(SqlHelper.ReadStatement, SqlHelper.TestId);
Note: This example is using the default execution options, you can also tweak the options to boost performance shown later in this blog.
You can see from a RAD point of view Micro ORMs really come into their own compared to hand coding your DAL. Since the start of .NET plenty of frameworks have been created to generate your Sql commands and parameters (based on your DAL method attributes or parameters) but these Micro ORM frameworks are special.
Firstly, other than one file for each framework no other code is required to pollute your solution. No attributes clogging up your DAL or DTOs or Business Layers. But now for the really special bit, both frameworks are actually generating code dynamically using Dynamic Methods and ILGenerators which is pretty cool and inspiring stuff. The source code for both frameworks are included in the source code download to this blog post and I encourage you to take a look – kudos to the guys that put these frameworks together!
In terms of performance, the full Dapper project (link also at the end of the post) includes a test project that runs a simple select statement 500 times and compares performance against various data access solutions, the results of which can be seen here:
Note: Dapper is named as Mapper Query when using the typed/generic read method and Dynamic Mapper Query when using the non generic method.
2nd Note: This code was produced on a travel netbook (I’m writing this blog post whilst stuck in Delhi airport for the night being eaten alive by mosquitos :) ). I would expect the figures to be much faster on a newer PC or laptop.
private static void DapperExamples()
{
Console.WriteLine("Running Dapper examples");
SqlConnection mapperConnection = SqlHelper.GetOpenConnection();
// CREATE
mapperConnection.Execute(SqlHelper.CreateStatement,
new
{
Id = SqlHelper.TestId,
CompanyName = SqlHelper.InsertCompanyName
});
// READ Mapper Query
IEnumerable<Customer> customer1 =
mapperConnection.Query<Customer>(SqlHelper.ReadStatement, new {Id = SqlHelper.TestId});
// READ Dynamic Object Mapper Query
var customer2 = mapperConnection.Query(SqlHelper.ReadStatement, new {Id = SqlHelper.TestId});
// UPDATE
mapperConnection.Execute(SqlHelper.UpdateStatement,
new
{
Id = SqlHelper.TestId,
CompanyName = SqlHelper.UpdateCompanyName
});
// DELETE
mapperConnection.Execute(SqlHelper.DeleteStatement, new {Id = SqlHelper.TestId});
}
private static void PetaPocoExamples()
{
Console.WriteLine("Running PetaPoco examples");
var petapoco = new Database(SqlHelper.ConnectionString, "System.Data.SqlClient");
petapoco.OpenSharedConnection();
// CREATE
petapoco.Execute(SqlHelper.CreateStatementWithIndexedParams, SqlHelper.TestId, SqlHelper.InsertCompanyName);
// READ with all default options
List<Customer> customer1 = petapoco.Fetch<Customer>(SqlHelper.ReadStatementWithIndexedParams,
SqlHelper.TestId);
// READ with some "smart" functionality disabled
var petapocoFast = new Database(SqlHelper.ConnectionString, "System.Data.SqlClient");
petapocoFast.OpenSharedConnection();
petapocoFast.EnableAutoSelect = false;
petapocoFast.EnableNamedParams = false;
petapocoFast.ForceDateTimesToUtc = false;
List<Customer> customer2 = petapocoFast.Fetch<Customer>(SqlHelper.ReadStatementWithIndexedParams,
SqlHelper.TestId);
// UPDATE
petapoco.Execute(SqlHelper.UpdateStatementWithIndexedParams, SqlHelper.TestId, SqlHelper.UpdateCompanyName);
// DELETE
petapoco.Execute(SqlHelper.DeleteStatementWithIndexedParams, SqlHelper.TestId);
}
internal static class SqlHelper
{
public static readonly string ConnectionString =
@"Data Source=.;Initial Catalog=tempdb;Integrated Security=True";
public static readonly string CreateStatement =
@"INSERT dbo.Customers (CustomerID, CompanyName) SELECT @id, @companyName";
public static readonly string ReadStatement = @"SELECT * FROM dbo.Customers WHERE CustomerId = @id";
public static readonly string UpdateStatement =
@"UPDATE dbo.Customers SET CompanyName = @companyName WHERE CustomerId = @id";
public static readonly string DeleteStatement = @"DELETE FROM dbo.Customers WHERE CustomerId = @id";
// PetaPoco parameters are named based on index
public static readonly string CreateStatementWithIndexedParams =
@"INSERT dbo.Customers (CustomerID, CompanyName) SELECT @0, @1";
public static readonly string ReadStatementWithIndexedParams =
@"SELECT * FROM dbo.Customers WHERE CustomerId = @0";
public static readonly string UpdateStatementWithIndexedParams =
@"UPDATE dbo.Customers SET CompanyName = @1 WHERE CustomerId = @0";
public static readonly string DeleteStatementWithIndexedParams =
@"DELETE FROM dbo.Customers WHERE CustomerId = @0";
public static readonly string TestId = "TEST";
public static readonly string InsertCompanyName = "Inserted company";
public static readonly string UpdateCompanyName = "Updated company";
internal static void EnsureDbSetup()
{
using (var cnn = GetOpenConnection())
{
var cmd = cnn.CreateCommand();
cmd.CommandText = @"
if (OBJECT_ID('Customers') is null)
begin
CREATE TABLE [dbo].[Customers](
[CustomerID] [nchar](5) NOT NULL,
[CompanyName] [nvarchar](40) NOT NULL,
[ContactName] [nvarchar](30) NULL,
[ContactTitle] [nvarchar](30) NULL,
[Address] [nvarchar](60) NULL,
[City] [nvarchar](15) NULL,
[Region] [nvarchar](15) NULL,
[PostalCode] [nvarchar](10) NULL,
[Country] [nvarchar](15) NULL,
[Phone] [nvarchar](24) NULL,
[Fax] [nvarchar](24) NULL)
end
";
cmd.Connection = cnn;
cmd.ExecuteNonQuery();
}
}
public static SqlConnection GetOpenConnection()
{
var connection = new SqlConnection(ConnectionString);
connection.Open();
return connection;
}
}
Using SQL Profiler we can view the TSQL that is being sent by Dapper and PetaPoco:
I hope you find this article helpful, happy coding!
Blog source code (including source for Dapper and PetaPoco): http://www.stevenhollidge.com/blog-source-code/MicroOrmDemo.zip
Dapper (includes performance tests comparing Data Access solutions): http://code.google.com/p/dapper-dot-net/
PetaPoco: http://www.toptensoftware.com/petapoco/
