"Colin Robinson" <ColinRobinson@newsgroups.nospam> wrote in message
news:eCYXHzVGGHA.2708@TK2MSFTNGP11.phx.gbl...
> Appologies for cross posting this but languages.vb may not be the correct
> place to have asked in hindsight.
>
> 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 seperate spawned threads 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
>
>

"W.G. Ryan eMVP" <WilliamR***@gmail.com> wrote in message
news:eSU9yJYGGHA.312@TK2MSFTNGP09.phx.gbl...
> Colin - one quick way to get the responsive UI is to use an Async
> delegate. If you're using the 2.0 framework you may be able to use MARS to
> accomplish some of what you want to.  Right off the bat I don't see
> anything wrong with the code, I'm not on my dev machine so I'll take a
> look at it when I get back home.  I have a quick example of using async
> delegates here http://www.knowdotnet.com/articles/reponsiveui.html
>
> Two things though you might want to be aware of... 1)  The close doesn't
> appear to be in a finally block so may not execute.  Connections aren't
> thread safe btw either but that's not the problem - I just mention it as a
> FYI.  But if an exception is throw, the connection will stay open and that
> can cause a problems - (as a sample, intentionally throw an exception in
> your execution after opening a connection putting it in a loop, it won't
> be long before you experience major drama).
> 2- Believe it or not, hackers can actually launch a pretty destructive
> injection attack by virtue of a connection string. For instance, if they
> used blahblahblah; trusted_connection = true, you'll actually end up using
> the trust instead of the other account which depending on your security
> configuration, may allow them to connect without knowing the password.
> They can also specify a minimum and maximum pool size of 1 billion for
> instance that can cause some real mischief.  Main point, if you're
> allowing user input for the connection string, you may want to reconsider.
> In the 2.0 framework there's a ConnectionStringBuilder object that you
> want to use for the same reason you want to use paramaterized queries
> instead of concatenated strings - Microsoft knows better than anyone what
> all can be done with connection strings and they've built the
> ConnectionStringBuilder with all that knowledge - not only is it safer,
> it's easier to use.  However this isn't the problem you're facing, again I
> mention it simply as a FYI. I'll get back to you shortly when I get home
> and see if we can't solve your problem.
>
> Cheers,
>
> Bill
> "Colin Robinson" <ColinRobinson@newsgroups.nospam> wrote in message
> news:eCYXHzVGGHA.2708@TK2MSFTNGP11.phx.gbl...
>> Appologies for cross posting this but languages.vb may not be the correct
>> place to have asked in hindsight.
>>
>> 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 seperate spawned threads 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
>>
>>
>
>