How to use the DataSet object to update data in a database in vb.net

The following article shows us that how to make a DataSet from database , modify that data , and send it back to the database to update the original source.

In following example first we will make a dataset object from Sql Server table and after that we will add a new record and edit an existing record and then update the database according to updated dataset object.
Suppose we have a sql server table named ‘tbl_employee.

Table name: tbl_Employee

Code for making dataset from database

Dim ds As New DataSet
Dim ConnectionString = “Data Source=pspl-pc6; Initial Catalog=EmployeeDatabase;Integrated Security=true”
Dim SqlCon As New SqlClient.SqlConnection
SqlCon.ConnectionString = ConnectionString
SqlCon.Open()
Dim cmd As New SqlClient.SqlCommand
cmd.Connection = SqlCon
cmd.CommandText = “Select * from tbl_employee”
cmd.CommandTimeout = 6000
Dim oDataAdapter As New SqlClient.SqlDataAdapter(cmd)
oDataAdapter.Fill(ds)

Create a new data table

Dim tblTemp As DataTable
tblTemp = ds.Tables(0)

Add new record in datatable

add new record fro new employee ‘Ram’

newRecord(“Employee Id”) = “6”
newRecord(“EmpName”) = “Ram”
newRecord(“Age”) = “40”
newRecord(“Department”) = “HR”
tblTemp.Rows.Add(newRecord)

Edit an existing Record

if you want to change department of the ‘John’ to ‘HR’

Dim EditRecord As DataRow
EditRecord = tblTemp.Rows(2)
EditRecord.BeginEdit()
EditRecord(“Department”) = “Sales”
EditRecord.EndEdit()

Update Database

Dim objCommandBuilder As New SqlClient.SqlCommandBuilder(oDataAdapter)
oDataAdapter.Update(tblTemp)