Retrieving Datasets Generically


ASP.NET Developer's JumpStart
By Paul D. Sheriff, Ken Getz
Table of Contents
Chapter 14.  Working with Data

Because you're likely to need DataSet objects as part of your applications, and the typed DataSet created by the user-interface components may be more than you need, it makes sense to provide a generic "get me a DataSet" procedure. You pass in the SQL string and the connection string, and the GetDataSet procedure does the rest.

The sample form, Products.aspx, uses DataSets in the CategoryLoad and ProductsLoad procedures. In both cases, the code is quite similar: You supply the SQL string and the connection string and then create a DataAdapter that uses this information. You call the Fill method of the DataAdapter, filling up the DataSet you need. You can have the user-interface components provided by Visual Studio .NET do this same work for you, but either way, if all you want is a DataSet to work with, the same concepts apply.

To add GetDataSet to your arsenal of tools, follow these steps:

  1. In the Solution Explorer window, select DataHandler.vb. Double-click to open the class in the code designer window.

  2. Add the following procedure to the module:

     Public Shared Function GetDataSet( _  ByVal SQLString As String, _  ByVal ConnectionString As String) As DataSet   Dim da As OleDbDataAdapter   Dim ds As DataSet   Try     ' Create new DataAdapter     da = New OleDbDataAdapter( _      SQLString, ConnectionString)     ' Fill DataSet from DataAdapter     ds = New DataSet()     da.Fill(ds)   Catch     Throw   End Try   Return ds End Function 

It's not that this procedure does anything miraculous it simply encapsulates code that you'll call often, if you decide not to use the user-interface components for creating DataSet objects.

Using the GetDataSet Procedure

Now that you've got a generic procedure for creating a DataSet, you really don't need the user-interface objects you placed onto Products.aspx in an earlier chapter, nor do you need the blocks of code you've previously written. To clean things up, you'll need to modify the ProductsLoad and CategoryLoad procedures you created earlier.

To fix up the procedures, follow these steps:

  1. With Products.aspx loaded in the page designer, press F7 to view the code designer (or choose the View, Code menu item).

  2. Modify the CategoryLoad procedure so that it looks like this (removing the extra code):

     Private Sub CategoryLoad()   Dim ds As DataSet   Dim strSQL As String   Dim strConn As String   strSQL = _    "SELECT CategoryID, CategoryName " & _    "FROM Categories"   strConn = Session("ConnectString").ToString()   ' Build DataSet   ds = DataHandler.GetDataSet(strSQL, strConn)   With ddlCategories     .DataTextField = "CategoryName"     .DataValueField = "CategoryID"     .DataSource = ds     .DataBind()   End With End Sub 
  3. Modify the ProductsLoad procedure so that it looks like this:

     Private Sub ProductsLoad( _  Optional ByVal SupplierID As String = "")   Dim ds As DataSet   Dim strSQL As String   Dim strConn As String   strSQL = "SELECT SupplierID, CategoryID, " & _    "ProductID, ProductName, UnitPrice, UnitsInStock " & _    "FROM Products"   If SupplierID = String.Empty Then     ' No supplier ID? Use the value in the     ' Category dropdown list.     strSQL &= " WHERE CategoryID = " & _      ddlCategories.SelectedItem.Value   Else     ' If you got a SupplierID value, use     ' that to filter the data.     strSQL &= " WHERE SupplierID = " & _      SupplierID   End If   strConn = Session("ConnectString").ToString()   ' Build DataSet   ds = DataHandler.GetDataSet(strSQL, strConn)   grdProducts.DataSource = ds   grdProducts.DataBind() End Sub 
  4. Press Shift+F7 (or use the View, Designer menu item) to load the page designer and then delete all the items from the tray area of the page (daProducts, cnNorthwind, and DsProducts1). You can also delete dsProducts.xsd from the Solution Explorer window, because you won't be needing it any longer.

  5. Browse the page once again and verify that everything works the way it did originally.

As you can see, using the generic GetDataSet procedure makes your code simpler, more readable, and removes your reliance on the data tools provided by Visual Studio .NET.


    ASP. NET Developer's JumpStart
    ASP.NET Developers JumpStart
    ISBN: 0672323575
    EAN: 2147483647
    Year: 2002
    Pages: 234 © 2008-2017.
    If you may any questions please contact us: