none
SQL Server Cache Dependency did not read from web cashe with stored Procedure RRS feed

  • Question

  • hi every one

    i am using vs 2008 and sql server 2008 c#.

    i have gridview and i want it read from database when only happen any changes in my database so i use sql cashe dependency.

    i create "MyStoredProcedure" which select data from 2  tables.

    the problem is when there was any change in database or not web cache alwayes is null which means that  my grid read every time from data base not from cashing even if there is no changes in database!!!!!!!!!!..

    there are my steps:

    1-i enabled broker service to my databas

    ALTER DATABASE cars SET ENABLE_BROKER

    2-i create ASPNET account in sql2008 and i grant it with "SUBSCRIBE QUERY NOTIFICATIONS"

    GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "FATMA\ASPNET"

    after that 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["TryCashConnectionString"].ConnectionString);

    }

    protected void Application_End(object sender, EventArgs e)

    {

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

    }

    //My Code Behind//i create this method

    private DataTable GetData()
            {
                string connectionString = ConfigurationManager.ConnectionStrings["TryCashConnectionString"].ConnectionString;
                DataTable dtAlbums = new DataTable();
                using (SqlConnection connection =
                    new SqlConnection(connectionString))
                {
                    SqlCommand command = new SqlCommand();
                    command.Connection = connection;
                    connection.Open();
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "dbo.spGetPeteTest";
                                    System.Web.Caching.SqlCacheDependency new_dependency =
                        new System.Web.Caching.SqlCacheDependency(command);
                    SqlDataAdapter DA1 = new SqlDataAdapter();
                    DA1.SelectCommand = command;
                    DataSet DS1 = new DataSet();
                    DA1.Fill(DS1);
                    dtAlbums = DS1.Tables[0];
                    Cache.Insert("Albums", dtAlbums, new_dependency);
                    connection.Close();
                }
                return dtAlbums;
            }

    // in page load

     DataTable dtAlbums = (DataTable)Cache.Get("Albums");
            if (dtAlbums == null)
            {
                dtAlbums = GetData();
            }
                GridView1.DataSource = dtAlbums.DefaultView;
                GridView1.DataBind();

    what is wrong in my steps??

    please inform me

    best wishes

    fatma


    fatma mohamed
    Saturday, May 1, 2010 6:00 PM

All replies

  • Hi Fatma,

    It is not a reply but a query on your question as i am thinking of a way to update my cache when corresponding data changes in database.

    Can you please elaborate for me as how you are using SQL Dependency to do this at database level and at the code level in application.

    Regards

    Fatma

    Monday, May 3, 2010 3:18 PM
  • Hi TicAtch,

    about Data base Level: and there are My Steps:

    1-at first i have to know if broker service is enabled or not in  "My_Database",i did it by This query:

    SELECT name, is_broker_enabled FROM sys.databases

    2-if i fount that is_broker_enabled  in "My_Database" =0

    i have to enable broker service to my "My_Database"  by This Command:

    ALTER DATABASE My_Database SET ENABLE_BROKER

    3-after that i create ASPNET account in sql2008 and i grant it with "SUBSCRIBE QUERY NOTIFICATIONS"

    i did it with that command:

    GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "FATMA\ASPNET"

    and it is  My Stored Procedure:

    USE [TryCash]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER PROCEDURE [dbo].[spGetPeteTest]
    AS


    SELECT    
    Names.[First Name], Job.JobName
    FROM      
    Job INNER JOIN
    Names ON Job.id = Names.id
     

    This is my data base level no less no more

    Please if any thing is unclear inform me ,and if u found that there was any wrong step inform me.

    if any thing in my database level is clear i will send Code level in application

    thanx
    Best wishes


    fatma mohamed

     

    Monday, May 3, 2010 4:25 PM
  • Thanks a lot for responding.,

    That means there is a service broker implemented in SQL server by default which can be enabled to get alerts about data changes in database.Can you please let me know as at what level do it work i.e. can we fetch alerts if value changes in one specific column.

    Also please share the application code to fetch this broker alerts

    Can you share any good URL which describes this

    Cheers

    TicArch

    Monday, May 3, 2010 5:21 PM
  • thanx TicArch for your interest,\

    After finish database level i put thi code in global.asax:

    protected void Application_Start(object sender, EventArgs e)

    {

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

    }

    protected void Application_End(object sender, EventArgs e)

    {

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

    }

    and in web .config i add these tags under <system.web> tag:

    <caching>
          <sqlCacheDependency enabled="true"   pollTime="1000"   >
            <databases>
              <add name="TryCashConnectionString" connectionStringName="TryCashConnectionString"   pollTime="1000"   />
            </databases>
          </sqlCacheDependency>

    </caching> 

    and finally in my web page code behind ,i add this  cash method :

    private DataTable GetData()
            {
                string connectionString = ConfigurationManager.ConnectionStrings["TryCashConnectionString"].ConnectionString;
                DataTable dtAlbums = new DataTable();
                using (SqlConnection connection =
                    new SqlConnection(connectionString))
                {
                    SqlCommand command = new SqlCommand();
                    command.Connection = connection;
                    connection.Open();
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "dbo.spGetPeteTest";
                    System.Web.Caching.SqlCacheDependency new_dependency =
        new System.Web.Caching.SqlCacheDependency(command);
                    SqlDataAdapter DA1 = new SqlDataAdapter();
                    DA1.SelectCommand = command;
                    DataSet DS1 = new DataSet();
                    DA1.Fill(DS1);
                    dtAlbums = DS1.Tables[0];
                    Cache.Insert("Albums", dtAlbums, new_dependency);
                    connection.Close();
                }
                return dtAlbums;
            }

    //this is my method which on "Albums" cash and fill My grid:

    private void FillGrid()
            {
                DataTable dtAlbums = (DataTable)Cache.Get("Albums");
                if (dtAlbums == null)//Problem is dtAlbums  is alwayes Null!!!
                {
                    dtAlbums = GetData();
                }
                GridView1.DataSource = dtAlbums.DefaultView;
                GridView1.DataBind();

            }

    Finally in Page_Load Event :

     if (!Page.IsPostBack)
               {
                    FillGrid();
               }

    But i notice some thing : now i have 2 tables in data base is change any value of these table ,there are no changes in "AspNet_SqlCacheTablesForChangeNotification"

    i know that this table recored any changes in tables but if it is stored what it will be??

    that is all i did to use dependency with stored procedure
     can you please in form me wat is wrong in thi code????

    about usiful links

    http://stackoverflow.com/questions/2314890/sql-cache-dependency-not-working-with-stored-procedure/2750761#2750761

    thanx for your interest           

     


    fatma mohamed
    • Proposed as answer by TicArch Tuesday, May 4, 2010 11:59 AM
    • Unproposed as answer by fatma2 Tuesday, May 4, 2010 7:24 PM
    Monday, May 3, 2010 6:51 PM
  • So you are getting sqldependency funtionality in SQL server 2008.I doubted that because the notification services are absent in SQL server 2008.Then how does it capture data change event and broadcast it to our code.

    However, thansk a lot for responding. I appreciate your gesture of sharing code and thoughts.

    Cheers

    TicArch

    Tuesday, May 4, 2010 12:02 PM
  • I just posted a reply and it appears to have black holed. 

    Change notification is the recommended mechanism for sql 2008 since they decided to make notification services deprecated.

    Might even make your code somewhat simpler.

    http://msdn.microsoft.com/en-us/library/cc305322.aspx

    http://www.microsoft.com/uk/msdn/nuggets/nugget/299/SQL-Server-2008-Change-Tracking.aspx

    Tuesday, May 4, 2010 4:09 PM
  • Hi Andy1559 ,and thanx for your interest,

    i think there is no problem in notification cause i can use "SQL Server Cache Dependency" with table "Not Stored Procedure" and  cashing was working successfully which means that there  was a notification from sql sever to asp net .

    i think the problem is in code!

    but i have Query:

    if there was any change in any table the changes is recorded in "AspNet_SqlCacheTablesForChangeNotification"

    is it happen in a result of "MyStored" Procedure?or wat ?wat is the different?!

    Do you see My code?is there any thing wrong ?Please inform me?

    please do u have useful links with  video of how to use stored procedure with "SQL Server Cache Dependency"

    best wishes

    fatma


     


    fatma mohamed
    Tuesday, May 4, 2010 7:27 PM
  • Hi TicArch;

    do u try my example?if so do it works with u?

    thanx best wishes


    fatma mohamed
    Tuesday, May 4, 2010 7:31 PM
  • OK.

    2 possible things and an alternative.

    You didn't mention you changed global.asax.  You need SqlCacheDependency.Start(connectionString);  But as you say you have tables working OK then that seems unlikely to be your problem.

    There are a series of requirements - such as you must use a field list rather than * and maybe fail on one of these.

    See

    http://msdn.microsoft.com/en-US/library/ms181122.aspx

    In the past I have written systems which used caching but with a code based invalidation.  All updates and access to discrete sets of data go through the same business server layer.  So as a user requests a change the layer invalidates it's cache.  Of course you can't do that if you don't have control over updates. 

    Wednesday, May 5, 2010 10:00 AM
  • hi Andy1559,

    thanx for your reply

    i read the link which u sent but i will show u my stored procedure to specify ,can i use it in cashing or not:

    select top (10) tt1.Time,tt1.Attend_Name,tt1.EmployeeName,tt1.DepartmentName
     from
    ((SELECT AttendTime AS Time ,
    CASE WHEN AttendTime is not null
            THEN 'حضور' end as Attend_Name, Employees.EmployeeName,Departments.DepartmentName
    FROM  dbo.Attendance_Transaction left JOIN
                          Employees ON Attendance_Transaction.EmployeeID = Employees.EmployeeID left JOIN
                          Departments ON Employees.DepartmentID = Departments.DepartmentID )
                         
    UNION

    (SELECT DepartureTime AS Time ,
    CASE WHEN DepartureTime is not null
            THEN 'إنصراف' end as Attend_Name,Employees.EmployeeName,Departments.DepartmentName
    FROM dbo.Attendance_Transaction left JOIN
                          Employees ON Attendance_Transaction.EmployeeID = Employees.EmployeeID left JOIN
                          Departments ON Employees.DepartmentID = Departments.DepartmentID
    --
     )) tt1 where Attend_Name is not null ORDER by tt1.Time

    --End

    from Article i understand  that this stored can not work  with cashe cause of Top(10)!!!

    please help me how can i solve this problem???

    best wishes

    fatma


    fatma mohamed
    Saturday, May 8, 2010 1:13 PM
  • Or union, that's another problem.

    Simplest solution would be to forget about caching for this particular bit of data.

    Another alternative would be to use the approach I outlined above.

    Use a business layer that looks for cached data and presents that if it's there or runs that stored procedure, caches the data in an object per employee and serves it to whatever is using the data.  When you get a new time for someone save it to the database and remove the data for that person.

    Saturday, May 8, 2010 3:06 PM
  • hi Andy1559,

    thanx for your reply

    i was thinking in other solution but i dont the degree of performance caz my grid will be updated every one second:

    i want to create Temp_table and  create trigger will  be fired if my main table has any changes will read my stored and insert it in my temp table ;every changes remove the content of my table and insert the new;

    finally i will check on  this table and cash it;

    is it load on my page?

    fatma

    best wishes 


    fatma mohamed
    Saturday, May 8, 2010 5:34 PM
  • Personally, I wouldn't like to be running that sort of union query inside a trigger.  I think that's a bad idea.

    I'd rather have a collection which is held constantly in memory and updated as new times are entered.  So you have a hash table or something which has a collection of the 10 latest times per person.

    Use linq to objects to pull out your data.

    That reduces load on your database server - which is always a good thing. 

    Sunday, May 9, 2010 1:01 PM
  • hi Andy1559

    i want to explain some thing about my system:

    there is a web page ,it has agrid view,which show every Attendance and deprature transaction for every employee so i have to check on web cashing every 1 second by ajax timer control:

    i use sql cash dependency to check on my main table .wat i did is:

    if there were any changes in my main table web cache will be empty and when i get my new table cache  i make my gridview read from stored procedure ;

    i did this solution and it works ...

    please how you can evalute this solution??

    best wishes

    fatma

     

     


    fatma mohamed
    Monday, May 10, 2010 9:12 AM
  • How many employees do you have? 

    How dynamic is this data really?  It looks to me that this is just time someone arrives and time someone leaves - is this just full of arrived about 9 am left about 5pm data for almost everyone?

    How many people will have this web page open concurrently?

    Is this all your web page does?

    What is the purpose of seeing this data change in real time?    The user will only be able to see like 20 records at a time. 

    Are you really interested in seeing everyone or is the user looking for something else - like someone is only working 3 hour days?

    Monday, May 10, 2010 9:31 AM
  • -my employees not about 2000 not more

    -this web page will work forever and show evert sttend and deprature to every employee.

    -there is one user to this page ,it is just "show" not more.

    -this all my web page. 

    -i make this screen to make employees can see the registration of their attendance and deprature .

    -it is just a grid view has 10 rows (read from my stored procedure) just show employee name and time of transaction and Name of transaction(attendance and deprature) not more....

    please how you can evalute this solution??

    best wishes

    fatma 


    fatma mohamed
    Monday, May 10, 2010 10:05 AM
  • 1 user 10 records?

    I don't get it.

    Why not:

    Forget caching and just read the data once on page load.

     

    Monday, May 10, 2010 2:27 PM
  • why forget caching???!if i did that how can i check on date if it changed??

    if i read it once how can i record changes in data base??i check on cashing every second

    best wishes


    fatma mohamed
    Monday, May 10, 2010 10:05 PM
  • Why do you need to know within a second that someone's clocked on?
    Tuesday, May 11, 2010 10:27 AM
  • thanx Andy1559 for your reply and interst,

    i need to know withen a secont to record in my screen every Attendance and deprature transaction once happen ,i want every transaction happens appears in my screen without delay. 

    and it works but i ask about performance.

    if any thing is unclear please inform me

    best wishes

    fatma


    fatma mohamed
    Tuesday, May 11, 2010 10:52 AM