help with threading these requests please?

  • Thread starter Thread starter Colin Robinson
  • Start date Start date
C

Colin Robinson

Im creating a dotnet windows form app that will run a given TSL script
across a domain of sql servers with the same login credentials(for this
version at least)

I would like the Runallservers() below to run in a backround worker to keep
my ui responsive. but within the for each loop I would like the executesql()
to run on a spawned thread appending its results to the public dataset
table.

the background worker is looking ok, its spawning threads for the
Executesql() that is posing a problem.

I am not bothered what order the results are obtained they are destined for
a sorted datagrid. The rest of the app is looking fine but running tsql over
150 servers takes a bit of time and im hoping threading the individual
requests could help, but that bit is beyond my experience. Im hoping someone
here can help!

(incidentally if I declare another background worker im getting background
worker is busy error on the second iteration of the loop even when the do
work event dims a new backroundworker as = to sender )


Colin Robinson

Sub RunAllServers()

'CheckForIllegalCrossThreadCalls = False

Me.sqlDATA.Clear()

Me.TabControl1.TabPages("Results").Focus()

'all servers

'Reset the Dataset

Me.TxtSQL.ForeColor = System.Drawing.Color.Black

Servers.Tables("sqlData").Clear()

Dim server As String = ""

Dim dr As DataRow

Dim servercount As Integer = 0



For Each dr In Servers.Tables(0).Rows

status.Text = " Processing: " & CStr(dr.Item(0) + ";" + dr.Item(1))

Application.DoEvents()

Windows.Forms.Cursor.Current = Cursors.WaitCursor

executesql(dr.Item(0))

'Me.ExecutesqlThread.RunWorkerAsync(dr.Item(0))

servercount = servercount + 1

Windows.Forms.Cursor.Current = Cursors.Default

Next

status.Text = " Done: " & "Servers = " + CStr(servercount) + " Rows =" &
CStr(Servers.Tables("sqlData").Rows.Count)

GridResults.DataSource = Servers.Tables("sqlData")

End Sub

Function RunAllservers(ByVal n As Integer, ByVal worker As
System.ComponentModel.BackgroundWorker, ByVal e As
System.ComponentModel.DoWorkEventArgs) As String

Me.sqlDATA.Clear()

CheckForIllegalCrossThreadCalls = False

Me.TabControl1.TabPages("Results").Focus()

'all servers

'Reset the Dataset

Me.TxtSQL.ForeColor = System.Drawing.Color.Black

Servers.Tables("sqlData").Clear()

Dim server As String = ""

Dim dr As DataRow

Dim servercount As Integer = 0



For Each dr In Servers.Tables(0).Rows

status.Text = " Processing: " & CStr(dr.Item(0) + ";" + dr.Item(1))

Application.DoEvents()

Windows.Forms.Cursor.Current = Cursors.WaitCursor

'executesql(dr.Item(0))

Me.ExecutesqlThread.RunWorkerAsync(dr.Item(0))

servercount = servercount + 1

Windows.Forms.Cursor.Current = Cursors.Default

Next

status.Text = " Done: " & "Servers = " + CStr(servercount) + " Rows =" &
CStr(Servers.Tables("sqlData").Rows.Count)

GridResults.DataSource = Servers.Tables("sqlData")

Return status.Text

End Function

Function executesql(ByVal server As String) As String

CheckForIllegalCrossThreadCalls = False

Me.TxtSQL.ForeColor = System.Drawing.Color.Black

Dim connstr As String

If txtPassword.Text = "" Then

connstr = "timeout = 60;packet size=4096;user id=" & Me.TxtLogin.Text &
";pwd = '' ;data source=" & server & ";persist security info=False;initial
catalog=" & Me.TxtDB.Text

Else

connstr = "timeout = 60;packet size=4096;user id=" & Me.TxtLogin.Text &
";Pwd=" & Me.txtPassword.Text & ";data source=" & server & ";persist
security info=False;initial catalog=" & Me.TxtDB.Text

End If

Me.SqlConnection1.ConnectionString = connstr

Me.SqlCommand1.CommandText = Me.TxtSQL.Text

If Not Me.ChkProcedure.Checked Then

Me.SqlCommand1.CommandType = CommandType.Text

Else

Me.SqlCommand1.CommandType = CommandType.StoredProcedure

End If

Me.SqlCommand1.Connection = Me.SqlConnection1

Dim dt As New DataTable

dt.TableName = "execute"

'Split and Execute GO batches

Dim sqlBatches() As String

Dim batchseperator As String = "GO"

sqlBatches = Microsoft.VisualBasic.Split(Me.TxtSQL.Text, batchseperator)

'sqlBatches = Me.TxtSQL.Text.Split(batchseperator)

Dim batch As String

'Me.TxtSQL.ForeColor = System.Drawing.Color.Black

Dim batchconnection As New SqlConnection

batchconnection.ConnectionString = Me.SqlConnection1.ConnectionString

batchconnection.Open()

Me.SqlCommand1.Connection = batchconnection



For Each batch In sqlBatches

If batch.ToString.StartsWith("O") Then

batch = batch.Remove(0, 1)

End If

Me.SqlCommand1.CommandText = batch.ToString

With Me.SqlDataAdapter1

..SelectCommand = SqlCommand1

'.FillSchema(dt, SchemaType.Source)

Try

..Fill(sqlDATA)

Catch ex As Exception

Me.TxtSQL.Text = "Error Server " & server & ": " & ex.Message & vbNewLine &
TxtSQL.Text

Me.TxtSQL.ForeColor = System.Drawing.Color.Red

End Try

End With

Next

batchconnection.Close()



'add server name

'dt.Columns.Add("Server")

'Dim dr As DataRow

'For Each dr In dt.Rows

' dr.Item("server") = server

'Next



'Servers.Tables.Add(dt)

'Dim parentCol As DataColumn

'Dim childCol As DataColumn

'' Code to get the DataSet not shown here.

'parentCol = Servers.Tables("server").Columns(0)

'childCol = dt.Columns("server")

'' Create DataRelation.

'Dim relserver As DataRelation

'relserver = New DataRelation("Results", parentCol, childCol)

'' Add the relation to the DataSet.

'Servers.Relations.Add(relserver)

Me.GridResults.DataSource = Servers.Tables("sqlDATA")

Return server

End Function
 
Im creating a dotnet windows form app that will run a given TSL script
across a domain of sql servers with the same login credentials(for this
version at least)

I would like the Runallservers() below to run in a backround worker to keep
my ui responsive. but within the for each loop I would like the
executesql()
to run on a spawned thread appending its results to the public dataset
table.

the background worker is looking ok, its spawning threads for the
Executesql() that is posing a problem.

I am not bothered what order the results are obtained they are destined for
a sorted datagrid. The rest of the app is looking fine but running tsql
over
150 servers takes a bit of time and im hoping threading the individual
requests could help, but that bit is beyond my experience. Im hoping
someone
here can help!

(incidentally if I declare another background worker im getting background
worker is busy error on the second iteration of the loop even when the do
work event dims a new backroundworker as = to sender )


Colin Robinson

Sub RunAllServers()

'CheckForIllegalCrossThreadCalls = False

Me.sqlDATA.Clear()

Me.TabControl1.TabPages("Results").Focus()

'all servers

'Reset the Dataset

Me.TxtSQL.ForeColor = System.Drawing.Color.Black

Servers.Tables("sqlData").Clear()

Dim server As String = ""

Dim dr As DataRow

Dim servercount As Integer = 0



For Each dr In Servers.Tables(0).Rows

status.Text = " Processing: " & CStr(dr.Item(0) + ";" + dr.Item(1))

Application.DoEvents()

Windows.Forms.Cursor.Current = Cursors.WaitCursor

executesql(dr.Item(0))

'Me.ExecutesqlThread.RunWorkerAsync(dr.Item(0))

servercount = servercount + 1

Windows.Forms.Cursor.Current = Cursors.Default

Next

status.Text = " Done: " & "Servers = " + CStr(servercount) + " Rows =" &
CStr(Servers.Tables("sqlData").Rows.Count)

GridResults.DataSource = Servers.Tables("sqlData")

End Sub

Function RunAllservers(ByVal n As Integer, ByVal worker As
System.ComponentModel.BackgroundWorker, ByVal e As
System.ComponentModel.DoWorkEventArgs) As String

Me.sqlDATA.Clear()

CheckForIllegalCrossThreadCalls = False

Me.TabControl1.TabPages("Results").Focus()

'all servers

'Reset the Dataset

Me.TxtSQL.ForeColor = System.Drawing.Color.Black

Servers.Tables("sqlData").Clear()

Dim server As String = ""

Dim dr As DataRow

Dim servercount As Integer = 0



For Each dr In Servers.Tables(0).Rows

status.Text = " Processing: " & CStr(dr.Item(0) + ";" + dr.Item(1))

Application.DoEvents()

Windows.Forms.Cursor.Current = Cursors.WaitCursor

'executesql(dr.Item(0))

Me.ExecutesqlThread.RunWorkerAsync(dr.Item(0))

servercount = servercount + 1

Windows.Forms.Cursor.Current = Cursors.Default

Next

status.Text = " Done: " & "Servers = " + CStr(servercount) + " Rows =" &
CStr(Servers.Tables("sqlData").Rows.Count)

GridResults.DataSource = Servers.Tables("sqlData")

Return status.Text

End Function

Function executesql(ByVal server As String) As String

CheckForIllegalCrossThreadCalls = False

Me.TxtSQL.ForeColor = System.Drawing.Color.Black

Dim connstr As String

If txtPassword.Text = "" Then

connstr = "timeout = 60;packet size=4096;user id=" & Me.TxtLogin.Text &
";pwd = '' ;data source=" & server & ";persist security info=False;initial
catalog=" & Me.TxtDB.Text

Else

connstr = "timeout = 60;packet size=4096;user id=" & Me.TxtLogin.Text &
";Pwd=" & Me.txtPassword.Text & ";data source=" & server & ";persist
security info=False;initial catalog=" & Me.TxtDB.Text

End If

Me.SqlConnection1.ConnectionString = connstr

Me.SqlCommand1.CommandText = Me.TxtSQL.Text

If Not Me.ChkProcedure.Checked Then

Me.SqlCommand1.CommandType = CommandType.Text

Else

Me.SqlCommand1.CommandType = CommandType.StoredProcedure

End If

Me.SqlCommand1.Connection = Me.SqlConnection1

Dim dt As New DataTable

dt.TableName = "execute"

'Split and Execute GO batches

Dim sqlBatches() As String

Dim batchseperator As String = "GO"

sqlBatches = Microsoft.VisualBasic.Split(Me.TxtSQL.Text, batchseperator)

'sqlBatches = Me.TxtSQL.Text.Split(batchseperator)

Dim batch As String

'Me.TxtSQL.ForeColor = System.Drawing.Color.Black

Dim batchconnection As New SqlConnection

batchconnection.ConnectionString = Me.SqlConnection1.ConnectionString

batchconnection.Open()

Me.SqlCommand1.Connection = batchconnection



For Each batch In sqlBatches

If batch.ToString.StartsWith("O") Then

batch = batch.Remove(0, 1)

End If

Me.SqlCommand1.CommandText = batch.ToString

With Me.SqlDataAdapter1

.SelectCommand = SqlCommand1

'.FillSchema(dt, SchemaType.Source)

Try

.Fill(sqlDATA)

Catch ex As Exception

Me.TxtSQL.Text = "Error Server " & server & ": " & ex.Message & bNewLine &
TxtSQL.Text

Me.TxtSQL.ForeColor = System.Drawing.Color.Red

End Try

End With

Next

batchconnection.Close()



'add server name

'dt.Columns.Add("Server")

'Dim dr As DataRow

'For Each dr In dt.Rows

' dr.Item("server") = server

'Next



'Servers.Tables.Add(dt)

'Dim parentCol As DataColumn

'Dim childCol As DataColumn

'' Code to get the DataSet not shown here.

'parentCol = Servers.Tables("server").Columns(0)

'childCol = dt.Columns("server")

'' Create DataRelation.

'Dim relserver As DataRelation

'relserver = New DataRelation("Results", parentCol, childCol)

'' Add the relation to the DataSet.

'Servers.Relations.Add(relserver)

Me.GridResults.DataSource = Servers.Tables("sqlDATA")

Return server

End Function
 
Hi Colin,
Thanks for your post.

In your post, it seems that the real concern lies in the following
paragraph:“I am not bothered what order the results are obtained they are
destined for a sorted datagrid. The rest of the app is looking fine but
running tsql over 150 servers takes a bit of time and im hoping threading
the individual requests could help, but that bit is beyond my experience.
Im hoping someone here can help!?

I am not sure I understand your question very well. Can you express your
concern more clearly?

Actually, in .Net Winform, we can use threading to improve the UI
responsibility, however, we must take care of the calling to the UI
controls in other thread, that is, all the calling to the UI thread must be
marshaled with Control.Invoke or Control.BeginInvoke methods. So if we want
to use certain properties/methods of the UI control, we should marshal them
with Control.Invoke or Control.BeginInvoke. For more information, please
refer to the link below:
"How to: Manipulate Controls from Threads?"
http://msdn2.microsoft.com/en-us/library/757y83z4.aspx

For more useful samples regarding multithreading in .Net Winform, please
refer to the 2 articles below:
"Practical Multithreading for Client Apps?"
http://msdn.microsoft.com/msdnmag/issues/04/01/NET/

"Safe, Simple Multithreading in Windows Forms, Part 1?"
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnforms/htm
l/winforms06112002.asp

Hope it helps

Best regards,
Terry Fei

Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights
 
Back
Top