Waiting for events in Atlas and .NET 2.0?

[emer.kurbegovic]

I wonder if I can wait for the "events/notifications" from sql 2005 and
then perform some action when the "event/notification" has been sent.

example:
i have a small table with some statistical data that can be updated at
random point of time (based on when the user action was performed).
now, i am caching this table (as custom object) with the
sqlcachedependency and check the values in that class every few seconds
with the atlas:TimerControl in the MasterPage.
I wonder if there is a way where i can just wait for some kind of
notification when the data in the table has been changed or when the
custom object has been removed from the db because of the
sqlcachedependency and then update the content on the page.

wouldn't this be better than checking this object every couple of
seconds?

i hope i explained what i want to accomplish well enough.

thanks,
emer

 

[Shaun McDonnell]

Emer,

One thing you could do is create a SQL CLR trigger that calls back to your
code, or updates a item in your dependency. Interesting idea.

Shaun McDonnell

 

[emer.kurbegovic]

Shaun,
thanks for the quick response.

do you have any samples for the SQL CLR triggers? i'd like to try it
out.

thanks

 

[emer.kurbegovic]

actually, i've found some examples.

i am trying to figure out how to call back to my code and update the
webform when the trigger runs.

 

[Shaun McDonnell]

http://www.sqljunkies.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk

Let me know if you need anything else.

Shaun McDonnell

 

[emer.kurbegovic]

i've build a sample UDF and deployed it to the sql server successfully,
however when i try to deploy the Sql Trigger i get following error
message:

"Deploy failed"

"Cannot find the object "dbo.Table1" because it does not exist or you
do not have permissions"

what am i doing wrong here?

here is the code for the sql trigger:
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;


public partial class Triggers
{
// Enter existing table or view for the target and uncomment the
attribute line
[Microsoft.SqlServer.Server.SqlTrigger (Name="Table1Updated",
Target="dbo.Table1", Event="FOR UPDATE")]
public static void MessageStatisticsUpdated()
{
// Replace with your own code
SqlContext.Pipe.Send("Trigger FIRED");
}
}

 

[bruce barker \(sqlwork.com\)]

the browser page still has to poll. you can use any ajax technology for
this.

-- bruce (sqlwork.com)

 

[emer.kurbegovic]

i've deplyed the Trigger successfully as well. had to remove the "dbo."
from the table name

like this:
[Microsoft.SqlServer.Server.SqlTrigger (Name="Table1Updated",
Target="Table1", Event="FOR UPDATE")]