| ADO.NET supports all the normal database operations of updating existing data, adding new data, and deleting existing data. Updating Data Updating 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: Add a new Visual C# ASP.NET Web Application project (Example2_3) to the existing solution. Place three Label controls (name one of them lblResults), two TextBox controls (txtCustomerID and txtContactName), and a Button control (btnUpdate) on the form. Figure 2.2 shows the layout of this form. Figure 2.2. Design of a form that enables you to update data. 
Switch to code view and add the following using directive: using System.Data.SqlClient; Double-click the Button control and enter this code to handle its Click event: private void btnUpdate_Click(object sender, System.EventArgs e) { // Create some ADO.NET objects SqlConnection cnn = new SqlConnection(@"Data Source=(local)\NetSDK;" + "Initial Catalog=Northwind;Integrated Security=SSPI"); DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(); // Create a SqlCommand to select data SqlCommand cmdSelect = cnn.CreateCommand(); cmdSelect.CommandType = CommandType.Text; cmdSelect.CommandText = "SELECT CustomerID, ContactName FROM Customers"; // Create a SqlCommand to update data SqlCommand cmdUpdate = cnn.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 da.UpdateCommand = cmdUpdate; da.SelectCommand = cmdSelect; da.Fill(ds, "Customers"); // Get the DataRow to edit DataRow[] adrEdit = ds.Tables["Customers"].Select("CustomerID = '" + txtCustomerID.Text + "'"); // Make sure there's some data if(adrEdit.Length > 0) { adrEdit[0]["ContactName"] = txtContactName.Text; // Save the changes da.Update(ds, "Customers"); // And make a note on the UI lblResults.Text = "Row has been updated"; } } Set the project as the startup project for the solution and run the application. Enter a customer ID (such as ALFKI) and a new contact name; then click OK. The code writes the change back to the database and shows the Row has been updated text on the form.
The Update() method 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.  | If you set the SelectCommand property of the SqlDataAdapter, a SqlCommandBuilder object can automatically generate INSERT, UPDATE, and DELETE commands for single-table updates. You use this technique when you want to write minimum code. However, automatic generation of commands also requires extra trips to the server to get the schema information. You should make convenience and performance trade-offs depending on the application's requirements. |
Adding Data To 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 Data To 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"); |
