本文介绍了如何使用VS.NET实现多线程处理，通过一个实例展示了如何在处理大型数据表查询的同时进行其他查询。通过创建两个线程，分别执行不同的SQL查询操作，展示了VS.NET中处理多线程的基本机制。示例涉及SqlDataAdapter、SqlDataSet、SqlConnection等数据访问组件，以及线程创建和数据填充方法。
通过VS.NET实现一个简单的多线程访问SQL Server数据库
今天学习了VS.NET中的多线程处理方法，通过下面的例子对今天的内容进行一个总结。
例子实现的功能时：当一个线程在处理一个较大的数据表查询时，另一个线程同时也可以进行另一个数据表的查询。通过这个例子子可以掌握在VS.NET中处理多线程的基本机制
例子中用到以下部分类型：SqlDataAdapter,SqlDataSet,SqlConnection,SqlCommand,Thread,ThreadStart
1:用VS.NET建立一个基于VB的Windows窗体应用程序
2:在窗体上布置第一组控件：TextBox,Button,DataGrid，排列好位置，并且保持各个控件的默认Name属性
3:在窗体上布置第二组控件：TextBox,Button,DataGrid,排列好位置，并且保持各个控件的默认Name属性。
注：第一组控件和第二组控件的Button按钮下分别实现一个查询功能，并且将结果填入相对的DataGrid控件中
4:在窗体上放一个SqlDataAdapter，并且利用向导设置各个属性。
1）数据库连接选择SQL Server中自带的示例数据库Northwind,如果没有这个选择，则先建立一个新连接
2）使用现有存储过程（GetCustomersByCountry），选择之后对进入下个画面，然后取消多余的(插入，更新等）存储过程，本例中不对数据库进行更新操作。如果没有GetCustomersByCountry此存储过程，则选择新建存储过程：新建的存储过程代码如下：
ALTER PROCEDURE dbo.GetCustomersByCountry
(
@Contry nvarchar(15)
)
AS
SET NOCOUNT ON;
SELECT CustomerID, CompanyName, ContactName, Country FROM Customers WHERE (Country = @Contry)
3）设置完之后回到窗体设计模式
5:右击窗体设计器下放的SqlDataAdapter1---生成数据集。选择：新建，并且命名为DsCustomerByCountry.然后选择下放的SqlDataAdapter1中的数据表，最后选择确定回到窗体设计器
此时我们就基本建立好了窗体中第一组控件的用户界面，这组控件的功能就是当你单击Button1按钮时，SqlDataAdapter1根据TextBox1中的数值（与CountryName值关联）调用GetCustomersByCountry存储过程，然后将结果显示在DataGrid1中
6:再在窗体中放另一个SqlDataAdapter控件。以下的操作同上面的操作类似。只是让SqlDataAdapter2使用里另外一个存储过程(SelectOrdersByCustomer),并且将新建立的数据集命名为：DsOrdersByCustomer
SelectOrdersByCustomer存储的存储过程代码如下：
ALTER PROCEDURE dbo.SelectOrdersByCustomer
(
@CustomerID nchar(5)
)
AS
SET NOCOUNT ON;
SELECT OrderID, CustomerID, EmployeeID, OrderDate, ShipVia, ShippedDate, Freight FROM Orders WHERE (CustomerID = @CustomerID)
注：这一组控件的作用是，当单击Button2按钮时，SqlDataAdapter2将调根据TextBox2中的值（与CustomerID值关联)调用SelectOrderByCustomer存储过程，并且将结果填入DataGrid2中
7:在解决方案中添加一个新项目，项目类型为: 类
在这个文件中编写以下代码：
Imports System.Data.SqlClient '引入命名空间，以便使用其中的功能
Public Class Fillter '定义类
Private m_dsCustomer As DataSet '用于和主线程通信的成员变量
Private m_daCustomer As SqlClient.SqlDataAdapter
Private m_dgCustomer As DataGrid
Public Property CustDataSet() As DataSet
Get
Return m_dsCustomer
End Get
Set(ByVal Value As DataSet)
m_dsCustomer = Value
End Set
End Property
Public Property CustDataAdapter() As SqlDataAdapter
Get
Return m_daCustomer
End Get
Set(ByVal Value As SqlDataAdapter)
m_daCustomer = Value
End Set
End Property
Public Property CustDataGrid() As DataGrid
Get
Return m_dgCustomer
End Get
Set(ByVal Value As DataGrid)
m_dgCustomer = Value
End Set
End Property
Public Sub FillCustomers() '成员方法，用于填充DataGrid
Try
m_dsCustomer.Clear()
m_daCustomer.Fill(m_dsCustomer, "Customer")
m_dgCustomer.DataSource = m_dsCustomer
Catch excFill As SqlClient.SqlException
Console.WriteLine("Error:" & excFill.Message)
Catch excGeneral As System.Exception
Console.WriteLine("Error:" & excGeneral.Message)
End Try
End Sub
End Class
然后保存此文件，回到到窗体设计器
8:在Button1_Click事件中编写以下代码
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim thdFill As Thread
Dim clsFillter As New Fillter() '实例化自定义的Fillter类
Me.DataGrid1.Refresh()
Try
Me.SqlSelectCommand1.Parameters("@Contry").Value = TextBox1.Text '设置存储过程中的参数
Catch excParam As System.Exception
Console.WriteLine("Error" & excParam.Message
End Try
Try
clsFillter.CustDataAdapter = Me.SqlDataAdapter1 '设置类成员，以便在Fillter类中使用
clsFillter.CustDataSet = Me.DsCustomerByCountry1
clsFillter.CustDataGrid = Me.DataGrid1
Dim tsFill As ThreadStart = New ThreadStart(AddressOf clsFillter.FillCustomers) '设置线程启动后调用的方法
thdFill = New Thread(tsFill)
thdFill.Start() '启动线程
Catch excFill As SqlClient.SqlException
Console.WriteLine("Error:" & excFill.Message)
End Try
End Sub
9:在Button2_Click事件下编写以下代码：
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Try
Me.SqlSelectCommand2.Parameters("@CustomerID").Value = TextBox2.Text
Catch excParam As System.Exception
Console.WriteLine("Error:" & excParam.Message)
End Try
Try
FillOrders() '调用自定义过程
Catch excFill As SqlClient.SqlException
Console.WriteLine("Erroe:" & excFill.Message)
End Try
End Sub
10:向当前的Form1类中添加自定义过程FillOrders,代码如下：
Private Sub FillOrders()
Try
Me.DsOrdersByCustomer1.Clear()
Me.SqlDataAdapter2.Fill(Me.DsOrdersByCustomer1) '间接的执行了存储过程，并返回一个DataTable到DataSet中
Catch excFill As SqlClient.SqlException
Console.WriteLine("Erroe:" & excFill.Message)
End Try
End Sub
11:记得在Form1顶部添加以下导入语句：
Imports System.Threading '线程功能所在的命名空间
最终的Form1的完整代码如下：
Imports System.Threading
Public Class Form1
Inherits System.Windows.Forms.Form
#Region " Windows 窗体设计器生成的代码 " '此出代码段由VS.NET自动生成
Public Sub New()
MyBase.New()
'该调用是 Windows 窗体设计器所必需的。
InitializeComponent()
'在 InitializeComponent() 调用之后添加任何初始化
End Sub
'窗体重写处置以清理组件列表。
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Windows 窗体设计器所必需的
Private components As System.ComponentModel.IContainer
'注意：以下过程是 Windows 窗体设计器所必需的
'可以使用 Windows 窗体设计器修改此过程。
'不要使用代码编辑器修改它。
Friend WithEvents TextBox1 As System.Windows.Forms.TextBox
Friend WithEvents Button1 As System.Windows.Forms.Button
Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
Friend WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter
Friend WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
Friend WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
Friend WithEvents DsCustomerByCountry1 As Threading.DsCustomerByCountry
Friend WithEvents Button2 As System.Windows.Forms.Button
Friend WithEvents TextBox2 As System.Windows.Forms.TextBox
Friend WithEvents DataGrid2 As System.Windows.Forms.DataGrid
Friend WithEvents SqlDataAdapter2 As System.Data.SqlClient.SqlDataAdapter
Friend WithEvents SqlSelectCommand2 As System.Data.SqlClient.SqlCommand
Friend WithEvents DsOrdersByCustomer1 As Threading.DsOrdersByCustomer
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.TextBox1 = New System.Windows.Forms.TextBox()
Me.Button1 = New System.Windows.Forms.Button()
Me.DataGrid1 = New System.Windows.Forms.DataGrid()
Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter()
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection()
Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand()
Me.DsCustomerByCountry1 = New Threading.DsCustomerByCountry()
Me.Button2 = New System.Windows.Forms.Button()
Me.TextBox2 = New System.Windows.Forms.TextBox()
Me.DataGrid2 = New System.Windows.Forms.DataGrid()
Me.SqlDataAdapter2 = New System.Data.SqlClient.SqlDataAdapter()
Me.SqlSelectCommand2 = New System.Data.SqlClient.SqlCommand()
Me.DsOrdersByCustomer1 = New Threading.DsOrdersByCustomer()
CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
CType(Me.DsCustomerByCountry1, System.ComponentModel.ISupportInitialize).BeginInit()
CType(Me.DataGrid2, System.ComponentModel.ISupportInitialize).BeginInit()
CType(Me.DsOrdersByCustomer1, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'TextBox1
'
Me.TextBox1.Location = New System.Drawing.Point(8, 24)
Me.TextBox1.Name = "TextBox1"
Me.TextBox1.Size = New System.Drawing.Size(184, 21)
Me.TextBox1.TabIndex = 0
Me.TextBox1.Text = "USA"
'
'Button1
'
Me.Button1.Location = New System.Drawing.Point(200, 24)
Me.Button1.Name = "Button1"
Me.Button1.Size = New System.Drawing.Size(120, 23)
Me.Button1.TabIndex = 1
Me.Button1.Text = "Select By Country"
'
'DataGrid1
'
Me.DataGrid1.DataMember = ""
Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
Me.DataGrid1.Location = New System.Drawing.Point(8, 56)
Me.DataGrid1.Name = "DataGrid1"
Me.DataGrid1.Size = New System.Drawing.Size(344, 280)
Me.DataGrid1.TabIndex = 2
'
'SqlDataAdapter1
'
Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "GetCustomersByCountry", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("CustomerID", "CustomerID"), New System.Data.Common.DataColumnMapping("CompanyName", "CompanyName"), New System.Data.Common.DataColumnMapping("ContactName", "ContactName"), New System.Data.Common.DataColumnMapping("Country", "Country")})})
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "data source=4CE9E2BA477A4C6;initial catalog=Northwind;integrated security=SSPI;pe" & _
"rsist security info=False;user id=seven;workstation id=4CE9E2BA477A4C6;packet si" & _
"ze=4096"
'
'SqlSelectCommand1
'
Me.SqlSelectCommand1.CommandText = "[GetCustomersByCountry]"
Me.SqlSelectCommand1.CommandType = System.Data.CommandType.StoredProcedure
Me.SqlSelectCommand1.Connection = Me.SqlConnection1
Me.SqlSelectCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
Me.SqlSelectCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Contry", System.Data.SqlDbType.NVarChar, 15))
'
'DsCustomerByCountry1
'
Me.DsCustomerByCountry1.DataSetName = "DsCustomerByCountry"
Me.DsCustomerByCountry1.Locale = New System.Globalization.CultureInfo("zh-CN")
Me.DsCustomerByCountry1.Namespace = "http://www.tempuri.org/DsCustomerByCountry.xsd"
'
'Button2
'
Me.Button2.Location = New System.Drawing.Point(560, 24)
Me.Button2.Name = "Button2"
Me.Button2.Size = New System.Drawing.Size(136, 23)
Me.Button2.TabIndex = 3
Me.Button2.Text = "Select By CustomerID"
'
'TextBox2
'
Me.TextBox2.Location = New System.Drawing.Point(368, 24)
Me.TextBox2.Name = "TextBox2"
Me.TextBox2.Size = New System.Drawing.Size(184, 21)
Me.TextBox2.TabIndex = 4
Me.TextBox2.Text = "VINET"
'
'DataGrid2
'
Me.DataGrid2.DataMember = "SelectOrdersByCustomer"
Me.DataGrid2.DataSource = Me.DsOrdersByCustomer1
Me.DataGrid2.HeaderForeColor = System.Drawing.SystemColors.ControlText
Me.DataGrid2.Location = New System.Drawing.Point(368, 56)
Me.DataGrid2.Name = "DataGrid2"
Me.DataGrid2.Size = New System.Drawing.Size(384, 280)
Me.DataGrid2.TabIndex = 5
'
'SqlDataAdapter2
'
Me.SqlDataAdapter2.SelectCommand = Me.SqlSelectCommand2
Me.SqlDataAdapter2.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "SelectOrdersByCustomer", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("OrderID", "OrderID"), New System.Data.Common.DataColumnMapping("CustomerID", "CustomerID"), New System.Data.Common.DataColumnMapping("EmployeeID", "EmployeeID"), New System.Data.Common.DataColumnMapping("OrderDate", "OrderDate"), New System.Data.Common.DataColumnMapping("ShipVia", "ShipVia"), New System.Data.Common.DataColumnMapping("ShippedDate", "ShippedDate"), New System.Data.Common.DataColumnMapping("Freight", "Freight")})})
'
'SqlSelectCommand2
'
Me.SqlSelectCommand2.CommandText = "[SelectOrdersByCustomer]"
Me.SqlSelectCommand2.CommandType = System.Data.CommandType.StoredProcedure
Me.SqlSelectCommand2.Connection = Me.SqlConnection1
Me.SqlSelectCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
Me.SqlSelectCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CustomerID", System.Data.SqlDbType.NVarChar, 5))
'
'DsOrdersByCustomer1
'
Me.DsOrdersByCustomer1.DataSetName = "DsOrdersByCustomer"
Me.DsOrdersByCustomer1.Locale = New System.Globalization.CultureInfo("zh-CN")
Me.DsOrdersByCustomer1.Namespace = "http://www.tempuri.org/DsOrdersByCustomer.xsd"
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
Me.ClientSize = New System.Drawing.Size(760, 357)
Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.DataGrid2, Me.TextBox2, Me.Button2, Me.DataGrid1, Me.Button1, Me.TextBox1})
Me.Name = "Form1"
Me.Text = "Form1"
CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
CType(Me.DsCustomerByCountry1, System.ComponentModel.ISupportInitialize).EndInit()
CType(Me.DataGrid2, System.ComponentModel.ISupportInitialize).EndInit()
CType(Me.DsOrdersByCustomer1, System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)
End Sub
#End Region
''以下代码为用户手工编写代码
Private Sub FillOrders()
Try
Me.DsOrdersByCustomer1.Clear()
Me.SqlDataAdapter2.Fill(Me.DsOrdersByCustomer1)
Catch excFill As SqlClient.SqlException
Console.WriteLine("Erroe:" & excFill.Message)
End Try
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim thdFill As Thread
Dim clsFiller As New Fillter()
Me.DataGrid1.Refresh()
Try
Me.SqlSelectCommand1.Parameters("@Contry").Value = TextBox1.Text
Catch excParam As System.Exception
Console.WriteLine("Error" & excParam.Message)
End Try
Try
clsFiller.CustDataAdapter = Me.SqlDataAdapter1
clsFiller.CustDataSet = Me.DsCustomerByCountry1
clsFiller.CustDataGrid = Me.DataGrid1
Dim tsFill As ThreadStart = New ThreadStart(AddressOf clsFiller.FillCustomers)
thdFill = New Thread(tsFill)
thdFill.Start()
Catch excFill As SqlClient.SqlException
Console.WriteLine("Error:" & excFill.Message)
End Try
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Try
Me.SqlSelectCommand2.Parameters("@CustomerID").Value = TextBox2.Text
Catch excParam As System.Exception
Console.WriteLine("Error:" & excParam.Message)
End Try
Try
FillOrders() '调用自定义过程
Catch excFill As SqlClient.SqlException
Console.WriteLine("Erroe:" & excFill.Message)
End Try
End Sub
End Class
运行程序后在TextBox1中输入USA然后点击Button1，然后在TextBox2中输入VINET，并且点击Button2.
在应用程序中如果没有使用多线程，那么当你点击Button1，程序开始查询后，是不可以立即在TextBox2中操作的，而现在的程序使用了多线程，所以可以让Button1下面线程执行的过程中，继续让主应用程序对用户响应相关的操作。
为了更好的看清楚应用程序的功能，你可以在Fillter类的FillCustomers方法下面添加以下语句，使此过程持续更长的时间。然后在运行程序时，打开下面的输出窗口，用Console语句的变化分析此例子。
Dim douTemp As Double
For douTemp = 0 To 10000
Console.WriteLine(douTemp)
Next
这是一个简单的多线程应用的例子，线程设计的知识和难点，我知道还有很多，比如同步处理，异常处理等等。这个例子只是一个很初级的示例。要了解线程，还需要弥补很多知识！
seven 2005年8月30日 15:37
扫一扫
3662
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
