ASP.NET development recipes (c#): Getting Started with Dapper – a simple object mapper for dot Net (15 min)

Getting Started with Dapper – a simple object mapper for dot Net (15 min)

Tools required: (minimum)

- Visual C# 2010 Express (minimum)

- SQL server express

In this post I will try to present the beauty of this nice (amazing) little but powerful library called Dapper that lets me have control over my TSQL scripts, we will see it working in a WCF service.

As always I will try to make of this post a recipe that can be always improved adding flavors like IoC, logging, etc.

1) Open Visual Studio

2) Create a New Project, Name it DapperDemo

3) From Installed Templates select WCF > WCF Service Application > Click on “Create directory for solution” and click OK.

4) We’ll need to download the library, we can download the SqlMapper.cs file from here (or if you want to download the project with tests and comparisons with other ORMs go here, here a nice Git for windows that makes it painless to fetch the project), once you are in the page (http://code.google.com/p/dapper-dot-net/source/browse/Dapper/SqlMapper.cs) just right click on the link View Row file and select save target as and save the file into the project’s folder.

5) Once we have the file added in the project’s folder, we need to include it in our project, right click on the project’s name (DapperDemo), select Add and Existing Item… and select the file SqlMapper.cs and click OK.

6) And here we start the implementation…Rich click on the project we’ve just created and Select Add > Class…

7) Name it Customer and click the add botton.

8) Replace the empty class Customer we’ve just generated with the following code

[DataContract]

public class Customer

{

[DataMember]

public Int32 CustomerId { get; set; }

[DataMember]

public String Name { get; set; }

[DataMember]

public String Email { get; set; }

[DataMember]

public String Phone { get; set; }

}

9) Resolve errors; just right click on the attributes that prompt the errors and resolve.

10) Add a new class, name it DataAccessManager.cs (Right click on the project’s name, select Add > Class… name your class and click Add bottom)

11) Replace the empty class DataAccessManager we’ve just generated with the following code

public class DataAccessManager

{

public static readonly string connectionString = ConfigurationManager.ConnectionStrings["dapperConnectionString"].ToString();

public static SqlConnection GetOpenConnection()

{

var connection = new SqlConnection(connectionString);

connection.Open();

return connection;

}

12) Resolve errors

13) Add a new class, name it CustomerRepository.cs (Right click on the project’s name, select Add > Class… name your class and click Add bottom)

14) Replace the empty class Customer we’ve just generated with the following code

public class CustomerRepository

{

public Customer[] GetCustomers(int page, int pagesize)

{

using (SqlConnection connection = DataAccessManager.GetOpenConnection())

{

var sqlGetCustomers = @"

WITH CteCustomers AS

(

SELECT *, ROW_NUMBER() OVER ( ORDER BY Name) AS rownumber

FROM Customers

)

SELECT *

FROM CteCustomers

WHERE rownumber > (@page -1)*@pagesize AND rownumber <= (@page -1)*@pagesize + @pagesize

var customers = connection.Query<Customer>(sqlGetCustomers, new { page = page, pagesize = pagesize });

return customers.ToArray();

}

public Customer GetCustomer(int customerId)

{

using (SqlConnection connection = DataAccessManager.GetOpenConnection())

{

var sqlGetCustomer = @"

SELECT *

FROM Customers

WHERE CustomerId=@CustomerId

var customers = connection.Query<Customer>(sqlGetCustomer, new { CustomerId = customerId });

return customers.FirstOrDefault();

}

public bool SaveCustomer(Customer customer)

{

var sqlSaveCustomer = string.Empty;

if (customer.CustomerId == 0) // insert

{

sqlSaveCustomer = @"INSERT INTO Customers(Name, Email, Phone) VALUES(@name, @email, @phone)";

}

else

{

sqlSaveCustomer = @"UPDATE Customers SET Name = @name, Email = @email, Phone = @phone WHERE CustomerId=@customerId";

}

using (SqlConnection connection = DataAccessManager.GetOpenConnection())

{

var rowAffected = connection.Execute(sqlSaveCustomer, customer);

return rowAffected != 0;

}

15) Resolve errors and add the following reference at the top of the current file:

using Dapper;

16) Open IService1.cs, remove the class CompositeType and replace the body of the class IService1 with the following code:

[OperationContract]

Customer[] GetCustomers(int page, int pagesize);

[OperationContract]

Customer GetCustomer(int customerId);

[OperationContract]

bool SaveCustomer(Customer customer);

17) Open Service1.svc and replace the body of the class Service1 with the following:

CustomerRepository repository;

public Service1()

{

repository = new CustomerRepository();

}

public Customer[] GetCustomers(int page, int pagesize)

{

return repository.GetCustomers(page, pagesize);

}

public Customer GetCustomer(int customerId)

{

return repository.GetCustomer(customerId);

}

public bool SaveCustomer(Customer customer)

{

return repository.SaveCustomer(customer);

}

18) Open Web.config and add the following markup:

</connectionStrings>

19) Open Microsoft SQL Server Management Studio and add the Database and table we need for our demo.

(Make sure that you have a folder C:\Temp as shown in the script)

CREATE DATABASE [DBTest] ON PRIMARY

( NAME = N'DBTest', FILENAME = N'C:\Temp\DBTest.mdf' , SIZE = 3072KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N'DBTest_log', FILENAME = N'C:\Temp\DBTest_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

USE DBTest

CREATE TABLE [dbo].[Customers]

(

[CustomerId] [int] IDENTITY(1,1) NOT NULL,

[Name] [nvarchar](100) NOT NULL,

[Email] [nvarchar](100) NOT NULL,

[Phone] [nvarchar](50) NOT NULL,

CONSTRAINT [PK_Clients] PRIMARY KEY CLUSTERED

(

[CustomerId] ASC

)

WITH

(

PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON

) ON [PRIMARY]

20) Back to the project (VS) and Press F5

21) If WcfTestClient didn’t load after running the service, open it manually from "C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\WcfTestClient.exe", search for it if you don’t find it there.

22) Try adding some Customers, double click on IService1(BasicHttp…) and then double click on SaveCustomer service, fill email, name and phone and thenk click Invoke bottom… then add as many clients as you wish.