Updating a Database

Chapter 7 - Updating and Deleting Records
byJohn Kauffman, Fabio Claudio Ferracchiatiet al.?
Wrox Press ?2002

ADO.NET provides several different ways of updating the records in a database. If you know what records need to be updated, what their new values should be, and you want to update them immediately, you can use a command object to execute an SQL UPDATE statement or a stored procedure (see the next chapter). If you need some input from your users, you can create a dataset and populate it with the records you want the users to modify. A user can then look at the records and modify them as they wish; once they're done, you create a data adapter object to push the changes back to the database.

SQL UPDATE Statements

In the last chapter, we created new records using the SQL INSERT statement. SQL also defines a UPDATE statement to update one or more existing records. The generic form of the UPDATE statement's syntax is represented below.

     UPDATE     { table_name }     {       SET column1_name = expression1,           column2_name = expression2,           ...           columnM_name = expressionM       [WHERE condition1 AND|OR condition2 AND|OR ... AND|OR conditionN]     } 

Where:

  • UPDATE is the SQL command

  • table_name is the name of the table containing the record(s) to be updated

  • SET is a SQL keyword that starts the column update operations

  • column1_name, column2_name, etc. are the name of columns in which the values will be updated

  • expression1, expression2, etc. are the new values for each column

  • WHERE is a SQL keyword that specifies one or more conditions that qualify the records to be updated, just as it does in SELECT statements

Syntax alone is rather dry, though, so let's look at some SQL code that would make changes to the Categories table of the Northwind database, to see how you can apply the theory in practice. If you followed the examples in the previous chapter, you now have an entry in the Categories table called Books/Magazines, created with the following INSERT statement:

    INSERT INTO Categories    (CategoryName, Description)    VALUES ('Books/Magazines', 'Some books, some magazines') 

Now, if you wanted to change the description associated with this category, you could use an UPDATE statement like this one:

    UPDATE Categories    SET Description = 'Great books'    WHERE CategoryName = 'Books/Magazines' 

What it says is, "Find all records with a CategoryName of Books/Magazines, and change their Description value to Great books." In this case, of course, there's only one record that satisfies this condition.

In addition to assigning a literal value to a column, the SET clause also allows you to assign an expression to a column, or to assign values to several columns at once. Assuming that the CategoryID of the Books/Magazines record is 9, the following UPDATE statement would update both CategoryName and Description columns:

    UPDATE Categories    SET CategoryName = 'Magazines/Books',        Description = 'Interesting magazines' + ' and ' + 'books'    WHERE CategoryID = 9 

When it comes to updating records with ADO.NET, the two options available are very much like the ones we had in the last chapter when we wanted to insert records. You can use a command object to execute an UPDATE statement directly, or you can use a DataSet object to modify records in memory, and then update the database with those changes.

In the next section, we'll demonstrate how to update records using a command object. The DataSet solution will be covered after that.

Using a Command Object to Update Records

You're already familiar with a number of operations that use command objects, so extending that knowledge to cover updating a database shouldn't prove to be too tricky a task. The sequence of actions goes like this:

  • Create a database connection

  • Create a command object, and specify either an SQL UPDATE statement or a stored procedure

  • Link the command object to the database connection

  • Invoke the ExecuteNonQuery() method of the command object to execute the UPDATE query

  • Close the connection

The skeleton code below shows a simple database update operation along precisely these lines. In the example that follows, we'll give it a spin.

    Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")    Dim objConnection As New SqlConnection(strConnection)    Dim strSQL As String = "UPDATE Categories " & _                           "SET Description = 'Great books' " & _                           "WHERE CategoryName = 'Books/Magazines'"    Dim dbComm As New SqlCommand(strSQL, objConnection)    objConnection.Open()    dbComm.ExecuteNonQuery()    objConnection.Close() 

Try It Out – Displaying and Updating Database information with ASP.NET

start example

For this chapter's first example, we'll create an ASP.NET page that displays the name and price of a product in the Northwind database, and allows the user to enter a new price for the product, thereby updating the database. For simplicity, we'll hard-wire the code to use the product with a ProductID of 1 from the Products table of the database.

  1. Another chapter, another new folder. In webroot, create a new directory called ch07, and then generate a new text file called Update_Price.aspx.

  2. As usual, we'll start our listing with the HTML code for our elementary project. It involves placing a label, a text box, and a button on the page:

     <%@ Import namespace="System.Data" %> <%@ Import namespace="System.Data.SqlClient" %> <html>   <head>     <title>Updating a Price</title>   </head>   <body>     <form method="post" runat="server">       <asp:Label  runat="server" /><br/><br/>       <asp:TextBox  runat="server" /><br/><br/>       <asp:Button  runat="server" Text="Change" />     </form>   </body> </html> 

  3. Our first task must be to load the first record from the database and display it on the page. We'll isolate this functionality into a procedure called LoadProduct () that forms the first piece of Visual Basic .NET code in our ASPX file:

     <script language="VB" runat="server"> Sub LoadProduct()   Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")   Dim objConnection As New SqlConnection(strConnection)   Dim strQuery As String = "SELECT ProductName, UnitPrice " & _                            "FROM Products WHERE ProductID = 1"   objConnection.Open()   Dim dbComm As New SqlCommand(strQuery, objConnection)   Dim reader As SqlDataReader = dbComm.ExecuteReader()   reader.Read()   lblProductName.Text = reader.GetString(0)   txtPrice.Text = reader.GetSqlMoney(1).ToString()   reader.Close()   objConnection.Close() End Sub 

    This procedure executes the SELECT query and stores the returned records in a data reader object. It then goes to the first record (which happens to be the only record returned), and assigns the value of the ProductName and UnitPrice fields to the Text properties of the lblProductName label and the txtPrice text box respectively. With that done, it closes the reader and the connection to release the database connection.

  4. The next task is to get ASP.NET to run this procedure when the user loads the page. You do this by calling it from the Page_Load () event handler, as listed below.

     Sub Page_Load(ByVal Source As Object, ByVal E As EventArgs)   If Not IsPostBack Then     LoadProduct()   End If End Sub </script> 

    Later in this example, we'll use a button to instruct the database to change this value, but we don't want the page to be refreshed with new data from the database as soon as that happens - we need to give the database time to make the change. The code in the If statement ensures that the database will be queried for new data only when we ask for that to happen.

  5. It's a good time to give this page a run before we go any further. If you load the page into your browser, you should see something like this:

    click to expand

  6. So far, we've just been setting the scene - there's little here that you haven't seen in previous chapters. What we really want is to be able to change the price shown in the text box, and press the Change button to update the database. We need to add a click event handler for that button to our page.

        <asp:Button  runat="server"                OnClick="btnInsert_Click" Text="Change" />    Sub btnChange_Click(ByVal Sender As Object, ByVal E As EventArgs)      Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")    Dim objConnection As New SqlConnection(strConnection)      Dim strSQL As String = "UPDATE Products" & _                             " SET UnitPrice = " & txtPrice.Text & _                             " WHERE ProductID = 1"      Dim dbComm As New SqlCommand(strSQL, objConnection)      objConnection.Open()      dbComm.ExecuteNonQuery()      objConnection.Close()      LoadProduct()    End Sub 

end example

How It Works

Although the SQL statement in question has changed, this is similar to the examples we've been considering elsewhere, and you should be starting to feel comfortable with this form of database access. In the btnChange_Click () event handler, we open a connection, execute a command, and close the connection. On this occasion, the command in question is an UPDATE statement that assigns whatever the user entered in the txtPrice textbox to the UnitPrice field. Once the query has run, we call the LoadProduct() procedure to reload the modified record from the database and display the new price on the page.

Before we move on, there's one more thing worth mentioning. While the code we've written so far works, it has some deficiencies that we can improve. To demonstrate the basic techniques of reading and updating database records, LoadProduct () and btnChange_Click() are self-contained - each of them has code to open and close a connection for its own use. In a production-quality application, you'd put the common code (in this case, the code for opening and closing connections) in some centralized procedures, and call it from all other places that such functionality is needed. The code below suggests one way of doing this:

     Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")     Dim objConnection As SqlConnection     Sub LoadProduct()       Connect()       Dim strQuery As String = "SELECT ProductName, UnitPrice " & _                                "FROM Products WHERE ProductID = 1"       Dim dbComm As New SqlCommand(strQuery, objConnection)       Dim reader As SqlDataReader = dbComm.ExecuteReader()       reader.Read()       lblProductName.Text = reader.Getstring(0)       txtPrice.Text = reader.GetSqlMoney(1).ToString()       reader.Close()       Disconnect() End Sub    Sub btnChange_Click(ByVal Sender As Object, ByVal E As EventArgs)      UpdateProduct()      LoadProduct()    End Sub    Private Sub UpdateProduct()      Dim strSQL As String = "UPDATE Products" & _                             " SET UnitPrice = " & txtPrice.Text & _                             " WHERE ProductID = 1"      Connect()      Dim dbComm As New SqlCommand(strSQL, objConnection)      dbComm.ExecuteNonQuery()      Disconnect()    End Sub    Private Sub Connect()      If objConnection Is Nothing Then        objConnection = New SqlConnection(strConnection)      End If      If objConnection.State = ConnectionState.Closed Then        obj Connection.Open()      End If    End Sub   Private Sub Disconnect()     objConnection.Close()   End Sub 

Now, the code for connecting to and disconnecting from the database is in dedicated procedures, as is the code that actually updates the product information. Such changes result in cleaner, more structured, more extensible code.

Updating Records Using a DataSet

An ADO.NET DataSet object provides better programmatic database update functionality. Because a DataSet object is always disconnected from the database, you can add to, modify, and delete any of the records it contains offline. Once you've finished making your changes, you can transfer them to the database by linking the DataSet to a data adapter object. In this section, we'll explore how to do that.

Firstly, assume that you've filled a DataSet with records from the Products table in the Northwind database, as illustrated in the code snippet below:

    Dim strSQL As String = "SELECT ProductID, ProductName, UnitPrice FROM Products"    Dim adapter As New SqlDataAdapter(strSQL, objConnection)    Dim ds As New DataSet()    adapter.Fill(ds, "ProductTable") 

In the last line of code here, we're naming the table in the DataSet that will hold these records as ProductTable, but in general this name can be anything you like - it certainly doesn't have to be the same as the name of a table in the database. The advantage of this feature is that you can easily separate the in-memory representation of a table from its source in the database. As far as your application is concerned, you're always dealing with the table called ProductTable, as in:

    Dim tbl As DataTable = ds.Tables("ProductTable") 

If your friendly DBA later decides that the table in the database should be named differently, the only place where you need to change your code is the SELECT statement. (In fact, most applications use stored procedures to execute database operations, so your DBA can modify all the stored procedures that reference the table, without you having to change your code at all.)

Note 

After you've got hold of the DataSet, you should always release the connection to the database as quickly as possible, by invoking the Close() method of the connection object.

Modifying Records In a DataSet

With a DataSet object in your hands, you can modify one or more of the records it contains. The code snippet below shows how you might change the price of a product:

     Dim tbl As DataTable = ds.Tables("ProductTable")     tbl.PrimaryKey = New DataColumn() _                      { _                        tbl.Columns("ProductID") _                      }     Dim row As DataRow = tbl.Rows.Find(1)     row.Item("UnitPrice") = 100 

A key element to be aware of here is that in order to modify a record in a DataSet, we've got to find it first, and that's something we haven't done before. The first step in this process is to set up a column (or a selection of columns) in a dataset table as a primary key into that table. We can then use the primary key as an index to the rows of the table, so that we can quickly find the record we're looking for.

As suggested above, it's sometimes the case that a single column can be used as the primary key. On other occasions, the primary key may be a composite of several columns. As a result, the DataTable.PrimaryKey property is defined as a collection of columns that together make up the primary key, and you initialize it with all of the columns concerned, as shown in the second statement above. By contrast, if the primary key had consisted of a composite of the CategoryID and ProductID columns, we would have had to define the primary key like this:

    tbl.PrimaryKey = New DataColumn() _                     { _                       tbl.Columns("CategoryID"), _                       tbl.Columns("ProductID") _                     } 

Moving on to the next line of our sample, the Find() method of the DataRowCollection object that's represented by the DataTable.Rows property accepts a primary key value, and returns the DataRow containing the matching key. If the primary key consists of multiple columns, you pass in an array of values. For example, if the primary key consisted of a composite of the CategoryID and ProductID columns, the code snippet below would find a row with CategoryID value 1 and ProductID value 3.

    Dim KeyValues(1) As Object    KeyValues(0) = 1;    KeyValues(1) = 3;    Dim row As DataRow = tbl.Rows.Find(KeyValues) 

Once you have the row, you can modify any field in the record it represents simply by assigning a new value to it. The above example modified the UnitPrice field by assigning it with the value 100. You can make as many changes as required to this record, and you can modify more records by searching for them and executing the same set of operations. Remember that the changes you're making here only affect the DataSet - the database is not being updated at this point.

Updating Records in the Database

To push the changes you make in a DataSet object to the database, you must first reconnect to the database, and then use the same data adapter object that you used to retrieve the data to update the database with your new information:

    objConnection.Open()    adapter.Update(ds, "ProductTable")    objConnection.Close() 

Just as we saw when we were inserting records into the database in the previous chapter, the call to the data adapter's Update () method neatly encapsulates the task of transferring the new information to the database. Then, as now, we needed to set up a command builder object in order to make the mechanism work, but this time the work going on behind the scenes is rather more involved, and it's interesting to examine exactly how it works.

ADO.NET determines which records in the database to update by examining all of the records in the specified table in the DataSet object - ProductTable in this example. When you first fill the DataSet with records from a database, ADO.NET saves two copies of each field: Original and Current. If you wish, you can get access to these values individually by specifying the one you want in expressions like these:

    row.Item("UnitPrice", DataRowVersion.Original)    row.Item("UnitPrice", DataRowVersion.Current) 

In addition, each row has a property called RowState that indicates the current state of that row. It may be one of the following five values defined in the System.Data.DataRowState enumeration.

Value

Description

Added

The row has been added to the table.

Deleted

The row has been deleted from the table. Note that it's only been marked as deleted, and has not been physically removed. This allows ADO.NET to delete the corresponding row in the database later on, as we'll shortly discuss in more detail.

Detached

The row is not in a table. This happens when you have created a new DataRow object, but have not yet added it to the table's Rows collection (or you have removed the row from the table using the Remove () method). This is also covered later on.

Modified

The row has been modified. For instance, you have assigned a new value to one of the fields in the row.

Unchanged

The row has not been changed.

When you modify a record by changing the value of a field (as we did with UnitPrice), ADO.NET changes the row's Current value to the newly assigned value, and changes the RowState property to Modified. When you then invoke the data adapter's Update() method, ADO.NET updates the corresponding field in the database with the current value.

If you wish, you can call the AcceptChanges () method of a DataRow object to change the original values of all fields to the current value. (This method also sets the RowState to Unchanged.) Alternatively, you can call the AcceptChanges () method on a DataTable to accept changes to all rows in the table.

The opposite of AcceptChanges() is RejectChanges(),which is also supported by both row and table objects, and discards any changes that you've made to the DataSet. Calling a row's RejectChanges() method changes its RowState back to Unchanged. If you call the RejectChanges() method of a table, changes to all rows will be lost, and the RowState of all rows will be reset to Unchanged. Calling the table's RejectChanges() method will also remove any rows that you've added to the table.

Using a Command Builder

We said in the last chapter - and repeated above - that the creation of a command builder object is essential to the operation of the data adapter's Update() method. Once again, though, the increase in complexity here makes it useful to explore the subject in more depth.

    Dim cb As New SqlCommandBuilder(adapter) 

As a result of the above line of code, the SqlCommandBuilder's constructor will create a SqlCommand object with an SQL UPDATE statement based on the adapter's SELECT command, and then assign this command object to the data adapter's UpdateCommand property. Command builder classes can build INSERT and DELETE commands as well, and do so automatically when you create instances of them.

Creating a Update Command Manually

While command builder objects are undoubtedly handy and can save you a lot of coding, they have their drawbacks - or at least, their limitations:

  • They can only update records in a single database table only. If you have a query that pulls records from two tables or more, the command builder won't build the update command for you.

  • The SQL statement assigned to the data adapter object's SelectCommand property must return a column containing values that uniquely identify the returned records.

  • If the SelectCommand property changes, you must call the adapter object's RefreshSchema() method to update the metadata that's used to generate the insert, update, and delete commands.

If any of these describes your situation, command builder objects won't work for you, and you'll have to build the update command manually.

    Dim cmd As New SqlCommand("UPDATE Products SET UnitPrice = @Price " & _                              "WHERE ProductID = @ProductID", objConnection)    Dim param As SqlParameter = cmd.Parameters.Add("@ProductID", SqlDbType.Int)    param.SourceColumn = "ProductID"    param.SourceVersion = DataRowVersion.Original    param = cmd.Parameters.Add("@Price", SqlDbType.Money)    param.SourceColumn = "UnitPrice"    param.SourceVersion = DataRowVersion.Current    adapter.UpdateCommand = cmd 

In this code, we create a command object and pass to its constructor the SQL UPDATE statement (or the stored procedure) that performs the update. Because at this point we don't know the exact record to be updated, or the price it will be updated with, we specify that the ID and price of the product will be provided later by parameterizing the statement.

The SQL statement must include every column in the table that you want to update. If you change the values of some fields, but don't include the relevant columns in the UPDATE statement, none of the changes you make will find their way to the database.

Next, we add two parameters to the command. The first, @ProductID, is an integer - we specify that it should be bound to the ProductID column in the table, and ensure that we're dealing with the right row by binding it to the original value of this field in the database through the parameter's SourceVersion property. The second parameter, @Price, is bound to the UnitPrice column, and the current version of the value it contains.

Having defined the command object, we then assign it to the data adapter object's UpdateCommand property. When we invoke the data adapter's Update() method, this command is executed to update the correct product record in the database.

Try It Out - Editing Database Entries in a DataGrid in ASP.NET

start example

With the theory under our belts, we can think about putting it into practice. In this exercise, we'll load all of the beverage product records from the Products table in the Northwind database, and display them in a DataGrid control on an ASP.NET page. The DataGrid control is configured in such a way that you can edit a record inline, and update it.

  1. The ASPX file for this example is going to be called ChangePrice_Datagrid.aspx, so create a text file by that name and add it to your ch07 folder.

  2. The HTML for this example is brief, but contains some features that we haven't seen before. This is what it looks like:

     <%@ Import namespace="System.Data" %> <%@ Import namespace="System.Data.SqlClient" %> <html>   <head>     <title>Updating Beverages</title>   </head>   <body>     <form method="post" runat="server">       <asp:DataGrid  runat="server"                     CellPadding="5" AutoGenerateColumns="False">         <Columns>           <asp:BoundColumn DataField="ProductID" ReadOnly="True"                         Visible="False" />           <asp:BoundColumn DataField="ProductName" ReadOnly="True"                         HeaderText="Name" />           <asp:BoundColumn DataField="UnitPrice" HeaderText="Price" />           <asp:EditCommandColumn ButtonType="LinkButton"                         UpdateText="Save" CancelText="Cancel" EditText="Edit" />         </Columns>       </asp:DataGrid>     </form>   </body> </html> 

    As you can see, we've specified explicitly that the DataGrid should contain four columns: three BoundColumns, and an EditCommandColumn, which is new to us here. Let's look at each of these in turn.

    The first column will be linked to the ProductID column of the table in the dataset, but while we need to keep track of this value (so that we have a link back to the table in the database), we don't need to display it to the user, and we've no intention of allowing it to be changed.

    The second column will be linked to the ProductName column of the table in the dataset, which we certainly will display to our users. However, we don't want to allow changes to these values.

    The third column will be linked to the UnitPrice column of the table in the dataset. We'll display this to the users too, and they'll be able to change the values it contains by clicking on the Edit button in the fourth column.

    The fourth column is an ASP.NET EditCommandColumn control. As you'll see, these columns are specifically intended for users to interact with. After the user has chosen to Edit data, the contents of the column change to show Save and Cancel buttons that allow them to confirm or discard their changes respectively.

  3. The first VB.NET procedure we'll write is, as so often, the Page_Load() event handler. As is our habit, we pass off the action to a helper, which on this occasion is called LoadGrid().

     Private Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs)   If Not IsPostBack Then     LoadGrid()   End If End Sub 

  4. LoadGrid() creates a data adapter, fills a dataset from the database, and then populates the DataGrid with the information it retrieved.

     Private Sub LoadGrid()   Connect()   Dim adapter As New SqlDataAdapter(strSQLSelect, objConnection)   Dim ds As New DataSet()   adapter.Fill(ds, ProductTableName)   Disconnect()   With dgProducts     .DataSource = ds.Tables(ProductTableName)     .DataBind()   End With End Sub 

  5. After that, and for the time being, we move on to our little helpers, Connect() and Disconnect().

     Private Sub Connect()   If objConnection Is Nothing Then     objConnection = New SqlConnection(strConnection)   End If   If objConnection.State = ConnectionState.Closed Then     objConnection.Open()   End If End Sub Private Sub Disconnect()   objConnection.Close() End Sub </script> 

  6. Finally, we have the definitions of the global variables we've used in the above routines, which should be placed at the top of the <script> block.

     Private strConnection As String = ConfigurationSettings.AppSettings("NWind") Private strSQLSelect As String = "SELECT ProductID, ProductName, UnitPrice " & _                                  "FROM Products "WHERE CategoryID = 1" Private ProductTableName As String = "ProductTable" Private objConnection As SqlConnection 

  7. Provided that everything has gone to plan, if you run the code at this stage, you should be presented with something like this in your browser:

    click to expand

end example

How It Works (1)

When the ASPX page is loaded for the first time, the Page_Load () event handler calls LoadGrid() to do the data binding work. In this exercise, we associate the DataGrid with the ProductTable in the DataSet, and then call the DataGrid's DataBind() method to bind it. This populates the DataGrid with the records in the table. So far, so good.

To go further, it's interesting to take a look at how ASP.NET renders the DataGrid. If you take a look at the source of the generated page, each record is a row in the table, and each field is an HTML cell. The first row of the DataGrid, for example, looks like this:

    <tr>      <td>Chai</td>      <td>18</td>      <td>        <a href="javascript:__doPostBack('dgProducts:_ct12:_ct10' , ' ') ">Edit</a>      </td>    </tr> 

As you can see, the Edit button is rendered as a link. When you click on the Edit link, an automatically generated JavaScript function called __doPostBack() is triggered to post the form to the server. On the server, ASP.NET checks the posted form, and generates an EditCommand event. It's now up to you to create an event handler to respond to this event, and that's where we need to go next.

  1. The first step in setting up an event handler is to add a new attribute to the <asp:DataGrid> element that specifies the procedure that will be used to handle the EditCommand event:

       <asp:DataGrid  runat="server"                 CellPadding="5" AutoGenerateColumns="False"                 OnEditCommand="EditRecord"> 

  2. With the plumbing in place, implementing the EditRecord () handler is a two-line affair:

     Public Sub EditRecord(ByVal Sender As Object, _                       ByVal E As DataGridCommandEventArgs)   dgProducts.EditItemIndex = E.Item.ItemIndex   LoadGrid() End Sub 

  3. Now when you click on one of the Edit links, you'll see a change occur:

    click to expand

How It Works (2)

The EditItemIndex property of a DataGrid specifies which row should be put into edit mode. When you click an Edit link, ASP.NET adds the row to a DataGridCommandEventArgs object and exposes it as the object's Item property. As you can see above, the event handler gets the DataGridCommandEventArgs object as an argument of the EditCommand event. You can then retrieve the row by reading its Item property, which returns a DataGridItem object, and the row index with the ItemIndex property.

The LoadGrid () function will again populate the DataGrid with all of the records in the table, but this time it will behave differently, because we've assigned the DataGrid's EditItemIndex property with the index of a row. ASP.NET checks the DataGrid column definitions to find out which fields can be edited, and renders those fields as HTML text input controls. Our sample HTML row now looks like this:

    <tr>      <td>Chai</td>      <td><input name="dgProducts:_ct12:_ct10" type="text" value="18" /></td>      <td>        <a href="javascript: __doPostBack('dgProducts: _ct12:_ct11', '')">Save</a>        &nbsp;        <a href="javascript:_doPostBack('dgProducts:_ct12:_ct12','')">Cancel</a>      </td>    </tr> 

Now you can change the value in the Price column, and click either the Save or the Cancel button. These buttons raise an UpdateCommand and a CancelCommand event respectively, and to progress further with this demonstration, we need to define handlers for both of them.

  1. The CancelCommand event handler is relatively simple. In most cases, you'll simply discard any changes that have been made, and redisplay the grid with records loaded from the database.

           <asp:DataGrid  runat="server"                     CellPadding="5" AutoGenerateColumns="False"                     OnEditCommand="EditRecord"                     OnCancelCommand="CancelEdit"> Public Sub CancelEdit(ByVal Sender As Object, _                       ByVal E As DataGridCommandEventArgs)   dgProducts.EditItemIndex = -1   LoadGrid() End Sub 

    By setting the EditItemIndex property to -1 (remember that the index of the first row in the grid is 0), ASP.NET will render the grid without creating any input controls. You can then call the LoadGrid () function to populate the grid to be displayed on the page.

  2. The UpdateCommand event handler, on the other hand, has to do rather more than the one for CancelCommand. Usually, you'll want to update the database when this event fires - either by creating a command object and invoking its ExecuteNonQuery() method, as we did in the previous exercise, or by using the combination of a DataSet and data adapter object, as we explored above. Let's see how to apply the latter approach in this exercise.

          <asp:DataGrid  runat="server"                     CellPadding="5" AutoGenerateColumns="False"                     OnEditCommand="EditRecord"                     OnCancelCommand="CancelEdit"                     OnUpdateCommand="Updaterecord"> Public Sub UpdateRecord(ByVal Sender As Object, _                         ByVal E As DataGridCommandEventArgs)   ' Retrieve the field values in the edited row   Dim ProductID As Int32 = Convert.ToInt32(E.Item.Cells(0).Text)   Dim PriceTextBox As TextBox = CType(E.Item.Cells(2).Controls(0), TextBox)   Dim Price As Decimal = Convert.ToDecimal(PriceTextBox.Text)   dgProducts.EditItemIndex = -1   UpdateProduct(ProductID, Price)      dgProducts.DataBind() End Sub 

How It Works (3)

To update the record, you need to figure out which product and field(s) have been changed. To help in this task, the E.Item property again returns the row that has been changed. Its Cells property returns a collection of cells, which is a TableCellCollection object. You retrieve a cell in this collection by providing its index; unfortunately, it doesn't provide a method to retrieve a cell using the column name. Once you have the cell, you can extract the value in its Text property.

In our event handler code, the product ID is bound to the first column, and therefore E.Item.Cells(0).Text returns the ID of the product being updated. Because it's an integer, we convert and assign it to an integer variable called ProductID.

You could also retrieve the product name by checking e.Item.Cells (1).Text, but the product name is never changed in this example, so we can safely ignore this column here.

Getting the value of the changed field requires a little more work. When it's being edited, the field is represented as an input text control inside a table cell, and you need to go via an enumeration containing the all of the controls in the cell in order to get hold of it.

      Dim PriceTextBox As TextBox = CType(E.Item.Cells(2).Controls(0), TextBox)      Dim Price As Decimal = Convert.ToDecimal(PriceTextBox.Text) 

Breaking this down a little, you can get to the cell containing the changed price with E.Item.Cells (2). Once you have the cell, you can look further into its Controls property to find the input box, which is the one and only control in the cell: E.Item.Cells(2).Controls(0). In the above code, it's cast to a TextBox control, from which you can get the value in the Text property. This value is then converted to a Decimal type, which maps to SQL Server's money type.

  1. Once you have both the product ID and the new price, you're ready to update the database. The UpdateProduct () procedure does just that.

     Private Sub UpdateProduct(ByVal ProductID As Long, ByVal Price As Decimal)   ' Create and load a DataSet with records from Northwind.Products table   Connect()   Dim adapter As New SqlDataAdapter(strSQLSelect, objConnection)   Dim ds As New DataSet()   adapter.Fill(ds, ProductTableName)   Disconnect()   ' Modify the in-memory records in the DataSet   Dim tbl As DataTable = ds.Tables(ProductTableName)   tbl.PrimaryKey = New DataColumn() _                    { _                      tbl.Columns("ProductID") _                    }   Dim row As DataRow = tbl.Rows.Find(ProductID)   row.Item("UnitPrice") = Price   ' Reconnect the DataSet and update the database   Dim cb As New SqlCommandBuilder(adapter)   Connect()   adapter.Update(ds, ProductTableName) dgProducts.DataSource = ds.Tables(ProductTableName) Disconnect()    End Sub 

How It Works (4)

Does that look familiar? It should - this essentially contains the same code as we used to illustrate the theory section. The first block reads an entire result set from the database, and fills a DataSet object. The second block finds a record with the product ID passed to this procedure, and updates its price with the new price. The third block builds the UPDATE command (plus the INSERT and DELETE commands, which we don't use here), and then updates the record in the database. Finally, on return to UpdateRecord (), the grid is re-bound to the DataSet. Try the application one last time, and you'll find that it does everything we've asked of it.

And that's just about all for our discussion about updating database records. In the next section, we'll move on to see how to delete records from a database.



Beginning ASP. NET 2.0 and Databases
Beginning ASP.NET 2.0 and Databases (Wrox Beginning Guides)
ISBN: 0471781347
EAN: 2147483647
Year: 2004
Pages: 263

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