Dapper.Net is a simple, flexible, fast, open-source, lightweight ORM built by Sam Saffron of Stack Overflow. To gain an understanding on Dapper and get started using it quickly, you can take a look at the earlier article in this series.
Best of both worlds
The best thing in Dapper is that while it is lightweight and easy to use, it provides you the best of both worlds -- you get almost the same speed of execution as that of ADO.Net while at the same time you get your POCOs back after execution of a query easily.
public static IEnumerable<Event> GetEventsBySpeaker(int speakerId)
{
string connectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
IEnumerable<Event> result =
connection.Query<Event>("SELECT * FROM Events WHERE SpeakerId = @SpeakerId", new { SpeakerId = speakerId });
return result;
}
}
The DapperExtensions library
There are many NuGet packages for Dapper available now. One of these is DapperExtensions. You can install DapperExtensions using the PackageManager window in Visual Studio. The DapperExtensions library includes many features that include the GetList and Count methods for more advanced queries, provides support for composite primary keys, etc. You can take a look at the complete list of features of this library on GitHub.
DapperExtensions allows you to use predicates for more advanced queries. Here's an example that illustrates this.
public static IEnumerable<Speaker> GetSpeakers()
{
string connectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
var predicate = Predicates.Field<Speaker>(s => s.IsActive, Operator.Eq, true);
return connection.GetList<Speaker>(predicate);
}
}
Handling transactions using Dapper
A transaction comprises of a unit of statements that are guaranteed to be executed either in their entirety. While a transaction block is in execution, if one of the operations in the transaction block fails, the entire transaction is aborted, i.e., the entire transaction fails.
Let’s now get into action! Assume that we have two tables in the database named, Speaker and Event. While the Speaker table contains information about the speakers of an event, the event table contains information about one or more events, i.e., the speaker of the event, the date of the event, description of the event, etc.
Here's the script for creating these two database tables.
CREATE TABLE [dbo].[Speaker](
[SpeakerId] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL
CONSTRAINT [PK_Speaker] PRIMARY KEY CLUSTERED
(
[SpeakerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Event](
[EventId] [int] IDENTITY(1,1) NOT NULL,
[EventName] [varchar](50) NULL,
[EventDescription] [varchar](50) NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL,
[Remarks] [varchar](max) NULL,
CONSTRAINT [PK_Event] PRIMARY KEY CLUSTERED
(
[EventId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Create a database and execute the above script to create the two tables namely, Speakers and Events. Now, populate some dummy records into both these tables so that you can test the program we would be writing next.
Now suppose you have a need to drop the details of a particular speaker from both the speaker and the event tables as the speaker is no longer available and you don't need to reserve the speaker details either, or, etc. The following piece of code illustrates how you can do this.
public static int DeleteSpeaker(Speaker speaker)
{
string connectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
try
{
using (IDbConnection connection = new SqlConnection(connectionString))
{
string speakerQuery = "DELETE FROM Speakers WHERE SpeakerId = @SpeakerId";
string eventQuery = "DELETE FROM Events WHERE SpeakerId = @SpeakerId";
IDbTransaction transaction = connection.BeginTransaction();
int rowsAffected = connection.Execute(speakerQuery, new { SpeakerId = speaker.SpeakerId }, transaction);
rowsAffected += connection.Execute(eventQuery, new { SpeakerId = speaker.SpeakerId }, transaction);
transaction.Commit();
return rowsAffected;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return -1;
}
}
To invoke the above method, all you need to do is create an instance of the Speaker class and assign the SpeakerId property of the instance of this class to a value that denotes the speaker you would want to delete from your database.
When you execute the above piece of code, the speaker information for the particular speaker would be deleted from both the tables in the database.
The ExecuteMapperQuery and ExecuteMapperCommand methods
Dapper.Net takes advantage of extension methods to add two mapping functions to the IDbConnection interface. These include the ExecuteMapperQuery and the ExecuteMapperCommand methods. While the former is used to map the result of a query to a strongly typed list, the latter is used to map the result of a query to a list of dynamic objects. Here’s an example that illustrates how the ExecuteMapperQuery extension method can be used.
List<Events> events = connection.ExecuteMapperQuery<Customer>("SELECT * FROM Events WHERE SpeakerId=@Id, new { Id=3});