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:
Create an MS SQL database called ProductsDB
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