This sample is my .10 version of implementing multi-threading in a console application. I've used this technique to increase the performance of administrative scripts that do remoting. The current sample is hardcoded to use 2 threads, one of the things I'm not sure how to implement making the thread count configurable. If you know, let me @ (steve@adminblogs.com). I imagine there is some "magic" object that could be implemented but I don't know how to do that yet.
This application does the following items
- Selects data from a SQL database
- Stores the data into an in-memory datatable
- Uses multi-threading to loop through the datatable updating the "VAL" column
My Wishes to make this code cleaner
- Implement a method of dynamically increase or decrease the thread count.
Requirements for this sample
- .NET v1.1
- SQL 2000 or MSDE
- VS.NET 2003
How to get started
Database Portion
1 - Create Table called Multithread
CREATE DATABASE [Multithread] ON (NAME = N'Multithread_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\Multithread_Data.MDF' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'Multithread_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\Multithread_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
2 - Create Table called computerList with three columns (id, COL, VAL)
CREATE TABLE [dbo].[computerList] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[col] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[val] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[computerList] ADD
CONSTRAINT [PK_computerList] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
3 - Create a stored procedure called computerList_table_col_sel
CREATE PROCEDURE computerList_table_col_sel AS
SELECT COL, VAL
FROM computerList
GO
4 - Add an odd number of records to the computerList table, leave the VAL column empty
Module Portion - Module1.vb
This is the module that holds the code that implements multi-threading
Imports
System.Configuration
Imports System.Data.SqlClient
Imports multithread.dbTools
Module
Module1
Private m_intCount As Integer
Private m_intStep As Integer
Private Delegate Sub m_delMyDelegate(ByVal strValue As String, ByRef drRow As DataRow)
<MTAThread()>
Sub Main()
Dim objDT As New dbTools
Dim dt As New DataTable
dt = objDT.ExecuteDataTable("computerList_table_col_sel")
Dim ds1 As New DataSet
ds1.Tables.Add(dt)
ds1.WriteXml("c:\dataBefore.xml")
ds1.Tables.Remove(dt)
ds1.Dispose()
Dim intX As Integer = 0
Dim intS As Integer = 0
m_intCount = dt.Rows.Count
If m_intCount > 0 Then
DetermineStep(m_intCount)
End If
For intX = 0 To dt.Rows.Count Step m_intStep
Dim strCN1 As String
Dim objDR1 As DataRow
Dim strCN2 As String
Dim objDR2 As DataRow
Try
strCN1 =
CType(dt.Rows(intX)("COL"), String)
objDR1 = dt.Rows(intX)
Catch
End Try
Try
intS = intX + 1
strCN2 =
CType(dt.Rows(intS)("COL"), String)
objDR2 = dt.Rows(intS)
Catch
End Try
Dim delThread1 As m_delMyDelegate = AddressOf GetComputerInfo
Dim delThread2 As m_delMyDelegate = AddressOf GetComputerInfo
Dim ar1 As IAsyncResult
Dim ar2 As IAsyncResult
Dim intThreadCount As Integer
Dim a_WaitHandles(1) As System.Threading.WaitHandle
If Not strCN1 Is Nothing Then
ar1 = delThread1.BeginInvoke(intX, objDR1, Nothing, Nothing)
End If
If Not strCN2 Is Nothing Then
ar2 = delThread2.BeginInvoke(intS, objDR2, Nothing, Nothing)
End If
a_WaitHandles(0) = ar1.AsyncWaitHandle
a_WaitHandles(1) = ar2.AsyncWaitHandle
System.Threading.WaitHandle.WaitAll(a_WaitHandles, 10000,
False)
If ar1.IsCompleted Then delThread1.EndInvoke(objDR1, ar1)
If ar2.IsCompleted Then delThread2.EndInvoke(objDR2, ar2)
m_intCount = m_intCount - m_intStep
DetermineStep(m_intCount)
Next
Dim ds2 As New DataSet
ds2.Tables.Add(dt)
ds2.WriteXml("c:\dataAfter.xml")
ds2.Dispose()
dt.Dispose()
End Sub
Sub GetComputerInfo(ByVal strValue As String, ByRef drRow As DataRow)
drRow.Item("VAL") = System.DateTime.Now
End Sub
Function DetermineStep(ByVal intCount As Integer) As Integer
Try
If intCount >= 2 Then
m_intStep = 2
Else
m_intStep = intCount
End If
Catch
End Try
End Function
End
Module
Utility Class - Class1.vb
This is the a utility class I use that does common items, returns DataTables, DataSets etc..
Imports
System.Data.SqlClient
Imports System.Configuration
Public
Class dbTools
Public Shared Function ExecuteDataTable(ByVal strSQL As String) As DataTable
Dim myConn As New SqlConnection(ConfigurationSettings.AppSettings("CONN"))
Dim command1 As New SqlCommand(strSQL, myConn)
Dim adapter1 As New SqlDataAdapter
Dim table1 As New DataTable
adapter1.SelectCommand = command1
adapter1.Fill(table1)
adapter1.Dispose()
command1.Dispose()
Return table1
End Function
End
Class
Here is the results of the XML files
BEFORE
c:\dataBefore.xml
<?xml version="1.0" standalone="yes"?>
<NewDataSet>
<Table1>
<COL>PC1</COL>
</Table1>
<Table1>
<COL>PC2</COL>
</Table1>
<Table1>
<COL>PC3</COL>
</Table1>
<Table1>
<COL>PC4</COL>
</Table1>
<Table1>
<COL>PC5</COL>
</Table1>
<Table1>
<COL>PC6</COL>
</Table1>
<Table1>
<COL>PC7</COL>
</Table1>
</NewDataSet>
AFTER
c:\dataAfter.xml
<?xml version="1.0" standalone="yes"?>
<NewDataSet>
<Table1>
<COL>PC1</COL>
<VAL>11/16/2003 12:30:27 PM</VAL>
</Table1>
<Table1>
<COL>PC2</COL>
<VAL>11/16/2003 12:30:27 PM</VAL>
</Table1>
<Table1>
<COL>PC3</COL>
<VAL>11/16/2003 12:30:27 PM</VAL>
</Table1>
<Table1>
<COL>PC4</COL>
<VAL>11/16/2003 12:30:27 PM</VAL>
</Table1>
<Table1>
<COL>PC5</COL>
<VAL>11/16/2003 12:30:27 PM</VAL>
</Table1>
<Table1>
<COL>PC6</COL>
<VAL>11/16/2003 12:30:31 PM</VAL>
</Table1>
<Table1>
<COL>PC7</COL>
<VAL>11/16/2003 12:30:31 PM</VAL>
</Table1>
</NewDataSet>