private void loadProducts()
{
IProductBussinessService productBussinessService = null;
DataTable productTable = null;
try
{
if (Cache["Products"] == null)
{
productBussinessService = BizDelegateFactory.Current.ProductBussinessService;
productTable = productBussinessService.RetreiveAllProducts();
SqlCacheDependency productCache = new SqlCacheDependency("SpencersDB", "Products");
Cache.Insert("Products", productTable, productCache);
}
}
catch (Exception ex)
{
CommonLabel.Text = ex.Message;
CommonLabel.ForeColor = Color.Red;
}
finally
{
productBussinessService = null;
}
}
protected AggregateCacheDependency GetAggregateCacheDependency <T>()
{
AggregateCacheDependency acd = new AggregateCacheDependency();
IEnumerable <string> tables = GetTables <T>();
foreach (string table in tables)
{
SqlCacheDependency cd = null;
try
{
cd = new SqlCacheDependency(DatabaseEntryName, table);
}
catch (DatabaseNotEnabledForNotificationException ex)
{
SqlCacheDependencyAdmin.EnableNotifications(ConfigurationManager.ConnectionStrings[ConnectionStringName].ConnectionString);
SqlCacheDependencyAdmin.EnableTableForNotifications(ConfigurationManager.ConnectionStrings[ConnectionStringName].ConnectionString, table);
}
catch (TableNotEnabledForNotificationException ex)
{
SqlCacheDependencyAdmin.EnableTableForNotifications(ConfigurationManager.ConnectionStrings[ConnectionStringName].ConnectionString, table);
}
if (cd == null)
{
cd = new SqlCacheDependency(DatabaseEntryName, table);
}
acd.Add(cd);
}
return(acd);
}
private void ReloadAndCacheCustomers()
{
// Read connection string from web.config file
string CS = ConfigurationManager.ConnectionStrings["StoreConnectionString"].ConnectionString;
lock (thisLock)
{
using (SqlConnection con = new SqlConnection(CS))
{
SqlDataAdapter da = new SqlDataAdapter("spGetCustomers", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
da.Fill(ds);
CacheItemRemovedCallback onCacheItemRemoved = new CacheItemRemovedCallback(CacheItemRemovedCallbackMethod);
// Build SqlCacheDependency object using the database and table names
SqlCacheDependency sqlDependency = new SqlCacheDependency("Store", "Customers");
// Pass SqlCacheDependency object, when caching data
Cache.Insert("CustomersData", ds, sqlDependency, DateTime.Now.AddHours(24), Cache.NoSlidingExpiration,
CacheItemPriority.Default, onCacheItemRemoved);
GridView1.DataSource = ds;
GridView1.DataBind();
}
}
}
/// <summary>
/// Enlists a cache dependency to recieve change notifciations with an underlying resource
/// </summary>
/// <returns>
/// The cache dependency linked to the notification subscription
/// </returns>
public CacheDependency Enlist()
{
SqlCacheDependency dependency;
//setup and execute the command that will register the cache dependency for
//change notifications
using (var connection = new SqlConnection(connectionString))
{
using (var exeCommand = new System.Data.SqlClient.SqlCommand(command, connection))
{
//is the command a sproc
if (isStoredProcedure)
{
exeCommand.CommandType = CommandType.StoredProcedure;
}
if (commandTimeout.HasValue)
{
exeCommand.CommandTimeout = this.commandTimeout.Value;
}
//hook the deondency up to the command
dependency = new SqlCacheDependency(exeCommand);
connection.Open();
//execute the query, this will enlist the dependency. Notice that we execute a non query since
//we dont need any results
exeCommand.ExecuteNonQuery();
}
}
return(dependency);
}
public List <Event> LoadJson()
{
List <Event> events;
using (StreamReader r = new StreamReader("../../EventList.json"))
{
string json = r.ReadToEnd();
var serializer = new JavaScriptSerializer();
// events = JsonConvert.DeserializeObject<List<Event>>(json);
}
if (HttpRuntime.Cache["Events"] == null)
{
string myConnectionString = ConfigurationManager.ConnectionStrings["SouthWorkDB"].ConnectionString;
//Create your Sql Connection here
using (SqlConnection con = new SqlConnection(myConnectionString))
{
//Open the sql connection
con.Open();
events = getEventsFromDB(con);
}
// Create the Sql Cache dependency object to use it on ur cache.
// First parameter is the database name, second the table to create the dependency. SouthWorkDB
SqlCacheDependency SQL_DEPENDENCY = new SqlCacheDependency("SouthWorkDBCache", "Events");
HttpRuntime.Cache.Insert("Events", events, SQL_DEPENDENCY);
}
else
{
events = HttpRuntime.Cache["Events"] as List <Event>;
}
return(events);
}
public static DataSet MyData()
{
DataSet ds;
Cache cache = HttpContext.Current.Cache;
lock (lockObject)
{
ds = (DataSet)cache[DataKey];
if (ds == null)
{
string cs = ConfigurationManager.ConnectionStrings["data"]
.ConnectionString;
using (SqlConnection conn = new SqlConnection(cs))
{
string sql = "dbo.GetInfo";
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
conn.Open();
SqlCacheDependency dep = new SqlCacheDependency(cmd);
adapter.Fill(ds);
cache.Insert(DataKey, ds, dep);
}
}
}
}
}
return(ds);
}
internal override void ProcessOutputCacheDirective(string directiveName, IDictionary directive)
{
bool val = false;
string andRemoveNonEmptyAttribute = System.Web.UI.Util.GetAndRemoveNonEmptyAttribute(directive, "varybycontentencoding");
if (andRemoveNonEmptyAttribute != null)
{
base.OutputCacheParameters.VaryByContentEncoding = andRemoveNonEmptyAttribute;
}
string str2 = System.Web.UI.Util.GetAndRemoveNonEmptyAttribute(directive, "varybyheader");
if (str2 != null)
{
base.OutputCacheParameters.VaryByHeader = str2;
}
object obj2 = System.Web.UI.Util.GetAndRemoveEnumAttribute(directive, typeof(OutputCacheLocation), "location");
if (obj2 != null)
{
this._outputCacheLocation = (OutputCacheLocation)obj2;
base.OutputCacheParameters.Location = this._outputCacheLocation;
}
string depString = System.Web.UI.Util.GetAndRemoveNonEmptyAttribute(directive, "sqldependency");
if (depString != null)
{
base.OutputCacheParameters.SqlDependency = depString;
SqlCacheDependency.ValidateOutputCacheDependencyString(depString, true);
}
if (System.Web.UI.Util.GetAndRemoveBooleanAttribute(directive, "nostore", ref val))
{
base.OutputCacheParameters.NoStore = val;
}
base.ProcessOutputCacheDirective(directiveName, directive);
}
protected void Page_Load(object sender, EventArgs e)
{
//Ao carregar a página pela 1 vez
if (!Page.IsPostBack)
{
//Habilitar o serviço aspnet_regsql -ed -E -d CURSO
//Habilitar a tabela aspnet_regsql -et -E -d CURSO -t TB_LINGUAGEM
//Habilitei o serviço de notificação na base(CURSO)
SqlCacheDependencyAdmin.EnableNotifications(
WebConfigurationManager
.ConnectionStrings["CURSO"].ConnectionString);
//Habilitei o monitoramento na tabela(TB_LINGUAGEM) localizada na base(CURSO)
SqlCacheDependencyAdmin.EnableTableForNotifications(
WebConfigurationManager
.ConnectionStrings["CURSO"].ConnectionString,
"TB_LINGUAGEM");
//Classe responsável por criar uma dependência com a tabela(TB_LINGUAGEM)
//localizada na base(CURSO)
SqlCacheDependency Dependencia = new SqlCacheDependency("CURSO", "TB_LINGUAGEM");
//Inseri o conteúdo da tabela(TB_LINGUAGEM) no cache
Cache.Insert("TABELA", Contexto.TB_LINGUAGEM);
}
}
// private static EntityConnectionStringBuilder entityConnectionString = new
// EntityConnectionStringBuilder(ConfigurationManager.ConnectionStrings["TestdbEntities"].ConnectionString);
public static IEnumerable <Student> GetStudentData()
{
IEnumerable <Student> studentData = HttpContext.Current.Cache.Get("student") as
IEnumerable <Student>;
if (studentData == null)
{
using (var context = new SchoolContext())
{
//try
// {
SqlCacheDependency SqlDep = new SqlCacheDependency("TEST_SAMO", "Student");
// }
//catch (DatabaseNotEnabledForNotificationException exDBDis)
// {
// SqlCacheDependencyAdmin.EnableNotifications(System.Configuration.ConfigurationManager.ConnectionStrings["SchoolContext"].ConnectionString);
// }
//catch (TableNotEnabledForNotificationException exTabDis)
// {
// SqlCacheDependencyAdmin.EnableTableForNotifications(System.Configuration.ConfigurationManager.ConnectionStrings["SchoolContext"].ConnectionString, "Student");
IQueryable <Student> studentDataCache = context.Students.Include("Enrollments.Course");
studentData = studentDataCache.ToList();
HttpContext.Current.Cache.Insert("student", studentData, SqlDep, DateTime.Now.AddMinutes(3), Cache.NoSlidingExpiration);
// }
}
}
return(studentData);
}
public DataTable GetRealDataWithDependency(string queryString, ref SqlCacheDependency dependency)
{
var connection = GetActualConnection();
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
var adapter = CreateDbAdapter();
var cmd = CreateDbCommand(queryString, connection);
cmd.Transaction = GetActualTransaction();
dependency = new SqlCacheDependency((SqlCommand)cmd);
adapter.SelectCommand = cmd;
return(GetFilledDataTable(adapter));
}
finally
{
if (NeedToDisposeActualConnection)
{
connection.Dispose();
}
}
}
public static IEnumerable <Course> GetCourseData()
{
IEnumerable <Course> courseData = HttpContext.Current.Cache.Get("course") as
IEnumerable <Course>;
if (courseData == null)
{
using (var context = new SchoolContext())
{
//try
// {
SqlCacheDependency SqlDep = new SqlCacheDependency("TEST_SAMO", "Course");
// }
//catch (TableNotEnabledForNotificationException exTabDis)
// {
// SqlCacheDependencyAdmin.EnableTableForNotifications(System.Configuration.ConfigurationManager.ConnectionStrings["SchoolContext"].ConnectionString, "Course");
// }
//finally
// {
IQueryable <Course> courseDataCache = context.Courses;
courseData = courseDataCache.ToList();
HttpContext.Current.Cache.Insert("course", courseData, SqlDep, DateTime.Now.AddMinutes(1), Cache.NoSlidingExpiration);
// }
}
}
return(courseData);
}
private void LoadCategories()
{
ICategoryBussinessService categoryBussinessService = null;
DataTable categoryTable = null;
try
{
if (Cache["Categories"] == null)
{
categoryBussinessService = BizDelegateFactory.Current.CategoryBussinessService;
categoryTable = categoryBussinessService.RetreiveAllCategory();
SqlCacheDependency categoryCache = new SqlCacheDependency("SpencersDB", "Products");
Cache.Insert("Categories", categoryTable, categoryCache);
}
CategoryIdDataList.DataSource = Cache["Categories"];
CategoryIdDataList.DataBind();
}
catch (Exception ex)
{
CommonLabel.Text = ex.Message;
CommonLabel.ForeColor = Color.Red;
}
finally
{
categoryBussinessService = null;
}
}
private void UpdateCache()
{
var connectionString =
ConfigurationManager.ConnectionStrings["AdventureWorks"]
.ConnectionString;
SqlDependency.Start(connectionString);
using (var conn = new SqlConnection(connectionString))
{
using (SqlCommand command =
new SqlCommand("SELECT PersonType FROM Person.Person WHERE PersonType = 'EM'",
conn))
{
var empCount = GetCurrentEmployeeCount();
string key = "employee_count";
string value = "Count: " + empCount + ", as of " + DateTime.Now.ToLongTimeString();
SqlCacheDependency dep = new SqlCacheDependency(command);
DateTime exp = DateTime.Now.AddMinutes(5);
conn.Open();
command.ExecuteScalar();
HttpContext.Cache
.Add(key, value, dep, exp,
Cache.NoSlidingExpiration,
CacheItemPriority.Default, CallBack);
}
}
}
public List <ShopingInfoViewModel> GetInfoViewModels()
{
var cache = CacheHelper.GetCache("ShopingInfo"); //先读取
if (cache == null) //如果没有该缓存
{
SqlCacheDependency dep = new SqlCacheDependency("TestCache", "ShopingInfoes");
var shopinfo = (from sp in GetAllShopingInfo()
join st in GetAllShopingType() on sp.ShopingTypeId equals st.ID into temp
from a in temp.DefaultIfEmpty()
select new ShopingInfoViewModel
{
ShopingName = sp.ShopingName,
ShopingCount = sp.ShopingCount,
ShopingPric = sp.ShopingPric,
Stock = sp.Stock,
Volumeofvolume = sp.Volumeofvolume,
ShopingTypeName = a.ShopingName
}).ToList();
CacheHelper.AddobjectToCache("ShopingInfo", shopinfo, dep);//添加缓存
return(shopinfo);
}
var result = (List <ShopingInfoViewModel>)cache;//有就直接返回该缓存
return(result);
}
public void SetSqlDateTableCache(string key, object value, string table, string connectionName = null)
{
string database = connectionName ?? "ImageOfTaiWan";
sqlCacheDependency = new SqlCacheDependency(database, table);
cache.Insert(key, value, sqlCacheDependency);
}
private void LoadCategories()
{
ICategoryBussinessService categoryBussinessService = null;
DataTable categoryTable = null;
try
{
if (Cache["Categories"] == null)
{
categoryBussinessService = BizDelegateFactory.Current.CategoryBussinessService;
categoryTable = categoryBussinessService.RetreiveAllCategory();
SqlCacheDependency categoryCache = new SqlCacheDependency("SpencersDB", "Products");
Cache.Insert("Categories", categoryTable, categoryCache);
}
CategoryIdDataList.DataSource = Cache["Categories"];
CategoryIdDataList.DataBind();
}
catch (Exception ex)
{
CommonLabel.Text = ex.Message;
CommonLabel.ForeColor = Color.Red;
}
finally
{
categoryBussinessService = null;
}
}
protected void btnGetData_Click(object sender, EventArgs e)
{
if (Cache["ProductData"] == null)
{
string CS = @"Data Source=DESKTOP-661ODQ1;Initial Catalog=ASPDOTNETDB;Integrated Security=True";
string CS1 = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(CS1);
SqlDataAdapter da = new SqlDataAdapter("spGetProducts", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet DS = new DataSet();
da.Fill(DS);
//Cache.Insert("ProductData", DS, null, System.Web.Caching.Cache.NoAbsoluteExpiration, TimeSpan.FromSeconds(120), CacheItemPriority.Default, null);
SqlCacheDependency sqlCacheDependency = new SqlCacheDependency("ASPDOTNETDB", "tblProducts");
Cache.Insert("ProductData", DS, sqlCacheDependency, System.Web.Caching.Cache.NoAbsoluteExpiration, TimeSpan.FromSeconds(120), CacheItemPriority.Default, null);
GridView1.DataSource = DS;
GridView1.DataBind();
lblMessage.Text = DS.Tables[0].Rows.Count.ToString() + " row(s) retrieve from the Database";
}
else
{
DataSet DS = new DataSet();
DS = (DataSet)Cache["ProductData"];
GridView1.DataSource = DS;
GridView1.DataBind();
lblMessage.Text = DS.Tables[0].Rows.Count.ToString() + " row(s) retrieve from the Cache";
}
}
protected void Page_Load(object sender, EventArgs e)
{
Label1.Text = "Cache Refresh: " +
DateTime.Now.ToLongTimeString();
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["WageringConn"].ConnectionString))
{
using (SqlCommand command =
new SqlCommand(GetSQL(), connection))
{
SqlCacheDependency dependency =
new SqlCacheDependency(command);
// Refresh the cache after the number of minutes
// listed below if a change does not occur.
// This value could be stored in a configuration file.
int numberOfMinutes = 3;
DateTime expires =
DateTime.Now.AddMinutes(numberOfMinutes);
Response.Cache.SetExpires(expires);
Response.Cache.SetCacheability(HttpCacheability.Public);
Response.Cache.SetValidUntilExpires(true);
Response.AddCacheDependency(dependency);
connection.Open();
GridView1.DataSource = command.ExecuteReader();
GridView1.DataBind();
}
}
}
public void CreateOutputCacheDependency()
{
CacheDependency sqc;
AssertExtensions.Throws <HttpException> (() =>
{
sqc = SqlCacheDependency.CreateOutputCacheDependency(null);
}, "#A1");
AssertExtensions.Throws <ArgumentException> (() =>
{
sqc = SqlCacheDependency.CreateOutputCacheDependency(String.Empty);
}, "#A2");
AssertExtensions.Throws <ArgumentException> (() =>
{
sqc = SqlCacheDependency.CreateOutputCacheDependency("Database");
}, "#A2");
// Not testable without a real database
//sqc = SqlCacheDependency.CreateOutputCacheDependency ("Database:table");
//Assert.IsTrue (sqc is SqlCacheDependency, "#B1");
//
//sqc = SqlCacheDependency.CreateOutputCacheDependency ("Database:table; AnotherDatabase:table");
//Assert.IsTrue (sql is AggregateCacheDependency, "#B2");
}
public AspNetNotication(
CacheSpec aCacheSpec,
SqlCacheDependency aDependency)
{
CacheSpec = aCacheSpec;
dependency = aDependency;
}
public static List<KeyValuePair<string,string>> GetData() {
var cache = System.Web.HttpContext.Current.Cache;
SqlCacheDependency SqlDep = null;
var modules = Cache["Modules"] as List<KeyValuePair<string,string>>;
if (modules == null) {
// Because of possible exceptions thrown when this
// code runs, use Try...Catch...Finally syntax.
try {
// Instantiate SqlDep using the SqlCacheDependency constructor.
SqlDep = new SqlCacheDependency("Test", "SD_TABLES");
}
// Handle the DatabaseNotEnabledForNotificationException with
// a call to the SqlCacheDependencyAdmin.EnableNotifications method.
catch (DatabaseNotEnabledForNotificationException exDBDis) {
SqlCacheDependencyAdmin.EnableNotifications("Test");
}
// Handle the TableNotEnabledForNotificationException with
// a call to the SqlCacheDependencyAdmin.EnableTableForNotifications method.
catch (TableNotEnabledForNotificationException exTabDis) {
SqlCacheDependencyAdmin.EnableTableForNotifications("Test", "SD_TABLES");
}
finally {
// Assign a value to modules here before calling the next line
Cache.Insert("Modules", modules, SqlDep);
}
}
return modules;
}
/// <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;
}
}
/// <summary>
/// 数据库缓存依赖
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="value">存储值</param>
/// <param name="cacheKey">缓存键</param>
/// <param name="database">数据库名称</param>
/// <param name="table">表名称</param>
public void WriteCache <T>(T value, string cacheKey, string database, string table) where T : class
{
//制定缓存策略
SqlCacheDependency scd = new SqlCacheDependency(database, table);
//插入缓存
cache.Insert(cacheKey, value, scd);
}
/// <summary>
/// Add a new object into the cache using the SQL cache dependency. Only for HttpRuntime cache.
/// </summary>
/// <param name="key">The key of the object to add</param>
/// <param name="value">The value of the object to add</param>
/// <param name="databaseEntryName">The database entry name</param>
/// /// <param name="tableName">The table name</param>
public void Insert(string key, object value, string databaseEntryName, string tableName)
{
// Add the SqlCacheDependency objects for Products
var objectTableDependency = new SqlCacheDependency(databaseEntryName, tableName);
// Add the item to the data cache using productsTableDependency
HttpRuntime.Cache.Insert(key, value, objectTableDependency, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration);
}
public DealDAO()
{
dependency = new SqlCacheDependency("anythinglk", "Deals");
allDealsQuery = from deal in db.Deals orderby deal.PlacedOn descending select deal;
cacheInfo = new CacheInfo();
HttpContext.Current.Cache.Insert("CacheValid", cacheInfo, dependency);
}
public void Constructor_1()
{
SqlCacheDependency sqc;
Assert.Throws <ArgumentNullException> (() => {
sqc = new SqlCacheDependency(null);
}, "#A1");
}
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 SqlCacheDependency CreateSqlDepenedency(string conString, DbCommand dbCommand)
{
SqlCommand sqlCmd = dbCommand as SqlCommand;
SqlCacheDependency depenedency = null;
if (sqlCmd != null)
{
depenedency = new SqlCacheDependency(conString, sqlCmd.CommandText.StripTabsAndNewlines());
if (sqlCmd.Parameters != null && sqlCmd.Parameters.Count > 0)
{
foreach (SqlParameter parameter in sqlCmd.Parameters)
{
SqlCmdParams dependencyParameter = new SqlCmdParams();
//dependencyParameter.Direction = (SqlParamDirection)((int)parameter.Direction - 1);
switch (parameter.Direction)
{
case ParameterDirection.Input:
dependencyParameter.Direction = SqlParamDirection.Input;
break;
case ParameterDirection.InputOutput:
dependencyParameter.Direction = SqlParamDirection.InputOutput;
break;
case ParameterDirection.Output:
dependencyParameter.Direction = SqlParamDirection.Output;
break;
case ParameterDirection.ReturnValue:
dependencyParameter.Direction = SqlParamDirection.ReturnValue;
break;
}
dependencyParameter.IsNullable = parameter.IsNullable;
dependencyParameter.LocaleID = parameter.LocaleId;
dependencyParameter.Offset = parameter.Offset;
dependencyParameter.Precision = parameter.Precision;
dependencyParameter.Scale = parameter.Scale;
dependencyParameter.Size = parameter.Size;
dependencyParameter.SourceColumn = parameter.SourceColumn;
dependencyParameter.SourceColumnNullMapping = parameter.SourceColumnNullMapping;
dependencyParameter.SourceVersion = (SqlDataRowVersion)parameter.SourceVersion;
dependencyParameter.SqlValue = parameter.SqlValue;
dependencyParameter.UdtTypeName = parameter.UdtTypeName;
dependencyParameter.SourceColumn = parameter.SourceColumn;
dependencyParameter.Type = (CmdParamsType)parameter.SqlDbType;
dependencyParameter.TypeName = parameter.TypeName;
dependencyParameter.SourceColumn = parameter.SourceColumn;
dependencyParameter.Value = parameter.Value;
depenedency.CommandParams.Add(parameter.ParameterName, dependencyParameter);
}
}
}
return(depenedency);
}
public static void SaveToCacheDependency(string cacheName, object data)
{
string database = System.Configuration.ConfigurationSettings.AppSettings["CoreDb"];
SqlCacheDependency sqlDep = new SqlCacheDependency(database, "HtmlCached");
if (data != null)
{
HttpContext.Current.Cache.Insert(cacheName, data, sqlDep);
}
}
/// <summary>
/// SQL缓存依赖 用于SQL2000数据库 sql2005不需要
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public static SqlCacheDependency GetDependency(string ConnectionString_SQL2000, string table)
{
SqlCacheDependencyAdmin.EnableNotifications(ConnectionString_SQL2000); //启动数据库的数据缓存依赖功能
SqlCacheDependencyAdmin.EnableTableForNotifications(ConnectionString_SQL2000, table); //启用数据表缓存
SqlCacheDependency dependency = new SqlCacheDependency(
new System.Data.SqlClient.SqlConnectionStringBuilder(
ConnectionString_SQL2000
).InitialCatalog, table);
return(dependency);
}
//启用SQL缓存依赖
protected void EnableSqlDenpendency()
{
DataSet ds = (DataSet)Cache["ProductsInfo"];
if (ds == null)
{
ds = GetProductDataSet();
//指定SQL缓存依赖
SqlCacheDependency sqldependency = new SqlCacheDependency("NorthwindDataBase", "Products");
Cache.Insert("ProductsInfo", ds, sqldependency);
}
}
private void InsertCache(string cacheKey, object ob,
SqlCommand cmd, CacheItemRemovedCallback removedCallback)
{
SqlCacheDependency scd = new SqlCacheDependency(cmd);
using (cmd.Connection)
{
cmd.ExecuteNonQuery();
}
HttpRuntime.Cache.Insert(cacheKey, ob, scd, absoluteExpiration, slidingExpiration,
cachePriority, removedCallback);
}
public static void SetToCache(object dataCache, string cacheName, string[] tableNameInDatabase)
{
System.Web.Caching.SqlCacheDependency[] sqlDep = new SqlCacheDependency[tableNameInDatabase.Length];
for (int i = 0; i < tableNameInDatabase.Length; i++)
{
sqlDep[i] = new System.Web.Caching.SqlCacheDependency(DATABASE_NAME, tableNameInDatabase[i]);
}
System.Web.Caching.AggregateCacheDependency agg = new System.Web.Caching.AggregateCacheDependency();
agg.Add(sqlDep);
HttpContext.Current.Cache.Insert(cacheName, dataCache, agg, DateTime.Now.AddDays(1), Cache.NoSlidingExpiration);
}
private DataTable GetTable()
{
SqlConnection con = new SqlConnection(connectionString);
string sql = "SELECT ContactName FROM dbo.Customers";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
// Create a dependency for the Employees table.
dependency = new SqlCacheDependency(da.SelectCommand);
DataSet ds = new DataSet();
da.Fill(ds, "Customers");
return ds.Tables[0];
}
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
lblInfo.Text += "Creating dependent item...<br />";
Cache.Remove("CachedItem");
// Create a dependency for the Employees table.
SqlCacheDependency dependency = new SqlCacheDependency(
"Northwind", "Employees");
DataTable dt = GetEmployeeTable();
lblInfo.Text += "Adding dependent item<br>";
Cache.Insert("CachedItem", dt, dependency);
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
System.Data.SqlClient.SqlDependency.Start(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["connection"].ConnectionString);
SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["connection"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select CategoryID, ProductID, ProductName, UnitPrice, UnitsInStock from dbo.Products";
cmd.Connection = con;
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
SqlCacheDependency dependency = new SqlCacheDependency(cmd);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, "Products");
Cache.Insert("key1", dataSet, dependency);
}
Label1.Text = Cache["key1"].ToString();
}
public void comentarios(string razon)
{
lblCache.Text = "Cache Refresh: (Cada 3 minutos) " + DateTime.Now.ToLongTimeString();
// Create a dependency connection to the database.
SqlDependency.Start(GetConnectionString());
using (SqlConnection connection = new SqlConnection(GetConnectionString()))
{
using (SqlCommand command = new SqlCommand(ObtenerComentarios(razon), connection))
{
SqlCacheDependency dependency = new SqlCacheDependency(command);
// Refresh the cache after the number of minutes
// listed below if a change does not occur.
// This value could be stored in a configuration file.
int numberOfMinutes = 3;
DateTime expires = DateTime.Now.AddMinutes(numberOfMinutes);
Response.Cache.SetExpires(expires);
Response.Cache.SetCacheability(HttpCacheability.Public);
Response.Cache.SetValidUntilExpires(true);
Response.AddCacheDependency(dependency);
connection.Open();
GridComentarios.DataSource = command.ExecuteReader();
GridComentarios.DataBind();
if (!Context.User.Identity.IsAuthenticated)
{
imgComentario1.ImageUrl = string.Format("img/avatar/Ninguno.jpg");
}
else
{
imgComentario1.ImageUrl = string.Format("img/avatar/{0}.jpg", Context.User.Identity.Name);
}
}
}
}
static DummySqlCacheObjectCacheNoDI()
{
//create the new cache object
Cache = new SqlCacheDependency<IEnumerable<DummyObject>>("DummySqlCacheObjectCache",
BuildCacheDataSource,
SqlDataProviderTest.ConnectionStringToUse(),
DatabaseSchemaUsedForCacheRefresh,
CacheSqlToUseToTriggerRefresh);
}
private void loadProducts()
{
IProductBussinessService productBussinessService = null;
DataTable productTable = null;
try
{
if (Cache["Products"] == null)
{
productBussinessService = BizDelegateFactory.Current.ProductBussinessService;
productTable = productBussinessService.RetreiveAllProducts();
SqlCacheDependency productCache = new SqlCacheDependency("SpencersDB", "Products");
Cache.Insert("Products", productTable,productCache);
}
}
catch (Exception ex)
{
CommonLabel.Text = ex.Message;
CommonLabel.ForeColor = Color.Red;
}
finally
{
productBussinessService = null;
}
}
protected void GridComentarios_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
//string idComentario = e.Row.Cells[0].Text; // Si fuese un BoundField
string idComentario = (e.Row.Cells[0].FindControl("lblIdComentario") as Label).Text;
GridView grilla = (GridView)e.Row.FindControl("GridSubComentario");
lblCache.Text = "Cache Refresh: " + DateTime.Now.ToLongTimeString();
SqlDependency.Start(GetConnectionString());
using (SqlConnection connection = new SqlConnection(GetConnectionString()))
{
using (SqlCommand command = new SqlCommand(ObtenerSubComentarios(idComentario), connection))
{
SqlCacheDependency dependency = new SqlCacheDependency(command);
int numberOfMinutes = 3;
DateTime expires = DateTime.Now.AddMinutes(numberOfMinutes);
Response.Cache.SetExpires(expires);
Response.Cache.SetCacheability(HttpCacheability.Public);
Response.Cache.SetValidUntilExpires(true);
Response.AddCacheDependency(dependency);
connection.Open();
grilla.DataSource = command.ExecuteReader();
grilla.DataBind();
if (!Context.User.Identity.IsAuthenticated)
{
imgComentario1.ImageUrl = string.Format("img/avatar/Ninguno.jpg");
}
else
{
imgComentario1.ImageUrl = string.Format("img/avatar/{0}.jpg", Context.User.Identity.Name);
}
}
}
}
}
public override SiteMapNode BuildSiteMap()
{
lock (_lock)
{
// Return immediately if this method has been called before
if (_root != null)
return _root;
// Query the database for site map nodes
SqlConnection connection = new SqlConnection(_connect);
try
{
SqlCommand command = new SqlCommand("proc_GetSiteMap", connection);
command.CommandType = CommandType.StoredProcedure;
// Create a SQL cache dependency if requested
SqlCacheDependency dependency = null;
if (_2005dependency)
dependency = new SqlCacheDependency(command);
else if (!String.IsNullOrEmpty(_database) && !string.IsNullOrEmpty(_table))
dependency = new SqlCacheDependency(_database, _table);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
_indexID = reader.GetOrdinal("ID");
_indexUrl = reader.GetOrdinal("Url");
_indexTitle = reader.GetOrdinal("Title");
_indexDesc = reader.GetOrdinal("Description");
_indexRoles = reader.GetOrdinal("Roles");
_indexParent = reader.GetOrdinal("Parent");
if (reader.Read())
{
// Create the root SiteMapNode and add it to the site map
_root = CreateSiteMapNodeFromDataReader(reader);
AddNode(_root, null);
// Build a tree of SiteMapNodes underneath the root node
while (reader.Read())
{
// Create another site map node and add it to the site map
SiteMapNode node = CreateSiteMapNodeFromDataReader(reader);
AddNode(node, GetParentNodeFromDataReader(reader));
}
// Use the SQL cache dependency
if (dependency != null)
{
HttpRuntime.Cache.Insert(_cacheDependencyName, new object(), dependency,
Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.NotRemovable,
new CacheItemRemovedCallback(OnSiteMapChanged));
}
}
}
finally
{
connection.Close();
}
// Return the root SiteMapNode
return _root;
}
}
