Use ExecuteScalar Function in F#

Introduction

This article explains the "ExecuteScalar( )" function of the SqlCommand class and how to use this function in a Windows Forms application.

ExecuteScalar( )

The ExecuteScalar function is part of the SqlCommand class. The ExecuteScalar method retrieves a single value from the database. The ExecuteScalar function executes the query and returns the first column of the first row in the resultset returned by the query. The ExecuteScalar function is used with the aggregate functions, such as Max, Min, Count, Sum, AVG and so on.

Create Database and Table

Create the database and table as in the following:

Create Database Employee
use Employee
create table EmployeeSalary
(
EmpId int primary key,
EmpName varchar(max),
Salary money
)

Insert Values in Field Columns

Insert values into the field columns as in the following:

insert into EmployeeSalary values(101,'Pankaj',20000)
insert into EmployeeSalary values(102,'Nimit',15000)
insert into EmployeeSalary values(103,'Pravesh',16000)
insert into EmployeeSalary values(104,'Amit',12000)
insert into EmployeeSalary values(105,'Ravi',25000)
insert into EmployeeSalary values(106,'Ainul',30000)

Write the query to execute the table schema as in the following:

select * from EmployeeSalary

Create Stored Procedure for Highest Salary

Create a Stored Procedure for the highest salary using the following:

Create proc Hsalary
as
begin
select max (Salary) from EmployeeSalary
end

Create Stored Procedure for Second Highest Salary

Create a Stored Procedure for the second highest salary using the following:

Create proc SHSalary
as begin
select max(Salary) from EmployeeSalary where salary not in (select max(Salary) from EmployeeSalary)
end

Create Stored Procedure for Third Highest Salary

Create a Stored Procedure for the third highest salary using the following:

Create proc THSalary
as begin
SELECT Salary
FROM (
SELECT Salary, ROW_NUMBER() OVER(ORDER BY Salary DESC) AS sal
FROM EmployeeSalary) AS E
WHERE sal = 3;
end

Create Stored Procedure for Lowest Salary

Create a Stored Procedure for the lowest salary using the following:

Create proc LWSalary
as begin
select min(Salary) from EmployeeSalary
end

Now I will show you how to display the highest salary, second highest salary, third highest salary and lowest salary from the database in a Windows Forms application. Let's use the following procedure.

Step 1

Open Visual Studio then select "Create New Project" --> F# Console Application.

Step 2

Now go to the Solution Explorer on the right side of the application. Right-click on "References" and select "Add references".

Step 3

After selecting "Add References", in the framework template you need to select "System.Windows.Forms", "System.Drawing" and "System.Data" while holding down the Ctrl key and click on "Ok."

Step 4

Use the following code for getting the salary from the database in F#.

open System
open System.Windows.Forms
open System.Data
open System.Data.SqlClient
open System.Drawing
let salaryform=new Form(Text="Binding in DataTable")
salaryform.BackColor<-Color.BlanchedAlmond
let ffont=new Font("Arial", 9.75F,FontStyle.Regular, GraphicsUnit.Point)
let constring = @"Data Source=MCNDESKTOP34;Initial Catalog=Employee;User ID=; Password="
let adap=new SqlDataAdapter("select * from EmployeeSalary",constring)
let dt=new DataTable()
adap.Fill(dt) |>ignore
let datagrid=new DataGridView(Top=20,Left=0,Width=330,Height=200)
datagrid.DataSource<-dt
let Hsalarylbl=new Label(Top=350,Left=0,Width=120)
Hsalarylbl.Text<-"Higest Salary:"
let HigestSalary=new Label(Top=350,Left=140,BorderStyle=BorderStyle.FixedSingle)
let SHsalarylbl=new Label(Top=380,Left=0,Width=120)
SHsalarylbl.Text<-"Second Higest Salary:"
let SecondHigestSalary=new Label(Top=380,Left=140,BorderStyle=BorderStyle.FixedSingle)
let THsalarylbl=new Label(Top=410,Left=0,Width=120)
THsalarylbl.Text<-"Third Higest Salary:"
let ThirdHigestSalary=new Label(Top=410,Left=140,BorderStyle=BorderStyle.FixedSingle)
let Lowestsalarylbl=new Label(Top=440,Left=0,Width=120)
Lowestsalarylbl.Text<-"Lowest Salary:"
let LowestSalary=new Label(Top=440,Left=140,BorderStyle=BorderStyle.FixedSingle)
let findbtn=new Button(Top=290,Left=80)
findbtn.Text<-"Show"
findbtn.BackColor<-Color.Ivory
salaryform.Controls.Add(datagrid)
salaryform.Controls.Add(Hsalarylbl)
salaryform.Controls.Add(SHsalarylbl)
salaryform.Controls.Add(THsalarylbl)
salaryform.Controls.Add(HigestSalary)
salaryform.Controls.Add(Lowestsalarylbl)
salaryform.Controls.Add(SecondHigestSalary)
salaryform.Controls.Add(ThirdHigestSalary)
salaryform.Controls.Add(LowestSalary)
salaryform.Controls.Add(findbtn)
findbtn.Click.Add(fun _->
let con = new SqlConnection(constring)
//open connection
con.Open()
//FOR HIGEST SALARY
let com = new SqlCommand("Hsalary",con)
com.Connection <- con
com.CommandType <- CommandType.StoredProcedure
com.CommandText <- "Hsalary"
HigestSalary.Text<-com.ExecuteScalar().ToString()
//FOR SECOND HIGHEST SALARY
let com = new SqlCommand("SHSalary",con)
com.Connection <- con
com.CommandType <- CommandType.StoredProcedure
com.CommandText <- "SHSalary"
SecondHigestSalary.Text<-com.ExecuteScalar().ToString()
//FOR THIRD HIGHEST SALARY
let com = new SqlCommand("THSalary",con)
com.Connection <- con
com.CommandType <- CommandType.StoredProcedure
com.CommandText <- "THSalary"
ThirdHigestSalary.Text<-com.ExecuteScalar().ToString()
//FOR LOWEST SALARY
let com = new SqlCommand("LWSalary",con)
com.Connection <- con
com.CommandType <- CommandType.StoredProcedure
com.CommandText <- "LWSalary"
LowestSalary.Text<-com.ExecuteScalar().ToString())
Application.Run(salaryform)

Step 5

Debug the application by pressing F5 to execute the Windows Forms application. After debugging the application the output will be as in the following figure

Step 6

Now click on the Show button to display the Higest Salary, Second Highest Salary , Third Highest Salary and Lowest Salary from the table as shown in the following figure.

Output

Summary

This article has explained the ExecuteScalar function and how to create a Stored Procedure for getting the scalar value of the highest salary, second highest salary, third highest salary and lowest salary from the datatable in a Windows Forms application.

Use ExecuteScalar Function in F#

Reactions

Comments