Editing Data with ADO.NET
Now that you know how to retrieve data with ADO.NET, there's one other important database- related topic to cover: editing data. ADO.NET supports all the normal database operations of updating existing data, adding new data, and deleting existing data.
As you read this section, keep in mind the distinction between the data model and the database. As you work with data in the DataSet and its subsidiary objects, you're altering the data in the data model. These changes will not be reflected in the underlying database until and unless you call the Update method of the DataAdapter method. So far, I've only been using the SqlDataAdapter to move data from the database to the data model; in this section, you'll see how to move data back from the data model to the database.
Updating Data
Updating data is easy: Just assign a new value to the item in the data row that you want to changebut there's more to finishing the job. For the Update method of the SqlDataAdapter to write changes back to the database, you must set its UpdateCommand property to an appropriate SqlCommand object. Step By Step A.11 shows you how.
STEP BY STEP A.11 Updating Data with a SqlDataAdapter -
Add a new form to your Visual Basic .NET project. -
Place a Button control named btnUpdate on the form. -
Double-click the Button control to open the form's module. Enter these statements at the top of the module: Imports System.Data Imports System.Data.SqlClient -
Enter this code to load data when the form is opened: ' Create some ADO.NET objects Dim mcnn As SqlConnection = New SqlConnection(_ "Data Source=(local);" & _ "Initial Catalog=Northwind;Integrated Security=SSPI") Dim mds As DataSet = New DataSet() Dim mda As SqlDataAdapter = New SqlDataAdapter() Private Sub StepByStep6_24_Load(_ ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load ' Create a SqlCommand to select data Dim cmdSelect As SqlCommand = mcnn.CreateCommand() cmdSelect.CommandType = CommandType.Text cmdSelect.CommandText = _ "SELECT CustomerID, ContactName FROM Customers" ' Create a SqlCommand to update data Dim cmdUpdate As SqlCommand = mcnn.CreateCommand() cmdUpdate.CommandType = CommandType.Text cmdUpdate.CommandText = "UPDATE Customers SET " & _ "ContactName = @ContactName WHERE " & _ "CustomerID = @CustomerID" cmdUpdate.Parameters.Add("@ContactName", _ SqlDbType.NVarChar, _ 30, "ContactName") cmdUpdate.Parameters.Add(_ "@CustomerID", SqlDbType.NChar, _ 5, "CustomerID") cmdUpdate.Parameters("@CustomerID"). _ SourceVersion = _ DataRowVersion.Original ' Set up the DataAdapter and fill the DataSet mda.SelectCommand = cmdSelect mda.UpdateCommand = cmdUpdate mda.Fill(mds, "Customers") End Sub -
Enter this code to handle the Click event of the Button control: Private Sub btnUpdate_Click(_ ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnUpdate.Click ' Prompt the user for a customer ID Dim strCustomerID As String = _ InputBox("Enter Customer ID to modify:") ' Get just that customer's DataRow Dim adrEdit() As DataRow = _ mds.Tables("Customers").Select(_ "CustomerID = '" & strCustomerID & "'") ' Make sure there's some data If UBound(adrEdit) > -1 Then ' Prompt for new data and put it in the DataRow adrEdit(0)("ContactName") = _ InputBox("Enter new contact name", , _ adrEdit(0)("ContactName")) ' And save the changes mda.Update(mds, "Customers") End If End Sub -
Set the form as the startup object for the project. -
Run the project and click the button. The code will prompt you for a CustomerID value. Enter a CustomerID (such as ALFKI ) and click OK. The code will prompt you for a contact name. Enter a new contact name and click OK to write the change back to the database. You can verify the change by running the program a second time and changing the same customer's data. |
The Update method of the SqlDataAdapter is syntactically similar to the Fill method. It takes as its parameters the DataSet to be reconciled with the database and the name of the DataTable to be saved. You don't have to worry about which rows or columns of data were changed. The SqlDataAdapter automatically locates the changed rows. It executes the SqlCommand specified in its UpdateCommand property for each of those rows.
In this particular case, the UpdateCommand property has two parameters. The SqlParameter objects are created using a version of the constructor that takes four parameters rather than the three you saw earlier in the chapter. The fourth parameter is the name of a DataColumn which contains the data to be used in this particular parameter. Note also that you can specify whether a parameter should be filled in from the current data in the DataSet (the default) or from the original version of the data before any edits were made.
In this case, the @CustomerID parameter is being used to locate the row to edit in the database, so the code uses the original value of the column as the value for the parameter.
Adding Data
To add data to the database, you must supply a SqlCommand for the InsertCommand property of the SqlDataAdapter, as in Step By Step A.12.
STEP BY STEP A.12 Adding Data with a SqlDataAdapter -
Add a new form to your Visual Basic .NET project. -
Place four Label controls, three TextBox controls ( txtCustomerID , txtCompanyName , and txtContactName ) and a Button control ( btnAdd ) on the form. Figure A.12 shows a design for the form. Figure A.12. Designing a form to add new customers to the database.  -
Double-click the Button control to open the form's module. Enter these statements at the top of the module: Imports System.Data Imports System.Data.SqlClient -
Enter this code to load data when the form is opened: ' Create some ADO.NET objects Dim mcnn As SqlConnection = _ New SqlConnection("Data Source=(local);" & _ "Initial Catalog=Northwind;Integrated Security=SSPI") Dim mds As DataSet = New DataSet() Dim mda As SqlDataAdapter = New SqlDataAdapter() Private Sub StepByStep6_25_Load(_ ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load ' Create a SqlCommand to select data Dim cmdSelect As SqlCommand = mcnn.CreateCommand() cmdSelect.CommandType = CommandType.Text cmdSelect.CommandText = _ "SELECT CustomerID, CompanyName, " & _ "ContactName FROM Customers" ' Create a SqlCommand to insert data Dim cmdInsert As SqlCommand = mcnn.CreateCommand() cmdInsert.CommandType = CommandType.Text cmdInsert.CommandText = _ "INSERT INTO Customers " & _ "(CustomerID, CompanyName, ContactName) " & _ "VALUES(@CustomerID, @CompanyName, @ContactName)" cmdInsert.Parameters.Add(_ "@CustomerID", SqlDbType.NChar, _ 5, "CustomerID") cmdInsert.Parameters.Add(_ "@CompanyName", SqlDbType.NVarChar, _ 40, "CompanyName") cmdInsert.Parameters.Add(_ "@ContactName", SqlDbType.NVarChar, _ 30, "ContactName") cmdInsert.Parameters("@CustomerID"). _ SourceVersion = _ DataRowVersion.Original ' Set up the DataAdapter and fill the DataSet mda.SelectCommand = cmdSelect mda.InsertCommand = cmdInsert mda.Fill(mds, "Customers") End Sub -
Enter this code to handle the Click event of the Button control: Private Sub btnAdd_Click(_ ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnAdd.Click ' Create a new DataRow Dim dr As DataRow = _ mds.Tables("Customers").NewRow() ' Set values dr(0) = txtCustomerID.Text dr(1) = txtCompanyName.Text dr(2) = txtContactName.Text ' And append the new row to the DataTable mds.Tables("Customers").Rows.Add(dr) ' Now save back to the database mda.Update(mds, "Customers") MessageBox.Show("Row added!") End Sub -
Set the form as the startup object for the project. -
Run the project. Enter data in the text boxes and click the button. The code will add the new row to the database. |
As you can see, adding a new DataRow to a DataTable takes several steps. First, call the NewRow method of the DataTable to return a DataRow object that has the proper schema for that particular DataTable. Then you can set the values of the individual items in the DataRow. Finally, call the Add method of the DataTable to actually append this DataRow to the DataTable.
Of course, appending the DataRow to the DataTable doesn't make any changes to the database. For that you must call the Update method of the SqlDataAdapter once again. If the SqlDataAdapter finds any new rows in its scan of the database, it will call the SqlCommand specified by its InsertCommand property once for each new row. This SqlCommand actually permanently saves the data.
Deleting Data
The Rows collection of the DataTable object supports a Remove method that deletes an entire DataRow from the DataTable. To persist the changes to the database, you'll need to call the Update method of the SqlDataAdapter as in Step By Step A.13.
STEP BY STEP A.13 Deleting Data with a SqlDataAdapter -
Add a new form to your Visual Basic .NET project. -
Place a Button control named btnDelete on the form. -
Double-click the Button control to open the form's module. Enter these statements at the top of the module: Imports System.Data Imports System.Data.SqlClient -
Enter this code to load data when the form is opened: ' Create some ADO.NET objects Dim mcnn As SqlConnection = _ New SqlConnection("Data Source=(local);" & _ "Initial Catalog=Northwind;Integrated Security=SSPI") Dim mds As DataSet = New DataSet() Dim mda As SqlDataAdapter = New SqlDataAdapter() Private Sub StepByStep6_26_Load(_ ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load ' Create a SqlCommand to select data Dim cmdSelect As SqlCommand = mcnn.CreateCommand() cmdSelect.CommandType = CommandType.Text cmdSelect.CommandText = _ "SELECT CustomerID, ContactName FROM Customers" ' Create a SqlCommand to delete data Dim cmdDelete As SqlCommand = mcnn.CreateCommand() cmdDelete.CommandType = CommandType.Text cmdDelete.CommandText = _ "DELETE FROM Customers " & _ "WHERE CustomerID = @CustomerID" cmdDelete.Parameters.Add(_ "@CustomerID", SqlDbType.NChar, _ 5, "CustomerID") cmdDelete.Parameters("@CustomerID"). _ SourceVersion = _ DataRowVersion.Original ' Set up the DataAdapter and fill the DataSet mda.SelectCommand = cmdSelect mda.DeleteCommand = cmdDelete mda.Fill(mds, "Customers") End Sub -
Enter this code to handle the Click event of the Button control: Private Sub btnDelete_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnDelete.Click ' Prompt the user for a Customer ID Dim strCustomerID As String = _ InputBox("Enter Customer ID to delete:") Dim dr As DataRow Dim i As Integer For i = 0 To mds.Tables("Customers").Rows.Count - 1 dr = mds.Tables("Customers").Rows(i) If dr(0) = strCustomerID Then mds.Tables("Customers").Rows(i).Delete() Exit For End If Next ' Save the changes mda.Update(mds, "Customers") MessageBox.Show("Row deleted!") End Sub -
Set the form as the startup object for the project. -
Run the project and click the button. The code will prompt you for a CustomerID value. Enter a CustomerID (such as ALFKI ) that exists in the database and click OK. That customer will be deleted from the DataSet and from the database. |
Note that the deletion command uses the original value of the CustomerID column to locate the correct row to delete from the database.
REVIEW BREAK -
To move data to a DataSet, create an appropriate DataAdapter variable and call its Fill method. -
To move through a DataSet, you can use nested For Each loops , or you can simply specify which DataRow and DataColumn contain the data that you're interested in. -
Strongly typed DataSets allow you to use early-bound syntax with your data access code. -
You can use DataRelation objects to specify the relationships between multiple tables in the same DataSet. -
The DataTable.Select method and the DataView object offer two different ways to find and sort data. -
Changing data in a DataSet can be done by treating the items in the DataSet like any other variable. -
To persist changes from the data model to the underlying database, you must call the Update method of the SqlDataAdapter object. -
The UpdateCommand property of the SqlDataAdapter object specifies a SqlCommand object to be executed for all changed rows. -
The InsertCommand property of the SqlDataAdapter object specifies a SqlCommand object to be executed for all new rows. -
The DeleteCommand property of the SqlDataAdapter object specifies a SqlCommand object to be executed for all deleted rows. -
The DataGrid control can provide a convenient way to handle data changes on the user interface. |
