F# Connect To Online Mysql Db Execute Query

I am making a F# project and need to do some database queries to an online mysql db. Can anyone please help me. I need something like this \\ Connect to DB let servername

Solution 1:

You should install the .NET driver for MySQL. Then install the SQLprovider. There are samples for MySQL in the docs. You would connect to the db and query it like this:

type sql = SqlDataProvider<
                dbVendor,
                connString,
                ResolutionPath = resPath,
                IndividualsAmount = indivAmount,
                UseOptionTypes = useOptTypes,
                Owner = "HR"
            >
let ctx = sql.GetDataContext()

let employees = 
    ctx.Hr.Employees 
    |> Seq.map (fun e -> e.ColumnValues |> Seq.toList)
    |> Seq.toList

connstring will be something like this:

[<Literal>]
let connString  = "Server=localhost;Database=HR;User=root;Password=password"

You should also read https://msdn.microsoft.com/visualfsharpdocs/conceptual/walkthrough-accessing-a-sql-database-by-using-type-providers-%5bfsharp%5d

Solution 2:

I would also say SQLProvider is the way to go as then you have a validation of your logics against your database and you notice if your database changes.

But you can connect manually if you want to:

// Reference Nuget package MySql.Data//#r @"./../packages/MySql.Data/lib/net40/MySql.Data.dll"

open System
open MySql.Data.MySqlClient

let cstr = "server = localhost; database = myDatabase; uid = username;pwd = password"letExecuteSqlAsync (query : string) parameters =
  use rawSqlConnection = new MySqlConnection(cstr)
  async {
    do! rawSqlConnection.OpenAsync() |> Async.AwaitIAsyncResult |> Async.Ignore
    use command = new MySqlCommand(query, rawSqlConnection)
    parameters |> List.iter(fun (par:string*string) -> command.Parameters.AddWithValue(par) |> ignore)
    let! affectedRows = command.ExecuteNonQueryAsync() |> Async.AwaitTask
    match affectedRows with
    | 0 -> "ExecuteSql 0 rows affected: " + query |> Console.WriteLine
           ()
    | x -> ()
  }

letExecuteSql (query : string) parameters =
    use rawSqlConnection = new MySqlConnection(cstr)
    rawSqlConnection.Open()
    use command = new MySqlCommand(query, rawSqlConnection)
    parameters |> List.iter(fun (par:string*string) -> command.Parameters.AddWithValue(par) |> ignore)
    let affectedRows = command.ExecuteNonQuery()
    match affectedRows with
    | 0 -> "ExecuteSql 0 rows affected: " + query |> Console.WriteLine
           ()
    | x -> ()