📄 ordersxquery.vb
字号:
Option Explicit On
Option Strict On
Imports System.Xml
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Text
Partial Public Class NwXmlCols
Private strMaxRows As String
Private intNewOrderID As Integer
Private datBegDate As DateTime
Private datEndDate As DateTime
Private blnResetMaxRows As Boolean = True
'Use FLWOR expression for Random IDs
Private blnUseFLWOR As Boolean = My.Settings.UseFLWORForRandomIDs
'Returned by GetXQueryResult if blnUseFLWOR is False
Private intEmptyRows As Integer
'For testing cache status of XQuery SELECT statements
Private blnCheckCache As Boolean = My.Settings.CheckCacheState
Private blnClearCache As Boolean = My.Settings.ClearQueryCache
Private strCacheMsg As String = ""
'To test for edits of SQL/XQuery text box
Private strOrdersXQuery As String
'For Showplans table
Private strXQueryName As String
Private blnXQueryEdited As Boolean
'Procedures and functions for Orders and SalesOrders XQuery examples
Private Sub rbFindLastOrderById_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbFindLastOrderById.CheckedChanged
If rbFindLastOrderById.Checked Then
strXQueryName = "Last by ID"
blnXQueryEdited = False
If intXML1Start = 0 Then
'Required to handle preserved Orders table
If alOrderIDs.Count > 0 Then
'Count is 0 when form starts up
intXML1Start = CInt(alOrderIDs(alOrderIDs.Count - 1)) + 1
intXML2Start = intXML1Start
End If
End If
strXQuery = "SELECT OrderXML1.query('(/Order[/Order/OrderID=" + _
(intXML1Start - 1).ToString + "])') FROM Orders;"
If rbOrderXML2.Checked Then
strXQuery = "SELECT OrderXML2.query('(/SalesOrder[/SalesOrder/@OrderID=" + _
(intXML2Start - 1).ToString + "])') FROM Orders;"
strXQuery = Replace(strXQuery, "/", "/nwso:")
AddOrdersNamespaces(0, 1)
strXQuery = Replace(strXQuery, "nwso:@", "@nwso:")
End If
If blnSalesOrders Then
If rbOrderXML2.Checked Then
strXQuery = Replace(strXQuery, "Orders;", "SalesOrders WHERE OrderXML2 IS NOT NULL;")
Else
strXQuery = Replace(strXQuery, "Orders;", "SalesOrders WHERE OrderXML1 IS NOT NULL;")
End If
'The preceding XPath query doesn't work for SalesOrders (it does for Orders); not sure why
'Following is a temporary FLWOR workaround on a promoted column
strXQuery = "SELECT OrderXML1.query('for $Result in /Order return $Result') " + _
"FROM SalesOrders WHERE OrderID = " + (intXML1Start - 1).ToString + ";"
If rbOrderXML2.Checked Then
strXQuery = "SELECT OrderXML2.query('for $Result in /SalesOrder return $Result') " + _
"FROM SalesOrders WHERE OrderID = " + (intXML2Start - 1).ToString + ";"
strXQuery = Replace(strXQuery, "/", "/nwso:")
AddOrdersNamespaces(0, 1)
End If
End If
Dim strPrefix As String = Nothing
If blnSalesOrders Then
strPrefix = "-- Last SalesOrder with FLWOR expression"
txtOrdersXQuery.Text += strClick
Else
txtOrdersXQuery.Text += vbCrLf + vbCrLf
If rbOrderXML1.Checked Then
strPrefix = "-- Last Order by OrderID element predicate (not FLWOR)"
Else
strPrefix = "-- Last Order by OrderID attribute predicate (not FLWOR)"
End If
End If
strPrefix += vbCrLf + vbCrLf
txtOrdersXQuery.Text = strPrefix + strXQuery + strClick
If blnShowPlanXML Then
txtOrdersXQResult.Text = ShowPlanXML(strXQuery)
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 rbShredWithNodes_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbFindLastOrderByIdF.CheckedChanged
'Changed to .nodes shred operation after adding Showplan DataGridView
If rbFindLastOrderByIdF.Checked Then
strXQueryName = "Nodes (Shred)"
blnXQueryEdited = False
chkUseOrdersSqlXml.Checked = False
If rbOrderXML1.Checked Then
strXQuery = "SELECT Columns.value('./OrderID[1]', 'int') AS OrderID, " + vbCrLf + _
" Columns.value('./CustomerID[1]', 'nvarchar(5)') AS CustomerID, " + vbCrLf + _
" Columns.value('./EmployeeID[1]', 'int') AS EmployeeID, " + vbCrLf + _
" CONVERT(datetime, Columns.value('./OrderDate[1]', 'varchar(19)'), 126) " + vbCrLf + _
" AS OrderDate, " + vbCrLf + _
" CONVERT(datetime, Columns.value('./RequiredDate[1]', 'varchar(19)'), 126) " + vbCrLf + _
" AS RequiredDate, " + vbCrLf + _
" CONVERT(datetime, Columns.value('./ShippedDate[1]', 'varchar(19)'), 126) " + vbCrLf + _
" AS ShippedDate, " + vbCrLf + _
" Columns.value('./ShipVia[1]', 'int') AS ShipVia, " + vbCrLf + _
" Columns.value('./Freight[1]', 'money') AS Freight, " + vbCrLf + _
" Columns.value('./ShipName[1]', 'nvarchar(40)') AS ShipName, " + vbCrLf + _
" Columns.value('./ShipAddress[1]', 'nvarchar(60)') AS ShipAddress, " + vbCrLf + _
" Columns.value('./ShipCity[1]', 'nvarchar(15)') AS ShipCity, " + vbCrLf + _
" Columns.value('./ShipRegion[1]', 'nvarchar(15)') AS ShipRegion, " + vbCrLf + _
" Columns.value('./ShipPostalCode[1]', 'nvarchar(10)') AS ShipPostalCode, " + vbCrLf + _
" Columns.value('./ShipCountry[1]', 'nvarchar(10)') AS ShipCountry " + vbCrLf + _
"FROM Orders " + vbCrLf + _
"CROSS APPLY OrderXML1.nodes('/Order') AS RowSet(Columns) " + vbCrLf + _
"WHERE OrderXML1.exist('/Order[data(CustomerID)=""RATTC""]') = 1 " + vbCrLf + _
"ORDER BY OrderXML1.value('/Order[1]/OrderID[1]', 'int') DESC;"
Else
'Add namespace declarations to *everything*
strXQuery = "SELECT Columns.value('declare namespace nwso=""http://www.northwind.com/schemas/SalesOrder""; ./@nwso:OrderID[1]', 'int') AS OrderID, " + vbCrLf + _
" Columns.value('declare namespace nwso=""http://www.northwind.com/schemas/SalesOrder""; ./@nwso:CustomerID[1]', 'nvarchar(5)') AS CustomerID, " + vbCrLf + _
" Columns.value('declare namespace nwso=""http://www.northwind.com/schemas/SalesOrder""; ./@nwso:EmployeeID[1]', 'int') AS EmployeeID, " + vbCrLf + _
" CONVERT(datetime, Columns.value('declare namespace nwso=""http://www.northwind.com/schemas/SalesOrder""; ./@nwso:OrderDate[1]', 'varchar(19)'), 126) AS OrderDate, " + vbCrLf + _
" CONVERT(datetime, Columns.value('declare namespace nwso=""http://www.northwind.com/schemas/SalesOrder""; ./nwso:OrderDates[1]/nwso:RequiredDate[1]', 'varchar(19)'), 126) AS RequiredDate, " + vbCrLf + _
" Columns.value('declare namespace nwso=""http://www.northwind.com/schemas/SalesOrder""; ./@nwso:ShipperID[1]', 'int') AS ShipVia, " + vbCrLf + _
" Columns.value('declare namespace nwso=""http://www.northwind.com/schemas/SalesOrder""; ./nwso:Shipment[1]/nwso:EstimatedFreight[1]', 'money') AS Freight, " + vbCrLf + _
" Columns.value('declare namespace nwst=""http://www.northwind.com/schemas/ShipTo""; ./nwst:ShipTo[1]/nwst:Name[1]', 'nvarchar(40)') AS ShipName, " + vbCrLf + _
" Columns.value('declare namespace nwst=""http://www.northwind.com/schemas/ShipTo""; ./nwst:ShipTo[1]/nwst:Address[1]', 'nvarchar(60)') AS ShipAddress, " + vbCrLf + _
" Columns.value('declare namespace nwst=""http://www.northwind.com/schemas/ShipTo""; ./nwst:ShipTo[1]/nwst:City[1]', 'nvarchar(15)') AS ShipCity, " + vbCrLf + _
" Columns.value('declare namespace nwst=""http://www.northwind.com/schemas/ShipTo""; ./nwst:ShipTo[1]/nwst:Region[1]', 'nvarchar(15)') AS ShipRegion, " + vbCrLf + _
" Columns.value('declare namespace nwst=""http://www.northwind.com/schemas/ShipTo""; ./nwst:ShipTo[1]/nwst:PostalCode[1]', 'nvarchar(10)') AS ShipPostalCode, " + vbCrLf + _
" Columns.value('declare namespace nwst=""http://www.northwind.com/schemas/ShipTo""; ./nwst:ShipTo[1]/nwst:Country[1]', 'nvarchar(10)') AS ShipCountry " + vbCrLf + _
"FROM Orders " + vbCrLf + _
"CROSS APPLY OrderXML2.nodes('declare namespace nwso=""http://www.northwind.com/schemas/SalesOrder""; /nwso:SalesOrder') AS RowSet(Columns) " + vbCrLf + _
"WHERE OrderXML2.exist('declare namespace nwso=""http://www.northwind.com/schemas/SalesOrder""; /nwso:SalesOrder[data(@nwso:CustomerID)=""RATTC""]') = 1 " + vbCrLf + _
"ORDER BY OrderXML2.value('declare namespace nwso=""http://www.northwind.com/schemas/SalesOrder""; /nwso:SalesOrder[1]/@nwso:OrderID[1]', 'int') DESC;"
End If
If blnSalesOrders Then
strXQuery = Replace(strXQuery, "Orders", "SalesOrders")
End If
Dim strPrefix As String = "-- Uses the xml.nodes method to shred the instance into a relational rowset (not FLWOR)" + vbCrLf + _
"-- A DataGridView displays the rowset (Note: ORDER BY and DESC are case-sensitive)" + vbCrLf + vbCrLf
txtOrdersXQuery.Text = strPrefix + strXQuery + strClick
If blnShowPlanXML Then
txtOrdersXQResult.Text = ShowPlanXML(strXQuery)
Else
txtOrdersXQResult.Text = ""
End If
btnExecuteOrders.Focus()
pbFillColumn.Value = 0
pbFillColumn.Width = 291
btnShowplan.Visible = False
strOrdersXQuery = txtOrdersXQuery.Text
Else
chkUseOrdersSqlXml.Checked = True
End If
End Sub
Private Sub rbGetAllOrdersF_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbGetAllOrdersF.CheckedChanged
If rbGetAllOrdersF.Checked Then
strXQueryName = "Get All"
blnXQueryEdited = False
strXQuery = "SELECT OrderXML1.query('for $Result in /Order return $Result') " + _
"FROM Orders;"
If rbOrderXML2.Checked Then
strXQuery = "SELECT OrderXML2.query('for $Result in /SalesOrder return $Result') " + _
"FROM Orders;"
strXQuery = Replace(strXQuery, "/", "/nwso:")
AddOrdersNamespaces(0, 1)
End If
If blnSalesOrders Then
If rbOrderXML2.Checked Then
strXQuery = Replace(strXQuery, "Orders;", "SalesOrders WHERE OrderXML2 IS NOT NULL;")
Else
strXQuery = Replace(strXQuery, "Orders;", "SalesOrders WHERE OrderXML1 IS NOT NULL;")
End If
End If
Dim strPrefix As String = "-- Returns the number of "
If blnSalesOrders Or rbOrderXML2.Checked Then
strPrefix += "SalesOrder "
Else
strPrefix += "Order "
End If
strPrefix += "documents specified by the Max. Rows text box" + vbCrLf + vbCrLf
txtOrdersXQuery.Text = strPrefix + strXQuery + strClick
If blnShowPlanXML Then
If blnShowStatsXML And blnSalesOrders Then
Dim strMsg As String = "Running STATISTICS XML on this query " + _
"takes a while even 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 rbFindByOrderCountryF_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbFindByOrderCountryF.CheckedChanged
If rbFindByOrderCountryF.Checked Then
strXQueryName = "Find by Country"
blnXQueryEdited = False
strXQuery = "SELECT OrderXML1.query('for $Result in /Order return $Result') " + _
"FROM Orders WHERE OrderXML1.exist('/Order[data(ShipCountry)=""USA""]') = 1;"
If rbOrderXML2.Checked Then
strXQuery = "SELECT OrderXML2.query('for $Result in /nwso:SalesOrder/nwbt:BillTo return $Result') " + _
"FROM Orders WHERE OrderXML2.exist('/nwso:SalesOrder/nwbt:BillTo[data(nwbt:Country)=""USA""]') = 1;"
AddOrdersNamespaces(1, 1) 'nwso + nwbt for query
AddOrdersNamespaces(1, 2) 'nwso + nwbt for exists
End If
If blnSalesOrders Then
strXQuery = Replace(strXQuery, "Orders", "SalesOrders")
End If
Dim strPrefix As String = "-- Uses the 'exists' method as a WHERE clause qualifier" + vbCrLf
If rbOrderXML2.Checked Then
strPrefix += "-- Uses multiple namespaces with the 'exists' method" + vbCrLf
End If
strPrefix += vbCrLf
txtOrdersXQuery.Text = strPrefix + strXQuery + strClick
If blnShowPlanXML And Not blnFromUpdate Then
'Don't run SHOWPLAN_XML if from Update (DDL) query
If blnShowStatsXML And blnSalesOrders Then
Dim strMsg As String = "Running STATISTICS XML on this query " + _
"takes a while, even 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 rbFindByOrderDatesF_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbFindByOrderDatesF.CheckedChanged
If rbFindByOrderDatesF.Checked Then
strXQueryName = "Find by Dates"
blnXQueryEdited = False
Dim strBegDate As String = "1996-07-10T00:00:00Z"
Dim strEndDate As String = "1997-07-10T00:00:00Z"
If blnSalesOrders Then
Try
cmNwind.CommandText = "SELECT MIN(OrderDate) FROM SalesOrders " + _
"WHERE OrderXML1 IS NOT NULL AND OrderXML2 IS NOT NULL; "
cnNwind.Open()
datBegDate = DateTime.Parse(cmNwind.ExecuteScalar.ToString)
strBegDate = datBegDate.ToString("s") + "Z"
strEndDate = datBegDate.AddMonths(1).AddDays(-1).ToString("s") + "Z"
cnNwind.Close()
Catch exc As Exception
txtOrdersXQResult.Text = exc.Message
Return
Finally
cnNwind.Close()
End Try
End If
'Following compares execution time of single vs. double exist tests
Dim blnSingleExists As Boolean = My.Settings.UseSingleExistsForTest
strXQuery = "SELECT OrderXML1.query('for $Result in /Order return " + vbCrLf + _
"<Order>" + vbCrLf + _
" <OrderDate>{data($Result/OrderDate)}</OrderDate>" + vbCrLf + _
" <OrderID>{data($Result/OrderID)}</OrderID>" + vbCrLf + _
" <ShipName>{data($Result/ShipName)}</ShipName>" + vbCrLf
'Add Phone sql:Column and text Fax sql:column
Dim strSqlCol As String = " <ShipPhone>{sql:column(""Customers.Phone"")}</ShipPhone>" + vbCrLf + _
" {if (not(empty(sql:column(""Customers.Fax"")))) then " + vbCrLf + _
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -