Using ADO.NET Connection Objects

IOTA^_^    

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


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.

Figure 13.1. You'll create this page as you work through this section.

graphics/13fig01.jpg

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

  1. Open Products.aspx in the page designer.

  2. Add a Label control to the page, to the right of the DropDownList control. Set the new control's ID property to lblAverage and its Text property to an empty string.

  3. Double-click the DropDownList control to load the ddlCategories_SelectedIndexChange procedure in the code editor.

    Not only do you need to load the products using the ProductsLoad procedure, but you will also need to call another procedure, CategoryAvgPrice, that will calculate the average price.

  4. Modify the procedure so that it looks like this (because the CategoryAvgPrice procedure doesn't yet exist in the class, you'll see a blue squiggle in the code don't worry, you'll fix that soon):

     Private Sub ddlCategories_SelectedIndexChanged( _  ByVal sender As System.Object, _  ByVal e As System.EventArgs) _  Handles ddlCategories.SelectedIndexChanged   ProductsLoad()   CategoryAvgPrice() End Sub 
  5. Add the procedure shown in Listing 13.3 to the class.

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 

TIP

The code in the CategoryAvgPrice procedure counts on you having added the appropriate Imports statement to your file. If you don't see Imports System.Data.OleDb at the top of your code-behind file, add it now.


  1. Modify the Page_Load procedure, adding a call to the CategoryAvgPrice procedure as well, as shown in Listing 13.4.

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 
  1. Build and browse the Products.aspx page and then select a category. You should see the label at the top of the page change to reflect the newly selected category.

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.

TIP

The example uses the String.Format method and the replaceable parameter {0:C} to convert the results of the ExecuteScalar method into currency formatting. Although there are other ways to format an object into a currency value, this is one of the simplest. String.Format supplies a large group of formatting values, such as the "C" used here, to format numbers and dates.



    IOTA^_^    
    Top


    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