Creating a C# Repository in .NET Core using RepoDb

In this article, we will show you how to create an entity-based C# Repository using RepoDb ORM.

Benefits

This will give you a knowledge on how to implement a standard entity-based Repository in fast and clean manner.

Before we begin

We expect that you have the following software install in your machine.

• Microsoft SQL Server (at least 2016).
• Microsoft SQL Server Management Studio (at least 2016)
• Microsoft Visual Studio (at least 2017).

To proceed, follow the steps below.

Create a Database

In your SQL Server, execute the script below.

CREATE DATABASE [Inventory];
GO

Create a Table

In your SQL Server, execute the script below.

USE [Inventory];
GO

CREATE TABLE [dbo].[Customer]
(
    [Id] BIGINT IDENTITY(1,1) 
    , [Name] NVARCHAR(128) NOT NULL
    , [Address] NVARCHAR(MAX)
    , CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED ([Id] ASC )
)
ON [PRIMARY];
GO

Create a C# Project

The project information must be below:

• Name: InventoryAPI
• Type: ASP.NET Core API
• Test: No
• Location: Any Location

Leave this project an empty one as we will create our own objects moving forward.

Create a Model Class

In your C# project, do the following.

• Add a folder named Models.
• Inside the Models folder, add a new class named Customer.cs.

Replace the content of the Customer.cs with the code snippets below.

public class Customer
{
    public long Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
}

Creating a Repository Class

In your C# project, do the following.

• Add a folder named Repositories.
• Inside the Repositories folder, add a new class named CustomerRepository.cs.

The content of your class would be below.

public class CustomerRepository
{
}

Inherit from BaseRepository

At this point in time, you already have the following.

• A database named Inventory.
• A table named Customer.
• A C# ASP.NET Core API project.
• A class model named Customer.
• A repository class named CustomerRepository.

Before we proceed, you need to install RepoDb. Type the command below in your Package Manager Console.

Install-Package RepoDb

Now, replace the content of your class CustomerRepository with the one below.

public class CustomerRepository : BaseRepository<Customer, SqlConnection>
{
    public CustomerRepository() : base(@"Server=.;Database=Inventory;Integrated Security=SSPI;")
    { }
}

As recommended, not covered by this tutorial, the repository must accept a connectionString (or settings object) in the constructor. Then pass the value of connectionString on the constructor of the base class BaseRepository.

The class BaseRepository is an embedded base repository object within RepoDb library. It is usually used to create an entity-based repository.

Actually, at this point in time, you already have inherited all the operations of the BaseRepository. Without doing anything more, you can now call the CustomerRepository.

See sample codes below.

using (var repository = new CustomerRepository())
{
    var customer = new Customer
    {
        Name = "John Doe",
        Address = "New York"
    };
    var id = repository.Insert<long>(customer);
    ...
}

The code above will insert a new record in your table Customer.

To query, you can use the code below.

using (var repository = new CustomerRepository())
{
    var customer = repository.QueryAll().FirstOrDefault();
    ...
}

Even though you already have a working CustomerRepository, that is still not enough as per the C# standard. By standard, we usually use an interface for dependency-injection.

Note: You can also inject a class, not just an interface!

Implement a Repository Interface

In your C# project, do the following.

• Add a folder named Interfaces.
• Inside the Interfaces folder, add a new class named ICustomerRepository.cs.

Replace the content of the file with the code snippets below.

public interface ICustomerRepository
{
    Customer GetById(long id);
    Customer GetByName(string name);
    IEnumerable<Customer> GetAll();
    long Insert(Customer customer);
    int Update(Customer customer);
    int Delete(long id);
}

We have created 6 methods which would enable you to do a basic CRUD operations on the Customer entity.

Implement your Custom Methods

First, implement the interface within the CustomerRepository class.

See the codes below.

public class CustomerRepository : BaseRepository<Customer, SqlConnection>, ICustomerRepository
{
    public CustomerRepository ()
        : base(@"Server=.;Database=Inventory;Integrated Security=SSPI;")
    { }
}

Then, implement the method one-by-one. Simply copy and paste the code snippets provided below inside your CustomerRepository class.

For GetById:

public Customer GetById(long id)
{
    return Query(id).FirstOrDefault();
}

For GetByName:

public Customer GetByName(string name)
{
    return Query(e => e.Name == name).FirstOrDefault();
}

For GetAll:

public IEnumerable<Customer> GetAll()
{
    return QueryAll();
}

For Insert:

public long Insert(Customer customer)
{
    return Insert<long>(customer);
}

For Update:

public int Update(Customer customer)
{
    return Update(customer);
}

For Delete:

public int Delete(long id)
{
    return Delete(id);
}

By this time, you can now call your own customized methods inside CustomerRepository class.

See sample codes below to insert.

using (var repository = new CustomerRepository())
{
    var customer = new Customer
    {
        Name = "John Doe",
        Address = "New York"
    };
    var id = repository.Insert(customer);
    ...
}

And the code below is for GetByName.

using (var repository = new CustomerRepository())
{
    var customer = repository.GetByName("John Doe");
    ...
}

Register your Repository as Service Component

Now, in order for you to be able to use the Repository in your project as an injectable object within your controllers, you have to register it as a service component.

To register as a service component, follow the steps below.

• In your Solution Explorer, double-click the Startup.cs file.
• Navigate inside ConfigureServices() method and paste the code below before the method end.

services.AddTransient<ICustomerRepository, CustomerRepository>();

• Resolve the missing namespaces by placing the mouse inside the CustomerRepository and press Ctrl+Space > Enter.
• Press Ctrl+S keys to save the changes.

The engine will register the CustomerRepository object (as implemented by ICustomerRepository interface) into the services collection. Once the registration is complete, it signifies that our CustomerRepository class is now ready to be used for injection.

Note: We can as well add it as Singleton via AddSingleton() method of IServiceCollection if we wish to have our Repository in a singleton mood.

Create a Controller

Follow the steps below to create a Controller for your Customer entity.

• Inside the Controllers folder, add a new class named CustomerController.cs.
• The new file named CustomerController.cs will be created. Replace the class implementation with the script below.

[Route("api/[controller]")]
[ApiController]
public class CustomerController : ControllerBase
{
    private ICustomerRepository m_customerRepository;

    public CustomerController(ICustomerRepository repository)
    {
        m_customerRepository = repository;
    }
}

• Press Ctrl+S keys to save the changes.

The Controller class above accepts an injected ICustomerRepository service component. We hold an instance of Interface, not Class.

The engine will do the magic for injecting the ICustomerRepository after your service component registration.

Create a Get Method from your Controller

We are almost done! :)

The provided methods below will call the CustomerRepository operation. Please copy the provided code snippets below and paste it inside CustomerController class just right after the Constructor.

[HttpGet()]
public ActionResult<IEnumerable<Customer>> Get()
{
    return m_customerRepository.GetAll().AsList();
}

[HttpGet("{id}")]
public ActionResult<Customer> Get(long id)
{
    return m_customerRepository.GetById(id);
}

Testing the Controller

At this point, our solution is now ready for testing. Build the solution by simply pressing the Alt + B + R keys.

Once the build is complete, press the F5 key to start.

In the browser, type the URL below.

http://localhost:44341/customer

You can also query the specific records with the link below.

http://localhost:44341/customer/1

Where the value 1 is equals to the Customer id you have in the database.

You will notice that the result is being displayed in the browser as JSON file.

How to find a port?

• Right-click on the project InventoryAPI from the Solution Explorer and click the Properties.
• Click the Debug tab.
• Under Web Server Settings, you will see the App URL field that contains the port.

Links

You can see and download the actual project here.

Voila! You have completed this tutorial! Thank you for reading this article. Your support is valuable to us, please do not forget to star our GitHub page.