How to tell if a record is still in a SQL table - using linq maybe?

I have an SQL database, which is a "feeder" table. I put records in said table, a 3rd party package consumes (and deletes) them. All hunky dory - until the 3rd party package isn't running. In thinking about how to detect that, I thought to myself... "well... what if I read all the keys in the table (its not very big - max a few dozen records), and kept them, and then in, say, 5 minutes, I checked if any were still in the table ?"

开发者_如何学运维It may not be a brilliant solution, but it sent me off thinking about Linq and whether you could do such a thing (I haven't used Linq before).

So, if I read all the record keys into a DataTable object and then, five minutes later, read all the records into another DataTable object, I can do a Linq select, joining the two DataTable objects on the key column, and then look at the results of "Count", and if one or more, chances are the data in the table isn't being consumed.

Or... is there a "cleverer" way than that ?

Create a DELETE trigger which records in a separate table the timestamp of the last delete. An additional INSERT trigger would record the timestamp of the last insert statement.

Compare the two timestamps.

You could return the identity column value (assuming there is one) after your insert and record it in a separate table along with its commit datetime they just pull outstanding records with;

SELECT * FROM feeder_table F
  INNER JOIN other_table T ON (F.id = T.id)
WHERE DATEDIFF(MINUTE, T.commitdate, GETDATE()) > 5

That way your not persisting data in memory so it will work between application restarts/across machines.

(If this is just for fault detection you would only need to store the last inserted id.)

This is one way:

DataTable t1 = GetData(); // returns a datatable with an Int16 "Id" column
// time passes... a shabby man enters and steals your lamp
DataTable t2 = GetData();

// some data changes have occurred
t2.Rows.Add(null, DateTime.Now.AddSeconds(10), "Some more");
t2.Rows[1].Delete();

EnumerableRowCollection<DataRow> rows1 = t1.AsEnumerable();
EnumerableRowCollection<DataRow> rows2 = t2.AsEnumerable();

var keys1 = rows1.Select(row => (Int16)row["Id"]).ToList();
var keys2 = rows2.Select(row => (Int16)row["Id"]).ToList();

// how many keys from t1 are still in t2
Console.WriteLine("{0} rows still there", keys1.Count(id => keys2.Contains(id)));

But this is more what I had in mind:

DataTable t1 = GetData(); // returns a datatable with an Int16 "Id" column
// time passes... your lamp is getting dim
DataTable t2 = GetData();

// some data changes have occurred
t2.Rows.Add(null, DateTime.Now.AddSeconds(10), "Some more");
t2.Rows[1].Delete();

EnumerableRowCollection<DataRow> rows1 = t1.AsEnumerable();
EnumerableRowCollection<DataRow> rows2 = t2.AsEnumerable();

// how many rows from r1 are still in r2
int n = (from r1 in rows1
        join r2 in rows2 on (Int16)r1["Id"] equals (Int16)r2["Id"]
        select r1).Count();

...which is the "linq/join" method I alluded to in the original question.