ordersxquery.vb

wrox出版社的另一套经典的VB2005数据库编程学习书籍,收集了书中源码,郑重推荐,电子书,电子书下载

			 "     <ShipFax>{sql:column(""Customers.Fax"")}</ShipFax>" + vbCrLf + _
			 "   else ()} " + vbCrLf
			strXQuery += strSqlCol + "</Order>') " + vbCrLf + "FROM Orders, Customers WHERE " + vbCrLf

			If blnSingleExists Then
				'Single exists statement for date range (almost twice as fast as below)
				strXQuery += "OrderXML1.exist('/Order/OrderDate[.>= xs:dateTime(""" + strBegDate + """) and " + vbCrLf + _
				"  .<= xs:dateTime(""" + strEndDate + """)]') = 1 AND " + vbCrLf
			Else
				'Two exists statements for date range
				strXQuery += "OrderXML1.exist('/Order[data(OrderDate) >= xs:dateTime(""" + strBegDate + """)]') = 1 AND" + vbCrLf + _
				"OrderXML1.exist('/Order[data(OrderDate) <= xs:dateTime(""" + strEndDate + """)]') = 1 AND" + vbCrLf
			End If

			'WHERE clause for join
			strXQuery += "Customers.CustomerID = OrderXML1.value('(/Order/CustomerID)[1]', 'nvarchar(5)'); "

			'Below repeats for XML2 version
			If rbOrderXML2.Checked Then
				strXQuery = "SELECT OrderXML2.query('for $Result in /nwso:SalesOrder return " + vbCrLf + _
				 "<SalesOrder>" + vbCrLf + _
				 "  <SalesOrderDate>{data($Result/@nwso:OrderDate)}</SalesOrderDate>" + vbCrLf + _
				 "  <SalesOrderNum>{data($Result/@nwso:OrderID)}</SalesOrderNum>" + vbCrLf + _
				 "  <Name>{data($Result/nwbt:BillTo/nwbt:Name)}</Name>" + vbCrLf
				strXQuery += strSqlCol + "</SalesOrder>') " + vbCrLf + "FROM Orders, Customers WHERE " + vbCrLf
				If blnSingleExists Then
					strXQuery += "OrderXML2.exist('/nwso:SalesOrder/@nwso:OrderDate[.>= xs:dateTime(""" + strBegDate + """) and " + vbCrLf + _
					 "  .<= xs:dateTime(""" + strEndDate + """)]') = 1 AND " + vbCrLf
				Else
					strXQuery += "OrderXML2.exist('/nwso:SalesOrder[data(@nwso:OrderDate) >= xs:dateTime(""" + strBegDate + """)]') = 1 AND " + vbCrLf + _
					"OrderXML2.exist('/nwso:SalesOrder[data(@nwso:OrderDate) <= xs:dateTime(""" + strEndDate + """)]') = 1 AND "
				End If
				strXQuery += "Customers.CustomerID = OrderXML2.value('(/nwso:SalesOrder/@nwso:CustomerID)[1]', 'nvarchar(5)'); "
				AddOrdersNamespaces(1, 1) 'nwso + nwbt for query
				AddOrdersNamespaces(0, 2) 'nwso for exists
				AddOrdersNamespaces(0, 4) 'nwso for value
			End If
			If blnSalesOrders Then
				strXQuery = Replace(strXQuery, "Orders", "SalesOrders")
			End If
			txtOrdersXQuery.Text = "-- Uses xs:date and multiple WHERE criteria for a date range (tests secondary indexes)" + vbCrLf + _
			 "-- Adds renamed fields from the Customers table with a value-based join, sql:column," + vbCrLf + _
			 "-- and an if ... then ... else conditional expression " + vbCrLf + vbCrLf + strXQuery + strClick
			If blnShowPlanXML Then
				If blnShowStatsXML And blnSalesOrders Then
					Dim strMsg As String = "Running STATISTICS XML on this query " + _
					"takes a minute or two with a fast machine. " + vbCrLf + vbCrLf + _
					"Click Yes if you want to execute STATISTICS XML; otherwise click No."
					If MsgBox(strMsg, MsgBoxStyle.Question Or MsgBoxStyle.YesNo, "About to Execute a Long-Running Query") = MsgBoxResult.Yes Then
						txtOrdersXQResult.Text = ShowPlanXML(strXQuery)
					Else
						txtOrdersXQResult.Text = ""
					End If
				Else
					txtOrdersXQResult.Text = ShowPlanXML(strXQuery)
				End If
			Else
				txtOrdersXQResult.Text = ""
			End If
			btnExecuteOrders.Focus()
			pbFillColumn.Value = 0
			pbFillColumn.Width = 291
			btnShowplan.Visible = False
			strOrdersXQuery = txtOrdersXQuery.Text
		End If
	End Sub

	Private Sub rbAggregationsF_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbAggregationsF.CheckedChanged
		If rbAggregationsF.Checked Then
			strXQueryName = "Aggregate Functions"
			blnXQueryEdited = False
			rbOrderXML2.Checked = True
			If rbOrderXML2.Checked Then
				strXQuery = "SELECT OrderXML2.query('declare namespace nwso=""http://www.northwind.com/schemas/SalesOrder""; for $Result in /nwso:SalesOrder return " + vbCrLf + _
				 "<SalesOrder> " + vbCrLf + _
				 "  <SalesOrderNum>{data($Result/@nwso:OrderID)}</SalesOrderNum> " + vbCrLf + _
				 "  <SalesOrderDate>{data($Result/@nwso:OrderDate)}</SalesOrderDate> " + vbCrLf + _
				 "  <LineItemsCount>{count($Result/nwso:LineItems/nwso:LineItem)}</LineItemsCount> " + vbCrLf + _
				 "  <LineItemsValue>" + vbCrLf + _
				 "    {concat(""$"", xs:string(round(sum($Result/nwso:LineItems/nwso:LineItem/nwso:Extended))))}" + vbCrLf + _
				 "  </LineItemsValue> " + vbCrLf + _
				 "  <LineItemsAverage>" + vbCrLf + _
				 "    {concat(""$"", xs:string(round(avg($Result/nwso:LineItems/nwso:LineItem/nwso:Extended))))}" + vbCrLf + _
				 "  </LineItemsAverage> " + vbCrLf + _
				 "</SalesOrder>') FROM Orders; "
				If blnSalesOrders Then
					strXQuery = Replace(strXQuery, "Orders", "SalesOrders")
				End If
				txtOrdersXQuery.Text = "-- Examples of count, sum, average, round, concat, and string functions " + _
				 vbCrLf + vbCrLf + strXQuery + strClick
				If blnShowPlanXML Then
					If blnShowStatsXML Then
						Dim strMsg As String = "Running STATISTICS XML on this query " + _
						"might take a minute or two with a fast machine. " + vbCrLf + vbCrLf + _
						"Click Yes if you want to execute STATISTICS XML; otherwise click No."
						If MsgBox(strMsg, MsgBoxStyle.Question Or MsgBoxStyle.YesNo, "About to Execute a Long-Running Query") = MsgBoxResult.Yes Then
							txtOrdersXQResult.Text = ShowPlanXML(strXQuery)
						Else
							txtOrdersXQResult.Text = ""
						End If
					Else
						txtOrdersXQResult.Text = ShowPlanXML(strXQuery)
					End If
				Else
					txtOrdersXQResult.Text = ""
				End If
				btnExecuteOrders.Focus()
			Else
				txtOrdersXQuery.Text = "-- Aggregate methods aren't applicable to Orders documents." + vbCrLf + _
				 "-- Please select the OrderXML2 column."
			End If
			pbFillColumn.Value = 0
			pbFillColumn.Width = 291
			btnShowplan.Visible = False
			strOrdersXQuery = txtOrdersXQuery.Text
		End If
	End Sub

	Private Sub rbRandomIDs_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbRandomIDs.CheckedChanged
		'Query for testing random row scans for index performance
		If rbRandomIDs.Checked Then
			strXQueryName = "Random IDs"
			blnXQueryEdited = False
			chkBytes.Enabled = False
			If blnResetMaxRows Then
				strMaxRows = txtMaxRows.Text
			End If
			Dim intOrderID1 As Integer
			Dim intOrderID2 As Integer
			If blnResetMaxRows Then
				txtMaxRows.Text = "10"
			End If
			intOrderID1 = intXML1Start - 1
			intOrderID2 = intXML2Start - 1

			Dim intMaxRows As Integer = CInt(txtMaxRows.Text)
			If rbOrderXML1.Checked Then
				If blnUseFLWOR Then
					strXQuery = "SELECT OrderXML1.query('for $Result in /Order " + _
					 "return $Result') FROM Orders " + _
					 "WHERE OrderXML1.exist('/Order/OrderID[.> " + _
					 (intOrderID1 - intMaxRows).ToString + " and .<= " + _
					 intOrderID1.ToString + "]') = 1;"
				Else
					strXQuery = "SELECT OrderXML1.query('(/Order[./OrderID > " + _
					  (intOrderID1 - intMaxRows).ToString + " and ./OrderID <= " + _
					 intOrderID1.ToString + "])') FROM Orders;"
				End If
			Else
				If blnUseFLWOR Then
					strXQuery = "SELECT OrderXML2.query('for $Result in /SalesOrder " + _
					 "return $Result') FROM Orders " + _
					 "WHERE OrderXML2.exist('/SalesOrder/@OrderID[.> " + _
					 (intOrderID2 - intMaxRows).ToString + " and .<= " + _
					 intOrderID2.ToString + "]') = 1;"
				Else
					strXQuery = "SELECT OrderXML2.query('(/SalesOrder[./@OrderID > " + _
					 (intOrderID2 - intMaxRows).ToString + " and ./@OrderID <= " + _
					 intOrderID2.ToString + "])') FROM Orders;"
				End If
				strXQuery = Replace(strXQuery, "/", "/nwso:")
				AddOrdersNamespaces(0, 1)
				AddOrdersNamespaces(0, 2)
				strXQuery = Replace(strXQuery, "nwso:@", "@nwso:")
			End If
			If blnSalesOrders Then
				strXQuery = Replace(strXQuery, "Orders", "SalesOrders")
			End If
			txtOrdersXQuery.Text = "-- Reads sequential document instances starting with random OrderID values" + vbCrLf + _
			  "-- " + intMaxRows.ToString + " instances read for each OrderID value (" + intMaxRows.ToString + " Max Rows = " + (intMaxRows * intMaxRows).ToString("#,##0") + " total instances.)" + _
			  vbCrLf + vbCrLf + strXQuery + vbCrLf + vbCrLf + "-- This query isn't editable. (Displays first 10 XQuery expressions and typical result.)"
			If blnSalesOrders Then
				txtOrdersXQuery.Text += vbCrLf + "-- If your SalesOrders table has a large number of rows, test 10 rows first."
			End If
			If blnShowPlanXML And blnResetMaxRows Then
				If blnShowStatsXML Then
					Dim strMsg As String = "Running STATISTICS XML on this query " + _
					"might take a minute or two with a fast machine. " + vbCrLf + vbCrLf + _
					"Click Yes if you want to execute STATISTICS XML; otherwise click No."
					If MsgBox(strMsg, MsgBoxStyle.Question Or MsgBoxStyle.YesNo, "About to Execute a Long-Running Query") = MsgBoxResult.Yes Then
						txtOrdersXQResult.Text = ShowPlanXML(strXQuery)
					Else
						txtOrdersXQResult.Text = ""
					End If
				Else
					txtOrdersXQResult.Text = ShowPlanXML(strXQuery)
				End If
			Else
				txtOrdersXQResult.Text = ""
			End If
			btnExecuteOrders.Focus()
			pbFillColumn.Value = 0
			pbFillColumn.Width = 291
			btnShowplan.Visible = False
			strOrdersXQuery = ""
		Else
			chkBytes.Enabled = True
			If blnResetMaxRows Then
				txtMaxRows.Text = strMaxRows
			End If
		End If
	End Sub

	Private Sub rbUpdateOrders_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbUpdateOrders.CheckedChanged
		If rbUpdateOrders.Checked Then
			strXQueryName = "Update (DML)"
			blnXQueryEdited = False
			'Typed xml columns require specifying the xs:datatype
			strXQuery = "UPDATE Orders SET OrderXML1.modify('replace value of (/Order/ShipVia/text())[1] " + _
			 "with ""1""') WHERE ShipCountry = 'USA' AND OrderXML1 IS NOT NULL;"
			If rbOrderXML2.Checked Then
				strXQuery = Replace(strXQuery, "XML1", "XML2")
				AddOrdersNamespaces(0, 3)
				strXQuery = Replace(strXQuery, "/Order/ShipVia", "/nwso:SalesOrder/@nwso:ShipperID")
				'Issue: If you don't remove '/text()' for an attribute value, you receive an
				'XQuery: Result of 'text()' expression is statically 'empty' exception

				'If you remove 'text()' for an element value, you recieve an
				'XQuery: The target of 'replace value of' must be a non-metadata attribute or an 
				'element with simple typed content, found 'element(ShipVia,xdt:untypedAny) ?' exception

				'Thus the following replacement is required for untyped xml attributes:
				strXQuery = Replace(strXQuery, "/text()", "")
				If chkOrderXML2Schema.Checked Then
					strXQuery = Replace(strXQuery, "/text()", "")
					'ShipperID is unsignedByte
					strXQuery = Replace(strXQuery, """1""", "xs:unsignedByte(1)")
				End If
			Else
				If chkOrderXML1Schema.Checked Then
					strXQuery = Replace(strXQuery, "/text()", "")
					'ShipVia is xs:int
					strXQuery = Replace(strXQuery, """1""", "xs:int(1)")
				End If
			End If
			If blnSalesOrders Then
				strXQuery = Replace(strXQuery, "Orders", "SalesOrders")
			End If
			Dim strText As String
			strText = "-- Replaces ShipVia value with 1 in specified OrderXML1 documents" + vbCrLf + _
			 "-- Uses xs:int type if the Orders table's OrderXML1 column has a schema."
			If rbOrderXML2.Checked Then
				strText = Replace(strText, "ShipVia", "ShipperID attribute")
				strText = Replace(strText, "xs:int", "xs:unsignedByte")
				strText = Replace(strText, "XML1", "XML2")
			End If
			If blnSalesOrders Then
				'Might take a long time
				cmNwind.CommandTimeout = 300
				strText = Replace(strText, "Orders", "SalesOrders")
			End If
			strText += vbCrLf + vbCrLf
			txtOrdersXQuery.Text = strText + strXQuery + Replace(strClick, "return the specified data or documents", "update the specified documents")
			If blnShowPlanXML Then
				If blnShowStatsXML Then
					Dim strMsg As String = "Running STATISTICS XML on this query with XML indexes " + _
					 "might take a minute or two with a fast machine. " + vbCrLf + vbCrLf + _
					 "Click Yes if you want to execute STATISTICS XML; otherwise click No."
					If MsgBox(strMsg, MsgBoxStyle.Question Or MsgBoxStyle.YesNo, "About to Execute a Long-Running Query") = MsgBoxResult.Yes Then
						txtOrdersXQResult.Text = ShowPlanXML(strXQuery)
					Else
						txtOrdersXQResult.Text = ""
					End If
				Else
					txtOrdersXQResult.Text = ShowPlanXML(strXQuery)
				End If
			Else
				txtOrdersXQResult.Text = ""
			End If
			txtMaxRows.ReadOnly = True
			btnExecuteOrders.Focus()
			pbFillColumn.Value = 0
			pbFillColumn.Width = 291
			btnShowplan.Visible = False
			strOrdersXQuery = txtOrdersXQuery.Text
		Else
			txtMaxRows.ReadOnly = False
			cmNwind.CommandTimeout = 30
		End If
	End Sub

    Private Sub AddOrdersNamespaces(ByVal intNS As Integer, ByVal intMethod As Integer)
        'Revised to require semicolons separators (not spaces) for namespaces
        strXQuery = Replace(strXQuery, "XML1", "XML2")
        Dim strNwso As String = "declare namespace nwso=""http://www.northwind.com/schemas/SalesOrder"""
        Dim strNwbt As String = "declare namespace nwbt=""http://www.northwind.com/schemas/BillTo"""
        Dim strNwsc As String = "declare namespace nwsc=""http://www.northwind.com/schemas/SalesContact"""
        Dim strNwst As String = "declare namespace nwst=""http://www.northwind.com/schemas/ShipTo"""
        Dim strNS As String = Nothing
        Select Case intNS
            Case 0
                strNS = strNwso
            Case 1
                strNS = strNwso + "; " + strNwbt
            Case 2
                strNS = strNwso + "; " + strNwsc