Xem mẫu

Chapter 7 CREATE PROCEDURE dbo.spRetrieveCategories AS select * from categories RETURN 9. Next, add this procedure to the clsDatabase class. This procedure gets called from the LoadCompleteDataSetfunction created previously and outputs the DataSet information to the Output window. Sub WriteCompleteDataSetToOutputWindow(ByVal dsData As DataSet) `**************************************************************** `Write data to the output window from the DataSet `**************************************************************** Try Dim oRow As DataRow Dim strRecord As String `write some data in the Products table to the Output window `to show that the data is there. For Each oRow In dsData.Tables("Products").Rows strRecord = "Product Id: " & oRow("ProductId").ToString() strRecord = strRecord & " Product Name: " strRecord = strRecord & oRow("ProductName").ToString() strRecord = strRecord & " Supplier Id: " strRecord = strRecord & oRow("SupplierId").ToString() Console.WriteLine(strRecord) Next `write some data in the Suppliers table to the Output window `to show that the data is there. For Each oRow In dsData.Tables("Suppliers").Rows strRecord = "Supplier Id: " & oRow("SupplierId").ToString() strRecord = strRecord & " Company Name: " strRecord = strRecord & oRow("CompanyName").ToString() strRecord = strRecord & " Contact Name: " strRecord = strRecord & oRow("ContactName").ToString() Console.WriteLine(strRecord) Next `write some data in the Categories table to the Output window `to show that the data is there. For Each oRow In dsData.Tables("Categories").Rows strRecord = "Category Id: " & oRow("CategoryId").ToString() strRecord = strRecord & " Category Name: " strRecord = strRecord & oRow("CategoryName").ToString() strRecord = strRecord & " Description: " strRecord = strRecord & oRow("Description").ToString() Console.WriteLine(strRecord) Next 32 Reading Data into the DataSet Catch `error handling goes here UnhandledExceptionHandler() End Try End Sub 10.Finally, add the UnhandledExceptionHandler to the clsDatabase class Sub UnhandledExceptionHandler() `display an error to the user MsgBox("An error occurred. Error Number: " & Err.Number & _ " Description: " & Err.Description & " Source: " & Err.Source) End Sub How It Works In this section, first we created a new class, clsDatabase.vb, and added the following namespaces: Imports System.Data Imports System.Data.SqlClient The DataSetfeatures we will be using come from these two namespaces. If you do not have these references in your class module, then some of the code that follows will generate a compiler error when you try to build your project as they are required to locate certain classes and methods. The first namespace (System.Data) is for general data access, and the second namespace (System.Data.SqlClient) is SQL Server specific. Next we added a generic routine that populates a DataSet with the results of a stored procedure or SQL statement. Dim sqlConn As New SqlClient.SqlConnection(strConnection) sqlConn.Open() Dim adapterProducts As New SqlClient.SqlDataAdapter() adapterProducts.TableMappings.Add("Table", strTableName) Dim cmdTable As SqlClient.SqlCommand = New _ SqlClient.SqlCommand(strSQLorStoredProc, _ sqlConn) `run stored procedure or SQL statement accordingly If blnStoredProcedure Then cmdTable.CommandType = CommandType.StoredProcedure Else cmdTable.CommandType = CommandType.Text End If 33 Chapter 7 adapterProducts.SelectCommand = cmdTable `fill the data set with the table information as specified in `the stored procedure or from the results of the SQL statement adapterProducts.Fill(dsDataSet) In the code snippet from the PopulateDataSetTable function above, notice how a SqlConnection is declared first, and then opened. Then, a new SqlDataAdapter is declared. SqlDataAdapter is the class used to fill and update DataSets. Note that OleDbDataAdapter can also be used, and it works with OLE DB data sources, including SQL Server. SqlDataAdapter on the other hand only works with SQL Server databases but, in such cases, it outperforms OleDbDataAdapter. Next, table mappings are defined for the adapter. The primary purpose of a table mapping is to specify what the table in the DataSetshould be called, regardless of the source it is coming from. The first parameter to the Add method is the source table and the second is the destination table. The source table is the table in the data source to retrieve information from while the destination table is the table in the DataSet that the data goes into. When populating the DataSet from a stored procedure or SQL statement, simply specifying the default value of "Table" for the source table is sufficient. Dim adapterProducts As New SqlClient.SqlDataAdapter() adapterProducts.TableMappings.Add("Table", strTableName) Dim cmdTable As SqlClient.SqlCommand = New _ SqlClient.SqlCommand(strSQLorStoredProc, _ sqlConn) A Commandobject is declared next to define the SQL statement or stored procedure to base the DataSettable on, as well as which database connection to use. The Command object is then associated with the adapter, which is how the adapter is made aware of from where to retrieve the results. `run stored procedure or SQL statement accordingly If blnStoredProcedure Then cmdTable.CommandType = CommandType.StoredProcedure Else cmdTable.CommandType = CommandType.Text End If adapterProducts.SelectCommand = cmdTable Finally, using the DataAdapter, the DataSet can be populated from the SQL statement or stored procedure. `fill the data set with the table information as specified in `the stored procedure or from the results of the SQL statement adapterProducts.Fill(dsDataSet) sqlConn.Close() 34 Reading Data into the DataSet After creating the generic function to populate a DataSet, we then created a function called PopulateDataSetRelationship to relate two tables in a DataSet together. Recall that a DataSet is an in-memory copy of information. It can contain tables that are totally independent from the source, once placed in memory. Thus, even though relationships may exist in a database, when you populate such information into a DataSet, those relationships do not carry over between tables. You can create relationships between tables in your DataSet so that tables in the in-memory copy relate to each other. This example makes use of the DataRelation and DataColumn objects. After the DataColumns to be related are specified (as columns already present in the DataSet), then the DataRelation object creates the relationship. Dim drRelation As DataRelation Dim dcCol1 As DataColumn Dim dcCol2 As DataColumn dcCol1 = _ dsDataSet.Tables(strTable1).Columns(strColumnFromTable1) dcCol2 = _ dsDataSet.Tables(strTable2).Columns(strColumnFromTable2) drRelation = New System.Data.DataRelation _ (strRelationshipName, dcCol1, dcCol2) dsDataSet.Relations.Add(drRelation) In the above code, dcCol1 is the first table in the DataRelationmethod`s parameters, and dcCol2 is the second. This means that dcCol1 is the parent table, and dcCol2 is the child table. A table is known as the parent table because it is the one that ensures the uniqueness of the key field on which this relationship hinges. If you were to reverse the order of these parameters, then you would likely get a run-time error about non-unique columns. Now that we have our generic functions in place to populate a DataSet from a stored procedure or SQL statement, and one to create relationships in a DataSet, we`re ready to populate a DataSet with information from the Products, Suppliers, and Categories tables. We created a LoadCompleteDataSetfunction to populate the DataSet that will be used in the application to store some values to populate the ComboBoxes. We sometimes refer to these as code tables. Notice how we get to make use of the generic functions we created before to populate the DataSet. We populate the Products, Suppliers, and Categories tables in the DataSet by calling the PopulateDataSetTable function and passing the proper parameters, one of them being the stored procedure to run to retrieve the records. `Create a Products table in the DataSet dsData = PopulateDataSetTable(strConnection, "Products", _ "spRetrieveProducts", blnRunStoredProc, dsData) `Create a Suppliers table in the DataSet dsData = PopulateDataSetTable(strConnection, "Suppliers", _ "spRetrieveSuppliers", blnRunStoredProc, dsData) `Create a Categories table in the DataSet dsData = PopulateDataSetTable(strConnection, "Categories", _ "spRetrieveCategories", blnRunStoredProc, dsData) `Create the relationship between Products and Suppliers tables 35 Chapter 7 dsData = PopulateDataSetRelationship("Suppliers", "Products", _ "SupplierId", "SupplierId", "ProductsVsSuppliers", _ dsData) `Create the relationship between Products and Categories tables dsData = PopulateDataSetRelationship("Categories", "Products", _ "CategoryId", "CategoryId", "ProductsVsCategories", _ dsData) WriteCompleteDataSetToOutputWindow(dsData) Stored procedures should be used to retrieve data whenever possible because they are pre-compiled on the database server and contain an execution plan which tells SQL Server how to execute them. This means that they execute faster than a SQL statement being passed on the fly to the database. Thus, retrieving values to populate our first DataSetwas handled using stored procedures instead of a SQL statement in Visual Basic .NET code. Later, we will look at an example of when you might need to use a SQL statement in the code instead of a stored procedure. Such cases occur typically when it would be extremely difficult, if not impossible, to determine the SQL statement up front such that it could be stored in a stored procedure. In instances like that, it makes sense to just create the SQL statement in the Visual Basic .NET code and pass the SQL statement to the database. After populating the DataSet, we then created the relationships between the tables. Near the end of the PopulateDataSetTable function is a call to the WriteCompleteDataSetToOutputWindow procedure. We can comment the call to this out later but, in this chapter, we keep it in to verify that the DataSet is being correctly populated with the results of the query. Let`s have a quick look at what this procedure accomplishes: Dim oRow As DataRow Dim strRecord As String `write some data in the Products table to the Output window `to show that the data is there. For Each oRow In dsData.Tables("Products").Rows strRecord = "Product Id: " & oRow("ProductId").ToString() strRecord = strRecord & " Product Name: " strRecord = strRecord & oRow("ProductName").ToString() strRecord = strRecord & " Supplier Id: " strRecord = strRecord & oRow("SupplierId").ToString() Console.WriteLine(strRecord) Next In this case, we used the DataRow object to manipulate the DataSet and output all rows but only certain columns to the Output window. Lastly, we added the UnhandledExceptionHandler to the clsDatabase class. This procedure will handle all unhandled exceptions that get raised in the clsDatabase class. This can be modified to handle errors in the clsDatabase class in whatever manner you desire. 36 ... - tailieumienphi.vn
nguon tai.lieu . vn