3

GitHub - Zaid-Ajaj/DustyTables: Thin F# API for SqlClient for easy data access t...

 2 years ago
source link: https://github.com/zaid-ajaj/dustytables
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

DustyTables

Functional wrapper around plain old (dusty?) SqlClient to simplify data access when talking to MS Sql Server databases.

Install

# nuget client
dotnet add package DustyTables

Query a table

open DustyTables

// get the connection from the environment
let connectionString() = Env.getVar "app_db"

type User = { Id: int; Username: string }

let getUsers() : User list =
    connectionString()
    |> Sql.connect
    |> Sql.query "SELECT * FROM dbo.[Users]"
    |> Sql.execute (fun read ->
        {
            Id = read.int "user_id"
            Username = read.string "username"
        })

Handle null values from table columns:

open DustyTables

// get the connection from the environment
let connectionString() = Env.getVar "app_db"

type User = { Id: int; Username: string; LastModified : Option<DateTime> }

let getUsers() : User list =
    connectionString()
    |> Sql.connect
    |> Sql.query "SELECT * FROM dbo.[users]"
    |> Sql.execute(fun read ->
        {
            Id = read.int "user_id"
            Username = read.string "username"
            // Notice here using `orNone` reader variants
            LastModified = read.dateTimeOrNone "last_modified"
        })

Providing default values for null columns:

open DustyTables

// get the connection from the environment
let connectionString() = Env.getVar "app_db"

type User = { Id: int; Username: string; Biography : string }

let getUsers() : User list =
    connectionString()
    |> Sql.connect
    |> Sql.query "select * from dbo.[users]"
    |> Sql.execute (fun read ->
        {
            Id = read.int "user_id";
            Username = read.string "username"
            Biography = defaultArg (read.stringOrNone "bio") ""
        })

Execute a parameterized query

open DustyTables

// get the connection from the environment
let connectionString() = Env.getVar "app_db"

// get product names by category
let productsByCategory (category: string) =
    connectionString()
    |> Sql.connect
    |> Sql.query "SELECT name FROM dbo.[Products] where category = @category"
    |> Sql.parameters [ "@category", Sql.string category ]
    |> Sql.execute (fun read -> read.string "name")

Executing a stored procedure with parameters

open DustyTables

// get the connection from the environment
let connectionString() = Env.getVar "app_db"

// check whether a user exists or not
let userExists (username: string) : bool =
    connectionString()
    |> Sql.connect
    |> Sql.storedProcedure "user_exists"
    |> Sql.parameters [ "@username", Sql.string username ]
    |> Sql.executeRow (fun read -> read.bool 0)

Executing a stored procedure with table-valued parameters

open DustyTables
open System.Data

// get the connection from the environment
let connectionString() = Env.getVar "app_db"

let executeMyStoredProcedure () : Async<int> =
    // create a table-valued parameter
    let customSqlTypeName = "MyCustomSqlTypeName"
    let dataTable = new DataTable()
    dataTable.Columns.Add "FirstName" |> ignore
    dataTable.Columns.Add "LastName"  |> ignore
    // add rows to the table parameter
    dataTable.Rows.Add("John", "Doe") |> ignore
    dataTable.Rows.Add("Jane", "Doe") |> ignore
    dataTable.Rows.Add("Fred", "Doe") |> ignore

    connectionString()
    |> Sql.connect
    |> Sql.storedProcedure "my_stored_proc"
    |> Sql.parameters
        [ "@foo", Sql.int 1
          "@people", Sql.table (customSqlTypeName, dataTable) ]
    |> Sql.executeNonQueryAsync

Building and running tests locally

You only need a working local SQL server. The tests will create databases when required and dispose of them at the end of the each test

cd ./DustyTables.Build

# Build the solution
dotent run
# Run the tests
dotent run -- test
# Publish the nuget
dotnet run -- publish

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK