Executing DML Commands in F#

Introduction:

At first, we create a Database. Here I am using SQL Server. Write the following SQL statements to create a Database as EMP and Database table as EMP_DETAIL with four columns as ID, F_Name, L_Name, Salary.

CREATE DATABASE EMP

USE EMP

CREATE TABLE EMP_DETAIL
( ID INT,
  F_Name VARCHAR(20),
  L_Name VARCHAR(20),
  Salary VARCHAR(10)
 )

Step 1: Create a F# application.

Step 2: Go to Solution Explorer and Right Click on References. 

Step 3: Click on Add References. Then a pop-up window with caption Add Reference will open as in the below figure.

Step 4: Click on .NET in the Add Reference window and select System.Windows.Forms, System.Drawing and System.Data with holding down Ctrl key and Click on Ok.

Step 5: Write the following F# code.

// Learn more about F# at http://fsharp.net
//------------------------- Importing Namespace

open System
open System.Windows.Forms
open System.Drawing
open System.Data.SqlClient
open System.Data

//------------------------- Creating Form & User Controls
//   Form

let frm = new Form(Height =400, Width = 500)
frm.BackColor <- Color.LightGreen

//   Labels

let lbl1 = new Label(Top =20 , Height =30, Width = 50)
let lbl2 = new Label(Top =50 , Height =30, Width = 100)
let lbl3 = new Label(Top =80 , Height =30, Width = 100)
let lbl4 = new Label(Top =110 , Height =30, Width = 50)

//   TextBoxes

let txt1 = new TextBox(Top =20, Left =120)
et txt2 = new TextBox(Top =50, Left =120)
let txt3 = new TextBox(Top =80, Left =120)
let txt4 = new TextBox(Top =110,Left=120)
lbl1.Text <- "ID"
lbl2.Text <- "First Name"
lbl3.Text <- "Last Name"
lbl4.Text <- "Salary"

//   Buttons

let btn1 = new Button(Top =150, Left =40, Height =30, Width =50)
let btn2 = new Button(Top =150, Left =100, Height =30, Width =50)
let btn3 = new Button(Top =150, Left =160, Height =30, Width =50)
let btn4 = new Button(Top =150, Left =220, Height =30, Width =50)
let btn5 = new Button(Top =150, Left =280, Height =30, Width =50)
btn1.BackColor <- Color.LightGray
btn1.Text <- "New"
btn2.BackColor <- Color.LightGray
btn2.Text <- "Save"
btn3.BackColor <- Color.LightGray
btn3.Text <- "Update"
btn4.BackColor <- Color.LightGray
btn4.Text <- "Search"
btn5.BackColor <- Color.LightGray
btn5.Text <- "Delete"

//-------------  Adding Controls to Form 

frm.Controls.Add(lbl1)
frm.Controls.Add(lbl2)
frm.Controls.Add(lbl3)
frm.Controls.Add(lbl4)
frm.Controls.Add(txt1)
frm.Controls.Add(txt2)
frm.Controls.Add(txt3)
frm.Controls.Add(txt4)
frm.Controls.Add(btn1)
frm.Controls.Add(btn2)
frm.Controls.Add(btn3)
frm.Controls.Add(btn4)
frm.Controls.Add(btn5)
 
//----- Connection String

let constr = @"Data Source=SERVER_NAME;Initial Catalog=EMP;Integrated Security=True"
let con = new SqlConnection(constr)
let com1 = new SqlCommand()
let com2 = new SqlCommand()
let com3 = new SqlCommand()
let com4 = new SqlCommand()
let ds = new DataSet()
 
//------------  Clear the TextBoxes

btn1.Click.Add(fun _->
txt1.Clear()
txt2.Clear()
txt3.Clear()
txt4.Clear()
txt1.Focus()|>ignore
)
 
//-------- Insert record into DataBase

btn2.Click.Add(fun _->
con.Open()
com1.Connection <- con
com1.CommandText <- "insert into emp_detail values"+txt1.Text+",'"+txt2.Text+"','"+txt3.Text+"','"+txt4.Text+"')"
MessageBox.Show("Recod Saved")|>ignore
com1.ExecuteNonQuery()|> ignore
con.Close()
)

//--------- Update Record

btn3.Click.Add(fun _->
con.Open()
com2.Connection <- con
com2.CommandText <- "update emp_detail set F_Name = '"+txt2.Text+"', L_Name = '"+txt3.Text+"', Salary =
'"+txt4.Text+"' where ID = "+txt1.Text+" "
MessageBox.Show("Recod Updated")|>ignore
com2.ExecuteNonQuery()|> ignore
con.Close()
)

//------ Search Record

btn4.Click.Add(fun _->
con.Open()
com3.Connection <- con
com3.CommandText <- " select * from emp_detail where ID = "+txt1.Text+" "
let dr = com3.ExecuteReader()
while dr.Read() do
 txt2.Text <- dr.Item(1).ToString()
 txt3.Text <- dr.Item(2).ToString()
 txt4.Text <- dr.Item(3).ToString())|>ignore

//----- Delete Record

btn5.Click.Add(fun _->
con.Open()
com4.Connection <- con
com4.CommandText <- "delete from emp_detail where ID = "+txt1.Text+" "
MessageBox.Show("Recod Deleted")|>ignore
com4.ExecuteNonQuery()|> ignore
con.Close()
)
Application.Run(frm)

Step 6: Run the application. The output looks like the below figure



Now, we perform different operation by clicking on buttons.

Saving Record: Fill the form and Click the save button. A Message Box will be displayed to show that Record is saved with message "Record Saved".

Output:



Searching Record: Write the ID of employee and Click the search button. The record related to ID will appear in TextBoxes. Like the below figure

Output: 



Here I have written 1 as ID. Click the Search Button.



Updating Record: You can update the record by Clicking the Update Button.

Output:



I am changing the Last name of employee whose ID is 1. Click the Update Button.



Deleting Record: To delete a record, write the ID of employee which is to be deleted and Click the Delete Button.

Output:

The Button New only clears the contents of TextBoxes.