0

I am looking at the possibilities when it comes to making a trigger on a SQL server, trigger a function on my website (without having to poll it every x minutes).

The reason being, I am attempting to create a caching service for my SQL data to where it will only update the cache when there are changes to the table it is setup for. The idea would be that items which are in the database, rarely changed and used often enough to want to skip the expensive trip to the database.

any ideas would be great, thanks.

Krum110487
  • 611
  • 1
  • 7
  • 20

3 Answers3

1

SQL Server supports event notifications for just this type of problem.

http://msdn.microsoft.com/en-us/library/ms190427(v=sql.105).aspx

Bill Gregg
  • 7,067
  • 2
  • 22
  • 39
  • it's been awhile since I have dabbled in services, do you have an example with this being used in a asp.net environment, I did a google search and the results were less than helpful. I would like to avoid making my own service, but I will if needed. – Krum110487 Jun 28 '13 at 15:33
1

If it's data that you want to put in the application cache then you could consider using the SqlCacheDependency class (http://msdn.microsoft.com/en-us/library/ms178604(v=vs.100).aspx). This will expire the cache when there is a predefined change in the database thus avoiding the need for any kind of polling.

Of course it depends what your exact problem is to whether this will meet your needs

Crab Bucket
  • 6,219
  • 8
  • 38
  • 73
  • while this is a cool solution, I believe (from my limited research on the class) it still uses the database to check to see if the item has updated, I am attempting to limit the amount of sql traffic as much as possible. Source: http://stackoverflow.com/questions/7353558/how-does-a-sqlcachedependency-know-when-to-communicate-back-to-any-listeners-whe – Krum110487 Jun 28 '13 at 15:24
1

SQL Server has CLR integration (albeit at last check, only with .NET 2.0). This means you can run some assembly marked as such from a function/stored-procedure/query. Here is the documentation:

MSDN SQL Server CLR description

You can write an assembly with an exposed method for SQL Server and run it directly from there, thus completing your loop in C#/VB/.NET language of your choosing. This may be a little heavy handed if none of the above answers have what you're looking for, but I'm pretty sure offers the most flexible approach where you control everything.

welegan
  • 3,013
  • 3
  • 15
  • 20
  • I investigated CLR, but I wanted to see if there was anything baked in, I assume I would need to create some sort of call remote service (assuming the server is remote from the website) – Krum110487 Jun 28 '13 at 15:28
  • Given that SQL Server runs as its own service separate from your website, the only way for it to communicate back to your website with SQL Server as the origin of the event you are trying to handle is to make a service call. If you want a more automated caching solution, I would recommend looking into redis which has a persistance layer idea that you can set to be your SQL server. The website is at: http://redis.io/ Alternatively, I can tell you from experience that writing services is very easy with this framework: http://www.servicestack.net/ and there are WebAPI and plain old .NET MVC also. – welegan Jun 28 '13 at 16:05
  • fortunately, I have created a service before, I was just curious if I could avoid it as I want a least effort solution (for the user of this cache). I will just have to look up how to expose code between service and website, this is a good start so far. I am also considering the possibility of creating a page like this: www.mysite.com/sqlcache?table="table1;table2:item2;" this then my CLR could simply "navigate" to the link, I guess the only thing I would have to worry about is tampering. – Krum110487 Jun 28 '13 at 16:55