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.

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

Private Sub btnRaise_Click( _
 ByVal sender As System.Object, _
 ByVal e As System.EventArgs) _
 Handles btnRaise.Click

  RaisePrices()
End Sub

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

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.
  
  

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.