Data Binding From Database Using Stored Stored Procedure In F#

Introduction

This article explains how to bind data from a SQL Server database to a DataGridView in a Windows Forms application. I have used a Stored Procedure in the database and explained how it works from the front end. You will see I have saved the records in the database from the front end and after saving the records these records are bound to the DataGridView. Here I have used Visual Studio 2012 for the front end and SQL Server 2008 for the back end database .

Use the following to create a database and 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

)

Stored Procedure In Database

A Stored Procedure is the precompiled entity that executes on the database server itself. Stored Procedures are defined in the database by a "proc" or "procedure" keyword and variables are defined by the "@" character. Stored Procedures are used to do the SQL injection, sql-injeciton is the technique that allows the SQL queries to be hidden from the front end. Stored Procedures are commonly used for security purposes. A Stored Procedure is a group of SQL statements that have been created and stored in the database. A Stored Procedure accepts input and output parameters as well so that one Stored Procedure can be used over the network by various front ends using various input data. If we do any modification inside the Stored Procedure then all the front ends will be modified by the Stored Procedure. Stored Procedures reduce the network traffic and increase the performance of the applicaiton. I have designed two Stored Procedures, the first Stored Procedure for saving the records in the database and another one is for showing the records in the front end.

Create proc SaveData

@username varchar(30),

@firstname varchar(30),

@lastname varchar(30),

@address nvarchar(max),

@city varchar(30)

begin

insert into UserInformation(UserName,FirstName,LastName,Address,City)values(@username,@firstname,@lastname,@address,@city)

end

create proc BindData

begin

select * from userinformation

end

Now use the following procedure to bind the data inside the DataGridView using a Stored Procedure.

Step 1:

Open Visual Studio then create a new F# Console Application project.

Step 2:

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

After selecting the references right-click on "References".

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

Step 4:

Write the following code in the F# editor:

open System

open System.Drawing

open System.Data.SqlClient

open System.Windows.Forms

open System.Data

//connection string

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

//Creating user controls

let form = new Form()

let txt1 = new TextBox(Top = 10, Left = 70)

let txt2 = new TextBox(Top = 30, Left = 70)

let txt3 = new TextBox(Top = 50, Left = 70)

let txt4 = new TextBox(Top = 70, Left = 70)

let txt5 = new TextBox(Top = 90, Left = 70)

let btn1 = new Button(Top = 120)

let btn2 = new Button(Top = 150)

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

let lbl2 = new Label(Top = 30, Left = 0, Height = 20)

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

let lbl4 = new Label(Top = 70, Left = 0, Height = 20)

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

//form caption

form.Text <- "Inserting Records Into DataBase"

btn1.Text <- "Save"

btn2.Text <- "Show Details"

lbl1.Text <- "UserName"

lbl2.Text <- "First Name"

lbl3.Text <- "Last Name"

lbl4.Text <- "Address"

lbl5.Text <- "City"

//Adding user control

form.Controls.Add(txt1)

form.Controls.Add(txt2)

form.Controls.Add(txt3)

form.Controls.Add(txt4)

form.Controls.Add(txt5)

form.Controls.Add(lbl1)

form.Controls.Add(lbl2)

form.Controls.Add(lbl3)

form.Controls.Add(lbl4)

form.Controls.Add(lbl5)

form.Controls.Add(btn1)

form.Controls.Add(btn2)

//creating SqlConnection

let con = new SqlConnection(constring)

//open connection

con.Open()

let com = new SqlCommand()

com.Connection <- con

com.CommandType <- CommandType.StoredProcedure

com.CommandText <- "SaveData"

btn1.Click.Add( fun _ ->

com.Parameters.AddWithValue("@username", txt1.Text ) |> ignore// passing values

com.Parameters.AddWithValue("@firstname", txt2.Text ) |> ignore

com.Parameters.AddWithValue("@lastname", txt3.Text ) |> ignore

com.Parameters.AddWithValue("@address", txt4.Text ) |> ignore

com.Parameters.AddWithValue("@city", txt5.Text ) |> ignore

com.ExecuteNonQuery() |> ignore //ExecuteNonQuery

con.Close()

MessageBox.Show("Records Inserted") |> ignore //passing message

txt1.Clear() //clear TextBox1

txt2.Clear() //" "

txt3.Clear() // " "

txt4.Clear()

txt5.Clear() // " "

txt1.Focus() |> ignore)// setting focus

//Application.Run(form)

btn2.Click.Add(fun _ ->

let adapter = new SqlDataAdapter("BindData",con)

//creating DataSet

let ds = new DataSet()

//filling the DataSet

adapter.Fill(ds) |>ignore

//creating Form

//let form2 = new Form()

//creating DataGridView

let gridview = new DataGridView(Top=200,Width=600,Height=500)

//Adding DataGridView

form.Controls.Add(gridview)

//Binding DAtaGridView to DataSet

gridview.DataSource <- ds.Tables.[0]

)

//Showing the form

Application.Run(form)

In the preceding section I have used the SqlDataAdapter class. It is in the "System.Data.SqlClient" namespace. SqlDataAapter works as a bridge between the dataset and SQL Server to retrieve and save the data. It opens the database connection, executes the SQL statements and closes the connections at last. There is no need to open and close the connection. It has two more important methods Fill() and Update(). The Fill() method fills the DataSet or DataTable and Update() saves the changes to the Database in the same order as the DataSet, that is made with DataSet. The Fill method is the static method of the SqlDataAdapter class. When we call the Fill() method it automatically opens the connection from the database. DataSet works in a disconnected environment in which there is no need to keep the connection open until working with the DataSet. A DataSet is used to contain the multiple tables with their data and here I have used a DataGridView that shows the data in a tabular format.

Step 5:

Debug the application by pressing F5 to execute the Windows Forms application. After debugging the application you will get a Windows Forms application as in the figure below.

Output

Now you need to fill in the records in the given Windows application and click on the "Save" button. These records are saved in the database.

If you want to access all the saved records then you need to click on the "Show Details" button.

Let's have a look at the database. Just write the query:

select * from userinformation

Summary

In this article I have explained Stored Procedures and shown how data is bound from the database using a Stored Procedure. I hope this article explains for you how to bind data in a DataGridView in a F# Windows Forms application.