SavantBufferDbConnector
  • Licensed under the Apache License, Version 2.0 Copyright © 2019 Robert Orama
  • Introduction
  • Requirements 1.1
  • Installation 1.2
  • Getting Started 1.3
  • IDbJob 1.4
  • IDbJobCommand 1.5
  • Parameters
  • Essentials 2.1
  • Anonymous 2.2
  • Provider-Specific 2.3
  • Read-Queries
  • Reading 3.1
  • Batch-Reading 3.2
  • Multi-Reading 3.3
  • Dynamic-Reading 3.4
  • Typed-Reading 3.5
  • Scalar-Reading 3.6
  • One To One 3.7
  • One To Many 3.8
  • Buffering 3.9
  • Non-Queries
  • Single 4.1
  • Multiple 4.2
  • Batch Inserts 4.3
  • IDbJob Batching 4.4
  • Techniques
  • Security 5.1
  • Data Mapping 5.2
  • Flags 5.3
  • Cache Settings 5.4
  • Logging 5.5
  • Thread-Safety 5.6
  • Performance
  • Release Notes
  • Known Issues 7.1
  • Pending Features 7.2
  • License 7.3
  • API
  • References
  • Contact

Last updated: Oct 28, 2022 11:45Introduction

What is a DbConnector?

DbConnector is a performance-driven and ADO.NET data provider-agnostic ORM library for .NET developed for individuals who strive to deliver high-quality software solutions. Object-Relational Mapping (ORM) is a technique that lets you query and manipulate data from a database using an object-oriented paradigm. This highly efficient library helps with the task of projecting/mapping data from any database, with the support of any third party data provider, into .NET objects and is comparable to the use of raw ADO.NET data reader implementations.


Why use SavantBuffer's DbConnector?

The purpose of this library is not to replace the Entity Framework since it can be very useful in certain scenarios.   It's for those who prefer to write SQL queries with optimal performance in mind. Some might say writing plain-text SQL queries is easier to understand and debug than using LINQ or .NET’s query syntax. Maybe someone in your team tends to lean towards a "stored procedure only" architecture? You might also be migrating your old Data Access Layer code into a more “modern” framework (e.g. .NET MVC). If you can relate to one of the mentioned, you’ll be happy to use this library. This documentation will further serve as guidance while providing examples on how to tackle these situations and more.

Note

This project started with a “let’s learn” mindset and slowly evolved into a fully concrete and feature-packed software library. It took time, dedication, and sacrifice to make this an attractive solution.

Don’t forget to donate: Donate


How fast is DbConnector?

It's extremely fast and memory efficient! Check out the Performance section for more details.


Is DbConnector an open-source project?

Yes! Feel free to visit the GitHub repo:
https://github.com/SavantBuffer/DbConnector



Requirements 1.1

Before we start, this documentation assumes you have knowledge on the following:

  • C# 6.0 or later (other .NET languages are supported but examples will not be provided in this documentation)
  • .NET Framework 4.5 or later (or .NET Core 1.0 or later)
  • SQL
  • Visual Studio 2017 or later
  • NuGet packages
  • One or more ADO.NET data-providers (e.g. SqlClient, Npgsql, etc.)

These topics are important for you to know since this library was developed leveraging the latest built-in .NET features. To name a few, some of these features are lambda delegates, the task-based async model, and value tuples.

Installation 1.2

DbConnector is installed via Visual Studio's NuGet package manager: https://www.nuget.org/packages/SavantBuffer.DbConnector

PM> Install-Package SavantBuffer.DbConnector

Warning

.NET Standard 2.0 and its implementations are only supported.

Getting Started 1.3

DbConnector Instance

Once you've downloaded and/or included the package into your .NET project, you have to reference the DbConnector and your preferred ADO.NET data provider namespaces in your code:

using DbConnector.Core;
using System.Data.SqlClient; //Using SqlClient in this example.

Now, we can create an instance of the DbConnector using the targeted DbConnection type:

//Note: You can use any type of data provider adapter that implements a DbConnection.
//E.g. PostgreSQL, Oracle, MySql, SQL Server

//Example using SQL Server connection
DbConnector<SqlConnection> _dbConnector = new DbConnector<SqlConnection>("connection string goes here");

Tip

DbConnector instances should be used as a singleton for optimal performance.



Main Functions

There are multiple functions available depending on your goals. The ones for reading data start with the word “Read” and “Non” for non-queries. For a better understanding, the IDbCommand Interface method naming convention was followed for all the functions. This was decided in order to assist in the migration of raw data provider code into DbConnector’s design pattern.

The following are the main generic functions:
  • Read
  • ReadAsAsyncEnumerable (v1.6.0)
  • ReadFirst
  • ReadFirstOrDefault
  • ReadSingle
  • ReadSingleOrDefault
  • ReadToList
  • ReadToDataTable
  • ReadToDataSet
  • ReadToKeyValuePairs
  • ReadToDictionaries
  • ReadToListOfKeyValuePairs
  • ReadToListOfDictionaries
  • NonQuery
  • NonQueries
  • Scalar
  • Build


Basic Example

Let’s say we have an “Employee” class serving as a container for the data we want to fetch from the database. The following function example shows how to synchronously load data from a database entity called “Employees” into an object of type List<Employee>:

public List<Employee> GetAll()
{
    return _dbConnector.ReadToList<Employee>(
        onInit: (cmd) =>
        {
            cmd.CommandText = "SELECT * FROM Employees";

        }).Execute();
}

public List<Employee> GetAllSimple()
{
    //v1.1 Allows the use of simpler overloads:
    return _dbConnector.ReadToList<Employee>("SELECT * FROM Employees").Execute();
}
What happened here?

The DbConnector was used to call the ReadToList function with the use of an “Employee” generic type. This function is requesting an Action<IDbJobCommand> delegate to be declared which is being explicitly provided by the use of the “onInit” named parameter. Inside this delegate, the IDbJobCommand argument object is then used to set the text command to run against the data source. Noticed how a property called “CommandText” was set? This is because the IDbCommand Interface naming convention is being followed like previously mentioned. Lastly, a function called Execute was called. A functional design pattern is being used and, consequently, an IDbJob object is being returned. This pattern allows for a lot of flexibility and you'll learn more in the following sections.

Tip

Starting from v1.1, simple overloads are available for almost all features (Multi-Reading is not supported).

IDbJob 1.4

An IDbJob object instance will be created and returned when calling the available DbConnector functions. This IDbJob object allows for the configuration of events, settings, and the execution of configured commands.

The following functions can be used to manipulate a result using delegates for each particular event:
  • OnExecuted
  • OnCompleted
  • OnError
The following functions can be used to configure settings:
  • WithBuffering
  • WithCache
  • WithLogging
  • WithIsolationLevel

All of these functions will keep returning the same IDbJob reference when invoked. This is because, like we previously mentioned, a functional design pattern is being followed. How is this also helpful? This allows you to build any desired DbConnector implementation and then delegate responsibility back to clients:

public IDbJob<Employee> GetEmployeeDbJob()
{
    return _dbConnector.ReadFirstOrDefault<Employee>(
        onInit: (cmd) =>
        {
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "dbo.GetEmployee";
        })
        .OnCompleted(employee =>
        {
            //Change the name of the employee here
            employee.Name = "This is a change";

            return employee;
        })
        .WithIsolationLevel(IsolationLevel.Snapshot);
}


//v1.1 simple overload example:
public IDbJob<Employee> GetEmployeeDbJobSimple()
{
    return _dbConnector.ReadFirstOrDefault<Employee>("dbo.GetEmployee", null, CommandType.StoredProcedure)
        .OnCompleted(employee =>
        {
            //Change the name of the employee here
            employee.Name = "This is a change";

            return employee;
        })
        .WithIsolationLevel(IsolationLevel.Snapshot);
}

Note

Multiple IDbJobs can be executed together in a shared transaction. This can help you build a better "Repository Layer" by reducing code redundancy.
You can learn more in the IDbJob Batching subsection of this documentation.



Execution

There are multiple ways to execute an IDbJob object with all of its configured commands. You can simply use the “Execute” functions and get a straightforward result. There’s also the “ExecuteHandled” functions which will return any result wrapped inside an IDbResult<T> container along with any thrown errors represented by an Exception object.

In the case of Stream results, for example, then there is the “ExecuteDisposable” functions. These facilitate the extraction of serialized data by entrusting you with the responsibility of “disposing” the result.

All of these are synchronous operations unless you use the available “Async” overloads:
  • Execute
  • ExecuteAsync
  • ExecuteHandled
  • ExecuteHandledAsync
  • ExecuteDisposable
  • ExecuteDisposableAsync
  • ExecuteDisposableHandled
  • ExecuteDisposableHandledAsync
public Task<Employee> GetEmployeeAsync(int id)
{
    return _dbConnector.ReadFirstOrDefault<Employee>(
        onInit: (cmd) =>
        {
            cmd.CommandText = "SELECT * FROM Employees Where id = @id";

            cmd.Parameters.AddFor(new { id });

        }).ExecuteAsync();//Using the Async overload
}


//v1.1 simple overload example:
public Task<Employee> GetEmployeeSimpleAsync(int id)
{
    return _dbConnector
           .ReadFirstOrDefault<Employee>("SELECT * FROM Employees Where id = @id", new { id })
           .ExecuteAsync();//Using the Async overload
}

Danger

Always use parameterized queries. This will prevent SQL injection attacks, help database servers improve execution performance, and allow the DbConnector to cache and return the fastest result(s). Do not forget to read the Parameters and Techniques sections of this documentation.

IDbJobCommand 1.5

Our previous examples reveal how we can set an “onInit” delegate. This delegate has a signature which forces us to set an IDbJobCommand object as an argument therefore allowing us to configure the command properties to be used in the execution of an IDbJob.

Note

It's important to know the “onInit” delegate will be invoked internally, either synchronously or asynchronously depending on the IDbJob “Execute” overload you use, just before a database connection is opened.

You can use the following IDbJobCommand configuration properties:
  • CommandText
  • CommandType
  • CommandBehavior
  • CommandTimeout
  • Parameters
  • MapSettings
  • Flags

Following the IDbCommand naming convention, notice the "CommandText", "CommandType", "CommandBehavior", and "CommandTimeout" properties are very straightforward.

Tip

You should only set the "CommandBehavior" when absolutely necessary. The DbConnector will optimize all commands by default for each particular “Read” scenario by setting the appropriate "CommandBehavior" flag(s). It will also apply the use of CommandBehavior.SequentialAccess when applicable.

"MapSettings" is used to configure column-property matching and "One to One" query joins. The "Flags" enumeration allows you to enable/disable the caching of query mappings and other settings. The following example shows how to leverage the "MapSettings" property for a "One to One" join:

public IEnumerable<Person> ReadPersonByJoinExample()
{
    return _dbConnector.Read<Person>((cmd) =>
    {
        cmd.CommandText = @"
                            SELECT TOP (3) P.*, E.* 
                            FROM [Person].[Person] P
                            LEFT JOIN [HumanResources].[Employee] E ON E.BusinessEntityID = P.BusinessEntityID
                            ";

        //For each Person, assuming the class has an Employee property, 
        //map the joined data into a new Employee instance starting at the BusinessEntityId column.
        cmd.MapSettings.WithSplitOnFor<Employee>(e => e.BusinessEntityId);
    }).Execute();
}

All of these properties, including the "Parameters", will be thoroughly covered in the next sections.

Parameters

We can set parameters using the IDbJobCommand argument inside the "onInit" delegate when invoking DbConnector functions. The IDbJobCommand's "Parameters" property includes multiple overloads which enable the setting of parameters. There are multiple overloads and most of them follow the SqlParameterCollection naming convention.

Remember to ALWAYS use parameters instead of concatenating. Using parameters will prevent SQL injection attacks, help database servers improve execution performance, and allow the DbConnector to cache and return the fastest result(s):

public Task<Employee> GetEmployeeByParameterAsync(string name)
{
    //GOOD PRACTICE:
    return _dbConnector.ReadFirstOrDefault<Employee>(
        onInit: (cmd) =>
        {
            cmd.CommandText = "SELECT * FROM Employees Where name = @name";

            //Good job here using parameters!
            cmd.Parameters.AddWithValue("name", name);

        }).ExecuteAsync();
}

public Task<Employee> GetEmployeeAsync(string name)
{
    //BAD PRACTICE:
    return _dbConnector.ReadFirstOrDefault<Employee>(
        onInit: (cmd) =>
        {
            //THIS IS EXTREMELY BAD! DON'T DO THIS!
            cmd.CommandText = "SELECT * FROM Employees Where name = " + name;

        }).ExecuteAsync();
}

Danger

For implicit performance reasons, the DbConnector caches all "Read" IDbJob operations on first execution in order to provide the fastest results. This is done internally by leveraging .NET DynamicMethods which allows for runtime Microsoft Intermediate Language (MSIL) compilation. The DbConnector will continue to cache, using more and more memory, any new/different IDbJob operation if you decide to ignore the use of parameters.

Please review the Cache Settings subsection for more information.



Essentials 2.1

The "AddWithValue" method can be used to add a paramater (as an input) just like when using a SqlParameterCollection:

public Task<Employee> GetEmployeeByParameterAsync(string name)
{
    return _dbConnector.ReadFirstOrDefault<Employee>(
        onInit: (cmd) =>
        {
            cmd.CommandText = "SELECT * FROM Employees Where name = @name";

            //AddWithValue example:
            cmd.Parameters.AddWithValue("name", name);

        }).ExecuteAsync();
}

Output parameters are also supported:

public bool OutParamExample(string name)
{
    return _dbConnector.NonQuery<bool>((cmd) =>
    {
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.CommandText = @"dbo.UpdateName";

        cmd.Parameters.AddWithValue("name", name);

        //Set a boolean parameter as output:
        cmd.Parameters.Add("someVariableName", DbType.Boolean).Direction = ParameterDirection.Output;
    })
    .OnExecuted((v, e) =>
    {
        //Find, cast, and return the boolean once the IDbJobCommand has been executed
        return (bool)e.Parameters["someVariableName"].Value;
    })
    .Execute();
}

Anonymous 2.2

Let's face it, using "AddWithValue" can become a maintenance nightmare when adding multiple parameters. The "AddFor" method is available in case you have an object full of properties or you simply want to use an Anonymous .NET object.

All valid properties found in a container object will be added independently as input parameters:

public IEnumerable<Person> AddForExample()
{
    return _dbConnector.Read<Person>((cmd) =>
    {
        cmd.CommandText = @"
            SELECT *
            FROM [Person].[Person] 
            WHERE 
                id = @id
                AND rowguid = @rowguid
                AND name = @Name
        ";

        //Using a class object with AddFor:
        cmd.Parameters.AddFor(new Currency { Name = "this is a name" });

        //Using an Anonymous object with AddFor:
        cmd.Parameters.AddFor(new { id = "9000", rowguid = new Guid("92C4279F-1207-48A3-8448-4636514EB7E2") });

    }).Execute();
}

public IEnumerable<Person> AddForExampleSimple()
{
    //v1.1 allows the use of simpler overloads:
    return _dbConnector.Read<Person>(
        @"
            SELECT *
            FROM [Person].[Person] 
            WHERE 
                id = @id
                AND rowguid = @rowguid
                AND name = @Name
        ",
        new
        {
            id = "9000",
            Name = "this is a name",
            rowguid = new Guid("92C4279F-1207-48A3-8448-4636514EB7E2")
        }
    ).Execute();
}

Looking at this example we notice the "AddFor" is case-sensitive. Always pay close attention to this detail since it can easily introduce bugs in your query.

Another thing to note is "AddFor" will add all the valid properties of an object as parameters. The "Currency" object in the example might have extra properties not being set but they will still be added anyways. Because of this and to prevent exceptions, the use of Anonymous objects is encouraged since they explicitly allow for better property customization.

Note

  • Field mapping is not supported.
  • Enums will be converted to their applicable underlying type.
  • For now, IEnumerable objects are not supported.

Provider-Specific 2.3

The "GetDbParameters" function allows you to add provider-specific parameter types. Invoke this function, cast the result to an applicable DbParameterCollection type, and then use any of its supported methods:

public Guid ProviderSpecificExample(Currency dtoToUpdate)
{
    return _dbConnector.NonQuery<Guid>((cmd) =>
        {
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = @"dbo.UpdateCurrency";

            cmd.Parameters.AddFor(dtoToUpdate);

            //Invoke GetDbParameters and cast to SqlParameterCollection:
            var parameters = cmd.GetDbParameters() as SqlParameterCollection;

            parameters.Add("someVariableName", SqlDbType.UniqueIdentifier).Direction = ParameterDirection.Output;

        })
        .OnExecuted((v, e) => {
            //Get and return the "out" value (you can also cast "e.Parameters" to "SqlParameterCollection" if you need to):
            return (Guid)e.Parameters["someVariableName"].Value;
        })                
        .Execute();
}

Read-Queries

Reading and mapping data as fast and efficiently possible is DbConnector's greatest quality. It allows for great flexibility and puts other ORMs to sweat when talking about performance.

For data reading, you need three main things: a DbConnector instance with a configured connection string, a SQL query, and in most cases a .NET data transfer object (DTO). The DbConnector will internally map all valid properties by name to incoming column names when using a non-native and property based DTO. It can do this extremely fast because it caches all IDbJob operations at runtime via dynamic MSIL code.

Note

Do not forget to read the Introduction and Techniques sections.

Tip

Starting from v1.1, simple overloads are available for almost all features (Multi-Reading is not supported).

Data projection/mapping will always be accomplished by this cached logic after the first execution of an IDbJob. Property mapping is case-insensitive (since v1.4.0) and can be configured in multiple ways which will be covered later in the Data Mapping subsection.

using DbConnector.Core;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.SqlClient;
using System.Threading.Tasks;

namespace DbConnector.Example.Repository
{
    /// <summary>
    /// DTO model example.
    /// </summary>
    public class Example
    {
        /// <summary>
        /// The use of attributes will be covered in the Techniques - Data Mapping 5.2 section.
        /// </summary>
        [Column("id")]
        public int Id { get; set; }

        [Column("name")]
        public string Name { get; set; }

        [Column("modified_date")]
        public DateTime ModifiedDate { get; set; }

        [NotMapped]
        public int NotMappedExample { get; set; }
    }

    /// <summary>
    /// Repository example.
    /// TODO: Inherit from a base repository class.
    /// </summary>
    public class ExampleRepository
    {
        protected static IDbConnector<SqlConnection> _dbConnector;

        /// <summary>
        /// Static constructor
        /// </summary>
        static ExampleRepository()
        {
            //TODO: Use Dependency Injection inside a non-static constructor in order to provide this instance.
            _dbConnector = new DbConnector<SqlConnection>("connection string goes here");
        }


        /// <summary>
        /// Using async allows you to execute asynchronous grouped operations 
        /// with the help of Task.WaitAll (as an example) therefore improving response times.
        /// </summary>
        public Task<Example> Get(int id)
        {
            return _dbConnector.ReadFirstOrDefault<Example>(
                onInit: (cmd) =>
                {
                    cmd.CommandText = "SELECT * FROM Example WHERE id = @id";

                    cmd.Parameters.AddWithValue("id", id);

                }).ExecuteAsync();
        }

        public Task<IEnumerable<Example>> GetAll()
        {
            return BuildGetAll().ExecuteAsync();
        }

        public Task<List<Example>> GetAllByList()
        {
            return _dbConnector.ReadToList<Example>("SELECT * FROM Example").ExecuteAsync();
        }

        /// <summary>
        /// Leave it up to the client.
        /// </summary>
        public IDbJob<IEnumerable<Example>> BuildGetAll()
        {
            return _dbConnector.Read<Example>("SELECT * FROM Example");
        }


        /// <summary>
        /// Non-Queries will be covered in section 4.
        /// </summary>
        public Task<int?> Insert(Example entity)
        {
            return _dbConnector.NonQuery(
                onInit: (cmd) =>
                {
                    cmd.CommandText = @"
                        INSERT INTO Example
                        (id, name)
                        VALUES(@Id, @Name)
                    ";

                    cmd.Parameters.AddFor(new { entity.Id, entity.Name });

                }).ExecuteAsync();
        }
    }
}
Transactions

Read-queries will NOT use a custom transaction by default unless you specify an IsolationLevel:

public Task<Example> Get(int id)
{
    return _dbConnector.ReadFirstOrDefault<Example>(
        onInit: (cmd) =>
        {
            cmd.CommandText = "SELECT * FROM Example WHERE id = @id";

            cmd.Parameters.AddWithValue("id", id);

        })
        .WithIsolationLevel(IsolationLevel.Snapshot)
        .ExecuteAsync();
}

Now that we've covered the basics, various examples will be provided in this section on how to "Read" data from a database. These examples will be divided by built-in features and common SQL strategies.



Reading 3.1

There are multiple DbConnector generic functions available for reading depending on your goals:

  • Read - Used to read all rows from a query result into an IDbJob<IEnumerable<T>>.
  • ReadAsAsyncEnumerable (v1.6.0) - Used to read all rows from a query result into an IDbJob<IAsyncEnumerable<T>>. Please review the Buffering subsection for more information.
  • ReadFirst - Used to read the first row from a query result into an IDbJob<T>. Throws exception if query result is empty.
  • ReadFirstOrDefault - Used to read the first row from a query result into an IDbJob<T>. Default value of T if query result is empty.
  • ReadSingle - Used to read a single row from a query result into an IDbJob<T>. Throws exception if query result has multiple rows or is empty.
  • ReadSingleOrDefault - Used to read a single row from a query result into an IDbJob<T>. Throws exception if query result has multiple rows. Default value of T if query result is empty.
  • ReadToList - Used to read all rows from a query result into an IDbJob<List<T>>.
  • ReadToDataTable - Used to read all rows from a query result into an IDbJob<DataTable>.
  • ReadToDataSet - Used to read all rows from multiple query results into an IDbJob<DataSet>.
  • ReadToKeyValuePairs - Used to read all rows from a query result into a weakly-typed IDbJob<IEnumerable<List<KeyValuePair<string, object>>>>.
  • ReadToDictionaries - Used to read all rows from a query result into a weakly-typed IDbJob<IEnumerable<Dictionary<string, object>>>.
  • ReadToListOfKeyValuePairs - Used to read all rows from a query result into a weakly-typed IDbJob<List<List<KeyValuePair<string, object>>>>.
  • ReadToListOfDictionaries - Used to read all rows from a query result into a weakly-typed IDbJob<List<Dictionary<string, object>>>.
  • ReadTo - Used to read all rows from multiple query results into an IDbJob<T> with the use of custom logic.

It's important to note these names are explicit, try to be self-explanatory, and follow .NET's System.Linq.Enumerable naming convention.

using DbConnector.Core;
using DbConnector.Core.Extensions;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Threading.Tasks;

namespace DbConnector.Example.Repository
{
    /// <summary>
    /// Repository example.
    /// TODO: Inherit from a base repository class.
    /// </summary>
    public class ExampleRepository
    {
        protected static IDbConnector<SqlConnection> _dbConnector;

        /// <summary>
        /// Static constructor
        /// </summary>
        static ExampleRepository()
        {
            //TODO: Use Dependency Injection inside a non-static constructor in order to provide this instance.
            _dbConnector = new DbConnector<SqlConnection>("connection string goes here");
        }


        /// <summary>
        /// Using async allows you to execute asynchronous grouped operations 
        /// with the help of Task.WaitAll (as an example) therefore improving response times.
        /// </summary>
        public Task<Example> Get(int id)
        {
            return _dbConnector.ReadFirstOrDefault<Example>(
                onInit: (cmd) =>
                {
                    cmd.CommandText = "SELECT * FROM Example WHERE id = @id";

                    cmd.Parameters.AddWithValue("id", id);

                }).ExecuteAsync();
        }

        public IEnumerable<Example> GetAll()
        {
            return _dbConnector.Read<Example>(
                onInit: (cmd) =>
                {
                    cmd.CommandText = "SELECT * FROM Example";
                }).Execute();
        }

        public List<Example> GetAllByList()
        {
            return _dbConnector.ReadToList<Example>(
                onInit: (cmd) =>
                {
                    cmd.CommandText = "SELECT * FROM Example";

                }).Execute();
        }

        public DataTable GetAllByDataTable()
        {
            return _dbConnector.ReadToDataTable(
                onInit: (cmd) =>
                {
                    cmd.CommandText = "SELECT * FROM Example";

                })
                .Execute();
        }

        public DataSet GetAllByDataSet()
        {
            return _dbConnector.ReadToDataSet(
                onInit: (cmds) =>
                {
                    cmds.Enqueue((cmd) =>
                    {
                        cmd.CommandText = "SELECT * FROM Example";
                    });

                    cmds.Enqueue((cmd) =>
                    {
                        cmd.CommandText = "SELECT * FROM AnotherRelatedEntity";
                    });

                }).Execute();
        }

        public Example GetAllByReadTo()
        {
            return _dbConnector.ReadTo<Example>(
                onInit: (cmds) =>
                {
                    cmds.Enqueue((cmd) =>
                    {
                        cmd.CommandText = "SELECT * FROM Example";
                    });

                    cmds.Enqueue((cmd) =>
                    {
                        cmd.CommandText = "SELECT id FROM AnotherRelatedEntity";
                    });
                },
                onLoad: (Example d, IDbExecutionModel e, DbDataReader odr) =>
                {
                    //Use the IDbExecutionModel's index which is based on the onInit's enqueued order.
                    switch (e.Index)
                    {
                        case 0:
                            //Use the FirstOrDefault extension function 
                            //from the DbConnector.Core.Extensions namespace
                            d = odr.FirstOrDefault<Example>();
                            break;
                        case 1:
                            //TODO: Do something with this second result?
                            int idAnotherRelatedEntity = odr.FirstOrDefault<int>();
                            break;
                        default:
                            break;
                    }

                    return d;
                }).Execute();
        }
    }


    /// <summary>
    /// v1.1 Simple overloads example
    /// Repository example.
    /// TODO: Inherit from a base repository class.
    /// </summary>
    public class ExampleSimpleRepository
    {
        protected static IDbConnector<SqlConnection> _dbConnector;

        /// <summary>
        /// Static constructor
        /// </summary>
        static ExampleSimpleRepository()
        {
            //TODO: Use Dependency Injection inside a non-static constructor in order to provide this instance.
            _dbConnector = new DbConnector<SqlConnection>("connection string goes here");
        }


        /// <summary>
        /// Using async allows you to execute asynchronous grouped operations 
        /// with the help of Task.WaitAll (as an example) therefore improving response times.
        /// </summary>
        public Task<Example> Get(int id)
        {
            //v1.1 usage example:
            return _dbConnector.ReadFirstOrDefault<Example>("SELECT * FROM Example WHERE id = @id", new { id }).ExecuteAsync();
        }

        public IEnumerable<Example> GetAll()
        {
            //v1.1 usage example:
            return _dbConnector.Read<Example>("SELECT * FROM Example").Execute();
        }

        public List<Example> GetAllByList()
        {
            //v1.1 usage example:
            return _dbConnector.ReadToList<Example>("SELECT * FROM Example").Execute();
        }

        public DataTable GetAllByDataTable()
        {
            //v1.1 usage example:
            return _dbConnector.ReadToDataTable("SELECT * FROM Example").Execute();
        }

        public DataSet GetAllByDataSet()
        {
            //v1.1 usage example:
            return _dbConnector.ReadToDataSet("SELECT * FROM Example; SELECT * FROM AnotherRelatedEntity").Execute();
        }
    }
}

Constraints

Supported types for generic reading functions:

  • DataSet
  • DataTable
  • Dictionary<string,object>
  • List<KeyValuePair<string, object>>
  • Any .NET built-in type (e.g. string, int, bool, DateTime, etc.)
  • Any struct or class with a parameterless constructor not assignable from IEnumerable (Note: only properties will be mapped)

Batch-Reading 3.2

SQL queries can be "batched" together in a single operation, regardless of a transaction presence, thus reducing the amount of database connections required.

The following query returns two results:
public (IEnumerable<Example>, IEnumerable<ExampleSalary>) GetAllByBatch()
{
    return _dbConnector.Read<Example, ExampleSalary>(
        onInit: (cmd) =>
        {
            cmd.CommandText = @"
                SELECT * FROM Example;
                SELECT * FROM ExampleSalaryEntity;
            ";

        }).Execute();
}

public (IEnumerable<Example>, IEnumerable<ExampleSalary>) GetAllByBatchFromSP()
{
    return _dbConnector.Read<Example, ExampleSalary>(
        onInit: (cmd) =>
        {
            //A stored procedure is being used here.
            //In this case, based on the generic signature, only two results 
            //will be returned from the stored procedure.                    
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = @"dbo.GetQueriesByBatch";

        }).Execute();
}

//v1.1 simple overload example:
public (IEnumerable<Example>, IEnumerable<ExampleSalary>) GetAllByBatch()
{
    return _dbConnector.Read<Example, ExampleSalary>("SELECT * FROM Example; SELECT * FROM ExampleSalaryEntity;").Execute();
}
What happened here?

The DbConnector was used to call the "Read" function with the use of two generic types. Another thing that changed here was the query used for the "CommandText". Two queries are now in a single operation. This is commonly known as a "batch query".

This batch of queries will be executed and its results will be mapped into two separate objects. All of this will be synchronously done with the use of a single connection.

The last thing some might find unusual is the type of result being returned from the IDbJob. This is called a .NET "value tuple". A value tuple is a data structure that has a specific number and sequence of elements. This structure acts as a generic wrapper and removes the need for creating custom objects when a logical and/or common relationship cannot be established.

Tip

You can find numerous online tutorials and articles related to .NET value tuples. Make sure you familiarize yourself with this topic since DbConnector's features depend on its implementation.


Constraints

DbConnector’s batch-reading feature only supports up to 8 generic types, limiting to the same amount of queries, when using the following generic functions:

  • Read
  • ReadFirst
  • ReadFirstOrDefault
  • ReadSingle
  • ReadSingleOrDefault
  • ReadToList
/// <summary>
/// Example: Using 8 queries with batch-reading.
/// </summary>
/// <returns></returns>
public (int, int, int, int, int, int, int, int) GetAllByBatch()
{
    return _dbConnector.ReadFirstOrDefault<int, int, int, int, int, int, int, int>(
        onInit: (cmd) =>
        {
            cmd.CommandText = @"
                SELECT id FROM Entity1;
                SELECT id FROM Entity2;
                SELECT id FROM Entity3;
                SELECT id FROM Entity4;
                SELECT id FROM Entity5;
                SELECT id FROM Entity6;
                SELECT id FROM Entity7;
                SELECT id FROM Entity8;
            ";

        }).Execute();
}

Multi-Reading 3.3

Multiple unrelated/independent queries can be mapped using a single DbConnector function. Just like the batch-reading feature, multi-reading was implemented leveraging .NET value tuples:

Tip

Reading the Batch-Reading 3.2 section is encouraged.

public Task<(IEnumerable<Employee>, IEnumerable<Employee>)> GetEmployeesByMultiReader()
{
    return _dbConnector.Read<Employee, Employee>(
        onInit: () => (
        (cmd1) =>
        {
            cmd1.CommandText = @"
                SELECT * FROM Employees WHERE name LIKE 'a%'; 
            ";
        }
        ,
        (cmd2) =>
        {
            cmd2.CommandText = @"
                SELECT * FROM Employees WHERE name LIKE 'b%';
            ";
        }
        ), withIsolatedConnections: true).ExecuteAsync();
}
What happened here?

In this example, the "onInit" signature is now different. A .NET value tuple is being returned with multiple delegates and each one of these delegates represents one IDbJobCommand. As you can see, there is one per generic type being declared.

All the configured IDbJobCommands will be executed asynchronously using separate connections by default. This can be easily configured via the "withIsolatedConnections" parameter when wanting to use a single connection for all of the commands.


Constraints

DbConnector’s multi-reading feature only supports up to 8 generic types, limiting to the same amount of IDbJobCommands returned from the "onInit", when using the following generic functions:

  • Read
  • ReadFirst
  • ReadFirstOrDefault
  • ReadSingle
  • ReadSingleOrDefault
  • ReadToList
public Task<(
      IEnumerable<Employee>
    , IEnumerable<Employee>
    , IEnumerable<Employee>
    , IEnumerable<Employee>
    , IEnumerable<Employee>
    , IEnumerable<Employee>
    , IEnumerable<Employee>
    , IEnumerable<Employee>
    )> GetEmployeesByMultiReader()
{
    return _dbConnector.Read<
        Employee,
        Employee,
        Employee,
        Employee,
        Employee,
        Employee,
        Employee,
        Employee>(
        onInit: () => (
        (cmd1) =>
        {
            cmd1.CommandText = @"
                SELECT * FROM Employees WHERE name LIKE 'a%'; 
            ";
        }
        ,
        (cmd2) =>
        {
            cmd2.CommandText = @"
                SELECT * FROM Employees WHERE name LIKE 'b%';
            ";
        }
        ,
        (cmd3) =>
        {
            cmd3.CommandText = @"
                SELECT * FROM Employees WHERE name LIKE 'c%';
            ";
        }
        ,
        (cmd4) =>
        {
            cmd4.CommandText = @"
                SELECT * FROM Employees WHERE name LIKE 'd%';
            ";
        }
        ,
        (cmd5) =>
        {
            cmd5.CommandText = @"
                SELECT * FROM Employees WHERE name LIKE 'e%';
            ";
        }
        ,
        (cmd6) =>
        {
            cmd6.CommandText = @"
                SELECT * FROM Employees WHERE name LIKE 'f%';
            ";
        }
        ,
        (cmd7) =>
        {
            cmd7.CommandText = @"
                SELECT * FROM Employees WHERE name LIKE 'g%';
            ";
        },
        (cmd8) =>
        {
            cmd8.CommandText = @"
                SELECT * FROM Employees WHERE name LIKE 'h%';
            ";
        }
        )).ExecuteAsync();
}

Tip

DbConnector’s ReadTo function can be used as an alternative when dealing with complex queries.

Dynamic-Reading 3.4

Queries can be dynamically mapped using .NET's dynamic object and keyword feature. This is internally accomplished using the .NET ExpandoObject built-in class.

Although .NET dynamic objects are natively more resource intensive, a weakly-typed approach can be beneficial for some scenarios. This could be true, for example, when wanting to reduce code maintenance in a web service architecture where the main goal is to deliver data back to the client without any alterations.

The following example demonstrates the advantages of dynamic types:
public dynamic GetExampleDynamically(int id)
{
    //Notice there is no generic type being configured
    //making this a weakly-typed approach:
    return _dbConnector.ReadFirstOrDefault(
        onInit: (cmd) =>
        {
            cmd.CommandText = "SELECT id, name FROM Example WHERE id = @id";
            cmd.Parameters.AddFor(new { id });

        }).Execute();
}

//v1.1 simple overload example:
public dynamic GetExampleSimpleDynamically(int id)
{
    //Notice there is no generic type being configured
    //making this a weakly-typed approach:
    return _dbConnector.ReadFirstOrDefault("SELECT id, name FROM Example WHERE id = @id", new { id }).Execute();
}

public void ClientExample()
{
    int testId = 1;

    dynamic example = GetExampleDynamically(testId);

    if (example != null)
    {
        //The property names will 
        //equal the column names from the query result:
        Console.WriteLine(example.id);
        Console.WriteLine(example.name);

        //The following line will throw a runtime exception 
        //since "test" is not part of the query result:
        Console.WriteLine(example.test);
    }
}

Constraints

DbConnector’s dynamic-reading feature is only supported for the following generic functions:

  • Read
  • ReadFirst
  • ReadFirstOrDefault
  • ReadSingle
  • ReadSingleOrDefault
  • ReadToList

Note

DbConnector’s Batch-Reading and Multi-Reading features can also support dynamic types!

Typed-Reading 3.5

DbConnector supports the mapping of data using .NET's System.Type class as an alternative to generics:

public object GetExampleByType(int id)
{
    //Notice the Example's System.Type is being used:
    return _dbConnector.ReadFirstOrDefault(typeof(Example),
        onInit: (cmd) =>
        {
            cmd.CommandText = "SELECT id, name FROM Example WHERE id = @id";
            cmd.Parameters.AddFor(new { id });

        }).Execute();
}

//v1.1 simple overload example:
public object GetExampleSimpleByType(int id)
{
    //Notice the Example's System.Type is being used:
    return _dbConnector.ReadFirstOrDefault(typeof(Example), "SELECT id, name FROM Example WHERE id = @id", new { id }).Execute();
}

public void ClientExample()
{
    int testId = 1;

    object boxedExampleObj = GetExampleByType(testId);

    if (boxedExampleObj != null)
    {
        Example example = boxedExampleObj as Example;

        //TODO: Do something with the example object
    }
}

Constraints

DbConnector’s typed-reading feature is only supported for the following generic functions:

  • Read
  • ReadFirst
  • ReadFirstOrDefault
  • ReadSingle
  • ReadSingleOrDefault
  • ReadToList

Scalar-Reading 3.6

Reading "scalar" values provides the ability to get the first column of the first row in the result set returned by a query. All other columns and rows are ignored otherwise:

Note

The DbConnector function for scalar-reading does not start with "Read". This is a special case therefore the available function is named "Scalar".

public Task<int?> GetScalarExample(string nameSearch)
{
    //This will return the id of the first row result:  
    return _dbConnector.Scalar<int?>(
        onInit: (cmd) =>
        {
            cmd.CommandText = "SELECT id, name FROM Example WHERE name like '%' + @nameSearch + '%'";

            cmd.Parameters.AddWithValue("nameSearch", nameSearch);

        }).ExecuteAsync();
}

//v1.1 simple overload example:
public Task<int?> GetScalarSimpleExample(string nameSearch)
{
    //This will return the id of the first row result:  
    return _dbConnector
        .Scalar<int?>("SELECT id, name FROM Example WHERE name like '%' + @nameSearch + '%'", new { nameSearch })
        .ExecuteAsync();
}

Constraints

Supported types for scalar-reading:

  • Any .NET built-in type (e.g. string, int, bool, DateTime, etc.) including arrays
  • Any value/non-reference type not assignable from IEnumerable

Warning

The assigned generic type must directly match the result type unless an indirect match can be made (e.g. int to Enum).
Please review the Data Mapping subsection for more information.

One To One 3.7

Mapping "one to one" data relations can easily be accomplished by following a few steps. First, a .NET container object needs to have a property relation with another object thus establishing a logical representation of a database "foreign" relationship:

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace DbConnector.Example.Entities
{
    public class Person
    {
        [Column("PersonId")]
        public int PersonId { get; set; }

        public string Title { get; set; }

        [Required]
        [StringLength(50)]
        public string FirstName { get; set; }

        [StringLength(50)]
        public string MiddleName { get; set; }

        [Required]
        [StringLength(50)]
        public string LastName { get; set; }

        [StringLength(10)]
        public string Suffix { get; set; }

        [Column("Date_Modified")]
        public DateTime ModifiedDate { get; set; }

        //"One To One" relation representation
        public Employee Employee { get; set; }
    }

    public class Employee
    {
        [Column("EmployeeId")]
        public int EmployeeId { get; set; }

        [Column("JobTitle")]
        public string JobTitle { get; set; }

        [Column("Date_Modified")]
        public DateTime ModifiedDate { get; set; }
    }
}

Once the container objects are in place and ready to be used, execute an IDbJob with the proper configuration:

public IEnumerable<Person> ReadPersonsByOneToOneExample()
{
    return _dbConnector.Read<Person>((cmd) =>
        {
        //Property names of the joined object should be grouped together 
        //since the DbConnector will map all matched columns by name.
        //Otherwise, a wrong match could be made when dealing with misplaced duplicate names.
        cmd.CommandText = @"
            SELECT
                P.PersonId,
                P.FirstName,
                P.MiddleName,
                P.LastName,
                P.Suffix,
                P.Date_Modified,

                E.EmployeeId,
                E.JobTitle,
                E.Date_Modified,
            FROM Person P
            LEFT JOIN Employee E ON E.EmployeeId = P.PersonId;
        ";

        //Strongly-typed usage:
        cmd.MapSettings.WithSplitOnFor<Employee>(e => e.EmployeeId);

        //You can also use the weakly-typed version:
        //cmd.MapSettings.WithSplitOn<Employee>("EmployeeId");

    }).Execute();
}
What happened here?

In this example, the "MapSettings" property of the IDbJobCommand was used to configure the joined column name. This is being done with the help of "WithSplitOnFor". Note, as an alternative, the weakly-typed but less maintainable "WithSplitOn" version can be used.

It is worth mentioning the configured column name will act as the inclusive starting point for the "Employee" object mapping. Another important detail is the column names need to be grouped together in the query's select statement. Not doing so could result in a wrong match when when dealing with misplaced/duplicate names.

Danger

Column names need to be grouped together in the query's select statement for proper "one to one" mapping. Not doing so could result in a wrong match when when dealing with misplaced/duplicate names.

With that said, the DbConnector will create an "Employee" object for each "Person" returned from the query. Each "Employee" object will have its properties mapped starting from the "EmployeeId" column name based on the "MapSettings.WithSplitOnFor" configuration.

Warning

"One to one" will only work if the target IDbJobCommand is properly configured by one of its "MapSettings.WithSplitOn" overloads.


Constraints

DbConnector’s "one to one" mapping feature is only supported for the following generic functions regardless of the reading approach:

  • Read
  • ReadFirst
  • ReadFirstOrDefault
  • ReadSingle
  • ReadSingleOrDefault
  • ReadToList

One To Many 3.8

Mapping "one to many" data relations can be accomplished by combining any number of DbConnector’s built-in generic reading functions, any feature (Batch-Reading, Multi-Reading), or the use of custom logic with the help of "ReadTo":

Tip

Using Batch-Reading for "One To Many" is encouraged for optimal performance.

using DbConnector.Core;
using DbConnector.Core.Extensions;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;

namespace DbConnector.Example.Repository
{
    public class Person
    {
        [Column("PersonId")]
        public int PersonId { get; set; }

        public string Title { get; set; }

        [Required]
        [StringLength(50)]
        public string FirstName { get; set; }

        [StringLength(50)]
        public string MiddleName { get; set; }

        [Required]
        [StringLength(50)]
        public string LastName { get; set; }

        [StringLength(10)]
        public string Suffix { get; set; }

        [Column("Date_Modified")]
        public DateTime ModifiedDate { get; set; }

        //"One To Many" relation representation via IEnumerable
        public IEnumerable<EmailAddress> Emails { get; set; }
    }

    public class EmailAddress
    {
        [Column("EmailAddressId")]
        public int EmailAddressId { get; set; }

        [Column("PersonId")]
        public int PersonId { get; set; }

        [Column("EmailAddressValue")]
        [StringLength(50)]
        public string EmailAddressValue { get; set; }
    }

    /// <summary>
    /// Repository example.
    /// TODO: Inherit from a base repository class.
    /// </summary>
    public class PersonRepository
    {
        protected static IDbConnector<SqlConnection> _dbConnector;

        /// <summary>
        /// Static constructor
        /// </summary>
        static PersonRepository()
        {
            //TODO: Use Dependency Injection inside a non-static constructor in order to provide this instance.
            _dbConnector = new DbConnector<SqlConnection>("connection string goes here");
        }



        public async Task<Person> Get(int personId)
        {
            //Note: Using Batch-Reading for "One To Many" is encouraged.
            //Load Person
            Person personObj = _dbConnector.ReadFirstOrDefault<Person>(
                onInit: (cmd) =>
                {
                    cmd.CommandText = @"
                        SELECT * 
                        FROM Person 
                        where PersonId = @PersonId
                    ";

                    cmd.Parameters.AddWithValue("PersonId", personId);

                }).Execute();


            if (personObj != null)
            {
                //Load Emails
                personObj.Emails = await _dbConnector.Read<EmailAddress>(
                    onInit: (cmd) =>
                    {
                        cmd.CommandText = @"
                            SELECT * 
                            FROM EmailAddress 
                            where PersonId = @PersonId
                        ";

                        cmd.Parameters.AddWithValue("PersonId", personId);

                    }).ExecuteAsync();
            }

            return personObj;
        }

        public async Task<Person> GetByBatchReader(int personId)
        {
            //Loading by Batch-Reading is both cleaner and efficient:
            var batchResult = await _dbConnector.Read<Person, EmailAddress>(
                            @"
                                SELECT TOP(1) 
                                FROM Person 
                                where PersonId = @PersonId;
                                SELECT * 
                                FROM EmailAddress 
                                where PersonId = @PersonId;
                            ", new { PersonId = personId }).ExecuteAsync();

            Person personObj = batchResult.Item1.FirstOrDefault();

            if (personObj != null)
            {
                personObj.Emails = batchResult.Item2;
            }

            return personObj;
        }

        public async Task<Person> GetByMultiReader(int personId)
        {
            //Note: Using Batch-Reading for "One To Many" is encouraged.
            //Loading by Multi-Reading:
            var batchResult = await _dbConnector.Read<Person, EmailAddress>(
                onInit: () =>
                (
                    (cmd) =>
                    {
                        cmd.CommandText = @"
                            SELECT TOP(1) 
                            FROM Person 
                            where PersonId = @PersonId;
                        ";

                        cmd.Parameters.AddWithValue("PersonId", personId);

                    },
                    (cmd) =>
                    {
                        cmd.CommandText = @"
                            SELECT * 
                            FROM EmailAddress 
                            where PersonId = @PersonId;
                        ";

                        cmd.Parameters.AddWithValue("PersonId", personId);

                    }
                )).ExecuteAsync();

            Person personObj = batchResult.Item1.FirstOrDefault();

            if (personObj != null)
            {
                personObj.Emails = batchResult.Item2;
            }

            return personObj;
        }

        public Task<Person> GetByReadTo(int personId)
        {
            //Note: Using Batch-Reading for "One To Many" is encouraged.
            //Loading by custom logic with ReadTo:
            return _dbConnector.ReadTo<Person>(
                onInit: (cmds) =>
                {
                    cmds.Enqueue((cmd) =>
                    {
                        cmd.CommandText = @"
                            SELECT TOP(1) 
                            FROM Person 
                            where PersonId = @PersonId;
                        ";

                        cmd.Parameters.AddWithValue("PersonId", personId);

                    });

                    cmds.Enqueue((cmd) =>
                    {
                        cmd.CommandText = @"
                            SELECT * 
                            FROM EmailAddress 
                            where PersonId = @PersonId;
                        ";

                        cmd.Parameters.AddWithValue("PersonId", personId);

                    });
                },
                onLoad: (Person p, IDbExecutionModel e, DbDataReader odr) =>
                {
                    //Use the IDbExecutionModel's index which is based on the onInit's enqueued order.
                    switch (e.Index)
                    {
                        case 0:
                            //Use the FirstOrDefault extension function 
                            //from the DbConnector.Core.Extensions namespace
                            p = odr.FirstOrDefault<Person>();
                            break;
                        case 1:
                            if (p != null)
                            {
                                //Use the ToEnumerable extension function
                                //from the DbConnector.Core.Extensions namespace
                                p.Emails = odr.ToEnumerable<EmailAddress>(e.Token, e.JobCommand);
                            }
                            break;
                        default:
                            break;
                    }

                    return p;
                }).ExecuteAsync();
        }
    }
}

Buffering 3.9

All data is completely buffered, meaning all records are loaded into memory, by default when using DbConnector’s IEnumerable generic reading functions. What if you needed to iterate thru a large amount of records, for whatever reason, just to modify one specific property? Maybe your goal is to reduce memory impact in a high-availability troubleshooting scenario?

Note the same amount of memory will always be used when comparing buffered vs. non-buffered scenarios. The difference being having memory usage all at once vs incrementally.

The DbConnector can easily be configured for supported IDbJob instances leveraging the “WithBuffering” function. This allows buffering to be disabled (a.k.a. deferred/lazy buffering/execution) for IEnumerable types only:

Tip

Please review the .NET yield keyword. This feature uses "yielded" logic internally by postponing the disposal of database connections and related resources.

Tip

Starting from v1.6, asynchronous steams are supported when using the "ReadAsAsyncEnumerable" functions. There's no need to use "WithBuffering" since IAsyncEnumerables will always execute without buffering.

Danger

Always perform an iteration of the returned IEnumerable by either implementing a "for-each" loop or a data projection (e.g. invoking the "ToList" extension). You can also dispose the enumerator as an alternative.

Not doing so will internally leave disposable resources opened (e.g. database connections) consequently creating memory leak scenarios.

Warning

Exceptions may occur while looping deferred IEnumerable types because of the implicit database connection dependency.

public Task<IEnumerable<Employee>> GetAllEmployees()
{
    return _dbConnector.Read<Employee>("SELECT * FROM Employees")
        //Set Buffering to false.
        .WithBuffering(false)
        .ExecuteAsync();
}

//https://learn.microsoft.com/en-us/dotnet/csharp/whats-new/tutorials/generate-consume-asynchronous-stream?source=recommendations
public IAsyncEnumerable<Employee> GetAllEmployeesAsAsyncEnumerable()
{
    //v1.6 supports asynchronous streams
    //No need to call ".WithBuffering(false)" since IAsyncEnumerables will always execute without buffering.
    return _dbConnector.ReadAsAsyncEnumerable<Employee>("SELECT * FROM Employees")      
        .Execute();
}

public async void ClientExample()
{
    IEnumerable<Employee> deferredEmployees = await GetAllEmployees();

    //A single employee object will be loaded into memory
    //from the query result per iteration.
    foreach (Employee emp in deferredEmployees)
    {
        //TODO: Do something with the Employee data.
    }

    //v1.6 supports asynchronous streams
    //A single employee object will be loaded into memory
    //from the query result per iteration.
    var cancellation = new CancellationTokenSource();
    await foreach (Employee empItemAsync in GetAllEmployeesAsAsyncEnumerable().WithCancellation(cancellation.Token))
    {
        //TODO: Do something with the Employee data and/or cancel, if needed, using the cancellation token.
    }
}

Constraints

DbConnector’s deferred buffering feature is only supported for the following IEnumerable generic functions:

  • Read
  • ReadToKeyValuePairs
  • ReadToDictionaries

Note

Batch-Reading is not supported for deferred buffering.

Non-Queries

Reading data from a database will only get us halfway there. Data access layers will typically also include inserting, updating, and deleting database records in order to provide a fully developed solution.

Tip

Reading first the Introduction, Parameters, and Read-Queries sections is recommended. Also, do not forget to read the Techniques section for a complete overview.

Like previously mentioned in the Getting Started section, non-queries can be executed using DbConnector’s functions whose names begin with “Non”. There are two main overloads for these functions. A generic overload for custom type results and another returning a nullable integer type.

Note

The overload returning a nullable integer type represents either the "number of rows affected" or an exception if null.

With that in mind, three main things are necessary: a DbConnector instance with a configured connection string, a SQL query, and any action related data:

using DbConnector.Core;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.SqlClient;
using System.Threading.Tasks;

namespace DbConnector.Example.Repository
{
    /// <summary>
    /// DTO model example.
    /// </summary>
    public class Example
    {
        /// <summary>
        /// The use of attributes will be covered in the Techniques - Data Mapping 5.2 section.
        /// </summary>
        [Column("id")]
        public int Id { get; set; }

        [Column("name")]
        public string Name { get; set; }

        [Column("date_created")]
        public DateTime DateCreated { get; set; }

        [Column("date_modified")]
        public DateTime DateModified { get; set; }

        [NotMapped]
        public int NotMappedExample { get; set; }
    }

    /// <summary>
    /// Repository with read, insert, update, and delete examples.
    /// TODO: Inherit from a base repository class.
    /// </summary>
    public class ExampleRepository
    {
        protected static IDbConnector<SqlConnection> _dbConnector;

        /// <summary>
        /// Static constructor
        /// </summary>
        static ExampleRepository()
        {
            //TODO: Use Dependency Injection inside a non-static constructor in order to provide this instance.
            _dbConnector = new DbConnector<SqlConnection>("connection string goes here");
        }



        public Task<Example> Get(int id)
        {
            return _dbConnector.ReadFirstOrDefault<Example>(
                onInit: (cmd) =>
                {
                    cmd.CommandText = "SELECT * FROM Example WHERE id = @id";

                    cmd.Parameters.AddWithValue("id", id);

                }).ExecuteAsync();
        }

        public Task<IEnumerable<Example>> GetAll()
        {
            return _dbConnector.Read<Example>(
                onInit: (cmd) =>
                {
                    cmd.CommandText = "SELECT * FROM Example";
                }).ExecuteAsync();
        }

        public Task<int?> Insert(Example entity)
        {
            return _dbConnector.NonQuery(
                onInit: (cmd) =>
                {
                    cmd.CommandText = @"
                        INSERT INTO Example
                        (name, date_created)
                        VALUES(@Name, @DateCreated)
                    ";

                    cmd.Parameters.AddFor(new { entity.Name, entity.DateCreated });

                }).ExecuteAsync();
        }

        public Task<int?> Update(Example entity)
        {
            return _dbConnector.NonQuery(
                onInit: (cmd) =>
                {
                    cmd.CommandText = @"
                        UPDATE Example
                        SET name = @Name, date_modified = @DateModified
                        WHERE id= @Id;
                    ";

                    cmd.Parameters.AddFor(new { entity.Id, entity.Name, entity.DateModified });

                }).ExecuteAsync();
        }

        public Task<bool> Delete(int id)
        {
            //Setting a custom generic type:
            return _dbConnector.NonQuery<bool>(
                onInit: (cmd) =>
                {
                    cmd.CommandText = @"
                        DELECT FROM Example
                        WHERE id= @id;
                    ";

                    cmd.Parameters.AddFor(new { id });
                })
                //Leverage the OnCompleted event by returning a boolean.
                //Please note this will also return true if the "where" condition fails to match a record.
                .OnCompleted((v) => true)
                .ExecuteAsync();
        }
    }


    /// <summary>
    /// v1.1 Simple overloads example
    /// Repository with read, insert, update, and delete examples.
    /// TODO: Inherit from a base repository class.
    /// </summary>
    public class ExampleSimpleRepository
    {
        protected static IDbConnector<SqlConnection> _dbConnector;

        /// <summary>
        /// Static constructor
        /// </summary>
        static ExampleSimpleRepository()
        {
            //TODO: Use Dependency Injection inside a non-static constructor in order to provide this instance.
            _dbConnector = new DbConnector<SqlConnection>("connection string goes here");
        }

        public Task<int?> Insert(Example entity)
        {
            //v1.1 usage example:
            return _dbConnector.NonQuery(
                    @"
                        INSERT INTO Example
                        (name, date_created)
                        VALUES(@Name, @DateCreated)
                    ",
                    new { entity.Name, entity.DateCreated }).ExecuteAsync();
        }

        public Task<int?> Update(Example entity)
        {
            //v1.1 usage example:
            return _dbConnector.NonQuery(
                    @"
                        UPDATE Example
                        SET name = @Name, date_modified = @DateModified
                        WHERE id= @Id;
                    ",
                    new { entity.Id, entity.Name, entity.DateModified }).ExecuteAsync();
        }

        public Task<bool> Delete(int id)
        {
            //v1.1 usage example:

            //Setting a custom generic type:
            return _dbConnector.NonQuery<bool>(
                @"
                    DELECT FROM Example
                    WHERE id= @id;
                ", new { id })
                //Leverage the OnCompleted event by returning a boolean.
                //Please note this will also return true if the "where" condition fails to match a record.
                .OnCompleted((v) => true)
                .ExecuteAsync();
        }
    }
}
Transactions

By default, all non-queries will be executed using a custom transaction configured to use IsolationLevel.ReadCommitted.
You can override this behavior with the help of "WithIsolationLevel":

public Task<int?> Update(Example entity)
{
    return _dbConnector.NonQuery(
        onInit: (cmd) =>
        {
            cmd.CommandText = @"
                UPDATE Example
                SET name = @Name, date_modified = @DateModified
                WHERE id= @Id;
            ";

            cmd.Parameters.AddFor(new { entity.Id, entity.Name, entity.DateModified });

        })
        //Change Isolation Level or null to prevent a transaction:
        .WithIsolationLevel(IsolationLevel.Snapshot)
        .ExecuteAsync();
}

Tip

The use of transactions is very important when modifying multiple database entities/records in order to ensure accuracy, completeness, and data integrity. Reviewing ACID standards is encouraged.

Various examples will be provided in this section on how to execute "non-queries". These examples will be divided by built-in features and common SQL strategies.



Single 4.1

The DbConnector supports the execution of a single database command:

public Task<int?> Update(Example entity)
{
    return _dbConnector.NonQuery(
        onInit: (cmd) =>
        {
            cmd.CommandText = @"
                UPDATE Example
                SET name = @Name, date_modified = @DateModified
                WHERE id= @Id;
            ";

            cmd.Parameters.AddFor(new { entity.Id, entity.Name, entity.DateModified });

        })
        //Change Isolation Level or null to prevent a transaction:
        .WithIsolationLevel(IsolationLevel.Snapshot)
        .ExecuteAsync();
}

public Task<int?> UpdateSimple(Example entity)
{
    //v1.1 usage example:
    return _dbConnector.NonQuery(
        @"
            UPDATE Example
            SET name = @Name, date_modified = @DateModified
            WHERE id= @Id;
        ", new { entity.Id, entity.Name, entity.DateModified })
        //Change Isolation Level or null to prevent a transaction:
        .WithIsolationLevel(IsolationLevel.Snapshot)
        .ExecuteAsync();
}

public Task<bool> Delete(int id)
{
    //Setting a custom generic type:
    return _dbConnector.NonQuery<bool>(
        onInit: (cmd) =>
        {
            cmd.CommandText = @"
                DELECT FROM Example
                WHERE id= @id;
            ";

            cmd.Parameters.AddFor(new { id });
        })
        //Leverage the OnCompleted event by returning a boolean.
        //Please note this will also return true if the "where" condition fails to match a record.
        .OnCompleted((v) => true)
        .ExecuteAsync();
}

Multiple 4.2

The DbConnector supports the execution of multiple database commands. This is extremely helpful when wanting to group commands in a single transaction:

public Task<int?> MultipleNonQueriesExample(Example entity, int entityIdToDelete)
{
    return _dbConnector.NonQueries(
        onInit: (cmds) =>
        {
            //Insert first
            cmds.Enqueue((cmd) =>
            {
                cmd.CommandText = @"
                    INSERT INTO Example
                    (name, date_created)
                    VALUES(@Name, @DateCreated)
                ";

                cmd.Parameters.AddFor(new
                {
                    entity.Name,
                    entity.DateCreated
                });
            });

            //Then delete
            cmds.Enqueue((cmd) =>
            {
                cmd.CommandText = @"
                    DELECT FROM Example
                    WHERE id= @id;
                ";

                cmd.Parameters.AddFor(new { id = entityIdToDelete });
            });

        }).ExecuteAsync();
}

Batch Inserts 4.3

There are platform-specific limitations when trying to marshal multiple rows of data concerning mostly the use of countless parameters and database round trips. These limitations apply particularly when Table-Valued Parameters or similar techniques are not available in your development environment.

The following example demonstrates how to efficiently insert a batch of items with the help of DbConnector's "Build" generic function:
/// <summary>       
/// Note: This example leverages the "System.Linq" namespace.
/// </summary>
public Task<int?> InsertBatch(IEnumerable<Example> entities)
{
    if (entities == null || !entities.Any())
        throw new ArgumentException($"{nameof(entities)} is null or empty.", nameof(entities));

    Example firstEntity = entities.First();

    return _dbConnector.Build<int?>(
            sql: @$"INSERT INTO Example
            (
                name,
                date_created
            ) 
            VALUES (
                @{nameof(Example.Name)},
                @{nameof(Example.DateCreated)}
            )",
            param: new { firstEntity.Name, firstEntity.DateCreated },//Set the first row.
            onExecute: (int? result, IDbExecutionModel em) =>
            {
                //Get the command
                DbCommand command = em.Command;

                //Execute first row.
                em.NumberOfRowsAffected = command.ExecuteNonQuery();

                //Set and execute remaining rows efficiently by using the same command.
                foreach (var ent in entities.Skip(1))
                {
                    command.Parameters[nameof(Example.Name)].Value = ent.Name ?? (object)DBNull.Value;
                    command.Parameters[nameof(Example.DateCreated)].Value = (ent.DateCreated == default(DateTime) ? (object)DBNull.Value : ent.DateCreated);

                    em.NumberOfRowsAffected += command.ExecuteNonQuery();
                }

                return em.NumberOfRowsAffected;
            }
        )
        .WithIsolationLevel(IsolationLevel.ReadCommitted)//USE A TRANSACTION!
        .ExecuteAsync();
}

IDbJob Batching 4.4

Multiple IDbJobs can be executed together in a shared transaction. This can help you build a better "Repository Layer" by reducing code redundancy. To accomplish this, the DbConnector includes a static class called DbJob. This static class contains different function overloads allowing multiple IDbJobs as arguments.

These functions resemble the same functionality and naming convention as the IDbJob class but with a static approach:
  • ExecuteAll
  • ExecuteAllAsync
  • ExecuteAllHandled
  • ExecuteAllHandledAsync

Tip

IDbJob batching is supported for both non-query and read (when applicable but not encouraged) scenarios.

The static DbJob class will always return a List<(List<IDbExecutedModel>, dynamic)> (inside a Task for asynchronous overloads), regardless of arguments, when invoking one of its overloads since an IDbJob can return any generic type.

Each value tuple inside the returned list represents one IDbJob. The first item of the tuple is a list containing an IDbExecutedModel per executed command. Lastly, the second item is the result of the IDbJob represented by a dynamic object.

Note

It's important to know the IsolationLevel will default to "ReadCommitted" if not configured.

public class ExampleRepository
{
    protected static IDbConnector<SqlConnection> _dbConnector;

    /// <summary>
    /// Static constructor
    /// </summary>
    static ExampleRepository()
    {
        //TODO: Use Dependency Injection inside a non-static constructor in order to provide this instance.
        _dbConnector = new DbConnector<SqlConnection>("connection string goes here");
    }



    public async Task<bool> IDbJobBatchingExample(Example entity, int entityIdToDelete)
    {
        //The static DbJob class will always return the same list signature:
        List<(List<IDbExecutedModel>, dynamic)> results = await DbJob.ExecuteAllAsync(
                                                                BuildInsert(entity),
                                                                BuildDelete(entityIdToDelete)
                                                            );

        //Access the value tuples:
        int result1 = results[0].Item2 ?? 0;
        int result2 = results[1].Item2 ?? 0;

        //Return true if greater than zero.
        return (result1 + result2) > 0;
    }

    public async Task<bool> IDbJobBatchingByIsolationLevelExample(Example entity, int entityIdToDelete)
    {
        //The static DbJob class will always return the same list signature:
        List<(List<IDbExecutedModel>, dynamic)> results = await DbJob.ExecuteAllAsync(
                                                System.Data.IsolationLevel.Snapshot,
                                                                BuildInsert(entity),
                                                                BuildDelete(entityIdToDelete)
                                                            );

        //Access the value tuples:
        int result1 = results[0].Item2 ?? 0;
        int result2 = results[1].Item2 ?? 0;

        //Return true if greater than zero.
        return (result1 + result2) > 0;
    }

    internal IDbJob<int?> BuildInsert(Example entity)
    {
        return _dbConnector.NonQuery(
            onInit: (cmd) =>
            {
                cmd.CommandText = @"
                    INSERT INTO Example
                    (name, date_created)
                    VALUES(@Name, @DateCreated)
                ";

                cmd.Parameters.AddFor(new { entity.Name, entity.DateCreated });

            });
    }

    internal IDbJob<int?> BuildDelete(int id)
    {
        //Setting a custom generic type:
        return _dbConnector.NonQuery<int?>(
            onInit: (cmd) =>
            {
                cmd.CommandText = @"
                    DELECT FROM Example
                    WHERE id= @id;
                ";

                cmd.Parameters.AddFor(new { id });
            });
    }
}

Techniques

Multiple topics will be presented in this section with the goal of providing a supplement to all of the discussed DbConnector features. Reading all of the previous sections of this documentation is encouraged since they will assist on learning the subsequent extra material.



Security 5.1

Due to DbConnector’s approach to writing string/text based SQL commands, it's imperative to ALWAYS use parameters instead of concatenating. Using parameters will prevent SQL injection attacks, help database servers improve execution performance, and allow the DbConnector to cache and return the fastest result(s).

Warning

Reviewing the Parameters section is strongly recommended!

Data Mapping 5.2

The Read-Queries feature efficiently allows for data to be mapped into properties belonging to an object. This mapping is done by matching column names and types returned from a query result.

Let's first review a DTO example:
using System;
using System.ComponentModel.DataAnnotations.Schema;

namespace DbConnector.Example.Repository
{
    /// <summary>
    /// DTO model example.
    /// </summary>
    public class Example
    {
        [Column("id")]
        public int Id { get; set; }

        [Column("name_first")]
        public string FirstName { get; set; }

        public string name_last { get; set; }

        [Column("date_modified")]
        public DateTime ModifiedDate { get; set; }

        [NotMapped]
        public int NotMappedExample { get; set; }
    }
}

Tip

Creating DTOs becomes tedious when dealing with large databases. You can leverage the EF's CLI scaffold feature to reduce errors and make better use of your time.


Name Matching

DbConnector depends on the System.ComponentModel.Annotations NuGet package. This allows the Column attribute from the "System.ComponentModel.DataAnnotations.Schema" namespace to be used for name matching. Regardless if you use this attribute or not, it's important to know name matching is case-insensitive.

How is the "Column" attribute useful? It improves code maintainability by decoupling database column names from .NET implementation.

The other available attribute is NotMapped. Being self-explanatory, it prevents properties from being mapped by the DbConnector.

To summarize:
  • Declared properties need to be public, non-static, and cannot be read-only otherwise they will be ignored.
  • Name mapping is case-insensitive (since v1.4.0).
  • The System.ComponentModel.Annotations NuGet package needs to be installed to leverage the "Column" or "NotMapped" attributes.
  • The "Column" attribute can be used for name decoupling.
  • The "NotMapped" attribute can be used to prevent a mapping.

Note

A warning will be logged if a property/column name match could not be established when running in "Debug" mode.


Type Matching

An Exception will be thrown by the DbConnector if the type of a matched column cannot be converted to the target property's type.

DbConnector will try to indirectly match and convert/parse the following:

  • string to Guid
  • (bool, byte, sbyte, short, ushort, int, uint, long ulong, float, double, or string) to Enum
  • (bool, byte, sbyte, short, ushort, int, uint, long ulong, float, double) to any of the listed

Map Settings

Flexibility is important when dealing with data mapping. It's not feasible to use the same DTO in all scenarios therefore the DbConnector allows for data mapping customization per IDbJobCommand via the MapSettings property:

using DbConnector.Core;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.SqlClient;
using System.Threading.Tasks;

namespace DbConnector.Example.Repository
{
    /// <summary>
    /// DTO model example.
    /// </summary>
    public class Example
    {
        [Column("id")]
        public int Id { get; set; }

        [Column("name_first")]
        public string FirstName { get; set; }

        public string name_last { get; set; }

        [Column("date_modified")]
        public DateTime ModifiedDate { get; set; }

        [NotMapped]
        public int NotMappedExample { get; set; }
    }

    /// <summary>
    /// Repository example.
    /// TODO: Inherit from a base repository class.
    /// </summary>
    public class ExampleRepository
    {
        protected static IDbConnector<SqlConnection> _dbConnector;

        /// <summary>
        /// Static constructor
        /// </summary>
        static ExampleRepository()
        {
            //TODO: Use Dependency Injection inside a non-static constructor in order to provide this instance.
            _dbConnector = new DbConnector<SqlConnection>("connection string goes here");
        }


        //NOTE: v1.1 INCLUDES SIMPLER OVERLOADS!
        public Task<IEnumerable<Example>> IncludeNameExampleSimple(int id)
        {
            //v1.1 simple usage example:

            //Note: DO NOT modify this object after setting it!
            var mapSettings = new ColumnMapSetting().IncludeNames("id", "name_first");

            return _dbConnector.Read<Example>(mapSettings, "SELECT * FROM Example").ExecuteAsync();
        }

        public Task<IEnumerable<Example>> IncludeNameExample(int id)
        {
            return _dbConnector.Read<Example>(
                onInit: (cmd) =>
                {
                    cmd.CommandText = "SELECT * FROM Example";

                    //Will only map the configured names:
                    cmd.MapSettings.IncludeNames("id", "name_first");

                }).ExecuteAsync();
        }

        public Task<IEnumerable<Example>> IncludeNameForExample(int id)
        {
            return _dbConnector.Read<Example>(
                onInit: (cmd) =>
                {
                    cmd.CommandText = "SELECT * FROM Example";

                    //Will only map the configured names:
                    cmd.MapSettings.IncludeNamesFor<Example>(isUseColumnAttribute: true, e => e.Id, e => e.FirstName);

                }).ExecuteAsync();
        }

        public Task<IEnumerable<Example>> ExcludeNameExample(int id)
        {
            return _dbConnector.Read<Example>(
                onInit: (cmd) =>
                {
                    cmd.CommandText = "SELECT * FROM Example";

                    //Will prevent the configured names from being mapped:
                    cmd.MapSettings.ExcludeNames("id", "name_first");

                }).ExecuteAsync();
        }

        public Task<IEnumerable<Example>> ExcludeNameForExample(int id)
        {
            return _dbConnector.Read<Example>(
                onInit: (cmd) =>
                {
                    cmd.CommandText = "SELECT * FROM Example";

                    //Will prevent the configured names from being mapped:
                    cmd.MapSettings.ExcludeNamesFor<Example>(isUseColumnAttribute: true, e => e.Id, e => e.FirstName);

                }).ExecuteAsync();
        }

        public Task<IEnumerable<Example>> AliasStartsWithExample(int id)
        {
            return _dbConnector.Read<Example>(
                onInit: (cmd) =>
                {
                    cmd.CommandText = "SELECT id, name_first, name_last as 'x_name_last' FROM Example";

                    //Configure an alias for the name_last property
                    //since the column name starts with 'x_':
                    cmd.MapSettings.WithAliasFor<Example>("x_", ColumnAliasMode.StartsWith, isUseColumnAttribute: true, c => c.name_last);

                }).ExecuteAsync();
        }

        public Task<IEnumerable<Example>> AliasEndsWithExample(int id)
        {
            return _dbConnector.Read<Example>(
                onInit: (cmd) =>
                {
                    cmd.CommandText = "SELECT id, name_first, name_last as 'name_last_x' FROM Example";

                    //Configure an alias for the name_last property
                    //since the column name ends with '_x':
                    cmd.MapSettings.WithAliasFor<Example>("_x", ColumnAliasMode.EndsWith, isUseColumnAttribute: true, c => c.name_last);

                }).ExecuteAsync();
        }

        public Task<IEnumerable<Example>> AliasMultipleEndsWithExample(int id)
        {
            return _dbConnector.Read<Example>(
                onInit: (cmd) =>
                {
                    cmd.CommandText = "SELECT id, name_first as 'name_first_x', name_last as 'name_last_x' FROM Example";

                    //Configure an alias for both the FirstName and name_last properties
                    //since the column names end with '_x':
                    cmd.MapSettings.WithAliasFor<Example>("_x", ColumnAliasMode.EndsWith, isUseColumnAttribute: true, c => c.FirstName, c => c.name_last);

                }).ExecuteAsync();
        }

        public Task<IEnumerable<Example>> AliasEqualsExample(int id)
        {
            return _dbConnector.Read<Example>(
                onInit: (cmd) =>
                {
                    cmd.CommandText = "SELECT id, name_first, name_last as 'anotherCustomName' FROM Example";

                    //Configure an alias for the name_last property
                    //since the column name equals 'anotherCustomName':
                    cmd.MapSettings.WithAliasFor<Example>("anotherCustomName", ColumnAliasMode.Equals, isUseColumnAttribute: true, c => c.name_last);

                }).ExecuteAsync();
        }
    }
}

Flags 5.3

Any DbConnector or IDbJobCommand instance can be configured using the available flags:

/// <summary>
/// Repository example.
/// TODO: Inherit from a base repository class.
/// </summary>
public class ExampleRepository
{
    protected static IDbConnector<SqlConnection> _dbConnector;

    /// <summary>
    /// Static constructor
    /// </summary>
    static ExampleRepository()
    {
        //TODO: Use Dependency Injection inside a non-static constructor in order to provide this instance.
        _dbConnector = new DbConnector<SqlConnection>(
            "connection string goes here",

            //GLOBALLY disable SequentialAccess CommandBehavior optimization:
            DbConnectorFlags.NoAutoSequentialAccessCommandBehavior
        );
    }


    public Task<IEnumerable<Example>> FlagExample(int id)
    {
        return _dbConnector.Read<Example>(
            onInit: (cmd) =>
            {
                cmd.CommandText = "SELECT * FROM Example";

                //LOCALLY disable SequentialAccess CommandBehavior optimization:
                //cmd.Flags = DbJobCommandFlags.NoAutoSequentialAccessCommandBehavior;

            }).ExecuteAsync();
    }


    //NOTE: v1.1 INCLUDES SIMPLER OVERLOADS!
    public Task<IEnumerable<Example>> FlagExampleSimple(int id)
    {
        //LOCALLY disable SequentialAccess CommandBehavior optimization:
        return _dbConnector.Read<Example>(null, "SELECT * FROM Example", flags: DbJobCommandFlags.NoAutoSequentialAccessCommandBehavior).ExecuteAsync();
    }
}

Note

There is no "NoCommandBehaviorOptimization" in the DbJobCommandFlags. To override, simply configure the "CommandBehavior" property of the targeted IDbJobCommand to a valid flag value.

Cache Settings 5.4

The DbConnector caches all "Read" IDbJob operations on first execution in order to provide the fastest results. This is done internally by leveraging .NET DynamicMethods which allows for runtime Microsoft Intermediate Language (MSIL) compilation.

The use of Parameters is extremely important. Not only for Security reasons but also because DbConnector will continue to cache, using more and more memory, any new/different IDbJob operation if you decide to ignore the use of parameters.

Note

Cached operations will be cleaned every 1024 executions if not used more than once! As an alternative, the cache can be manually cleared by calling DbConnectorCache.ClearColumnMapCache() or DbConnectorCache.ClearCache().

With that in mind, caching can be disabled if necessary:
/// <summary>
/// Repository example.
/// TODO: Inherit from a base repository class.
/// </summary>
public class ExampleRepository
{
    protected static IDbConnector<SqlConnection> _dbConnector;

    /// <summary>
    /// Static constructor
    /// </summary>
    static ExampleRepository()
    {
        //TODO: Use Dependency Injection inside a non-static constructor in order to provide this instance.
        _dbConnector = new DbConnector<SqlConnection>(
            "connection string goes here",

            //GLOBALLY disable the caching of query mappings and types:
            DbConnectorFlags.NoCache
        );
    }


    public Task<IEnumerable<Example>> FlagExample(int id)
    {
        return _dbConnector.Read<Example>(
            onInit: (cmd) =>
            {
                cmd.CommandText = "SELECT * FROM Example";

                //Disable the caching
                //of query mappings and types at the IDbJobCommand level:
                //cmd.Flags = DbJobCommandFlags.NoCache;

            })
            //Disable the caching
            //of query mappings and types at the IDbJob level:
            //.WithCache(false)
            .ExecuteAsync();
    }
}

Logging 5.5

ADO.NET data-providers (e.g. SqlClient, Npgsql, etc.) usually provide built-in error logging APIs. For additional support, the DbConnector library also provides the ability to capture errors by using the "IDbConnectorLogger" interface. This behavior can be useful when leveraging IDbJob's "Handled" overloads and more:

using DbConnector.Core;
using DbConnector.Core.Extensions;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Threading.Tasks;

namespace DbConnector.Example.Repository
{
    /// <summary>
    /// Implements the "IDbConnectorLogger"
    /// </summary>
    public class ErrorHandler : IDbConnectorLogger
    {
        protected ExampleRepository _exampleRepository;

        public ErrorHandler()
        {
            //TODO: Use Dependency Injection.
            _exampleRepository = new ExampleRepository();
        }


        //Make sure this function's implementation is "thread-safe"
        //since it will potentially get called by multiple/different threads.
        public async void Log(Exception ex)
        {
            //3.)Catch the Exception

            await _exampleRepository.InsertLog(ex);
        }
    }


    /// <summary>
    /// Repository example.
    /// TODO: Inherit from a base repository class.
    /// </summary>
    public class ExampleRepository
    {
        protected static IDbConnector<SqlConnection> _dbConnector;

        /// <summary>
        /// Static constructor
        /// </summary>
        static ExampleRepository()
        {
            //TODO: Use Dependency Injection inside a non-static constructor in order to provide these instances.
            ErrorHandler errorHandler = new ErrorHandler();

            _dbConnector = new DbConnector<SqlConnection>(
                "connection string goes here",

                //1.)Set the error handler:
                errorHandler
            );
        }


        /// <summary>
        /// An error will be thrown when calling this function 
        /// thus passing the Exception to the ErrorHandler's log function.
        /// </summary>
        public Task<IDbResult<IEnumerable<dynamic>>> ThrowTest(int id)
        {
            //2.)Throw test Exception
            return _dbConnector.Read(
                onInit: (cmd) =>
                {
                    throw new Exception("This is a test!");
                })
                .ExecuteHandledAsync();
        }


        /// <summary>
        /// TODO: This function should be placed in another repository (E.g. LogRepository).
        /// </summary>
        public Task<int?> InsertLog(Exception ex)
        {
            //4.)Insert the Exception
            try
            {
                return _dbConnector.NonQuery(
                        onInit: (cmd) =>
                        {
                            cmd.CommandText = @"
                                INSERT INTO Log
                                (
                                    type,
                                    source,
                                    message,
                                    xml_content
                                )
                                VALUES
                                (
                                    @Type,
                                    @Source,
                                    @Message,
                                    @Xml
                                )
                            ";

                            cmd.Parameters.AddFor(new
                            {
                                Type = ex.GetType().ToString(),
                                Source = ex.Source ?? "",
                                Message = ex.Message ?? "",
                                Xml = ex.SerializeToXml() //using DbConnector.Core.Extensions
                            });

                        })
                        //Make sure you disable logging to prevent infinite loops:
                        .WithLogging(false)
                        .ExecuteAsync();
            }
            catch (Exception e)
            {
#if DEBUG
                Debug.WriteLine("Exception: " + e.ToString());
#endif

                return null;
            }
        }
    }
}

Thread-Safety 5.6

DbConnector was built with thread-safety in mind. First, using a single DbConnector instance is encouraged throughout this documentation. This singleton implementation is thread-safe and must be used for optimal performance.

Secondly, IDbJobs have a functional approach and can be asynchronously executed. Nonetheless, IDbJob instances are also thread-safe since "locking" is properly implemented inside all visible APIs.

But what happens if two or more threads execute the same IDbJob at the exact time? An IDbJob instance will clone itself to prevent "locking" if necessary therefore allowing the continuation of threads.

What is not thread-safe? You are fully responsible of code logic implemented inside delegate functions.

/// <summary>
/// Repository example.
/// TODO: Inherit from a base repository class.
/// </summary>
public class ExampleRepository
{
    protected static IDbConnector<SqlConnection> _dbConnector;

    /// <summary>
    /// Static constructor
    /// </summary>
    static ExampleRepository()
    {
        //TODO: Use Dependency Injection inside a non-static constructor in order to provide this instance.
        _dbConnector = new DbConnector<SqlConnection>("connection string goes here");
    }


    public void NonThreadSafeExample()
    {
        int x = 0;

        Task<dynamic> task1 = _dbConnector.ReadFirstOrDefault(
            onInit: (cmd) =>
            {
                cmd.CommandText = "SELECT * FROM Example Where id = @id";

                cmd.Parameters.AddFor(new { id = 1 });

                //This is NOT thread-safe:
                x = 1;

            }).ExecuteAsync();//Using the Async overload


        Task<dynamic> task2 = _dbConnector.ReadFirstOrDefault(
            onInit: (cmd) =>
            {
                cmd.CommandText = "SELECT * FROM Example Where id = @id";

                cmd.Parameters.AddFor(new { id = 2 });

                //This is NOT thread-safe:
                x = 2;

            }).ExecuteAsync();//Using the Async overload


        //Wait for both task to run asynchronously:
        Task.WaitAll(task1, task2);


        //We do not know if x == 1 or x == 2 
        //since any Task could've finished first:
        if (x == 2)
        {
        }
    }


    public void ThreadSafeExample()
    {
        IDbJob<dynamic> jobBuild1 = _dbConnector.ReadFirstOrDefault(
            onInit: (cmd) =>
            {
                cmd.CommandText = "SELECT * FROM Example Where id = @id";

                cmd.Parameters.AddFor(new { id = 1 });

            });

        //This is thread-safe. 
        //The IDbJob will clone itself if necessary and will run twice. 
        Task.WaitAll(jobBuild1.ExecuteAsync(), jobBuild1.ExecuteAsync());
    }
}

Performance

SavantBuffer's DbConnector is extremely fast and memory efficient. To put it into perspective, other light ORMs (e.g. Dapper) are often just extension libraries. These extension libraries, in particular cases, can sometimes be faster (up to a negligible 1 microsecond) but tend to put more responsibility on developers since database connections and other resources need to be properly managed (e.g. opened, closed). Manually controlling these resources can sometimes become tedious and a code maintenance issue for some.

With that said, the DbConnector lies between an extension library and a full-blown framework (e.g. Entity Framework). It provides a lot of flexibility without the need to manage disposable resources while delivering the fastest results.

Note

Feel free to visit DbConnector’s GitHub repo for more!


Latest performance test based on BenchmarkDotNet:

Release Notes

Please see DbConnector’s CHANGELOG for details.



Known-Issues 7.1

Issues can be reported via DbConnector’s GitHub Issues feature.

Pending Features 7.2

New feature requests can be submited using DbConnector’s GitHub repo.

License 7.3

Notice

Copyright 2019 Robert Orama

Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

API

Please stay tuned for the API reference documentation.




References

  • ORM Cookbook - Please visit the official ORM Cookbook for more DbConnector examples. (Special thanks to Jonathan Allen.)


Contact

Hello,

My name is Robert Orama and I'm the author of the DbConnector library. As a computer engineer and software enthusiast, my goal is to share knowledge by means of this project. I hope the use of DbConnector is taken into serious consideration and can help provide efficient results for any type of .NET solution.

You can reach me via email if really necessary:
rorama@savantbuffer.com

Don’t forget to donate: Donate

Thank you for your support!