top of page

The easiest .NET ORM framework

There are plenty of awesome ORMs out there but when it comes to the easiest to learn and use by far is Insight.Database


What’s Insight.Database?

Simply put, it’s a super easy and lightweight micro-ORM for .NET, it is available as a NuGet package and it works with major database providers (see here for a list of supported providers).


Installing & Registering Insight

1- Install package Install-Package Insight.Database

2- Install provider-specific package (e.g. Insight.Database.Providers.MySql))if you’re using a database other than MS SQL

3- Register your provider in application startup, note that I’m using MS SQL Server in this example SqlInsightDbProvider.RegisterProvider();


Insight in Action

Insight is a set of extension methods to the database connection class, and you can use it in different ways, the one I like best is by defining interfaces that match stored procedures in my database, this allows me to call stored procedures by invoking the interface methods, sounds great no!


1- Define the interface


Product Repository


2- Register the interface


Register interface as scoped service


The above code registers :

1- The IDbConnection with concrete type ReliableConnection

2- A multithreaded connection implementing the given interface, IProductRepository in this case.


This is pretty much it! Insight will automagically connect your interface to the stored procedures, all you need to do now is to inject and invoke a method on your interface.


Using the repository


If you’re wondering where do we open/close the connection, well, we don’t have to worry about that as Insight does this on behalf of us


Advanced Scenarios

In this section, I’ll cover more advanced scenarios that are more likely to be encountered when working with Insight


Parent-Child relationship

Insight can handle one to many or many to many relationships between two entities, so let’s say you want to retrieve top X products per category, that is retrieve a list of all categories along with the top X products for each category.


1 Category can have multiple products


Below is a query that returns all categories followed by the top products and we need to criss-cross combine the 2 result sets to match the above hierarchy

Parent-Child query


First, we need to add TopProducts child property to ProductCategory class as in the below:


Next, add the Recordset attribute to define the relationship:


Parent-Child relationship

  • Index parameter (optional) is the index of the result set in case you have multiple children, here it’s 1, which means the second query after the main one will be mapped into the TopProducts

  • Id parameter is the name of the Parent ID column, which is column “Id” of the parent table ProductCategory

  • GroupBy parameter is the name of the Child ID column, which is column “CategoryId” of child table Product

  • Into parameter is the property name to which the result set will be assigned to.


Multiple Result Sets

Insight enables you to return multiple results as well, let’s say we want to perform paging over products so we need to return products in batches of X and we also need to know what is the total number of products in one round trip.


The first thing we need to do here is to define a class that will be used to map the two result sets (products list and total count):


a- Inherit from Insight.Database.Results abstract class which is used to encapsulate multiple types of data T1,T2,T3…. where T1 is T (generic type) the first result set, and T2 is int (Total count) the second result set


Multiple results


of course, I could have done it the following way but this is not reusable


multiple products


b- Add a method that returns the above type and map it to the stored procedure to be invoked:


Multiple resultsets


c- Finally, create the multi-result sets stored procedure that matches the new result type


Stored proc with multiple result sets


Let’s test it!

A sample project source code can be found here.


The project is using FluentMigrator to create database migrations (see here for an introduction to FluentMigrator), All you need to is:

  1. Create an MS SQL database called ProductsDB

  2. Update the appsettings.json connections strings to point to your server, it uses two connection strings pointing to the same database, the Migrator is used by the fluent migrator to update the schema as per the migrations (SQL user has higher privilige) while the second is used by the API/Repository to query and eventually CRUD the data.

Running swagger will display the following endpoints:

endpoints


Paged endpoint sample result:

paged endpoint result


Wrap Up

This story introduced the Insight.Database library, a lightweight and simple to use ORM. It empowers developers allowing them to write data access code in less time and effort and at the same time requiring a very low learning curve.



Source: Medium - Mohammed Hamdan


The Tech Platform

0 comments

Comments


bottom of page