Cache dependencies
Jeg er ved at lave en ASP.NET application der benytter en SQL 2008 database. Jeg vil gerne benytte cache dependencies (notifikationer, så cachen automatisk ryddes når den række man har cachet, ændres i databasen).Jeg har lavet ASP.NET kode der håndterer dette, men cachen bliver ikke ryddet. Jeg tror ikke problemet ligger i koden, men jeg vedhæfter den alligevel nedenfor. Jeg håber der er nogen der kan hjælpe, jeg sidder virkelig fast her.
Her er det jeg har gjort/prøvet på at gøre:
*Enable_broker = true
*GRANT SUBSCRIBE QUERY NOTIFICATIONS TO (alle de brugere som jeg tror er relevante - men jeg er dog usikker på hvem)
I koden har jeg gjort følgende. I global.asax.cs:
protected void Application_Start(object sender, EventArgs e)
{
System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings["XXConnectionString"].ConnectionString);
}
protected void Application_End(object sender, EventArgs e)
{
System.Data.SqlClient.SqlDependency.Stop(ConfigurationManager.ConnectionStrings["XXConnectionString"].ConnectionString);
}
Der hvor cachen checkes:
System.Data.SqlClient.SqlDependency.Stop(ConfigurationManager.ConnectionStrings["XXConnectionString"].ConnectionString);
}
if (HttpRuntime.Cache["testCache"] == null)
{
SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["XXConnectionString"].ConnectionString);
String myQuery = "Select ElementID, ElementText from dbo.WebControlElements";
SqlCommand cmd = new SqlCommand(myQuery, con);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds, "WebControlElements");
SqlCacheDependency empDependency = new SqlCacheDependency(cmd);
HttpRuntime.Cache.Insert("testCache", ds, empDependency);
}
DataSet dataset = (DataSet)HttpRuntime.Cache["testCache"];
String aa = dataset.Tables["WebControlElements"].Rows[0]["ElementText"].ToString();
return aa;
Yderligere har jeg benyttet dette kode, jeg fandt på http://code.msdn.microsoft.com/linqtosqlcache
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections.Generic;
namespace XX
{
public static class StaticTextCacheManager
{
/// <summary>
/// Caches Linq query´s that is created for LinqToSql.
/// Limitations are the same as SqlCacheDependency
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="q">The linq query</param>
/// <param name="dc">Your LinqToSql DataContext</param>
/// <param name="CacheId">The unique Id for the cache</param>
/// <returns></returns>
public static List<T> LinqCache<T>(this System.Linq.IQueryable<T> q, System.Data.Linq.DataContext dc, string CacheId)
{
try
{
List<T> objCache = (List<T>)System.Web.HttpRuntime.Cache.Get(CacheId);
if (objCache == null)
{
/////////No cache... implement new SqlCacheDependeny//////////
//1. Get connstring from DataContext
string connStr = dc.Connection.ConnectionString;
//2. Get SqlCommand from DataContext and the LinqQuery
string sqlCmd = dc.GetCommand(q).CommandText;
//3. Create Conn to use in SqlCacheDependency
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStr))
{
conn.Open();
//4. Create Command to use in SqlCacheDependency
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sqlCmd, conn))
{
//5.0 Add all parameters provided by the Linq Query
foreach (System.Data.Common.DbParameter dbp in dc.GetCommand(q).Parameters)
{
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter(dbp.ParameterName, dbp.Value));
}
//5.1 Enable DB for Notifications... Only needed once per DB...
System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications(connStr);
//5.2 Get ElementType for the query
string NotificationTable = q.ElementType.Name;
//5.3 Enable the elementtype for notification (if not done!)
if (!System.Web.Caching.SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connStr).Contains(NotificationTable))
System.Web.Caching.SqlCacheDependencyAdmin.EnableTableForNotifications(connStr, NotificationTable);
//6. Create SqlCacheDependency
System.Web.Caching.SqlCacheDependency sqldep = new System.Web.Caching.SqlCacheDependency(cmd);
// - removed 090506 - 7. Refresh the LinqQuery from DB so that we will not use the current Linq cache
// - removed 090506 - dc.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues, q);
//8. Execute SqlCacheDepency query...
cmd.ExecuteNonQuery();
//9. Execute LINQ-query to have something to cache...
objCache = q.ToList();
//10. Cache the result but use the already created objectCache. Or else the Linq-query will be executed once more...
System.Web.HttpRuntime.Cache.Insert(CacheId, objCache, sqldep);
}
}
}
//Return the created (or cached) List
return objCache;
}
catch (Exception ex)
{
throw ex;
}
}
}
}
