CLib.OleDb2
1.0.0
dotnet add package CLib.OleDb2 --version 1.0.0
NuGet\Install-Package CLib.OleDb2 -Version 1.0.0
<PackageReference Include="CLib.OleDb2" Version="1.0.0" />
paket add CLib.OleDb2 --version 1.0.0
#r "nuget: CLib.OleDb2, 1.0.0"
// 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 | Versions Compatible and additional computed target framework versions. |
---|---|
.NET Framework | net47 is compatible. net471 was computed. net472 was computed. net48 was computed. net481 was computed. |
-
- FSharp.Core (>= 4.5.2)
- System.ValueTuple (>= 4.4.0)
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 | 856 | 9/21/2018 |
Initial release.