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

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

1. Auto Interface Implementation
2. SQL Attribute with Async
3. Pass SQL Table-Valued Parameters in a single line
4. Get multiple result Structures
5. Dependency Injection with .NET Core
6. Unit test WeatherForecastController with MOQ
7. 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

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

2. Create a Model or POCO for Beer Table:
   C#

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

3. 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);
   }

4. Access the Repository Interface from constructor of Service Layer or Controller:
   C#

   public WeatherForecastController()
   {
       // Create an instance
       DbConnection c = new SqlConnection(connectionString);
       IBeerRepository i = c.As<IBeerRepository>();
   
       // Call the Stored Procedure
       var results = i.GetBeerByType("ipa");
   }
   

5. 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:
   C#

   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

1. Install .NET Core 3.1.0 or above SDK.
2. Install Visual Studio 2019 and SQL Server Management Studio.
3. 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

1. Create a Stored Procedure to Get:
   SQL

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

2. Create an Interface for WeatherForecast as IWeatherForecastRepository.cs:

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

   C#

   namespace Insight.Database.Demo.Part1.Repository
   {
       using System;
       using System.Collections.Generic;
       using System.Linq;   
   
       public interface IWeatherForecastRepository
       {
           List<WeatherForecast> GetAllWeatherForecast();
       }
   }

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

   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;
   }
   

4. Get WeatherForecast details from Repository:
   C#

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

5. Result in swagger:

   

Table data for your reference:

2. Simple WeatherForecast Get with SQL Attribute with Async

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

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

2. Update IWeatherForecastRepository.cs with Async method and SQL Attribute:
   C#

   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();
       }
   }

3. Get WeatherForecast details from Repository:
   C#

   [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;
   }
   

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

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

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

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

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

4. Update WeatherForecastController.cs:
   C#

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

5. 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:

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

   

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

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

   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);
       }
   }

4. Update WeatherForecastController.cs:
   C#

   [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);
   }

5. Results in swagger:

   

   C#

   {
     "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