protected void Page_PreRender(object sender, EventArgs e)
{
try
{
string[] enabledTablesList =
SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(ConnString.SqlServer);
if (enabledTablesList.Length > 0)
{
enabledTables.DataSource = enabledTablesList;
enabledTables.DataBind();
}
else
{
enabledTablesMsg.Text = "No tables are enabled for change notifications.";
enabledTables.Visible = false;
disableTable.Visible = false;
}
}
catch (DatabaseNotEnabledForNotificationException ex)
{
enabledTables.Visible = false;
disableTable.Visible = false;
enabledTablesMsg.Text = "Cache notifications are not enabled in this database.";
tableName.Visible = false;
enableTable.Visible = false;
tableEnableMsg.Text = "Must enable database for notifications before enabling tables";
}
}
protected void Page_PreRender(object sender, EventArgs e)
{
try
{
// <Snippet2>
string[] enabledTablesList =
SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(
ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);
// </Snippet2>
if (enabledTablesList.Length > 0)
{
enabledTables.DataSource = enabledTablesList;
enabledTables.DataBind();
}
else
{
enabledTablesMsg.Text = "No tables are enabled for change notifications.";
enabledTables.Visible = false;
disableTable.Visible = false;
}
}
// <Snippet8>
catch (DatabaseNotEnabledForNotificationException ex)
{
enabledTables.Visible = false;
disableTable.Visible = false;
enabledTablesMsg.Text = "Cache notifications are not enabled in this database.";
tableName.Visible = false;
enableTable.Visible = false;
tableEnableMsg.Text = "Must enable database for notifications before enabling tables";
}
// </Snippet8>
}
/// <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 IQueryable <T> q, DataContext dc, string CacheId)
{
try
{
List <T> objCache = (List <T>)HttpRuntime.Cache.Get(CacheId);
if (objCache == null)
{
/////////No cache... implement new SqlCacheDependeny//////////
//1. Get connstring from DataContext
var connStr = Properties.Settings.Default.ioschoolsConnectionString;
//2. Get SqlCommand from DataContext and the LinqQuery
string sqlCmd = dc.GetCommand(q).CommandText;
//3. Create Conn to use in SqlCacheDependency
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
//4. Create Command to use in SqlCacheDependency
using (SqlCommand cmd = new 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 SqlParameter(dbp.ParameterName, dbp.Value));
}
//5.1 Enable DB for Notifications... Only needed once per DB...
SqlCacheDependencyAdmin.EnableNotifications(connStr);
//5.2 Get ElementType for the query
var attrib = (TableAttribute)Attribute.GetCustomAttribute(q.ElementType, typeof(TableAttribute));
string notificationTable = attrib.Name;
//5.3 Enable the elementtype for notification (if not done!)
if (!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connStr).Contains(notificationTable))
{
SqlCacheDependencyAdmin.EnableTableForNotifications(connStr, notificationTable);
}
//6. Create SqlCacheDependency
SqlCacheDependency sqldep = new 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...
HttpRuntime.Cache.Insert(CacheId, objCache, sqldep);
}
}
}
//Return the created (or cached) List
return(objCache);
}
catch (Exception ex)
{
Syslog.Write(ex);
throw;
}
}
public bool isDependencyTableEnabled(Type aType)
{
String _table = Loader.GetClassMap(aType).GetTableName();
return(Loader.HasClassMap(aType)
&&
SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(Config.DataSource).Contains(_table));
}
public static List <T> FromCache <T>(this IQueryable <T> q, ICache currentCache, string sqlConnStr)
{
var queryObject = (ObjectQuery)(q);
string sqlCmd = queryObject.ToTraceString();
var cacheKey = string.Format("{0}__{1}", sqlCmd.GetMd5Sum(), string.Join(",", queryObject.Parameters.OrderBy(a => a.Name).Select(a => a.Value).ToArray()));
List <T> objCache = (List <T>)(currentCache.Get(cacheKey));
if (objCache == null)
{
using (SqlConnection conn = new SqlConnection(sqlConnStr))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sqlCmd, conn);
foreach (var param in queryObject.Parameters)
{
cmd.Parameters.AddWithValue(param.Name, param.Value);
}
foreach (SqlParameter param in cmd.Parameters)
{
sqlCmd = sqlCmd.Replace("@" + param.ParameterName, "'{0}'".FormatWith(param.Value.ToString().Replace("'", "''")));
}
cmd = new SqlCommand(sqlCmd, conn);
using (cmd)
{
SqlCacheDependency sqlDep = null;
CreateDependency:
try
{
sqlDep = new SqlCacheDependency(cmd);
}
catch (DatabaseNotEnabledForNotificationException)
{
SqlCacheDependencyAdmin.EnableNotifications(sqlConnStr);
goto CreateDependency;
}
catch (TableNotEnabledForNotificationException ex)
{
((string[])(ex.Data["Tables"]))
.Where(table => !SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(sqlConnStr).Contains(table)).ToList()
.ForEach(table => SqlCacheDependencyAdmin.EnableTableForNotifications(sqlConnStr, table));
goto CreateDependency;
}
var reader = cmd.ExecuteReader();
string results = string.Empty;
while (reader.Read())
{
}
objCache = q.ToList();
currentCache.Insert(cacheKey, objCache, sqlDep);
}
}
}
return(objCache);
}
private void EnableDependency(params Type[] DependencyTablesType)
{
foreach (Type _tableType in DependencyTablesType)
{
String _table = Loader.GetClassMap(_tableType).GetTableName();
if (Loader.HasClassMap(_tableType))
{
if (!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(Config.DataSource).Contains(_table))
{
SqlCacheDependencyAdmin.EnableTableForNotifications(Config.DataSource, _table);
logger.Info("Enabling Notification For: " + _table);
}
else if (!MutableDependencyTablesType.Exists(m => m == _tableType))
{
logger.Info("Already Enabled Notification For: " + _table);
MutableDependencyTablesType.Add(_tableType);
}
}
}
}
/// <summary>
/// 快取 LINQ Query 的結果（僅適用於 LINQ to SQL 環境）
/// 使用的的限制跟使用 SqlCacheDependency 的限制一樣
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="q"></param>
/// <param name="dc">你的 LINQ to SQL DataContext</param>
/// <param name="CacheId">Cache ID，需為每一個不同的 IQueryable 物件設定一個唯一的 ID</param>
/// <returns></returns>
public static List <T> LinqCache <T>(this IQueryable <T> q, DataContext dc, string CacheId)
{
List <T> objCache = (List <T>)System.Web.HttpRuntime.Cache.Get(CacheId);
if (objCache == null)
{
#region 从数据库查找
///////// 尚未快取，實做 new SqlCacheDependeny //////////
// 1. 透過 DataContext 取得連線字串
string connStr = dc.Connection.ConnectionString;
// 2. 透過 DataContext 與 IQueryable 物件取得 SqlCommand 物件
SqlCommand sqlCmd = dc.GetCommand(q) as SqlCommand;
// 3. 建立要給 SqlCacheDependency 使用的 SqlConnection 物件
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
// 4. 建立要給 SqlCacheDependency 使用的 SqlCommand 物件
using (SqlCommand cmd = new SqlCommand(sqlCmd.CommandText, conn))
{
// 5.0 將 sqlCmd 中的所有參數傳遞給 cmd 物件
foreach (System.Data.Common.DbParameter dbp in sqlCmd.Parameters)
{
cmd.Parameters.Add(new SqlParameter(dbp.ParameterName, dbp.Value));
}
// 5.1 啟用資料庫的 Query notifications 功能
SqlCacheDependencyAdmin.EnableNotifications(connStr);
// 5.2 取得要進行異動通知的表格名稱(ElementType)
string NotificationTable = q.ElementType.Name;
// 5.3 將取得的 NotificationTable 啟用通知功能
if (!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connStr).Contains(NotificationTable))
{
SqlCacheDependencyAdmin.EnableTableForNotifications(connStr, NotificationTable);
}
// 6. 建立 SqlCacheDependency物件
SqlCacheDependency sqlDep = new SqlCacheDependency(cmd);
// 7. 刷新 LINQ to SQL 的值（取得資料庫中的最新資料）
dc.Refresh(RefreshMode.OverwriteCurrentValues, q);
// 8. 執行 SqlCacheDepency 查詢
cmd.ExecuteNonQuery();
// 9. 執行 LINQ to SQL 的查詢，並將結果轉成 List<T> 型別，避免延遲查詢(Delayed Query)立即將資料取回
objCache = q.ToList();
//10. 將結果插入到 System.Web.HttpRuntime.Cache 物件中，並且指定 SqlCacheDependency 物件
System.Web.HttpRuntime.Cache.Insert(CacheId, objCache, sqlDep);
}
}
#endregion
}
// 回傳查詢結果（或快取的結果）
return(objCache);
}
