Post
Ask Question

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:
  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.


Similar Articles