Can't get SqlCacheDependency working correctly

Discussion in 'ASP .Net' started by Joel Barsotti, Feb 7, 2006.

  1. I'm trying to get sql cache dependency working correctly.

    I've got sql server 2005 for my back end.

    I've don the "ALTER DATABASE dbName SET broker_enable" command.

    I made sure the user in my connection string has subscripe notifications
    permission set to grant.

    But now it seems that the cache is kicking my product out of the cache
    immediately.

    I'm using Cache.Insert(prodName, productObject, prodDepenancy) to put
    stuff into the cache, and when I go to refresh the page Cache[prodName] has
    reverted to null.

    Any ideas?
    Joel Barsotti, Feb 7, 2006
    #1
    1. Advertising

  2. Hi Joel,

    Currently we are looking for some people to help you on this issue, we will
    update you ASAP. Thanks for your understanding

    Best regards,
    Jeffrey Tan
    Microsoft Online Partner Support
    Get Secure! - www.microsoft.com/security
    This posting is provided "as is" with no warranties and confers no rights.
    Jeffrey Tan[MSFT], Feb 8, 2006
    #2
    1. Advertising

  3. Hi Joel,

    Welcome to the MSDN newsgroup.

    As for the SQL Cache dependency in ASP.NET 2.0, we need to make sure the
    configuration in the following places are correct:

    1. In sqlserver, we need to make sure we've enabled the sql cache through
    aspnet_regsql.exe tool

    2. In ASP.NET web application, ensure that web.config file contains the
    correct setting for that certain sql cache database/table

    If the above are all correctly configured, there may have something else
    cause the problem. And for general throubleshooting, you can try the
    following means also:

    a.Use Sql Profiler to monitor the SQL Server database server to see whether
    there're sql query operations peformed by asp.net sql cache thread...

    b.Add a RemoveCallBack handler for the cached item to see whether it is
    called immediately after you add the cache item.


    In addition, here is the walkthrough article in MSDN on using sql cache
    dependency in asp.net 2.0, you can also try testing through this to see
    whether it can work:

    #Walkthrough: Using ASP.NET Output Caching with SQL Server
    http://msdn2.microsoft.com/en-us/library(d=robot)/e3w8402y.aspx

    Hope this helps.

    Regards,

    Steven Cheng
    Microsoft Online Support

    Get Secure! www.microsoft.com/security
    (This posting is provided "AS IS", with no warranties, and confers no
    rights.)
    Steven Cheng[MSFT], Feb 9, 2006
    #3
  4. Joel Barsotti

    chris Guest

    Steven,

    It was my understanding that you step 1 and 2 were only needed if you
    were hooking to a Sql 2000 server and Sql 2005. In order to get mine
    working all I needed to do was have a user with the right permissions
    () and then make sure he was hooked to a custom schema and not the dbo
    schema. Also, I am sure you probably have this, otherwise it would be
    giving you an error, but make sure to call SqlDependency.Start.

    void Application_Start(object sender, EventArgs e)
    {


    SqlDependency.Start(WebConfigurationManager.ConnectionStrings["MainDB"].ConnectionString);
    }

    Here is my code that works for me. Note that I have my application
    abstracted to multiple layers.
    Data Layer:
    public DataReader GetLists(Guid ownerid, ref SqlCacheDependency
    scd)
    {
    //Note that _Command and _Connection are already declared as
    new SqlCommand and SqlConnection objects respectively by this point.
    _Command.Parameters.Add(new SqlParameter("@OwnerID",
    SqlDbType.UniqueIdentifier, 0));
    _Command.Parameters["@OwnerID"].Value = ownerid;

    spname = "[dbo].[usp_ListGetList]";
    _Command.Connection = _Connection;
    _Command.CommandType = CommandType.StoredProcedure;
    _Command.CommandText = spname;

    scd = new SqlCacheDependency(_Command);


    return new DataReader(_Command.ExecuteReader());

    }


    Here is my Business Layer
    public Dictionary<string, string> LoadDictionary()
    {
    Dictionary<string, string> dict = new Dictionary<string,
    string>();

    Cache cache = HttpRuntime.Cache;
    string cacheKey = "List::" + _OwnerID.ToString();
    if (cache[cacheKey] == null)
    {
    dict.Add("", "Choose One...");

    //Note that SqlBuilder is a custom wrapper class that
    contains all db calls like the one shown above
    using (SqlBuilder builder = new SqlBuilder())
    {
    SqlCacheDependency scd = null;
    using (DataReader reader =
    builder.GetLists(_OwnerID, ref scd))
    {
    while (reader.Read())
    {

    dict.Add(reader.GetGuid("ItemListID").ToString(),
    reader.GetString("Description"));
    }
    }
    //insert this into cache with a sql cache
    dependency.
    cache.Insert(cacheKey, dict, scd);
    }

    }
    else
    dict = cache[cacheKey] as Dictionary<string, string>;

    return dict;
    }

    This took me a little while to get working, but I was having
    permissions issues and not the issue you were having. But anyway I
    hope this help because once you get this working it is very slick.

    Chris
    chris, Feb 9, 2006
    #4
  5. Any ideas yet?

    ""Jeffrey Tan[MSFT]"" <> wrote in message
    news:...
    > Hi Joel,
    >
    > Currently we are looking for some people to help you on this issue, we
    > will
    > update you ASAP. Thanks for your understanding
    >
    > Best regards,
    > Jeffrey Tan
    > Microsoft Online Partner Support
    > Get Secure! - www.microsoft.com/security
    > This posting is provided "as is" with no warranties and confers no rights.
    >
    Joel Barsotti, Feb 14, 2006
    #5
  6. Hi Joel,

    Have you also viewed Chris and mine response in this thread?

    Regards,

    Steven Cheng
    Microsoft Online Support

    Get Secure! www.microsoft.com/security
    (This posting is provided "AS IS", with no warranties, and confers no
    rights.)
    Steven Cheng[MSFT], Feb 15, 2006
    #6
  7. Joel Barsotti

    poojamca

    Joined:
    Jun 5, 2009
    Messages:
    2
    Can't get SqlCacheDependency working correctly Reply to Thread

    Hi Can anyone please help me .i m getting data in dataset from store procedure and stored it in cache its work fine but dataset not refreshed when storeprocedure result changes.
    poojamca, Nov 24, 2009
    #7
  8. Joel Barsotti

    dhaval42

    Joined:
    Dec 21, 2009
    Messages:
    1
    SQL Cache dependency not working properly

    HI Experts i am using sql cache dependency in my application and it is working fine on my local machine but when i port it to the production server the application performs caching for few initial combinations but after that the application slows down tremendiously.
    On running cleartemp on the server and on changing the dependent tables the application starts running again but the issue crops up again after sometime. Please note that the database and application are on 2 seperate servers and cleartemp is hit on application server. Can anyone please help!!!
    dhaval42, Dec 21, 2009
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. stewart

    SqlCacheDependency

    stewart, Oct 19, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    1,325
    stewart
    Oct 19, 2005
  2. Adrian Parker
    Replies:
    5
    Views:
    657
    Steven Cheng[MSFT]
    Oct 24, 2005
  3. Yosi
    Replies:
    1
    Views:
    416
    =?Utf-8?B?RHVuY2FuIFNtaXRoIFtNU0ZUXQ==?=
    Apr 13, 2006
  4. Anton
    Replies:
    0
    Views:
    508
    Anton
    Mar 12, 2007
  5. mark4asp
    Replies:
    1
    Views:
    461
    mark4asp
    Aug 7, 2007
Loading...

Share This Page