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. 

executescalar() in f#

Step 2: We go to Solution Explorer and Right Click on References. 

executescalar() in f#

Step 3: Click on Add References. Then a pop-up window with caption Add Reference will open as in the below figure.

executescalar() in f#

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.

 executescalar() in f#

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. 

executescalar() in f#

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:

executescalar() in f#

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

executescalar() in f#

Up Next
    Ebook Download
    View all
    Learn
    View all