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.

step1.gif

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

step2.gif

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

step3.gif

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.

step4.gif

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+
"')"
M
essageBox.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

output.gif

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:

save.gif

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: 

search1.gif

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

search2.gif

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

Output:

update1.gif

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

update2.gif

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

Output:

delete.gif

The Button New only clears the contents of TextBoxes.