Forem: Mike Pendon (Zym)

Empowering MySQL DB Access with MySqlConnector + RepoDb

Mike Pendon (Zym) — Sat, 30 May 2020 13:04:49 +0000

Introduction

MySqlConnector is an ADO.NET data provider for MySQL Server, MariaDB, Percona Server, Amazon Aurora, Azure Database for MySQL, Google Cloud SQL for MySQL and more. It provides implementations of DbConnection, DbCommand, DbDataReader, DbTransaction — the classes needed to query and update databases from managed code. MySqlConnector claims to be the real asynchronous data provider for MySQL in .NET space. It is known to much more faster and optimal than MySql.Data data provider the Oracle has written.

RepoDb is an open-source .NET ORM that bridge the gaps between micro-ORMs and full-ORMs. It helps the developer to simplify the switch-over of when to use the BASIC and ADVANCE operations during the development. It is your best alternative ORM to both Dapper and Entity Framework. RepoDb claims to be the most-fastest and most-efficient ORM library in .NET in which even surpassed the Dapper performance by huge gap. It is best suited for the developers who wish to control the Data Access Layers with a more simple and dynamic implementations.

We are knocking on the windows of .NET/MySQL community about this newly built and released capability for MySQL DB accessibility space. With the combination of these 2 libraries, the DB accessibility for MySQL is now very powerful as both packages are offering different advantages for its users.

About the Library

The library is deployed as Nuget Package named RepoDb.MySqlConnector in which both being authored by the authors of RepoDb and MySqlConnector. The project is hosted at Github as extension of RepoDb.

The library is currently on its first version release as of writing this. The test suites (both Unit and Integration tests) were written to cover hundred of real-life business scenarios for all its operations.

By using this library, you had inheritted all the capabilities of both powerful packages in one installation.

Get Started

To get started, install the package via Package Manager Console.

> Install-Package RepoDb.MySqlConnector

Then, call the bootstrapper once.

RepoDb.MySqlBootstrap.Initialize();

Once the bootstrapping is done, then the library can then be used right away.

Or, please visit our official MySQL Get Started page for more information.

Inserting a Record

To insert a record, use the Insert method.

var person = new Person
{
    Name = "John Doe",
    Age = 54,
    CreatedDateUtc = DateTime.UtcNow
};
using (var connection = new MySqlConnection(ConnectionString))
{
    var id = connection.Insert(person);
}

To insert multiple rows, use the InsertAll method instead.

var people = GetPeople(100);
using (var connection = new MySqlConnection(ConnectionString))
{
    var rowsInserted = connection.InsertAll(people);
}

Querying a Record

To query a record, use the Query method.

using (var connection = new MySqlConnection(ConnectionString))
{
    var person = connection.Query<Person>(e => e.Id == 1);
    /* Do the stuffs for the 'person' here */
}

To query all the rows, use the QueryAll method instead.

using (var connection = new MySqlConnection(ConnectionString))
{
    var people = connection.QueryAll<Person>();
    /* Do the stuffs for the 'people' here */
}

Merging a Record

To merge a record, use the Merge method.

var person = new Person
{
    Id = 1,
    Name = "John Doe",
    Age = 57,
    CreatedDateUtc = DateTime.UtcNow
};
using (var connection = new MySqlConnection(ConnectionString))
{
    var id = connection.Merge(person);
}

To merge all the rows, use the MergeAll method instead.

var people = GetPeople(100);
people
    .AsList()
    .ForEach(p => p.Name = $"{p.Name} (Merged)");
using (var connection = new MySqlConnection(ConnectionString))
{
    var affectedRecords = connection.MergeAll<Person>(people);
}

Deleting a Record

To delete a record, use the Delete method.

using (var connection = new MySqlConnection(ConnectionString))
{
    var deletedRows = connection.Delete<Person>(1);
}

By default, it uses the primary key as the qualifier. The other fields can also be used via Linq expression.

using (var connection = new MySqlConnection(ConnectionString))
{
    var deletedRows = connection.Delete<Person>(p => p.Name == "John Doe");
}

To delete all the rows, use the DeleteAll method instead.

using (var connection = new MySqlConnection(ConnectionString))
{
    var deletedRows = connection.DeleteAll<Person>();
}

The list of primary keys can also be passed for this method.

using (var connection = new MySqlConnection(connectionString).EnsureOpen())
{
    var primaryKeys = new [] { 10045, 11001, ..., 12011 };
    var deletedRows = connection.DeleteAll<Person>(primaryKeys);
}

Updating a Record

To update a record, use the Update method.

var person = new Person
{
    Id = 1,
    Name = "James Doe",
    Age = 55,
    DateInsertedUtc = DateTime.UtcNow
};
using (var connection = new MySqlConnection(ConnectionString))
{
    var updatedRows = connection.Update<Person>(person);
}

To update all the rows, use the UpdateAll method instead.

var people = GetPeople(100);
people
    .AsList()
    .ForEach(p => p.Name = $"{p.Name} (Updated)");
using (var connection = new MySqlConnection(ConnectionString))
{
    var updatedRows = connection.UpdateAll<Person>(people);
}

By default, the Primary (or Identity) field is used as a qualifier. The other fields can also be used as the qualifiers.

var people = GetPeople(100);
people
    .AsList()
    .ForEach(p => p.Name = $"{p.Name} (Updated)");
using (var connection = new MySqlConnection(ConnectionString))
{
    var updatedRows = connection.UpdateAll<Person>(people, qualifiers: Field.From("Name", "DateOfBirth"));
}

Executing a Query

To execute a query use the ExecuteNonQuery method.

using (var connection = new MySqlConnection(ConnectionString))
{
    var affectedRecords = connection.ExecuteNonQuery("DELETE FROM `Person` WHERE Id = @Id;", new { Id = 1 });
}

To execute a query while expecting a result of class object, then use the ExecuteQuery method.

using (var connection = new MySqlConnection(ConnectionString))
{
    var people = connection.ExecuteQuery<Person>("SELECT * FROM `Person` ORDER BY Id ASC;");
    /* Do the stuffs for the 'people' here */
}

To execute a query while expecting a single result, then use the ExecuteScalar method.

using (var connection = new MySqlConnection(ConnectionString))
{
    var maxId = connection.ExecuteQuery<Person>("SELECT MAX(Id) FROM `Person`;");
}

To execute a query while expecting a result of DbDataReader, then use the ExecuteReader method.

using (var connection = new MySqlConnection(ConnectionString))
{
    using (var reader = connection.ExecuteReader("SELECT * FROM `Person` ORDER BY Id ASC;"))
    {
        /* Do the stuffs for the data reader here */
    }
}

Community Address

We are here to gather some feedback from the community pertaining to the experiences when using the library.

For any issues, you can directly visits the official channels of RepoDb.

GitHub - for any issues, requests and problems.
StackOverflow - for any technical questions.
Twitter - for the latest news.
Gitter Chat - for direct and live Q&A.

The original post can be found here. Thank you for reading this article.

Welcome to RepoDb.NET

Mike Pendon (Zym) — Sat, 18 Apr 2020 05:08:04 +0000

Welcome to RepoDb.NET. This is the official site of RepoDb library. The intention of this site is to provide and guide you as a developer with all the information needed when using the RepoDb library.

You can use the library to work with SQL Server, SQLite, MySQL and PostgreSQL Relational Database Management Systems (RDBMS).

It is here to improve the things and experiences when working with the Data Access Technology in .NET space.

About the Site

This site is powered by Jekyll and is hosted as Github Pages. The design utilizes the Material design of Google (and also the icons).

Site Map

This site has different pages that describe the organization of the contents.

Home - informative front page about the purpose of this library.
Docs - where all the links were consolidated to simplify your navigation within the site.
References - our recommended way of implementing the things.
Releases - for us to showcase the changes and improvements in all packages.
Blogs - where all the activites and news are placed.
About - for you to know more about us.

Get-Started Pages

Each supported RDBMS data providers has its own get-started page.

Site Contents

Various pages were provided and has been grouped on its purpose.

Features - it contains the capabilities of the library that you might be interested.
Operations - it contains the methods that you can use.
Classses - it contains the publicly exposed classes.
Attributes - it contains about the necessary attributes exposed by the library.
Enumerations - it contains the pages about the enumerations exposed by the library.
Interfaces - it contains the pages about the interfaces exposed by the library.
Cachers - it contains the pages about the cachers of the library.
Mappers - it contains the pages that describe all mapper classes of the library.
Extensibilities - it contains the pages that describe the extensibilities of the library.

Community

This site is open for contribution from the community. If you have something would like to place on this site, please do a PR on our repository.

Official Channels

GitHub - for any issues, requests and problems.
StackOverflow - for any technical questions.
Twitter - for the latest news.
Gitter Chat - for direct and live Q&A.

The original post can be found here. You can directly send an email at my personal email address by clicking here.

RepoDb remains as the most-fastest and most-efficient ORM in .NET Core Ecosystem (v3.1)

Mike Pendon (Zym) — Fri, 10 Apr 2020 20:25:52 +0000

Yesterday (April 9, 2020), I had issued a PR to FransBouma's RawDataAccessBencher with the latest version of RepoDb, plus with the following updates:

Upgrading the System.Data.SqlClient to version 4.8.0.
Referencing the Microsoft.Data.SqlClient version 1.1.1.
Added the Initialize() method in the BencherBase class.

By today (April 10, 2020), FransBouma reach out to me to have some collaborations about the PR (can be found here), in which was approved in few hours after.

Right after the PR's approval, I received a Tweet about the bencher's execution.

RepoDb remains as the fastest and the most-efficient in .NET ORM library in SET fetches. It is 2 years in a row as of writing this.

Please exclude the HandCoded Materializer result when the outcome of the bencher.

Result for NET Core (Set Fetches)

The actual result can be found here.

Result for NET Framework (Set Fetches)

The actual result can be found here.

Result for NET Core (Individual Fetches)

RepoDb has downed to 4th place (previous 1st place) in INDIVIDUAL fetches. See the result below.

The actual result can be found here.

The reason to this is the update at v1.10.x. There is a feature that has been introduced to RepoDb that needs to be executed in every calls to ExecuteQuery method. This would affect both the INDIVIDUAL and SET fetches. However, the library is fast enough when converting big chunk of records, the effect to SET fetches is not that much.

The actual post can be found here.

C#: Bulk Operations for .NET Core (BulkDelete, BulkInsert, BulkMerge, BulkUpdate) is now very simple with RepoDb ORM

Mike Pendon (Zym) — Sun, 01 Mar 2020 13:48:50 +0000

Introduction

We would like to share you the latest and greatest updates of RepoDb ORM. Recently, we have published the complete support to the bulk operations for SQL Server in .NET data access space.

The actual repository can be found at RepoDb.SqlServer.BulkOperations.

The updates covered the following methods.

This feature is useful if you are working with huge datasets. It is extremely improving the performance of your application, and at the same time, increasing your productivity during the development due to the simplicity of the design and implementation.

As an author of RepoDb, and being an OSS contributor, I am trying to improve the development experience of the .NET developers when it comes to data accessibility. I am doing it for free, and it will always be. In return, I only like to hear your thoughts, feedback and also the usability experiences when using this library. Through this, we can help each other to further improve this solution.

The benefits of using the Bulk Operations

Nothing else, but PERFORMANCE!

Though, the same goal can be achieved by the batch operations (i.e.: DeleteAll, InsertAll, MergeAll and UpdateAll), but, by leveraging the real bulk operations functionalities, the performance of your application will be improved drastically with a huge-percentage gap.

To explain further, when you only use the batch operations, it is only batching the multiple atomic activities. There are multiple round-trips between your application and your database server. However, when you use the bulk operations, you are bringing all the data from your application into the database server at once. In addition, it is ignoring the database specific operations (i.e.: Logging, Audits, Data-Type Checks, Constraints, etc), thus gives you maximum performance during the operation.

How does it works?

It is leveraging the ADO.NET SqlBulkCopy class from both System.Data.SqlClient and Microsoft.Data.SqlClient namespaces.

The BulkInsert operation is calling the SqlBulkCopy.WriteToServer() method underneath to bring all the data from your application into the database. No additional logic is implied. However, the BulkDelete, BulkMerge and the BulkUpdate operations has an implied logic and technique implemented.

Implied Logic

Basically, a pseudo-temporary table will be created in the database under the transaction context. It then re-uses the BulkInsert operation to bulk process the data from your application towards that pseudo-temporary table. Through this intermediary table, we brought all the data from your application into the database server at one-go.

The library will then create a CLUSTERED INDEX on the pseudo-temporary table using your qualifiers. You can pass your customized qualifiers with a list of Field objects into the qualifiers argument.

If you have not passed any values to the qualifiers argument, the PrimaryKey will be used by default. If the PrimaryKey is not present on the target table, it will use the Identity field instead. Otherwise, an exception will be thrown.

Once all the data is in the database pseudo-temporary table, an optimized SQL Statement will be used to cascade the changes towards the original table.

The generated SQL Statement will be vary on the operations you used. See the SQL Statements below per operation.

For BulkDelete

DELETE T
FROM [dbo].[OriginalTable] T
INNER JOIN [PseudoTempTable] TMP ON TMP.QualiferField1 = T.Field1 AND TMP.QualifierField2 = T.Field2;

For BulkMerge

MERGE [dbo].[OriginalTable] T
USING [PseudoTempTable] S ON S.QualiferField1 = T.Field1 AND S.QualifierField2 = T.Field2
WHEN NOT MATCHED THEN
     INSERT (...) VALUES (...)
WHEN MATCHED THEN
     UPDATE
     SET (...);

For BulkUpdate

UPDATE T
SET T.Field3 = TMP.Field3
     , T.Field4 = TMP.Field4
     , ...
FROM [OriginalTable] T
INNER JOIN [PseudoTempTable] TMP ON TMP.QualiferField1 = T.Field1 AND TMP.QualifierField2 = T.Field2;

Object Parameters

The bulk operations support the following object in all operations.

System.DataTable
System.Data.Common.DbDataReader
System.Collections.Generic.IEnumerable<T>

When you the use the IEnumerable<T> object, the library is wrapping it with a customized object named DataEntityDataReader before even calling the actual bulk operation.

Special Arguments

The arguments qualifiers and usePhysicalPseudoTempTable is provided at the BulkDelete, BulkMerge and BulkUpdate operations.

The argument qualifiers is used to define the qualifier fields to be used during the execution. It refers to the WHERE expression of SQL Statements. If not given, the PrimaryKey will be used. If not given, the Identity field will be used.

The argument usePhysicalPseudoTempTable is used to define whether a physical pseudo-table will be created during the operation. By default, a temporary table (i.e.: #TableName) is created.

Caveats

RepoDb is automatically setting the value of the options argument to SqlBulkCopyOptions.KeepIdentity when calling the (https://repodb.net/operation/bulkdelete), BulkMerge and BulkUpdate operations. Only if you have not passed any qualifiers and if your table has an Identity-PrimaryKey column. The same logic also applies if the PrimaryKey is not present but the underlying table has an Identity column.

Also, when calling the (https://repodb.net/operation/bulkdelete), BulkMerge and BulkUpdate operations, the library is creating a pseudo-temporary table behind the scene. It requires your user to have the correct privilege to create a table in your database, otherwise a SqlException will be thrown.

Getting Started

To start, simply install the library. At the Package Manager Console, type the command below.

> Install-Package RepoDb.SqlServer.BulkOperations

Then, call the boostrapper once.

SqlServerBootstrap.Initialize();

You can visit our installation page for more information.

BulkDelete

The code below only showcasing the BulkDelete via the IEnumerable<T> parameter.

using (var connection = new SqlConnection(ConnectionString))
{
    var customers = GetCustomers();
    var rows = connection.BulkDelete<Customer>(customers);
}

Or with qualifiers

using (var connection = new SqlConnection(ConnectionString))
{
    var customers = GetCustomers();
    var rows = connection.BulkDelete<Customer>(customers, qualifiers: e => new { e.LastName, e.BirthDate });
}

Or via table-name

using (var connection = new SqlConnection(ConnectionString))
{
    var customers = GetCustomers();
    var rows = connection.BulkDelete("Customer", customers);
}

Or via table-name with qualifiers

using (var connection = new SqlConnection(ConnectionString))
{
    var customers = GetCustomers();
    var rows = connection.BulkDelete("Customer", customers, qualifiers: e => new { e.LastName, e.BirthDate });
}

BulkInsert

The code below only showcasing the BulkInsert via IEnumerable<T> parameter.

using (var connection = new SqlConnection(ConnectionString))
{
    var customers = GetCustomers();
    var rows = connection.BulkInsert<Customer>(customers);
}

Or via table-name

using (var connection = new SqlConnection(ConnectionString))
{
    var customers = GetCustomers();
    var rows = connection.BulkInsert("Customer", customers);
}

BulkMerge

The codes below only showcasing the BulkMerge via IEnumerable<T> parameter.

using (var connection = new SqlConnection(ConnectionString))
{
    var customers = GetCustomers();
    var rows = connection.BulkMerge<Customer>(customers);
}

Or with qualifiers

using (var connection = new SqlConnection(ConnectionString))
{
    var customers = GetCustomers();
    var rows = connection.BulkMerge<Customer>(customers, qualifiers: e => new { e.LastName, e.BirthDate });
}

Or via table-name

using (var connection = new SqlConnection(ConnectionString))
{
    var customers = GetCustomers();
    var rows = connection.BulkMerge("Customer", customers);
}

Or via table-name with qualifiers

using (var connection = new SqlConnection(ConnectionString))
{
    var customers = GetCustomers();
    var rows = connection.BulkMerge("Customer", customers, qualifiers: e => new { e.LastName, e.BirthDate });
}

BulkUpdate

The codes below only showcasing the BulkUpdate via IEnumerable<T> parameter.

using (var connection = new SqlConnection(ConnectionString))
{
    var customers = GetCustomers();
    var rows = connection.BulkUpdate<Customer>(customers);
}

Or with qualifiers

using (var connection = new SqlConnection(ConnectionString))
{
    var customers = GetCustomers();
    var rows = connection.BulkUpdate<Customer>(customers, qualifiers: e => new { e.LastName, e.BirthDate });
}

Or via table-name

using (var connection = new SqlConnection(ConnectionString))
{
    var customers = GetCustomers();
    var rows = connection.BulkUpdate("Customer", customers);
}

Or via table-name with qualifiers

using (var connection = new SqlConnection(ConnectionString))
{
    var customers = GetCustomers();
    var qualifiers = Field.From("LastName", "BirthDate");
    var rows = connection.BulkUpdate("Customer", customers, qualifiers: qualifiers);
}

As mentioned in the earlier sections, both the System.Data.Common.DbDataReader and System.DataTable objects are also supported.

When to use the Batch and Bulk Operations?

There is no standard of when to use what. It all depends on your situation. Consider the following criteria when deciding.

No of Rows/Columns
Network Latency
Type of Data (Blob, etc)

The most common factor to choose the bulk operations over the batch operations are the number of rows. We usually use the batch operations if the rows we are processing are above 1K, otherwise, we are using the batch operations.

It is also good to understand the advantages of one from the other. The pros of using the bulk operation is performance, nothing else. The cons is, it overrides the operations of the underlying database. It also keeps blocking the target table while being under the bulk operations transaction.

Summary

By using the RepoDb.SqlServer.BulkOperations features, you will have the following.

It gives you maximum performance when processing the large datasets.
You do not need to write the complex implementation of the bulk operations. Every call is a single-line of code.
The library is reviewed by the OSS community.
It is tested and is used by the large number of developers.
High-quality. Packed with real-world Unit and Integration Tests.
Simple implementation. You code will be easy to operate and maintain.

Thank you for reading this article.

We are asking your help to please support this repository and solution. Your stars to our Github page is very valuable.

C#: What will make you choose RepoDb over Dapper (ORM)

Mike Pendon (Zym) — Sun, 16 Feb 2020 14:27:06 +0000

Introduction

In this page, we will share you the differences and what sets RepoDb apart from Dapper. We tried our best to make a 1-to-1 comparisson for most area. This page will hopefully help you decide as a developer to choose RepoDb as your micro-ORM (with compelling reason).

"I am an open source contributor and I am here to share you what I had done. I worked hard for it to improve the space of data access in .NET. I personally ask your support towards this library. I hope you share, you blog and use it."

All the contents of this tutorial is written by me (the author itself). Our knowledge to Dapper is not that deep enough when compared to our knowledge with RepoDb. So, please allow yourselves to check or comments right away if you think we made this page bias for RepoDb.

Before we begin

The programming language and database provider we are using on our samples below are C# and SQL Server.

Both library is an ORM framework for .NET. They are both lightweight, fast and efficient. The Dapper is a full-fledge micro-ORM whereas RepoDb is a hybrid-ORM.

To avoid the bias on the comparisson, we will not cover the features that is present in RepoDb but is absent in Dapper (ie: Cache, Trace, QueryHints, Extensibility, StatementBuilder and Repositories) (vice-versa). Also, the comparisson does not included any other extension libraries of both (ie: RepoDb.SqLite, RepoDb.MySql, RepoDb.PostgreSql, Dapper.Contrib, DapperExtensions, Dapper.SqlBuilder, etc).

Tables

Let us assumed we have the following database tables.

CREATE TABLE [dbo].[Customer]
(
    [Id] BIGINT IDENTITY(1,1) 
    , [Name] NVARCHAR(128) NOT NULL
    , [Address] NVARCHAR(MAX)
    , CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED ([Id] ASC )
)
ON [PRIMARY];
GO

CREATE TABLE [dbo].[Product]
(
    [Id] BIGINT IDENTITY(1,1) 
    , [Name] NVARCHAR(128) NOT NULL
    , [Price] Decimal(18,2)
    , CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED ([Id] ASC )
)
ON [PRIMARY];
GO

CREATE TABLE [dbo].[Order]
(
    [Id] BIGINT IDENTITY(1,1) 
    , [ProductId] BIGINT NOT NULL
    , [CustomerId] BIGINT
    , [OrderDateUtc] DATETIME(5)
    , [Quantity] INT
    , CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED ([Id] ASC )
)
ON [PRIMARY];
GO

Models

Let us assumed we have the following class models.

public class Customer
{
    public long Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
}

public class Product
{
    public long Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

public class Order
{
    public long Id { get; set; }
    public long ProductId { get; set; }
    public long CustomerId { get; set; }
    public int Quantity { get; set; }
    public DateTime OrderDateUtc{ get; set; }
}

Basic CRUD Differences

Querying multiple rows

Dapper:

Query:

using (var connection = new SqlConnection(ConnectionString))
{
    var customers = connection.Query<Customer>("SELECT * FROM [dbo].[Customer];");
}

RepoDb:

Raw-SQL:

using (var connection = new SqlConnection(ConnectionString))
{
    var customers = connection.ExecuteQuery<Customer>("SELECT * FROM [dbo].[Customer];");
}

Fluent:

using (var connection = new SqlConnection(ConnectionString))
{
    var customers = connection.QueryAll<Customer>();
}

Querying a single record

Dapper:

Query

using (var connection = new SqlConnection(ConnectionString))
{
    var customer = connection.Query<Customer>("SELECT * FROM [dbo].[Customer] WHERE (Id = @Id);", new { Id = 10045 }).FirstOrDefault();
}

RepoDb:

Raw-SQL:

using (var connection = new SqlConnection(ConnectionString))
{
    var customer = connection.ExecuteQuery<Customer>("SELECT * FROM [dbo].[Customer] WHERE (Id = @Id);", new { Id = 10045 }).FirstOrDefault();
}

Fluent:

using (var connection = new SqlConnection(ConnectionString))
{
    var customer = connection.Query<Customer>(e => e.Id == 10045).FirstOrDefault();
}

Inserting a record

Dapper:

Execute:

By default, it returns the number of affected rows.

using (var connection = new SqlConnection(ConnectionString))
{
    var customer = new Customer
    {
        Name = "John Doe",
        Address = "New York"
    };
    var affectedRows = connection.Execute("INSERT INTO [dbo].[Customer] (Name, Address) VALUES (@Name, @Address);", customer);
}

Query:

Returning the identity value.

using (var connection = new SqlConnection(ConnectionString))
{
    var customer = new Customer
    {
        Name = "John Doe",
        Address = "New York"
    };
    var id = connection.Query<long>("INSERT INTO [dbo].[Customer] (Name, Address) VALUES (@Name, @Address); SELECT CONVERT(BIGINT, SCOPE_IDENTITY());", customer).Single();
}

RepoDb:

Raw-SQL:

using (var connection = new SqlConnection(ConnectionString))
{
    var customer = new Customer
    {
        Name = "John Doe",
        Address = "New York"
    };
    var id = connection.ExecuteScalar<long>("INSERT INTO [dbo].[Customer] (Name, Address) VALUES (@Name, @Address); SELECT CONVERT(BIGINT, SCOPE_IDENTITY());", customer);
}

Fluent:

using (var connection = new SqlConnection(ConnectionString))
{
    var customer = new Customer
    {
        Name = "John Doe",
        Address = "New York"
    };
    var id = (long)connection.Insert<Customer>(customer); // or connection.Insert<Customer, long>(customer);
}

Updating a record

Dapper:

Execute:

using (var connection = new SqlConnection(ConnectionString))
{
    var affectedRows = connection.Execute("UPDATE [dbo].[Customer] SET Name = @Name, Address = @Address WHERE Id = @Id;",
    new
    {
        Id = 10045,
        Name = "John Doe",
        Address = "New York"
    });
}

RepoDb:

Raw-SQL:

using (var connection = new SqlConnection(ConnectionString))
{
    var affectedRows = connection.ExecuteScalar<int>("UPDATE [dbo].[Customer] SET Name = @Name, Address = @Address WHERE Id = @Id;",
    new
    {
        Id = 10045,
        Name = "John Doe",
        Address = "New York"
    });
}

Fluent:

using (var connection = new SqlConnection(ConnectionString))
{
    var customer = new Customer
    {
        Id = 10045,
        Name = "John Doe",
        Address = "New York"
    };
    var affectedRows = connection.Update<Customer>(customer);
}

Deleting a record

Dapper:

Execute:

using (var connection = new SqlConnection(ConnectionString))
{
    var affectedRows = connection.Execute("DELETE FROM [dbo].[Customer] WHERE Id = @Id;", new { Id = 10045 });
}

RepoDb:

Raw-SQL:

using (var connection = new SqlConnection(ConnectionString))
{
    var affectedRows = connection.ExecuteScalar<int>("DELETE FROM [dbo].[Customer] WHERE Id = @Id;", new { Id = 10045 });
}

Fluent:

using (var connection = new SqlConnection(ConnectionString))
{
    var affectedRows = connection.Delete<Customer>(10045);
}

Advance Calls Differences

Querying a parent and its children

Let us assumed we have added the Orders (of type IEnumerable<Order>) property on our Customer class.

Customer

public class Customer
{
    public long Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public IEnumerable<Order> Orders { get; set; }
}

Order

public class Order
{
    public long Id { get; set; }
    public long ProductId { get; set; }
    public long CustomerId { get; set; }
    public int Quantity { get; set; }
    public DateTime OrderDateUtc{ get; set; }
}

Dapper:

Query:

using (var connection = new SqlConnection(ConnectionString))
{
    var sql = "SELECT C.Id, C.Name, C.Address, O.ProductId, O.Quantity, O.OrderDateUtc FROM [dbo].[Customer] C INNER JOIN [dbo].[Order] O ON O.CustomerId = C.Id WHERE C.Id = @Id;";
    var customers = connection.Query<Customer, Order, Customer>(sql,
    (customer, order) =>
    {
        customer.Orders = customer.Orders ?? new List<Order>();
        customer.Orders.Add(order);
        return customer;
    },
    new { Id = 10045 });
}

QueryMultiple:

using (var connection = new SqlConnection(ConnectionString))
{
    var sql = "SELECT * FROM [dbo].[Customer] WHERE Id = @CustomerId; SELECT * FROM [dbo].[Order] WHERE CustomerId = @CustomerId;";
    using (var result = connection.QueryMultiple(sql, new { CustomerId = 10045 }))
    {
        var customer = result.Read<Customer>().First();
        var orders = result.Read<Order>().ToList();
    }
}

RepoDb:

The JOIN feature is purposely not being supported yet. We have explained it on our Multiple Resultsets via QueryMultiple and ExecuteQueryMultiple page. Also, we have provided an answer already on our FAQs.

However, the support to this feature will soon to be developed. We are now doing a poll-survey on how to implement this one based on the perusal of the community. The discussion can be seen here and we would like to hear yours!

No question to this. The most optimal way is to do an actual INNER JOIN in the database like what Dapper is doing!

However, there is an alternative way to do this in RepoDb. It can be done via Multi-Query that executes packed SELECT-statements in a single-call.

Raw-SQL:

using (var connection = new SqlConnection(ConnectionString))
{
    var sql = "SELECT * FROM [dbo].[Customer] WHERE Id = @CustomerId; SELECT * FROM [dbo].[Order] WHERE CustomerId = @CustomerId;";
    var extractor = connection.ExecuteQueryMultiple(sql, new { CustomerId = 10045 });
    var customer = extractor.Extract<Customer>().FirstOrDefault();
    var orders = extractor.Extract<Order>().AsList();
    customer.Orders = orders;
}

Fluent:

using (var connection = new SqlConnection(ConnectionString))
{
    var customerId = 10045;
    var tuple = connection.QueryMultiple<Customer, Order>(customer => customer.Id == customerId, order => order.CustomerId == customerId);
    var customer = tuple.Item1.FirstOrDefault();
    var orders = tuple.Item2.AsList();
    customer.Orders = orders;
}

Querying multiple parent and their children

Almost the same as previous section.

Query:

var customers = new List<Customer>();
using (var connection = new SqlConnection(ConnectionString))
{
    var sql = "SELECT C.Id, C.Name, C.Address, O.ProductId, O.Quantity, O.OrderDateUtc FROM [dbo].[Customer] C INNER JOIN [dbo].[Order] O ON O.CustomerId = C.Id;";
    var customers = connection.Query<Customer, Order, Customer>(sql,
    (customer, order) =>
    {
        customer = customers.Where(e => e.Id == customer.Id).FirstOrDefault() ?? customer;
        customer.Orders = customer.Orders ?? new List<Order>();
        customer.Orders.Add(order);
        return customer;
    });
}

Note: The hacking technique happens on the developer side, not embedded inside the library.

QueryMultiple:

using (var connection = new SqlConnection(ConnectionString))
{
    var sql = "SELECT * FROM [dbo].[Customer]; SELECT * FROM [dbo].[Order];";
    using (var result = connection.QueryMultiple(sql, new { CustomerId = 10045 }))
    {
        var customers = result.Read<Customer>().ToList();
        var orders = result.Read<Order>().ToList();
        customers.ForEach(customer =>
            customer.Orders = orders.Where(o => o.CustomerId == customer.Id).ToList()); // Client memory processing
    }
}

RepoDb:

Raw-SQL:

using (var connection = new SqlConnection(ConnectionString))
{
    var extractor = connection.ExecuteQueryMultiple("SELECT * FROM [dbo].[Customer]; SELECT * FROM [dbo].[Order];");
    var customers = extractor.Extract<Customer>().AsList();
    var orders = extractor.Extract<Order>().AsList();
    customers.ForEach(customer =>
        customer.Orders = orders.Where(o => o.CustomerId == customer.Id).AsList()); // Client memory processing
}

Fluent:

using (var connection = new SqlConnection(ConnectionString))
{
    var customerId = 10045;
    var tuple = connection.QueryMultiple<Customer, Order>(customer => customer.Id == customerId, order => order.CustomerId == customerId);
    var customers = tuple.Item1.FirstOrDefault();
    var orders = tuple.Item2.AsList();
    customers.ForEach(customer =>
        customer.Orders = orders.Where(o => o.CustomerId == customer.Id).AsList()); // Client memory processing
}

Inserting multiple rows

Dapper:

Query:

using (var connection = new SqlConnection(ConnectionString))
{
    var customers = GenerateCustomers(1000);
    var identities = connection.Query<long>("INSERT INTO [dbo].[Customer] (Name, Address) VALUES (@Name, @Address); SELECT CONVERT(BIGINT, SCOPE_IDENTITY());", customers);
}

Actually, this is not clear to me:

Is it creating an implicit transaction? What if one row fails?
Is it iterating the list and call the DbCommand.Execute multiple times?

Please correct me here so I can update this page right away.

RepoDb:

Batch operation:
```
using (var connection = new SqlConnection(ConnectionString))
{
    var customers = GenerateCustomers(1000);
    var affectedRows = connection.InsertAll<Customer>(customers);
}
```
The above operation can be batched by passing a value on the batchSize argument.

Note: You can target a specific column. In addition, the identity values are automatically set back to the entities.
Bulk operation:
```
using (var connection = new SqlConnection(ConnectionString))
{
    var customers = GenerateCustomers(1000);
    var affectedRows = connection.BulkInsert<Customer>(customers);
}
```
The above operation can be batched by passing a value on the batchSize argument.

Note: This is just an FYI. The operation is using the SqlBulkCopy of ADO.Net. This should not be compared to Dapper performance due to the fact that this is a real bulk-operation. This is far (extremely fast) when compared to both Dapper (multi-inserts) and RepoDb (InsertAll) operations.

Merging multiple rows

Dapper:

Query:

using (var connection = new SqlConnection(ConnectionString))
{
    var sql = @"MERGE [dbo].[Customer] AS T
        USING
            (SELECT @Name, @Address) AS S
        ON
            S.Id = T.Id
        WHEN NOT MATCH THEN
            INSERT INTO
            (
                Name
                , Address
            )
            VALUES
            (
                S.Name
                , S.
            Address)
        WHEN MATCHED THEN
            UPDATE
            SET Name = S.Name
                , Address = S.Address
        OUTPUT INSERTED.Id AS Result;";
    var customers = GenerateCustomers(1000);
    var identities = connection.Query<long>(sql, customers);
}

Here, I have the same question as the previous section.

RepoDb:

Fluent:
```
using (var connection = new SqlConnection(ConnectionString))
{
    var customers = GenerateCustomers(1000);
    var affectedRows = connection.MergeAll<Customer>(customers);
}
```
The above operation can be batched by passing a value on the batchSize argument.

Note: You can set the qualifier fields. In addition, the identity values are automatically set back to the entities for the newly inserted records.

Updating multiple rows

Dapper:

Query:

using (var connection = new SqlConnection(ConnectionString))
{
    var customers = GenerateCustomers(1000);
    var affectedRows = connection.Execute("UPDATE [dbo].[Customer] SET Name = @Name, Address = @Address WHERE Id = @Id;", customers);
}

RepoDb:

Fluent:

using (var connection = new SqlConnection(ConnectionString))
{
    var customers = GenerateCustomers(1000);
    var affectedRows = connection.UpdateAll<Customer>(customers);
}

The above operation can be batched by passing a value on the batchSize argument.

Note: You can set the qualifier fields.

Bulk-inserting multiple rows

Dapper:

ADO.NET:

using (var connection = new SqlConnection(ConnectionString))
{
    var customers = GenerateCustomers(1000);
    var table = ConvertToTable(customers);
    using (var sqlBulkCopy = new SqlBulkCopy(connection, options, transaction))
        {
        sqlBulkCopy.DestinationTableName = "Customer";
        sqlBulkCopy.WriteToServer(table);
    }
}

Note: You can as well pass an instance of DbDataReader (instead of DataTable).

RepoDb:

Fluent:

using (var connection = new SqlConnection(ConnectionString))
{
    var customers = GenerateCustomers(1000);
    var affectedRows = connection.BulkInsert<Customer>(customers);
}

Note: You can as well pass an instance of DbDataReader.

Fluent (Targetted):

using (var connection = new SqlConnection(ConnectionString))
{
    var customers = GenerateCustomers(1000);
    var affectedRows = connection.BulkInsert("[dbo].[Customer]", customers);
}

Querying the rows by batch

Dapper:

Query:

using (var connection = new SqlConnection(ConnectionString))
{
    var sql = @"WITH CTE AS
        (
            SELECT TOP (@Rows) ROW_NUMBER() OVER(ORDER BY Name ASC) AS RowNumber
            FROM [dbo].[Customer]
            WHERE (Address = @Address)
        )
        SELECT Id
            , Name
            , Address
        FROM
            CTE
        WHERE
            RowNumber BETWEEN @From AND (@From + @Rows);";
    using (var connection = new SqlConnection(ConnectionString))
    {
        var customers = connection.Query<Customer>(sql, new { From = 0, Rows = 100, Address = "New York" });
    }
}

Note: You can as well execute it via (LIMIT) keyword. It is on your preference.

RepoDb:

Fluent:

using (var connection = new SqlConnection(ConnectionString))
{
    var customers = connection.BatchQuery<Customer>(e => e.Address == "New York",
        page: 0,
        rowsPerBatch: 100,
        orderBy: OrderField.Parse(new { Name = Order.Ascending }));
}

Replicate records from different database

Dapper:

Query:

using (var sourceConnection = new SqlConnection(SourceConnectionString))
{
    var customers = sourceConnection.Query<Customer>("SELECT * FROM [dbo].[Customer];");
    using (var destinationConnection = new SqlConnection(DestinationConnectionString))
    {
        var identities = destinationConnection.Query<long>("INSERT INTO [dbo].[Customer] (Name, Address) VALUES (@Name, @Address); SELECT CONVERT(BIGINT, SCOPE_IDENTITY());", customers);
    }
}

RepoDb:

Fluent (InsertAll):

using (var sourceConnection = new SqlConnection(SourceConnectionString))
{
    var customers = sourceConnection.QueryAll<Customer>();
    using (var destinationConnection = new SqlConnection(DestinationConnectionString))
    {
        var affectedRows = destinationConnection.InsertAll<Customer>(customers);
    }
}

Fluent (BulkInsert):

using (var sourceConnection = new SqlConnection(SourceConnectionString))
{
    var customers = sourceConnection.QueryAll<Customer>();
    using (var destinationConnection = new SqlConnection(DestinationConnectionString))
    {
        var affectedRows = destinationConnection.BulkInsert<Customer>(customers);
    }
}

Fluent (Streaming):

This is the most optimal and recommended calls for large datasets. We do not bring the data as class objects in the client application.

using (var sourceConnection = new SqlConnection(SourceConnectionString))
{
    using (var reader = sourceConnection.ExecuteReader("SELECT * FROM [dbo].[Customer];"))
    {
        using (var destinationConnection = new SqlConnection(DestinationConnectionString))
        {
            var affectedRows = destinationConnection.BulkInsert<Customer>(reader);
        }
    }
}

Note: Check for collation constraints. It is an ADO.NET thing.

Passing of Parameters

Dapper:

Dynamic:
```
Query<T>(sql, new { Id = 10045 });
```
It is always an Equal operation. You control the query through SQL Statement.

Dynamic Parameters:

var parameters = new DynamicParameters();
parameters.Add("Name", "John Doe");
parameters.Add("Address", "New York");
Query<T>(sql, parameters);

RepoDb:

Dynamic:
```
Query<T>(new { Id = 10045 });
```
Same as Dapper, it is always referring to an Equal operation. You control the query through SQL Statement.
Linq Expression:
```
Query<T>(e => e.Id == 10045);
```
QueryField:
```
Query<T>(new QueryField("Id", 10045));
```

QueryField(s) or QueryGroup:

var queryFields = new[]
{
    new QueryField("Name", "John Doe")
    new QueryField("Address", "New York")
};
Query<T>(queryFields); // or Query<T>(new QueryGroup(queryFields));

Array of Parameters

Dapper:

Query:

using (var connection = new SqlConnection(ConnectionString))
{
    var addresses = new [] { "New York", "Washington" };
    var customers = connection.Query<Customer>("SELECT * FROM [dbo].[Customer] WHERE Address IN (@Addresses);", new { Addresses = addresses });
}

RepoDb:

ExecuteQuery:

using (var connection = new SqlConnection(ConnectionString))
{
    var addresses = new [] { "New York", "Washington" };
    var customers = connection.ExecuteQuery<Customer>("SELECT * FROM [dbo].[Customer] WHERE Address IN (@Addresses);", new { Addresses = addresses });
}

For further explanation, you can visit our documentation.

Query:

using (var connection = new SqlConnection(ConnectionString))
{
    var addresses = new [] { "New York", "Washington" };
    var customers = connection.Query<Customer>(e => addresses.Contains(e => e.Address));
}

Expression Trees

Dapper do not support Linq Expressions, only dynamics and DynamicParameters.
RepoDb supports Linq Expressions, dynamics and QueryObjects.

Note: The Dapper.DynamicParameters is just a subset of RepoDb.QueryObjects. The QueryObjects has much more capability that can further support the Linq Expressions.

Please visit both documentation.

Supported Databases

Dapper:

Supports all RDBMS data providers.

RepoDb:

Raw-SQLs support all RDBMS data providers.
Fluent calls only supports SQL Server, SqLite, MySql and PostgreSql.

Performance and Efficiency

We only refer to one of the the community-approved ORM bencher, the RawDataAccessBencher.

Net Core:

Here is our observation from the official execution results. The official result can be found here.

Performance:

RepoDb is the fastest ORM when fetching set-records. Both raw-SQL and Fluent calls.
Dapper and RepoDb speed is identical when fetching single-record.
Dapper is faster than RepoDb's Fluent calls when fetching single-record.

Efficiency:

RepoDb is the most-efficient ORM when fetching set-records. Both raw-SQL and Fluent calls.
Dapper is must more efficient than RepoDb when fetching single-record.

NetFramework:

RepoDb is the fastest and the most-efficient ORM for both set and single record(s) fetching. Official results can been found here.

Quality

Dapper:

Dapper is already running since 2012 and is being used by StackOverflow.com. It has a huge consumers and is hugely backed by the community.

RepoDb:

We did our best to write one-test per scenario and we have delivered thousand of items (approximately 6.5K) for both Unit and IntegrationTests. We would like your help to review it as well.

Below are the links to our test suites.

We (or I as an author) has been challenged that the quality of the software does not depends on the number of tests. However, we strongly believe that spending much efforts on writing a test will give confidence to the library consumers (ie: .NET community). Practially, it helps us to avoid manual revisits on the already-working features if somebody is doing a PR to us; it prevents the library from any surprising bugs.

Conclusion to the Quality:

Both is with high-quality but the Dapper is far matured over RepoDb. We will not contest this!

Library Support

Dapper:

Proven and is backed hugely by the .NET Community; funded by StackOverflow.com.

RepoDb:

Backed by one person and is not funded nor sponsored by any entity. Just starting to expand and asking for more supports from the .NET Community.

Licensing and Legality

Both is under the Apache-2.0 license.

Disclaimer:

We are not an expert in legal but we are consulting. If any conflict arises on the copyright or trademark in-front of RepoDb, then that is not yet addressed.

Overall Conclusion

We hope that you somehow consider and revisits this library. It has improve a lot from where it has start.

Simplicity

Dapper is lightweight but will drag you to the most complex-level of code development. It is always tedious to write raw-SQLS and it is hard to maintain due to the fact that it is not a compiler friendly. In addition, to obtain necessary task, you need to implement necessary features.

RepoDb is a very easy-to-use ORM with enough feature sets that you can play on.

Performance

RepoDb is faster than Dapper, enough reason to choose this library if the only factor is performance.

RepoDb is the fastest ORM in .NET. This claim is supported by the official run of the community-approved ORM bencher RawDataAccessBencher.

Efficiency

RepoDb is more efficient than Dapper (same claim as in Performance).

Experience

It is more easier and faster to develop the codes with RepoDb. It has a rich feature sets which can be used right-away (ie: 2nd-Layer Cache, Fluent Methods). It will help you as a developer deliver more codes in fast and clean manner.

Features

In RepoDb, by having the necessary features within the space of a micro-ORM will help you a lot on your development.

The features of like Bulk & Batch Operations, Property Handlers, 2nd-Level Cache, Expression Trees, Multi Queries and Inline Hints are the most common in-used features. The major pain points are, it is absent in Dapper.

Thank you for reading this article. The original article was here.

We are asking your help to please support this repository and solution. Your stars to our Github page is very valuable.

C#: Simplify your Property Transformation with RepoDb ORM

Mike Pendon (Zym) — Sat, 15 Feb 2020 07:53:55 +0000

Introduction

A property handler is a class that handles the process of property transformations. It lives in between your data model extractor (or ORM) and the actual database object (ie: Data Reader).

With this, you as a developer have the full control over the model extractor (or ORM) on how the way you would your data to be handled (read/save).

Why we need such process

There are various reasons why we need to manually override the default implementation of the data extractors.

Auto transform the JSON/String into Class.
Handle all the transformations of the specific .NET CLR Type.
Setting the state of the System.DateTime objects (ie: Kind).
Usage as trigger to something (ie: after value assignment).
Querying another child records.
and many more...

The use cases can actually be unlimited. However, such numbers will all depends on the user scenarios.

Before we begin

The programming language we will be using for this tutorial is C# and the RDBMS is SQL Server.

We expect that you already have experience working with RepoDb ORM, otherwise you can visit our Getting Started page.

In this article, we will only aim the scenario of transforming the JSON/String into a Class object.

Create the DB and Tables

CREATE DATABASE [TestDB];
GO

USE [TestDB];
GO

CREATE TABLE [dbo].[Person]
(
    [Id] BIGINT PRIMARY KEY IDENTITY(1, 1)
    , [Name] NVARCHAR(128)
    , [Age] INT
    , [ExtendedInfo] NVARCHAR(MAX)
    , [CreatedDateUtc] DATETIME2(5)
)
ON [PRIMARY];
GO

Create a C# Project

Type: .NET Core Console Application
Name: PropertyHandler

Within the project, create a folder named Models and add the class below.

public class PersonExtendedInfo
{
    public string Address { get; set; }
    public string Affiliations { get; set; }
    public string Biography { get; set; }
    public string Certifications { get; set; }
}

The class above will be used as the transformed class.

Then, add the parent class below that reference to the above class.

public class Person
{
    public long Id { get; set; }

    public string Name { get; set; }

    public PersonExtendedInfo ExtendedInfo { get; set; }

    public DateTime CreatedDateUtc { get; set; }
}

Notice the property ExtendedInfo is of type NVARCHAR from the database.

Create a Class Factory

This class is only used to create an instance of the Person class.

Add a folder named Factories and add the class below.

public static class PersonFactory
{
    public static Person Create()
    {
        return new Person
        {
            Name = $"Name-{Guid.NewGuid().ToString()}",
            Age = $"{(short)(new Random().Next(100))} years old",
            ExtendedInfo = new PersonExtendedInfo
            {
                Address = $"Address-{Guid.NewGuid().ToString()}",
                Affiliations = $"Affiliations-{Guid.NewGuid().ToString()}",
                Biography = $"Biography-{Guid.NewGuid().ToString()}",
                Certifications = $"Certifications-{Guid.NewGuid().ToString()}"
            },
            CreatedDateUtc = DateTime.UtcNow
        };
    }

    public static IEnumerable<Person> CreateMultiple(int count)
    {
        for (var i = 0; i < count; i++)
        {
            yield return Create();
        }
    }
}

Simulation

By this time, our database and C# project is now ready for use.

Install the Libaries

Type the command below within your Package Manager Console.

> Install-Package RepoDb.SqlServer
> Install-Package Newtonsoft.Json

Inside your Program.cs file follow the steps below.

Create a GetConnection method

private static SqlConnection GetConnection()
{
    return new SqlConnection("Server=.;Database=TestDB;Integrated Security=SSPI;");
}

Create an InsertPerson method

private static void InsertPerson()
{
    using (var connection = GetConnection())
    {
        var people = PersonFactory.CreateMultiple(100).AsList();
        connection.InsertAll(people);
    }
}

Create a QueryPerson method

private static void QueryPerson()
{
    using (var connection = GetConnection())
    {
        var people = connection.QueryAll<Person>();
    }
}

The methods InsertAll and QueryAll were both extended methods of IDbConnection object implemented within RepoDb.

Create a PropertyHandler

Add a folder named PropertyHandlers and add the class below.

public class PersonExtendedInfoHandler : IPropertyHandler<string, PersonExtendedInfo>
{
    public PersonExtendedInfo Get(string input,
        ClassProperty property)
    {
        return JsonConvert.DeserializeObject<PersonExtendedInfo>(input);
    }

    public string Set(PersonExtendedInfo input,
        ClassProperty property)
    {
        return JsonConvert.SerializeObject(input);
    }
}

The Get method is being called when you read a data from the database. The Set method is being called when you push a data towards the database.

Decorate the Attribute in the Property

In the Customer class, decorate the property ExtraInfo with PropertyHandlerAttribute by passing the type of your handler.

public class Person
{
    public long Id { get; set; }
    public string Name { get; set; }
    [PropertyHandler(typeof(PersonExtendedInfoHandler))]
    public PersonExtendedInfo ExtendedInfo { get; set; }
    public DateTime CreatedDateUtc { get; set; }
}

RepoDb also provides a type-level handler in which it allow the developers to handle the transformation of specific .NET CLR Type. The feature can be enabled by simply calling the PropertyTypeHandlerMapper.Add() method.

Extend the Program.Main method

static void Main(string[] args)
{
    // Intialize the RepoDb for SQL Server
    SqlServerBootstrap.Initialize();

    // Calls to the method
    ClearPerson();
    InsertPerson();
    QueryPerson();

    // Exit the console
    Console.WriteLine("Press any key to exit.");
    Console.ReadLine();
}

Testing the program

By this time, the solution is now ready for execution and testing.

Place a breakpoint on the Get or Set method of your Property Handler class.
Run the program by pressing F5

Your breakpoint will be hit and by this time you are controlling the transformation.

The actual Test project can be found here.

Remarks

Thank you for reading this tutorial. Please do not forget to share this library and star our GitHub repository.

C#: Data Access Batch Execution is now very simple with RepoDb ORM

Mike Pendon (Zym) — Sat, 25 Jan 2020 09:47:53 +0000

Introduction

The batch operations are the process of making the multiple single-operations be executed against the database in one-go. The executions are ACID; an implicit transaction is provided if not present.

Benefits

Below are the benefits you will gain after this tutorial.

Understanding the batch operations.
How the batch operations are differed from the bulk operations.
Clean and simple code implementations.
Separation of concerns with much more SOLID written code.
Huge performance improvements with (or over) different Use Cases and Scenarios.

ADO.NET

In ADO.NET, we usually do the following when doing a batch operation.

Create a connection and command object.
Set the command text.
Create a transaction object.
Loop the entities and pass the parameters into the command object.
Execute the command on every item in the iteration.
Commit the transaction.

To further elaborate on the code level, see below.

Let us say we have a Customer class like below and an identity-based table from the database named [dbo].[Customer].

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string SSN { get; set; }
    public string Address { get; set; }
    public DateTime CreatedUtc { get; set; }
    public DateTime ModifiedUtc { get; set; }
}

Then, to achieve the batch executions, we usually write the code below.

using (var connection = new SqlConnection(ConnectionString))
{
    connection.Open();
    using (var transaction = connection.BeginTransaction())
    {
        using (var command = connection.CreateCommand())
        {
            command.CommandText = "INSERT INTO [dbo].[Customer] (Name, SSN, Address, CreatedUtc, ModifiedUtc) VALUES (@Name, @SSN, @Address, @CreatedUtc, @ModifiedUtc);";
            command.Transaction = transaction;
            foreach (var customer in customers)
            {
                command.Parameters.Clear();
                command.Parameters.AddWithValue("@Name", customer.Name);
                command.Parameters.AddWithValue("@SSN", customer.SSN);
                command.Parameters.AddWithValue("@Address", customer.Address);
                command.Parameters.AddWithValue("@CreatedUtc", customer.CreatedUtc);
                command.Parameters.AddWithValue("@ModifiedUtc", customer.ModifiedUtc);
                command.ExecuteNonQuery();
            }
        }
        transaction.Commit();
    }
}

The code above is a bit optimized when it comes to execution. During the development, most developers are creating a single DbConnection in each iteration without being aware of it. If they does, most developers are failed to realize the re-usability of the single DbCommand object.

You usually do the same thing when writing the codes for Update and Merge operations, only differs from the command text.

ADO.NET Drawback

The drawback to this is that, each item is being iterated and executed against the database in an atomic way. The calls made via ExecuteNonQuery() method is actually transmitting a data from the client application into the database server (or cloud) over the wire multiple times.

In a normal scenario, this is not a problem at all. But it is a big problem in a scenario of like processing thousand of items a very slow network connectivity (or transmitting the data from on-premise to the cloud). In short, this is not the most optimal way.

Solution

We have written RepoDb in such a way to cater the scenario mentioned above. It also brings simplicity during the development, resulting to a more productive software maintainability..

There is no much ORM that caters the scenario of the batch operations, even for the simple CRUD operations (i.e.: Insert, Update, Merge, Delete).

In RepoDb, the calls to the methods are very direct and simple. The way you invoke it is just by simply calling the extended methods of the DbConnection object. On top of this, the methods are ACID on its own way.

Simply install the library and open a connection object, then you can do all the things you can do with your database.

To install, simply type the command below in you Package Manager Console.

Install-Package RepoDb

Let us say, we would like to insert certain numbers of Customer records in the database. Then, you can call the method below.

var customers = CreateCustomers(1000);
using (var connection = new SqlConnection(ConnectionString))
{
    connection.InsertAll<Customer>(customers);
}

If the target table has an identity column, the newly generated identity value will be mapped back to the data models accordingly.

By default, the batch is 10, but this can be overridden by simply specifying the value of the batchSize argument. See the code below.

using (var connection = new SqlConnection(ConnectionString))
{
    connection.InsertAll<Customer>(customers, batchSize: 100);
}

The calls to other batch operations (i.e.: Update, Merge and Delete) are also identical to Insert. See the code snippets below.

// Update
using (var connection = new SqlConnection(ConnectionString))
{
    var rowsAffected = connection.UpdateAll<Customer>(customers);
}

// Merge
using (var connection = new SqlConnection(ConnectionString))
{
    var rowsAffected = connection.MergeAll<Customer>(customers);
}

// Delete
using (var connection = new SqlConnection(ConnectionString))
{
    var rowsAffected = connection.DeleteAll<Customer>(customers);
}

The operations are controllable in such a way you (as a developer) can optimize the number of rows to be batched, specifically for the following situations.

No. of Columns
Network Latency
Type of Data (Blobs, etc)
Database Network (Cloud, On-Premise)
Etc

Behind the scene

To further understand what happens behind the scene, below are some of the explanations.

Pre-compiling the transformation ahead-of-time (AOT) via Expression.
Creating an implicit transaction if not passed by the caller.
Composing a packed-statements based on the batchSize.
Mapping the entities into the given parameters of the packed-statements.
Executing the command in one-go.
Committing the created implicit transaction.

Understanding the SQL Statement Composition

We also would like you to understand how the SQL statements are composed and the parameters are being passed.

When you call the operations, like below.

using (var connection = new SqlConnection(ConnectionString))
{
    connection.InsertAll<Customer>(customers, batchSize: 100);
}

The library will then generate and pre-compile the packed-statements below.

INSERT INTO [dbo].[Customer] (Name, SSN, Address, CreatedUtc, ModifiedUtc) VALUES (@Name, @SSN, @Address, @CreatedUtc, @ModifiedUtc);
INSERT INTO [dbo].[Customer] (Name, SSN, Address, CreatedUtc, ModifiedUtc) VALUES (@Name1, @SSN1, @Address1, @CreatedUtc1, @ModifiedUtc1);
...
...
...
INSERT INTO [dbo].[Customer] (Name, SSN, Address, CreatedUtc, ModifiedUtc) VALUES (@Name99, @SSN99, @Address99, @CreatedUtc99, @ModifiedUtc99);

The generated packed-statements above will be utilized by the DbCommand object once.

The library will then split the array of the Customer entities based on the value of the batchSize argument, in this case 100. Those making the 1000 rows to only be executed by 10 iterations with 100 rows each.

After the splits, the library will map the parameters as below.

The first 10 customers will be on the parameters (@Name, ..., @ModifiedUtc) to (@Name9, ..., @ModifiedUtc9).
The second 10 customers will be on the parameters (@Name10, ..., @ModifiedUtc10) to (@Name19, ..., @ModifiedUtc19).
The last 10 customers will be on the parameters (@Name90, ..., @ModifiedUtc90) to (@name99 , ..., @ModifiedUtc99).

The library also solved the non-modular number of items passed in the batch operations. Everything is pre-compiled ahead-of-time (AOT).

Performance

The performance is far beyond the usual atomic operation we do when using the ADO.NET. Unless the developers will make an effort to create a complex implementation of pre-compilation, packed-statements generations, auto-mapping of the parameters, etc. There is no way the performance can beat by a native implementation. It is an overkill and a headache to the developers to implement such complex logic.

The performance improvement varies from 40% to 80% based on the number of records in the datasets being processed. The bigger the datasets the higher the percentages you gain.

See some simple statistics below.

Executing via RepoDb. First execution is with compilations.
0.8159257 second(s) for RepoDb with 1000 customers.
Executing via ADO.NET.
0.323014 second(s) for ADO.NET with 1000 customers.
Executing via RepoDb. AOT compilation.
0.0850875 second(s) for RepoDb with 1000 customers.
--------------------------------------------------
Executing via ADO.NET.
1.6131903 second(s) for ADO.NET with 10000 customers.
Executing via RepoDb. AOT compilation.
0.606382 second(s) for RepoDb with 10000 customers.
--------------------------------------------------
Executing via ADO.NET.
13.872307 second(s) for ADO.NET with 100000 customers.
Executing via RepoDb. AOT compilation.
9.8594733 second(s) for RepoDb with 100000 customers.
Press any key to exit.

The actual Test project can be found here.

Thank you for reading this tutorial. Please do not forget to star our GitHub repository.

Increasing C# data access performance by 90% using RepoDb 2nd-Level Cache

Mike Pendon (Zym) — Thu, 09 Jan 2020 06:24:06 +0000

In this article, we will show you on how to implement and maximize the caching functionality when using the RepoDb ORM library.

Benefits

You will be learning the techniques on how to properly incorporate the caching layers into your application that could help you prevent frequent queries towards your database. It will help you improve the application performance by more than 90% in some scenarios.

In this tutorial, we brought the 8 seconds down to less than 500 milliseconds when querying the 20K records with child entity.

Scenarios Covered

Below is the very common scenario we will be covering on this tutorial.

You have a look-up entity (a child entity). These records are not frequently changing (or not changing at all).
You are retrieving the parent records with the child entity mentioned in #1.
You are frequently iterating and re-doing #2.

There are other different scenarios that could lead us to a different solution and approach. In this tutorial, we would like to clear that the 90% performance improvement is feasible but it is a very targeted scenario. It is also important to take note that you can fetch the data via JOINS or whatever tuning you can do, but this tutorial is focusing on the 2nd-level caching.

In here, we will do the following.

Student - the parent entity.
Teacher - the look-up entity (or child entity).
Create a REST API with database, table, C# project, interfaces, repositories and models.
Display the query statistics (cache vs no-cache).

Before we begin

The cache in RepoDb is by default implemented as memory-based cache object. It can be overridden by your own customized cache object. The cache setting is by default set to 180 minutes (it can be overriden), and is only a single string argument. You can learn more about this one from here.

In addition, we expect that you have the following software install in your machine.

Microsoft SQL Server (at least 2016).
Microsoft SQL Server Management Studio 2019 (at least 2017)
Microsoft Visual Studio 2019 (or with .NET Core 3.1).

Create a Database

In your SQL Server, execute the script below.

CREATE DATABASE [SchoolDB];
GO

Create a Table

In your SQL Server, execute the script below.

USE [SchoolDB];
GO

CREATE TABLE [dbo].[Student]
(
    [Id] INT IDENTITY(1,1)
    , [TeacherId] INT 
    , [Name] NVARCHAR(128) NOT NULL
    , CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED ([Id] ASC )
)
ON [PRIMARY];
GO

CREATE TABLE [dbo].[Teacher]
(
    [Id] INT IDENTITY(1,1)
    , [Name] NVARCHAR(128) NOT NULL
    , CONSTRAINT [PK_Teacher] PRIMARY KEY CLUSTERED ([Id] ASC )
)
ON [PRIMARY];
GO

Create a Child Entities

In your SQL Server, execute the script below.

USE [SchoolDB];
GO

INSERT INTO [dbo].[Teacher]
(
    [Name]
)
VALUES
('Lurlene Laury')
, ('Wynell Kort')
, ('Alexa Dempsey')
, ('Loan Goggins')
, ('Lien Lange')
, ('Veronika Hershey')
, ('Erasmo Milo')
, ('Columbus Hadden')
, ('Lena Cendejas')
, ('Shawana Bono')
, ('Morton Jourdan')
, ('Myesha Griffin')
, ('Cassi Pelayo')
, ('Shelly Chouinard')
, ('Gabrielle Cloninger')
, ('Brandee Rominger')
, ('Kimberly Blackmore')
, ('Efren Wey')
, ('Fabiola Douse')
, ('Heath Sessums');

The names mentioned above was randomly generated from listofrandomnames.com.

Create a C# Project

The C# project must be with the following information:

Name: InventoryAPI
Type: ASP.NET Core API
Test: No
Location: Any Location

No need to add a test framework. Leave this project an empty one.

Create the Class Models

In your C# project. Do the following.

Add a folder named Models.
Inside the Models folder, add the model classes named Student.cs and Teacher.cs.

Replace the content of the Student.cs with the code snippets below.

public class Student
{
    public int Id { get; set; }
    public int TeacherId { get; set; }
    public string Name { get; set; }
}

And replace the content of the Teacher.cs with the code snippets below.

public class Teacher
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Install the Library

To install the library, simply execute the command below in your Package Manager Console.

Install-Package RepoDb

Create the Repository Interfaces

In your C# project. Do the following.

Add a folder named Interfaces.
Inside the Interfaces folder, add the model classes named IStudentRepository.cs and ITeachRepository.cs.

Replace the content of the IStudentRepository.cs with the code snippets below.

public interface IStudentRepository
{
    IEnumerable<Student> GetAllStudents();
    int Save(Student student);
    int SaveAll(IList<Student> students);
}

And replace the content of the ITeachRepository.cs with the code snippets below.

public interface ITeacherRepository
{
    IEnumerable<Teacher> GetAllTeachers();
    Teacher GetTeacher(int teacherId);
    Teacher GetTeacherCache(int teacherId);
}

The interface contains the necessary methods for us to be able to retrieve the Student and Teacher entities from the database. Inside the ITeacherRepository, we introduced a GetTeacherCache method that will be used for caching. You will learn more as we go further.

Create the Repository Classes

In your C# project. Do the following.

Add a folder named Repositories.
Inside the Repositories folder, add the repository classes named SudentRepository.cs and TeacherRepository.cs.

Replace the content of your StudentRepository.cs with the one below.

public class StudentRepository : BaseRepository<Student, SqlConnection>,
    IStudentRepository
{
    public StudentRepository()
        : base(@"Server=.;Database=SchoolDB;Integrated Security=SSPI;")
    { }

    public IEnumerable<Student> GetAllStudents()
    {
        return QueryAll();
    }

    public int Save(Student student)
    {
        return Insert<int>(student);
    }

    public int SaveAll(IList<Student> students)
    {
        return this.BulkInsert(students);
    }
}

And replace the content of your TeacherRepository.cs with the code snippets below.

public class TeacherRepository : BaseRepository<Teacher, SqlConnection>,
    ITeacherRepository
{
    public TeacherRepository()
        : base(@"Server=.;Database=SchoolDB;Integrated Security=SSPI;")
    { }

    public IEnumerable<Teacher> GetAllTeachers()
    {
        return QueryAll();
    }

    public Teacher GetTeacher(int teacherId)
    {
        return Query(teacherId).FirstOrDefault();
    }

    public Teacher GetTeacherCache(int teacherId)
    {
        return Query(teacherId, cacheKey: $"Teacher{teacherId}").FirstOrDefault();
    }
}

Important Note: A method named GetTeacherCache has been introduced. This method is passing a cacheKey value of Teacher{teacherId} to cache the result into the Cache object.

Register the Repositories as Service Components

To register as a service component, follow the steps below.

In your Solution Explorer, double-click the Startup.cs file.
Navigate inside ConfigureServices method and paste the code below before the method end.

services.AddTransient<IStudentRepository, StudentRepository>();
services.AddTransient<ITeacherRepository, TeacherRepository>();

Resolve the missing namespaces by placing the mouse inside the StudentRepository (and IStudentRepository) and press Ctrl+Space > Enter.
Press Ctrl+S keys to save the changes.

The engine will register both the StudentRepository and TeacherRepository objects (as implemented by IStudentRepository and ITeacherRepository interface) into the services collection. Once the registration is complete, it signifies that these repository classes are now ready to be used for injection.

We can as well add it as Singleton via AddSingleton() method of IServiceCollection if we wish to have our Repository in a singleton mood.

Calling the Repository operations from the Controller

In this section, the ultimate goal is to call the StudentRepository operations inside a Controller method.

To attain this, we need to do the following steps below.

Create a Controller

Inside the Controllers folder, add a new class named StudentController.cs.
The new file named StudentController.cs will be created. Replace the class implementation with the script below.

public class StudentController : Controller
{
    private IStudentRepository m_studentRepository;
    private ITeacherRepository m_teacherRepository;

    public StudentController(IStudentRepository studentRepository,
        ITeacherRepository teacherRepository)
    {
        m_studentRepository = studentRepository;
        m_teacherRepository = teacherRepository;
    }

    [HttpGet()]
    public ActionResult<IEnumerable<Student>> Get()
    {
        return m_studentRepository.GetAllStudents().AsList();
    }
}

Press Ctrl+S keys to save the changes.

There you see a method named Get. That method is the default method being routed that will display all the Student records from the database.

Create a Student Generator Method

This method will be used as a generator of the Student entity.

In your StudentController.cs, add the code snippets below just right after the Get method.

[HttpGet("generate/{count}")]
public ActionResult<dynamic> Generate(int count = 10000)
{
    var teachers = m_teacherRepository
        .GetAllTeachers()
        .AsList();
    var random = new Random();
    var students = new List<Student>();
    var dateTime = DateTime.UtcNow;

    for (var i = 0; i < count; i++)
    {
        var teacher = teachers.ElementAt(random.Next(0, teachers.Count - 1));
        var student = new Student
        {
            Name = $"Student-{Guid.NewGuid().ToString()}",
            TeacherId = teacher.Id
        };
        students.Add(student);
    }

    var insertedCount = m_studentRepository.SaveAll(students);
    var elapsed = (DateTime.UtcNow - dateTime).TotalMilliseconds;

    return new
    {
        Inserted = insertedCount,
        ElapsedInMilliseconds = elapsed
    };
}

What does the Generate method do?

It does the following:

Retrieve all Teachers from the database.
Create a Student entity based on the number passed in the count argument. The default is 10000.
Each Student, a random Name and TeacherId is assigned.
Bulk-inserting the Student entities into the database.

Create a QueryStat Method

This method is being used to display the query statistics without caching.

In your StudentController.cs, add the code snippets below just right after the Generate method.

[HttpGet("querystat")]
public ActionResult<dynamic> GetQueryStat()
{
    var students = m_studentRepository.GetAllStudents();
    var iteration = 0;
    var dateTime = DateTime.UtcNow;
    foreach (var student in students)
    {
        var teacher = m_teacherRepository.GetTeacher(student.TeacherId);
        if (teacher != null)
        {
            iteration++;
        }
    }
    var elapsed = (DateTime.UtcNow - dateTime).TotalMilliseconds;
    return new
    {
        Iteration = iteration,
        ElapsedInMilliseconds = elapsed
    };
}

This method is calling the GetTeacher method from the controller.

What does the QueryStat method do?

It does the following:

Retrieve all Students from the database.
Iterate each Student and retrieve the related Teacher from the database.
Records the iteration account and total number of milliseconds elapsed.

Create a QueryStatCache Method

This method is being used to display the query statistics with caching.

In your StudentController.cs, add the code snippets below just right after the GetQueryStat method.

[HttpGet("querystatcache")]
public ActionResult<dynamic> GetQueryStatCache()
{
    var students = m_studentRepository.GetAllStudents();
    var iteration = 0;
    var dateTime = DateTime.UtcNow;
    foreach (var student in students)
    {
        var teacher = m_teacherRepository.GetTeacherCache(student.TeacherId);
        if (teacher != null)
        {
            iteration++;
        }
    }
    var elapsed = (DateTime.UtcNow - dateTime).TotalMilliseconds;
    return new
    {
        Iteration = iteration,
        ElapsedInMilliseconds = elapsed
    };
}

By this time, this method is calling the GetTeacherCache method from the controller.

What does the QueryStat method do?

It does the following:

Retrieve all Students from the database.
Iterate each Student and retrieve the related Teacher from the cache. If not present, it queries from the database.
Records the iteration account and total number of milliseconds elapsed.

Testing the Controller

At this point, our solution is now ready for testing. Build the solution by simply pressing the Alt + B + R keys.

Once the build is complete, press the F5 key to start.

In the browser, type the URL below.

Generate 20000 Student Records

In your browser, please enter the link below to generate 20K records of Student entity.

https://localhost:44397/student/generate/20000

Expected result:

{"inserted":20000,"elapsedInMilliseconds":111.4921}

Query Student Statistics without Cache

In your browser, please enter the link below to query all the 20K records of Student entity directly from the database.

https://localhost:44397/student/querystat

Expected result:

{"iteration":40000,"elapsedInMilliseconds":18862.6261}

Query Student Statistics withCache

In your browser, please enter the link below to query all the 20K records of Student entity directly from the cache.

https://localhost:44397/student/querystatcache

Expected result:

{"iteration":40000,"elapsedInMilliseconds":209.4439}

How to find a port?

Right-click on the project InventoryAPI from the Solution Explorer and click the Properties.
Click the Debug tab.
Under Web Server Settings, you will see the App URL field that contains the port.

Links

You can see and download the actual project here.

You support is valuable to us, please do not forget to star our GitHub page.

Thank you for reading this article.

Creating a C# Repository in .NET Core using RepoDb

Mike Pendon (Zym) — Thu, 02 Jan 2020 16:08:33 +0000

In this article, we will show you how to create an entity-based C# Repository using RepoDb ORM.

Benefits

This will give you a knowledge on how to implement a standard entity-based Repository in fast and clean manner.

Before we begin

We expect that you have the following software install in your machine.

Microsoft SQL Server (at least 2016).
Microsoft SQL Server Management Studio (at least 2016)
Microsoft Visual Studio (at least 2017).

To proceed, follow the steps below.

Create a Database

In your SQL Server, execute the script below.

CREATE DATABASE [Inventory];
GO

Create a Table

In your SQL Server, execute the script below.

USE [Inventory];
GO

CREATE TABLE [dbo].[Customer]
(
    [Id] BIGINT IDENTITY(1,1) 
    , [Name] NVARCHAR(128) NOT NULL
    , [Address] NVARCHAR(MAX)
    , CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED ([Id] ASC )
)
ON [PRIMARY];
GO

Create a C# Project

The project information must be below:

Name: InventoryAPI
Type: ASP.NET Core API
Test: No
Location: Any Location

Leave this project an empty one as we will create our own objects moving forward.

Create a Model Class

In your C# project, do the following.

Add a folder named Models.
Inside the Models folder, add a new class named Customer.cs.

Replace the content of the Customer.cs with the code snippets below.

public class Customer
{
    public long Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
}

Creating a Repository Class

In your C# project, do the following.

Add a folder named Repositories.
Inside the Repositories folder, add a new class named CustomerRepository.cs.

The content of your class would be below.

public class CustomerRepository
{
}

Inherit from BaseRepository

At this point in time, you already have the following.

A database named Inventory.
A table named Customer.
A C# ASP.NET Core API project.
A class model named Customer.
A repository class named CustomerRepository.

Before we proceed, you need to install RepoDb. Type the command below in your Package Manager Console.

Install-Package RepoDb

Now, replace the content of your class CustomerRepository with the one below.

public class CustomerRepository : BaseRepository<Customer, SqlConnection>
{
    public CustomerRepository() : base(@"Server=.;Database=Inventory;Integrated Security=SSPI;")
    { }
}

As recommended, not covered by this tutorial, the repository must accept a connectionString (or settings object) in the constructor. Then pass the value of connectionString on the constructor of the base class BaseRepository.

The class BaseRepository is an embedded base repository object within RepoDb library. It is usually used to create an entity-based repository.

Actually, at this point in time, you already have inherited all the operations of the BaseRepository. Without doing anything more, you can now call the CustomerRepository.

See sample codes below.

using (var repository = new CustomerRepository())
{
    var customer = new Customer
    {
        Name = "John Doe",
        Address = "New York"
    };
    var id = repository.Insert<long>(customer);
    ...
}

The code above will insert a new record in your table Customer.

To query, you can use the code below.

using (var repository = new CustomerRepository())
{
    var customer = repository.QueryAll().FirstOrDefault();
    ...
}

Even though you already have a working CustomerRepository, that is still not enough as per the C# standard. By standard, we usually use an interface for dependency-injection.

Note: You can also inject a class, not just an interface!

Implement a Repository Interface

In your C# project, do the following.

Add a folder named Interfaces.
Inside the Interfaces folder, add a new class named ICustomerRepository.cs.

Replace the content of the file with the code snippets below.

public interface ICustomerRepository
{
    Customer GetById(long id);
    Customer GetByName(string name);
    IEnumerable<Customer> GetAll();
    long Insert(Customer customer);
    int Update(Customer customer);
    int Delete(long id);
}

We have created 6 methods which would enable you to do a basic CRUD operations on the Customer entity.

Implement your Custom Methods

First, implement the interface within the CustomerRepository class.

See the codes below.

public class CustomerRepository : BaseRepository<Customer, SqlConnection>, ICustomerRepository
{
    public CustomerRepository ()
        : base(@"Server=.;Database=Inventory;Integrated Security=SSPI;")
    { }
}

Then, implement the method one-by-one. Simply copy and paste the code snippets provided below inside your CustomerRepository class.

For GetById:

public Customer GetById(long id)
{
    return Query(id).FirstOrDefault();
}

For GetByName:

public Customer GetByName(string name)
{
    return Query(e => e.Name == name).FirstOrDefault();
}

For GetAll:

public IEnumerable<Customer> GetAll()
{
    return QueryAll();
}

For Insert:

public long Insert(Customer customer)
{
    return Insert<long>(customer);
}

For Update:

public int Update(Customer customer)
{
    return Update(customer);
}

For Delete:

public int Delete(long id)
{
    return Delete(id);
}

By this time, you can now call your own customized methods inside CustomerRepository class.

See sample codes below to insert.

using (var repository = new CustomerRepository())
{
    var customer = new Customer
    {
        Name = "John Doe",
        Address = "New York"
    };
    var id = repository.Insert(customer);
    ...
}

And the code below is for GetByName.

using (var repository = new CustomerRepository())
{
    var customer = repository.GetByName("John Doe");
    ...
}

Register your Repository as Service Component

Now, in order for you to be able to use the Repository in your project as an injectable object within your controllers, you have to register it as a service component.

To register as a service component, follow the steps below.

In your Solution Explorer, double-click the Startup.cs file.
Navigate inside ConfigureServices() method and paste the code below before the method end.

services.AddTransient<ICustomerRepository, CustomerRepository>();

Resolve the missing namespaces by placing the mouse inside the CustomerRepository and press Ctrl+Space > Enter.
Press Ctrl+S keys to save the changes.

The engine will register the CustomerRepository object (as implemented by ICustomerRepository interface) into the services collection. Once the registration is complete, it signifies that our CustomerRepository class is now ready to be used for injection.

Note: We can as well add it as Singleton via AddSingleton() method of IServiceCollection if we wish to have our Repository in a singleton mood.

Create a Controller

Follow the steps below to create a Controller for your Customer entity.

Inside the Controllers folder, add a new class named CustomerController.cs.
The new file named CustomerController.cs will be created. Replace the class implementation with the script below.

[Route("api/[controller]")]
[ApiController]
public class CustomerController : ControllerBase
{
    private ICustomerRepository m_customerRepository;

    public CustomerController(ICustomerRepository repository)
    {
        m_customerRepository = repository;
    }
}

Press Ctrl+S keys to save the changes.

The Controller class above accepts an injected ICustomerRepository service component. We hold an instance of Interface, not Class.

The engine will do the magic for injecting the ICustomerRepository after your service component registration.

Create a Get Method from your Controller

We are almost done! :)

The provided methods below will call the CustomerRepository operation. Please copy the provided code snippets below and paste it inside CustomerController class just right after the Constructor.

[HttpGet()]
public ActionResult<IEnumerable<Customer>> Get()
{
    return m_customerRepository.GetAll().AsList();
}

[HttpGet("{id}")]
public ActionResult<Customer> Get(long id)
{
    return m_customerRepository.GetById(id);
}

Testing the Controller

At this point, our solution is now ready for testing. Build the solution by simply pressing the Alt + B + R keys.

Once the build is complete, press the F5 key to start.

In the browser, type the URL below.

http://localhost:44341/customer

You can also query the specific records with the link below.

http://localhost:44341/customer/1

Where the value 1 is equals to the Customer id you have in the database.

You will notice that the result is being displayed in the browser as JSON file.

How to find a port?

Right-click on the project InventoryAPI from the Solution Explorer and click the Properties.
Click the Debug tab.
Under Web Server Settings, you will see the App URL field that contains the port.

Links

You can see and download the actual project here.

Voila! You have completed this tutorial! Thank you for reading this article. Your support is valuable to us, please do not forget to star our GitHub page.

Happy New Year Everyone!

Mike Pendon (Zym) — Tue, 31 Dec 2019 21:33:50 +0000

We are looking forward for a great year 2020.

We will be spending most of our time at DEV.to by next year (or years to come). We are eager to collaborate further.

Cheers! Mabuhay! Skål!

Mvh!

RepoDb - a hybrid ORM library for .NET.

Mike Pendon (Zym) — Mon, 30 Dec 2019 17:43:09 +0000

Introduction

Introducing RepoDb, a new high performant and efficient hybrid ORM library for .NET.

To begin with, you can start reading our Getting Started page or learn more from our documentation.

Why use RepoDb? Your benefits!

Easy installation, only takes few seconds.
No controlled layer like DbContext, those make the developers speed-up the usability.
Calls to Fluent and Raw-SQL methods is just a dot away.
Repository implementation becomes more simpler by leveraging the built-in repositories.
Can work without the models; everything can be dynamics.
Ease of pain when working with large data sets.
Minimizes the round trips with 2nd-Layer cache.
Transmission of data from different RDBMS DB Providers only take few lines of codes.

How does it works?

Basically, all operations were implemented as extended methods of the IDbConnection object. Once you hold the opened-state of your database connection object, you can then do all the activities you would like to do with your database through those extended methods.

See sample code snippets below:

For Query.

using (var connection = new SqlConnection(ConnectionString))
{
    var customer= connection.Query<Customer>(10045).FirstOrDefault();
}

For Insert.

using (var connection = new SqlConnection(ConnectionString))
{
    var customer = new Customer
    {
        Name = "John Doe",
        Address = "New York"
    };
    var id = connection.Insert<Customer>(customer);
}

For Update.

using (var connection = new SqlConnection(ConnectionString))
{
    var customer = new Customer
    {
        Id = 10045,
        Name = "Jane Doe",
        Address = "Chicago"
    };
    var rowsAffected = connection.Update<Customer>(customer);
}

And for Delete.

using (var connection = new SqlConnection(ConnectionString))
{
    var deletedRows = connection.Delete<Customer>(10045);
}

You can also execute a Raw SQLs via Execute methods (ie: ExecuteQuery, ExecuteNonQuery, ExecuteScalar and ExecuteReader).

using (var connection = new SqlConnection(ConnectionString))
{
    var customers = connection.ExecuteQuery<Customer>("SELECT * FROM [dbo].[Customer];");
}

Or even executing a StoredProcedure.

using (var connection = new SqlConnection(ConnectionString))
{
    var customers = connection.ExecuteQuery<Customer>("sp_GetCustomersByState", new { State = "New York" }, commandType: CommandType.StoredProcedure);
}

Core Features

It has a rich features that you can leverage and use during the development.

Asynchronous Operations
Batch Operations
Bulk Operations
Caching
Connection Persistency
Database Helpers
Database Settings
Expression Trees
Extension Methods
Field Mapping
Inline Hints
Massive Operations (Generics/Explicits/MethodCalls/TableBased)
Multi-Resultset Query
Query Builder
Repositories
Resolvers (CLR Types, DB Types)
Statement Builder
Tracing
Transaction
Type Mapping

To learn more about these operation, please visit our reference implementations page.

Database Supports

The library supports the SqlServer, SqLite, MySql and PostgreSql (soon) RDBMS data providers.

Quality

It also covers a lot of real-world scenarios through its rich Unit Tests and Integration Tests.

Feedback

Please let us know about your feedback if you have some.

Lastly, please do not forget to share this to your friends and colleagues; give us your valuable Star on our GitHub Repository.

We are here to engage more with the .NET Community and collaborate further for the development of this library.