public void Constructor_1 ()
{
SqlCacheDependency sqc;
AssertExtensions.Throws<ArgumentNullException> (() => {
sqc = new SqlCacheDependency (null);
}, "#A1");
}
/// <summary>
/// 向 Cache 对象中插入对象，后者具有依赖项、过期和优先级策略以及一个委托（可用于在从 Cache 移除插入项时通知应用程序）。
/// </summary>
/// <param name="key"></param>
/// <param name="value"></param>
/// <param name="dep">在存储于 ASP.NET 应用程序的 Cache 对象中的项与文件、缓存键、文件或缓存键的数组或另一个 CacheDependency 对象之间建立依附性关系。CacheDependency 类监视依附性关系，以便在任何这些对象更改时，该缓存项都会自动移除。</param>
/// <param name="seconds"></param>
/// <param name="priority"></param>
public static void Insert(string key, object value, System.Web.Caching.SqlCacheDependency dep, int seconds, CacheItemPriority priority)
{
if (value != null)
{
s_Cache.Insert(key, value, dep, DateTime.Now.AddSeconds((double)(Factor * seconds)), TimeSpan.Zero, priority, null);
}
}
public void CacheTableUser()
{
SqlDependency.Start(conStr); //启动监听服务，ps：只需启动一次
System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications(conStr); //设置通知的数据库连接，ps：只需设置一次
System.Web.Caching.SqlCacheDependencyAdmin.EnableTableForNotifications(conStr, "dbo.tUser"); //设置通知的数据库连接和表，ps：只需设置一次
string sql = "select * from dbo.tUser";
SqlConnection con = new SqlConnection(conStr);
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds, "cache");
System.Web.Caching.SqlCacheDependency cd = new System.Web.Caching.SqlCacheDependency("SqlCacheDependencyDB", "dbo.tUser");//建立关联
//添加到缓存中
System.Web.HttpContext.Current.Cache.Insert("cache", ds, cd);
}
public static CacheDependency CreateOutputCacheDependency(string dependency)
{
Sql7DependencyInfo info;
if (dependency == null)
{
throw new HttpException(System.Web.SR.GetString("Invalid_sqlDependency_argument", new object[] { dependency }));
}
if (StringUtil.EqualsIgnoreCase(dependency, "CommandNotification"))
{
HttpContext current = HttpContext.Current;
SqlCacheDependency dependency2 = new SqlCacheDependency();
current.SqlDependencyCookie = dependency2._sqlYukonDep.Id;
return(dependency2);
}
AggregateCacheDependency dependency3 = null;
ArrayList list = ParseSql7OutputCacheDependency(dependency);
if (list.Count == 1)
{
info = (Sql7DependencyInfo)list[0];
return(CreateSql7SqlCacheDependencyForOutputCache(info._database, info._table, dependency));
}
dependency3 = new AggregateCacheDependency();
for (int i = 0; i < list.Count; i++)
{
info = (Sql7DependencyInfo)list[i];
dependency3.Add(new CacheDependency[] { CreateSql7SqlCacheDependencyForOutputCache(info._database, info._table, dependency) });
}
return(dependency3);
}
public static void Max(string key, object value, System.Web.Caching.SqlCacheDependency dep)
{
if (value != null)
{
s_Cache.Insert(key, value, dep, DateTime.MaxValue, TimeSpan.Zero, CacheItemPriority.Normal, null);
}
}
public DataTable GetPersonalDetails(string personalid)
{
string cachekey = "Personal"+personalid;
if (HttpContext.Current.Cache[cachekey] != null)
{
DataTable cacheds = HttpContext.Current.Cache[cachekey] as DataTable;
return cacheds;
}
else
{
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand("spGetPersonalDetails", dbconn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PERSONALID", personalid);
SqlCacheDependency dependency = new SqlCacheDependency(cmd);
try
{
if (dbconn.State == ConnectionState.Closed) { dbconn.Open(); }
SqlDataReader dr = cmd.ExecuteReader();
dt.Load(dr);
HttpContext.Current.Cache.Insert(cachekey, dt, dependency);
return dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbconn.State == ConnectionState.Open) { dbconn.Close(); }
}
}
}
/**
* 该方法放在需要存储缓存的地方
* **/
public static void Insert(string tableName, object data, int cacheTime)
{
//制定缓存策略,"SqlDependency"是web.config中的名称.
System.Web.Caching.SqlCacheDependency scd = new System.Web.Caching.SqlCacheDependency("SqlDependency", tableName);
//插入缓存
Cache.Insert(tableName, data, scd, DateTime.Now.AddSeconds(cacheTime), Cache.NoSlidingExpiration);
}
public static int GetCountFromCache <T>(this System.Linq.IQueryable <T> q, System.Data.Linq.DataContext dc)
{
try
{
string CacheId = q.ToString() + "Count?";
foreach (System.Data.Common.DbParameter dbp in dc.GetCommand(q).Parameters)
{
CacheId += dbp.ParameterName + "=" + dbp.Value.ToString() + "&";
}
object count;
//if (Environment.StackTrace.Contains("CMS\\"))
//{
count = q.Count();
return((int)count);
//}
//else
//{
//System.Threading.Thread.Sleep(500);
count = System.Web.HttpRuntime.Cache.Get(CacheId);
// }
if (count == null)
{
List <string> tablesNames = dc.Mapping.GetTables().Where(t => CacheId.Contains("[" + t.TableName.Substring(4) + "]")).Select(t => t.TableName.Substring(4)).ToList();
string connStr = dc.Connection.ConnectionString;
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStr))
{
conn.Open();
System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications(connStr);
System.Web.Caching.SqlCacheDependency sqldep;
AggregateCacheDependency aggDep = new AggregateCacheDependency();
foreach (string tableName in tablesNames)
{
if (!System.Web.Caching.SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connStr).Contains(tableName))
{
System.Web.Caching.SqlCacheDependencyAdmin.EnableTableForNotifications(connStr, tableName);
}
if (tableName.Contains("Comment") || tableName.Contains("PollDetail"))
{
sqldep = new System.Web.Caching.SqlCacheDependency("PlatformCacheSmallPollTime", tableName);
}
else
{
sqldep = new System.Web.Caching.SqlCacheDependency("PlatformCache", tableName);
}
aggDep.Add(sqldep);
}
count = q.Count();
System.Web.HttpRuntime.Cache.Insert(CacheId, count, aggDep, DateTime.Now.AddDays(1), System.Web.Caching.Cache.NoSlidingExpiration);
}
}
//Return the created (or cached) List
return((int)count);
}
catch (System.Exception ex)
{
throw ex;
}
}
/// <summary>
/// ��ӻ��� SQL �������������
/// </summary>
/// <param name="CacheKey"></param>
/// <param name="objObject"></param>
/// <param name="cd"></param>
/// <param name="absoluteExpiration"></param>
/// <param name="slidingExpiration"></param>
public static void SetCache(string CacheKey, object objObject, System.Web.Caching.SqlCacheDependency cd, DateTime absoluteExpiration, TimeSpan slidingExpiration, CacheItemRemovedCallback onRemove)
{
onRemove = new CacheItemRemovedCallback(RemovedCallback);
System.Web.Caching.Cache objCache = HttpRuntime.Cache;
objCache.Insert(CacheKey, objObject, cd, absoluteExpiration, slidingExpiration, CacheItemPriority.High, onRemove);
}
public DataSet GetSeasonDates()
{
string cachekey = "SeasonDates";
if (HttpContext.Current.Cache[cachekey] != null)
{
DataSet cacheds = HttpContext.Current.Cache[cachekey] as DataSet;
return cacheds;
}
else
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand("SPDIARYSEASONDATE", dbconn);
cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.AddWithValue("@DATE", date);
cmd.CommandTimeout = 0;
SqlCacheDependency dependency = new SqlCacheDependency(cmd);
try
{
if (dbconn.State == ConnectionState.Closed) { dbconn.Open(); }
SqlDataAdapter da = new SqlDataAdapter();
da = new SqlDataAdapter(cmd);
da.Fill(ds);
HttpContext.Current.Cache.Insert(cachekey, ds, dependency);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbconn.State == ConnectionState.Open) { dbconn.Close(); }
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
List<Photos> list;
if (Cache["list"] == null)
{
list = new PhotosBLL().GetAllPhotos();
//Cache["list"] = list;
//string path = Request.MapPath("~/log/TextFile.txt");
//CacheDependency cd = new CacheDependency(path);
//Cache.Insert("list", list, cd);
System.Web.Caching.SqlCacheDependency dep = new System.Web.Caching.SqlCacheDependency("myphotos", "photos");
Cache.Insert("list", list, dep, System.Web.Caching.Cache.NoAbsoluteExpiration, System.Web.Caching.Cache.NoSlidingExpiration);
Response.Write("读取数据库");
}
else
{
list = Cache["list"] as List<Photos>;
Response.Write("读取缓存");
}
TimeSpan ts = DateTime.Now - Context.Timestamp;
Response.Write(ts.TotalMilliseconds + "<br />");
foreach (Photos item in list)
{
Response.Write(item.PTitle + "<br />");
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
// here to get data from database
string connectionString = WebConfigurationManager.ConnectionStrings["NorthWind"].ConnectionString;
SqlConnection con = new SqlConnection(connectionString);
string sql = "SELECT EmployeeID, FirstName, LastName, City FROM dbo.Employees";
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
try
{
// fill two data sets
DataSet ds = new DataSet();
da.Fill(ds, "Employees");
SqlCacheDependency empDependency = new SqlCacheDependency(cmd);
Cache.Insert("Employees", ds, empDependency);
}
finally
{
}
}
}
public static DataSet GetTable1()
{
var cache = HttpRuntime.Cache;
var value = cache.Get("Table_1") as DataSet;
if (value == null)
{
value = GetData();
var cacheDependency = new SqlCacheDependency("sqlCacheDependConnection", "Table_1");
SetCache("Table_1", value, cacheDependency);
}
return value;
}
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);
}
public static void CreateSQLDependency(string dbName,string tblName,string CacheName,object Data)
{
try
{
System.Web.Caching.SqlCacheDependency __sqlDep = new System.Web.Caching.SqlCacheDependency(dbName, tblName);
if (Data == null) return;
HttpContext.Current.Cache.Insert(CacheName, Data, __sqlDep);
}
catch (Exception ex)
{
}
}
public static void SetCache(DataTable dataCache, string cacheName, string[] tableNameInDatabase)
{
//System.Web.Caching.SqlCacheDependency sqlDep1 = new System.Web.Caching.SqlCacheDependency(Const.DATABASE_NAME, "tblTradeTransaction");
//System.Web.Caching.SqlCacheDependency sqlDep2 = new System.Web.Caching.SqlCacheDependency(Const.DATABASE_NAME, "tblRemainTransaction");
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(sqlDep1, sqlDep2);
agg.Add(sqlDep);
HttpContext.Current.Cache.Insert(cacheName, dataCache, agg, DateTime.Now.AddDays(1), Cache.NoSlidingExpiration);
}
private static void SetCacheData(string cacheItemName, object dataSet, string connString, string tableName)
{
string cacheEntryname = "NorthwindCache";
//Many articles online state that the following statement should be run only once at the start of the application.
//Few articles say the object get replaced if called again with same connection string.
SqlDependency.Start(connString);
//The following statements needs to be run only once against the connection string and the database table, hence may also be moved to an appropriate place in code.
SqlCacheDependencyAdmin.EnableNotifications(connString);
SqlCacheDependencyAdmin.EnableTableForNotifications(connString, tableName);
System.Web.Caching.SqlCacheDependency dependency = new System.Web.Caching.SqlCacheDependency(cacheEntryname, tableName);
HostingEnvironment.Cache.Insert(cacheItemName, dataSet, dependency);
}
private static SqlCacheDependency CreateSql7SqlCacheDependencyForOutputCache(string database, string table, string depString)
{
SqlCacheDependency dependency;
try
{
dependency = new SqlCacheDependency(database, table);
}
catch (HttpException exception)
{
HttpException e = new HttpException(System.Web.SR.GetString("Invalid_sqlDependency_argument2", new object[] { depString, exception.Message }), exception);
e.SetFormatter(new UseLastUnhandledErrorFormatter(e));
throw e;
}
return(dependency);
}
public void Constructor_2 ()
{
SqlCacheDependency sqc;
AssertExtensions.Throws<ArgumentNullException> (() => {
sqc = new SqlCacheDependency (null, "myTable");
}, "#A1");
AssertExtensions.Throws<ArgumentNullException> (() => {
sqc = new SqlCacheDependency ("myDatabase", null);
}, "#A2");
// Cannot be tested without an existing database
//AssertExtensions.Throws<ArgumentNullException> (() => {
// sqc = new SqlCacheDependency ("myDatabase", "myTable");
//}, "#A3");
}
public override List<TranslatableText> GetAll()
{
List<TranslatableText> all = new List<TranslatableText>();
object o = HttpContext.Current.Cache[CacheKey];
if (o != null)
{
all = (List<TranslatableText>)o;
}
else
{
all = base.GetAll();
SqlCacheDependency dep = new SqlCacheDependency("SiteDB", "TranslatableTexts");
HttpContext.Current.Cache.Insert(CacheKey, all, dep);
}
return all;
}
public static void AddToCache(System.Data.Linq.DataContext dc, object cachedObject, string cacheId, List <string> notificationTables)
{
try
{
return;
if (cachedObject != null)
{
//List<string> tablesNames = dc.Mapping.GetTables().Where(t => CacheId.Contains("[" + t.TableName.Substring(4) + "]")).Select(t => t.TableName.Substring(4)).ToList();
string connStr = dc.Connection.ConnectionString;
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStr))
{
conn.Open();
System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications(connStr);
System.Web.Caching.SqlCacheDependency sqldep;
AggregateCacheDependency aggDep = new AggregateCacheDependency();
foreach (string tableName in notificationTables)
{
if (!System.Web.Caching.SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connStr).Contains(tableName))
{
System.Web.Caching.SqlCacheDependencyAdmin.EnableTableForNotifications(connStr, tableName);
}
if (tableName.Contains("Comment") || tableName.Contains("PollDetail"))
{
sqldep = new System.Web.Caching.SqlCacheDependency("PlatformCacheSmallPollTime", tableName);
}
else
{
sqldep = new System.Web.Caching.SqlCacheDependency("PlatformCache", tableName);
}
aggDep.Add(sqldep);
}
System.Web.HttpRuntime.Cache.Insert(cacheId, cachedObject, aggDep, DateTime.Now.AddDays(1), System.Web.Caching.Cache.NoSlidingExpiration);
}
}
}
catch (System.Exception ex)
{
throw ex;
}
}
protected static Dictionary<string, DbConfigItem> GetAll()
{
Dictionary<string, DbConfigItem> all = new Dictionary<string, DbConfigItem>();
object o = HttpContext.Current.Cache[CacheKey];
if (o != null)
{
all = (Dictionary<string, DbConfigItem>)o;
}
else
{
foreach (DbConfigItem item in DbConfig.GetAll())
{
all.Add(item.Name, item);
}
SqlCacheDependency dep = new SqlCacheDependency("SiteDB", "DbConfig");
HttpContext.Current.Cache.Insert(CacheKey, all, dep);
}
return all;
}
public DataSet GetDiary(Nullable<DateTime> date, string type)
{
string cachekey = "Diary" +Convert.ToString(date) + type;
if (HttpContext.Current.Cache[cachekey] != null)
{
DataSet cacheds = HttpContext.Current.Cache[cachekey] as DataSet;
return cacheds;
}
else
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand("spDiaryDetailsget", dbconn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@DATE", date);
cmd.Parameters.AddWithValue("@TYPE", type);
cmd.CommandTimeout = 60;
SqlCacheDependency dependency = new SqlCacheDependency(cmd);
try
{
if (dbconn.State == ConnectionState.Closed) { dbconn.Open(); }
SqlDataAdapter da = new SqlDataAdapter();
da = new SqlDataAdapter(cmd);
da.Fill(ds);
HttpContext.Current.Cache.Insert(cachekey, ds, dependency);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbconn.State == ConnectionState.Open) { dbconn.Close(); }
}
}
}
public DataTable Getcountry()
{
string cachekey = "country";
if (HttpContext.Current.Cache[cachekey] != null)
{
DataTable cacheds = HttpContext.Current.Cache[cachekey] as DataTable;
return cacheds;
}
else
{
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand("spGetCountry", dbconn);
cmd.CommandType = CommandType.StoredProcedure;
SqlCacheDependency dependency = new SqlCacheDependency(cmd);
try
{
if (dbconn.State == ConnectionState.Closed) { dbconn.Open(); }
SqlDataReader dr = cmd.ExecuteReader();
dt.Load(dr);
HttpContext.Current.Cache.Insert(cachekey, dt, dependency);
return dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbconn.State == ConnectionState.Open) { dbconn.Close(); }
}
}
}
public static void AddCacheItem(string tableName, string cacheKey, object value)
{
SqlCacheDependency tableDependency = null;
Cache DataCache = HttpRuntime.Cache;
try
{
tableDependency = new SqlCacheDependency("ezFixUpDB", tableName);
}
// Handle the DatabaseNotEnabledForNotificationException with
// a call to the SqlCacheDependencyAdmin.EnableNotifications method.
catch (DatabaseNotEnabledForNotificationException exDBDis)
{
try
{
SqlCacheDependencyAdmin.EnableNotifications("ezFixUpDB");
}
// If the database does not have permissions set for creating tables,
// the UnauthorizedAccessException is thrown. Handle it by redirecting
// to an error page.
catch (UnauthorizedAccessException exPerm)
{
}
}
// Handle the TableNotEnabledForNotificationException with
// a call to the SqlCacheDependencyAdmin.EnableTableForNotifications method.
catch (TableNotEnabledForNotificationException exTabDis)
{
try
{
SqlCacheDependencyAdmin.EnableTableForNotifications("ezFixUpDB", tableName);
}
// If a SqlException is thrown, redirect to an error page.
catch (SqlException exc)
{
}
}
// If all the other code is successful, add MySource to the Cache
// with a dependency on SqlDep. If the Categories table changes,
// MySource will be removed from the Cache. Then generate a message
// that the data is newly created and added to the cache.
finally
{
DataCache.Insert(tableName, value, tableDependency,
Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration);
}
}
/// <devdoc>
/// Returns all the rows of the datasource.
/// Parameters are taken from the SqlDataSource.Parameters property collection.
/// If DataSourceMode is set to DataSet then a DataView is returned.
/// If DataSourceMode is set to DataReader then a DataReader is returned, and it must be closed when done.
/// </devdoc>
protected internal override IEnumerable ExecuteSelect(DataSourceSelectArguments arguments) {
if (SelectCommand.Length == 0) {
return null;
}
DbConnection connection = _owner.CreateConnection(_owner.ConnectionString);
if (connection == null) {
throw new InvalidOperationException(SR.GetString(SR.SqlDataSourceView_CouldNotCreateConnection, _owner.ID));
}
DataSourceCache cache = _owner.Cache;
bool cacheEnabled = (cache != null) && (cache.Enabled);
//int startRowIndex = arguments.StartRowIndex;
//int maximumRows = arguments.MaximumRows;
string sortExpression = arguments.SortExpression;
if (CanPage) {
arguments.AddSupportedCapabilities(DataSourceCapabilities.Page);
}
if (CanSort) {
arguments.AddSupportedCapabilities(DataSourceCapabilities.Sort);
}
if (CanRetrieveTotalRowCount) {
arguments.AddSupportedCapabilities(DataSourceCapabilities.RetrieveTotalRowCount);
}
// If caching is enabled, load DataSet from cache
if (cacheEnabled) {
if (_owner.DataSourceMode != SqlDataSourceMode.DataSet) {
throw new NotSupportedException(SR.GetString(SR.SqlDataSourceView_CacheNotSupported, _owner.ID));
}
arguments.RaiseUnsupportedCapabilitiesError(this);
DataSet dataSet = _owner.LoadDataFromCache(0, -1) as DataSet;
if (dataSet != null) {
/*if (arguments.RetrieveTotalRowCount) {
int cachedTotalRowCount = _owner.LoadTotalRowCountFromCache();
if (cachedTotalRowCount >= 0) {
arguments.TotalRowCount = cachedTotalRowCount;
}
else {
// query for row count and then save it in cache
cachedTotalRowCount = QueryTotalRowCount(connection, arguments);
arguments.TotalRowCount = cachedTotalRowCount;
_owner.SaveTotalRowCountToCache(cachedTotalRowCount);
}
}*/
IOrderedDictionary parameterValues = FilterParameters.GetValues(_context, _owner);
if (FilterExpression.Length > 0) {
SqlDataSourceFilteringEventArgs filterArgs = new SqlDataSourceFilteringEventArgs(parameterValues);
OnFiltering(filterArgs);
if (filterArgs.Cancel) {
return null;
}
}
return FilteredDataSetHelper.CreateFilteredDataView(dataSet.Tables[0], sortExpression, FilterExpression, parameterValues);
}
}
// Create command and add parameters
DbCommand command = _owner.CreateCommand(SelectCommand, connection);
InitializeParameters(command, SelectParameters, null);
command.CommandType = GetCommandType(SelectCommandType);
// Raise event to allow customization and cancellation
SqlDataSourceSelectingEventArgs selectingEventArgs = new SqlDataSourceSelectingEventArgs(command, arguments);
OnSelecting(selectingEventArgs);
// If the operation was cancelled, exit immediately
if (selectingEventArgs.Cancel) {
return null;
}
// Add the sort parameter to allow for custom stored procedure sorting, if necessary
string sortParameterName = SortParameterName;
if (sortParameterName.Length > 0) {
if (command.CommandType != CommandType.StoredProcedure) {
throw new NotSupportedException(SR.GetString(SR.SqlDataSourceView_SortParameterRequiresStoredProcedure, _owner.ID));
}
command.Parameters.Add(_owner.CreateParameter(ParameterPrefix + sortParameterName, sortExpression));
// We reset the sort expression here so that we pretend as
// though we're not really sorting (since the developer is
// worrying about it instead of us).
arguments.SortExpression = String.Empty;
}
arguments.RaiseUnsupportedCapabilitiesError(this);
// reset these values, since they might have changed in the OnSelecting event
sortExpression = arguments.SortExpression;
//startRowIndex = arguments.StartRowIndex;
//maximumRows = arguments.MaximumRows;
// Perform null check if user wants to cancel on any null parameter value
if (CancelSelectOnNullParameter) {
int paramCount = command.Parameters.Count;
for (int i = 0; i < paramCount; i++) {
DbParameter parameter = command.Parameters[i];
if ((parameter != null) &&
(parameter.Value == null) &&
((parameter.Direction == ParameterDirection.Input) || (parameter.Direction == ParameterDirection.InputOutput))) {
return null;
}
}
}
// Replace null values in parameters with DBNull.Value
ReplaceNullValues(command);
/*if (arguments.RetrieveTotalRowCount && SelectCountCommand.Length > 0) {
int cachedTotalRowCount = -1;
if (cacheEnabled) {
cachedTotalRowCount = _owner.LoadTotalRowCountFromCache();
if (cachedTotalRowCount >= 0) {
arguments.TotalRowCount = cachedTotalRowCount;
}
}
if (cachedTotalRowCount < 0) {
cachedTotalRowCount = QueryTotalRowCount(connection, arguments);
arguments.TotalRowCount = cachedTotalRowCount;
if (cacheEnabled) {
_owner.SaveTotalRowCountToCache(cachedTotalRowCount);
}
}
}*/
IEnumerable selectResult = null;
switch (_owner.DataSourceMode) {
case SqlDataSourceMode.DataSet:
{
SqlCacheDependency cacheDependency = null;
if (cacheEnabled && cache is SqlDataSourceCache) {
SqlDataSourceCache sqlCache = (SqlDataSourceCache)cache;
if (String.Equals(sqlCache.SqlCacheDependency, SqlDataSourceCache.Sql9CacheDependencyDirective, StringComparison.OrdinalIgnoreCase)) {
if (!(command is System.Data.SqlClient.SqlCommand)) {
throw new InvalidOperationException(SR.GetString(SR.SqlDataSourceView_CommandNotificationNotSupported, _owner.ID));
}
cacheDependency = new SqlCacheDependency((System.Data.SqlClient.SqlCommand)command);
}
}
DbDataAdapter adapter = _owner.CreateDataAdapter(command);
DataSet dataSet = new DataSet();
int rowsAffected = 0;
bool eventRaised = false;
try {
rowsAffected = adapter.Fill(dataSet, Name);
// Raise the Selected event
eventRaised = true;
SqlDataSourceStatusEventArgs selectedEventArgs = new SqlDataSourceStatusEventArgs(command, rowsAffected, null);
OnSelected(selectedEventArgs);
}
catch (Exception ex) {
if (!eventRaised) {
// Raise the Selected event
SqlDataSourceStatusEventArgs selectedEventArgs = new SqlDataSourceStatusEventArgs(command, rowsAffected, ex);
OnSelected(selectedEventArgs);
if (!selectedEventArgs.ExceptionHandled) {
throw;
}
}
else {
bool isCustomException;
ex = BuildCustomException(ex, DataSourceOperation.Select, command, out isCustomException);
if (isCustomException) {
throw ex;
}
else {
throw;
}
}
}
finally {
if (connection.State == ConnectionState.Open) {
connection.Close();
}
}
// If caching is enabled, save DataSet to cache
DataTable dataTable = (dataSet.Tables.Count > 0 ? dataSet.Tables[0] : null);
if (cacheEnabled && dataTable != null) {
_owner.SaveDataToCache(0, -1, dataSet, cacheDependency);
}
if (dataTable != null) {
IOrderedDictionary parameterValues = FilterParameters.GetValues(_context, _owner);
if (FilterExpression.Length > 0) {
SqlDataSourceFilteringEventArgs filterArgs = new SqlDataSourceFilteringEventArgs(parameterValues);
OnFiltering(filterArgs);
if (filterArgs.Cancel) {
return null;
}
}
selectResult = FilteredDataSetHelper.CreateFilteredDataView(dataTable, sortExpression, FilterExpression, parameterValues);
}
break;
}
case SqlDataSourceMode.DataReader:
{
if (FilterExpression.Length > 0) {
throw new NotSupportedException(SR.GetString(SR.SqlDataSourceView_FilterNotSupported, _owner.ID));
}
if (sortExpression.Length > 0) {
throw new NotSupportedException(SR.GetString(SR.SqlDataSourceView_SortNotSupported, _owner.ID));
}
bool eventRaised = false;
try {
if (connection.State != ConnectionState.Open) {
connection.Open();
}
selectResult = command.ExecuteReader(CommandBehavior.CloseConnection);
// Raise the Selected event
eventRaised = true;
SqlDataSourceStatusEventArgs selectedEventArgs = new SqlDataSourceStatusEventArgs(command, 0, null);
OnSelected(selectedEventArgs);
}
catch (Exception ex) {
if (!eventRaised) {
// Raise the Selected event
SqlDataSourceStatusEventArgs selectedEventArgs = new SqlDataSourceStatusEventArgs(command, 0, ex);
OnSelected(selectedEventArgs);
if (!selectedEventArgs.ExceptionHandled) {
throw;
}
}
else {
bool isCustomException;
ex = BuildCustomException(ex, DataSourceOperation.Select, command, out isCustomException);
if (isCustomException) {
throw ex;
}
else {
throw;
}
}
}
break;
}
}
return selectResult;
}
public DataTable GetMetroCityStates(string active_flag)
{
string cachekey = "MetroCityStates";
if (HttpContext.Current.Cache[cachekey] != null)
{
DataTable cacheds = HttpContext.Current.Cache[cachekey] as DataTable;
return cacheds;
}
else
{
SqlCommand cmd = new SqlCommand("spGetMetroList", dbconn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ActiveFlag", active_flag);
SqlCacheDependency dependency = new SqlCacheDependency(cmd);
DataTable dt = new DataTable();
try
{
dbconn.Open();
SqlDataReader dr = cmd.ExecuteReader();
dt.Load(dr);
HttpContext.Current.Cache.Insert(cachekey, dt, dependency);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbconn.State == ConnectionState.Open) { dbconn.Close(); }
}
return dt;
}
}
/// <summary>
/// Loads the site map information from rb_Pages table and builds the site map information
/// in memory.
/// </summary>
/// <returns>
/// The root System.Web.SiteMapNode of the site map navigation structure.
/// </returns>
/// <remarks>
/// </remarks>
public override SiteMapNode BuildSiteMap()
{
lock (this._theLock)
{
// Return immediately if this method has been called before
// if (_root != null) {
// if (_root["PortalID"] == PortalID) {
// return _root;
// } else {
this.Clear();
// }
// }
// Query the database for site map nodes
var connection = new SqlConnection(this.connect);
try
{
var command = new SqlCommand(BuildSiteMapQuery(), connection) { CommandType = CommandType.Text };
var parameterPageId = new SqlParameter("@PortalID", SqlDbType.Int, 4) { Value = PortalId };
command.Parameters.Add(parameterPageId);
var parameterCulture = new SqlParameter("@Culture", SqlDbType.VarChar, 5) { Value = Thread.CurrentThread.CurrentUICulture.ToString() };
command.Parameters.Add(parameterCulture);
//Thread.CurrentThread.CurrentUICulture + PortalId
// Create a SQL cache dependency if requested
SqlCacheDependency dependency = null;
if (this.dependency2005)
{
dependency = new SqlCacheDependency(command);
}
else if (!String.IsNullOrEmpty(this.database) && !string.IsNullOrEmpty(this.table))
{
dependency = new SqlCacheDependency(this.database, this.table);
}
connection.Open();
var reader = command.ExecuteReader();
this.indexPageId = reader.GetOrdinal("PageID");
this.indexParentPageId = reader.GetOrdinal("ParentPageID");
this.indexPageOrder = reader.GetOrdinal("PageOrder");
this.indexPortalId = reader.GetOrdinal("PortalID");
this.indexPageName = reader.GetOrdinal("PageName");
this.indexAuthorizedRoles = reader.GetOrdinal("AuthorizedRoles");
this.indexPageLayout = reader.GetOrdinal("PageLayout");
this.indexPageDescription = reader.GetOrdinal("PageDescription");
if (reader.Read())
{
// Create an empty root node and add it to the site map
this.root = new SiteMapNode(
this,
RootNodeId.ToString(),
HttpUrlBuilder.BuildUrl(),
string.Empty,
string.Empty,
new[] { "All Users" },
null,
null,
null);
this.root["PortalID"] = PortalId;
this.theNodes.Add(RootNodeId, this.root);
this.AddNode(this.root, null);
// Build a tree of SiteMapNodes underneath the root node
do
{
// Create another site map node and add it to the site map
var node = this.CreateSiteMapNodeFromDataReader(reader);
var parentNode = this.GetParentNodeFromDataReader(reader);
if (parentNode != null)
{
try
{
this.AddNode(node, parentNode);
}
catch
{
node.Url = node.Url.Contains("?")
? string.Format("{0}&lnkId={1}", node.Url, node.Key)
: string.Format("{0}?lnkId={1}", node.Url, node.Key);
this.AddNode(node, parentNode);
}
}
}
while (reader.Read());
// Use the SQL cache dependency
if (dependency != null) {
HttpRuntime.Cache.Insert(
CacheDependencyName + PortalId,
new object(),
dependency,
Cache.NoAbsoluteExpiration,
Cache.NoSlidingExpiration,
CacheItemPriority.NotRemovable,
this.OnSiteMapChanged);
} else {
}
}
}
finally
{
connection.Close();
}
// Return the root SiteMapNode
return this.root;
}
}
/// <summary>
/// 初始化依赖项
/// </summary>
/// <param name="tablename"></param>
/// <returns></returns>
public static System.Web.Caching.SqlCacheDependency initalSqlCacheDependency(string tablename)
{
//依赖数据库codematic中的P_Product表变化 来更新缓存
System.Web.Caching.SqlCacheDependency dep = new System.Web.Caching.SqlCacheDependency("OnLineTest", tablename);
return(dep);
}
/// <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 override SiteMapNode BuildSiteMap()
{
lock (this)
{
if (null == rootNode)
{
Clear();
LoadSiteMap();
SqlCacheDependency dep = new SqlCacheDependency(CacheDB,CacheTable);
HttpRuntime.Cache.Insert(CacheKey,new object(),dep,Cache.NoAbsoluteExpiration,Cache.NoSlidingExpiration, CacheItemPriority.NotRemovable, new CacheItemRemovedCallback(OnSiteMapChanged));
}
return rootNode;
}
}
public static void SetDataToCache(String dataTableToCache, string cacheName, string tableNameInDatabase, String databaseName)
{
SqlCacheDependency sqlDependency = new SqlCacheDependency(databaseName, tableNameInDatabase);
HttpContext.Current.Cache.Insert(cacheName, dataTableToCache, sqlDependency, DateTime.Now.AddDays(1), Cache.NoSlidingExpiration);
}
public DataTable LoadCities()
{
string cachekey = "City";
if (HttpContext.Current.Cache[cachekey] != null)
{
DataTable cacheds = HttpContext.Current.Cache[cachekey] as DataTable;
return cacheds;
}
else
{
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand("spCityNameSearch", dbconn);
cmd.CommandType = CommandType.StoredProcedure;
SqlCacheDependency dependency = new SqlCacheDependency(cmd);
try
{
cmd.Parameters.AddWithValue("@CITY_NAME", "");
cmd.Parameters.AddWithValue("@ActiveFlag", "Y");
if (dbconn.State == ConnectionState.Closed) { dbconn.Open(); }
SqlDataReader dr = cmd.ExecuteReader();
dt.Load(dr);
HttpContext.Current.Cache.Insert(cachekey, dt, dependency);
return dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbconn.State == ConnectionState.Open) { dbconn.Close(); }
}
}
}
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("P_Sunny_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;
}
}
/// <summary>
/// Execute the stored procedure. Uses the tracer from the diagnostics instead of global
/// </summary>
/// <returns>The instance of the StoredProcedureReader.</returns>
public IDnaDataReader Execute2()
{
using (IDnaTracer tracer = _dnaDiagnostics.CreateTracer(this.GetType().Namespace))
{
tracer.Write("Executing " + _name, this.GetType().Namespace);
WriteTimedEventToLog("Executing " + _name);
OpenConnection();
_cmd = _connection.CreateCommand();
_cmd.CommandTimeout = 0;
_cmd.CommandType = CommandType.StoredProcedure;
_cmd.CommandText = _name;
foreach (KeyValuePair<string, object> param in _parameters)
{
tracer.Write(new LogEntry() { Message = String.Format("Adding Parameter:{0} Value:{1}", param.Key, param.Value), Severity = TraceEventType.Verbose });
var sqlParam = new SqlParameter(param.Key, param.Value);
if (param.Value == null)
{
sqlParam.Value = DBNull.Value;
}
_cmd.Parameters.Add(sqlParam);
}
foreach (KeyValuePair<string, KeyValuePair<SqlDbType, object>> outParam in _outputParams)
{
tracer.Write(new LogEntry() { Message = String.Format("Adding Out Parameter:{0} Value:{1}", outParam.Key, outParam.Value), Severity = TraceEventType.Verbose });
SqlParameter param = new SqlParameter(outParam.Key, outParam.Value.Value);
param.Direction = ParameterDirection.Output;
param.SqlDbType = outParam.Value.Key;
_cmd.Parameters.Add(param);
}
if (_returnValue != null)
{
_cmd.Parameters.Add(_returnValue);
}
if (_canCache)
{
if (!_dependencyStarted)
{
lock (_dependencyLock)
{
if (!_dependencyStarted)
{
SqlDependency.Start(GetConnectionString());
}
_dependencyStarted = true;
}
}
_dependency = new SqlCacheDependency(_cmd);
}
tracer.Write("Executed before " + _name, this.GetType().Namespace);
_dataReader = _cmd.ExecuteReader();
tracer.Write("Executed After " + _name, this.GetType().Namespace);
WriteTimedEventToLog("Executed " + _name);
}
return this;
}
/// <summary>
///
/// </summary>
/// <param name="connectionString"></param>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
//--------------------------------------------------------------------------------------------------------------
public static SqlDataReader ExecuteCacheReader(string connectionString, CommandType cmdType, string cmdText,string CacheKey, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlCacheDependency dependency = new SqlCacheDependency(cmd);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
Cache cache = new Cache();
cache.Add(CacheKey, "", dependency, System.Web.Caching.Cache.NoAbsoluteExpiration,System.Web.Caching.Cache.NoSlidingExpiration,System.Web.Caching.CacheItemPriority.Default , null);
cache.Insert(CacheKey, rdr, dependency);
return (SqlDataReader)cache.Get(CacheKey) ;
// conn.Close();
// System.Web.HttpContext.Current.Response.Write(ex.Message.ToString());
}
public static System.Data.DataTable FromCache(this DataAccessComponents.DataAccessComponent q, DbCommand commands)
{
try
{
bool UseCache = false;
Boolean.TryParse(System.Configuration.ConfigurationManager.AppSettings["UseCache"],out UseCache);
System.Data.SqlClient.SqlCommand command = commands as System.Data.SqlClient.SqlCommand;
System.Data.DataTable objCache;
if (!UseCache)
{
objCache = new System.Data.DataTable();
objCache.Load(q.Database.ExecuteReader(command), System.Data.LoadOption.OverwriteChanges);
return objCache;
}
if (command == null) return null;
string CacheId = command.CommandText + "?";
foreach (System.Data.Common.DbParameter dbp in command.Parameters)
{
CacheId += dbp.ParameterName + "=" + dbp.Value.ToString() + "&";
}
//if (Environment.StackTrace.Contains("Administrator\\"))
//{
// objCache = q.ToList();
// return objCache;
//}
//else
{
// System.Threading.Thread.Sleep(500);
objCache = System.Web.HttpRuntime.Cache.Get(CacheId) as System.Data.DataTable;
}
if (objCache == null)
{
List<string> tablesNames = q.TableNames; //command.Mapping.GetTables().Where(t => (t.TableNames.Contains("[")) ? CacheId.Contains(t.TableNames.Substring(4)) : CacheId.Contains("[" + t.TableNames.Substring(4) + "]")).Select(t => t.TableNames.Substring(4)).ToList();
string connStr = q.Database.ConnectionString; // System.Configuration.ConfigurationManager.ConnectionStrings["MainDB"].ConnectionString;
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStr))
{
conn.Open();
System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications(connStr);
System.Web.Caching.SqlCacheDependency sqldep;
AggregateCacheDependency aggDep = new AggregateCacheDependency();
foreach (string tableName in tablesNames)
{
if (!System.Web.Caching.SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connStr).Contains(tableName))
System.Web.Caching.SqlCacheDependencyAdmin.EnableTableForNotifications(connStr, tableName);
if (tableName.Contains("Comment") || tableName.Contains("FormBuilder"))
sqldep = new System.Web.Caching.SqlCacheDependency("NatiqNewsPoll", tableName);
else
sqldep = new System.Web.Caching.SqlCacheDependency("NatiqNews", tableName);
aggDep.Add(sqldep);
}
objCache = new System.Data.DataTable();
objCache.Load(q.Database.ExecuteReader(command), System.Data.LoadOption.OverwriteChanges);
if (objCache != null)
System.Web.HttpRuntime.Cache.Insert(CacheId, objCache, aggDep, DateTime.Now.AddDays(1), System.Web.Caching.Cache.NoSlidingExpiration);
}
}
//Return the created (or cached) List
return objCache;
}
catch (Exception ex)
{
throw ex;
}
}
public static void Insert(string key, object value, System.Web.Caching.SqlCacheDependency dep, int seconds)
{
Insert(key, value, dep, seconds, CacheItemPriority.NotRemovable);
}
public DataTable GetEVPDetails(int id, int type)
{
string cachekey = "EVP"+id+type;
if (HttpContext.Current.Cache[cachekey] != null)
{
DataTable cacheds = HttpContext.Current.Cache[cachekey] as DataTable;
return cacheds;
}
else
{
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand("spGetEVPDetails", dbconn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@ID", id);
cmd.Parameters.AddWithValue("@Type", type);
SqlCacheDependency dependency = new SqlCacheDependency(cmd);
try
{
if (dbconn.State == ConnectionState.Closed) { dbconn.Open(); }
SqlDataReader dr = cmd.ExecuteReader();
dt.Load(dr);
HttpContext.Current.Cache.Insert(cachekey, dt, dependency);
return dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
HttpContext.Current.Session["Sdt"] = dt;
if (dbconn.State == ConnectionState.Open) { dbconn.Close(); }
}
}
}
public DataTable GetTimezone(string activeflag)
{
string cachekey = "timezone";
if (HttpContext.Current.Cache[cachekey] != null)
{
DataTable cacheds = HttpContext.Current.Cache[cachekey] as DataTable;
return cacheds;
}
else
{
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand("spGetTimeZone", dbconn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@TIMEZONE_ACTIVE_FLAG", activeflag);
SqlCacheDependency dependency = new SqlCacheDependency(cmd);
try
{
if (dbconn.State == ConnectionState.Closed) { dbconn.Open(); }
SqlDataReader dr = cmd.ExecuteReader();
dt.Load(dr);
HttpContext.Current.Cache.Insert(cachekey, dt, dependency);
return dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbconn.State == ConnectionState.Open) { dbconn.Close(); }
}
}
}
public static void SetDataToCache(DataSet dataToCache, string cacheName, string tableNameInDatabase)
{
SqlCacheDependency sqlDependency = new SqlCacheDependency(Const.DATABASE_NAME, tableNameInDatabase);
HttpContext.Current.Cache.Insert(cacheName, dataToCache, sqlDependency, DateTime.Now.AddDays(1), Cache.NoSlidingExpiration);
}
public override SiteMapNode BuildSiteMap()
{
// Use a lock to make this method thread-safe
lock (siteMapLock)
{
// First, see if we already have constructed the
// rootNode. If so, return it...
if (root != null)
{
return(root);
}
// We need to build the site map!
// Clear out the current site map structure
base.Clear();
// Get the categories and products information from the database
ProductsBLL productsAPI = new ProductsBLL();
Northwind.ProductsDataTable products = productsAPI.GetProducts();
// Create the root SiteMapNode
root = new SiteMapNode(
this, "root", "~/SiteMapProvider/Default.aspx", "All Categories");
AddNode(root);
// Create SiteMapNodes for the categories and products
foreach (Northwind.ProductsRow product in products)
{
// Add a new category SiteMapNode, if needed
string categoryKey, categoryName;
bool createUrlForCategoryNode = true;
if (product.IsCategoryIDNull())
{
categoryKey = "Category:None";
categoryName = "None";
createUrlForCategoryNode = false;
}
else
{
categoryKey = string.Concat("Category:", product.CategoryID);
categoryName = product.CategoryName;
}
SiteMapNode categoryNode = FindSiteMapNodeFromKey(categoryKey);
// Add the category SiteMapNode if it does not exist
if (categoryNode == null)
{
string productsByCategoryUrl = string.Empty;
if (createUrlForCategoryNode)
{
productsByCategoryUrl =
"~/SiteMapProvider/ProductsByCategory.aspx?CategoryID="
+ product.CategoryID;
}
categoryNode = new SiteMapNode(
this, categoryKey, productsByCategoryUrl, categoryName);
AddNode(categoryNode, root);
}
// Add the product SiteMapNode
string productUrl =
"~/SiteMapProvider/ProductDetails.aspx?ProductID="
+ product.ProductID;
SiteMapNode productNode = new SiteMapNode(
this, string.Concat("Product:", product.ProductID),
productUrl, product.ProductName);
AddNode(productNode, categoryNode);
}
// Add a "dummy" item to the cache using a SqlCacheDependency
// on the Products and Categories tables
System.Web.Caching.SqlCacheDependency productsTableDependency =
new System.Web.Caching.SqlCacheDependency("NorthwindDB", "Products");
System.Web.Caching.SqlCacheDependency categoriesTableDependency =
new System.Web.Caching.SqlCacheDependency("NorthwindDB", "Categories");
// Create an AggregateCacheDependency
System.Web.Caching.AggregateCacheDependency aggregateDependencies =
new System.Web.Caching.AggregateCacheDependency();
aggregateDependencies.Add(productsTableDependency, categoriesTableDependency);
// Add the item to the cache specifying a callback function
HttpRuntime.Cache.Insert(
CacheDependencyKey, DateTime.Now, aggregateDependencies,
Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration,
CacheItemPriority.Normal,
new CacheItemRemovedCallback(OnSiteMapChanged));
// Finally, return the root node
return(root);
}
}
public static void SetCache(DataSet 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(Const.DATABASE_NAME, tableNameInDatabase[i]);
System.Web.Caching.AggregateCacheDependency agg = new System.Web.Caching.AggregateCacheDependency();
//agg.Add(sqlDep1, sqlDep2);
agg.Add(sqlDep);
HttpContext.Current.Cache.Insert(cacheName, dataCache, agg, DateTime.Now.AddDays(1), Cache.NoSlidingExpiration);
}
}//此方法是新加的
public static void Insert(string key, object value, System.Web.Caching.SqlCacheDependency dep)
{
Insert(key, value, dep, 0x21c0);
}
public void Set(string key, object data, int cacheTime)
{
SqlCacheDependency dep = new SqlCacheDependency(DbEntryName, TableName);
_cache.Add(key, data, dep, Cache.NoAbsoluteExpiration, TimeSpan.FromMinutes(cacheTime), System.Web.Caching.CacheItemPriority.Normal, null);
}
protected internal override IEnumerable ExecuteSelect(DataSourceSelectArguments arguments)
{
SqlCacheDependency dependency;
if (this.SelectCommand.Length == 0)
{
return null;
}
DbConnection connection = this._owner.CreateConnection(this._owner.ConnectionString);
if (connection == null)
{
throw new InvalidOperationException(System.Web.SR.GetString("SqlDataSourceView_CouldNotCreateConnection", new object[] { this._owner.ID }));
}
DataSourceCache cache = this._owner.Cache;
bool flag = (cache != null) && cache.Enabled;
string sortExpression = arguments.SortExpression;
if (this.CanPage)
{
arguments.AddSupportedCapabilities(DataSourceCapabilities.Page);
}
if (this.CanSort)
{
arguments.AddSupportedCapabilities(DataSourceCapabilities.Sort);
}
if (this.CanRetrieveTotalRowCount)
{
arguments.AddSupportedCapabilities(DataSourceCapabilities.RetrieveTotalRowCount);
}
if (flag)
{
if (this._owner.DataSourceMode != SqlDataSourceMode.DataSet)
{
throw new NotSupportedException(System.Web.SR.GetString("SqlDataSourceView_CacheNotSupported", new object[] { this._owner.ID }));
}
arguments.RaiseUnsupportedCapabilitiesError(this);
DataSet set = this._owner.LoadDataFromCache(0, -1) as DataSet;
if (set != null)
{
IOrderedDictionary parameterValues = this.FilterParameters.GetValues(this._context, this._owner);
if (this.FilterExpression.Length > 0)
{
SqlDataSourceFilteringEventArgs args = new SqlDataSourceFilteringEventArgs(parameterValues);
this.OnFiltering(args);
if (args.Cancel)
{
return null;
}
}
return FilteredDataSetHelper.CreateFilteredDataView(set.Tables[0], sortExpression, this.FilterExpression, parameterValues);
}
}
DbCommand command = this._owner.CreateCommand(this.SelectCommand, connection);
this.InitializeParameters(command, this.SelectParameters, null);
command.CommandType = GetCommandType(this.SelectCommandType);
SqlDataSourceSelectingEventArgs e = new SqlDataSourceSelectingEventArgs(command, arguments);
this.OnSelecting(e);
if (e.Cancel)
{
return null;
}
string sortParameterName = this.SortParameterName;
if (sortParameterName.Length > 0)
{
if (command.CommandType != CommandType.StoredProcedure)
{
throw new NotSupportedException(System.Web.SR.GetString("SqlDataSourceView_SortParameterRequiresStoredProcedure", new object[] { this._owner.ID }));
}
command.Parameters.Add(this._owner.CreateParameter(this.ParameterPrefix + sortParameterName, sortExpression));
arguments.SortExpression = string.Empty;
}
arguments.RaiseUnsupportedCapabilitiesError(this);
sortExpression = arguments.SortExpression;
if (this.CancelSelectOnNullParameter)
{
int count = command.Parameters.Count;
for (int i = 0; i < count; i++)
{
DbParameter parameter = command.Parameters[i];
if (((parameter != null) && (parameter.Value == null)) && ((parameter.Direction == ParameterDirection.Input) || (parameter.Direction == ParameterDirection.InputOutput)))
{
return null;
}
}
}
this.ReplaceNullValues(command);
IEnumerable enumerable = null;
switch (this._owner.DataSourceMode)
{
case SqlDataSourceMode.DataReader:
{
if (this.FilterExpression.Length > 0)
{
throw new NotSupportedException(System.Web.SR.GetString("SqlDataSourceView_FilterNotSupported", new object[] { this._owner.ID }));
}
if (sortExpression.Length > 0)
{
throw new NotSupportedException(System.Web.SR.GetString("SqlDataSourceView_SortNotSupported", new object[] { this._owner.ID }));
}
bool flag4 = false;
try
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
enumerable = command.ExecuteReader(CommandBehavior.CloseConnection);
flag4 = true;
SqlDataSourceStatusEventArgs args6 = new SqlDataSourceStatusEventArgs(command, 0, null);
this.OnSelected(args6);
}
catch (Exception exception2)
{
bool flag5;
if (!flag4)
{
SqlDataSourceStatusEventArgs args7 = new SqlDataSourceStatusEventArgs(command, 0, exception2);
this.OnSelected(args7);
if (!args7.ExceptionHandled)
{
throw;
}
return enumerable;
}
exception2 = this.BuildCustomException(exception2, DataSourceOperation.Select, command, out flag5);
if (flag5)
{
throw exception2;
}
throw;
}
return enumerable;
}
case SqlDataSourceMode.DataSet:
dependency = null;
if (flag && (cache is SqlDataSourceCache))
{
SqlDataSourceCache cache2 = (SqlDataSourceCache) cache;
if (string.Equals(cache2.SqlCacheDependency, "CommandNotification", StringComparison.OrdinalIgnoreCase))
{
if (!(command is SqlCommand))
{
throw new InvalidOperationException(System.Web.SR.GetString("SqlDataSourceView_CommandNotificationNotSupported", new object[] { this._owner.ID }));
}
dependency = new SqlCacheDependency((SqlCommand) command);
break;
}
}
break;
default:
return enumerable;
}
DbDataAdapter adapter = this._owner.CreateDataAdapter(command);
DataSet dataSet = new DataSet();
int affectedRows = 0;
bool flag2 = false;
try
{
affectedRows = adapter.Fill(dataSet, base.Name);
flag2 = true;
SqlDataSourceStatusEventArgs args3 = new SqlDataSourceStatusEventArgs(command, affectedRows, null);
this.OnSelected(args3);
}
catch (Exception exception)
{
if (flag2)
{
bool flag3;
exception = this.BuildCustomException(exception, DataSourceOperation.Select, command, out flag3);
if (flag3)
{
throw exception;
}
throw;
}
SqlDataSourceStatusEventArgs args4 = new SqlDataSourceStatusEventArgs(command, affectedRows, exception);
this.OnSelected(args4);
if (!args4.ExceptionHandled)
{
throw;
}
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
DataTable table = (dataSet.Tables.Count > 0) ? dataSet.Tables[0] : null;
if (flag && (table != null))
{
this._owner.SaveDataToCache(0, -1, dataSet, dependency);
}
if (table == null)
{
return enumerable;
}
IOrderedDictionary values = this.FilterParameters.GetValues(this._context, this._owner);
if (this.FilterExpression.Length > 0)
{
SqlDataSourceFilteringEventArgs args5 = new SqlDataSourceFilteringEventArgs(values);
this.OnFiltering(args5);
if (args5.Cancel)
{
return null;
}
}
return FilteredDataSetHelper.CreateFilteredDataView(table, sortExpression, this.FilterExpression, values);
}