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.
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
- 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.
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();
}
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>>.
- 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:
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
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 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.
Danger
Always perform a full 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();
}
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.
}
}
Constraints
DbConnector’s deferred buffering feature is only supported for the following IEnumerable generic functions:
- Read
- ReadToKeyValuePairs
- ReadToDictionaries
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 });
});
}
}
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());
}
}
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.