Using ADO.NET Connection Objects

As you work through the steps in this chapter, you will create and open a connection to a SQL Server database using an OleDbConnection object and then retrieve information using an OleDbCommand object. In addition, in the second half of the chapter, you will submit SELECT and UPDATE statements to the same SQL Server database using the OleDbCommand object. These are the steps you'll often use—anytime you want to retrieve information and display it in a control, you'll write code like the code you'll see here.

Adding Code to Retrieve and Display Data

For this example, you'll open a connection to a SQL Server database and return the average price of products within a product category. Figure 13.1 shows what your Products.aspx page will look like when you have completed this section.

Follow these steps to add a label and retrieve the calculated average:

Private Sub ddlCategories_SelectedIndexChanged( _
 ByVal sender As System.Object, _
 ByVal e As System.EventArgs) _
 Handles ddlCategories.SelectedIndexChanged

  ProductsLoad()
  CategoryAvgPrice()
End Sub

Listing 13.3. Display the Average Price for a Category in a Label

Private Sub CategoryAvgPrice()
  Dim cmd As OleDbCommand
  Dim cnn As OleDbConnection
  Dim strSQL As String

  strSQL = _
   "SELECT Avg(UnitPrice) " & _
   "FROM Products " & _
   "WHERE CategoryID = " & _
   ddlCategories.SelectedItem.Value

  Try
    ' Create and open a new Connection
    cnn = New OleDbConnection()
    With cnn
      .ConnectionString = _
        Session("ConnectString").ToString
      .Open()
    End With

    cmd = New OleDbCommand()
    With cmd
      .Connection = cnn
      .CommandText = strSQL
      lblAverage.Text = _
       String.Format("Average price of products " & _
       "in this category is {0:C}", .ExecuteScalar)
    End With

  Catch exp As Exception
    lblAverage.Text = exp.Message
  Finally
    cnn.Close()
  End Try
End Sub

Listing 13.4. Add a Call to CategoryAvgPrice to the Page_Load Procedure

Private Sub Page_Load( _
 ByVal sender As System.Object, _
 ByVal e As System.EventArgs) _
 Handles MyBase.Load

  If Not Page.IsPostBack Then
    CategoryLoad()
    ProductsLoad()
    CategoryAvgPrice()
  End If
End Sub

Investigating the CategoryAvgPrice Procedure's Code

Obviously, it's the CategoryAvgPrice procedure that's doing all the work here. It's worth taking the time to figure out what the procedure is doing by reviewing each step carefully. The procedure takes these actions:

• It creates a SQL string that it will use to retrieve just the data it needs from SQL Server. The procedure needs to calculate the average price for a particular category (the category supplied by the Value property of the selected item in the drop-down list):
  
  

  strSQL = _
   "SELECT Avg(UnitPrice) " & _
   "FROM Products " & _
   "WHERE CategoryID = " & _
   ddlCategories.SelectedItem.Value
  

  
  
• It sets up exception handling and instantiates an OleDbConnection object. The error handling ensures that if anything goes wrong, you'll see a description in the label on the page. No matter what happens, the open Connection object gets closed in the Finally block:
  
  

  Try
    ' Create and open a new Connection
    cnn = New OleDbConnection()
  
    ' code removed here...
  
  Catch exp As Exception
    lblAverage.Text = exp.Message
  
  Finally
    cnn.Close()
  End Try
  

  
  
• It uses the Session variable, ConnectString, to set up the Connection object and then calls its Open method to open the connection:
  
  

  With cnn
    .ConnectionString = _
      Session("ConnectString").ToString
    .Open()
  End With
  

  
  
• It instantiates a new OleDbCommand object and sets its Connection and CommandText properties appropriately. The Command object needs a connection in order to retrieve its data, and it uses the object you just created. In addition, the Command object needs a CommandText property, which indicates what you want the Command to do:
  
  

  cmd = New OleDbCommand()
  With cmd
    .Connection = cnn
    .CommandText = strSQL
  
    ' code removed here...
  
  End With
  

  
  
• Finally, it fills the Label control with the results of executing the command text you specified:
  
  

  With cmd
  
    ' code removed here...
  
    lblAverage.Text = _
     String.Format("Average price of products " & _
     "in this category is {0:C}", .ExecuteScalar)
  End With
  

  
  

In this case, the code uses the ExecuteScalar method of the Command object. In the next section, you'll learn how to use the ExecuteNonQuery method. In Chapter 14, “Working with Data,” you'll learn about the other method you might use to retrieve data using a Command object: the ExecuteReader method.

The ExecuteScalar method executes the SQL statement you've supplied (in this case, calculating the average price for a given category of products) and then returns the value from the first column in the first row of the set of rows created by executing the command. This method makes it simple to request a single value back from a database, given a criteria and a calculation.