DOCSEARCHbyAlgolia
Submit Docs

Donald F# Library

repository·master·Indexed 41 days ago

https://github.com/pimbrouwers/donald

A generic F# library for type-safe and succinct ADO.NET operations. It provides an abstraction layer for SQL Server, SQLite, and PostgreSQL, featuring asynchronous workflows, object mapping via IDataReader, and a SqlType union for type-safe parameter passing. Includes support for batching, explicit transactions, and specialized database exception handling.

Tokens
2.2K
Snippets
10
Records
10
Agent score
2%

What's inside Donald

  1. Execute statements within a transaction

    master

    There are two ways to handle transactions in Donald:

    1. Batching: Use Db.batch or Db.Async.batch. This processes the entire block in an all-or-none fashion.
    2. Explicit Control: Use TryBeginTransaction(), TryCommit(), and TryRollback() on the connection or transaction object. TryBeginTransaction() safely starts a transaction or throws CouldNotBeginTransactionError.

    When using explicit transactions, use Db.newCommandForTransaction to create commands associated with that transaction.

    // Option 1: Batching
conn
|> Db.batch (fun tran ->
    for fullName in [ "John Doe"; "Jane Doe" ] do
        tran
        |> Db.newCommandForTransaction "INSERT INTO author (full_name) VALUES (@full_name)"
        |> Db.setParams ["full_name", SqlType.String fullName ]
        |> Db.exec)

// Option 2: Explicit control
use tran = conn.TryBeginTransaction()

for fullName in [ "John Doe"; "Jane Doe" ] do
    tran
    |> Db.newCommandForTransaction "INSERT INTO author (full_name) VALUES (@full_name)"
    |> Db.setParams ["full_name", SqlType.String fullName ]
    |> Db.exec

tran.TryCommit ()

  2. Configure CommandBehavior for performance

    master

    By default, Donald uses CommandBehavior.SequentialAccess. This allows streaming large CLOB/BLOB data in chunks, providing a performance gain. However, it requires that columns are read in the exact order they appear in the SELECT statement.

    To change this behavior, use Db.setCommandBehavior.

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

  4. Execute a statement many times with different parameters

    master

    Use Db.execMany to execute the same command multiple times with a list of parameter sets. This is useful for bulk inserts.

    conn
|> Db.newCommand "INSERT INTO author (full_name) VALUES (@full_name)"
|> Db.execMany [
    "full_name", SqlType.String "John Doe"
    "full_name", SqlType.String "Jane Doe" ] // Returns unit

  5. Read values from an IDataReader

    master

    Donald provides extension methods for IDataReader to simplify reading values. There are two sets of methods:

    1. Defaulted values: Returns the value directly (e.g., ReadString).
    2. Option values: Returns an option<'a> (e.g., ReadStringOption), which is useful for handling nulls.

    Available types include String, Boolean, Byte, Char, DateTime, Decimal, Double, Float, Guid, Int16, Int32, Int64, and Bytes.

    // Example of reading values
let name = rd.ReadString "some_field"         // string -> string
let age = rd.ReadInt32Option "age_field"     // int32 -> int32 option

  6. Query for a single strongly-typed result

    master

    Use Db.querySingle to retrieve at most one result. This returns an option<'T> (or Task<'T option> when using the async variant), which is None if no record is found.

    // Synchronous single query
conn
|> Db.newCommand "SELECT author_id, full_name FROM author"
|> Db.setParams [ "author_id", SqlType.Int32 1 ]
|> Db.querySingle Author.ofDataReader // Returns Author option

// Asynchronous single query
conn
|> Db.newCommand "SELECT author_id, full_name FROM author"
|> Db.setParams [ "author_id", SqlType.Int32 1 ]
|> Db.Async.querySingle Author.ofDataReader // Returns Task<Author option>

  7. Query for multiple strongly-typed results

    master

    Use Db.query to retrieve a list of items. You provide a mapping function that takes an IDataReader and returns your domain object. Donald uses CommandBehavior.SequentialAccess by default for better performance with large data, which requires reading columns in the order they appear in the SELECT clause.

    For asynchronous workflows, use Db.Async.query to return a Task<'T list>.

    // Synchronous query
conn
|> Db.newCommand "SELECT author_id, full_name FROM author"
|> Db.query Author.ofDataReader // Returns Author list

// Asynchronous query
conn
|> Db.newCommand "SELECT author_id, full_name FROM author"
|> Db.Async.query Author.ofDataReader // Returns Task<Author list>

  8. Execute a database statement

    master

    Use Db.exec to run a command that does not return a result set (e.g., INSERT, UPDATE, DELETE). Use Db.Async.exec for the asynchronous version.

    conn
|> Db.newCommand "INSERT INTO author (full_name) VALUES (@full_name)"
|> Db.setParams [ "full_name", SqlType.String "John Doe" ]
|> Db.exec // Returns unit

  9. Define command parameters using SqlType

    master

    Parameters are passed using the SqlType union type. This ensures type safety when mapping F# values to ADO.NET types.

    Supported SqlType cases include:

    • Null
    • String of string / AnsiString of string
    • Boolean of bool
    • Byte of byte / Bytes of byte[]
    • Char of char / AnsiChar of char
    • Decimal of decimal
    • Double of double
    • Float of float
    • Guid of Guid
    • Int16 of int16
    • Int32 of int32 / Int of int32
    • Int64 of int64
    • DateTime of DateTime

    There are also helper functions like sqlInt32 which implicitly call the respective conversion functions.

    type SqlType = 
    | Null
    | String of string
    | AnsiString of string
    | Boolean of bool
    | Byte of byte
    | Char of char
    | AnsiChar of char
    | Decimal of decimal
    | Double of double
    | Float of float
    | Guid of Guid
    | Int16 of int16
    | Int32 of int32
    | Int of int32
    | Int64 of int64
    | DateTime of DateTime
    | Bytes of byte[]

  10. Handle database exceptions

    master

    Donald provides custom exception types that include additional context like connection strings, SQL statements, or field names to aid debugging:

    • DbConnectionException: Failure to connect. Includes ConnectionString (option).
    • DbExecutionException: Failure during command or transaction execution. Includes Statement (option) and Step (option).
    • DbReaderException: Failure to access or cast an IDataRecord field. Includes FieldName (option).
    • DbTransactionException: Failure to commit or rollback. Includes Step.
    type DbConnectionException = inherit Exception
type DbExecutionException = inherit Exception
type DbReaderException = inherit Exception
type DbTransactionException = inherit Exception