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.