This dataAdapter update code keeps timing out...

Basically, I'm just iterating a searchresult collection, sticking it
into a datatable, and trying to update the source database. However, it
seems that no matter what I set the batch size or timeout (on the
connection string) to, it keeps timing out about 85% of the time! It's
about 130,000 records with 20 mostly varchar (50) fields each.
What am I doing wrong? The sql server is fine.

I'd really appreciate any help...


Private Function InsertAllADRecords(ByVal adResultsCollection As
DirectoryServices.SearchResultCollection) As Boolean
Dim iIndex1, iIndex2 As Integer
Dim oEntry As DirectoryServices.SearchResult
Dim strsql As String
Dim item As Object
Dim strProxy As String
Dim objProxy As
System.DirectoryServices.ResultPropertyValueCollection
Dim dsCurrent As New DataSet
Dim dsChanges As New DataSet
Dim dsProxies As New DataSet
Dim dsProxyChanges As New DataSet
Dim dtAllad As DataTable
Dim dtProxy As DataTable
dropAlladIndexes()
dropProxyIndexes()
Dim intProxyCount As Integer = 0
Dim intADCount As Integer = 0

opensql(2) 'just code to check if the sql server's open for
this connection. If not, it
'opens it...
opensql(1)
Dim sqlprxyAdapter As New SqlDataAdapter("Select * from
proxyAddresses;", sqlConnection2)
sqlprxyAdapter.UpdateBatchSize = 5000
sqlprxyAdapter.Fill(dsProxies, "proxyAddresses")
dtProxy = dsProxies.Tables("proxyAddresses")

strsql = "INSERT INTO TRADOC.dbo.allAD
(objectGUID,cn,mail,targetAddress," _
& "givenName,initials,sn,adsPath,dn) " _
& "VALUES(@objectGUID,@cn,@mail,@targetAddress,@givenName," _
& "@initials,@sn,@adsPath,@dn)"

Dim sqlAdapAllad As New SqlDataAdapter("SELECT cn, objectGUID,
mail, targetAddress, givenName, initials, sn, adsPath, dn, ou FROM
allAD", sqlConnection1)
'Dim sqlDS As New DataSet
sqlAdapAllad.UpdateBatchSize = 5000
opensql(1)
sqlAdapAllad.Fill(dsCurrent, "allAD")
dtAllad = dsCurrent.Tables("allAD")

Dim sqlAllADbuilder As SqlCommandBuilder = New
SqlCommandBuilder(sqlAdapAllad)

sqlAllADbuilder.QuotePrefix = "["
sqlAllADbuilder.QuoteSuffix = "]"
'sqlAllADbuilder.DataAdapter.UpdateCommand.CommandTimeout =
1200


Dim sqlProxybuilder As SqlCommandBuilder = New
SqlCommandBuilder(sqlprxyAdapter)

sqlProxybuilder.QuotePrefix = "["
sqlProxybuilder.QuoteSuffix = "]"

Dim drAllAD As DataRow
Dim drProxy As DataRow
drProxy = dtProxy.NewRow
drAllAD = dtAllad.NewRow
Dim adArray(9) As String
Dim drProxyNew As DataRow
iIndex1 = 0

For Each oEntry In adResultsCollection
'If wrkrMain.CancellationPending Then e.Cancel = True
iIndex2 = 0
'adArray = Nothing
Try
'Cast it as a guid
objProxy = oEntry.Properties("proxyAddresses")
strProxy = ""
For Each item In objProxy
If InStr(item.ToString, "SMTP:") Then
drProxyNew = dtProxy.NewRow
drProxyNew(0) = item.ToString
dtProxy.Rows.Add(drProxyNew)
'drProxy("proxyAddress") = item.ToString
End If
Next
Dim objectGuid As Guid = New
Guid(DirectCast(oEntry.Properties("objectGUID")(0), Byte()))
Dim rowAllad As DataRow = dtAllad.NewRow()

' Set values in the columns:
rowAllad("objectGUID") = objectGuid
rowAllad("cn") = oEntry.Properties("cn")(0).ToString

Try
rowAllad("targetAddress") =
oEntry.Properties("targetAddress")(0).ToString
Catch excp As Exception
rowAllad("targetAddress") = "SMTP:" &
oEntry.Properties("mail")(0).ToString
End Try
Try
rowAllad("mail") =
oEntry.Properties("mail")(0).ToString
Catch excp As Exception
rowAllad("mail") = ""
End Try
Try
rowAllad("givenName") =
oEntry.Properties("givenName")(0).ToString
Catch excp As Exception
rowAllad("givenName") = ""
End Try
Try
rowAllad("initials") =
oEntry.Properties("initials")(0).ToString
Catch excp As Exception
rowAllad("initials") = ""
End Try
Try
rowAllad("sn") =
oEntry.Properties("sn")(0).ToString
Catch excp As Exception
rowAllad("sn") = ""
End Try
rowAllad("adsPath") =
oEntry.Properties("adsPath")(0).ToString
rowAllad("dn") =
oEntry.Properties("distinguishedName")(0).ToString
dtAllad.Rows.Add(rowAllad)

iIndex1 += 1
'this just updates the form's progress report 100
records...
If Math.IEEERemainder(iIndex1, 100) = 0 Then
wrkrMain.ReportProgress(11, New String() {"Inserted
contact #: " & iIndex1.ToString})
End If
Catch excp As Exception
logit("InsertADRecords: " & excp.Message)
End Try
Next

'Here's where it always times out.... hits the catch with a timeout
error...
Try
opensql(2)
intProxyCount = sqlprxyAdapter.Update(dsProxies,
"proxyAddresses")
logit("Proxy addresses inserted: " & intProxyCount)
Catch excp As Exception
logit("Problem updating sqlprxyAdapter: " & excp.Message)
End Try

Try
intADCount = sqlAdapAllad.Update(dsCurrent, "Allad")
logit("AD accounts inserted: " & intADCount)
Catch excp As Exception
logit("Problem updating sqlAdapter (allad): " &
excp.Message)
End Try

sqlAdapAllad.Dispose()
sqlprxyAdapter.Dispose()
adResultsCollection.Dispose()
dsCurrent.Dispose()
dsProxyChanges.Dispose()
dsProxies.Dispose()
createAllAdIndexes()
createProxyIndexes()
closesql(1)
closesql(2)
End Function