Table of Contents

Getting started

Install

Most applications need one package. The others add integrations.

Package Use it for Targets
Open.Database.Extensions.Core Everything in this guide except channels, dataflow and SQL Server helpers. Provider-agnostic. netstandard2.0, netstandard2.1, net10.0
Open.Database.Extensions.Channel Reading into channels same
Open.Database.Extensions.Dataflow Reading into dataflow blocks same
Open.Database.Extensions.MSSqlClient SqlConnectionFactory and SQL Server specializations on Microsoft.Data.SqlClient net472, net10.0

.NET 8 and .NET 9 applications use the netstandard2.1 build; .NET Framework applications use netstandard2.0 (or net472 for the SQL Server package). Version 11 is a preview, so ask for prereleases:

dotnet add package Open.Database.Extensions.Core --prerelease

Core does not reference any database provider. Bring your own: the examples in this guide use Microsoft.Data.Sqlite so that you can run them without a server.

Your first query

A model needs a public parameterless constructor and public settable properties. Columns are matched to property names, ignoring case.

public class Post
{
    public int Id { get; set; }
    public int Author { get; set; }
    public string Title { get; set; } = "";
    public string? Body { get; set; }
}

There are two ways to start a command. Both end the same way, with AddParam for parameters and a terminal that runs the query. Side by side, row against row ("nothing" marks a step the factory makes unnecessary):

From a connection factory

// nothing: the factory creates, opens and disposes the connection
List<Post> posts = connectionFactory.Command(ListSql)
    .AddParam("author", 42)
    .Map<Post>().ToList();

From a connection you own

using var connection = new SqliteConnection(connectionString);
List<Post> posts = connection.Command(ListSql)
    .AddParam("author", 42)
    .Map<Post>().ToList();

The factory is created once per application:

var connectionFactory = new DbConnectionFactory<SqliteConnection>(() => new SqliteConnection(connectionString));

What is different:

  • Who creates the connection. The factory, for each execution; or you.
  • Open and close. In both forms the command opens the connection only if it is closed. The factory form disposes the connection when the terminal returns. In the connection form the command closes only what it opened and never disposes it.

Before and after

The same query with plain ADO.NET and with the extensions, row against row: the connection, the command, the parameter, then the read loop and the mapping.

Plain ADO.NET

using var connection = new SqliteConnection(connectionString);
connection.Open();
using var command = connection.CreateCommand();
command.CommandText = ListSql;
var parameter = command.CreateParameter();
parameter.ParameterName = "@author";
parameter.Value = 42;
command.Parameters.Add(parameter);
var posts = new List<Post>();
using var reader = command.ExecuteReader();
while (reader.Read())
{
    posts.Add(new Post
    {
        Id = reader.GetInt32(0),
        Author = reader.GetInt32(1),
        Title = reader.GetString(2),
        Body = reader.IsDBNull(3) ? null : reader.GetString(3),
    });
}

Open.Database.Extensions (factory)

// nothing: the factory creates, opens and disposes the connection

List<Post> posts = connectionFactory
    .Command(ListSql)
    .AddParam("author", 42)



    .Map<Post>().ToList();

Lines you write per call: plain ADO.NET 20, Open.Database.Extensions 4 (plus one line of one-time setup per application).

What is different:

  • Plain ADO.NET needs you to open the connection, create and dispose the command and the reader, add each parameter by hand and copy each column by ordinal, with an IsDBNull check for nullable columns.
  • With the extensions the factory opens and disposes the connection, the command is one call, and the terminal does the read loop and the mapping. Matching columns to properties is by name, ignoring case.

With SQL Server

Open.Database.Extensions.MSSqlClient provides SqlConnectionFactory. Everything above works the same way, and stored procedures are one call away:

// Open.Database.Extensions.MSSqlClient wraps Microsoft.Data.SqlClient.
var connectionFactory = new SqlConnectionFactory(@"Server=(localdb)\MSSQLLocalDB;Database=Blog;Integrated Security=true");

Post[] posts = await connectionFactory
    .Command("select * from posts where author = @author")
    .AddParam("author", 42)
    .Map<Post>()
    .ToArrayAsync();

int touched = await connectionFactory
    .StoredProcedure("[dbo].[TouchPosts]")
    .AddParam("author", 42)
    .ExecuteNonQueryAsync();

Next steps

Edit this page