mainform.vb

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

Option Explicit On
Option Strict On
Imports System.Data
Imports System.Data.SqlClient

Public Class MainForm
	Private cnNwind As SqlConnection
	Private cmNwind As SqlCommand
	Private datStartDate As Date
	Private datEndDate As Date
	Private intDays As Integer
	Private intRows As Integer
	Private alCustIDs As New ArrayList
	Private aProducts(68, 1) As String
	Private blnStop As Boolean
	Private intLastMonth As Integer
	Private intRowsPerDayInterval As Integer
	Private strRowCount As String
	Private strStartDate As String

	Private Sub MainForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
		cnNwind = New SqlConnection(My.Settings.NorthwindConnection)
		CreateTables(True)
		GetInitialValues(True)
	End Sub

	Private Sub CreateTables(ByVal blnTestFirst As Boolean)
		Try
			'Create the SalesOrder and SalesOrderItems tables if not present
			Dim blnCreateTables As Boolean
			Dim strSQL As String = Nothing
			Dim intRetVal As Integer
			strSQL = "SELECT COUNT(*) FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[SalesOrders]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1"
			cmNwind = New SqlCommand(strSQL, cnNwind)
			cmNwind.CommandType = CommandType.Text
			cnNwind.Open()
			Dim strMsg As String = Nothing
			If blnTestFirst Then
				intRetVal = CInt(cmNwind.ExecuteScalar)
				strSQL = "SELECT COUNT(*) FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[SalesOrderItems]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1"
				cmNwind.CommandText = strSQL
				intRetVal += CInt(cmNwind.ExecuteScalar)
				If intRetVal <> 2 Then
					'SalesOrders and SalesOrderItems are missing
					If intRetVal = 0 Then
						strMsg = "The 'SalesOrders' and 'SalesOrderItems' tables " + _
						 "aren't present in the Northwind database. Do you want to create them?"
					Else
						strMsg = "The 'SalesOrders' or 'SalesOrderItems' table " + _
						 "isn't present in the Northwind database. You must create both " + _
						 "tables to use this project. Do you want to create them?"
					End If
					If MsgBox(strMsg, MsgBoxStyle.Question Or MsgBoxStyle.YesNo, "Create SalesOrders and SalesOrderItems Tables") = MsgBoxResult.No Then
						strMsg = "Goodbye from the FillSalesOrdersTables project."
						MsgBox(strMsg, MsgBoxStyle.Exclamation, "This Application Requires New Tables")
						End
					Else
						blnCreateTables = True
					End If
				End If
			Else
				blnCreateTables = True
			End If
			If blnCreateTables Then
				Dim strFile As String = Application.StartupPath + "\CreateSalesOrdersFromProject.sql"
				If My.Computer.FileSystem.FileExists(strFile) Then
					strSQL = My.Computer.FileSystem.ReadAllText(strFile)
					cmNwind.CommandText = strSQL
					intRetVal = cmNwind.ExecuteNonQuery
					If intRetVal = 0 Then
						strMsg = "Unable to create the tables, possibly due to a collation conflict. " + vbCrLf + vbCrLf + _
						 "The 'CreateSalesOrdersWithCollation.sql' file in the ...\bin folder has " + _
						 "SQL_Latin1_General_CP1_CI_AS collation specified. Edit the collation entries " + _
						 "as necessary, and run the script from Management Studio or Express Manager."
					Else
						strMsg = "Created the 'SalesOrders' and 'SalesOrderItems' tables in the Northwind database."
					End If
				Else
					strMsg = "Can't find the '" + strFile + "' file to create the tables."
				End If
				MsgBox(strMsg, MsgBoxStyle.Information, "Status of Table Creation")
			End If
			cnNwind.Close()
		Catch exc As Exception
			Dim strMsg As String = "Exceptions at this point ordinarily occur as the result of a connection string problem. " + _
			"The default connection string specifies 'localhost' as the SQL Server 2005 " + _
			 "server name. If you're using SQL Express, specify '.\SQLEXPRESS' as the server name. Edit the 'app.config' " + _
			"file's 'connectionString' attribute to point to the correct instance name." + vbCrLf + vbCrLf
			MsgBox(strMsg + exc.Message, MsgBoxStyle.Information, "Exception During Project Startup")
		Finally
			If cnNwind.State = ConnectionState.Open Then
				cnNwind.Close()
			End If
		End Try
	End Sub

	Private Sub GetInitialValues(ByVal blnLoadData As Boolean)
		'Load text boxes and calculate dates
		Try
			If blnLoadData Then
				Dim strSQL As String = "SELECT COUNT(OrderID) FROM SalesOrders"
				cmNwind = New SqlCommand(strSQL, cnNwind)
				cmNwind.CommandType = CommandType.Text
				If Not cnNwind.State = ConnectionState.Open Then
					cnNwind.Open()
				End If
				intRows = CType(cmNwind.ExecuteScalar, Integer)

				'Populate the CustomerID ArrayList
				strSQL = "SELECT CustomerID FROM Customers"
				cmNwind.CommandText = strSQL
				Dim sdrCustIDs As SqlDataReader = cmNwind.ExecuteReader
				With sdrCustIDs
					If .HasRows Then
						While .Read
							alCustIDs.Add(.GetValue(0))
						End While
					End If
					.Close()
				End With

				'Add current (not discontinued) products
				strSQL = "SELECT ProductID, UnitPrice FROM Products WHERE Discontinued = 0"
				cmNwind.CommandText = strSQL
				Dim intCtr As Integer
				Dim sdrProds As SqlDataReader = cmNwind.ExecuteReader
				With sdrProds
					If .HasRows Then
						While .Read
							aProducts(intCtr, 0) = .GetInt32(0).ToString
							aProducts(intCtr, 1) = .GetDecimal(1).ToString
							intCtr += 1
						End While
					End If
					.Close()
				End With
			End If
			Dim intAddRows As Integer
			Dim intRowsPerDayStart As Integer
			Dim intRowsPerDayEnd As Integer
			If intRows = 0 Or Not blnLoadData Then
				If intRows = 0 Then
					txtRowCount.Text = "0"
				End If
				intAddRows = CType(txtAddRows.Text, Integer)
				Dim intTestRows As Integer
				intTestRows = CType(txtRowCount.Text, Integer)
				intRowsPerDayStart = CType(txtOrdersPerDayStart.Text, Integer)
				intRowsPerDayEnd = CType(txtOrdersPerDayEnd.Text, Integer)
				If intRowsPerDayEnd > intRowsPerDayStart Then
					intRowsPerDayInterval = CInt((intAddRows - intTestRows) / (intRowsPerDayEnd - intRowsPerDayStart))
				Else
					intRowsPerDayInterval = 0
				End If
				intDays = CType(2 * (intAddRows - intTestRows) / (intRowsPerDayStart + intRowsPerDayEnd), Integer)
				Dim dblCorr As Double
				Select Case intRowsPerDayEnd - intRowsPerDayStart
					Case Is >= 1000
						dblCorr = 0.595
					Case Is >= 500
						dblCorr = 0.692
					Case Is >= 200
						dblCorr = 0.822
					Case Is >= 100
						dblCorr = 0.901
					Case Is >= 50
						dblCorr = 0.95
					Case Else
						dblCorr = 1
				End Select
				intDays = CInt(intDays / dblCorr)
				If blnLoadData Then
					txtRowCount.Text = intRows.ToString
					datStartDate = Today.AddDays(-intDays)
					txtStartDate.Text = datStartDate.ToShortDateString
					txtEndDate.Text = Today.ToShortDateString
					strRowCount = txtRowCount.Text
					strStartDate = txtStartDate.Text
				Else
					datStartDate = CDate(txtStartDate.Text)
					If intDays > 0 Then
						txtEndDate.Text = datStartDate.AddDays(intDays).ToShortDateString
					End If
				End If
				If intRows = 0 Then
					btnTruncate.Enabled = False
					Me.Height = 230
					SalesOrdersDataNavigator.Visible = False
				End If
			Else
				txtRowCount.Text = intRows.ToString
				intAddRows = intRows
				txtAddRows.Text = intAddRows.ToString
				cmNwind.CommandText = "SELECT TOP 1 OrderDate FROM SalesOrders ORDER BY OrderID DESC"
				datStartDate = CDate(cmNwind.ExecuteScalar)
				'Test the preceding day, because last day might be truncated
				cmNwind.CommandText = "SELECT COUNT(OrderID) FROM SalesOrders WHERE OrderDate = '" + _
				 datStartDate.AddDays(-1).ToShortDateString + "'"
				intRowsPerDayStart = CInt(cmNwind.ExecuteScalar) + 2
				intRowsPerDayEnd = intRowsPerDayStart + 50
				intDays = CType(2 * (intAddRows - intRows) / (intRowsPerDayStart + intRowsPerDayEnd), Integer)
				If intRowsPerDayEnd > intRowsPerDayStart Then
					intRowsPerDayInterval = CInt((intAddRows - intRows) / (intRowsPerDayEnd - intRowsPerDayStart))
				Else
					intRowsPerDayInterval = 0
				End If
				'Start on the next day
				datStartDate = datStartDate.AddDays(1)
				txtStartDate.Text = datStartDate.ToShortDateString
				datEndDate = datStartDate.AddDays(intDays)
				txtEndDate.Text = datEndDate.ToShortDateString
				txtOrdersPerDayStart.Text = intRowsPerDayStart.ToString
				txtOrdersPerDayEnd.Text = intRowsPerDayEnd.ToString
				strRowCount = txtRowCount.Text
				strStartDate = txtStartDate.Text

				'Fill the table adapters and display the last 100 rows
				Me.SalesOrdersTableAdapter.FillSalesOrders(Me.NorthwindDataSet.SalesOrders)
				Me.SalesOrderItemsTableAdapter.FillSalesOrderItems(Me.NorthwindDataSet.SalesOrderItems)
				btnTruncate.Enabled = True
				Me.Height = 580
				SalesOrdersDataNavigator.Visible = True
			End If
			cnNwind.Close()
		Catch exc As Exception
			Dim strMsg As String = exc.Message + vbCrLf + vbCrLf
			strMsg += "This application requires creating the SalesOrders and SalesOrderItems " + _
			 "tables in the Northwind database. Execute the CreateSalesOrdersAndItems.sql script " + _
			 "in the project's \bin folder to create the the two tables." + vbCrLf + vbCrLf + _
			 "The connection string specifies 'localhost' as the SQL Server 2005 " + _
			 "server name. If you're using SQL Express, specify '.\SQLEXPRESS' as the server name."
			MsgBox(strMsg, MsgBoxStyle.Exclamation, "Error Getting Initial Values")
		End Try
	End Sub

	Private Sub btnRecalc_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRecalc.Click
		If CInt(txtAddRows.Text) - CInt(txtRowCount.Text) <= 0 Then
			Dim strMsg As String = "Desired Row Count value is less than or equal to Current Row Count."
			MsgBox(strMsg, MsgBoxStyle.Information, "Can't Display Calculated or Actual Values")
			Exit Sub
		End If

		'Recalculate the starting values
		If chkTest.Checked Then
			'Get correct EndDate value
			GetInitialValues(False)
			txtEndDate.Text = ""
			AddRows()
			txtEndDate.Text = txtDate.Text
		Else
			'Guesstimate EndDate value
			GetInitialValues(False)
		End If
	End Sub

	Private Sub btnStartAdding_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStartAdding.Click
		'Add rows to the tables or test EndDate value
		Dim intAdd As Integer = CInt(txtAddRows.Text) - CInt(txtRowCount.Text)
		Dim strMsg As String
		If intAdd > 0 Then
			If intRows > 0 And Not chkTest.Checked Then
				strMsg = "The SalesOrders database currently has " + _
				 Format(intRows, "#,##0") + " rows. " + vbCrLf + vbCrLf + _
				 "Are you sure you want to add " + Format(intAdd, "#,##0") + " rows?"
				If MsgBox(strMsg, MsgBoxStyle.Question Or MsgBoxStyle.YesNo, "Add Rows to the Database") = MsgBoxResult.Yes Then
					AddRows()
				End If
			Else
				AddRows()
			End If
		Else
			strMsg = "Desired Row Count value is less than or equal to Current Row Count."
			MsgBox(strMsg, MsgBoxStyle.Information, "No Rows to Add")
		End If
	End Sub

	Private Sub AddRows()
		'Add the number of rows specified by Desired Rows text box
		btnStopAdding.Enabled = True

		'Random CustomerID
		Dim intRow As Integer
		Dim intAdd As Integer = CInt(txtAddRows.Text) - CInt(txtRowCount.Text)
		If intAdd > 0 Then
			pbRows.Maximum = intAdd
		End If

		'Variables for SalesOrders table
		Dim strCustomerID As String