help with async = true scenario

[Colin Robinson]

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]

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]

Thanks.

I am aware of the injection stuff this code is running internally on a
10.10.xx.xx ip address so should be reasonably safe in that respect.

The connection strings may benefit from the connectionstring object now I
know the driver for it is. is there a new wizard front end for creating
them.

given the code will allow tsql like "drop database" Injection attacks and
security is not a concen its a quick tool for our dbas im creating.

All comments are welcome, the app will be better for it.

Thanks again

Colin

 

[Kevin Yu [MSFT]]

Hi Colin,

To get a wizard for building connection strings, you can use the OLE DB
Data Link Properties dialog box. However, it's not designed for .NET, you
have to make some changes to the connection string after getting it. It's a
COM object and you can check the following KB article for how to use it.

http://support.microsoft.com/default.aspx?scid=kb;en-us;286189

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."