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: -
Immediately above the DataGrid control, add Label, TextBox, and Button controls (to match the layout shown in Figure 13.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 | -
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 -
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 -
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.
TIP 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. TIP 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.
SETTING CONNECTION PROPERTIES 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. |
|
