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)

Nov 6, 2021

CPOL

3 min read

10551

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

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:

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.
```
public interface IBeerRepository
{        
    IList<Beer> GetBeerByType(string type);
}
```
**OR**
```
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

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:

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:

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:

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:

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:

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).

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