Last active
March 29, 2021 21:36
-
-
Save mouadcherkaoui/65c9640ca400e8723c4a31a7ae011046 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
(* | |
*** 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment