Getting Value By ExecuteScalar() Method in FSharp

Introduction:

ExecuteScalar() is a  method of the Command Class in .NET. It execute sqlcommand and returns a single value from DataBase. Generally we use ExecuteScalar() with aggregate function. Here we will use ExecuteScalar() method in F# application. At first we create DataBase. Here I am using SqlServer DataBase.

Creating the DataBase: In this example, the DataBase name is EMP and EMP_DATAIL is Table name. It has four columns as ID, F_Name, L_Name and Salary. Write the following SQL command to create DataBase and Table and inserting records in Table.

create database EMP

use EMP

create table EMP_DETAIL
 ( ID int primary key,
   F_Name varchar(20),
   L_Name varchar(20),
   Salary varchar(6)
 )

 insert into EMP_DETAIL values(1,'Alok','Pandey','10000')
 insert into EMP_DETAIL values(2,'Satish','Kumar','30000')
 insert into EMP_DETAIL values(3,'Amitabh','Pandey','60000')
 insert into EMP_DETAIL values(4,'Pramod','Sharma','50000')

Creating a F# Application:

Now we create a F# Application. Follow given steps,

Step 1: Create a F# application. 

Step 2: We 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 at .Net on Add Reference window and select System.Windows.Forms, System.Drawing and System.Data with holding down Ctrl key and Click on Ok.

 

Step 5: Now, we write code for getting maximum ID from DataBase Table EMP_DETAIL. Write the below F# code in the Program.fs file.

//namespace
open System
open System.Windows.Forms
open System.Data.SqlClient
open System.Drawing
open System.Data
 
//  cteating connection string
let constring = @"Data Source=SERVER_NAME;Initial Catalog=EMP;Integrated Security=True"
//Creating user controls
let form = new Form()
let txt = new TextBox(Top = 20)
let lbl = new Label()
 
lbl.Text <- "MAX ID :"
form.Controls.Add(txt)
form.Controls.Add(lbl)
//Creating SqlConnection
let con = new SqlConnection(constring)
//Creating SqlCommand
let com = new SqlCommand()
//open connection
con.Open()
com.Connection <- con
com.CommandText <- "select max(ID) from EMP_DETAIL"
txt.Text <- com.ExecuteScalar().ToString()
Application.Run(form)

Step 6: Run the application. 



Now, suppose we want to get maximum salary of employee from EMP_DETAIL Table on Button Click event. Then write the following F# code.

//  cteating connection string
let constring = @"Data Source=SERVER_NAME;Initial Catalog=EMP;Integrated Security=True"

//Creating user controls
let form = new Form()
let txt = new TextBox(Top = 20)
let lbl = new Label()
let btn = new Button(Top = 50, Width = 40 , Height = 20)
lbl.Text <- "MAX Salary :"
btn.BackColor <- Color.Pink
btn.Text <- "Click"
form.Controls.Add(txt)
form.Controls.Add(lbl)
form.Controls.Add(btn)
 
//Creating SqlConnection
let con = new SqlConnection(constring)
//Creating SqlCommand
let com = new SqlCommand()
//open connection
con.Open()
com.Connection <- con
com.CommandText <- "select max(salary) from EMP_DETAIL"
 
btn.Click.Add(fun a ->
txt.Text <- com.ExecuteScalar().ToString())
 
Application.Run(form)

Output:

Click on Button. It will show the maximum salary from Table.