I was going to use this in my application and quite frankly was shocked there was not a "do this or do that" for how to use LLBLGen with this!

There are some messages about people trying and some succeeding and the internal code needing lots of work and it not working with SS. http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=5710&HighLight=1

I would like to revive this. It seems Otis and the LLBLGen guys have not really sat down with this issue with full understanding (maybe they have), but let's review:

1) You state that it will not work with SS because 'connection must stay open' - that is not true. It does not have to stay open. 2) You state it would take major work in adapter and not possible in SS because of #1. I wouldn't think this is hard - but you know best. 3) I am going to stick with SQL2005 which does not require polling. This could work for SQL2000 also, but needs a little additional work.

To illustrate what is required - here is some modified code that performs SQLDependency caching using standard ADO.net stuff...

    public SomeCollection SomeDependencyCode()
    {
        lock (_lock)
        {
            // Return immediately if this method has been called before
            if (_root != null)
                return _root;

            // Query the database for items
            SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["serverConnect1"].ConnectionString);

            try
            {
                SqlCommand command = new SqlCommand("SomeStoredProc", connection);
                command.CommandType = CommandType.StoredProcedure;

                // Create a SQL cache dependency if requested
                SqlCacheDependency dependency = new SqlCacheDependency(command);

                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                _indexID = reader.GetOrdinal("ID");

                if (reader.Read())
                {
                    _root = new SomeCollection();

                    // Build collection
                    while (reader.Read())
                    {
                        _root.Add(NewItemFromReader(reader));
                    }

                    // Use the SQL cache dependency
                    HttpRuntime.Cache.Insert(_cacheDependencyName, new object(), dependency,
                        Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.NotRemovable,
                        new CacheItemRemovedCallback(OnSomeDataChanged));
                }
            }
            finally
            {
                connection.Close();
            }

            return _root;
        }
    }

    void OnSomeDataChanged(string key, object item, CacheItemRemovedReason reason)
    {
        lock (_lock)
        {
            if (key == _cacheDependencyName && reason == CacheItemRemovedReason.DependencyChanged)
            {
                _root = null;
            }
        }
    }

So, to review - you will see that the connection is CLOSED - so that elimiates issue #1 above. You will also see the SQLDependency needs 1) The connection string (which you can use something LLBLGen may provide to hand the connection string our just read it from the .config file yourself). 2) A reference to the SqlCommand actually used for the query. Ultimately LLBLGen sends it through SQLCommand - so just giving us access to that object which was created would work fine.

How can we easily make this work? Does not seem too bad?

<edit> Is creating custom collections for everything we want to do this to the way to go? http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=6580 BTW, I am using SS. </edit>

WayneBrantley wrote:

I was going to use this in my application and quite frankly was shocked there was not a "do this or do that" for how to use LLBLGen with this! There are some messages about people trying and some succeeding and the internal code needing lots of work and it not working with SS. http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=5710&HighLight=1

I would like to revive this. It seems Otis and the LLBLGen guys have not really sat down with this issue with full understanding (maybe they have), but let's review:

1) You state that it will not work with SS because 'connection must stay open' - that is not true. It does not have to stay open.

You're right, the connection doesn't seem to be left 'open' for .NET code. The underlying connection is of course either kept open for messages returned, or a new connection is re-opened for polling.

2) You state it would take major work in adapter and not possible in SS because of #1. I wouldn't think this is hard - but you know best.

It's not 'major' work for adapter, you just have to override some methods and grab the command. That's basicly it. For selfservicing you have the problem that grabbing the command is the hard part.

So, to review - you will see that the connection is CLOSED - so that elimiates issue #1 above. You will also see the SQLDependency needs 1) The connection string (which you can use something LLBLGen may provide to hand the connection string our just read it from the .config file yourself). 2) A reference to the SqlCommand actually used for the query. Ultimately LLBLGen sends it through SQLCommand - so just giving us access to that object which was created would work fine.

In adapter that's easy, it's handed to you in an override of a method which is created for that purpose.

For selfservicing this is harder to do because it's not extensible in a way that you can obtain the command object: queries are created inside DaoBase and executed there.

Of course, you can override Execute*RetrievalQuery routines in the Dao class of the entity in question (it's a generated class after all). This gives you access to the query generated and thus to the command object to use for the dependency.

The problem is... how do you relate that specific query to which element? If lazy loading is triggered, a query is produced, but the code doing that has to be updated to grab the command (or commandS, for example when prefetch paths are executed) and tie it to ... what exactly? IMHO the cached element so for example a routine fetches a graph, processes it, stores processed result in cache, and adds all commands used to fetch the graph as dependencies for that processing result. Then, next time the cached result is either removed or still there.

The main problem thus remains grabbing the command objects for a series of actions. Selfservicing doesn't use a centralized object, but uses per entitytype a dao object so you probably have to keep track of all these to grab all commands for a series of actions.

I agree there are edge cases in SS that make you question - 'how can that work'. Lazy loading and other such items.

However, the restrictions on this type of notification is actually HUGE. http://msdn.microsoft.com/en-us/library/aewzkxxh.aspx

Most of these scenarios are not covered. Basically, select * from table (NOTE, you cannot use distinct!)

It is really just straight data it is meant for. "select * from sometable" and that is a basic list of data or whatever. We would then easily be able to cache that and get notifications of that. And that is exactly how I would use it.

It would be SomeCollection.GetMulit() or SomeTypedList.Fill() that I would be using this for. Lazy loading would not be used (or cached of course).

My point is that the actual feature is very limited, so you can afford to have your support of it very limited also. So, is there some extension point (or one we can add) that gets me access to the 'base' sqlcommand use for a SS collection? Also, since typedLists would not participate in any sort of lazy loading scenario - seems that one would be especially easy?

WayneBrantley wrote:

I agree there are edge cases in SS that make you question - 'how can that work'. Lazy loading and other such items.

However, the restrictions on this type of notification is actually HUGE. http://msdn.microsoft.com/en-us/library/aewzkxxh.aspx

Most of these scenarios are not covered. Basically, select * from table (NOTE, you cannot use distinct!)

It is really just straight data it is meant for. "select * from sometable" and that is a basic list of data or whatever. We would then easily be able to cache that and get notifications of that. And that is exactly how I would use it.

Hmm, that's indeed pretty limited.

It would be SomeCollection.GetMulit() or SomeTypedList.Fill() that I would be using this for. Lazy loading would not be used (or cached of course).

My point is that the actual feature is very limited, so you can afford to have your support of it very limited also. So, is there some extension point (or one we can add) that gets me access to the 'base' sqlcommand use for a SS collection? Also, since typedLists would not participate in any sort of lazy loading scenario - seems that one would be especially easy?

The extension point is the Dao class generated for the particular entity or the TypedListDAO for typedlists. There, override ExecuteMultiRowRetrievalQuery for entities, and ExecuteMultiRowDataTableRetrievalQuery for datatable/typedlist fetches.

With the override, you get the IQuery object which contains the IDataCommand object with the query syntaxis. You then have to setup some kind of system to send back the grabbed command object so you can setup the dependency object as the Dao object at runtime isn't something you instantiate (except for typedlists, where you DO instantiate the Dao )

Hi Otis,

Would kindly be able to provide an example for TypedViews (in particular)?

To explain my scenario, I have a TypedView as the (single) datasource for multiple devices via WCF. I want to implement the SqlDependency.OnChanged event so that when the underlying data is updated, the TypedView will be updated via callback.

Thanks,

Stuart.

Adapter or selfservicing? With the descriptions given in this and linked threads, did you locate the places where you have to add some code?

Hi Otis,

I'm not sure if I'm on the right track or not but I started by inheriting the Adapter and overriding the RetievalQuery making available the QuertyToExecute.

    public class MyDataAccessAdapter : DataAccessAdapter
    {
        public IRetrievalQuery QueryToExecute { get; private set; }

        public override bool ExecuteMultiRowDataTableRetrievalQuery(IRetrievalQuery queryToExecute, System.Data.Common.DbDataAdapter dataAdapterToUse, System.Data.DataTable tableToFill, SD.LLBLGen.Pro.ORMSupportClasses.IFieldPersistenceInfo[] fieldsPersistenceInfo)
        {
            QueryToExecute = queryToExecute;

            return base.ExecuteMultiRowDataTableRetrievalQuery(queryToExecute, dataAdapterToUse, tableToFill, fieldsPersistenceInfo);
        }
    }

When I do my myDataAccesAadapter.FetchTypedView(blahView) I should have access to all the things that I need to create my SqlDependency, I think?

Let me know if I'm on the right track or way off...

Cheers.

You don't have to override that routine, as typedlist calls also call that routine. If you don't mind that, then that's a good candidate, though you can also override OnFetchTypedViewComplete.

This gets the query passed in as well, which has just been completed. a retrievalquery contains the IDbCommand object you need to setup the dependency.