Updating%20Database

1
Updating Database
2
Two Approaches to Update Database

• 1. Using the DataSet object, the updates are
  first applied to the DataSet then applied to the
  database using the Adapter object.
• 2. Using Command objects ExecuteNonQuery to run
  the SQL statements directly on the database.

3
Adding a New Record to a DataTable

• A DataTable consists of many rows. Each of these
  rows is represented by a DataRow object, and is
  held within the DataTable Rows property.
• To add a new row
• Define a DataRow object.
• Use DataTables NewRow() method to create the new
  row.
• Assign value to fields in the new row.
• Use the ADD method of the Rows to add the new row
  to the DataTable.

4
Code Example
dim objNewRow as DataRow objTableobjDataSet.Table
s("Cust") objNewRow objTable.NewRow() objNewRow(
"cid")cid.text objNewRow("Cname")cname.text objN
ewRow("city")city.text objNewRow("rating")rating
.text objTable.Rows.Add(objNewRow)
5
Add the New Row to the Data Source

• The Adapter object provides two-way interaction
  between the database and the DataSet.
• It has SelectCommand, InsertCommand,
  DeleteCommand, UpdateCommand properties that hold
  command objects to run SQL statements.
• To initialize these properties, we can
• define a command object( typically for
  SelectCommand)
• use a CommandBuilder object (for other commands)
• The Update method of an Adapter object executes
  commands to update the database.

6
OleDBCommandBuilder

• Property
• Adapter
• Methods
• GetDeleteCommand
• GetInsertCommand
• GetUpdateCommand
• The commandBuilder uses the Adapters
  SelectCommand to generate Insert, Delete, and
  Update commands.
• Adapters InsertCommand, DeleteCommand,
  UpdateCommand properties generated by the Command
  Builder object can transfer multiple updates back
  to the database.

7
Example Using the CommandBuilder
dim objBldernew OleDBCommandBuilder objBldernew
OleDBCommandBuilder(objAdapter) objAdapter.InsertC
ommandobjBlder.GetInsertCommand() objAdapter.Upda
te(objDataset,"cust") Note The commandBuilder
uses the Adapters SelectCommand to generate the
Insert command.
8
DataTables Select Method

• Select(FilterExpression)
• Gets an array of all System.Data.DataRow objects
  that match the filter criteria

9
Select Method Example
dim objRows() as DataRow objRowsobjTable.select("
cid '" CidList.selectedItem
"'") cid.textobjRows(0).item("cid") cname.textob
jRows(0).item("cname") city.textobjRows(0).item("
city") rating.textobjRows(0).item("rating")
10
Modifying An Existing Record

• Use Tables Select method to select the records
  for change.
• Assign new values to selected records.
• Apply the update to the database.

11
Modification Example
objTableobjDataset.tables("cust") objRowsobjTabl
e.select("cname '" CnameList.selectedItem.text
"'") objRows(0).item("cid")cid.text objRows(0).
item("cname")cname.text objRows(0).item("city")c
ity.text objRows(0).item("rating")rating.text str
SQL "select from customer" dim objAdapter as
new OledbDataAdapter(strSQL, objConn) dim
objBldernew OleDBCommandBuilder objBldernew
OleDBCommandBuilder(objAdapter) objAdapter.UpdateC
ommandobjBlder.GetUpdateCommand() objAdapter.Upda
te(objDataset,"cust")
12
Modification Example 2 Use Tables Rows Property
13
Example 2 Use Tables Rows PropertyForm Load
and Next Button
Private Sub Form2_Load(ByVal sender As
System.Object, ByVal e As System.EventArgs)
Handles MyBase.Load cid.Text
objDataSet.Tables("customer").Rows(rowIndex).Item(
"cid") cname.Text objDataSet.Tables("cus
tomer").Rows(rowIndex).Item("cname")
city.Text objDataSet.Tables("customer").Rows(row
Index).Item("city") rating.Text
objDataSet.Tables("customer").Rows(rowIndex).Item(
"rating") End Sub Private Sub
NextButton1_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles
Button1.Click objDataSet.Tables("customer"
).Rows(rowIndex).Item("cid") cid.Text
objDataSet.Tables("customer").Rows(rowIndex).Item(
"cname") cname.Text objDataSet.Tables("c
ustomer").Rows(rowIndex).Item("city")
city.Text objDataSet.Tables("customer").Ro
ws(rowIndex).Item("rating") rating.Text
rowIndex 1 cid.Text
objDataSet.Tables("customer").Rows(rowIndex).Item(
"cid") cname.Text objDataSet.Tables("cus
tomer").Rows(rowIndex).Item("cname")
city.Text objDataSet.Tables("customer").Rows(row
Index).Item("city") rating.Text
objDataSet.Tables("customer").Rows(rowIndex).Item(
"rating") End Sub
14
Update Button
Private Sub Button2_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs)
Handles Button2.Click Dim strConn As
String "ProviderMicrosoft.Jet.OLEDB.4.0Data
Source c\sales2k.mdb" Dim objConn As
New OleDbConnection(strConn) Dim
strSQLCust As String "select from customer"
Dim objAdapter As New OleDbDataAdapter(strS
QLCust, objConn) Dim objBlder New
OleDbCommandBuilder() objBlder New
OleDbCommandBuilder(objAdapter)
objAdapter.UpdateCommand objBlder.GetUpdateComma
nd() objAdapter.Update(objDataSet,
"CUSTOMER") End Sub
15
How .NET Keeps Track of Updates in the DataSet

• Each column in every row of a table maintains
  DataRowVersion
• Original The value in the column when the
  DataTable was created and filled with data.
• Current The actual column value after changes
  have been made.
• Each row in a table maintains RowState
• Unchanged
• Added
• Modified
• Deleted

16
DataRowVersion Demo

• objTable.Rows(0)("rating")"E"
• Messagebox.Show("Original rating is "
  objTable.Rows(0)("rating", DataRowVersion.Original
  ))
• Messagebox.Show("New rating is "
  objTable.Rows(0)("rating", DataRowVersion.Current)
  )

17
RowState Example

• DataSet objects GetChanges method returns a
  DataSet that contains updated rows.
• objModifiedDSobjDataSet.GetChanges(DataRowState.M
  odified)
• objAddedDS objDataSet.GetChanges(DataRowState.Add
  ed)
• objDeletedDS objDataSet.GetChanges(DataRowState.D
  eleted)
• DataTable objects GetChanges method returns a
  DataTable that contains updated tows.

18
Deletion

• The dataRows Delete method changes the RowState
  to Deleted.
• Apply the deletions to the database.

19
Delete Example
objTableobjDataset.tables("cust") objRowsobjTabl
e.select("cname '" CnameList.selectedItem.text
"'") objRows(0).Delete() strConn
"ProviderMicrosoft.Jet.OLEDB.4.0Data Source
c\sales2k.mdb" Dim objConn As New
OleDbConnection(strConn) strSQL "select from
customer" dim objAdapter as new
OledbDataAdapter(strSQL, objConn) dim
objBldernew OleDBCommandBuilder objBldernew
OleDBCommandBuilder(objAdapter) objAdapter.DeleteC
ommandobjBlder.GetDeleteCommand() objAdapter.Upda
te(objDataset,"cust")
20
Use Tables Rows with Index
Private Sub DeleteButton_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs)
Handles Button3.Click objDataSet.Tables("c
ustomer").Rows(rowIndex).Delete()
rowIndex 1 End Sub