CODEWITHSUNDEEP
sqlmysqldatabasef#
Alexander Andersen
Alexander Andersen

Reputation: 33

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 = "localhost"
    let username = "username"
    let password = "password"
    \\ Code that connects to db
    \\ Print error message if can connect 

    \\ Query 
    let query = "SELECT * FROM table ..."
    \\ Code that executes query 
    \\ Error Message if query not executed

Upvotes: 3

Views: 1085

Answers (2)

Tuomas Hietanen
Tuomas Hietanen

Reputation: 5323

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"

let ExecuteSqlAsync (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 -> ()
  }

let ExecuteSql (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 -> ()

Upvotes: 1

s952163
s952163

Reputation: 6324

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

Upvotes: 3

Related Questions