Sql server 2005 natively (2000 does sorta) supports callbacks from sql to sqlcommand objects to do row level notifications of data changed events.

For instance, I can register a query (Say select * from customers) with sql and when a row in the customer table changes, it calls back to my app and lets the sqlcommand object know. The command object then updates that single row in it's associated datatable or set. This is a huge new feature that makes caching in asp.net 2.0 very easy to implement and very powerfull.

Does llbl gen support this or will it in 2.0?

Thanks!

jspanocsi wrote:

Sql server 2005 natively (2000 does sorta) supports callbacks from sql to sqlcommand objects to do row level notifications of data changed events.

For instance, I can register a query (Say select * from customers) with sql and when a row in the customer table changes, it calls back to my app and lets the sqlcommand object know. The command object then updates that single row in it's associated datatable or set. This is a huge new feature that makes caching in asp.net 2.0 very easy to implement and very powerfull.

Does llbl gen support this or will it in 2.0?

Thanks!

Actually, you can do this today. The command object just provides the Event, which you handle and re-populate the cached items yourself. I did this, storing full TypedView objects into the cache, and repopulating them via the triggered event. It works great, especially for look-up interface elements from complex queries with few or no predicates, (i.e., when you store City, State, Country in separate tables, but want the combobox to read City, State, Country)

I'm not sure what LLBLGenPro could bring to the table that would make this better, or in what way this caching feature could be supported. I can't think of a way to more closely knit them together.

The way I understood it was that the cached element is invalidated because a dataelement which was used to build that cached element changed, so next time the code which refers to that element will not find it in the cache and re-load it again. Am I correct in that or did I misunderstand it?

swallace wrote:

jspanocsi wrote:

Sql server 2005 natively (2000 does sorta) supports callbacks from sql to sqlcommand objects to do row level notifications of data changed events.

For instance, I can register a query (Say select * from customers) with sql and when a row in the customer table changes, it calls back to my app and lets the sqlcommand object know. The command object then updates that single row in it's associated datatable or set. This is a huge new feature that makes caching in asp.net 2.0 very easy to implement and very powerfull.

Does llbl gen support this or will it in 2.0?

Thanks!

Actually, you can do this today. The command object just provides the Event, which you handle and re-populate the cached items yourself. I did this, storing full TypedView objects into the cache, and repopulating them via the triggered event. It works great, especially for look-up interface elements from complex queries with few or no predicates, (i.e., when you store City, State, Country in separate tables, but want the combobox to read City, State, Country)

I'm not sure what LLBLGenPro could bring to the table that would make this better, or in what way this caching feature could be supported. I can't think of a way to more closely knit them together.

That's good. How do I get to the command object? Can you post a little code with an example. You would usually attach a sqldependancy object or sqlcachedependancy object to the command. Did you do this or something different.

Thanks!

My current customer prefers I not post code, but you're correct, that you create a SqlCacheDependency object, and you have to run the SQL scripts that turn on SQL cache monitoring. We had some questions whether the 'heartbeat' (the query that ran every x seconds checking the change table) would be a problem, but it wasn't. The triggers on table changes didn't concern us either.

Try not to over-think it. Follow the steps in the documentation, especially the vs.net 2005 help document titled 'Caching Application Data', and you won't have any troubles. The 'How-To' tasks are great.

swallace wrote:

My current customer prefers I not post code, but you're correct, that you create a SqlCacheDependency object, and you have to run the SQL scripts that turn on SQL cache monitoring. We had some questions whether the 'heartbeat' (the query that ran every x seconds checking the change table) would be a problem, but it wasn't. The triggers on table changes didn't concern us either.

Try not to over-think it. Follow the steps in the documentation, especially the vs.net 2005 help document titled 'Caching Application Data', and you won't have any troubles. The 'How-To' tasks are great.

Thanks I'll check it out. how do you get to the internal command object in llbl gen? I looked all over and can't find it.

I'm using sql 2005 so I don't need the heartbeat as notification services takes care of it, but I'm stuck on the trying to attach the dependency to the non existant llbl command...

I looked in SS and Adapter and can't find it in either.

Thanks!

anyone know how to get the command object in SS or adapter that LLBL gen uses? I need access to that to attach a dependency object to.

Thanks!

In adapter, you can use teh OnSaveEntity, OnFetchEntity... methods in the DataAccessAdapter. Those methods receive the query object which contains the command object.

OK Thanks!

I wanted to reopen this for a quick follow up. Since moving to llbl 2.0 is there anything in SS that I can use to get to the sql adapter to use a sqldependency object on?

Thanks

Unfortunatly no, not in SelfServicing.

Walaa wrote:

Unfortunatly no, not in SelfServicing.

Hey, You say that its not possible when using SelfServicing. Request you to please gothrough the link

http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=6580&StartAtMessage=0

I did some testing on that and I observed that its working fine., I mean the SQLCacheDependency works fine.

Am I doing anything wrong in that. Currenty I migrated to the 2.0 version and trying to find out the way to get the SQLCacheDependency work for me in SelfServicing (General) Scenario.

Looking for more help in this regard.

Thanks Ravi K. Kalyan

If it works, perfect What you should consider though is that selfservicing doesn't allow you to keep a connection open at least not easily and if you do, you've to understand that the connection shouldn't be shared with other threads/processes, like in a webapp.

Guys,

I'm trying to get this to work myself. In my business tier, I currently cache TypedList collections for lookups using MS Enterprise Library 2.0 Caching, but this only allows timeouts. I would like to move to SqlDependency. swallace said he is using TypedViews, but how to retrieve the SqlCommand object to pass to SqlDependency? I tried using a overriden collection as in (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=6580&StartAtMessage=0), but with using GetMulti, the overridden ExecuteMultiRowRetrievalQuery was never executed. I could create my own SqlCommand specifying my table, but that means my business class has too much info about my data.

Any other suggestions?

A great enhancement would be for LLBLGen to create a "cached view" class that would use SqlDependency internally.

In selfservicing its not really possible to do so, in adapter it is, although it needs some work. It mainly is because of the connection that stays open, though selfservicing is simply opening/closing connections for the action it is performing.