CLib.OleDb2 1.0.0

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

// Install CLib.OleDb2 as a Cake Tool
#tool nuget:?package=CLib.OleDb2&version=1.0.0

DbOps

Overview

The library simplifies OleDb-based record insertions, updates, and deletions.

Usage

The Field Type

General

The Field type is used to capture name/value pairs of individual fields.

Property Description
RecordKey Used for deletions/updates only. Identifies target records by primary key name/value.
FieldName Identifies the field name. May not be null. Ignored in deletion statements.
FieldValue Field value. Ignored in deletion statements. Field value for INSERT statements and new field value for UPDATE statements.
Constructors
Constructor Description and Use
FromFieldNameValue/FromFieldNameValues Used to define INSERT statement fields. RecordKey property is set to None.
FromRecordKey/FromRecordKeys Used to define records to be deleted. FieldName and FieldValue fields are set to null.
FromRecordKeyFieldNameValue/FromRecordKeyFieldNameValues Used to define records to be updated. All properties must be populated.

Insertions

Insertions are made through the InsertStatement structure. An example is provided below.

    // table in which records are inserted
    let tableName = "InsertTests"
    // fields to be inserted into the table
    let insertionData =
        seq{
            yield seq{
                yield "InsertionDate", DateTime.Now :> obj
                yield "InsertionUser", Environment.UserName :> obj
                yield "Comments", Guid.NewGuid().ToString() :> obj
                yield "Valid", true :> obj
                yield "Count", 1 :> obj
            }
        }
        // map data into the Field record and then create the InsertStatement record
        |> Seq.map(fun p -> InsertStatement.FromFieldNamesValues(tableName, p))
    // connection string to an Access database
    let cs =
        @"C:\db.accdb"
        |> (FileInfo >> ConnectionString.MicrosoftAccessCs)
    // perform insertion
    InsertStatement.Insert(insertionData, cs.OleDbConnectionString)

Deletions

Deletions are made through the DeleteStatement structure. An example is provided below.

    // table with records to be deleted
    let tableName = "InsertTests"
    // keys of records to delete
    let deleteStatements =
        seq{
            yield "InsertionDate", DateTime(2018, 9, 12, 16, 58, 36) :> obj
        } |> Seq.map(fun x -> SqlStatements.DeleteStatement.FromFieldNamesValues(tableName, x))
    // connection string to an Access database
    let cs =
        @"C:\db.accdb"
        |> (FileInfo >> ConnectionString.MicrosoftAccessCs)
    // delete
    SqlStatements.DeleteStatement.Delete(deleteStatements, cs.OleDbConnectionString)

Updates

Updates are made through the UpdateStatement structure. An example is provided below.

    // table with records to be updated
    let tableName = "InsertTests"
    // update data
    let updateStatements =
        seq{
            yield ("InsertionDate", DateTime(2018, 9, 12, 18, 10, 19) :> obj), "Valid", true :> obj
        } |> Seq.map(fun (key, fn, fv) -> SqlStatements.UpdateStatement.FromFieldNamesValues("InsertTests", key, fn, fv))
    // perform updates
    SqlStatements.UpdateStatement.Update(updateStatements, cs.OleDbConnectionString)

Selections

Selections are made through the SelectStatement structure. An example is provided below.

    // table with records to be downloaded
    let tableName = "InsertTests"
    // query parameters
    let fields = [| "Key", "InsertionDate", "Valid" |]
    let filters = "[Valid] = True"
    // SELECT statement
    let ss =
        SqlStatements.SelectStatement.FromTargetTable(tableName, fields, filters)
    // resulting DataTable
    let dt =
        SqlStatements.SelectStatement.Select(selectStatement, cs.OleDbConnectionString)

To get all fields, set the fields parameter to null. To get unfiltered results, set the filters parameter to null.

DatabaseTableWrapper

DatabaseTableWrapper simplifies insertion, deletion, and update of records in a database table. The following example illustrates how the code can be used.

    [<DatabaseLink("Tests")>]
    type Tests =
        {
            [<PrimaryKey("Key")>]
            Key                     : Guid
            [<TableFieldAttribute()>]
            InsertionDate           : DateTime
            [<TableFieldAttribute()>]
            InsertionUser           : string
            [<TableFieldAttribute()>]
            Comments                : string
            [<TableFieldAttribute()>]
            Valid                   : bool
            [<TableFieldAttribute()>]
            Count                   : int
        }

        [<ConnectionStringAttribute()>]
        static member ConnectionString() =
            @"C:\db.accdb"
            |> (FileInfo >> Common.ConnectionString.MicrosoftAccessCs)

    let wrapper = DatabaseTableWrapper<Tests>.FromType<Tests>(null)
    let sampleData = Sample()

    // insert new records
    wrapper.InsertRecords(sampleData)

    // delete all records
    wrapper.DeleteAllRecords()

    // delete specific records
    [
        Guid "c4fff0ca-9887-4ee2-a754-961da4d9013d"
        Guid "cb81674a-7852-46f7-8a74-fa0b383a3fbd"
    ]
    |> List.map (fun a -> a :> obj)
    |> wrapper.DeleteRecords

    // update records
    //  for all inserted keys, change the 'Valid' field to 'false'
    sampleData
    |> Array.map(fun x -> x.Key)
    |> Array.map(fun x -> (x :> obj, "Valid", false :> obj))
    |> wrapper.UpdateRecords

    // download all data into specific records
    match wrapper.SelectRecords(null) with
    | Ok data ->
        data // data is an array of Tests
    | Error (cmd, ex) ->
        printfn "%A" cmd
        printfn "%s" ex.Message
        raise ex

'TableDataManager'

The purpose of this class is to allow simultaneous maintenance of two copies of data through a single interface. The TableDataManager class downloads all records from the database and maps them into the underlying F# record type. Then, insert/delete/update statements simultaneously operator on both the in-memory dataset (array of underying records) and the database table. An example is provided below.

[<DatabaseLink("Tests")>]
type Tests =
    {
        [<PrimaryKey("Key")>]
        Key                     : Guid
        [<TableFieldAttribute()>]
        InsertionDate           : DateTime
        [<TableFieldAttribute()>]
        InsertionUser           : string
        [<TableFieldAttribute()>]
        Comments                : string
        [<TableFieldAttribute()>]
        Valid                   : bool
        [<TableFieldAttribute()>]
        Count                   : int
    }

    [<ConnectionStringAttribute()>]
    static member ConnectionString() =
        @"C:\db.accdb"
        |> (FileInfo >> Common.ConnectionString.MicrosoftAccessCs)

let tdm = TableDataManager<Tests>(null, null)
tdm.Insert(r) // where r:Tests

Connection Strings

Connection strings can be generated through the ConnectionString discriminated union in the Common module.

Microsoft Access connection string and Microsoft Excel connection string can be created by passing the FileInfo parameter to respective constructors. Names Microsoft Excel tables that are in fact worksheets have to be followed by the dollar ($) character.

Microsoft SQL Server connection strings can be generated by providing server name, database name, user identifier, and the user password to the MicrosoftSqlServer constructor.

Product Compatible and additional computed target framework versions.
.NET Framework net47 is compatible.  net471 was computed.  net472 was computed.  net48 was computed.  net481 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
1.0.0 846 9/21/2018

Initial release.