Updating Data Using a Command Object


ASP.NET Developer's JumpStart
By Paul D. Sheriff, Ken Getz
Table of Contents
Chapter 13.  ADO.NET Connection and Command Objects

Updating Data Using a Command Object

Command objects can retrieve data, as you saw in the previous example. You can also use them to update, delete, and insert data that is, you can use them to take action with the data. In this example, you'll add code that can update the prices of items in the Products table, using a Command object. After you add the required controls and code to the page, it should look like Figure 13.2.

Figure 13.2. Raising prices can be accomplished with just a few controls and a little code.


Follow these steps to modify the layout of Products.aspx and to add the code necessary to update prices:

  1. Immediately above the DataGrid control, add Label, TextBox, and Button controls (to match the layout shown in Figure 13.2).

  2. Set the properties of the controls as shown in Table 13.1.

    Table 13.1. Set Properties of the New Controls to Match These Values
    Control Property Value
    Label Text Enter a percentage to raise prices:
    TextBox ID txtPercent
      Text 10
    Button ID btnRaise
      Text Raise Prices

  3. Double-click the btnRaise button to load the code-behind file and modify the btnRaise_Click procedure so that it looks like Listing 13.5.

    Listing 13.5 Add a Call to the RaisePrices Method
     Private Sub btnRaise_Click( _  ByVal sender As System.Object, _  ByVal e As System.EventArgs) _  Handles btnRaise.Click   RaisePrices() End Sub 
  4. Add the RaisePrices procedure shown in Listing 13.6.

    Listing 13.6 Use a Command Object to Raise All Prices
     Private Sub RaisePrices()   Dim cmd As New OleDbCommand()   Dim strSQL As String   Dim strConn As String   strSQL = _    "UPDATE Products " & _    "SET UnitPrice = UnitPrice * {0} " & _    "WHERE CategoryID = {1}"   strSQL = String.Format(strSQL, _    1 + (CDec(txtPercent.Text) / 100), _    ddlCategories.SelectedItem.Value)   strConn = Session("ConnectString").ToString   With cmd     .Connection = New OleDbConnection(strConn)     .Connection.Open()     .CommandText = strSQL     .CommandType = CommandType.Text     .ExecuteNonQuery()     .Connection.Close()   End With   ' Redisplay the grid   ProductsLoad()   CategoryAvgPrice() End Sub 
  5. Build and browse the page once more. This time, verify that clicking Raise Prices does indeed raise the prices by the amount you've specified in the text box.

RaisePrices does its work by taking these actions:

  • It creates a new OleDbCommand object:

     Dim cmd As New OleDbCommand() 
  • It sets up a SQL string template, with placeholders for the amount to raise prices and the category. In this case, the SQL statement is an UPDATE statement, specifying the field(s) to be updated and the value(s) to be inserted. The code then uses the String.Format method to insert the appropriate amount and category:

     strSQL = _  "UPDATE Products " & _  "SET UnitPrice = UnitPrice * {0} " & _  "WHERE CategoryID = {1}" strSQL = String.Format(strSQL, _  1 + (CDec(txtPercent.Text) / 100), _  ddlCategories.SelectedItem.Value) 
  • It retrieves the connection string from the Session variable, sets the necessary properties of the Command object, and calls the ExecuteNonQuery method of the Command object:

     With cmd   .Connection = New OleDbConnection(strConn)   .Connection.Open()   .CommandText = strSQL   .CommandType = CommandType.Text   .ExecuteNonQuery()   .Connection.Close() End With 
  • It redisplays the data:

     ' Redisplay the grid ProductsLoad() 

    This example uses the ExecuteNonQuery method of the OleDbCommand object. This method requires that you supply, in the CommandText property, either the name of a stored procedure that doesn't return any rows or an UPDATE, INSERT, or DELETE SQL statement. Because you're not using a DataSet here, you needn't create and fill a DataAdapter you simply need to execute the SQL you've supplied.


In case you need to retrieve the number of rows your SQL statement has modified, use the ExecuteNonQuery method. In this case, it doesn't matter, but if you should need the information, it's available.

To Close or Not to Close?

Here's something to consider: You can, if you want, open a connection to a data source and keep that connection open. Doing this makes it easier for you as a developer. You open the connection once, perhaps store the Connection object somewhere you can always use it, and then close it when your application has completed. The problem with this solution is that it's difficult, in an ASP.NET application, to really know when the application is done. In addition, Connection objects tie up valuable resources. You may, for example, pay for your connections individually you might not be able to have more than a fixed number of concurrent connections to your data source, for example. Opening a connection and leaving it open would tie up your resources quickly. In the interests of scalability, you generally want to use a connection for as short a period of time as possible. Generally, you want to open a connection, get the information you need, and then close the connection. This is how all the examples in this chapter (as well as those in later chapters) work.

Should you explicitly close Connection objects that you open? When the Command object you created goes out of scope, it will be destroyed (when the memory is required and when the garbage collector gets to it). When that happens, the connection used by the Command object will be destroyed as well. So why bother explicitly closing the Connection object? Calling the Close method explicitly releases the connection back to the connection pool (if connection pooling is available and enabled), and it rolls back any pending transactions.


In general, close connections when you're done with them. This leaves your data in a known state, at a known time. If you allow the garbage collector to close your connections for you, you won't be able to determine when the connection is closed and released.


In ADO, you could set several connection-related properties, such as the ConnectionTimeOut, DataSource, and Database properties. In ADO.NET, you can only set these values as part of a connection string. For example, to set the connection timeout, you'll need to include the Connect Timeout=n clause as part of your connection string. All these properties of the Connection object are still available they're just read-only in ADO.NET.


    ASP. NET Developer's JumpStart
    ASP.NET Developers JumpStart
    ISBN: 0672323575
    EAN: 2147483647
    Year: 2002
    Pages: 234

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