Generic Repository with SqlDependency RRS feed

  • Question

  • Hi,

    For one of our projects we want to make a generic repository. Only the idea behind this repository is that it turns itself up to date. This should be done with SqlDependency. Now we got two questions:

    • Are there better ways to do this?
    • How to use SqlDependency in a repository?

    Kind regards,


    Wednesday, April 28, 2010 7:59 AM

All replies

  • can you please elaborate or explain your question properly
    Baji Prasad
    Wednesday, April 28, 2010 8:40 AM
  • are you using Linq2Sql for your ORM. if so query notifications with Linq2Sql is possible. plz check this

    Baji Prasad
    Wednesday, April 28, 2010 8:45 AM
  • Hi Baji Prasad,

    Sorry about my short explanation. We want to make a general repository which we can use for products, orders, customers ....

    An example:

    When we need to show a list of customers we can do: Datagridview.datasource = Repository<Customer>.getAll().
    Now we got a list of all customers. But when a new customer is added to the database by an external application, this list/repository is not updated automaticly.

    Can we use the SqlDependency for this job? If yes are there any examples in combination with a repository?
    Are there other controls/options to do this job?

    Kind regards,


    Wednesday, April 28, 2010 9:22 AM
  • yes it is possible,

    it depends in which way do you implement.

    for instance if you are using Linq2Sql to populate your entities then refer this uRL


    other wise  create sqldependency on a particular command. say  for all the customer records in your customer table.

    when ever a record got updated in customer table. there will be a call back to your GUI. in the call back event repopulate  your entity objects and assign back to your datagridview.

    pleae refer to this, will give a basic understanding and usage of sqldependency

    hope this helps.

    pleas mark it as a Answer. if this solves your problem


    Baji Prasad
    Wednesday, April 28, 2010 9:51 AM
  • I would suggest a separate caching layer. It decides whether what it's got cached is up to date or not using sqlcachedependency

    Either presents you with the data stored in an object out of cache  or reads it out the database presents it and stores in cache.

    Along these lines

    If you interfaced to this system via something like a WCF service then you could use the same repository for web, windows or whatever.

    Wednesday, April 28, 2010 10:31 AM
  • Hi,

    I like both ideas. We can create a repository based on LinQ wich get his data from the cache provider. Only whats the best way to update the repository from the cache provider (SqlDependency OnChange Callback)?

    Kind regards,


    Wednesday, April 28, 2010 2:07 PM
  • Depends on your pattern of use.

    If reading the data will take a long time, the client needs it quickly and it only occasionally changes then you want the data re-read as soon as the cache is invalidated.

    If updates go through the same layer then you know when that data is being changed on the server.

    So EG a user commits an update.  The process hands the changes to the business server.  That writes it to the database and deletes the old cache of that data.  It could potentially then also re-read the base data and cache it.  The load here is on your business server.

    This also has the advantage that there is no overhead from sql notification.

    With large sets of data it is often the case that you can split the data cached into an object per area, team or whatever.  So a given table might have dozens of cached objects.  You might also decide that you only cache open orders or something.  

    Business servers are cheap so you could have 10 of them sharing the job of caching.

    Or you could rely on sql notification.  This is necessary if you have processes that will not go through the business server layer.

    Thursday, April 29, 2010 12:15 PM
  • Hi,

    Thank you for your advice. Only not all of my updates go through the same layer. For example when an another (external) application performs an update is not going through the same layer. I can use sql notification on each database table (about 25 tables) only what about performance?

    Kind regards,


    Friday, June 4, 2010 10:20 AM
  • Performance will depend on how much data you have in those 25 tables and how often it changes.


    Monday, June 7, 2010 9:52 AM