Insight.Database , .NET Micro ORM - Write Less Code In Data Access Layer Using Auto Interface Implementation

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
• Github Wiki
• Demo Project URL

Please check previous articles

Part 1: Insight.Database, .NET Micro ORM - Write Less Code In Data Access Layer Using Auto Interface Implementation

Part 2:  Insight.Database .NET Micro ORM - Executing And Carrying Out SELECT SQL Commands

Part3: 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

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

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

ii. Create a Model or POCO for Beer Table

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

iii. Create an Interface for Repository (You don't need a concrete class that implement interface)

Note

As you see SQL Procedure name "GetBeerByType" and Interface method name "GetBeerByType" are 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);
}

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

v. That's it. You don't need to create a Concrete Repository class that 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 with 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 a 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

2. Simple Get method to get all WeatherForecast details

i. Create a Stored Procedure to Get 

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

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

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

iv. Get WeatherForecast details from Repository

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

v. Result in swagger

Table data for you reference

2. Simple WeatherForecast Get with SQL Attribute with Async

i. Create a new Stored Procedure with naming standard "P_GetAllWeatherForecast" to Get 

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

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

    }
}

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

iv. 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 in passing TVP to procedure when compared to other ORMs.

i. Create a 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
)

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

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

iv. Update WeatherForecastController.cs

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

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

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

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

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

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

v. 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 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 Sturcture

Will be continued........