Creating a Standard ASP.NET Core Web API Infrastructure with RepoDB as an ORM

Technologies/Tools Used

  • .NET Core 3.1 as Framework
  • Swagger as API Frontend
  • RepoDB as ORM Tool
  • MS SQL Server 2017 as Database
  • Visual Studio 2019 as Development Interface

Create Solution and the Web Project

Create an empty .NET Core API Project:

Setup Swagger as an API Interface:

Right click the project and hit “Manage Nuget Packages”

In “Nuget Explorer” window, Select “Browse” tab and write “swagger”.

Install the library named “Swashbuckle.AspNetCore”.

Copy and paste this code block into Startup.cs => ConfigureServices(…)

services.AddMvc();
services.AddOptions();
services.AddSwaggerGen(c =>
{
   c.SwaggerDoc("v1", new OpenApiInfo { Title = "My API", Version = "v1" });
});

Resolve missing dependency for OpenApiInfo object.

Go to Configure Method in same place and paste following code:

app.UseSwagger();
app.UseSwaggerUI(c =>
{
    c.SwaggerEndpoint("/swagger/v1/swagger.json", "MYAPI V1");
});

Navigate to Properties => launchSettings.json to modify startup page to show swagger UI when our application runs:

Hit F5 to test initial run with swagger.

Create Core and Business Projects

Now we will add one project for Business layer and one for Entities. Navigate File => New => Project and Create a Class Library Project (.NET Core):

In the end your Solution Explorer should look like this:

and your folder structure should look like this:

Finally we need to add necessary nuget libraries to use RepoDB in these newly created projects. Open Nuget Manager and add RepoDB package to Core Project:

And Install RepoDB.SqlServer Package to Business Project.

Scenario

I aim to keep it simple but not so simple as standard examples in web. I would also like to show you the minimum viable implementation of RepoDB. So in order to achieve that I am going to follow the following scenario:

  • Create a DB with Two Tables: Products and ProductGroups
  • Create an Entity for those two tables: Product and ProductGroup
  • Create a Business Layer for Product and Product Group Repositories
  • Create Controllers for Product and ProductGroup

Implementation

Database

Create a SQL Database and name it RepoDBTestDB. Add following tables with following properties:

CREATE TABLE [dbo].[ProductGroups](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[Code] [nvarchar](50) NOT NULL,
	[Name] [nvarchar](255) NOT NULL,
	[AuditStatus] [int] NOT NULL,
	[CreatedDate] [smalldatetime] NOT NULL,
	[CreatedBy] [nvarchar](50) NOT NULL,
	[LastUpdatedDate] [smalldatetime] NULL,
	[LastUpdatedBy] [nvarchar](50) NULL,
 CONSTRAINT [PK_ProductGroups] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ProductGroups] ADD  CONSTRAINT [DF_ProductGroups_AuditStatus]  DEFAULT ((0)) FOR [AuditStatus]
CREATE TABLE [dbo].[Products](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[Code] [nvarchar](50) NOT NULL,
	[Name] [nvarchar](255) NOT NULL,
	[Price] [float] NULL,
	[ProductGroupId] [bigint] NOT NULL,
	[AuditStatus] [int] NOT NULL,
	[CreatedDate] [smalldatetime] NOT NULL,
	[CreatedBy] [nvarchar](50) NOT NULL,
	[LastUpdatedDate] [smalldatetime] NULL,
	[LastUpdatedBy] [nvarchar](50) NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Products] ADD  CONSTRAINT [DF_Products_ActiveStatus]  DEFAULT ((0)) FOR [AuditStatus]
GO

ALTER TABLE [dbo].[Products]  WITH CHECK ADD  CONSTRAINT [FK_Products_ProductGroups] FOREIGN KEY([ProductGroupId])
REFERENCES [dbo].[ProductGroups] ([Id])
GO

ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_ProductGroups]
GO

Fill with sample Data:

RepoDBTestDB_Data.xlsDownload

(also available as a database project. See end of this post)

Core Objects

After we finished database level tasks, we can now concentrate on Core/Entity design.

There are really various methods/approaches to handle Entity designs. I will intentionally stay away from any discussions regarding it and therefore will use the existing data structure directly for the presentation too, which means I am not going to create a “Model” (or View Model) and every entity will have the same structure as it is kept in a database.

Anyway. Let us concentrate more on RepoDB. RepoDB has a built-in entity mapping functionality. If your classes and properties have same names with your table, it would be able to map the data to your classes without any other extra configuration. However if you have different names, then you have to define a mapping configuration. To map your classes to the data we either decorate our classes by using RepoDB’s built-in MapAttribute or use its fluent mapping configuration. I will be using Decoration-Method both for Product and ProductGroup entities.

First I’ve created a Base Entity class to gather common properties into a single class to avoid repeating everywhere. I’ve also decorated Id property as a Primary key with Identity values. Properties that I’ve set here will be inherited by child classed which are derrived from this class.

public class BaseEntity
{
    [Primary]
    [Identity]
    public long Id { get; set; }
    public DateTime CreatedDate { get; set; }
    public string CreatedBy { get; set; }
    public DateTime? LastUpdatedDate { get; set; }
    public string LastUpdatedBy { get; set; }
}

Primary and Identity (which is pretty self documenting) are two property attributes of RepoDB which marks decorated column as a Primary key and sets its the identity specification to 1,1.

Now we create the Products class and inherit from BaseEntity.

[Map("Products")]
public class Product: BaseEntity
{
    public string Code { get; set; }
    public string Name { get; set; }
        
    [Map("Price")]
    public float RetailPrice { get; set; }
    public long ProductGroupId { get; set; }
    public int AuditStatus { get; set; }
}

As you see, in order to change the standard mapping we used MapAttribute. This attribute can be applied both for classes and for its properties. Product Entity in my model will correspond to Products table in my database and RetailPrice property in my class will map to Price column in that table respectively.

I’ve also created ProductGroup class as follows:

[Map("ProductGroups")]
public class ProductGroup:BaseEntity
{
    public string Code { get; set; }
    public string Name { get; set; }
    public int AuditStatus { get; set; }
}

In the end our Core project structure should be like that:

Business Layer

I’ve created three different sub folders to contain my Repositories, the Interfaces for common things such as Dependency Injection and its default implementations. I will be allowing injection of connection string and some other connection properties by the caller. So that I’ve created an IAppSettings interface with following properties:

public interface IAppSettings
{
    public string ConnectionString { get; set; }
    public int CommandTimeout { get; set; }
}

And also created a class under the Implementations folder to hold values for default implementation of the IAppSettings interface, as following:

public class AppSettings : IAppSettings
{
    public string ConnectionString { get; set; }
    public int CommandTimeout { get; set; } = 600;
}

Now I will create two repositories under Repositories folder. One for managing Products and second for managing ProductGroups.

RepoDB enables us to use two standard classes to implement Repository Pattern in our model. We can decide to implement Repository pattern as a generic implementation of “fancy data access method” or we can design our own aggregates. Or even better: we can employ both methods in same model. For managing a basic table (where one Aggregate Root is one single table) we can easily inherit from RepoDB’s default data access implementation: BaseRepository<TEntity,IDbConnection>.

public class ProductRepository : BaseRepository<Product, SqlConnection>
{
   //... Constructor
}

There are several constructors for this repository. I don’t want to dive into detail of various constructors however I strongly encourage you to read the details from RepoDb’s documentation (which is pretty good btw). I will be using the one with connection string and the command timeout.

public class ProductRepository : BaseRepository<Product, SqlConnection>
{
    IAppSettings settings;
    public ProductRepository(IAppSettings settings) : base(settings.ConnectionString, settings.CommandTimeout)
    {
        this.settings = settings;
    }
}

public class ProductGroupRepository : BaseRepository<ProductGroup, SqlConnection>
{
    IAppSettings settings;
    public ProductGroupRepository(IAppSettings settings) : base(settings.ConnectionString, settings.CommandTimeout)
    {
        this.settings = settings;
    }
}

When Business Layer is completed, finally it should look like this:

API Controllers

Normally it is pretty straight forward to inject implementations, add a controller and call the respective respository to expose to the caller. Since we’ve done with the Swagger, it is also self documenting. Therefore I plan to show you a basic implementation for Product and ProductGroup controllers and leave the professional implementation (with dependency injection of repositories, with repository factory and/or using IUnitOfWork) for another post.

Let us start with the ProductsController. Simply open the Api project, navigate to Controllers and add a new controller named ProductsController and paste the following code into the controller directly:

[Route("api/[controller]")]
[ApiController]
public class ProductsController : ControllerBase
{
    private IOptions<AppSettings> settings;

    public ProductsController(IOptions<AppSettings> settings)
    {
        this.settings = settings;
    }
        
    [HttpGet]
    public ActionResult Get()
    {
        var r = new ProductRepository(settings.Value);
        return new ObjectResult(r.QueryAll());
    }

    [HttpGet("{id}")]
    public ActionResult Get(long id)
    {
        var r = new ProductRepository(settings.Value);
        return new ObjectResult(r.Query(p=>p.Id==id));
    }

    [HttpPost]
    public void Post([FromBody] Product value)
    {
        var r = new ProductRepository(settings.Value);
        r.Insert<long>(value);
    }

    [HttpPut("{id}")]
    public void Put(long id, [FromBody] Product value)
    {
        var r = new ProductRepository(settings.Value);
        r.Update(value);
    }

    [HttpDelete("{id}")]
    public void Delete(long id)
    {
        var r = new ProductRepository(settings.Value);
        r.Delete(id);
    }
}

Also add the ProductGroupsController.

Before we run our project we have to do two things. Initialization of RepoDB SqlServer and an injection of connection string information into our repositories by looking it from our appsettings.json.

To initialize RepoDB.SqlServer, go to startup.cs and call initialize method of RepoDb SQL Server Bootstrap inside the constructor.

To inject the connection string, we have to add json formatted deserializable text version of AppSettings class that we’ve previously created into the appsettings.json file. We have to update the connection string to point our database.

And add it into development configuration of the appsettings.json as well.

"SettingsFromApi": {
    "ConnectionString": "Server=.;Database=RepoDBTestDB;Integrated Security=SSPI;",
    "CommandTimeout": 800
  }

Now go to Startup.cs and paste following code block for your app to read the config from the settings json file, deserializes it and injects it as a part of standard IOptions interface of .Net Core Framework:

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllers();

    services.AddMvc();
    services.AddOptions();
    services.AddSwaggerGen(c =>
    {
        c.SwaggerDoc("v1", new OpenApiInfo { Title = "RepoDB API", Version = "v1" });
    });

    services.AddOptions<AppSettings>().Bind(Configuration.GetSection("SettingsFromApi"));

}

Finally the folder structure of your api project should look like that:

Run your project and enjoy 🙂

Complete code for this post could be found at: https://github.com/onuromer/BlogPostSamples-RepoDBTrials