CLib.OleDb2 1.0.0

OleDb wrapper that allows management of database tables.

Install-Package CLib.OleDb2 -Version 1.0.0
dotnet add package CLib.OleDb2 --version 1.0.0
<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
The NuGet Team does not provide support for this client. Please contact its maintainers for support.

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.

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.

Release Notes

Initial release.

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version History

Version Downloads Last updated
1.0.0 308 9/21/2018