Editing Data with ADO.NET

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:

  1. Add a new Visual C# ASP.NET Web Application project ( Example7_2 ) to the existing solution.

  2. Place three Label controls ( name one of them lblResults ), two TextBox controls ( txtCustomerID and txtContactName ), and a Button control ( btnUpdate ) on the form. Figure 7.1 shows the layout of this form.

    Figure 7.1. You can update the data in a database using a SqlDataAdapter object.

    graphics/07fig01.jpg

  3. Switch to Code view and add the following using directive:

     using System.Data.SqlClient; 
  4. 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";     } } 
  5. 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 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 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"); 


MCAD Developing and Implementing Web Applications with Visual C#. NET and Visual Studio. NET (Exam [... ]am 2)
MCAD Developing and Implementing Web Applications with Visual C#. NET and Visual Studio. NET (Exam [... ]am 2)
ISBN: 789729016
EAN: N/A
Year: 2005
Pages: 191

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net