nwxmlcols.vb

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

			Dim strSQL As String = "CREATE PRIMARY XML INDEX pidx_" + strColName + _
			 " ON " + strTableName + " (" + strColName + "); " + vbCrLf
			strSQL += "CREATE XML INDEX sidx_path_" + strColName + " ON " + strTableName + _
			 " (" + strColName + ") USING XML INDEX pidx_" + strColName + " FOR PATH; " + vbCrLf
			strSQL += "CREATE XML INDEX sidx_value_" + strColName + " ON " + strTableName + _
			 " (" + strColName + ") USING XML INDEX pidx_" + strColName + " FOR VALUE; " + vbCrLf
			strSQL += "CREATE XML INDEX sidx_prop_" + strColName + " ON " + strTableName + _
			 " (" + strColName + ") USING XML INDEX pidx_" + strColName + " FOR PROPERTY; " + vbCrLf
			cmNwind.CommandText = strSQL
			cnNwind.Open()
			objTimer.Start()
			cmNwind.ExecuteNonQuery()
			txtTime.Text = objTimer.ElapsedTime.ToString("#0.000")
			objTimer.Done()
			cnNwind.Close()
			Return "-- Click Fill " + strColName + " to repopulate the column" + vbCrLf + strSQL
		Catch exc As Exception
			Return exc.Message
		Finally
			cnNwind.Close()
		End Try
	End Function

	Private Function GetXMLIndexStats(ByVal strTableName As String, ByVal strColName As String) As String
		'Get index statistics and defragment by rebuilding or reorganzing indexes, if necessary
        Try
            'Updated for changes to sys.dm_db_index_physical_stats 
            Dim strSQL As String = "SELECT object_id FROM sys.objects WHERE name = '" + strTableName + "';"
            Dim strObjectID As String
            Dim strStats As String = Nothing
            Dim strIndex As String = Nothing
            cmNwind.CommandText = strSQL
            cnNwind.Open()
            strObjectID = cmNwind.ExecuteScalar().ToString
            'Join required to return index name
            strSQL = "SELECT ix.name, dm.index_type_desc, dm.page_count, dm.avg_fragmentation_in_percent " + _
            "FROM sys.dm_db_index_physical_stats (DB_ID(), " + strObjectID + ", NULL, NULL, 'LIMITED') AS dm " + _
            "INNER JOIN sys.indexes ix ON dm.object_id = ix.object_id AND dm.index_id = ix.index_id " + _
            "ORDER BY ix.index_id;"
            cmNwind.CommandText = strSQL
            Dim sdrStats As SqlDataReader = cmNwind.ExecuteReader
            Dim intReorg As Integer
            Dim intRebuild As Integer
            With sdrStats
                If .HasRows Then
                    While .Read
                        'Test for index fragmentation of XML indexes with more than a few pages
                        'Microsoft recommends rebuilding for fragmentation > 30
                        If .GetString(1).Contains("idx_") Then
                            If .GetDouble(3) > 30 And .GetInt64(2) > 25 Then
                                intRebuild += 1
                            ElseIf .GetDouble(3) > 10 And .GetInt64(2) > 50 Then
                                intReorg += 1
                            End If
                        End If
                        strStats += "Index Name: " + .GetString(0) + ", Type: " + _
                         .GetString(1) + ", Pages: " + .GetInt64(2).ToString("#,##0") + _
                         ", Avg. Frag: " + .GetDouble(3).ToString("#0.00") + "%"
                        strStats += vbCrLf
                    End While
                End If
                .Close()
            End With
            strIndex = strSQL
            If intReorg + intRebuild > 0 And strTableName <> "Customers" Then
                'Customers fragmentation isn't important
                txtOrderData.Text = strStats
                Dim strMsg As String = Nothing
                Dim intRetVal As Integer
                If intRebuild > 0 Then
                    strMsg += intRebuild.ToString + " XML index(es) have average fragmentation " + _
                     "> 30 and should be rebuilt offline."
                    strMsg += vbCrLf + vbCrLf
                End If
                If intReorg > 0 Then
                    strMsg += intReorg.ToString + " XML index(es) have average fragmentation " + _
                     "> 10 and should be reorganized. " + vbCrLf + vbCrLf
                End If
                If chkUseSalesOrders.Checked Then
                    strMsg += "Note: Fragmented XML indexes can affect performance. " + _
                     "Rebuilding or reorganizing all indexes might take a few minutes " + _
                     "if your SalesOrders table has a large number of XML documents. " + _
                     "The CommandTimout is set to 600 seconds." + vbCrLf + vbCrLf
                End If
                If intReorg > 0 And intRebuild > 0 Then
                    strMsg += "Click Yes to rebuild, click No to reorganize, or click Cancel to skip."
                    intRetVal = MsgBox(strMsg, MsgBoxStyle.Question Or MsgBoxStyle.YesNoCancel, "Rebuilding Indexes Recommended")
                    If intRetVal = MsgBoxResult.Yes Then
                        intRetVal = 2
                    ElseIf intRetVal = MsgBoxResult.No Then
                        intRetVal = 1
                    Else
                        intRetVal = 0
                    End If
                ElseIf intRebuild > 0 Then
                    strMsg += "Click OK to rebuild all indexes or click Cancel to skip."
                    intRetVal = MsgBox(strMsg, MsgBoxStyle.Question Or MsgBoxStyle.OKCancel, "Rebuilding Indexes Recommended")
                    If intRetVal = MsgBoxResult.OK Then
                        intRetVal = 2
                    Else
                        intRetVal = 0
                    End If
                ElseIf intReorg > 0 Then
                    strMsg += "Click OK to reorganize all indexes or click Cancel to skip."
                    intRetVal = MsgBox(strMsg, MsgBoxStyle.Question Or MsgBoxStyle.OKCancel, "Reorganizing Indexes Recommended")
                    If intRetVal = MsgBoxResult.OK Then
                        intRetVal = 1
                    Else
                        intRetVal = 0
                    End If
                End If
                If intRetVal = 0 Then
                    'Skip rebuild or reorganize
                    Return strStats
                End If
                strSQL = "ALTER INDEX ALL ON " + strTableName
                If intRetVal = 2 Then
                    'Rebuild indexes
                    strSQL += " REBUILD WITH (ONLINE = OFF); "
                ElseIf intRetVal = 1 Then
                    'Reorganize indexes
                    strSQL += " REORGANIZE ;"
                End If
                txtOrderSQL.Text = strSQL
                Application.DoEvents()
                cmNwind.CommandTimeout = 600
                cmNwind.CommandText = strSQL
                Me.Cursor = Cursors.WaitCursor
                Try
                    'Execute the rebuild/reorg command
                    cmNwind.ExecuteNonQuery()
                    'Get the updated stats
                    strStats += vbCrLf + "Rebuilt or reorganized index stats:" + vbCrLf
                    cmNwind.CommandText = strIndex
                    sdrStats = cmNwind.ExecuteReader
                    With sdrStats
                        If .HasRows Then
                            While .Read
                                strStats += "Index Name: " + .GetString(1) + ", Type: " + _
                                .GetString(1) + ", Pages: " + .GetInt64(2).ToString("#,##0") + _
                                 ", Avg. Frag: " + .GetDouble(3).ToString("#0.00")
                                strStats += vbCrLf
                            End While
                        End If
                        .Close()
                    End With
                Catch exc As Exception
                    Return exc.Message
                Finally
                    cmNwind.CommandTimeout = 30
                    Me.Cursor = Cursors.Default
                End Try
            End If
            cnNwind.Close()
            blnGetIndexStats = False
            Return strStats
        Catch exc As Exception
            Return exc.Message
        Finally
            cnNwind.Close()
        End Try
	End Function

	Private Function DropXMLIndexes(ByVal strTableName As String, ByVal strColName As String, ByVal blnIsOpen As Boolean) As String
		'Drop all four XML indexes
		Dim intIndex As Integer
		Dim strSQL As String = Nothing
		Dim strDrop As String = Nothing
		If cnNwind.State <> ConnectionState.Open Then
			cnNwind.Open()
		End If
		If strTableName = "SalesOrders" Then
			strColName = Replace(strColName, "Order", "SalesOrder")
		End If
		For intIndex = 0 To 3
			Select Case intIndex
				Case 0
					strDrop = "DROP INDEX sidx_prop_" + strColName + " ON " + strTableName + "; "
				Case 1
					strDrop = "DROP INDEX sidx_value_" + strColName + " ON " + strTableName + "; "
				Case 2
					strDrop = "DROP INDEX sidx_path_" + strColName + " ON " + strTableName + "; "
				Case 3
					'Primary key index must be dropped last
					strDrop = "DROP INDEX pidx_" + strColName + " ON " + strTableName + "; "
				Case 4
					'Temporary for wrong index created
					strColName = Replace(strColName, "Order", "SalesOrder")
					strDrop = "DROP INDEX pidx_" + strColName + " ON " + strTableName + "; "
			End Select
			cmNwind.CommandText = strDrop
			Try
				cmNwind.ExecuteNonQuery()
				strDrop += " (Dropped)"
			Catch exc As Exception
				'Missing indexes
				strDrop += " (Missing)"
			End Try
			strSQL += strDrop + vbCrLf
			If strTableName.Contains("Orders") Then
				If strColName.Contains("XML1") Then
					chkOrderXML1IndexPath.Checked = False
					chkOrderXML1IndexValue.Checked = False
					chkOrderXML1IndexProp.Checked = False
				Else
					chkOrderXML2IndexPath.Checked = False
					chkOrderXML2IndexValue.Checked = False
					chkOrderXML2IndexProp.Checked = False
				End If
			End If
		Next
		If Not blnIsOpen Then
			cnNwind.Close()
		End If
		Return "-- Click Fill " + strColName + " to repopulate the column" + vbCrLf + strSQL
	End Function

	Private Sub chkBytes_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkBytes.CheckedChanged
		If chkBytes.Checked Then
			lblRows.Text = "KBytes:"
			If blnIsOrderXML2 Then
				lblRowsPerSec.Text = "KB/Sec:"
			Else
				lblRowsPerSec.Text = "~KB/Sec:"
			End If
			If txtTime.Text.Length > 0 Then
				Dim dblKB As Double
				dblKB = lngBytes / 1000
				If dblKB > 1.0 Then
					txtRows.Text = dblKB.ToString("#0.0")
				Else
					txtRows.Text = dblKB.ToString("0.000")
				End If
				txtRowsPerSec.Text = (dblKB / dblTime).ToString("#0.0")
			End If
		Else
			lblRows.Text = "Rows:"
			lblRowsPerSec.Text = "Rows/Sec:"
			If txtTime.Text.Length > 0 Then
				txtRows.Text = intRows.ToString
				txtRowsPerSec.Text = (intRows / dblTime).ToString("#0.0")
			End If
		End If
	End Sub

	Private Sub tabNwindXml_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles tabNwindXml.SelectedIndexChanged
		dgvShowPlan.Visible = False
		If tabNwindXml.SelectedIndex = 1 Then
			'Customers XQuery page
			TestCustomerXMLDocuments(False)
			ClearTimingData()
		ElseIf tabNwindXml.SelectedIndex = 3 Then
			'Orders/SalesOrders XQuery page
			Dim blnShowplans As Boolean = blnInsertShowplans
			'blnInsertShowplans = False
			TestCustomerXMLDocuments(True)
			TestOrderXMLDocuments()
			ClearTimingData()
			'Refresh the XQuery text box contents
			Dim intCtr As Integer
			Dim rbSel As RadioButton
			With gbOrdersXQuery
				For intCtr = 0 To .Controls.Count - 1
					Dim objSel As Object = .Controls(intCtr)
					If TypeOf .Controls(intCtr) Is RadioButton Then
						rbSel = CType(.Controls(intCtr), RadioButton)
						If rbSel.Checked Then
							If Not rbSel.Name.Contains("rbOrderXML") Then
								rbSel.Checked = False
								rbSel.Checked = True
							End If
						End If
					End If
				Next
			End With
			blnInsertShowplans = blnShowplans
		End If
	End Sub

	Private Sub TestCustomerXMLDocuments(ByVal blnFromOrders As Boolean)
		Dim intXML1Rows As Integer
		Dim intXML2Rows As Integer
		Dim strSQL As String = Nothing
        Application.DoEvents()
		Try
			strSQL = "SELECT COUNT(CustomerID) FROM Customers " + _
			 "WHERE CustomerXML1 IS NOT NULL"
			cnNwind.Open()
			cmNwind.CommandText = strSQL
			intXML1Rows = CInt(cmNwind.ExecuteScalar)
			strSQL = "SELECT COUNT(CustomerID) FROM Customers " + _
			 "WHERE CustomerXML2 IS NOT NULL"
			cmNwind.CommandText = strSQL
			intXML2Rows = CInt(cmNwind.ExecuteScalar)
			cnNwind.Close()
			If intXML1Rows = 0 Or intXML2Rows = 0 Then
				Dim strMsg As String = "The CustomersXML1 and/or CustomersXML2 columns " + _
				"aren't populated with XML documents, which are required for XQuery "
				If blnFromOrders Then
					strMsg += "join "
				End If
				strMsg += "examples. " + vbCrLf + vbCrLf + _
				 "Click OK to populate the columns with the default settings. " + vbCrLf + vbCrLf + _
				 "Click Cancel to return to the Customers Table page to specify default xml index and " + _
				 "SchemaCollection settings, then fill both tables."
				If MsgBox(strMsg, MsgBoxStyle.Information Or MsgBoxStyle.OKCancel, "CustomerXML Documents are Missing") = MsgBoxResult.OK Then
					If intXML1Rows = 0 Then
						btnFillCustomerXML1_Click(Nothing, Nothing)
					End If
					If intXML2Rows = 0 Then
						btnFillCustomerXML2_Click(Nothing, Nothing)
					End If
				Else
					tabNwindXml.SelectedIndex = 0
				End If
			End If
			If blnShowPlanXML And rbFindByCustID.Checked Then
				'Required to display showplan on startup
				rbFindByCustID.Checked = False
				rbFindByCustID.Checked = True
			End If
		Catch exc As Exception
			MsgBox(exc.Message, MsgBoxStyle.Exclamation, "TestCustomerXMLDocuments Exception")
		Finally
			cnNwind.Close()
		End Try
	End Sub

	Private Sub TestOrderXMLDocuments()
		'Orders/SalesOrders XQuery page
		Dim intXML1Rows As Integer
		Dim intXML2Rows As Integer
		Dim strSQL As String = Nothing
		Dim blnSOChecked As Boolean
		If chkUseSalesOrders.Checked Then
			blnSOChecked = True
		End If
        Application.DoEvents()
		Try
			strSQL = "SELECT COUNT(OrderID) FROM Orders " + _
			 "WHERE OrderXML1 IS NOT NULL"
			cnNwind.Open()
			cmNwind.CommandText = strSQL
			intXML1Rows = CInt(cmNwind.ExecuteScalar)
			strSQL = "SELECT COUNT(OrderID) FROM Orders " + _
			 "WHERE OrderXML2 IS NOT NULL"
			cmNwind.CommandText = strSQL
			intXML2Rows = CInt(cmNwind.ExecuteScalar)
			cnNwind.Close()
			If intXML1Rows = 0 Or intXML2Rows = 0 Then
				Dim strMsg As String = "The OrderXML1 and/or OrderXML2 columns " + _
				"aren't populated with XML documents, which are required for XQuery examples. " + vbCrLf + vbCrLf + _
				"Click OK to populate the columns with the default settings. " + vbCrLf + vbCrLf + _
				"Click Cancel to return to the Orders Table page to specify initial xml index and " + _
				"SchemaCollection settings, then fill both tables."
				If chkUseSalesOrders.Enabled Then
					strMsg += vbCrLf + vbCrLf + "Population of the SalesOrders table is optional " + _
					 "but you must return to the Orders Table page to update the columns, if you " + _
					 "haven't done so previously. " + vbCrLf + vbCrLf
				End If
				strMsg += "To prevent this message from appearing, set the DropXmlOrdersOnStart " + _
				 "application-level setting to False, which retains the tables' content between sessions."
				If MsgBox(strMsg, MsgBoxStyle.Information Or MsgBoxStyle.OKCancel, "OrderXML Documents are Missing") = MsgBoxResult.OK Then
					chkUseSalesOrders.Checked = False
					If intXML1Rows = 0 Then
						btnFillOrderXML1_Click(Nothing, Nothing)
					End If
					If intXML2Rows = 0 Then
						btnFillOrderXML2_Click(Nothing, Nothing)
					End If
					intXML1Start = CInt(alOrderIDs(alOrderIDs.Count - 1)) + 1
					intXML2Start = intXML1Start
					'Fix up the default XQuery expression
					txtOrdersXQuery.Text = Replace(txtOrdersXQuery.Text, "=0", "=" + (intXML1Start - 1).ToString)
					tabNwindXml.SelectedIndex = 3
					If chkUseSalesOrders.Enabled And blnSOChecked Then
						chkUseSalesOrders.Checked = True
					End If
				Else
					tabNwindXml.SelectedIndex = 2
				End If
			End If
		Catch exc As Exception
			MsgBox(exc.Message, MsgBoxStyle.Exclamation, "TestOrderXMLDocuments Exception")
		Finally
			cnNwind.Close()
		End Try
	End Sub

	Private Function ShowPlanXML(ByVal strQuery As String) As String
		'Display the SHOWPLAN_XML or STATISTICS XML content in the results text box
		'Execution is controlled by the DisplayShowPlan, DisplayStatisticsXML, and
		'ExpandShowplanEntities application-level settings.
		If Not blnHasLoaded Then
			Return ""
		End If
        Application.DoEvents()
		Dim strSQL As String = Nothing
		If blnShowPlanInGrid And Not blnShowStatsXML Then
			'Display ShowPlan in a DataGridView
			With dgvShowPlan
				.Columns.Clear()
				.Rows.Clear()
				If tabNwindXml.SelectedIndex = 1 Then
					.Left = txtCustsXQResult.Left + 9