CODEWITHSUNDEEP
c#sqlasp.netsql-servert-sql
user6048670
user6048670

Reputation: 2887

Best way in ASP.NET of configuring rows in a database to delete after a certain time

Suppose I have a table in my database like

                           Registrants
=======================================================================
  id |    name    |            email        |    registration_date
=======================================================================
  1  |   "Sam"    | "[email protected]"  |    "2016-03-26T14:25:10"
-----------------------------------------------------------------------
  2  |   "Bob"    | "[email protected]"   |    "2015-12-01T10:09:30"
-----------------------------------------------------------------------
  .  |     .      |           .             |              .
-----------------------------------------------------------------------
  .  |     .      |           .             |              .
-----------------------------------------------------------------------
  .  |     .      |           .             |              .
-----------------------------------------------------------------------
  .  |     .      |           .             |              .

and I want every registrant to automatically be deleted, say, 100 days after registering. What is a proper way to do this, and what is the best way?

The shoddy way I was planning on doing it was going to be to create a sproc 

CREATE PROCEDURE FlushOldRegistrants
AS
BEGIN
   DELETE FROM Registrants WHERE DATEADD(day,100,registration_date) < GETDATE()
END

and in my server-side code invoke that sproc every once in a while like

Script.SetInterval(delegate {
    using (var conn = new SqlConnection(connectionString))
using (var command = new SqlCommand("FlushOldRegistrants", conn) { 
                           CommandType = CommandType.StoredProcedure }) {
   conn.Open();
   command.ExecuteNonQuery();
   conn.Close();
}
 }, 60000); // flush old registrants every hour

But, can I do that all at the database level? Is it possible to create a T-SQL TRIGGER that does this? Or is there an even better way?

Upvotes: 6

Views: 156

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270443

The most obvious method is to use SQL Server Agent. Set up a job that runs every day or once a week, and have it call your stored procedure.

Another option is to leave all the data in the table and create a view:

create view v_Registrants as
   select r.*
   from Registrants r
   where registration_date < DATEADD(day, -100, getdate()) ;

When you access the data through the view, you will only see the most recent data. One advantage of this approach is that it is precise -- you will never get data that is 102 days old or 100 days and 8 hours old -- which is a danger using a scheduled job. In fact, if the exact cut-off is important, you might want this view as well as the job.

In both cases, you are safer using this where statement, because it can make use of indexes (and hence should be faster).

Upvotes: 6

Yogi
Yogi

Reputation: 9759

Such operations are good candidates to run as scheduled tasks.

Now if you want to run them at database end, you can opt for sql jobs. For non db option, there are variety of choices like, Windows Task Scheduler, Windows service, Third party tools like Quartz Or Hanfgire etc.

Each of the options have their own pros and cons. Consider what makes best sense for your application.

Upvotes: 1

Ankit Vijay
Ankit Vijay

Reputation: 4108

If you would like to perform this operation at database level SQL Jobs is a good option

Upvotes: 0

Related Questions