Dynamic reading from an Oracle database

Implementation

let inline tryUnbox<'a> (x:obj) =
    match x with
    | :? 'a as result -> Some(result)
    | _ -> None

type DynamicOracleDataReader(reader:OracleDataReader) =
    member private x.Reader = reader
    member x.Read() = reader.Read()
    static member (?) (dr:DynamicOracleDataReader, name:string) : 'a option = 
        tryUnbox (dr.Reader.[name])
    interface IDisposable with
        member x.Dispose() = reader.Dispose()

type DynamicOracleCommand(cmd:OracleCommand) =
    member private x.Command = cmd
    static member (?<-) (cmd:DynamicOracleCommand, name:string, value) = 
        let p = new OracleParameter(name, box value) 
        cmd.Command.Parameters.Add(p) |> ignore
    member x.ExecuteNonQuery() = cmd.ExecuteNonQuery()
    member x.ExecuteReader() = new DynamicOracleDataReader(cmd.ExecuteReader())
    member x.ExecuteScalar() = cmd.ExecuteScalar()
    member x.Parameters = cmd.Parameters
    interface IDisposable with
        member x.Dispose() = cmd.Dispose()

type DynamicOracleConnection(connStr:string) =
    let conn = new OracleConnection(connStr)
    member private x.Connection = conn
//    static member (?) (conn:DynamicOracleConnection, name) =
//        let command = new OracleCommand(name, conn.Connection)
//        command.CommandType <- CommandType.Text 
//        new DynamicOracleCommand(command)
    member x.GenerateCommand(query:string) = 
        let command = new OracleCommand(query, conn)
        command.BindByName <- true
        new DynamicOracleCommand(command)
    member x.Open() = conn.Open()

    interface IDisposable with
        member x.Dispose() = conn.Dispose()

Usage

type Order = {
    OrderId: int
    Comments: string option}

let connectionString = 
    "User Id = XXXXXX; " + 
    "Password = XXXXXX; " + 
    "Data Source = " + 
        "(Description = " +
        "(Address = " + 
        "(Protocol = TCP) " + 
        "(Host = 127.0.0.1) " + 
        "(Port = 1521)) " + 
        "(Connect_Data = (Service_Name = XXXXXX)))"

let getOrderByIdQuery =
    "select * " +
    "from ORDERS " +
    "where IDORDER = : orderId " +
    "order by IDORDER"

let getOrderById (orderId: int) = seq {
    use conn = new DynamicOracleConnection(connectionString)
    use cmd = conn.GenerateCommand(getOrderByIdQuery)
    
    cmd?orderId <- orderId

    conn.Open()
    use reader = cmd.ExecuteReader ()

    while (reader.Read()) do
        yield {
            OrderId = defaultArg reader?IDORDER 0
            Comments = reader?COMMENTS}}

References

• Adapted from http://tomasp.net/blog/dynamic-sql.aspx/

Icons of an F# WPF application

There are at least two different usages for icons. First, there is the application icon displayd on the top left corner of the application window and also in the task bar. Second, Windows Explorer shows application icons in the file lists and also uses them for shortcuts.

Application icon

1. Create the icon. For example, save an image with Paint as a 256 color bitmap and change the file extension to *.ico
2. Save the icon in an “Resources” subfolder of the project
3. Set the Build Action of the icon to Resource
4. Include the icon with the XAML file

<Window xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Icon="/Resources/MyIcon.ico"
        Title="MyApplication" Height="600" Width="1000" >

Windows Explorer icon

1. Save the icon in 3.00 format using the free tool @icon sushi.
2. Create a text file with a *.rc extension. Save the *.rc file in the “Resources” subfolder.
3. The *.rc file should contain a single line. The ‘1’ is part of the text and not a line number.

1 ICON "MyIcon.ico"

4. Ensure that the Resource Compiler rc.exe is installed. Adding the C++ Tools to Visual Studio should also install rc.exe.
5. Compile the *.rc file with rc.exe into an *.res file.

C:\Program Files (x86)\Windows Kits\10\bin\x86>rc.exe /v C:\pathToMyFile\Resources\MyResources.rc

C:\Program Files (x86)\Windows Kits\10\bin\10.0.19041.0\x86>rc.exe /v "C:\pathToMyFile\Resources\MyResources.rc"

6. In Visual Studio, select the Application tab of project property page
7. Select the compiled *.res file in the Resources section. The complete absolute path to the file is shown. Leave the tick box “Use standard resource names” unchecked.
8. With a text editor edit the project *.fsproj file. Change the absolut path to a relative path.
   Visual Studio should be closed.

<Win32Resource>Resources\MyResources.res</Win32Resource>

Windows Explorer Icon with .NET 5

Adding a Windows Explorer Icon became lot simpler with .NET 5:

1. Get an icon: Create an icon from a image as outline above or download it from https://icon-icons.com or https://iconarchive.com
2. Copy the icon to a Resources folder in the project folder
   Resources/MyIcon.ico
3. Edit the project file *.fsproj. Add the <ApplicationIcon> tag:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>WinExe</OutputType>
    <TargetFramework>net5.0-windows</TargetFramework>
    ...
	<ApplicationIcon>Resources/FillCertificate.ico</ApplicationIcon>
  </PropertyGroup>

References

• StackOverflow: Changing F# application icon
• Using RC (The RC Command Line)
• @icon sushi
• Embedding an F# Executable Icon
• StackOverflow: Embed an application icon using WPF and F#
• StackOverflow: How to set Resource file path relative to the project file?
• https://icon-icons.com
• https://iconarchive.com

Add a reference to the solution:
Project menu > Add Reference. On the COM tab, locate Microsoft Excel 16.0 Object Library. That’s the reference which contains Microsoft.Office.Interop.Excel.dll.
Also, add a reference to the office library Microsoft Office 16.0 Object Library.

#if INTERACTIVE
#r "Microsoft.Office.Interop.Excel"
#endif

open System
open Microsoft.Office.Interop.Excel

let excel = new ApplicationClass(Visible = false)
let workbook = excel.Workbooks.Open (templateFile)
//let workbook = excel.Workbooks.Add (xlWBATemplate.xlWBATWorksheet)
let worksheet = (workbook.Worksheets.[1] :?> Worksheet)

excel.Visible <- true
excel.Application.ScreenUpdating <- false

worksheet.Range("A9","E9").Value2 <- [| "This"; "is"; "my"; "test"; ":-)" |]

workbook.SaveAs(outputFile)

workbook.Close()
excel.Workbooks.Close()
excel.Application.Quit()

releaseObject excel

type Section (lines:string list) =
    member x.Lines = lines

/// Splits a sequence after the last line of a section defined by the predicate.
let splitAtEach (predicate: 'T -> bool) (xs : seq<'T>) : seq<'T list> =
    let section = new ResizeArray<'T>()
    seq {
        for x in xs do
            section.Add x
            if predicate x then 
                yield Seq.toList section
                section.Clear()
        if section.Count > 0 then
            yield Seq.toList section}

/// Splits a sequence before the first line of a section defined by the predicate.
let splitBeforeEach (predicate: 'T -> bool) (xs : seq<'T>) : seq<'T list> =
    let section = new ResizeArray<'T>()
    seq {
        for x in xs do
            if predicate x then 
                yield Seq.toList section
                section.Clear()
            section.Add x
        if section.Count > 0 then
            yield Seq.toList section}

usage:

open System.IO

let lines = File.ReadAllLines(myTextFile)

let getSections lines =
    splitAtEach isSectionLine lines
    |> Seq.map (fun x -> new Section(x))
    |> Seq.toList

This is a slightly modified version from: https://codereview.stackexchange.com/questions/55554/parsing-sections-from-a-log-file-using-f

This function prints the data type of an object to the standard output.

let getObjectType (x:obj) =
    match x with
    | :? sbyte       as y -> printfn  "sbyte : %A" y
    | :? byte        as y -> printfn  "byte : %A" y
    | :? int16       as y -> printfn  "int16 : %A" y
    | :? uint16      as y -> printfn  "uint16 : %A" y
    | :? int32       as y -> printfn  "int32 : %A (int)" y
//    | :? int         as y -> printfn  "int : %A" y            
//    //   int is actually the same as int32
    | :? uint32      as y -> printfn  "uint32 : %A" y
    | :? int64       as y -> printfn  "int64 : %A" y
    | :? uint64      as y -> printfn  "uint64 : %A" y
    | :? bigint      as y -> printfn  "bigint : %A" y
    | :? float32     as y -> printfn  "float32 : %A" y
    | :? float       as y -> printfn  "float : %A" y
    | :? decimal     as y -> printfn  "decimal : %A" y
//    | :? BigRational as y -> printfn  "BigRational : %A" y    
//    //   BigRational requires a reference to FSharp.PowerPack.dll
    | :? char        as y -> printfn  "char : %A" y
    | :? string      as y -> printfn  "string : %A" y
    | :? bool        as y -> printfn  "bool : %A" y
    | :? System.DateTime as y -> printfn "DateTime : %A" y
    | _ -> printfn "unknown data type"

List of F# data types: https://www.tutorialspoint.com/fsharp/fsharp_data_types.htm