Data Retrieval From Database In F#

Introduction

This article explains how to retrieve data from the database in a F# Windows Forms Application.

Create Table In Database

The following will create a table in the database:

CREATE TABLE [dbo].[UserInformation](

      [UserId] [int] IDENTITY(1,1) NOT NULL,

      [UserName] [varchar](30) NULL,

      [FirstName] [varchar](30) NULL,

      [LastName] [varchar](30) NULL,

      [Address] [nvarchar](max) NULL,

      [City] [varchar](30) NULL

      )

 

Insert values into the table fields:

insert into userinformation values ('Pank','Pankaj','Lohani','a-43 santnagar','Delhi')

insert into userinformation values ('Nicks','Nimit','Joshi','Vinod Nagar','Delhi')

insert into userinformation values ('pr','pravesh','khanduri','pratap vihar','Delhi')

insert into userinformation values ('Nicks','Nimit','Joshi','Vinod Nagar','Delhi')

insert into userinformation values ('Ammu','Amit','Senwal','East Vinod Nagar','Delhi')

output-database.jpg

Now let's use the following procedure.

Step 1:

Open Visual Studio and click on "Create New Project" then select "F# Console Application".

create application.jpg

Step 2:

Now go the Solution Explorer on to the right side of the application. Select "References" and right-click on it and select "Add references".

select-references.jpg


add-references.jpg

Step 3:

After selecting "Add References", in the framwork template you need to select "System.Windows.Forms", "System.Drawing" and "System.Data" while holding down the Ctrl key and click "Ok." 

import-namespaces.jpg

Step 4:

Write the code for the F# applcation as in the following:

// Learn more about F# at http://fsharp.net

// See the 'F# Tutorial' project for more help.

//Adding NameSpace

 open System

 open System.Windows.Forms

 open System.Data.SqlClient 

 open System.Drawing

 open System.Data

 //Connection String

 let constring = @"Data Source=MCNDESKTOP20;Initial Catalog=information;User ID=sa; Password=password@123"

 let con = new SqlConnection(constring)

 let com1 = new SqlCommand() //creating the command object

 //Creating Window Form

 let form = new Form()

 //Creating usercontrols on forms

 let lbluserId      = new Label(Top = 10, Left = 0, Height = 20)

 let txtuserId      = new TextBox(Top =10, Left = 100,Height = 20)

 let lblusername    = new Label(Top = 50, Left = 0,Height = 20)

 let lbluserfname   = new Label(Top = 90, Left = 0,Height = 20)

 let lbluserlname   = new Label(Top = 130, Left = 0,Height = 20)

 let lbluseraddress = new Label(Top = 170, Left = 0, Height = 20)

 let lblusercity    = new Label(Top = 200, Left = 0, Height = 20)

 let lblname        = new Label(Top = 50, Left = 100,Height = 20)

 let lblfname       = new Label(Top = 90, Left = 100,Height = 20)

 let lbllname       = new Label(Top = 130, Left = 100, Height = 20)

 let lbladdress     = new Label(Top = 170, Left = 100, Height = 20)

 let lblcity        = new Label(Top = 200, Left = 100, Height = 20)

 let btn1 = new Button(Top = 280, Left=80)

 //Adding Controls()

 form.Controls.Add(lbluserId)

 form.Controls.Add(txtuserId)

 form.Controls.Add(lblusername)

 form.Controls.Add(lblname)

 form.Controls.Add(lbluserfname)

 form.Controls.Add(lblfname)

 form.Controls.Add(lbluserlname)

 form.Controls.Add(lbllname)

 form.Controls.Add(lbluseraddress)

 form.Controls.Add(lbladdress)

 form.Controls.Add(lblusercity)

 form.Controls.Add(lblcity)

 form.Controls.Add(btn1)

 form.Text <- "Searching The Records"

 btn1.Text <-"Search"

 lbluserId.Text <- "Enter UserId :"

 lblusername.Text <- "UserName:"

 lbluserfname.Text <- "FirstName :"

 lbluserlname.Text <- "LastName :"

 lbluseraddress.Text <- "Address :"

 lblusercity.Text <- "City :"

 

 btn1.Click.Add(fun _->

 con.Open()

 com1.Connection <- con

 com1.CommandText <- " select * from UserInformation where UserId = "+txtuserId.Text+" "

 let dr = com1.ExecuteReader()

 while dr.Read() do

 lblname.Text <- dr.Item(1).ToString()

 lblfname.Text <- dr.Item(2).ToString()

 lbllname.Text <- dr.Item(3).ToString()

 lbladdress.Text <- dr.Item(4).ToString()

 lblcity.Text <- dr.Item(5).ToString())|>ignore

 Application.Run(form)// Run the application

 

Step 5:

Debug the application by pressing F5 and the result will be shown in the application as given below.

after-debug.jpg

Step 6:

Now you need to enter the user id because the user id is defined as a primary key in the table that finds the records uniquely from the database.

EnterUserId.jpg

Output

record-search.jpg