This utility allows you to speed up CRUD operations with Dapper by writing less code. The 'utility already integrates the connection system to various database providers such as:
- SQL Server
- MySQL / Maria DB
- PostgreSQL
- SQLite
This utility arose from the need to avoid instantiating a connection context each time and perform all CRUD operations without having to write the database access each time; it should better facilitate operations with Dapper.
To install the program simply download the nuget package(s) related to your needs.
There are several nuget packages available that you can install. The packages are these:
-
DapperContext
- This is the core package that is mandatory for other and will not work alone
-
DapperContext.SqlServer
- This package allows you to connect to a Microsoft SQL Server and is dependent on the core package. With this package you can use all the functions available to you
-
DapperContext.MySql
- This package allows you to connect to a MySQL / MariaDB and is dependent on the core package. With this package you can use all the functions available to you
-
DapperContext.PostgreSQL
- This package allows you to connect to a PostgreSQL and is dependent on the core package. With this package you can use all the functions available to you
-
DapperContext.SQLite
- This package allows you to connect to a SQLite and is dependent on the core package. With this package you can use all the functions available to you
Install sql server package via NuGet
PM > Install-Package BertoSoftware.DapperContext.SqlServerPM > Install-Package BertoSoftware.DapperContext.MySqlPM > Install-Package BertoSoftware.DapperContext.SQLitePM > Install-Package BertoSoftware.DapperContext.PostgreSQLThis packages will install also all dependecies regard main core package and specified database connection
In the main program you can define a settings globally for the project
Imports BertoSoftware.Context.Configuration
DapperContext.Settings = ContextConfiguration.CreateNew.
UseSettingsFileMode(SettingFileMode.NET4x).
WithConnectionName("MyConnection").
WithCustomConfigurationFile("app1.config").
WithCustomConnectionString("MyConnectionString")
DisableTransaction.
Build()using BertoSoftware.Context.Configuration;
DapperContext.Settings = ContextConfiguration.CreateNew().
UseSettingsFileMode(SettingFileMode.NET4x).
WithConnectionName("MyConnection").
WithCustomConfigurationFile("app1.config").
WithCustomConnectionString("MyConnectionString")
DisableTransaction().
Build();You can configure different configuration parameters such as:
.UseSettingsFileMode(SettingsFileMode.Net4x)With .UseSettingsFileMode you can select which configuration file should be loaded to look for the connection string.
-
SettingFileMode.NET4xload a file "app.config" and retrive appropriate connection string. -
SettingFileMode.NETCoreload a file "appsettings.json" and retrive a connection string
With .WithConnectionName("MyConnection") will search a name of connection string you provide into parameter, if this settings is omitted will search a connection string with name "DefaultConnection"
With .WithCustomConfigurationFile("app1.config") will search a file settings you provide into parameter, if this settings is omitted will search a default file like "app.config" or "appsettings.json"
With .WithCustomConnectionString("MyConnectionString") will use a custom connection you provide into parameter and this will be a priority on other parameter
With .DisableTransaction will disable automatic SQL transaction
You can terminate settings with .Build() method
Create a model class that respect the same on your database
Imports Dapper.Contrib.Extensions
Namespace Model
<Table("Person")>
Public Class Person
<Key>
Public Property ID As Integer
Public Property Name As String
Public Property Surname As String
Public Property Address As String
End Class
End Namespaceusing Dapper.Contrib.Extensions;
namespace Model
{
[Table("Person")]
public partial class Person
{
[Key]
public int ID { get; set; }
public string Name { get; set; }
public string Surname { get; set; }
public string Address { get; set; }
}
}You can configure a global setting once time into your start module program, like a Console app, Windows Forms, ASP.NET etc.
Otherwhere a default settings will be loaded
In this example, will take a look what how do a connection with SQL Server:
On package manager you have to install BertoSoftware.DapperContext.SqlServer to connect a SQL Server instance.
You can use a local variable with a simple declaration or you can use a using statement for automatic dispose item.
Imports BertoSoftware.Context.Configuration
Module Program
Sub Main(args As String())
'Configure context setting or leave default
'***Uncomment this line if you want to configure settings
DapperContext.Settings = ContextConfiguration.CreateNew.UseSettingsFileMode(SettingFileMode.NETCore).Build
End Sub
End ModuleImports BertoSoftware.Context.Tools
Dim ctx As New DapperContextSqlServer
'Your code
ctx.Dispose()
Using ctx As New DapperContextSqlServer
'Your code
End UsingIn following examples, declaration will be omitted
Private Function InsertRecord() As Long
'Create a record
Dim person As New Model.Person With {
.Name = "John",
.Surname = "Doe"
}
Return CLng(ctx.InsertOrUpdate(person))
End FunctionPrivate Function GetRecordByID(id As Object) As Model.Person
'Get a single record
Dim person As Model.Person = ctx.Get(Of Model.Person)(id)
Return person
End FunctionPrivate Function GetAllRecords() As List(Of Model.Person)
'Get all record
Dim lstPerson As List(Of Model.Person) = ctx.GetAll(Of Model.Person).ToList
Return lstPerson
End FunctionPublic Function UpdateRecord(person As Model.Person) As Boolean
'Update a record
person.Surname = "Butt"
Return CBool(ctx.InsertOrUpdate(person))
End FunctionPublic Function DeleteRecord(person As Model.Person) As Boolean
'Delete a record
Return ctx.Delete(person)
End FunctionPublic Function DeleteAllRecords() As Boolean
'Delete all record
Return ctx.DeleteAll(Of Model.Person)()
End Functionusing BertoSoftware.Configuration;
class Program
{
static void Main(string[] args)
{
//Configure context setting or leave default
//***Uncomment this line if you want to configure settings
DapperContext.Settings = ContextConfiguration.CreateNew().UseSettingsFileMode(SettingFileMode.NetCore).Build();
}
}using BertoSoftware.Context.Tools
var ctx = new DapperContextSqlServer;
//your code
ctx.Dispose();
using (var ctx = new DapperContextSqlServer())
{
//your code
}In following examples, declaration will be omitted
private long InsertRecord()
{
// Create a record
var person = new Model.Person()
{
Name = "John",
Surname = "Doe"
};
return (long)ctx.InsertOrUpdate(person);
}private Model.Person GetRecordByID(object id)
{
// Get a single record
Model.Person person = ctx.Get<Model.Person>(id);
return person;
}private List<Model.Person> GetAllRecords()
{
// Get all record
List<Model.Person> lstPerson = ctx.GetAll<Model.Person>.ToList;
return lstPerson;
}public bool UpdateRecord(Model.Person person)
{
// Update a record
person.Surname = "Butt";
return (bool)ctx.InsertOrUpdate(person);
}public bool DeleteRecord(Model.Person person)
{
// Delete a record
return ctx.Delete(person);
}public bool DeleteAllRecords()
{
// Delete all record
return ctx.DeleteAll<Model.Person>();
}This example is provided with DapperContext.SQLServer installed package but you can change the class DapperContextSqlServer with your appropriate.
The classes avaiabile are these:
| Package name | Classes | NuGet |
|---|---|---|
| BertoSoftware.DapperContext.SqlServer | BertoSoftware.DapperContextSqlServer |  |
| BertoSoftware.DapperContext.MySql | BertoSoftware.DapperContextMySql |  |
| BertoSoftware.DapperContext.SQLite | BertoSoftware.DapperContextSQLite |  |
| BertoSoftware.DapperContext.PostgreSQL | BertoSoftware.DapperContextPostgreSQL |  |
Please let me a feedback about your opinion, some issues or some missing feature to implement in future, I'll be happy to hear you.