none
SQL Server Cache Dependency did not work with stored proedure RRS feed

  • Question

  • hi every one,

    i am using vs 2008 and sql server 2008 c#:and that is first time i use Cache Dependency.

    i have gridview and i want it read from database when only happen any changes in my database.

    but there is no problem if i check on data in a table in database which means SQL Server Cache Dependency works with tables.

    the problem appears when i use stored procedure !!!!!!!!!

    when there was any change in database web cache  did not know this change (if i use stored procedure only) and my grid view alwayes read from cash which was created at first time..

    there are my steps:

    ofcourse at first i enable Broker service:

    ALTER DATABASE cars SET ENABLE_BROKER

    and

    GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "FATMA\ASPNET"

     i start in my code in vs 2008 c# which is :

    in global.asax:

    protected void Application_Start(object sender, EventArgs e)

    {

    SqlDependency.Start(ConfigurationManager.ConnectionStrings["CARSConnectionString"].ConnectionString);

    }

    protected void Application_End(object sender, EventArgs e)

    {

    SqlDependency.Stop(ConfigurationManager.ConnectionStrings["CARSConnectionString"].ConnectionString);

    }

    i create this method in  static class :

    public

    static List<Get_EmployeeThatDay_StoredResult> GetNames()

    {

    string cacheKey = "personsCacheKey";

    List<Get_EmployeeThatDay_StoredResult> people = (List<Get_EmployeeThatDay_StoredResult>)HttpContext.Current.Cache.Get(cacheKey);

    if (people == null)

    {

    DataClasses1DataContext ctx = new DataClasses1DataContext();

    var query = from p in ctx.Get_EmployeeThatDay_Stored() select p;

    people = query.ToList();

    SqlCacheDependency dependency = new SqlCacheDependency(ctx.GetCommand(query.AsQueryable()) as SqlCommand); HttpContext.Current.Cache.Add(cacheKey, people, dependency, DateTime.MaxValue,

     

    TimeSpan.Zero, CacheItemPriority.Normal, null);

    }

    return people;

    }

    //code in my web page

    protected void Page_Load(object sender, EventArgs e)

    { please any one help me

    if (!IsPostBack)

    {

    LoadGrid();

    }

    }

    private void LoadGrid()

    var EmpTable = MyExtensions.GetNames();

    GridView2.DataSource = EmpTable;

    GridView2.DataBind();

    }

    protected void Timer1_Tick(object sender, EventArgs e)

    {

    LoadGrid();

    }

    please inform me how can i use SQL Server Cache Dependency with stored procedure

    best wishes

    fatma


    fatma mohamed

    {

    Friday, April 30, 2010 11:52 PM