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