MySqlHelpers.fs

module helpers 
    open System
    open System.Data

    type Column = { Name:String; Type:DbType }
    type Value = { ColumnName:String; Value:obj }
    type Query = { Type:StatementType; TableName:String; Columns: Value list; IdColumn:string }

    let SELECT_TEMPLATE:FormattableString = $"SELECT {0} FROM {1}"; 
    let INSERT_TEMPLATE:FormattableString = $"INSERT INTO {0} ({1}) VALUES ({2})";
    let UPDATE_TEMPLATE:FormattableString = $"UPDATE {0} SET {1} WHERE {2}=@{2}";
    let DELETE_TEMPLATE:FormattableString = $"DELETE FROM {0} WHERE {1}";

    let formatSelect (query: Query) =
        let formattedCollumns = 
            query.Columns
            |> Seq.map(fun c -> c.ColumnName)
            |> Seq.toArray
            |> fun s -> String.Join(",", s) 
        String.Format(SELECT_TEMPLATE.Format, query.TableName, formattedCollumns)

    let formatUpdate (query: Query) =
        let setExpression = 
            query.Columns
            |> Seq.map(fun c -> $"{c.ColumnName}=@{c.ColumnName}") 
            |> Seq.toArray
            |> fun s -> String.Join(",", s) 
        String.Format(UPDATE_TEMPLATE.Format, query.TableName, setExpression, query.IdColumn, query.IdColumn)

    let formatInsert (query: Query) =
        let collumnsExpression = 
            query.Columns
            |> Seq.map(fun c -> $"{c.ColumnName}") 
            |> Seq.toArray
            |> fun s -> String.Join(",", s) 
        let ValuesExpression = 
            query.Columns 
            |> Seq.map(fun c -> $"@{c.ColumnName}")
            |> Seq.toArray
            |> fun s -> String.Join(",", s)
        String.Format(INSERT_TEMPLATE.Format, query.TableName, collumnsExpression, ValuesExpression)

    let formatDelete (query: Query) =
        String.Format(DELETE_TEMPLATE.Format, query.TableName, $"{query.IdColumn}=@{query.IdColumn}")


    let selectFromTable table columns = 
        String.Format(SELECT_TEMPLATE.Format, table, columns)

    let formatInsertQuery table columns values template = 
        String.Format(template, table, columns, values)        
    
    let (|Select|) (query) =
        formatSelect query

    let (|Insert|) (query) =
        formatInsert query

    let (|Update|) (query) =
        formatUpdate query
    
    let (|Delete|) (query) =
        formatDelete query

    let (|Batch|) (query) =
        query.ToString()
        
    let (|Select|Insert|Update|Delete|Batch|) (query:Query) =
        match query.Type with
        | StatementType.Select -> Select //query
        | StatementType.Insert -> Insert //query
        | StatementType.Update -> Update //query
        | StatementType.Delete -> Delete //query
        | StatementType.Batch  -> Batch

    

program.fs

(*
*** now to use the commands simply prepare the command record
*** and run the execute commands function 
*)

// Learn more about F# at http://docs.microsoft.com/dotnet/fsharp

open System
open System.Collections
open System.Data
open MySql.Data.MySqlClient
open helpers


type product = {id:int; name:string; price:int}

[<EntryPoint>]
let main argv =
    let connectionString = 
        "server=localhost;database=testdb;uid=root;pwd=P@ssw0rd"
    
    let getColumnsFromQuery (query:Query) =
        query.Columns
        |> Seq.map(fun c -> c.ColumnName)
        |> Seq.toArray

    let getCommand (query:Query) (commandText:String):MySqlCommand = 
        let command = new MySqlCommand(commandText)     
        getColumnsFromQuery query
        |> fun arr -> 
            if not (arr |> Array.contains query.IdColumn) then 
                Array.append arr [|query.IdColumn|]
            else arr
        |> Array.map(fun c -> new MySqlParameter(c, null))
        |> command.Parameters.AddRange
        command

    let getCommandParameters (command:MySqlCommand) =
        ArrayList.Adapter(command.Parameters).ToArray()
        |> Array.map(fun p -> p :?> MySqlParameter)

    let setParametersValues (query:Query) (parameters:MySqlParameter[]) = 
        parameters 
        |> Array.iter(fun parameter ->
            query.Columns
            |> List.find(fun c -> c.ColumnName = parameter.ParameterName)
            |> fun c -> parameter.Value <- c.Value)                

    let matchQuery query = 
        match query with 
        | Select -> (|Select|)(query) |> getCommand query
        | Insert -> (|Insert|)(query) |> getCommand query
        | Update -> (|Update|)(query) |> getCommand query
        | Delete -> (|Delete|)(query) |> getCommand query
    
    let executeCommands queries connectionString =
        let mutable result = 0
        using(new MySqlConnection(connectionString))
            (fun connection -> 
                connection.Open()
                for query in queries do
                    matchQuery query
                    |> fun command -> 
                        getCommandParameters command
                        |> setParametersValues query
                        
                        command.Connection <- connection
                        result <- command.ExecuteNonQuery()
                connection.Close())
        result
 
    let insertProductQuery = { Type=StatementType.Insert; TableName="products"; Columns=[{ ColumnName="id"; Value="631"}; {ColumnName="name"; Value="RaspberryPi"}; {ColumnName="price"; Value="100"}]; IdColumn="id"}
    let deleteProductQuery = { Type=StatementType.Delete; TableName="products"; Columns=[{ ColumnName="id"; Value="630"}; {ColumnName="name"; Value="Jetson Nano"}; {ColumnName="price"; Value="100"}]; IdColumn="id"}
    let updateProductQuery = { Type=StatementType.Update; TableName="products"; Columns=[{ ColumnName="id"; Value="629"}; {ColumnName="name"; Value="Edison"}; {ColumnName="price"; Value="100"}]; IdColumn="id"}

    executeCommands [insertMenuQuery; updateMenuQuery; deleteMenuQuery] connectionString
    |> printfn "records affected: %d" 

    0