Bind Data in DataTable From Database in F#

Introduction

In this article I have explained the DataTable class and the properties and methods of the DataTable class. You will then see how to bind  records in a data table from the database in a Windows Forms application. I have used SQL Server 2012 for the database and a Windows Forms form for the front-end.

DataTable

The DataTable class is part of the "System.Data" namespace in the .Net framework. There are many binding controls available in the .NET library that allow you to bind the values to controls. DataTable is one of the important classes; generally you fetch the data from the database and fill the data into the data containers in a DataTable and then bind the data to binding controls, such a DataGridView, DataList and so on. A DataTable is a subset of a table that is represented in memory data. A DataTable object works in a disconnected environment that allows users to create a single table.

Properties

The following are the properties of the DataTable class:

• Columns: Represents all table columns.
• Primary Key: Represents an array of columns that function as the primary key for the table.
• Rows: All rows of the data table.
• TableName: Name of the table.
• Constraints: Represents all table constraints.

Methods

The following are the methods of the DataTable class:

• NewRow: Creates a new row, that is added by the Rows.Add method.
• Clear: Deletes all data table data.
• Clone: Creates a clone of a DataTable, including its schema.
• Copy: Copies a data table, including its schema.
• Reset: Resets a data table to its original state.
• Select: Gets an array of rows based on the criteria.

Create Database and Table in SqlServer

The following is a sample of creating a database and table in SQL Server:

create database Records

use Records

create table EmployeeDetails(

EmployeeId int,

FirstName varchar(max),

LastName varchar(max),Address nvarchar(max),

EmailId nvarchar(max)

)

 

The following is a sample of inserting values into a database table's fields:

 

insert into EmployeeDetails values(1,'Nimit','Joshi','A-43 Vinod Nagar','[e-mail]')

insert into EmployeeDetails values(2,'Amit','Senwal','B-44 Sarswati Kunj','[e-mail]')

insert into EmployeeDetails values(3,'Pankaj','Lohani','C-45 Sant Nagar','[e-mail]')

insert into EmployeeDetails values(4,'Pravesh','Khanduri','D-46 Pratap Vihar','[e-mail]')

insert into EmployeeDetails values(5,'Ravi','Kumar','E-47 R.K Puram','[e-mail]')

insert into EmployeeDetails values(6,'Ainul','Hasan','F-48 South-X','[e-mail]')

 

Now write the querty to check the values are inserted as in the following:

 

select * from EmployeeDetails

 

Now  I want to show you how to bind the records in the DataTable 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".

Step 2:

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

 

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

Step 4:

In the F# editor, write the following code to bind the records in the DataTable from the database.

open System

open System.Windows.Forms

open System.Drawing

open System.Data

open System.Data.SqlClient

 

let bindform=new Form(Text="Binding in DataTable")

bindform.BackColor<-Color.BurlyWood

let constring = @"Data Source=MCNDESKTOP34;Initial Catalog=Records;User ID=; Password="

let adap=new SqlDataAdapter("select * from EmployeeDetails",constring)

let showbtn=new Button(Top=20,Left=80,Width=180)

showbtn.Text<-"Binding in DataTable"

showbtn.BackColor<-Color.Ivory

bindform.Controls.Add(showbtn)

showbtn.Click.Add(fun _->

let dt=new DataTable()

adap.Fill(dt) |>ignore

let datagrid=new DataGridView(Top=50,Left=0,Width=500,Height=150)

bindform.Controls.Add(datagrid)

datagrid.DataSource<-dt

)

let exitbtn=new Button(Top=220,Left=80,Width=100)

exitbtn.Text<-"Exit"

exitbtn.BackColor<-Color.Ivory

bindform.Controls.Add(exitbtn)

exitbtn.Click.Add(fun exit->

bindform.Close())

Application.Run(bindform)

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:

Step 6 :

Now click on the button to bind the records in the DataTable from the database as in the following figure:

Step 7 :

After clicking on the button you will get the records in the DataGridView.

Summary

In this article we have explained the DataTable and the properties of the DataTable class and the methods of the DataTable class. You then saw how to search the records from the database table in a Windows Forms application.