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:
  1. Create Database Employee
  2. use Employee
  3. create table EmployeeSalary
  4. (
  5. EmpId int primary key,
  6. EmpName varchar(max),
  7. Salary money
  8. )
Insert Values in Field Columns
Insert values into the field columns as in the following:
  1. insert into EmployeeSalary values(101,'Pankaj',20000)
  2. insert into EmployeeSalary values(102,'Nimit',15000)
  3. insert into EmployeeSalary values(103,'Pravesh',16000)
  4. insert into EmployeeSalary values(104,'Amit',12000)
  5. insert into EmployeeSalary values(105,'Ravi',25000)
  6. insert into EmployeeSalary values(106,'Ainul',30000)
Write the query to execute the table schema as in the following:
  1. select * from EmployeeSalary
DatabaseColumnValues

Create Stored Procedure for Highest Salary
Create a Stored Procedure for the highest salary using the following:
  1. Create proc Hsalary
  2. as
  3. begin
  4. select max (Salary) from EmployeeSalary
  5. end
Create Stored Procedure for Second Highest Salary
Create a Stored Procedure for the second highest salary using the following:
  1. Create proc SHSalary
  2. as begin
  3. select max(Salary) from EmployeeSalary where salary not in (select max(Salary) from EmployeeSalary)
  4. end
Create Stored Procedure for Third Highest Salary
Create a Stored Procedure for the third highest salary using the following:
  1. Create proc THSalary
  2. as begin
  3. SELECT Salary
  4. FROM (
  5. SELECT Salary, ROW_NUMBER() OVER(ORDER BY Salary DESC) AS sal
  6. FROM EmployeeSalary) AS E
  7. WHERE sal = 3;
  8. end
Create Stored Procedure for Lowest Salary
Create a Stored Procedure for the lowest salary using the following:
  1. Create proc LWSalary
  2. as begin
  3. select min(Salary) from EmployeeSalary
  4. 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.
CreateApplication
Step 2
Now go to the Solution Explorer on the right side of the application. Right-click on "References" and select "Add references".
SelectReferences
AddReferences
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."
ImportNamespaces
Step 4
Use the following code for getting the salary from the database in F#.
  1. open System
  2. open System.Windows.Forms
  3. open System.Data
  4. open System.Data.SqlClient
  5. open System.Drawing
  6. let salaryform=new Form(Text="Binding in DataTable")
  7. salaryform.BackColor<-Color.BlanchedAlmond
  8. let ffont=new Font("Arial", 9.75F,FontStyle.Regular, GraphicsUnit.Point)
  9. let constring = @"Data Source=MCNDESKTOP34;Initial Catalog=Employee;User ID=; Password="
  10. let adap=new SqlDataAdapter("select * from EmployeeSalary",constring)
  11. let dt=new DataTable()
  12. adap.Fill(dt) |>ignore
  13. let datagrid=new DataGridView(Top=20,Left=0,Width=330,Height=200)
  14. datagrid.DataSource<-dt
  15. let Hsalarylbl=new Label(Top=350,Left=0,Width=120)
  16. Hsalarylbl.Text<-"Higest Salary:"
  17. let HigestSalary=new Label(Top=350,Left=140,BorderStyle=BorderStyle.FixedSingle)
  18. let SHsalarylbl=new Label(Top=380,Left=0,Width=120)
  19. SHsalarylbl.Text<-"Second Higest Salary:"
  20. let SecondHigestSalary=new Label(Top=380,Left=140,BorderStyle=BorderStyle.FixedSingle)
  21. let THsalarylbl=new Label(Top=410,Left=0,Width=120)
  22. THsalarylbl.Text<-"Third Higest Salary:"
  23. let ThirdHigestSalary=new Label(Top=410,Left=140,BorderStyle=BorderStyle.FixedSingle)
  24. let Lowestsalarylbl=new Label(Top=440,Left=0,Width=120)
  25. Lowestsalarylbl.Text<-"Lowest Salary:"
  26. let LowestSalary=new Label(Top=440,Left=140,BorderStyle=BorderStyle.FixedSingle)
  27. let findbtn=new Button(Top=290,Left=80)
  28. findbtn.Text<-"Show"
  29. findbtn.BackColor<-Color.Ivory
  30. salaryform.Controls.Add(datagrid)
  31. salaryform.Controls.Add(Hsalarylbl)
  32. salaryform.Controls.Add(SHsalarylbl)
  33. salaryform.Controls.Add(THsalarylbl)
  34. salaryform.Controls.Add(HigestSalary)
  35. salaryform.Controls.Add(Lowestsalarylbl)
  36. salaryform.Controls.Add(SecondHigestSalary)
  37. salaryform.Controls.Add(ThirdHigestSalary)
  38. salaryform.Controls.Add(LowestSalary)
  39. salaryform.Controls.Add(findbtn)
  40. findbtn.Click.Add(fun _->
  41. let con = new SqlConnection(constring)
  42. //open connection
  43. con.Open()
  44. //FOR HIGEST SALARY
  45. let com = new SqlCommand("Hsalary",con)
  46. com.Connection <- con
  47. com.CommandType <- CommandType.StoredProcedure
  48. com.CommandText <- "Hsalary"
  49. HigestSalary.Text<-com.ExecuteScalar().ToString()
  50. //FOR SECOND HIGHEST SALARY
  51. let com = new SqlCommand("SHSalary",con)
  52. com.Connection <- con
  53. com.CommandType <- CommandType.StoredProcedure
  54. com.CommandText <- "SHSalary"
  55. SecondHigestSalary.Text<-com.ExecuteScalar().ToString()
  56. //FOR THIRD HIGHEST SALARY
  57. let com = new SqlCommand("THSalary",con)
  58. com.Connection <- con
  59. com.CommandType <- CommandType.StoredProcedure
  60. com.CommandText <- "THSalary"
  61. ThirdHigestSalary.Text<-com.ExecuteScalar().ToString()
  62. //FOR LOWEST SALARY
  63. let com = new SqlCommand("LWSalary",con)
  64. com.Connection <- con
  65. com.CommandType <- CommandType.StoredProcedure
  66. com.CommandText <- "LWSalary"
  67. LowestSalary.Text<-com.ExecuteScalar().ToString())
  68. 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
AfterDebug
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.
ClickOnShowButton
Output
ShowSalaries
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.