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. 

step1.gif

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

step2.gif

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

step3.gif

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.

step4.gif

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:

output.gif

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.

UPDATE1.gif

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

UPDATE2.gif

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

UPDATE3.gif

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:

UPDATE4.gif

By Executing SQL Command:

UPDATE5.gif

Up Next
    Ebook Download
    View all
    Learn
    View all