Web Forms :: Create A SqlCacheDependency With CommandNotification In Code
Jul 19, 2010
I have an ASP.NET 3.5 web page that tries to retrieve a DataSet from a cache object. If not available, it will be retrieved from the database (SQL Server 2005) and I will insert the newly retrieved DataSet into the Cache object. The problem is that I want to insert the DataSet with a SqlCacheDependency so that as soon as the database table changes, my ASP.NET application will be notified. Something that is accomplished in @PageOutput using SqlDependency="CommandNotification". SqlCacheDependency constructor has two overloads, neither of which seems to be able to resolve my problem. I have no idea where I can set that magical CommandNotification!
Is is possible to build a form, accept parameters and from THAT construct a new .ascx page AND the code behind for that page? What I'm proposing is having some code snippets that just need parameters added and can then be used to create a web user control that can be added to existing pages. This .ascx file would be a real file after creation, not dynamic every time.
What are the key differences between these and when should they be used? My initial understanding was that SqlCacheDependency used polling but I've read it doesn't have too for ASP.NET 2.0. I want to know which is most appropriate for caching of linq queries under ASP.NET web server. This will be ASP.NET 3.5.
I find the SqlCacheDependency very useful when writing C# ASP.NET applications, and would love to use something similar in my PHP applications. Can anyone suggest something?
SqlCacheDependency caches the page page output forever, until the specified table(s) are modified in the database.
Here's the basic jist of what happens in ASP.NET:
SqlCacheDependency SqlDep = null; // Check the Cache for the SqlSource key. // If it isn't there, create it with a dependency [code]...
So does anyone know of any MySql table dependency technique? - much cleaner than time-based caching.
I am using sqlCacheDependency, and usually upon any changes in the database cache items get deleted automatically. In my case everything works allright, but I had to buy a new laptop and I moved the same exact database and the same exact webapplication to the new laptop. Unfortunately, on my new machine the cache items remain in cache upon data updates on the database.
I have enabled servicebroker on the database, and started it in the application_start event in my webapplication. I also ran the following:
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "Naji-pcNaji"
The differance I see on my laptop from my other machine is that the database is named (local). Naji-pc is the name of my machine.
My Asp.Net app caches some lookup DataTables to avoid hitting DB for seldomly changed data. When the data does change, however, I would like to use the sqlCacheDependency to get SQL Server to send a notification to my application, which should then reload the table into the cache.
In my application, the callback is not being executed, and the cache is not updated when data is changed in SQL Server.
I have a big problem in using SqlCacheDependency and Sqlserver notification service.I just want to add a dataset to cache with a SqlCacheDependency and get expire notification from sql server , Here is the simple code that doesn't work
(.net 4 , sqlserver 2008)
private DataSet GetData()
{[code]....
I have enabled my database service broker with : [alter database dbName set ENABLE_BROKER] I have created a master key for my databese with : [CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1']
There is no error in sql server log file but when I change my skin table data , my cached dataset still remains in cache , it seems that there is no notification from sql server about changing the result of my Sqlcmd (that the SqlCacheDependency is created on it)
I would like to display real time updates on a web page (based on a status field in a database table that is altered by an external process). Based on my research, there are several ways of doing this.
Long Polling (Comet) - This seems to be complex to implement Regular Polling - I can have an AJAX method trigger a database hit every 5seconds to get the current status. But I fear this will have performance issues.
Then I read about using SqlCacheDependency - basically the cache gets invalidated based on a field in the table. I am assuming I can use the event trigerred when the cache is invalidated to show the new update to the user?
I am trying to use SqlCacheDependency in my web site.I got in touch with my hosting compnay and they said that they support cache but they set the trust level to medium and what I try to do needs full trust level whihch they cannot provide for me.
can someone show me a way or a code samples that use SqlCacheDependency with medium level trust?
the broker is enabled in the hosting sql server
this code raise the error that the hosting providers said that they cannot support in medium level:
In my database I have altered stored procedure AspNet_SqlCacheRegisterTableStoredProcedure that is used for SqlCacheDependency to register tables that are in different schema then dbo (original procedure allows only tables for dbo schema).
That works, but I can't register same table name for different schemas, for example dbo.MyTable and MySchema.MyTable.In column tableName in table AspNet_SqlCacheTablesForChangeNotification there are only table names without schemas.
Is it possible to register same table names and different schemas?
In our project we have planned to put cache dependency on the queries, such that whenever the query result changes the cache gets invalidated. The problem is most of our queries use Distinct, top and group by. As I have learnt that SqlCacheDependency does not support queries with Distinct and Top, So, how should we proceed to implement the query dependency when the queries are complex.
I have been trying to get SqlCacheDependency working. I think I have everything set up correctly, but when I update the table, the item in the Cache isn't invalidated.
I enabled the Service Broker for the Sandbox database.I have placed the following code in the Global.asax file. I also restart IIS to make sure it is called.
protected void CacheDataSetButton_Click(object sender, EventArgs e) { using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SandboxConnectionString"].ConnectionString))[code].....
Then I bind the GridView with this code:
protected void BindGridViewButton_Click(object sender, EventArgs e) { if (Cache["Pets"] != null) { GridView1.DataSource = Cache["Pets"] as DataSet; GridView1.DataBind(); } }
Between attempts to DataBind the GridView, I change the table's values expecting it to invalidate the Cache["Pets"] item, but it seems to stay in the Cache indefinitely.
I have setup and SqlCacheDependency for my site and all is working well, but there is one requirement in my task that I dont' seem to be able to find the workaround for. Currently, my code relies on the following web.config settings to allow the SqlCacheDependency to know where to connect to. it is like this:
The problem is that we can't allow in our application the connectionstring to be on the web.config. I am asked to programmatically do all of that. I don't know how and I don't know if that is even possible, is it?
I have followed this tutorial for signal r [URL]....
Instead of using MVC or Web api I have used Web Forms and Called the method as
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.Services; using System.Web.UI; using System.Web.UI.WebControls; public partial class Default2 : System.Web.UI.Page {
I think it is a problem that SqlCacheDependency may invalidate a cache item while it is being set:
function getCacheItem() if cacheitem is empty cacheitem = something AND AT THIS POINT SqlCacheDependency INVALIDATES THE cacheitem end if return cacheitem end function
how can we prevent this? Is it somehow possible to specify a ReaderWriterLockSlim for the SqlCacheDependecy to use?
I am binding repeater control for mega menu, its working fine but i want to repeater do not load every time from database ,it load when any change or add in database ....
how can i create listview that include pager without using the aspx page at all? i want to create the listview using only the code behaind. the issue is, that i dont know the names of the columns so its need to be in some loop or something,
