Binding Navigation Manually From Database in F#

Introduction

In my previous article Use Navigational Buttons you saw the use of BindingNavigator but now in this article you will see how to bind database records in a Windows Forms form without using a BindingNavigator class. We will bind the database records in the controls manually.

Write the following procedure for creating the database and a table in SQL Server:

Create Datbase Employee

use Employee

create table EmployeeInformation

(

EmpId int,

Emp_Name varchar(max),

Emp_Address nvarchar(max),

Emp_Department varchar(max)

)

Write the following procedure for inserting the values in the table columns:

insert into EmployeeInformation values(1,'Pankaj','SantNagar','Web Developer')

insert into EmployeeInformation values(2,'Pravesh','Pratap Vihar','Teacher')

insert into EmployeeInformation values(3,'Nimit','Vinod Nagar','Software Engineer')

insert into EmployeeInformation values(3,'Ravi','Vinod Nagar','Software Engineer')

Write the query to execute the table schema:

select * from EmployeeInformation

TableSchema

Now I will show you how to bind the database table values in a user control without using a BindingNavigator. Use the following procedure to do that.

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

Write the following code for binding the data values from the data source to the Windows Forms form.

Step 4:

open System

open System.Windows.Forms

open System.Data

open System.Data.SqlClient

open System.Drawing

//establish the connection with sql-server

let constring = @"Data Source=MCNDESKTOP34;Initial Catalog=Employee;User Id=sa; Password=mcn@123"

let con = new SqlConnection(constring)

let dataadpter=new SqlDataAdapter("Select * from EmployeeInformation", con)

let ds = new DataSet()

dataadpter.Fill(ds,"EmployeeInformation")|>ignore

//Create form

let recordform = new Form(Text="Display Records")

//create instances of labels

let empidlbl=new Label(Text="Employee Id:",Location=new System.Drawing.Point(0, 10),AutoSize=true)

let empnamelbl=new Label(Text="Emp Name:",Location=new System.Drawing.Point(0, 50),AutoSize=true)

let empaddlbl=new Label(Text="Emp Address:",Location=new System.Drawing.Point(0,100),AutoSize=true)

let empdeptlbl=new Label(Text="Emp Dept:",Location=new System.Drawing.Point(0,150),AutoSize=true)

let IdLabel=new Label(Location=new System.Drawing.Point(140,10),BorderStyle=BorderStyle.FixedSingle,Width=120)

let NameLabel=new Label(Location=new System.Drawing.Point(100,50),BorderStyle=BorderStyle.FixedSingle,Width=120)

let AddressLabel=new Label(Location=new System.Drawing.Point(100,100),BorderStyle=BorderStyle.FixedSingle,Width=120)

let DeptLabel=new Label(Location=new System.Drawing.Point(100,150),BorderStyle=BorderStyle.FixedSingle,Width=120)

//create buttons

let topbutton=new Button(Text="Top", Location=new System.Drawing.Point(40, 190))

let bottombutton=new Button(Text="Bottom", Location=new System.Drawing.Point(120, 190))

let exitbutton=new Button(Text="Exit", Location=new System.Drawing.Point(200, 190))

//assign the font

let ffont=new Font("Arial", 9.75F,FontStyle.Regular, GraphicsUnit.Point)

recordform.Font<-ffont

//adding the controls in form

recordform.Controls.Add(exitbutton)

recordform.Controls.Add(empidlbl)

recordform.Controls.Add(empnamelbl)

recordform.Controls.Add(empaddlbl)

recordform.Controls.Add(empdeptlbl)

recordform.Controls.Add(IdLabel)

recordform.Controls.Add(NameLabel)

recordform.Controls.Add(AddressLabel)

recordform.Controls.Add(DeptLabel)

recordform.Controls.Add(topbutton)

recordform.Controls.Add(bottombutton)

//binds the database records in label controls, you can also use commented code.

//IdLabel.Text<-Convert.ToString(ds.Tables.["EmployeeInformation"].Rows.Item(0).Item(0))

//NameLabel.Text<-Convert.ToString(ds.Tables.["EmployeeInformation"].Rows.Item(0).Item(1))

//AddressLabel.Text<-Convert.ToString(ds.Tables.["EmployeeInformation"].Rows.Item(0).Item(2))

//DeptLabel.Text<-Convert.ToString(ds.Tables.["EmployeeInformation"].Rows.Item(0).Item(3))

//click on top button

topbutton.Click.Add(fun top->

IdLabel.Text<-Convert.ToString(ds.Tables.["EmployeeInformation"].Rows.Item(0).Item(0))

NameLabel.Text<-Convert.ToString(ds.Tables.["EmployeeInformation"].Rows.Item(0).Item(1))

AddressLabel.Text<-Convert.ToString(ds.Tables.["EmployeeInformation"].Rows.Item(0).Item(2))

DeptLabel.Text<-Convert.ToString(ds.Tables.["EmployeeInformation"].Rows.Item(0).Item(3)))

//click on botoom button

bottombutton.Click.Add(fun bottom->

IdLabel.Text<-Convert.ToString(ds.Tables.["EmployeeInformation"].Rows.Item(3).Item(0))

NameLabel.Text<-Convert.ToString(ds.Tables.["EmployeeInformation"].Rows.Item(3).Item(1))

AddressLabel.Text<-Convert.ToString(ds.Tables.["EmployeeInformation"].Rows.Item(3).Item(2))

DeptLabel.Text<-Convert.ToString(ds.Tables.["EmployeeInformation"].Rows.Item(3).Item(3)))

exitbutton.Click.Add(fun exit->

recordform.Close()

con.Close())

recordform.Show()

Application.Run(recordform)

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 "Top" button. When you will click on the "Top" button the first row of the table is displayed in the controls as in the figure below.

TopButton

Step 7 :

Now click on the "Bottom" button. When you will click on the "Bottom" button the last row of the table is displayed in the controls as in the figure below.

BottomButton