CODEWITHSUNDEEP
c#sqlsql-serverauto-update
makambi
makambi

Reputation: 1170

Update db table with some schedule

I am developing simple auction app with c# and SqlServer.

I have an Auction entity. For simplicity it has next structure: 

AuctionId
ItemId
StartPrice
StartDate
Status
CurrentPrice
BidderId
LastBiddedat

A know that it is not in normal form, it does not matter here. Not production code.

What I need is to update Auction Status if there have not been any bids in last 2 minutes. No bids - auctions status sets to Inactive.

I can track db changes in c# code via SqlDependency and it works perfectly.

Now I need a mechanism to update db when auction time is up. I would like to do it on database layer for simplicity.

It can be done via SQL Agent, there even is an example how to do it how to schedule a job for sql query to run daily? .

Maybe you have another options? Just curious.

Upvotes: 0

Views: 481

Answers (1)

sarin
sarin

Reputation: 5307

Common solutions are polling or implementing some sort of scheduler. Creating a SQL Dependency won't really help as this is triggered from a database data change. Your trigger is 'the absence of a change'.

Polling from the application is generally a bad idea as this creates unwanted network traffic amongst other issues.

As you mention using the built in SQL Agent Job Scheduler to perhaps run a Stored procedure and update statuses for auctions that are 2+ minutes old and inactive is the best idea. This is part of the database and therefore will create minimal network traffic (if any). It's part of SQL Server and hence is designed to run quickly and smoothly. It's also easy to set up. This would be my recommendation.

Upvotes: 1

Related Questions