0

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.

Community
  • 1
  • 1
makambi
  • 1,100
  • 3
  • 13
  • 30

1 Answers1

1

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.

sarin
  • 5,227
  • 3
  • 34
  • 63
  • As I thought, Sql Dependency just for tracking changes and to update ui. Not for managing DB state. – makambi Jul 01 '14 at 07:16
  • @makambi feel free to mark my response as having answered your question if you believe so! Green tick! – sarin Jul 01 '14 at 08:32