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.
Top comments (7)
this tutorial doesn t work , can you show this project on Git, not all libraries(github.com/mikependon/RepoDb), only this project , thank you!(link to my project github.com/AssetOtinchiev/Inventor...)
Thank you for following this tutorial.
Yes, you are correct! We made a small change on the blog to remove the gaps of the ConnectionString. We just added a note in the blog that the ConnectionString must be injected via Settings object.
In addition, we have added one method called
GetAll()
to return all the Customer from the database.Here is the link of the sample project.
Thank you for operative answer , happy new year)
Thanks a lot for the great article! I will use the library in my next project.
Just one note:
m_customerRepository? do you really use this outdated notation? isn't the underscore enough?
Hi, thank your for your interests and also for the comments and feedback. The entire RepoDb is still using the same notation pertaining to local variables declaration. That is up for refactoring soon.
There is an article that is related to a "powerful" repository that you might be interested on using on your own project. You can see the blog here (medium.com/dev-genius/building-a-p...). The repository can be downloaded as a gist directly from my GH account.
Does this library support the unit of work?
Hi, yes! The implementation is a bit lower level when compared to EF and NH. You have a lot of control; you fell like coding with ADO.net via fluent operations.
Basically, all methods can be wrapped into a single unit of activity. It has built in repositories which you can as well leveraged.
We suggest to you to read this page.