nwxmlcols.vb

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

					.Top = txtCustsXQResult.Top + 29
					.Width = txtCustsXQResult.Width
					.Height = txtCustsXQResult.Height
				ElseIf tabNwindXml.SelectedIndex = 3 Then
					.Left = txtCustsXQResult.Left + 9
					.Top = txtCustsXQResult.Top + 29
					.Width = txtCustsXQResult.Width
					.Height = txtCustsXQResult.Height
				End If
				strSQL = "SET SHOWPLAN_ALL ON;"
				cmNwind.CommandText = strSQL
				Try
					If cnNwind.State <> Data.ConnectionState.Open Then
						cnNwind.Open()
					End If
					cmNwind.ExecuteNonQuery()
					cmNwind.CommandText = strQuery
					Dim sdrPlan As SqlDataReader = cmNwind.ExecuteReader
					With sdrPlan
						If .HasRows Then
							Dim intCol As Integer
							For intCol = 0 To .FieldCount - 1
								dgvShowPlan.Columns.Add(.GetName(intCol), .GetName(intCol))
								With dgvShowPlan
									.Columns(intCol).DefaultCellStyle.WrapMode = DataGridViewTriState.False
									.Columns(intCol).DefaultCellStyle.NullValue = ""
									Select Case intCol
										Case 0
											.Columns(intCol).Width = 175
										Case 1 To 3
											.Columns(intCol).Width = 45
										Case 4 To 5
											.Columns(intCol).Width = 115
										Case 8 To 11, 15
											.Columns(intCol).Width = 75
										Case 16
											.Columns(intCol).Width = 60
									End Select
								End With
							Next
							Dim objValues(.FieldCount - 1) As Object
							While .Read
								.GetValues(objValues)
								dgvShowPlan.Rows.Add(objValues)
							End While
							Dim intRow As Integer
							Dim strStmt As String
							With dgvShowPlan
								For intRow = 0 To .Rows.Count - 1
									'Try to shrink indents
									strStmt = .Rows(intRow).Cells(0).Value.ToString
									.Rows(intRow).Cells(0).Value = Replace(strStmt, "  ", " ", )
								Next
							End With
							dgvShowPlan.Visible = True
							dgvShowPlan.UseWaitCursor = False
						Else
							Dim strMsg As String = "Error: SHOWPLAN_ALL returned no rows."
							If tabNwindXml.SelectedIndex = 1 Then
								txtCustomerSQL.Text = strMsg
							ElseIf tabNwindXml.SelectedIndex = 3 Then
								txtOrderSQL.Text = strMsg
							End If
						End If
						.Close()
					End With
					strSQL = "SET SHOWPLAN_ALL OFF;"
					cmNwind.CommandText = strSQL
					cmNwind.ExecuteNonQuery()
				Catch exc As Exception
					Dim strMsg As String = "Error displaying SHOWPLAN_ALL in grid: " + exc.Message
					If tabNwindXml.SelectedIndex = 1 Then
						txtCustomerSQL.Text = strMsg
					ElseIf tabNwindXml.SelectedIndex = 3 Then
						txtOrderSQL.Text = strMsg
					End If
				Finally
					If Not blnInsertShowplans Then
						cnNwind.Close()
					End If
				End Try
			End With
			If Not blnInsertShowplans Then
				Return ""
			End If
		End If
		If blnInsertShowplans Or Not blnShowPlanInGrid Then
			Dim sbShowPlan As New StringBuilder
			If blnShowStatsXML Then
				strSQL = "SET STATISTICS XML ON;"
				'Must execute the query, so it's slower
				Me.Cursor = Cursors.WaitCursor
			Else
				strSQL = "SET SHOWPLAN_XML ON;"
			End If
			cmNwind.CommandText = strSQL
			Try
				If cnNwind.State <> Data.ConnectionState.Open Then
					cnNwind.Open()
				End If
				cmNwind.ExecuteNonQuery()
				Dim xrShowPlan As XmlReader = Nothing
				Dim xwSettings As New XmlWriterSettings
				With xwSettings
					.Encoding = Encoding.Unicode
					.Indent = True
					'Single-space indent for readability
					.IndentChars = (" ")
					.OmitXmlDeclaration = False
					.ConformanceLevel = ConformanceLevel.Document
				End With
				'Create an XmlWriter to format the result
				Dim xwShowPlan As XmlWriter = XmlWriter.Create(sbShowPlan, xwSettings)
				cmNwind.CommandText = strQuery
				Dim strPlan As String = Nothing
				Dim strFile As String = Application.StartupPath + "\Stats.xml"
				Dim intRows As Integer
				If blnShowStatsXML Then
					'Test for STATISTICS XML in second rowset
					Dim sdrPlan As SqlDataReader = cmNwind.ExecuteReader
					With sdrPlan
						While .Read
							intRows += 1
						End While
						If .NextResult Then
							'STATISTICS XML are in second rowset and are a string
							'so can't cast to SqlXml
							.Read()
							strPlan = .GetString(0)
							'Save in a file
                            File.WriteAllText(strFile, strPlan)
						End If
						.Close()
					End With
				End If
				If strPlan Is Nothing Then
					'It was a single item or from an UPDATE operation
					xrShowPlan = cmNwind.ExecuteXmlReader
				Else
					'Read the file (klutzy, but simple and speed isn't an issue)
					xrShowPlan = XmlReader.Create(strFile)
				End If
				xrShowPlan.MoveToContent()
				With xwShowPlan
					.WriteNode(xrShowPlan, False)
					.Flush()
					.Close()
				End With
				xrShowPlan.Close()
				With sbShowPlan
					If .Length > 100 Then
						If blnExpandEntities And Not blnInsertShowplans Then
							'Expand entities for readability (not well-formed)
							.Replace("&gt;", ">")
							.Replace("&lt;", "<")
							.Replace("&quot;", """")
							If blnShowStatsXML Then
								sbShowPlan.Insert(0, "<!-- STATISTICS XML &lt;, &gt;, and &quot; entities are expanded for readability -->" + vbCrLf)
							Else
								sbShowPlan.Insert(0, "<!-- SHOWPLAN_XML &lt;, &gt;, and &quot; entities are expanded for readability -->" + vbCrLf)
							End If
						End If
						If blnInsertShowplans Then
							strLastShowplan = .ToString
						End If
						Return .ToString
					Else
						If blnShowStatsXML Then
							Return "STATISTICS XML didn't return data." + vbCrLf
						Else
							Return "SHOWPLAN_XML didn't return data." + vbCrLf
						End If
					End If
				End With
			Catch exc As Exception
				If blnShowStatsXML Then
					Return "Error retrieving STATISTICS XML: " + exc.Message
				Else
					Return "Error retrieving SHOWPLAN_XML: " + exc.Message
				End If
			Finally
				'Shrink the StringBuilder
				sbShowPlan.Length = 0
				'Be sure to turn off SHOWPLAN_XML
				If blnShowStatsXML Then
					strSQL = "SET STATISTICS XML OFF;"
				Else
					strSQL = "SET SHOWPLAN_XML OFF;"
				End If
				Try
					If cnNwind.State <> ConnectionState.Open Then
						cnNwind.Open()
					End If
					cmNwind.ExecuteNonQuery()
					cnNwind.Close()
				Catch excFinal As Exception
					MsgBox(excFinal.Message)
                End Try
            End Try
		End If
        Me.Cursor = Cursors.Default
        Return ""
	End Function

	Private Sub InsertShowPlan(ByVal strShowplanXML As String)
		'Add Showplan instances to the Showplans table
		'Create the table if it's missing
		Dim strSQL As String = "INSERT Showplans(AddedDate, TableName, ColumnName, " + _
		"XQueryName, XQueryEdited, MaxRows, Typed, " + _
		"PrimaryXmlIndex, PathXmlIndex, ValueXmlIndex, PropertyXmlIndex, " + _
		"ExecuteRows, ExecuteTime, ReaderTimeIncluded, ShowplanXML) VALUES("
		strSQL += "'" + Now.ToString + "', "
		If rbOrderXML1.Checked Then
			If chkUseSalesOrders.Checked Then
				strSQL += "'SalesOrders', "
			Else
				strSQL += "'Orders', "
			End If
			strSQL += "'OrderXML1', '" + strXQueryName + "', "
			If blnXQueryEdited Then
				strSQL += "1, "
			Else
				strSQL += "0, "
			End If
			strSQL += txtMaxRows.Text + ", "
			If chkOrderXML1Schema.Checked Then
				strSQL += "1, "
			Else
				strSQL += "0, "
			End If
			If chkOrderXML1Index.Checked Then
				strSQL += "1, "
				If chkOrderXML1IndexPath.Checked Then
					strSQL += "1, "
				Else
					strSQL += "0, "
				End If
				If chkOrderXML1IndexValue.Checked Then
					strSQL += "1, "
				Else
					strSQL += "0, "
				End If
				If chkOrderXML1IndexProp.Checked Then
					strSQL += "1, "
				Else
					strSQL += "0, "
				End If
			Else
				strSQL += "0, 0, 0, 0, "
			End If
		Else
			If chkUseSalesOrders.Checked Then
				strSQL += "'SalesOrders', "
			Else
				strSQL += "'Orders', "
			End If
			strSQL += "'OrderXML2', '" + strXQueryName + "', "
			If blnXQueryEdited Then
				strSQL += "1, "
			Else
				strSQL += "0, "
			End If
			strSQL += txtMaxRows.Text + ", "
			If chkOrderXML2Schema.Checked Then
				strSQL += "1, "
			Else
				strSQL += "0, "
			End If
			If chkOrderXML2Index.Checked Then
				strSQL += "1, "
				If chkOrderXML1IndexPath.Checked Then
					strSQL += "1, "
				Else
					strSQL += "0, "
				End If
				If chkOrderXML2IndexValue.Checked Then
					strSQL += "1, "
				Else
					strSQL += "0, "
				End If
				If chkOrderXML2IndexProp.Checked Then
					strSQL += "1, "
				Else
					strSQL += "0, "
				End If
			Else
				strSQL += "0, 0, 0, 0, "
			End If
		End If
		strSQL += txtRows.Text + ", " + txtTime.Text + ", "
		If blnIncludeReader Then
			strSQL += "1, "
		Else
			strSQL += "0, "
		End If
		'Add the instance with ' fixed
		strSQL += "N'" + Replace(strShowplanXML, "'", "''") + "');"
		cmNwind.CommandText = strSQL
		Try
			If cnNwind.State <> ConnectionState.Open Then
				cnNwind.Open()
			End If
			cmNwind.ExecuteNonQuery()
		Catch exc As Exception
			If exc.Message.Contains("Invalid object name") Then
				Dim strMsg As String = "You have specified adding SHOWPLAN_XML and " + _
				"related data to the 'Northwind.Showplans' table, which doesn't exist. " + vbCrLf + vbCrLf + _
				"Click Yes to create the 'Showplans' table now or click No to cancel " + _
				"adding SHOWPLAN_XML data for this session."
				If MsgBox(strMsg, MsgBoxStyle.Question Or MsgBoxStyle.YesNo, "Showplans Table Missing") = MsgBoxResult.Yes Then
                    Application.DoEvents()
					Dim strCreateSQL As String = "SET ANSI_NULLS ON; " + vbCrLf + _
					"SET ANSI_PADDING ON; " + vbCrLf + _
					"CREATE TABLE dbo.Showplans( " + vbCrLf + _
					"   InstanceID int IDENTITY(1,1) NOT NULL, " + vbCrLf + _
					"   AddedDate datetime NOT NULL, " + vbCrLf + _
					"   TableName varchar(50) NOT NULL, " + vbCrLf + _
					"   ColumnName varchar(50) NOT NULL, " + vbCrLf + _
					"   XQueryName varchar(50) NOT NULL, " + vbCrLf + _
					"   XQueryEdited bit NOT NULL, " + vbCrLf + _
					"   MaxRows int NOT NULL, " + vbCrLf + _
					"   Typed bit NOT NULL, " + vbCrLf + _
					"   PrimaryXmlIndex bit NOT NULL, " + vbCrLf + _
					"   PathXmlIndex bit NOT NULL, " + vbCrLf + _
					"   ValueXmlIndex bit NOT NULL, " + vbCrLf + _
					"   PropertyXmlIndex bit NOT NULL, " + vbCrLf + _
					"   ExecuteRows int NULL, " + vbCrLf + _
					"   ExecuteTime decimal(10, 3) NULL, " + vbCrLf + _
					"   ReaderTimeIncluded bit NOT NULL, " + vbCrLf + _
					"   ShowplanXML xml NOT NULL); " + vbCrLf + _
					"CREATE UNIQUE CLUSTERED INDEX PKC_InstanceID ON Showplans(InstanceID); " + vbCrLf + _
					"SET ANSI_PADDING OFF; " + vbCrLf + _
					"SET ANSI_NULLS OFF; "
					cmNwind.CommandText = strCreateSQL
					Try
						cmNwind.ExecuteNonQuery()
						'Now insert the new record
						'cmNwind.CommandText = strSQL
						'cmNwind.ExecuteNonQuery()
					Catch excCreate As Exception
						MsgBox(excCreate.Message)
					End Try
				Else
					blnInsertShowplans = False
				End If
			Else
				MsgBox(exc.Message)
			End If
		Finally
			cnNwind.Close()
		End Try
	End Sub

	Private Sub btnShowplan_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShowplan.Click
		'Display Showplan for edited XQuery expressions
		Dim strXQ As String = Nothing
		If tabNwindXml.SelectedIndex = 1 Then
			'Customers XQuery
			'Remove the leading comments
			strXQ = txtCustsXQuery.Text
			If strXQ.IndexOf("SELECT") > 0 Then
				strXQ = strXQ.Substring(strXQ.IndexOf("SELECT"))
			ElseIf strXQ.IndexOf("UPDATE") > 0 Then
				strXQ = strXQ.Substring(strXQ.IndexOf("UPDATE"))
			End If
			'Remove the trailing comments
			strXQ = strXQ.Substring(0, strXQ.LastIndexOf(";") + 1)
			If blnShowPlanInGrid Then
				ShowPlanXML(strXQ)
				dgvShowPlan.Visible = True
			Else
				txtCustsXQResult.Text = ShowPlanXML(strXQ)
			End If
		ElseIf tabNwindXml.SelectedIndex = 3 Then
			'Orders XQuery
			blnXQueryEdited = True
			strXQ = txtOrdersXQuery.Text
			If strXQ.IndexOf("SELECT") > 0 Then
				strXQ = strXQ.Substring(strXQ.IndexOf("SELECT"))
			ElseIf strXQ.IndexOf("UPDATE") > 0 Then
				strXQ = strXQ.Substring(strXQ.IndexOf("UPDATE"))
			End If
			strXQ = strXQ.Substring(0, strXQ.LastIndexOf(";") + 1)
			If blnShowPlanInGrid Then
				ShowPlanXML(strXQ)
				dgvShowPlan.Visible = True
			Else
				txtOrdersXQResult.Text = ShowPlanXML(strXQ)
			End If
		End If
	End Sub

	Private Sub ClearTimingData()
		'Clear the text boxes and reset KB check box
		txtRows.Text = ""
		txtTime.Text = ""
		txtRowsPerSec.Text = ""
		chkBytes.Checked = False
	End Sub

End Class