Watch for a table new records in sql database

by

Here are my propositions:

  1. If you are able to add duplicate table to database then here is a solution. You have your table1 and table2 (Copy of table1). When you inserting new records to table1, you can compare them with existing records in your table2 and thus find new records. After comparing you should add all new records to table2. This is some kind of synchronization. This can be made via stored proc or programatically.

  2. You don’t need any another tables. You can store all your data in your app cache and check with some period of time (for example 5secs) are there any new events, that aren’t exist in your cache. If they aren’t exist – notify them in your log or somewhere else and add them to cache. But if there are too many records, the processing time will be greatly increased + memory consumption.

  3. If you are able to change db then you can add something like ‘isNew’ column to your table. When a new data came from the website, the column will be ‘true’, your program can track this, and after processing set this flag to false for each record. (If the website can’t set this flag, you can use SQL TRIGGER AFTER INSERT to set the flag value to true. Website can’t even know about this feature if it is third-party web site or you don’t want to change anything there)

  4. Here is article about EF changes tracking:
    http://blogs.msdn.com/b/adonet/archive/2009/06/10/poco-in-the-entity-framework-part-3-change-tracking-with-poco.aspx

But the problem is that you should check whole table for changes via EF that will hit your app performance.

Here are useful info about SQL Server side change tracking and implementation concepts:
http://www.mssqltips.com/sqlservertip/1819/using-change-tracking-in-sql-server-2008/
http://msdn.microsoft.com/en-us/library/bb933994.aspx

More Related Contents:

  1. summing values of a column based on id [closed]
  2. MSSQL Error ‘The underlying provider failed on Open’
  3. Conversion of a datetime2 data type to a datetime data type results out-of-range value
  4. Getting data from stored procedure with Entity Framework
  5. Entity framework very slow to load for first time after every compilation
  6. Writing Recursive CTE using Entity Framework Fluent syntax or Inline syntax
  7. How can I lock a table on read, using Entity Framework?
  8. Serialization of Entity Framework objects with One to Many Relationship
  9. Mapping composite keys using EF code first
  10. EF 6 – How to correctly perform parallel queries
  11. Entity-framework code is slow when using Include() many times
  12. ‘datetime2’ error when using entity framework in VS 2010 .net 4.0
  13. Delete a single record from Entity Framework?
  14. Optimistic concurrency: IsConcurrencyToken and RowVersion
  15. Entity Framework 6 transaction rollback
  16. The LINQ expression could not be translated and will be evaluated locally
  17. Dynamic query with OR conditions in Entity Framework
  18. Entity Framework | Sequence contains more than one matching element
  19. Keyword Not Supported: Metadata
  20. Why is EF generating SQL queries with unnecessary null-checks?
  21. How to Specify Primary Key Name in EF-Code-First
  22. How to Identify the primary key duplication from a SQL Server 2008 error code?
  23. Unable to connect to localDB in VS2012 – “A network-related or instance-specific error occurred while establishing a connection to SQL Server…”
  24. Should the order of LINQ query clauses affect Entity Framework performance?
  25. async/await – when to return a Task vs void?
  26. What is the purpose of self tracking entities?
  27. Creating Composite Key Entity Framework
  28. Serialization breaks in .NET 4.5
  29. Entity Framework: field of composite key cannot be nullable?
  30. Large Object Heap Compaction, when is it good?

Leave a Comment