SqlConnection/SqlCommand Reset ConnectionTimeout

[andegre]

Hello,

I have a c# job that calls a stored procedure which will update values in my database. The stored procedure only updates 25000 records per run (top 25000). Then in my c# code, I have a do/while loop that will repeatedly call that stored procedure until all 5 million records have been processed.

My problem, after updating around 125000 records (runs loop 5 times), it tells me the connection timed-out. It gives the error "Timeout period elapsed prior to completion of the operation"...

Is there a way to reset the connectiontimeout on each loop run instead of having that connectiontimeout number accumulate for all loop runs?

Thanks,
andegre

 

[RPredrag]

In your connection string you can set Connection TimeOut=0; it should work.

 

[andegre]

What if the job will run for more than 24 hours?

 

[jmeckley]

What if the job will run for more than 24 hours?

1 connection open for more than a few minutes is considered long. hours! I would figure out a new approach.

manipulating this much data at once is known as ETL (Extract, Transform, Load). There are 2 frameworks out there that handle this well.
SSIS (MS)
Rhino.ETL (OSS)

Not only would you want to consider the ETL approach. I would also recommend processing smaller chunks of data across multiple commands/connections.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190