| 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: -
In the Solution Explorer window, select DataHandler.vb. Double-click to open the class in the code designer window. -
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: -
With Products.aspx loaded in the page designer, press F7 to view the code designer (or choose the View, Code menu item). -
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 -
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 -
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. -
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. |
