CODEWITHSUNDEEP
c#asp.net.netsql
user1048083
user1048083

Reputation: 93

Best practice for adding sql records from an asp.net front end?

I have a website that takes user input, processes it, and then adds a record to a sql table.

I ran into a problem this weekend, where the SQL server was acting up and left the user with a really long processing time with a timeout response at the end. On top of that, the processed data was lost.

Ultimately, I want to know if it's possible to somehow keep this processed data stored somewhere until SQL is working again, and then add the records?

I imagine that this might be done with web services? Or can it be done in asp.net code behind?

Upvotes: 2

Views: 271

Answers (1)

competent_tech
competent_tech

Reputation: 44941

We dealt with this scenario awhile back when we had a fax server that was responsible for processing incoming faxes and storing them in a database, but the database was less than reliable.

In this case, if we couldn't get to SQL Server, we would serialize the data to a queue on disk and set a flag in the application indicating that SQL Server was offline. Any subsequent submissions would be stored in the disk queue when this flag was set.

We would then check SQL Server regularly to see if it was back up and, when it was, we would process each of the files in the queue and then turn the offline flag off.

In ASP.Net, once SQL Server is offline, you could start a thread that monitors SQL Server and, when it comes back online, perform this processing.

However, in the case that you have described, it sounds like either someone started a transaction and didn't finish it or a maintenance operation (DBCC, backup) was taking place.

If this happens regularly, you will probably need to set a CommandTimeout that is slightly longer than the expected normal duration (say double) and, if the operation doesn't complete in that time frame, either tell the user there is a problem or go into caching mode.

Upvotes: 1

Related Questions