locked
SQL Cache dependency is not working ! RRS feed

  • Question

  • Hi,

    The following code is for the SqlCacheDependency. I developed this one by read the following urls

    http://davidhayden.com/blog/dave/archive/2006/04/29/2929.aspx

    <cite>www.dotnetcurry.com/ShowArticle.aspx?ID=263</cite>

    Code:

    private void BindDivisions()
     {
      List<Divisions> divisionsList = new List<Divisions>();
       String conn = ConfigurationManager.ConnectionStrings["SampleConnectionString"].ToString();
      SqlConnection connObj = new SqlConnection(conn);
      connObj.Open();
      SqlCommand cmd = new SqlCommand();
      cmd.Connection = connObj;
    
      cmd.CommandType = System.Data.CommandType.StoredProcedure;
      cmd.CommandText = "GetTotalInfo";
      cmd.Notification = null;
      cmd.NotificationAutoEnlist = true;
      SqlCacheDependencyAdmin.EnableNotifications(conn);
      if (!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications((conn)).Contains("TestForDivisions"))
      {
       SqlCacheDependencyAdmin.EnableTableForNotifications(conn, "TestForDivisions");
      }     
       SqlCacheDependency dependency = new SqlCacheDependency(cmd);
      SqlDataReader reader = cmd.ExecuteReader();
      totalinfoList = new List<TotalInfo>();
      while (reader.Read())
      {
       TotalInfo totalInfo = new TotalInfo();
       totalInfo.DivisionID = Convert.ToInt32(reader["DivisionID"].ToString());
       totalInfo.Divisioname = reader["DivisionName"].ToString();
       totalInfo.SubDivisionID = Convert.ToInt32(reader["SubDivisionID"].ToString());
       totalInfo.SubDivisioname = reader["SubDivisionName"].ToString();
       totalInfo.DeptID = Convert.ToInt32(reader["DepartmentID"].ToString());
       totalInfo.DeptName = reader["DepartmentName"].ToString();
       totalinfoList.Add(totalInfo);
    
      }
      connObj.Close();
    
    // Upto here, it getting the records from database. It's created //the table,Trigger and stored procedures which are beloning to SqlServer Cache in the Sql Server 2005 also.
    
      HttpContext.Current.Cache.Insert("SqlDivisionInfo", totalinfoList, dependency);
    
    // But, here it is not inserted into Cache. I tried with Add() method also it is not added. if we replace dependency as null in Insert() method then it is ading to Cache. like
    
    HttpContext.Current.Cache.Insert("SqlDivisionInfo", totalinfoList, null);
    
      
      DropDownList1.DataSource = totalinfoList;
      DropDownList1.DataValueField = "DivisionID ";
      DropDownList1.DataTextField = "Divisioname ";
      DropDownList1.DataBind();
    
    
    
     }
    ALTER PROCEDURE [dbo].[GetTotalInfo]
    AS
    BEGIN	 
    select DivisionID,DivisionName,
    SubDivisionID,
    SubDivisionName,
    DepartmentID,
    DepartmentName from TestForDivisions 
    END
    

    The below are the permissions i given to login user.

    GRANT CREATE PROCEDURE TO TestLoginForCache
    GRANT CREATE QUEUE TO TestLoginForCache
    GRANT CREATE SERVICE TO TestLoginForCache
    
    GRANT SUBSCRIBE QUERY NOTIFICATIONS TO TestLoginForCache
    
    GRANT SELECT ON OBJECT::dbo.TestForDivisions TO TestLoginForCache
    
    GRANT RECEIVE ON QueryNotificationErrorsQueue TO TestLoginForCache
    
    exec sp_helprotect NULL, 'TestLoginForCache' 
    

    I don't know the list is not adding to Cache. If i modified the Insert() like this,

    HttpContext.Current.Cache.Insert("SqlDivisionInfo", totalinfoList, null);

    then it is adding to Cache. This time it is not supported SqlcacheDependency.

    If you look into URLs whcih i mentioned before, they mentioned Insert() method with dependency obeject.In Sql Server it is created table,Triggeres and Stored procedures what they mentioned.

    Versions used:

    SQL Server : 2005

    .Net : 3.5

    Visual Studio : 2008

    Can anyone help where i made mistake?

    Thanks in Advance..........

    Wednesday, December 15, 2010 4:59 AM