Bulk Update HELP!

I have an unusual question... I have a table that I have to select the top 300 records from, check the date on each record, and using an if... then... statement, loop through each record updating a field based on criteria.



The problem is that it takes so long to do this. Is there a way to populate an object and pass that object with the data to a SQL stored procedure to have the SQL server do all the updating as opposed to the application doing the record updating? Does that make sense? Here's a sample of the code and you'll see what I'm talking about.



Thanks in advance for any help or advice you can give.






Imports Microsoft.SqlServer.Server

Imports System.Data.SqlClient

Imports CallTracker_AgingCheck

Module CallTracker

Sub Main()

GetWebAgingData()

GetWebCallsData()

End Sub





#Region "Get Data"

Public agingStatusNumber As New ArrayList

Public agingEscalationNumber As New ArrayList

Public callStatusNumber As New ArrayList

Public CallStatus As New ArrayList



Public Sub GetWebCallsData()

Dim dt As New DataTable

Dim Criteria As String = "SELECT TOP 300 CallID, TIMEOFCALL, Status, StatusNumber " & _

"FROM WebCalls " & _

"WHERE (Status = 'OPEN') OR " & _

"(Status = 'IN PROCESS') OR " & _

"(Status = 'WORKORDER') OR " & _

"(Status = 'PRIORITY') OR " & _

"(Status = 'PENDING') " '& _

'"ORDER BY TimeOfCall DESC"

Dim Fa As String = String.Empty

Dim fromDATE As String = String.Empty

Dim toDate As String = String.Empty

Dim ds As New DataSet()

Dim tbl As String = "WebCalls"



Dim x1 As Integer = 0

Try

Using cn As New SqlClient.SqlConnection(Database.SQLConnection)

cn.Open()

Using cm As SqlClient.SqlCommand = cn.CreateCommand()

cm.CommandText = Criteria

cm.CommandType = CommandType.Text

cm.Parameters.AddWithValue("@CALLID", Fa)

cm.Parameters.AddWithValue("@TIMEOFCALL ", Fa)

cm.Parameters.AddWithValue("@STATUS", Fa)

cm.Parameters.AddWithValue("@STATUSNUMBER", Fa)

Dim Age As TimeSpan = Nothing

Dim _totalHours As Integer = Nothing

Dim EscalateTime As Integer = agingEscalationNumber.Count

Dim DAUpdateCmd As SqlCommand

Using da As New SqlDataAdapter(Criteria, cn)

da.Fill(dt)

Dim callID As Integer = Nothing

Dim statNO As Integer = Nothing

Dim toc As Integer = dt.Rows.Count

Dim x As Integer = 0





For x = 0 To toc - 1

Dim y As Date = dt.Rows(x).Item(1) 'gets the date from the first row in WebCalls for comparison with WebAging

Age = Today.Subtract(y)

_totalHours = Age.TotalHours

If _totalHours > 744 Then

Console.WriteLine("This record is older than 30 days")

Else

callID = dt.Rows(x).Item(0)



DAUpdateCmd = New SqlCommand("Update WebCalls SET STATUSNUMBER = @STATUSNUMBER where CALLID = @CALLID", da.SelectCommand.Connection)

DAUpdateCmd.Parameters.Add(New SqlParameter("@STATUSNUMBER", SqlDbType.Int))

DAUpdateCmd.Parameters("@STATUSNUMBER").SourceVersion = DataRowVersion.Current

DAUpdateCmd.Parameters("@STATUSNUMBER").SourceColumn = "STATUSNUMBER"

DAUpdateCmd.Parameters.Add(New SqlParameter("@CALLID", SqlDbType.Int))

DAUpdateCmd.Parameters("@CALLID").SourceVersion = DataRowVersion.Original

DAUpdateCmd.Parameters("@CALLID").SourceColumn = "CALLID"

da.UpdateCommand = DAUpdateCmd

da.Fill(ds, tbl)

Dim z As Integer = 0

Dim _stat0 As Integer = agingEscalationNumber(0)

Dim _stat1 As Integer = agingEscalationNumber(1)

Dim _stat2 As Integer = agingEscalationNumber(2)

Dim _stat3 As Integer = agingEscalationNumber(3)

Dim StatusNo_0 As Integer = agingStatusNumber(0)

Dim StatusNo_1 As Integer = agingStatusNumber(1)

Dim StatusNo_2 As Integer = agingStatusNumber(2)

Dim StatusNo_3 As Integer = agingStatusNumber(3)



If _totalHours <= _stat0 Then



Try

ds.Tables(tbl).Rows(3)("STATUSNUMBER") = StatusNo_0

da.Update(ds, tbl)

Console.WriteLine("Status Number: " & Str(StatusNo_0) & " callID = " & callID)

Catch ex As Exception

Console.WriteLine(ex)

End Try



End If



If _totalHours >= (_stat0 + 1) And _totalHours <= _stat1 Then

Try

ds.Tables(tbl).Rows(3)("STATUSNUMBER") = StatusNo_1

da.Update(ds, tbl)

Console.WriteLine("Status Number: " & Str(StatusNo_1) & " callID = " & callID)

Catch ex As Exception

Console.WriteLine(ex)

End Try

End If



If _totalHours >= (_stat1 + 1) And _totalHours <= _stat2 Then

Try

ds.Tables(tbl).Rows(3)("STATUSNUMBER") = StatusNo_2

da.Update(ds, tbl)

Console.WriteLine("Status Number: " & Str(StatusNo_2) & " callID = " & callID)

Catch ex As Exception

Console.WriteLine(ex)

End Try

End If



If _totalHours >= (_stat2 + 1) And _totalHours <= _stat3 Then

Try

ds.Tables(tbl).Rows(3)("STATUSNUMBER") = StatusNo_3

da.Update(ds, tbl)

Console.WriteLine("Status Number: " & Str(StatusNo_3) & " callID = " & callID)

Catch ex As Exception

Console.WriteLine(ex)

End Try

Else

End If

End If

Next

End Using

End Using

End Using

Catch ex As Exception

Console.WriteLine(ex)

End Try

End Sub

Public Sub GetWebAgingData()

Dim dt As New DataTable

Dim Fa As String = String.Empty

Dim fromDATE As String = String.Empty

Dim toDate As String = String.Empty

Dim Criteria As String = "Select RecordID, StatusNumber, EscalationTime from WebAging" 'create a selection statement"

'don't forget the email addys for sending based on escalation rates.

Dim x1 As Integer = 0

Try

Using cn As New SqlClient.SqlConnection(Database.SQLConnection)

cn.Open()

Using cm As SqlClient.SqlCommand = cn.CreateCommand()

cm.CommandText = Criteria

cm.CommandType = CommandType.Text

cm.Parameters.AddWithValue("@RecordID", Fa)

cm.Parameters.AddWithValue("@StatusNumber ", Fa)

cm.Parameters.AddWithValue("@EscalationTime", Fa)

Using da As New SqlClient.SqlDataAdapter(cm)

da.Fill(dt)

Dim dtCnt As Integer = dt.Rows.Count

Dim x As Integer = Nothing

Dim escalateTime As Integer = Nothing

Dim _StatusNumber As Integer = Nothing

For x = 0 To dtCnt - 1

escalateTime = dt.Rows(x).Item(2)

_StatusNumber = dt.Rows(x).Item(1)

agingEscalationNumber.Add(escalateTime)

agingStatusNumber.Add(_StatusNumber)

Next x

End Using

End Using

End Using



Catch ex As Exception

Console.WriteLine(ex)

End Try

End Sub

#End Region

#Region "Get Status Information From WebCalls"



#End Region









End Module