Introduction
This article explains how to access data from the database and how to update the records using 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')
Create Stored Procedure
The following will create a Stored Procedure for the updates:
create proc UpdateRecord
@uid int,
@username varchar(30),
@firstname varchar(30),
@lastname varchar(30),
@address nvarchar(max),
@city varchar(30)
as
begin
update UserInformation set UserName=@username,FirstName=@firstname,LastName=@lastname,Address=@address,City=@city where UserId=@uid
end
Now let's use the following procedure.
Step 1:
Open Visual Studio and select "Create New Project" then select "F# Console Application".
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".
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 for the F# applcation:
// Learn more about F# at http://fsharp.net
// See the 'F# Tutorial' project for more help.
//Adding NameSpaces
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
let com2 = new SqlCommand()
//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 txtname = new TextBox(Top = 50, Left = 100,Height = 20)
let txtfname = new TextBox(Top = 90, Left = 100,Height = 20)
let txtlname = new TextBox(Top = 130, Left = 100, Height = 20)
let txtaddress = new TextBox(Top = 170, Left = 100, Height = 20)
let txtcity = new TextBox(Top = 200, Left = 100, Height = 20)
let searchbtn = new Button(Top = 230, Left=80)
let updatebtn = new Button(Top=230, Left=170)
//Adding Controls()
form.Controls.Add(lbluserId)
form.Controls.Add(txtuserId)
form.Controls.Add(lblusername)
form.Controls.Add(txtname)
form.Controls.Add(lbluserfname)
form.Controls.Add(txtfname)
form.Controls.Add(lbluserlname)
form.Controls.Add(txtlname)
form.Controls.Add(lbluseraddress)
form.Controls.Add(txtaddress)
form.Controls.Add(lblusercity)
form.Controls.Add(txtcity)
form.Controls.Add(searchbtn)
form.Controls.Add(updatebtn)
form.Text <- "Updating The Records"
searchbtn.Text <-"Search"
updatebtn.Text <-"Update"
lbluserId.Text <- "Enter UserId :"
lblusername.Text <- "UserName:"
lbluserfname.Text <- "FirstName :"
lbluserlname.Text <- "LastName :"
lbluseraddress.Text <- "Address :"
lblusercity.Text <- "City :"
//Search the records
searchbtn.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
txtname.Text <- dr.Item(1).ToString()
txtfname.Text <- dr.Item(2).ToString()
txtlname.Text <- dr.Item(3).ToString()
txtaddress.Text <- dr.Item(4).ToString()
txtcity.Text <- dr.Item(5).ToString())|>ignore
//Update the Records
updatebtn.Click.Add(fun _->
con.Close()
com1.Connection <- con
con.Open()
com1.CommandType <- CommandType.StoredProcedure
com1.CommandText <- "UpdateRecord" //UpdateRecord is a stored procedure
com1.Parameters.AddWithValue("@uid",txtuserId.Text) |> ignore
com1.Parameters.AddWithValue("@username",txtname.Text ) |> ignore
com1.Parameters.AddWithValue("@firstname",txtfname.Text) |> ignore
com1.Parameters.AddWithValue("@lastname",txtlname.Text) |> ignore
com1.Parameters.AddWithValue("@address",txtaddress.Text) |> ignore
com1.Parameters.AddWithValue("@city",txtcity.Text) |> ignore
com1.ExecuteNonQuery()|> ignore
con.Close()
MessageBox.Show("Records Updated")|>ignore
txtname.Clear()
txtfname.Clear()
txtlname.Clear()
txtaddress.Clear()
txtcity.Clear()
txtname.Focus() |>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.
Step 6:
Now you need to enter the user id because the user id is defined as a primary key in the table for locating the records uniquely from the database. Now click on the "Search" button.
Step 7:
After clicking on the Search button all the records are fetched into the textboxes from the database.
Step 8:
Now you can update your records and after updating the fetched records, just click the "Update" button then the records are saved to the database.
Step 9:
To check your updated records, go to the SQL Server database and write the following query in the Query Editor and execute the query by pressing F5:
select * from UserInformation
Summary
In this article you have seen how to create a table and insert values into the table fields and how to search the records in the database to a Windows Forms application and after fetching these records you can update your records and save then back into the database.