Donald 10.0.0-alpha2

This is a prerelease version of Donald.
There is a newer version of this package available.
See the version list below for details.
dotnet add package Donald --version 10.0.0-alpha2
NuGet\Install-Package Donald -Version 10.0.0-alpha2
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="Donald" Version="10.0.0-alpha2" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add Donald --version 10.0.0-alpha2
#r "nuget: Donald, 10.0.0-alpha2"
#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 Donald as a Cake Addin
#addin nuget:?package=Donald&version=10.0.0-alpha2&prerelease

// Install Donald as a Cake Tool
#tool nuget:?package=Donald&version=10.0.0-alpha2&prerelease

Donald

NuGet Version build

Meet Donald (Chamberlin).

If you're a programmer and have used a database, he's impacted your life in a big way.

This library is named after him.

Honorable mention goes to @dsyme another important Donald and F#'s BDFL.

Key Features

Donald is a well-tested library that aims to make working with ADO.NET safer and a lot more succinct. It is an entirely generic abstraction, and will work with all ADO.NET implementations.

If you came looking for an ORM (object-relational mapper), this is not the library for you. And may the force be with you.

Design Goals

  • Support all ADO implementations
  • Provide a succinct, type-safe API for interacting with databases
  • Enable asynchronuos workflows
  • Make object mapping easier
  • Improve data access performance
  • Provide additional context during exceptions

Getting Started

Install the Donald NuGet package:

PM>  Install-Package Donald

Or using the dotnet CLI

dotnet add package Donald

Quick Start

open Donald

type Author = { FullName : string }

module Author =
  let ofDataReader (rd : IDataReader) : Author =
      { FullName = rd.ReadString "full_name" }

let authors (conn : IDbConnection) : Author list =
    let sql = "
    SELECT  full_name
    FROM    author
    WHERE   author_id = @author_id"

    let param = [ "author_id", SqlType.Int 1 ]

    conn
    |> Db.newCommand sql
    |> Db.setParams param
    |> Db.query Author.ofDataReader

An Example using SQLite

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

Reminder: Donald will work with any ADO implementation (SQL Server, SQLite, MySQL, Postgresql etc.).

Consider the following model:

type Author =
    { AuthorId : int
      FullName : string }

module Author -
    let ofDataReader (rd : IDataReader) : Author =
        { AuthorId = rd.ReadInt32 "author_id"
          FullName = rd.ReadString "full_name" }

Query for multiple strongly-typed results

Important: Donald is set to use CommandBehavior.SequentialAccess by default. See performance for more information.

let sql = "SELECT author_id, full_name FROM author"

conn
|> Db.newCommand sql
|> Db.query Author.ofDataReader // Author list

// Async
conn
|> Db.newCommand sql
|> Db.Async.query Author.ofDataReader // Task<Author list>

Query for a single strongly-typed result

let sql = "SELECT author_id, full_name FROM author"

conn
|> Db.newCommand sql
|> Db.setParams [ "author_id", SqlType.Int 1 ]
|> Db.querySingle Author.ofDataReader // Author option

// Async
conn
|> Db.newCommand sql
|> Db.setParams [ "author_id", SqlType.Int 1 ]
|> Db.Async.querySingle Author.ofDataReader // Task<Author option>

Execute a statement

let sql = "INSERT INTO author (full_name)"

// Strongly typed input parameters
let param = [ "full_name", SqlType.String "John Doe" ]

conn
|> Db.newCommand sql
|> Db.setParams param
|> Db.exec // unit

// Async
conn
|> Db.newCommand sql
|> Db.setParams param
|> Db.Async.exec // Task<unit>

Execute a statement many times

let sql = "INSERT INTO author (full_name)"

let param =
    [ "full_name", SqlType.String "John Doe"
      "full_name", SqlType.String "Jane Doe" ]

conn
|> Db.newCommand sql
|> Db.execMany param

// Async
conn
|> Db.newCommand sql
|> Db.Async.execMany param
let sql = "INSERT INTO author (full_name)"

let param = [ "full_name", SqlType.String "John Doe" ]

conn
|> Db.newCommand sql
|> Db.setParams param
|> Db.exec // unit

// Async
conn
|> Db.newCommand sql
|> Db.setParams param
|> Db.Async.exec // Task<unit>

Execute statements within an explicit transaction

Donald exposes most of it's functionality through the Db module. But three IDbTransaction type extension are exposed to make dealing with transactions safer:

  • TryBeginTransaction() opens a new transaction or raises DbTransactionError
  • TryCommit() commits a transaction or raises DbTransactionError and rolls back
  • TryRollback() rolls back a transaction or raises DbTransactionError
// Safely begin transaction or throw CouldNotBeginTransactionError on failure
use tran = conn.TryBeginTransaction()

let insertSql = "INSERT INTO author (full_name)"
let param = [ "full_name", SqlType.String "John Doe" ]

let insertResult =
    conn
    |> Db.newCommand insertSql
    |> Db.setTransaction tran
    |> Db.setParams param
    |> Db.exec

match insertResult with
| Ok () ->
    // Attempt to commit, rollback on failure and throw CouldNotCommitTransactionError
    tran.TryCommit ()

    conn
    |> Db.newCommand "SELECT author_id, full_name FROM author WHERE full_name = @full_name"
    |> Db.setParams param
    |> Db.querySingle Author.ofDataReader

| Error e ->
    // Attempt to commit, rollback on failure and throw CouldNotCommitTransactionError
    tran.TryRollback ()
    Error e

Reading Values

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

  1. Get value, automatically defaulted
  2. Get value as option<'a>

If you need an explicit Nullable<'a> you can use Option.asNullable.

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

rd.ReadString "some_field"         // string -> string
rd.ReadBoolean "some_field"        // string -> bool
rd.ReadByte "some_field"           // string -> byte
rd.ReadChar "some_field"           // string -> char
rd.ReadDateTime "some_field"       // string -> DateTime
rd.ReadDecimal "some_field"        // string -> Decimal
rd.ReadDouble "some_field"         // string -> Double
rd.ReadFloat "some_field"          // string -> float32
rd.ReadGuid "some_field"           // string -> Guid
rd.ReadInt16 "some_field"          // string -> int16
rd.ReadInt32 "some_field"          // string -> int32
rd.ReadInt64 "some_field"          // string -> int64
rd.ReadBytes "some_field"          // string -> byte[]

rd.ReadStringOption "some_field"   // string -> string option
rd.ReadBooleanOption "some_field"  // string -> bool option
rd.ReadByteOption "some_field"     // string -> byte option
rd.ReadCharOption "some_field"     // string -> char option
rd.ReadDateTimeOption "some_field" // string -> DateTime option
rd.ReadDecimalOption "some_field"  // string -> Decimal option
rd.ReadDoubleOption "some_field"   // string -> Double option
rd.ReadFloatOption "some_field"    // string -> float32 option
rd.ReadGuidOption "some_field"     // string -> Guid option
rd.ReadInt16Option "some_field"    // string -> int16 option
rd.ReadInt32Option "some_field"    // string -> int32 option
rd.ReadInt64Option "some_field"    // string -> int64 option
rd.ReadBytesOption "some_field"    // string -> byte[] option

Exceptions

Donald exposes several custom exceptions which interleave the exceptions thrown by ADO.NET with contextually relevant metadata.

/// Details of failure to connection to a database/server.
type DbConnectionException =
    inherit Exception
    val ConnectionString : string option

/// Details of failure to execute database command or transaction.
type DbExecutionException =
    inherit Exception
    val Statement : string option
    val Step : DbTransactionStep option

/// Details of failure to access and/or cast an IDataRecord field.
type DbReaderException =
    inherit Exception
    val FieldName : string option

Performance

By default, Donald will consume IDataReader using CommandBehavior.SequentialAccess. This allows the rows and columns to be read in chunks (i.e., streamed), but forward-only. As opposed to being completely read into memory all at once, and readable in any direction. The benefits of this are particular felt when reading large CLOB (string) and BLOB (binary) data. But is also a measureable performance gain for standard query results as well.

The only nuance to sequential access is that columns must be read in the same order found in the SELECT clause. Aside from that, there is no noticeable difference from the perspective of a library consumer.

Configuring CommandBehavior can be done two ways:

let sql = "SELECT author_id, full_name FROM author"

conn
|> Db.newCommand sql
|> Db.setCommandBehavior CommandBehavior.Default
|> Db.query Author.ofDataReader

Find a bug?

There's an issue for that.

License

Built with ♥ by Pim Brouwers in Toronto, ON. Licensed under Apache License 2.0.

Product Compatible and additional computed target framework versions.
.NET net6.0 is compatible.  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. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

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
10.1.0 103 3/20/2024
10.0.2 611 12/12/2023
10.0.1 908 7/11/2023
10.0.0 131 7/8/2023
10.0.0-alpha3 152 2/11/2023
10.0.0-alpha2 137 2/4/2023
10.0.0-alpha1 123 2/3/2023
9.0.1 1,303 1/11/2023
9.0.0 280 12/23/2022
8.0.2 9,210 11/23/2022
8.0.1 298 11/23/2022
8.0.0 348 11/23/2022
7.1.0 2,603 12/17/2021
7.0.0 307 12/14/2021
7.0.0-alpha1 243 11/1/2021
6.2.5 598 8/4/2021
6.2.4 353 8/4/2021
6.2.3 362 7/30/2021
6.2.2 421 7/27/2021
6.2.1 324 7/27/2021
6.2.0 549 7/26/2021
6.1.0 428 7/6/2021
6.1.0-beta3 207 7/5/2021
6.1.0-beta2 220 7/4/2021
6.1.0-beta1 316 7/4/2021
6.0.0 393 4/11/2021
5.1.3 392 3/29/2021
5.1.2 413 2/27/2021
5.1.1 430 1/23/2021
5.0.1 1,002 12/3/2020
5.0.0 408 12/1/2020
5.0.0-alpha3 255 12/1/2020
5.0.0-alpha2 258 11/30/2020
5.0.0-alpha1 240 11/30/2020
4.0.0 456 11/12/2020
3.0.4 1,486 10/31/2020
3.0.3 717 8/2/2020
3.0.2 501 7/17/2020
3.0.1 481 7/14/2020
3.0.0 518 6/29/2020
2.0.2 462 5/1/2020
2.0.1 442 4/27/2020
2.0.0 429 4/27/2020
1.0.6 413 4/24/2020
1.0.4 411 4/24/2020
1.0.3 424 4/24/2020
1.0.2 422 4/24/2020
1.0.1 626 4/18/2020
1.0.0 467 4/5/2020