Retrieving Datasets Generically

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:

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:

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

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

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.