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.
Step 2: We go to Solution Explorer and
Right Click on References.
Step 3: Click on Add References. Then a
pop-up window with caption Add Reference will open as in the below figure.
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.
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.
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:
Click on Button. It will show the maximum salary
from Table.