This topic illustrates how to update data in a database using a DataSet. It is important to remember that you can also insert, update, and delete data in a database directly using a SqlCommand.
Once the DataSet is loaded, you can modify the data, and the DataSet will track the changes.
The DataSet can be considered an in-memory cache of data retrieved from a database. The DataSet consists of a collection of tables, relationships, and constraints. In this example we will show how to use the Delete method on the DataSet to Delete rows from the database.The Database values are updated with those from Dataset using Update method of SqlDataAdapter
Below are some simple steps to preform Delete operation on Dataset.
STEP 1 : Fill the Dataset with the data you required and bind it to grid.
STEP 2: To Delete a particular row from dataset we required its DatasetIndex, to know the DatasetIndex we perform the delete operation on Grid’s ItemCommand Event.
If e.CommandName = "Delete" Then
dgUser.DataSource = UpdateDatabase(parentset, "OleDb")
STEP 3: Create a new instance of OleDbDataAdapter and pass the required parameters, you can either pass OleDbCommand object or write down the Sql statement alongwith the ConnectionString.
Create a instance of OleDbCommandBuilder, it is used to update the changes from Dataset to Database. The parameter required for OleDbCommandBuilder is OleDbDataAdapter with a SELECT Sql statement. The command builder will automatically generate INSERT,UPDATE & DELETE statements for the table and parameters used in SELECT statement.
Public Function UpdateDatabase(ByRef dataSet As DataSet, ByVal Provider As String) As DataSet
If Provider = "OleDb" Then
Dim dbAdapter As New OleDbDataAdapter("SELECT Sql Statement",ConnectionString)
Dim objCommandBldr As New OleDbCommandBuilder(dbAdapter)
ElseIf Provider = "SQL" Then
Dim sqlAdapter As New SqlDataAdapter("SELECT Sql Statement",ConnectionString)
Dim sqlCommandBuilder As New SqlCommandBuilder(sqlAdapter)
STEP 4: Build & Execute.
Here is the link to download the sample application which will demonstrate the above behavior.
You can also find a detailed explanation of Updating Database using Dataset over here.
Enjoy Coding and Keep Learning 🙂