My team and I needed, a while back, to put together a generator class for creating SQL statements which will be run by Dapper. The idea was to have it work like this:
DapperHelper helper = new DapperHelper("User"); //Table name
helper.Add("FirstName", "John")
.Add("LastName", "Harper")
.Add("DateOfBirth", new DateTime(1978, 5, 21));
string insertSql = helper.InsertSql;
DynamicParameters parameters = helper.Parameters;In other words, we wanted a little helper class that could generate SQL for us, and for reasons I'm not going to get into here, was not Entity Framework.
Of course, it only got more complex from there. But that said, the tool we ended up with was pretty dang nice, and I thought it might be useful for my dear readers and their projects.
Let's build a class DapperHelper that can generate parameterized INSERT and UPDATE SQL statements!
Warning: Experimental
This entire project is super experimental! It works for us, in two different production projects, but it may not work for your standards, desires, or needs. Use with care!
The Sample Project
As always, this series has a sample project over on GitHub. Check it out!
exceptionnotfound
Design Goals for DapperHelper
Before we code anything, we should take a moment to consider the design and functionality that we want our DapperHelper class to have by the end of this post.
First, our DapperHelper class will not actually execute the SQL it generates; it will merely create the SQL statement and theDynamicParameters object that can then be executed by a typical Dapper call.
When designing such a class, there are a couple of things we want to avoid allowing. Chief among these is SQL Injection, a classic attack pattern that occurs when we do not parameterize the inputs to the SQL statement. Hence, we must parameterize every input to any given SQL statement.
We also want to ensure that parameters being used in any given SQL statement are unique, so our class will need to enforce that uniqueness.
Finally, much of the time when we insert a new object to the database, we want to get back the value of the primary key for the newly inserted row. So, our class will need to retrieve that value out of the SQL database. There are many ways to do this, and some are better than others.
In summary, by the end of this post our DapperHelper class will be able to:
- Parameterize all inputs.
- Retrieve the primary key of a newly inserted row.
- Prevent parameters from having the same name.
- Store what table to execute the
INSERTorUPDATEagainst AND - Not actually execute the SQL it generates.
Let's go!
Building the DapperHelper Class
Let's start with a basic C# class:
public class DapperHelper { }We know that this class must keep track of the table it will execute against in order to generate the SQL statements. Let's make the table name a private variable, and create a constructor which takes that name.
public class DapperHelper
{
private readonly string _tableName;
public DapperHelper(string tableName)
{
_tableName = tableName;
}
}Note that we do not have a parameterless constructor; this is intentional since every SQL statement will be executed against a table.
Input Parameters
We must keep track of the parameters used in any SQL statement the DapperHelper class will generate, so let's hold them in a SortedDictionary object:
public class DapperHelper
{
private readonly SortedDictionary<string, object> _parameters
= new SortedDictionary<string, object>();
/* Other Properties and Constructor */
}The reason this is a SortedDictionary object is twofold: first, it's a dictionary because we don't want to allow two parameters with the same name, and second, it's a sorted dictionary because that makes it easy to find the parameter names when they are output to a display.
(You can just as easily make this property type Dictionary<string,object> and no functionality will change.)
Let's also create a method that adds parameters to the _parameter collection, which takes advantage of the .NET DuplicateNameException object.
public class DapperHelper
{
//Other Properties and Constructor
public DapperHelper Add(string parameterName, object value)
{
if (_parameters.ContainsKey(parameterName))
throw new DuplicateNameException("This field was already declared");
_parameters.Add(parameterName, value);
return this;
}
}Generating the INSERT SQL
Now that we have a way to add parameters, we will create a read-only property of the DapperHelper class that will generate the INSERT SQL statement.
An INSERT statement in SQL generally takes this form:
INSERT INTO tableName (Column1, Column2, ...)
VALUES (@Parameter1, @Parameter2, ...)Our system will treat the column name and the parameter name as the same string, so we will need to generate two different lists for the keys in the _parameters dictionary.
public class DapperHelper
{
//Other Properties and Methods
public string InsertSql
{
get
{
if (_parameters.Keys.Count == 0)
throw new Exception("Attempted to perform an insert without any input parameters.");
//Create a comma-separated list for
//the INSERT INTO part of the clause
var fields = string.Join(", ", _parameters.Keys);
//Create a comma-separated list for
//the VALUES part of the clause.
var values = string.Join(", @", _parameters.Keys);
//What do we return?
}
}
}Recall that we want our INSERT SQL to return the primary key value of the newly inserted row. We chose to do this in a roundabout way: we will declare an output table, insert the new PK to that table, then select it.
public class DapperHelper
{
//Other Properties and Methods
public string InsertSql
{
get
{
if (_parameters.Keys.Count == 0)
throw new Exception("Attempted to perform an insert without any input parameters.");
//Create a comma-separated list for
//the INSERT INTO part of the clause
var fields = string.Join(", ", _parameters.Keys);
//Create a comma-separated list for
//the VALUES part of the clause.
var values = string.Join(", @", _parameters.Keys);
return "DECLARE @output table(ID bigint); " +
$"INSERT INTO {_tableName}({fields}) " +
"OUTPUT INSERTED.[ID] " +
"INTO @output " +
$"VALUES(@{values}) " +
"SELECT * FROM @output;";
}
}
}We used this method for selecting the newly inserted PK because of this StackOverflow answer. Your mileage may vary.
Oded
Creating the DynamicParameters Object
Dapper uses a custom class, called DynamicParameters, to parameterize inputs to a SQL statement. The next thing we need to do for our DapperHelper class is to generate an instance of DynamicParameters using the _parameters dictionary.
public class DapperHelper
{
//Other Properties and Methods
public DynamicParameters Parameters
{
get
{
var parms = new DynamicParameters();
foreach (var parameterName in _parameters.Keys)
{
parms.Add(parameterName, _parameters[parameterName]);
}
return parms;
}
}
}Using the DapperHelper INSERT
Here's an example of how we might use this class in its current form:
DapperHelper helper = new DapperHelper("TableName");
helper.Add("IntValue", 5)
.Add("DateTimeValue", DateTime.Now)
.Add("StringValue", "This is a string");
var insertSql = helper.InsertSql;
var parameters = helper.Parameters;We would then pass the values for insertSql and parameters to another class, which would call Dapper to execute the SQL statement.
Adding Support for UPDATEs
We also want the DapperHelper class to support creating UPDATE SQL statements. In order to do this, we have to make several changes to the DapperHelper class.
The primary problem we have is that most of the time, SQL UPDATE statements involve a WHERE clause, and DapperHelper has no support for that yet. The parameters used in the WHERE clause will not have their values updated, so we cannot just include them in the existing parameter collection.
Initial Changes
Let's begin by creating two new private variables: one to hold parameters that will be used in a WHERE clause conditionalParameters, and one to hold the WHERE clause itself whereSql:
public class DapperHelper
{
private readonly Dictionary<string, object> _parameters
= new Dictionary<string, object>();
private readonly SortedDictionary<string, object> _conditionalParameters
= new SortedDictionary<string, object>(); //NEW
private readonly string _tableName;
private readonly string _whereSql; //NEW
//...Methods
}We can populate the WHERE clause SQL by creating an optional parameter for the constructor.
public class DapperHelper
{
//...Private properties
public DapperHelper(string tableName, string whereSql = null)
{
_tableName = tableName;
_whereSql = whereSql;
}
//...Other methods
}Populating the Conditional Parameters
Let's create a new method to add conditional parameters to our DapperHelper object. This method will throw an exception if the conditional parameter name already exists in either the normal parameters collection or the conditional parameters collection.
public class DapperHelper
{
//...Private properties, constructor, other methods
public DapperHelper AddCondition(string parameterName, object value)
{
if (_parameters.ContainsKey(parameterName)
|| _conditionalParameters.ContainsKey(parameterName))
{
throw new DuplicateNameException("This field was already declared");
}
_conditionalParameters.Add(parameterName, value);
return this;
}
}Generating the UPDATE SQL
Now we can create another property of DapperHelper to generate the UPDATE clause. This property will throw an exception if either:
- The class has no WHERE clause OR
- The class has no parameters that need their values updated.
public class DapperHelper
{
//...Other properties and methods
public string UpdateSql
{
get
{
if (string.IsNullOrEmpty(_whereSql))
throw new Exception("Attempted to perform an update without providing a where clause.");
if (_parameters.Keys.Count == 0)
throw new Exception("Attempted to perform an update without any input parameters.");
var sb = new StringBuilder();
foreach (var parameterName in _parameters.Keys)
{
sb.Append($"{parameterName} = @{parameterName}, ");
}
return $"UPDATE {_tableName} SET {sb.ToString().Substring(0, sb.Length - 2)} {_whereSql};";
}
}
}Note that the return clause actually removes the final ", " from the combined string list so we don't get a SQL syntax error.
Generating the DynamicParameters Object
The next step is to generate the new Parameters collection, which is really just combining the existing _parameters and _conditionalParameters private collections.
public class DapperHelper
{
//...Other properties and methods
public DynamicParameters Parameters
{
get
{
var parms = new DynamicParameters();
foreach (var parameterName in _parameters.Keys)
{
parms.Add(parameterName, _parameters[parameterName]);
}
foreach (var parameterName in _conditionalParameters.Keys)
{
parms.Add(parameterName,
_conditionalParameters[parameterName]);
}
return parms;
}
}
}Using DapperHelper UPDATEs
We can use these changes like so:
DapperHelper updateHelper
= new DapperHelper("UpdateTableName", "WHERE ID = @id");
updateHelper.AddCondition("id", 5)
.Add("DateTimeValue", DateTime.Now)
.Add("LongValue", 5827453L);
var updateSql = updateHelper.UpdateSql;
var updateParameters = updateHelper.Parameters;
Console.WriteLine(updateSql);
foreach (var parameterName in updateParameters.ParameterNames)
{
var value = updateParameters.Get<dynamic>(parameterName);
Console.WriteLine(parameterName + ": " + value);
}Which outputs the following:
Validating the Exceptions
For posterity, we can check if the expected exceptions are thrown in certain conditions. One should be thrown when we attempt to add a parameter that already exists:
DapperHelper updateHelper = new DapperHelper("UpdateTableName", "WHERE ID = @id");
updateHelper.AddCondition("id", 5)
.Add("DateTimeValue", DateTime.Now)
.Add("LongValue", 5827453L)
.Add("DateTimeValue", DateTime.Now.AddDays(-7)); //EXCEPTION!Another will be thrown if we don't include a WHERE clause and then try to generate the UPDATE SQL:
DapperHelper updateHelper = new DapperHelper("UpdateTableName");
updateHelper.AddCondition("id", 5)
.Add("DateTimeValue", DateTime.Now)
.Add("LongValue", 5827453L);
var updateSql = updateHelper.UpdateSql; //EXCEPTION!Finally, one will be thrown if we attempt to generate the UPDATE SQL but no normal parameters were added.
DapperHelper updateHelper = new DapperHelper("UpdateTableName", "WHERE ID = @id");
updateHelper.AddCondition("id", 5);
var updateSql = updateHelper.UpdateSql; //EXCEPTION!This is looking pretty good so far! But now we need to tie it all together by creating a new class that can actually run the SQL that DapperHelper objects generate.
Creating a Database Service
Let's start with a very basic class:
public class DatabaseService { }The new DatabaseService class will need to be able to connect using any given connection string. Let's pass the connection string in the constructor:
public class DatabaseService
{
protected string ConnectionString { get; set; }
public DatabaseService(string connectionString)
{
ConnectionString = connectionString;
}
}Since we don't want to allow an instance of DatabaseService to exist without a connection string, we won't include a parameterless constructor.
A Quick Note
Many of the examples below will use the following User class. This class is only provided for reference, and the implementations of DatabaseService and DapperHelper do not depend on it. This post assumes that ID is generated by the database.
public class User
{
public int ID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public DateTime DateOfBirth { get; set; }
}Query Methods
Dapper includes methods such as Query<T> which allows it to use an open SqlConnection instance and query the database that connection leads to. We want to allow our DatabaseService class to do the same. So we will need some new methods.
Let's start with the most basic one, Query<T>:
public class DatabaseService
{
public List<T> Query<T>(string sql, object parameters)
{
try
{
using SqlConnection conn = new SqlConnection(ConnectionString);
return conn.Query<T>(sql, parameters).ToList();
}
catch
{
return default;
}
}
}This method can be invoked in the following way.
DatabaseService myDB = new DatabaseService("ConnectionString");
var users = myDB.Query<User>("SQL statement", parameters);Note that our method makes a major assumption: that if there is an error when querying the database, we should return a default List<T>, which means it returns null. Keep this in mind if you want it to behave differently.
QueryFirst<T> and QuerySingle<T>
We will also need two more methods and two overloads of those methods in DatabaseService. We want the ability to get the first object matched by the query and to ensure that the query matches only a single object. Therefore we need QueryFirst<T> and QuerySingle<T> methods.
Each of the methods in this section will have two overloads; one that takes both the SQL statement to be executed and the parameters, and one that just takes the SQL statement. The second will then call the first.
public class DatabaseService
{
//...Other methods and properties
public T QueryFirst<T>(string sql)
{
return QueryFirst<T>(sql, null);
}
public T QueryFirst<T>(string sql, object parameters)
{
try
{
using SqlConnection conn = new SqlConnection(ConnectionString);
return conn.QueryFirst<T>(sql, parameters);
}
catch(Exception ex)
{
//Log the exception
throw;
}
}
public T QuerySingle<T>(string sql)
{
return QuerySingle<T>(sql, null);
}
public T QuerySingle<T>(string sql, object parameters)
{
try
{
using SqlConnection conn = new SqlConnection(ConnectionString);
return conn.QuerySingle<T>(sql, parameters);
}
catch(Exception ex)
{
//Log the exception
throw;
}
}
}These methods can be invoked in the following way:
DatabaseService myDB = new DatabaseService("ConnectionString");
User users = myDB.QueryFirst<User>("SQL statement", parameters);
User singleUser = myDB.QuerySingle<User>("SQL statement", parameters);Note that all of these methods include a try catch block so that we can log exceptions encountered here, though that feature is not implemented in this sample.
We will probably also want the QueryFirstOrDefault<T> and QuerySingleOrDefault<T> versions of these methods:
public class DatabaseService
{
//...Other properties and methods
public T QueryFirstOrDefault<T>(string sql)
{
return QueryFirstOrDefault<T>(sql, null);
}
public T QueryFirstOrDefault<T>(string sql, object parameters)
{
try
{
using SqlConnection conn = new SqlConnection(ConnectionString);
return conn.QueryFirstOrDefault<T>(sql, parameters);
}
catch
{
//Log the exception
return default;
}
}
public T QuerySingleOrDefault<T>(string sql)
{
return QuerySingleOrDefault<T>(sql, null);
}
public T QuerySingleOrDefault<T>(string sql, object parameters)
{
try
{
using SqlConnection conn = new SqlConnection(ConnectionString);
return conn.QuerySingleOrDefault<T>(sql, parameters);
}
catch (Exception ex)
{
return default;
}
}
}Execute Method
Now we need a method that will not return any objects, rather it will just execute the given SQL statement.
public class DatabaseService
{
//...Other properties and methods
public void Execute(string sql)
{
Execute(sql, null);
}
public void Execute(string sql, object parameters)
{
using SqlConnection conn = new SqlConnection(ConnectionString);
conn.Execute(sql, parameters);
}
}We can invoke this method like so:
DatabaseService myDB = new DatabaseService("ConnectionString");
myDB.Execute("SQL statement", parameters);As you might imagine, this is a pretty dangerous method, so you may not want to include it in your project.
Supporting DapperHelper
Now we need to consider what we want to happen for our DatabaseService class to directly support the DapperHelper class. Let's first consider what happens when we want to run an INSERT SQL statement.
For most of my projects, whenever some object was inserted into the database, we usually wanted to get back the new ID of the inserted item, which is most often an int, a long, or a Guid. To support this scenario, we need a method that will execute a SQL statement and return a scalar value. Dapper happens to support such a method, ExecuteScalar<T>, so we just need a method in DatabaseService that calls it.
public class DatabaseService
{
//...Other methods and properties.
public T ExecuteScalar<T>(string sql, object parameters)
{
try
{
using SqlConnection conn = new SqlConnection(ConnectionString);
T result = conn.ExecuteScalar<T>(sql, parameters);
return result;
}
catch (Exception ex)
{
return default;
}
}
}Note that there is no overload for this method; we assume that if you are calling this method you WILL have parameters for the query. If you don't, you can just pass null for that argument.
This kind of method is great if we expect the SQL query to return a single value. Now we can go one step further and implement two methods which take an instance of DapperHelper as a parameter; one for INSERTs, and one for UPDATEs.
public class DatabaseService
{
//...Other properties and methods
public T ExecuteInsert<T>(DapperHelper helper)
{
return ExecuteScalar<T>(helper.InsertSql, helper.Parameters);
}
public void ExecuteUpdate(DapperHelper helper)
{
Execute(helper.UpdateSql, helper.Parameters);
}
}You can make the argument that these methods are unnecessary overloads, but I find them useful.
Invoking ExecuteInsert<T> and ExecuteUpdate
Now let's see how to invoke the new execution methods using DapperHelper. Imagine we have the following helper:
DapperHelper helper = new DapperHelper("Users"); //"Users" is the SQL table nameWe can insert a new User object by doing the following:
helper.Add("FirstName", "John");
helper.Add("LastName", "Smith");
helper.Add("DateOfBirth", new DateTime(1970, 1, 1));
DatabaseService myDB = new DatabaseService("ConnectionString");
int userID = myDB.ExecuteInsert<int>(helper);We can update an existing user object by doing the following:
DapperHelper updateHelper = new DapperHelper("Users", "WHERE ID = @ID");
updateHelper.AddCondition("ID", 5);
updateHelper.Add("FirstName", "Josephina");
updateHelper.Add("LastName", "Ezekiel-Smith");
DatabaseService myDB = new DatabaseService("ConnectionString");
myDB.ExecuteUpdate(updateHelper);Ta-da! We now have a working DatabaseService class. With a little refactoring, this class and DapperHelper will fit right into any project.
Limitations
At the moment, INSERTs using DapperHelper are limited to tables that have an [ID] column as the primary key. A key future change would be to support tables that have primary keys on different columns.
Summary
The DapperHelper and DatabaseService classes permit us to create INSERT and UPDATE SQL statements in a more generic fashion, and allow us to ensure that parameters are not included more than once. These classes, though experimental, have given us a leg up on getting any project integrated with Dapper while keeping the speed and integrity it provides.
This post, and the original code and form of this project, was published as issues #31, #32, and #33 of The Catch Block. Aren't getting The Catch Block in your email every Wednesday? Sign up to be a paid subscriber now!
I'd like to hear any comments, suggestions, tips, or advice any of my dear readers have about making the DapperHelper project better. Sound off in the comments below!
Thanks for reading, and Happy Coding!
