Spiffy 3.1.4

dotnet add package Spiffy --version 3.1.4
NuGet\Install-Package Spiffy -Version 3.1.4
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="Spiffy" Version="3.1.4" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add Spiffy --version 3.1.4
#r "nuget: Spiffy, 3.1.4"
#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 Spiffy as a Cake Addin
#addin nuget:?package=Spiffy&version=3.1.4

// Install Spiffy as a Cake Tool
#tool nuget:?package=Spiffy&version=3.1.4

Spiffy - simple data access for .NET

NuGet Version build

Spiffy is a well-tested library that aims to make working with ADO.NET from C# a lot simpler.

The library is delivered as a fluent API for building IDbCommand instances, and IDbCommand extension methods to support execution. Spiffy is not an ORM, encouraging you to take back control of your mappings. However, Spiffy does extend the IDataReader interface with several helpers covering most primitive types to make retrieving values safer and more direct.

Key Features

  • Fluent API for build IDbCommand instances.
  • Simple execution model, delivered as IDbCommand extension methods.
  • Safe value reading via IDataReader extensions.
  • Enhanced exception output.
  • Asynchronous capabilities.

Design Goals

  • Appear "native", augmenting the base ADO.NET functionality as little as possible and adhering to internal naming conventions.
  • Encourage manual mappings by providing a succinct and safe methodology to obtain values from tabular data.
  • Provide an easy to reason about execution model.
  • Support asynchronous database workloads.

Getting Started

Install the Spiffy NuGet package:

PM>  Install-Package Spiffy

Or using the dotnet CLI

dotnet add package Spiffy

Quick Start

using System;
using System.Data.Sqlite;
using Spiffy;

namespace SpiffyQuickStart
{
  class Program
  {
    const connectionString = "{your connection string}";

    static void Main(string[] args)
    {
      var sql = "SELECT author_id, full_name FROM author WHERE author_id = @author_id";

      var param = new DbParams("author_id", 1);

      using var connection = new SqliteConnection(connectionString);

      using var cmd = new DbCommandBuilder(connection, sql, param).Build();

      cmd.Query(cmd, rd =>
        Console.WriteLine("Hello {0}" rd.ReadString("full_name")));
    }
  }
}


An Example using SQLite

For this example, assume we have an IDbConnection named connection:

using var connection = new SqliteConnection("Data Source=hello.db");

Consider the following domain model:

public class Author
{
  public int AuthorId { get; set; }
  public string FullName { get; set; }

  public static Author FromDataReader (IDataReader rd)
  {
    return new Author() {
      AuthorId = rd.ReadInt32("person_id"),
      FullName = rd.ReadString("full_name")
    }
  }
}

Query for multiple strongly-type results

var sql = "SELECT author_id, full_name FROM author";

using var cmd = new DbCommandBuilder(connection, sql).Build();

var authors = cmd.Query(Author.FromDataReader);

Query for a single strongly-type result

var sql = "SELECT author_id, full_name FROM author WHERE author_id = @author_id";

var param = new DbParams("author_id", authorId);

using var cmd = new DbCommandBuilder(connection, sql, param).Build();

// This method is optimized to dispose the `IDataReader` after safely reading the first `IDataRecord
var author = cmd.QuerySingle(Author.FromDataReader);

Execute a statement multiple times

var sql = "INSERT INTO author (full_name) VALUES (@full_name)";

var paramList = authors.Select(a => new DbParams("full_name", a.FullName));

using var cmd = new DbCommandBuilder(connection, sql).Build();

cmd.ExecMany(paramList);

Execute a statement transactionally

var sql = "UPDATE author SET full_name = @full_name where author_id = @author_id";

var param = new DbParams() {
    { "author_id", author.AuthorId },
    { "full_name", author.FullName }
};

using var transaction = connection.TryBeginTransaction();

using var cmd = new DbCommandBuilder(tran, sql, param).Build();

cmd.Exec();

transaction.TryCommit();

Asynchronously execute a scalar command (single value)

var sql = "SELECT COUNT(*) AS author_count FROM author";

using var cmd = new DbCommandBuilder(connection, sql).Build();

var count = await cmd.QuerySingleAsync(rd => rd.ReadInt32("author_count"));

Async versions of all data access methods are available: ExecAsync, ExecManyAsync, QueryAsync, QuerySingleAsync, ReadAsync

IDataReader Extension Methods

To make obtaining values from reader more straight-forward, 2 sets of extension methods are available for:

  • Get value, automatically defaulted
  • Get value as Nullable<'a>

Assume we have an active IDataReader called rd and are currently reading a row, the following extension methods are available to simplify reading values:

public static string ReadString(this IDataReader rd, string field);
public static char ReadChar(this IDataReader rd, string field);
public static bool ReadBoolean(this IDataReader rd, string field);
public static bool ReadBool(this IDataReader rd, string field);
public static byte ReadByte(this IDataReader rd, string field);
public static short ReadInt16(this IDataReader rd, string field);
public static short ReadShort(this IDataReader rd, string field);
public static int ReadInt32(this IDataReader rd, string field);
public static int ReadInt(this IDataReader rd, string field);
public static int ReadInt(this IDataReader rd, string field);
public static long ReadInt64(this IDataReader rd, string field);
public static long ReadLong(this IDataReader rd, string field);
public static decimal ReadDecimal(this IDataReader rd, string field);
public static double ReadDouble(this IDataReader rd, string field);
public static float ReadFloat(this IDataReader rd, string field);
public static Guid ReadGuid(this IDataReader rd, string field);
public static DateTime ReadDateTime(this IDataReader rd, string field);

public static bool? ReadNullableBoolean(this IDataReader rd, string field);
public static bool? ReadNullableBool(this IDataReader rd, string field);
public static byte? ReadNullableByte(this IDataReader rd, string field);
public static short? ReadNullableInt16(this IDataReader rd, string field);
public static short? ReadNullableShort(this IDataReader rd, string field);
public static int? ReadNullableInt32(this IDataReader rd, string field);
public static int? ReadNullableInt(this IDataReader rd, string field);
public static int? ReadNullableInt(this IDataReader rd, string field);
public static long? ReadNullableInt64(this IDataReader rd, string field);
public static long? ReadNullableLong(this IDataReader rd, string field);
public static decimal? ReadNullableDecimal(this IDataReader rd, string field);
public static double? ReadNullableDouble(this IDataReader rd, string field);
public static float? ReadNullableFloat(this IDataReader rd, string field);
public static Guid? ReadNullableGuid(this IDataReader rd, string field);
public static DateTime? ReadNullableDateTime(this IDataReader rd, string field);

Exceptions

Docs comming soon

Why no automatic mapping?

No matter how you slice it (cached or not) reflection is slow, brittle and hard to debug. As such, the library encourages you to define your mappings manually and aims to help you do this by extending the IDataReader interface with helpers to make retrieving values safer and more direct.

Why "Spiffy"?

It's an homage to Dapper which was transformative in it's approach to database-bound workloads for .NET.

Find a bug?

There's an issue for that.

License

Built with ♥ by NHLPA Engineering in Toronto, ON. Licensed under MIT.

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 netcoreapp2.0 was computed.  netcoreapp2.1 was computed.  netcoreapp2.2 was computed.  netcoreapp3.0 was computed.  netcoreapp3.1 was computed. 
.NET Standard netstandard2.0 is compatible.  netstandard2.1 was computed. 
.NET Framework net461 was computed.  net462 was computed.  net463 was computed.  net47 was computed.  net471 was computed.  net472 was computed.  net48 was computed.  net481 was computed. 
MonoAndroid monoandroid was computed. 
MonoMac monomac was computed. 
MonoTouch monotouch was computed. 
Tizen tizen40 was computed.  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.
  • .NETStandard 2.0

    • No dependencies.

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
3.1.4 432 9/16/2022
3.1.3 366 9/13/2021
3.1.2 327 9/13/2021
3.1.1 356 3/9/2021
3.1.0 333 2/26/2021
3.0.0 332 12/15/2020
2.0.0 449 11/28/2020
1.1.0 350 11/27/2020
1.0.0 379 11/23/2020