Beginning Visual Basic .NET Database Programming phần 8

ADO.NET and XML Relational Data At this point we know how to load and save XML data directly using the DataSet. We also know how to generate schemas and how to use those schemas for validation. However, at this point, we`ve only seen what happens when we have a single table. As we`ve already seen in earlier chapters, the DataGrid control is capable of letting us navigate around different tables pretty easily. In this section we`ll extend what we have so that, when we have an order shown in the DataGrid, we can drill down to see the lines that make up that order. Try It Out – Relating the "Order Details" Table 1. If the project is running, close it. 2. Using the code editor, find the code for Form1. Make these changes to btnConnect_Click. Private Sub btnConnect_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnConnect.Click ` Connect to the database... Dim connection As New sqlconnection(DbString) connection.Open() ` Create a new dataset Dim newDataset As New DataSet("Orders") ` Create a new table to hold the orders in... Dim ordersTable As New DataTable("Order") newDataset.Tables.Add(ordersTable) ` Load the last two orders from the database... Dim command As New SqlCommand("SELECT TOP 2 OrderID, CustomerID, " & _ "EmployeeID, OrderDate, RequiredDate, ShippedDate, " & _ "ShipVia, Freight, ShipName, ShipAddress, ShipCity, " & _ "ShipRegion, ShipPostalCode, ShipCountry FROM Orders " & _ "ORDER BY OrderID DESC", connection) ` Fill the dataset... Dim adapter As New SqlDataAdapter(command) adapter.Fill(ordersTable) ` Create a new table to hold the order details on... Dim detailsTable As New DataTable("Detail") newDataset.Tables.Add(detailsTable) ` Form a SQL string so that we only get the details that are ` included in the first table... Dim sql As String, row As DataRow For Each row In ordersTable.Rows ` Create a sql snippet... If sql <> "" Then sql &= " or " 25 Chapter 12 sql &= "OrderID=" & row("orderid") Next ` Do we need to bother? If sql <> "" Then ` Create a new command... sql = "SELECT OrderID, ProductID, UnitPrice, Quantity, Discount " & _ "FROM [Order Details] WHERE " & sql Dim detailsCommand As New SqlCommand(sql, connection) ` Fill the new table... Dim detailsAdapter As New SqlDataAdapter(detailsCommand) detailsAdapter.Fill(detailsTable) ` Create the new relationship... newDataset.Relations.Add("Details", _ ordersTable.Columns("OrderID"), detailsTable.Columns("OrderID")) End If ` Set the dataset property... Me.DataSet = newDataset ` Save the schema... newDataset.WriteXmlSchema(SchemaFilename) ` Close the database... connection.Close() End Sub 3. Run the project and click Connect. You`ll be able to use the "plus" buttons to show the Details link. 4. If you click on one of the Details links, you`ll be able to see the related data. 26 ADO.NET and XML How It Works None of that should be too new to you, as I`m sure you`re comfortable with linking tables with DataRelationobjects in this way. (See Chapter 7 for more details.) It`s worth taking a quick look at a portion of the code that we added. When we load a list of orders from the database into ordersTable, we need to load corresponding details in detailsTable. The way we do this is by looping through all the rows in ordersTable and creating a SQL snippet. ` Form a SQL string so that we only get the details that are ` included in the first table... Dim sql As String, row As DataRow For Each row In ordersTable.Rows ` Create a sql snippet... If sql <> "" Then sql &= " or " sql &= "OrderID=" & row("orderid") Next Once we`ve been through that loop, our sql variable will be set to: OrderID=11077 or OrderID=11076 We can combine this with the larger SQL statement to get this: SELECT OrderID, ProductID, UnitPrice, Quantity, Discount FROM [Order Details] WHERE OrderID=11077 OR OrderID=11076 …and that`s precisely what we do next. ` Do we need to bother? If sql <> "" Then ` Create a new command... sql = "SELECT OrderID, ProductID, UnitPrice, Quantity, Discount " & _ "FROM [Order Details] WHERE " & sql Dim detailsCommand As New SqlCommand(sql, connection) 27 Chapter 12 Once we have the command, we can populate the detailsTableDataTable object as normal: ` Fill the new table... Dim detailsAdapter As New SqlDataAdapter(detailsCommand) detailsAdapter.Fill(detailsTable) Finally, we create a relationship so that DataGrid knows how to present the data: ` Create the new relationship... newDataset.Relations.Add("Details", _ ordersTable.Columns("OrderID"), detailsTable.Columns("OrderID")) End If Now that we`ve proven we can load relational data, we need to look at what effect this has on our code to write the XML file. Saving the DataSet Saving the data isn`t even worth a "Try It Out…How It Works"! Run the project, click Connect, and then click Save. Save the file with a different name to the one you used before. Now find the file in Windows Explorer and open it. I`ve omitted quite a bit of XML here for brevity. 11077 RATTC 1 1998-05-06T00:00:00.0000000+01:00 1998-06-03T00:00:00.0000000+01:00 2 8.53 Rattlesnake Canyon Grocery 2817 Milton Dr. Albuquerque NM 87110 USA … … … 28 ADO.NET and XML … 11077 2 19 24 0.2 … First of all, what`s important here is noticing that we don`t need to change the code that saves the DataSetas an XML file, even though we`ve changed the structure of the DataSet. What`s also important is that there`s no physical link in the XML file between details and orders. It would make sense that the three Detail elements associated with order 11077 actually appeared within the Order element for 11077. But, for now, just bear in mind that we`ve changed the structure of the DataSet, yet the data can still be written out as XML without any changes. Loading the DataSet Again OK, so when we clicked the Connect button, we made a call to DataSet.WriteXmlSchema and created a new schema. This schema contains the details for the Detailelements and also contains details of the relationship between Order and Detail elements. If you open the OrdersSchema.xml file again, towards the bottom you`ll find this: Again, nothing complex there, we`re just defining an element called Detail and specifying the five elements that it contains. Notice that Detail and Orderappear as sibling elements in the document – they`re at the same level. They`ll appear as siblings to each other in the final XML document too. 29 ... - tailieumienphi.vn