ADO.NET supports all the normal database operations of updating existing data, adding new data, and deleting existing data. Updating DataUpdating data is easy: You simply assign a new value to the item in the DataRow object that you want to change. But there's more to finishing the job. For the Update() method of the SqlDataAdapter object to write changes back to the database, you need to set its UpdateCommand property to an appropriate SqlCommand object. Follow these steps to see how to use a SqlDataAdapter object to update data in a database:
The Update() method of the SqlDataAdapter object is syntactically similar to the Fill() method. It takes as its parameters the DataSet object to be reconciled with the database and the name of the DataTable object to be saved. You don't have to worry about which rows or columns of data are changed because the SqlDataAdapter object automatically locates the changed rows. It then executes the SqlCommand object specified in its UpdateCommand property for each of those rows. In the previous example, 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 that 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 object (the default) or 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 DataTo add data to the database, you must supply a SqlCommand object for the InsertCommand property of the SqlDataAdapter object, as shown in the following code segment: SqlDataAdapter da = new SqlDataAdapter(); // Create a SqlCommand to insert data SqlCommand cmdIns = cnn.CreateCommand(); cmdIns.CommandType = CommandType.Text; cmdIns.CommandText = "INSERT INTO Customers(CustomerID, CompanyName, " + "ContactName) " + "VALUES(@CustomerID, @CompanyName, @ContactName)"; cmdIns.Parameters.Add("@CustomerID", SqlDbType.NChar,5, "CustomerID"); cmdIns.Parameters.Add("@CompanyName", SqlDbType.NVarChar,40, "CompanyName"); cmdIns.Parameters.Add("@ContactName", SqlDbType.NVarChar,30, "ContactName"); cmdIns.Parameters["@CustomerID"].SourceVersion = DataRowVersion.Original; // Set up the DataAdapter and fill the DataSet da.InsertCommand = cmdIns; The process of adding a new DataRow object to a DataTable object has several steps. First, you call the NewRow() method of the DataTable object, which returns a DataRow object that has the proper schema for that particular DataTable object. Then you can set the values of the individual items in the DataRow object. Finally, you call the Add() method of the DataTable object to actually append this DataRow object to the DataTable object, as shown in the following code segment: // Create a new DataRow DataRow dr = ds.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 ds.Tables["Customers"].Rows.Add(dr); // Now save back to the database da.Update(ds, "Customers"); Of course, appending the DataRow object to the DataTable object doesn't make any changes to the database. For that, you need to call the Update() method of the SqlDataAdapter object again. If the SqlDataAdapter object finds any new rows in its scan of the database, it calls the SqlCommand object specified by its InsertCommand property once for each new row. This SqlCommand object is the one that does the actual work of permanently saving the data. Deleting DataTo delete a row from the database, you must supply a SqlCommand object for the DeleteCommand property of the SqlDataAdapter object, as shown in the following code segment: // Create a SqlCommand to delete data SqlCommand cmdDelete = cnn.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 da.SelectCommand = cmdSelect; da.DeleteCommand = cmdDelete; The DataRow object supports a Delete() method that deletes an entire DataRow object from the DataTable object, as in the following code: dr.Delete(); To cause the changes to the database to persist, you need to call the Update() method of the SqlDataAdapter object, as in the following code: da.Update(ds, "Customers"); |