CardboardBox.Database 2.0.5

There is a newer version of this package available.
See the version list below for details.
dotnet add package CardboardBox.Database --version 2.0.5
NuGet\Install-Package CardboardBox.Database -Version 2.0.5
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="CardboardBox.Database" Version="2.0.5" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add CardboardBox.Database --version 2.0.5
#r "nuget: CardboardBox.Database, 2.0.5"
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install CardboardBox.Database as a Cake Addin
#addin nuget:?package=CardboardBox.Database&version=2.0.5

// Install CardboardBox.Database as a Cake Tool
#tool nuget:?package=CardboardBox.Database&version=2.0.5

CardboardBox.Database

A wrapper around Dapper and various ADO.Net providers to connect to different SQL Engines with built in basic query generation.

Installation

You will probably want to install one of the ADO.net provider specific packages:

Install Postgres via Nuget:

PM> Install-Package CardboardBox.Database.Postgres

Install SQLite via Nuget:

PM> Install-Package CardboardBox.Database.SQLite

Install MSSQL / SQL Server via Nuget: (Coming soon)

PM> Install-Package CardboardBox.Database.MSSQL

Setup

This package is designed to work with Microsoft.Extensions.DependencyInjection:

using CardboardBox.Database;
using Microsoft.Extensions.DependencyInjection;

var services = new ServiceCollection();

services.AddSqlService(c => 
{
	//Configure the SQL engines you want to use
	c.AddPostgres("<connection string>");

	c.AddSqlite("<connection string>");
});

Querying

To use the database engine, you can inject the ISqlService interface into any of your services:

public class SomeService 
{
	private readonly ISqlService _sql;

	public SomeService(ISqlService sql) { _sql = sql; }

	public Task<User> Fetch(long id) => _sql.Fetch<User>("SELECT * FROM users WHERE id = @id", new { id });

	public Task<User[]> Get() => _sql.Get<User>("SELECT * FROM users");

	public async Task DoSomethingComplex()
	{
		using var con = await _sql.CreateConnection();
		//Do something with the active connection
	}
}

Query Generation

Query Generation is centered around the IQueryService that generates queries based on your POCOs. Said generation uses property names, Dapper Fluent, and optional attributes to generate SQL queries.

Basic Usage - Attributes

There are 2 primary attributes within the generation scheme: TableAttribute and ColumnAttribute. You can use them like so:

[Table("Users")]
public class User 
{
	[Column(PrimaryKey = true, ExcludeInserts = true, ExcludeUpdates = true)]
	public int Id { get; set; }

	[Column(Unique = true)]
	public string UserName { get; set; } = string.Empty;

	public string FirstName { get; set; } = string.Empty;

	public string LastName { get; set; } = string.Empty;
}

This will create the following queries when used against the IQueryService:

--IQueryService.Select<User>(t => t.With(a => a.FirstName));
SELECT * FROM [Users] WHERE [FirstName] = @FirstName;

--IQueryService.Fetch<User>();
SELECT * FROM [Users] WHERE [Id] = @Id;

--IQueryService.Insert<User>();
INSERT INTO [Users] ([UserName], [FirstName], [LastName]) VALUES (@UserName, @FirstName, @LastName);

Basic Usage - Changing Column Case Convention

You can also change the case convention during configuration and it will automatically change the case of the column names:

services.AddSqlService(c => 
{
	//Configure the SQL engines you want to use
	c.AddPostgres("<connection string>")
	 //This tells the query generation system to use camel_case names
	 .ConfigureGeneration(c => c.WithCamelCaseChange())
	 //This tells Dapper Fluent to use camel_case for resolving property names for the `User` class
	 .ConfigureTypes(c => 
		c.CamelCase()
		 .Entity<User>());
});

This will create the following queries when used against the IQueryService:

--IQueryService.Select<User>(t => t.With(a => a.FirstName));
SELECT * FROM "users" WHERE "first_name" = :FirstName;

--IQueryService.Fetch<User>();
SELECT * FROM "users" WHERE "id" = :Id;

--IQueryService.Insert<User>();
INSERT INTO "users" ("user_name", "first_name", "last_name") VALUES (:UserName, :FirstName, :LastName);
Product Compatible and additional computed target framework versions.
.NET net5.0 was computed.  net5.0-windows was computed.  net6.0 was computed.  net6.0-android was computed.  net6.0-ios was computed.  net6.0-maccatalyst was computed.  net6.0-macos was computed.  net6.0-tvos was computed.  net6.0-windows was computed.  net7.0 was computed.  net7.0-android was computed.  net7.0-ios was computed.  net7.0-maccatalyst was computed.  net7.0-macos was computed.  net7.0-tvos was computed.  net7.0-windows was computed.  net8.0 was computed.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed. 
.NET Core netcoreapp3.0 was computed.  netcoreapp3.1 was computed. 
.NET Standard netstandard2.1 is compatible. 
MonoAndroid monoandroid was computed. 
MonoMac monomac was computed. 
MonoTouch monotouch was computed. 
Tizen tizen60 was computed. 
Xamarin.iOS xamarinios was computed. 
Xamarin.Mac xamarinmac was computed. 
Xamarin.TVOS xamarintvos was computed. 
Xamarin.WatchOS xamarinwatchos was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages (2)

Showing the top 2 NuGet packages that depend on CardboardBox.Database:

Package Downloads
CardboardBox.Database.Postgres

A wrapper around Dapper and Npgsql that provides easy of use when using Postgres Also contains a few useful extensions to Dapper to increase productivity and enable SQL query generation. Works as a progressive replacement to EF.

CardboardBox.Database.Sqlite

A wrapper around Dapper and Sqlite that provides easy of use when using Sqlite Also contains a few useful extensions to Dapper to increase productivity and enable SQL query generation. Works as a progressive replacement to EF.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
2.0.6 50 5/3/2024
2.0.5 349 10/9/2023
2.0.4 292 7/13/2023
2.0.3 161 6/2/2023
2.0.2 248 3/25/2023
2.0.1 247 3/24/2023
2.0.0 340 3/24/2023
1.0.6 1,292 10/20/2022
1.0.5 383 10/14/2022
1.0.4 797 1/15/2022
1.0.3 433 1/15/2022
1.0.2 416 1/15/2022
1.0.1 255 1/15/2022
1.0.0 244 1/15/2022

Version 2.0 contains breaking changes compared to 1.0.
* CreateConnection is now asynchronous to allow for tasks to be run on first load of the connection.