Creating a C# Repository in .NET Core using RepoDb
Tags: csharp, dotnet, sql, beginners
Published: 2020-01-02 16:08:33

---
title: Creating a C# Repository in .NET Core using RepoDb
published: true
description: This is an article about creating a dynamic C# Repository using RepoDb
tags: csharp, dotnet, sql, beginner
---

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.

```csharp
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.

```csharp
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.

```csharp
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.

```csharp
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.

```csharp
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.

```csharp
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.

```csharp
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*:

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

For *GetByName*:

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

For *GetAll*:

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

For *Insert*:

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

For *Update*:

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

For *Delete*:

```csharp
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.

```csharp
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*.

```csharp
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.
```csharp
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.
```csharp
[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*.

```csharp
[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](https://github.com/mikependon/Tutorials/tree/master/Blogs/DEV.to/BaseRepository/InventoryAPI).

--------

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](https://github.com/mikependon/RepoDb) page.