Part 1 of the series showed the difference between a standard data provider common operations compared to the equivalent to those of the Dapper library in a Windows forms project. Although its common place for developers to place all code in a single project, in this part of the series learn how to separate the data operations into a class library along with introducing an interface and repository pattern.
Source code
Code samples are in the following repository
Why use an interface
Interfaces are useful for the following: Capturing similarities among unrelated classes without artificially forcing a class relationship. Declaring methods that one or more classes are expected to implement. Revealing an object's programming interface without revealing its class.
The main issue with an interface is that when you add a new members to its, then you must implement those members within all of the classes which implement that interface. Interfaces are slow as these required extra in-direction to find corresponding method in in the actual class.
ReSharper and interfaces
One of the benefits of ReSharper is that once a class/model has been defined a developer can extract an interface. ReSharper will place the new interface into the same file or a separate file. It is best to place the interface into a folder named Interfaces and have the class/model in a Class or Models folder.
Let's say a developer wants to add more functionality to a class which has an existing interface. Rerun the extract interface again and place in the same file, take the new item, place it in the original interface and removed the new version.
Repository Pattern
The Repository pattern is used for abstracting how data is persisted or retrieved from a database. The idea behind the Repository pattern is to decouple the data access layer from the business access layer of the application so that the operations (such as adding, updating, deleting, and selecting items from the collection) is done through straightforward methods without dealing with database concerns such as connections, commands, and so forth.
Putting things together
First step is to design the database tables needed, in this case there is only a Person table, in part 2 one to many relationships will be introduced and feel that teaching with a single table is best those just getting started.
Model to represent backend data
Next step is to create a class/model which represents the database tables.
In this case implementing INotifyPropertyChanged is dependent on a project requirements.
using System.ComponentModel;
using System.Runtime.CompilerServices;
#pragma warning disable CS8618 // Non-nullable field must contain a non-null value when exiting constructor. Consider declaring as nullable.
namespace DapperLibrary1.Models;
public class Person : INotifyPropertyChanged
{
private int _id;
private string _firstName;
private string _lastName;
private DateOnly _birthDate;
public int Id
{
get => _id;
set
{
if (value == _id) return;
_id = value;
OnPropertyChanged();
}
}
public string FirstName
{
get => _firstName;
set
{
if (value == _firstName) return;
_firstName = value;
OnPropertyChanged();
}
}
public string LastName
{
get => _lastName;
set
{
if (value == _lastName) return;
_lastName = value;
OnPropertyChanged();
}
}
public DateOnly BirthDate
{
get => _birthDate;
set
{
if (value.Equals(_birthDate)) return;
_birthDate = value;
OnPropertyChanged();
}
}
public override string ToString() => Id.ToString();
public event PropertyChangedEventHandler PropertyChanged;
protected virtual void OnPropertyChanged([CallerMemberName] string propertyName = null)
{
PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
}
}
Create the interface
Now create an interface for desired methods.
public interface IBaseRepository
{
List<Person> GetAll();
Task<List<Person>> GetAllAsync();
Task<Person> Get(int id);
Task<List<Person>> WhereIn(int[] ids);
Task<(bool, Exception ex)> Update(Person person);
Task Add(Person person);
Task<(bool, Exception ex)> AddRange(List<Person> list);
Task<bool> Remove(Person person);
}
Repository
Create a class with a meaningful name, in this case PersonRepository and implement IBaseRepository shown above.
Once this is done hover over IBaseRepository to allow Visual Studio to implement the methods that the developer writes code for as per below.
Notes
In the constructor, a single connection is created used for all methods along with a handler which tells Dapper how to handle DateOnly Struct unlike in part 1 were each method interacting with DateOnly needed to setup the handler.
IDbConnection is used for the connection which means even though for this example works with an SQL-Server database using IDbConnection means that a developer can use any data provider Microsoft supports
The last two methods are not part of the interface IBaseRepository and are only for use in the frontend project.
public class PersonRepository : IBaseRepository
{
private IDbConnection cn;
/// <summary>
/// Setup for DateOnly handler and for creating a connection
/// </summary>
public PersonRepository()
{
cn = new SqlConnection(ConnectionString());
SqlMapper.AddTypeHandler(new DapperSqlDateOnlyTypeHandler());
}
/// <summary>
/// Get all records in the Person table synchronously
/// </summary>
public List<Person> GetAll()
=> cn.Query<Person>(SqlStatements.ReadPeople).ToList();
/// <summary>
/// Get all records in the Person table asynchronously
/// </summary>
public async Task<List<Person>> GetAllAsync()
=> (List<Person>)await cn.QueryAsync<Person>(SqlStatements.ReadPeople);
/// <summary>
/// Get a single person by id
/// </summary>
/// <param name="id">Existing primary key</param>
/// <returns>A single person or null if not located</returns>
public async Task<Person> Get(int id)
=> await cn.QueryFirstOrDefaultAsync<Person>(SqlStatements.Get, new { Id = id });
/// <summary>
/// Perform a WHERE IN for identifiers
/// </summary>
/// <param name="ids">one or more identifiers</param>
/// <returns>list of people</returns>
public async Task<List<Person>> WhereIn(int[] ids)
{
IEnumerable<Person> result = await cn.QueryAsync<Person>(
SqlStatements.WhereInClause, new
{
Ids = ids
});
return result.ToList();
}
/// <summary>
/// Update an existing person
/// </summary>
/// <param name="person"></param>
/// <returns>success and on failure an exception</returns>
public async Task<(bool, Exception ex)> Update(Person person)
{
try
{
var affected = await cn.ExecuteAsync(SqlStatements.UpdatePerson, new
{
person.FirstName,
person.LastName,
person.BirthDate,
person.Id
});
return (affected == 1, null);
}
catch (Exception localException)
{
return (false, localException);
}
}
/// <summary>
/// Add a single person
/// </summary>
/// <param name="person"></param>
public async Task Add(Person person)
{
person.Id = await cn.QueryFirstAsync<int>(SqlStatements.InsertPeople, person);
}
/// <summary>
/// Add list of person to the person table
/// </summary>
/// <param name="list">List of people</param>
/// <returns>success and on failure an exception</returns>
/// <remarks>
/// Not great performance wise, for inserting a great deal of records see
/// https://www.learndapper.com/bulk-operations/bulk-insert
/// which for one developer is $999 per year.
/// </remarks>
public async Task<(bool, Exception ex)> AddRange(List<Person> list)
{
using TransactionScope transScope = new(TransactionScopeAsyncFlowOption.Enabled);
try
{
foreach (var person in list)
{
await Add(person);
}
transScope.Complete();
return (list.All(p => p.Id > 0), null);
}
catch (Exception localException)
{
return (false, localException);
}
}
/// <summary>
/// Remove a single record
/// </summary>
/// <param name="person">Existing person</param>
/// <returns>success of operation</returns>
public async Task<bool> Remove(Person person)
{
var affected = await cn.ExecuteAsync(SqlStatements.RemovePerson, new { person.Id });
return affected == 1;
}
#region For code samples
public async Task Reset()
{
await using SqlConnection cn = new(ConnectionString());
await cn.ExecuteAsync("DELETE FROM dbo.Person");
await cn.ExecuteAsync("DBCC CHECKIDENT (Person, RESEED, 0)");
}
public async Task<int> Count()
{
return await cn.ExecuteScalarAsync<int>(SqlStatements.CountOfPeople);
}
#endregion
}
Frontend project
A console project is used as it serves the needs of
- Creating a new instance of the class PersonRepository
- Zero out the database table
- Demonstrates
- Adding a range of records
- Retrieve all newly added records
- Retrieve a single record and updating the record
- Removing a record
internal partial class Program
{
static async Task Main(string[] args)
{
/*
* Create an new instance of PersonRepository which
* - Sets up the connection
* - Sets up a handler for DateOnly work
*/
PersonRepository repo = new();
/*
* Get current count of records if any
*/
Console.WriteLine($"Record count: {await repo.Count()}");
/*
* Remove all existing records
*/
await repo.Reset();
Console.WriteLine($"Record count: {await repo.Count()}");
/*
* Populate the table with five records created by Bogus package
*/
await repo.AddRange(BogusOperations.People(5));
Console.WriteLine($"Record count: {await repo.Count()}");
/*
* List all records to the console.
*/
var table = PersonTable();
var people = await repo.GetAllAsync();
foreach (var peep in people)
{
table.AddRow(peep.Id.ToString(), peep.FirstName, peep.LastName, peep.BirthDate.ToString());
}
AnsiConsole.MarkupLine($"[LightGreen]All people[/]");
AnsiConsole.Write(table);
/*
* Get the second record, change first name followed
* by saving to the table
*/
Person person = await repo.Get(2);
Console.WriteLine($"First name is {person.FirstName}");
person.FirstName += " Changed";
await repo.Update(person);
/*
* Get the first record and delete from the table
*/
person = await repo.Get(1);
Console.WriteLine($"First name is now {person.FirstName}");
await repo.Remove(person);
person = await repo.Get(1);
Console.WriteLine(person is null ? "Person remove" : "Failed to remove person");
/*
* Show all records, first record has been removed so there are now
* only four records.
*/
table = PersonTable();
people = await repo.GetAllAsync();
foreach (var peep in people)
{
table.AddRow(peep.Id.ToString(), peep.FirstName, peep.LastName, peep.BirthDate.ToString());
}
AnsiConsole.MarkupLine($"[LightGreen]All people after delete[/]");
AnsiConsole.Write(table);
ExitPrompt();
}
}
Summary
In this part of the series stronger code has been introduced by separating data layer from the presentation layer, introducing working with interfaces and the repository pattern to work with a SQL-Server database using Dapper to interact with the database.
In part 2, learn how to expand what has been presented in this part of the series to work with relational data.
Top comments (0)