Updating Record Using CommandBuilder Class In F#

Introduction:

The CommandBuilder Class automatically generate command to update records of DataSet. It is used with DataAdapter and save the changes of dataset which contains the record which is specified in DataAdapter into DataBase at Back - End. It works on a single table. It will not only update previous record but also adds or delete record form DataSet. There is no need to write the code for these operation. To working with COmmandBuilder, we should have some record in DataBase. So, first we create the DataBase.

Creating the DataBase:

Create the DataBase and insert records. Here, in this example I am using a SQL Server DataBase. DataBase name is STUDENT and Table is STD_RECORD with columna Roll_No and Name. SQL Statements used in this example is given below.

CREATE DATABASE STUDENT

USE STUDENT 

CREATE TABLE STD_RECORD
(
 Roll_No INT,
 Name VARCHAR(30),
)

 INSERT INTO STD_RECORD VALUES(1,'ALOK PANDEY')
 INSERT INTO STD_RECORD VALUES(2,'RAJESH TRIPATHI')
 INSERT INTO STD_RECORD VALUES(3,'MANOJ SINGH')
 INSERT INTO STD_RECORD VALUES(4,'SATISH KUMAR')
 INSERT INTO STD_RECORD VALUES(5,'AMIT KUMAR')

Creating F# Application: Follow the given steps.

Step 1: Open Visual Studio and create a F# application. 



Step 2: Go to Solution Explorer and Right Click on References. 



Step 3: Click on Add References. Then a pop-up window with caption Add Reference will open as below figure.



Step 4:Click on .NET in the Add Reference window and select System.Windows.Forms, System.Drawing and System.Data with holding down Ctrl key and Click on Ok.



Step 5: Then write the below F# code in the Program.fs file.

// Learn more about F# at http://fsharp.net
//--------------  Importing Namespace
open System
open System.Windows.Forms
open System.Drawing
open System.Data.SqlClient
open System.Data

//-------------   Connection String
let constr = @"Data Source=SERVER_NAME;Initial Catalog=STUDENT;Integrated Security=True"

//-------------   Creating Form and User Controls
let frm = new Form()
let dg = new DataGrid(Top =20, Left =30, Height =180, Width =300)
dg.BackColor <- Color.LightBlue
let btn = new Button(Top = 200, Left =50,Height =20, Width =60)
btn.BackColor <- Color.LightSeaGreen
btn.Text <- "Update"
frm.Controls.Add(dg)
frm.Controls.Add(btn)

//---------------- Creating DataAdapter
let da = new SqlDataAdapter("select * from std_record", constr)
 
//---------------- Creating DataSet
let ds = new DataSet()
da.Fill(ds)|>ignore
dg.DataSource <- ds.Tables.[0]

//-----------------  Button Click
btn.Click.Add(fun _->
//---------------- Creating CommandBuilder
let comndbuil = new SqlCommandBuilder(da)
da.Update(ds)|>ignore
MessageBox.Show("Record Updated")|>ignore
) 
Application.Run(frm)

Output:



Now we do some updates on records. Click on first row of DataGrid and change name as AMITHBH PANDEY and Click at Update Button. Like the below figure.

Now we add a new record in DataBase table. Click on new row (Which is marked with *). Like the below figure.



Here I am adding 6 to Roll_No  and ABC KUMAR to Name. Click on the Update button. Like the below figure.



This change has been made to the DataBase. We can check it by re running the program or executing a SQL command in the DataBase.

By Re-Running Program:



By Executing SQL Command: