Alachisoft Ncache SqlCacheDependency adding dependency through stored procedure

Question

We are trying to implement SqlCacheDependency and while adding the dependency through Stored Procedure it remove the object immediately after adding it to cache.It gives CacheItemRemovedReason as 4.

We have used the Database Synchronization sample provided in mentioned below link.

http://www.alachisoft.com/resources/samples/

string storedProcName = "dbo.TestStoredProc1";

SqlCmdParams sqlParams = new SqlCmdParams();
sqlParams.Value = prod.ProductID;
//sqlParams.
Dictionary<string, SqlCmdParams> prms = new Dictionary<string, SqlCmdParams>();
prms.Add("ID", sqlParams);
_cache.Add(prod.ProductID.ToString(), item,DSWriteOption.None,null);

item.Dependency = new SqlCacheDependency(connString, "select * from dbo.state_lookup where state_id = " + prod.ProductID.ToString());

Database Synchronizationpublic void OnCacheDataModification(string key, CacheEventArg args)
    {
        switch (args.EventType)
        {
            case EventType.ItemAdded:
                // Key has been added to the cache
                Console.WriteLine(key + " Item Added");
                break;

            case EventType.ItemUpdated:
                Console.WriteLine(key + " Item Updated");
                break;

            case EventType.ItemRemoved:
                break;
        }

I have added item change listener and it goes to ItemRemoved section immediatly after going into ItemAdded part.The item removed call back should get called only when data is changed in the database.

SQL Server Version : Microsoft SQL Server 2008 R2 (SP2)

Alachisoft Edition : Enterprise Trial Version

Alachisoft SDK version : 4.9.1.1

score 0 · Answer 1 · answered Feb 27 '19 at 06:53

there are a few things that you need to look into while working with NCache SQLDependency and I have listed them below.

• NCache SQLDependency makes use of SQL Broker service to invalidate and remove items from cache based on notifications. Please make sure that you have enabled the broker service for your database so that NCache SQLDependency can work without any issues as mentioned in the steps here.

• Moreover, please note that SQLDependency has a few limitations on the statements that are supported when you create a query for SQLDependency. This limitation gets inherited by NCache SQLDependency feature as well since NCache depends upon SQL Broker. One of the main limitation is with the SELECT statement, which is that "The statement may not use the asterisk () or table_name. syntax to specify columns.". Your SQLDependency query should follow all the rules and requirements that have been mentioned by Microsoft.

• A complete list of requirements for creating a query for notification on SQL Server can be found at the following MSDN site: https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms181122(v=sql.105)

• Moreover, please note that whenever NCache SQLDependency fails to get registered, the corresponding item (for which the dependency was added) gets removed instantaneously, and an error message gets logged in the server logs. Please go through your server logs and you should be able to see an error entry stating that SQL Broker was unable to establish SQLDependency due to an invalid SQL query.

Once you have addressed all the above mentioned items, your SQLDependency should work flawlessly.

Alachisoft Ncache SqlCacheDependency adding dependency through stored procedure

1 Answers1