Insight.Database - .NET Micro ORM, Part 1 - Write Less Code in Data Access layer using Auto Interface Implementation

keerthi raja

3.29/5 (4 votes)

7 Nov 2021CPOL3 min read

10.6K

An overview of Insight.Database, micro-orm for .NET

Insight.Database is a fast, lightweight, (and dare we say awesome) micro-orm for .NET extends your IDbConnection, simplifying query setup, execution, and result-reading.

Why Do You Want Insight.Database?

Insight.Database has all the common features of other .NET Micro-ORMs. But this article explains more about exclusive and interesting features in Insight.Database compared to other ORMs.

Github source code: https://github.com/jonwagner/Insight.Database
Github Wiki: https://github.com/jonwagner/Insight.Database/wiki
Demo Project URL: https://github.com/keerthirajap/Insight.Database.Demo

Please check my previous articles:

Part 1: Insight.Database - .NET Micro ORM, Part 1 - Write Less Code in Data Access layer using Auto Interface Implementation
Part 2: Insight.Database - .NET Micro ORM, Part 2 - Executing And Carrying Out SELECT SQL Commands
Part 3: Coming soon

Topics

Auto Interface Implementation
SQL Attribute with Async
Pass SQL Table-Valued Parameters in a single line
Get multiple result Structures
Dependency Injection with .NET Core
Unit test WeatherForecastController with MOQ
MOQ Multiple Result Sets

1. Auto Interface Implementation

I can say Auto Interface Implementation is an exclusive feature here, where we can write 1 or 2 lines of code in Interface to perform a complete database operation even if it's complex.

Quick Tour

Create a Procedure to search in Beer table as given below:

SQL

CREATE PROC GetBeerByType @type [varchar] AS 
      SELECT * FROM Beer WHERE Type = @type 
GO

Create a Model or POCO for Beer Table:

class Beer
{
    public string Name;
    public string Flavor;        
}

Create an Interface for Repository (You don't need a concrete class that implements interface.)
Note: As you see, SQL Procedure name "GetBeerByType" and Interface method name "GetBeerByType" are the same. But don't worry, you can use SQL Attribute too for better naming convention.

C#
```
public interface IBeerRepository
{        
    IList<Beer> GetBeerByType(string type);
}
```
**OR**

C#
```
public interface IBeerRepository
{
    [Sql("[dbo].[GetBeerByType]")]    // For better understanding    
    IList<Beer> GetBeerByType(string type);
}
```

Access the Repository Interface from constructor of Service Layer or Controller:

public WeatherForecastController()
{
    // Create an instance
    DbConnection c = new SqlConnection(connectionString);
    IBeerRepository i = c.As<IBeerRepository>();

    // Call the Stored Procedure
    var results = i.GetBeerByType("ipa");
}

That's it! You don't need to create a Concrete Repository class which implements Repository Interface. Because, underneath it all, Insight is creating an Anonymous class like this at runtime:

class Anonymous : DbConnectionWrapper, IBeerRepository
{
    public IList<Beer> GetBeerByType(string type)
    {
        return InnerConnection.ExecuteSql("GetBeerByType", new { type = type });
    }
}

Let's Proceed Further on this Tutorial

Prerequisites

Install .NET Core 3.1.0 or above SDK.
Install Visual Studio 2019 and SQL Server Management Studio.
SQL Server 2008 R2 or above.

Create a new database and execute the SQL scripts given below.

Create Database and Sample Table

SQL

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE DATABASE [Insight.Database.Demo]
GO

USE [Insight.Database.Demo]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE _
object_id = OBJECT_ID(N'[dbo].[WeatherForecast]') AND type in (N'U'))
DROP TABLE [dbo].[WeatherForecast]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[WeatherForecast](
    [WeatherForecastId] [int] IDENTITY(100,1) NOT NULL,
    [Date] [datetime] NULL,
    [TemperatureC] [int] NULL,
    [Summary] [nvarchar](max) NULL,
 CONSTRAINT [PK_dbo.WeatherForecastId] PRIMARY KEY CLUSTERED 
(
    [WeatherForecastId] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SET IDENTITY_INSERT [dbo].[WeatherForecast] ON 
GO
INSERT [dbo].[WeatherForecast] ([WeatherForecastId], [Date], [TemperatureC], _
[Summary]) VALUES (106, CAST(N'2021-09-09T00:00:00.000' AS DateTime), 45, N'Scorching')
GO
INSERT [dbo].[WeatherForecast] ([WeatherForecastId], [Date], [TemperatureC], _
[Summary]) VALUES (105, CAST(N'2021-09-10T00:00:00.000' AS DateTime), 35, N'Sweltering')
GO
INSERT [dbo].[WeatherForecast] ([WeatherForecastId], [Date], [TemperatureC], _
[Summary]) VALUES (104, CAST(N'2021-09-11T00:00:00.000' AS DateTime), 25, N'Hot')
GO
INSERT [dbo].[WeatherForecast] ([WeatherForecastId], [Date], [TemperatureC], _
[Summary]) VALUES (103, CAST(N'2021-09-12T00:00:00.000' AS DateTime), 0, N'Chilly')
GO
INSERT [dbo].[WeatherForecast] ([WeatherForecastId], [Date], [TemperatureC], _
[Summary]) VALUES (102, CAST(N'2021-09-13T00:00:00.000' AS DateTime), 10, N'Warm')
GO
INSERT [dbo].[WeatherForecast] ([WeatherForecastId], [Date], [TemperatureC], _
[Summary]) VALUES (101, CAST(N'2021-09-14T00:00:00.000' AS DateTime), 5, N'Mild')
GO
INSERT [dbo].[WeatherForecast] ([WeatherForecastId], [Date], [TemperatureC], _
[Summary]) VALUES (100, CAST(N'2021-09-15T00:00:00.000' AS DateTime), -5, N'Freezing')
GO
SET IDENTITY_INSERT [dbo].[WeatherForecast] OFF
GO

Simple Get Method to Get All WeatherForecast Details

Create a Stored Procedure to Get:

SQL

CREATE PROC GetAllWeatherForecast
AS
    SELECT * FROM [dbo].[WeatherForecast]
GO

Create an Interface for WeatherForecast as IWeatherForecastRepository.cs:

Note: As you see, SQL Procedure name and Interface method name are the same.

namespace Insight.Database.Demo.Part1.Repository
{
    using System;
    using System.Collections.Generic;
    using System.Linq;   

    public interface IWeatherForecastRepository
    {
        List<WeatherForecast> GetAllWeatherForecast();
    }
}

Create an Instance for IWeatherForecastRepository.cs in Controller's Constructor.

private readonly DbConnection _sqlConnection;

private readonly IWeatherForecastRepository _weatherForecastRepository;

public WeatherForecastController(ILogger<WeatherForecastController> logger)
{
    this._sqlConnection = new SqlConnection("Data Source=.;
    Initial Catalog=Insight.Database.Demo;Persist Security Info=true;
                    Integrated Security=true;");

    this._weatherForecastRepository =
          this._sqlConnection.As<IWeatherForecastRepository>();

    _logger = logger;
}

Get WeatherForecast details from Repository:

[HttpGet]
public List<WeatherForecast> Get()
{
    List<WeatherForecast> weatherForecasts = new List<WeatherForecast>();
    weatherForecasts = this._weatherForecastRepository.GetAllWeatherForecast();

    return weatherForecasts;
}

Result in swagger:

Table data for your reference:

2. Simple WeatherForecast Get with SQL Attribute with Async

Create new Stored Procedure with naming standard "P_GetAllWeatherForecast" to Get:

SQL

CREATE PROC P_GetAllWeatherForecast
AS
    SELECT * FROM [dbo].[WeatherForecast]
GO

Update IWeatherForecastRepository.cs with Async method and SQL Attribute:

namespace Insight.Database.Demo.Part1.Repository
{
    using System;
    using System.Collections.Generic;
    using System.Linq;  
    using System.Threading.Tasks; 

    public interface IWeatherForecastRepository
    {
        [Sql("[dbo].[P_GetAllWeatherForecast]")]
        Task<List<WeatherForecast>> GetAllWeatherForecastAsync();
    }
}

Get WeatherForecast details from Repository:

[HttpGet]
[Route("GetWeatherForecast-SQL-Attribute-Async")]
public async Task<List<WeatherForecast>> GetAllWeatherForecastAsync()
{
    List<WeatherForecast> weatherForecasts = new List<WeatherForecast>();
    weatherForecasts =
           await this._weatherForecastRepository.GetAllWeatherForecastAsync();

    return weatherForecasts;
}

Result in swagger:

3. Pass SQL Table-Valued Parameters in a Single Line

I can say this is a wonderful feature in Insight.Database, which reduces a lot of pain on passing TVP to procedure when compared to other ORMs.

Create new Table-Valued Parameter with respect to WeatherForecast Table:

SQL

CREATE TYPE T_AddWeatherForecast AS TABLE (
    [WeatherForecastId] [int],
    [Date] [datetime] NULL,
    [TemperatureC] [int] NULL,
    [Summary] [nvarchar](max) NULL
)

Create new Stored Procedure with Table-Valued Parameter:

SQL

ALTER PROC P_AddWeatherForecasts 
(@WeatherForecasts  [T_AddWeatherForecast] READONLY)
 AS

    INSERT INTO [dbo].[WeatherForecast]
               ([Date]
               ,[TemperatureC]
               ,[Summary])
    SELECT [Date]
          ,[TemperatureC]
          ,[Summary]
      FROM @WeatherForecasts

GO

Update IWeatherForecastRepository.cs with a new method to Add multiple WeatherForecast details by passing List<WeatherForecast>.

namespace Insight.Database.Demo.Part1.Repository
{
    using System;
    using System.Collections.Generic;
    using System.Linq;  
    using System.Threading.Tasks; 

    public interface IWeatherForecastRepository
    {
        [Sql("[dbo].[P_AddWeatherForecasts]")]
        Task AddWeatherForecastsAsync(List<WeatherForecast> WeatherForecasts);
    }
}

Note: Parameter for Stored Procedure's TVP and Parameter for AddWeatherForecastsAsync method are the same "WeatherForecasts". That's how ORM works. ;-)

Update WeatherForecastController.cs:

[HttpPost]
[Route("AddWeatherForecasts")]
public async Task<IActionResult> AddWeatherForecastsAsync
                 ([FromBody] List<WeatherForecast> weatherForecasts)
    {
        await this._weatherForecastRepository.AddWeatherForecastsAsync
              (weatherForecasts);

        return Ok();
    }

Post Array of WeatherForecast to Controller:

New records in database:

4. Get Multiple Result Structures

This is one awesome feature in Insight.Database, we can get two or more (select) results from procedure or queries in a single line. Of course, we can do this other ORM or ADO.NET, but here code will be clean and simple.

I had came into a scenario to show two tables in UI, and this package solved in single DB call, rather using two DB calls:

Create another table SummaryDim:

SQL

CREATE TABLE [dbo].[SummaryDim](
    [SummaryId] [int] IDENTITY(1000,1) NOT NULL,    
    [Summary] [nvarchar](max) NULL,
 CONSTRAINT [PK_dbo.SummaryId] PRIMARY KEY CLUSTERED 
(
    [SummaryId] DESC
)
)

Create a new Stored Procedure to select multiple results of both [WeatherForecast] and [dbo].[SummaryDim] table. If needed, pass TVP (just to show you how simple it is).

SQL

CREATE PROC P_GetAddWeatherForecastsAndSummary
(@WeatherForecasts  [T_AddWeatherForecast] READONLY)
 AS

    INSERT INTO [dbo].[WeatherForecast]
               ([Date]
               ,[TemperatureC]
               ,[Summary])
    SELECT [Date]
          ,[TemperatureC]
          ,[Summary]
      FROM @WeatherForecasts

     SELECT  * FROM [dbo].[WeatherForecast]
     SELECT  * FROM [dbo].[SummaryDim]

GO

Update IWeatherForecastRepository.cs with a new method to get multiple results, add multiple WeatherForecast details by passing List<WeatherForecast>.

namespace Insight.Database.Demo.Part1.Repository
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Threading.Tasks;

    public interface IWeatherForecastRepository
    {  
        [Sql("[dbo].[P_GetAddWeatherForecastsAndSummary]")]
        Task<Results<WeatherForecast, SummaryDim>> 
        GetAddWeatherForecastsAndSummaryAsync
           (List<WeatherForecast> WeatherForecasts_New);
    }
}

Update WeatherForecastController.cs:

[HttpPost]
[Route("GetAddWeatherForecastsAndSummary")]
public async Task<IActionResult> 
GetAddWeatherForecastsAndSummaryAsync([FromBody] 
             List<WeatherForecast> weatherForecasts_new)
{
    List<WeatherForecast> weatherForecasts = new List<WeatherForecast>();
    List<SummaryDim> summaries = new List<SummaryDim>();

    var result = await this._weatherForecastRepository.
                 GetAddWeatherForecastsAndSummaryAsync(weatherForecasts_new);

    weatherForecasts = result.Set1.ToList();
    summaries = result.Set2.ToList();

    dynamic returnVal = new System.Dynamic.ExpandoObject();
    returnVal.weatherForecasts = weatherForecasts;
    returnVal.summaries = summaries;

    return Ok(returnVal);
}

Results in swagger:

{
  "weatherForecasts": [
    {
      "weatherForecastId": 112,
      "date": "2021-11-06T10:08:23.66",
      "temperatureC": 101,
      "temperatureF": 213,
      "summary": "string_101"
    },
    {
      "weatherForecastId": 111,
      "date": "2021-11-05T10:08:23.66",
      "temperatureC": 100,
      "temperatureF": 211,
      "summary": "string_100"
    },
    {
      "weatherForecastId": 110,
      "date": "2021-11-06T10:08:23.66",
      "temperatureC": 101,
      "temperatureF": 213,
      "summary": "string_101"
    }
  ],
  "summaries": [
    {
      "summaryId": 1007,
      "summary": "Hot"
    },
    {
      "summaryId": 1006,
      "summary": "Balmy"
    },
    {
      "summaryId": 1005,
      "summary": "Warm"
    }
  ]
}

5. Dependency Injection with .NET Core for IWeatherForecastRepository

Simplified code here in startup.cs:

services.AddScoped(b => this._sqlConnection.AsParallel<IWeatherForecastRepository>());

Whole code:

Startup.cs

public class Startup
{
    public Startup(IConfiguration configuration)
    {
        Configuration = configuration;
        this._sqlConnection = new SqlConnection("Data Source=.;
        Initial Catalog=Insight.Database.Demo;Persist Security Info=true;
        Integrated Security=true;");
    }

    public IConfiguration Configuration { get; }

    private readonly DbConnection _sqlConnection;

    // This method gets called by the runtime.
    // Use this method to add services to the container.
    public void ConfigureServices(IServiceCollection services)
    {
        SqlInsightDbProvider.RegisterProvider();

        services.AddControllers();

        services.AddScoped
        (b => this._sqlConnection.AsParallel<IWeatherForecastRepository>());
    }

Inject IWeatherForecastRepository to WeatherForecastController.cs

public class WeatherForecastController : ControllerBase
{
    private readonly ILogger<WeatherForecastController> _logger;

    private readonly DbConnection _sqlConnection;

    private readonly IWeatherForecastRepository _weatherForecastRepository;

    public WeatherForecastController(ILogger<WeatherForecastController> logger,
    IWeatherForecastRepository weatherForecastRepository)
    {
        this._weatherForecastRepository = weatherForecastRepository;

        _logger = logger;
    }

6. Unit Test WeatherForecastController with MOQ

public class Tests
{
    private WeatherForecastController _weatherForecastController { get; set; }

    private Mock<ILogger<WeatherForecastController>> _logger { get; set; }

    private Mock<IWeatherForecastRepository> _weatherForecastRepository { get; set; }

    [SetUp]
    public void Setup()
    {
        this._weatherForecastRepository = new Mock<IWeatherForecastRepository>();
        this._logger = new Mock<ILogger<WeatherForecastController>>();
    }

    [Test]
    public void WeatherForecastController_Get()
    {
        //Arrange
        List<WeatherForecast> weatherForecasts =
        Builder<WeatherForecast>.CreateListOfSize(5).Build().ToList();

        this._weatherForecastRepository
         .Setup(m => m.GetAllWeatherForecast())
         .Returns(weatherForecasts);

        this._weatherForecastController =
        new WeatherForecastController(this._logger.Object,
                                      this._weatherForecastRepository.Object);

        //Act
        var result = this._weatherForecastController.Get();

        //Assert

        Assert.AreEqual(result, weatherForecasts);
    }

7. MOQ Multiple Result Sets

[Test]
public async Task GetAddWeatherForecastsAndSummaryAsync()
{
    //Arrange
    List<WeatherForecast> weatherForecasts =
    Builder<WeatherForecast>.CreateListOfSize(5).Build().ToList();

    List<SummaryDim> summaries =
    Builder<SummaryDim>.CreateListOfSize(5).Build().ToList();

    var resultSet = new Results<WeatherForecast, SummaryDim>
                    (weatherForecasts, summaries);

    this._weatherForecastRepository
     .Setup(m => m.GetAddWeatherForecastsAndSummaryAsync(weatherForecasts))
     .ReturnsAsync(resultSet);

    this._weatherForecastController =
    new WeatherForecastController(this._logger.Object,
                                  this._weatherForecastRepository.Object);

    //Act
    var result =
    await this._weatherForecastController.GetAddWeatherForecastsAndSummaryAsync
            (weatherForecasts);

    //Assert
    Assert.AreEqual(result.GetType(), typeof(OkObjectResult));
}

Project Structure

Will be continued...

History

6^th November, 2021: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)