/// <summary>
/// Returns true if file is expired
/// </summary>
public static bool FileExpired(string Key, ref SQLOptions o)
{
string strXMLXPath = "/SQLHelper_FileExpirations/File";
string strXMLForeignKeyAttribute = "Key";
System.Xml.XmlDocument doc = GetFileExpirations(ref o);
o.WriteToLog("searching expiration file for this value..." + Key);
General.Debug.Trace("searching expiration file for this value..." + Key);
System.Xml.XmlNode node = doc.SelectSingleNode(strXMLXPath + "[@" + strXMLForeignKeyAttribute + "='" + Key + "']");
if (node == null)
{
o.WriteToLog("record not found... " + Key);
General.Debug.Trace("record not found... " + Key);
return(true);
}
else
{
DateTime Expiration = Convert.ToDateTime(node.Attributes["ExpirationDate"].Value);
if (DateTime.Now >= Expiration)
{
o.WriteToLog("file is expired... " + Key);
DeleteFileExpiration(Key, ref o);
return(true);
}
else
{
return(false);
}
}
}
/// <summary>
/// Gets all expiration records
/// </summary>
private static System.Xml.XmlDocument GetFileExpirations(ref SQLOptions o)
{
System.Xml.XmlDocument doc;
object obj;
o.WriteToLog("attempt loading cache_expirations from memory");
obj = SQLMemoryCache.GetFromMemoryCache("cache_expirations", ref o);
if (obj == null)
{
o.WriteToLog("attempt loading cache_expirations from file");
string FileName = GetCacheFilePath("cache_expirations");
if (System.IO.File.Exists(FileName))
{
doc = new System.Xml.XmlDocument();
doc.Load(FileName);
o.WriteToLog("saving cache_expirations to memory");
SQLMemoryCache.AddToMemoryCache("cache_expirations", doc, ref o, DateTime.Now.AddDays(30), false);
return(doc);
}
else
{
o.WriteToLog("creating file and saving cache_expirations to memory");
doc = CreateFileExpirationsFile(null, ref o);
SQLMemoryCache.AddToMemoryCache("cache_expirations", doc, ref o, DateTime.Now.AddDays(30), false);
return(doc);
}
}
else
{
return((System.Xml.XmlDocument)obj);
}
}
/// <summary>
/// Adds expiration record to the SQLFileCache
/// </summary>
public static void AddFileExpiration(string Key, ref SQLOptions o)
{
string strXMLXPath = "/SQLHelper_FileExpirations/File";
string strXMLForeignKeyAttribute = "Key";
System.Xml.XmlDocument doc = GetFileExpirations(ref o);
o.WriteToLog("updating expiration file for this value..." + Key);
General.Debug.Trace("updating expiration file for this value..." + Key);
System.Xml.XmlNode node = doc.SelectSingleNode(strXMLXPath + "[@" + strXMLForeignKeyAttribute + "='" + Key + "']");
if (node == null)
{
o.WriteToLog("record not found... creating..." + Key);
General.Debug.Trace("record not found... creating..." + Key);
node = doc.CreateNode(System.Xml.XmlNodeType.Element, "File", "");
node.Attributes.Append(doc.CreateAttribute("Key"));
node.Attributes["Key"].Value = Key;
node.Attributes.Append(doc.CreateAttribute("ExpirationDate"));
node.Attributes["ExpirationDate"].Value = o.Expiration.ToString();
doc.DocumentElement.AppendChild(node);
}
else
{
o.WriteToLog("record found... updating..." + Key);
General.Debug.Trace("record found... updating..." + Key);
node.Attributes["ExpirationDate"].Value = o.Expiration.ToString();
}
SaveFileExpirations(doc, ref o);
}
/// <summary>
/// Adds expiration record to the SQLFileCache
/// </summary>
public static void AddFileExpiration(string Key,ref SQLOptions o)
{
string strXMLXPath = "/SQLHelper_FileExpirations/File";
string strXMLForeignKeyAttribute = "Key";
System.Xml.XmlDocument doc = GetFileExpirations(ref o);
o.WriteToLog("updating expiration file for this value..." +Key);
General.Debug.Trace("updating expiration file for this value..." +Key);
System.Xml.XmlNode node = doc.SelectSingleNode(strXMLXPath + "[@" + strXMLForeignKeyAttribute + "='" + Key + "']");
if(node == null)
{
o.WriteToLog("record not found... creating..." +Key);
General.Debug.Trace("record not found... creating..." +Key);
node = doc.CreateNode(System.Xml.XmlNodeType.Element,"File","");
node.Attributes.Append(doc.CreateAttribute("Key"));
node.Attributes["Key"].Value = Key;
node.Attributes.Append(doc.CreateAttribute("ExpirationDate"));
node.Attributes["ExpirationDate"].Value = o.Expiration.ToString();
doc.DocumentElement.AppendChild(node);
}
else
{
o.WriteToLog("record found... updating..." +Key);
General.Debug.Trace("record found... updating..." +Key);
node.Attributes["ExpirationDate"].Value = o.Expiration.ToString();
}
SaveFileExpirations(doc, ref o);
}
/// <summary>
/// Get object from SQLFileCache
/// </summary>
public static object GetFromFileCache(string Key, ref SQLOptions o)
{
o.WriteToLog("searching file cache..." + GetCacheFilePath(Key));
General.Debug.Trace("attempting file cache retrieval..." + GetCacheFilePath(Key));
if (System.IO.File.Exists(GetCacheFilePath(Key)))
{
if (!FileExpired(Key, ref o))
{
o.WriteToLog("found");
DataSet ds = new DataSet();
ds.ReadXml(GetCacheFilePath(Key));
return(ds);
}
else
{
o.WriteToLog("file is expired..." + GetCacheFilePath(Key));
General.Debug.Trace("file is expired..." + GetCacheFilePath(Key));
DeleteCacheFile(Key);
return(null);
}
}
else
{
o.WriteToLog("file does not exist..." + GetCacheFilePath(Key));
General.Debug.Trace("file does not exist..." + GetCacheFilePath(Key));
return(null);
}
}
private static System.Xml.XmlDocument CreateFileExpirationsFile(System.Xml.XmlDocument doc, ref SQLOptions o)
{
string FileName = GetCacheFilePath("cache_expirations");
if (doc == null)
{
doc = new System.Xml.XmlDocument();
System.Xml.XmlDeclaration dec = doc.CreateXmlDeclaration("1.0", "ASCII", "yes");
doc.AppendChild(dec);
System.Xml.XmlElement ele = doc.CreateElement("SQLHelper_FileExpirations");
doc.AppendChild(ele);
}
//General.IO.IOTools.QueueWriteFile(doc,FileName, "cache_expirations", new General.IO.IOTools.QueueWriteFileCompletedCallback(CreateFileExpirationsFileCallback));
try
{
o.WriteToLog("saving cache_expirations.xml");
doc.Save(FileName);
}
catch (System.UnauthorizedAccessException)
{
o.WriteToLog("failure to save cache_expirations.xml");
//DO NOTHING
}
return(doc);
}
/// <summary>
/// Adds an object to the SQLCache
/// </summary>
public static void AddToCache(string Key, object Obj, ref SQLOptions o)
{
if (System.Web.HttpContext.Current == null)
{
throw new Exception("Caching is not available outside of web context");
}
//if(log == null)
//log = new TextLog();
if (o.DoFileCache)
{
try
{
SQLFileCache.AddToFileCache(Key, Obj, ref o);
}
catch (Exception ex)
{
o.WriteToLog("Error trying to save to SQL FileCache: " + ex.Message);
General.Debugging.Report.SendError("Error trying to save to SQL FileCache", ex);
}
}
if (o.DoMemoryCache)
{
SQLMemoryCache.AddToMemoryCache(Key, Obj, ref o);
}
}
/// <summary>
/// Retrieves an object from the SQLCache
/// </summary>
public static object GetFromCache(string Key, ref SQLOptions o)
{
object obj = null;
if (System.Web.HttpContext.Current == null)
{
throw new Exception("Caching is not available outside of web context");
}
if (o.DoMemoryCache)
{
obj = SQLMemoryCache.GetFromMemoryCache(Key, ref o);
}
if (obj != null)
{
//General.Debug.Trace("Got from memory cache");
o.WriteToLog("Retrieved from memory cache");
}
if (obj == null && o.DoFileCache)
{
try
{
obj = SQLFileCache.GetFromFileCache(Key, ref o);
if (obj != null && o.DoMemoryCache)
{
SQLMemoryCache.AddToMemoryCache(Key, obj, ref o);
}
}
catch (Exception ex)
{
obj = null;
o.WriteToLog("Error trying to load SQL FileCache: " + ex.Message);
General.Debugging.Report.SendError("Error trying to load SQL FileCache", ex);
}
if (obj != null)
{
General.Debug.Trace("Got from file cache");
o.WriteToLog("Retrieved from file cache");
}
}
return(obj);
}
/// <summary>
/// Get object from SQLFileCache
/// </summary>
public static void AddToFileCache(string Key, object Obj,ref SQLOptions o)
{
General.Debug.Trace("attempting file cache save..." +GetCacheFilePath(Key));
o.WriteToLog("attempting file cache save..." +GetCacheFilePath(Key));
DataSet ds;
try
{
ds = (DataSet) Obj;
}
catch
{
General.Debug.Trace("not a DataSet object..." +Key);
o.WriteToLog("not a dataset object");
//Not a DataSet object
return;
}
ds.WriteXml(GetCacheFilePath(Key));
AddFileExpiration(Key,ref o);
}
/// <summary>
/// Get object from SQLFileCache
/// </summary>
public static void AddToFileCache(string Key, object Obj, ref SQLOptions o)
{
General.Debug.Trace("attempting file cache save..." + GetCacheFilePath(Key));
o.WriteToLog("attempting file cache save..." + GetCacheFilePath(Key));
DataSet ds;
try
{
ds = (DataSet)Obj;
}
catch
{
General.Debug.Trace("not a DataSet object..." + Key);
o.WriteToLog("not a dataset object");
//Not a DataSet object
return;
}
ds.WriteXml(GetCacheFilePath(Key));
AddFileExpiration(Key, ref o);
}
/// <summary>
/// Removes expiration record from the SQLFileCache
/// </summary>
public static void DeleteFileExpiration(string Key, ref SQLOptions o)
{
string strXMLXPath = "/SQLHelper_FileExpirations/File";
string strXMLForeignKeyAttribute = "Key";
System.Xml.XmlDocument doc = GetFileExpirations(ref o);
o.WriteToLog("deleting expiration file for this value..." + Key);
General.Debug.Trace("deleting expiration file for this value..." + Key);
System.Xml.XmlNode node = doc.SelectSingleNode(strXMLXPath + "[@" + strXMLForeignKeyAttribute + "='" + Key + "']");
if (node == null)
{
//DO NOTHING
}
else
{
o.WriteToLog("record found... deleting..." + Key);
General.Debug.Trace("record found... deleting..." + Key);
doc.DocumentElement.RemoveChild(node);
}
SaveFileExpirations(doc, ref o);
}
/// <summary>
/// Retrieves an object from the SQLCache
/// </summary>
public static object GetFromCache(string Key, ref SQLOptions o)
{
object obj = null;
if(System.Web.HttpContext.Current == null)
throw new Exception("Caching is not available outside of web context");
if(o.DoMemoryCache)
obj = SQLMemoryCache.GetFromMemoryCache(Key, ref o);
if(obj != null)
{
//General.Debug.Trace("Got from memory cache");
o.WriteToLog("Retrieved from memory cache");
}
if(obj == null && o.DoFileCache)
{
try
{
obj = SQLFileCache.GetFromFileCache(Key, ref o);
if(obj != null && o.DoMemoryCache)
SQLMemoryCache.AddToMemoryCache(Key,obj,ref o);
}
catch(Exception ex)
{
obj = null;
o.WriteToLog("Error trying to load SQL FileCache: " + ex.Message);
General.Debugging.Report.SendError("Error trying to load SQL FileCache",ex);
}
if(obj != null)
{
General.Debug.Trace("Got from file cache");
o.WriteToLog("Retrieved from file cache");
}
}
return obj;
}
/// <summary>
/// Add to SQL Memory Cache
/// </summary>
public static void AddToMemoryCache(string Key, object Obj, ref SQLOptions o)
{
if (System.Web.HttpContext.Current == null)
{
throw new Exception("Caching is not available outside of web context");
}
System.Web.Caching.Cache GlobalCache = System.Web.HttpContext.Current.Cache;
o.WriteToLog("adding object to memory cache... " + Key + "...Expire=" + o.Expiration.ToString() + "...FileDependency=" + o.DoFileCache);
System.Web.Caching.CacheDependency dep;
if (o.DoFileCache)
{
dep = new CacheDependency(SQLFileCache.GetCacheFilePath(Key));
}
else
{
dep = null;
}
CacheItemRemovedCallback OnCacheRemove = new CacheItemRemovedCallback(OnCacheRemoveCallback);
GlobalCache.Add("SQLHelper:" + Key, Obj, dep, o.Expiration, Cache.NoSlidingExpiration, CacheItemPriority.Normal, OnCacheRemove);
_intCacheCount++;
}
/// <summary>
/// Adds an object to the SQLCache
/// </summary>
public static void AddToCache(string Key, object Obj, ref SQLOptions o)
{
if(System.Web.HttpContext.Current == null)
throw new Exception("Caching is not available outside of web context");
//if(log == null)
//log = new TextLog();
if(o.DoFileCache)
{
try
{
SQLFileCache.AddToFileCache(Key,Obj,ref o);
}
catch(Exception ex)
{
o.WriteToLog("Error trying to save to SQL FileCache: " + ex.Message);
General.Debugging.Report.SendError("Error trying to save to SQL FileCache",ex);
}
}
if(o.DoMemoryCache)
SQLMemoryCache.AddToMemoryCache(Key,Obj,ref o);
}
/// <summary>
/// Execute a SqlCommand (that returns a resultset)
/// using the provided Command Object, and the provided SqlOptions.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(cmd, ref o);
/// </remarks>
/// <param name="cmd">A valid SqlCommand</param>
/// <param name="o">A valid reference to a SqlOptions object</param>
/// <returns>A dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(SqlCommand cmd, ref SQLOptions o)
{
#region CacheOverrides
bool boolFileCacheEnabled;
try {boolFileCacheEnabled = Convert.ToBoolean(General.Configuration.GlobalConfiguration.GlobalSettings["sqlhelper_do_file_cache"]);}
catch {boolFileCacheEnabled = true;}
if(!boolFileCacheEnabled)
o.DoFileCache = false;
bool boolMemoryCacheEnabled;
try {boolMemoryCacheEnabled = Convert.ToBoolean(General.Configuration.GlobalConfiguration.GlobalSettings["sqlhelper_do_memory_cache"]);}
catch {boolMemoryCacheEnabled = true;}
if(!boolMemoryCacheEnabled)
o.DoMemoryCache = false;
#endregion
//General.Debug.Trace("running GetDataset on " +cmd.CommandText+ "...DoMemoryCache = " +o.DoMemoryCache + ", DoFileCache = " + o.DoFileCache);
string strQueryHash = GetQueryHashCode(cmd);
if(o.DoMemoryCache || o.DoFileCache)
{
if(System.Web.HttpContext.Current == null)
throw new Exception("Caching is not available outside of web context");
else
{
object objCache = SQLCache.GetFromCache(strQueryHash, ref o);
if(objCache != null) {
o.WriteToLog("pulling from cache..." + strQueryHash);
General.Debug.Write("Getting From Cache: " + GetQueryString(cmd));
if(o.EmailLog)
o.ActivityLog.Send();
return (DataSet) objCache;
} else {
o.WriteToLog("pulling from db..." + cmd.CommandText);
General.Debug.Write("No Cache Available: " + GetQueryString(cmd));
DataSet ds = GetDatasetNoCache(cmd, ref o);
SQLCache.AddToCache(strQueryHash, ds, ref o);
if(o.EmailLog)
o.ActivityLog.Send();
return ds;
}
}
}
else
{
string strQueryString = GetQueryString(cmd);
LastProcedureCall = strQueryString;
//General.Debug.Trace("running GetDataset on " + strQueryString);
//General.Debug.JQueryDebugWrite(strQueryString);
return GetDatasetNoCache(cmd, ref o);
}
}
/// <summary>
/// Returns true if file is expired
/// </summary>
public static bool FileExpired(string Key, ref SQLOptions o)
{
string strXMLXPath = "/SQLHelper_FileExpirations/File";
string strXMLForeignKeyAttribute = "Key";
System.Xml.XmlDocument doc = GetFileExpirations(ref o);
o.WriteToLog("searching expiration file for this value..." +Key);
General.Debug.Trace("searching expiration file for this value..." +Key);
System.Xml.XmlNode node = doc.SelectSingleNode(strXMLXPath + "[@" + strXMLForeignKeyAttribute + "='" + Key + "']");
if(node == null)
{
o.WriteToLog("record not found... " +Key);
General.Debug.Trace("record not found... " +Key);
return true;
}
else
{
DateTime Expiration = Convert.ToDateTime(node.Attributes["ExpirationDate"].Value);
if(DateTime.Now >= Expiration)
{
o.WriteToLog("file is expired... " +Key);
DeleteFileExpiration(Key, ref o);
return true;
}
else
return false;
}
}
/// <summary>
/// Get object from SQLFileCache
/// </summary>
public static object GetFromFileCache(string Key,ref SQLOptions o)
{
o.WriteToLog("searching file cache..." + GetCacheFilePath(Key));
General.Debug.Trace("attempting file cache retrieval..." +GetCacheFilePath(Key));
if(System.IO.File.Exists(GetCacheFilePath(Key)))
{
if(!FileExpired(Key,ref o))
{
o.WriteToLog("found");
DataSet ds = new DataSet();
ds.ReadXml(GetCacheFilePath(Key));
return ds;
}
else
{
o.WriteToLog("file is expired..." +GetCacheFilePath(Key));
General.Debug.Trace("file is expired..." +GetCacheFilePath(Key));
DeleteCacheFile(Key);
return null;
}
}
else
{
o.WriteToLog("file does not exist..." +GetCacheFilePath(Key));
General.Debug.Trace("file does not exist..." +GetCacheFilePath(Key));
return null;
}
}
private static System.Xml.XmlDocument CreateFileExpirationsFile(System.Xml.XmlDocument doc, ref SQLOptions o)
{
string FileName = GetCacheFilePath("cache_expirations");
if(doc == null)
{
doc = new System.Xml.XmlDocument();
System.Xml.XmlDeclaration dec = doc.CreateXmlDeclaration("1.0","ASCII","yes");
doc.AppendChild(dec);
System.Xml.XmlElement ele = doc.CreateElement("SQLHelper_FileExpirations");
doc.AppendChild(ele);
}
//General.IO.IOTools.QueueWriteFile(doc,FileName, "cache_expirations", new General.IO.IOTools.QueueWriteFileCompletedCallback(CreateFileExpirationsFileCallback));
try
{
o.WriteToLog("saving cache_expirations.xml");
doc.Save(FileName);
}
catch(System.UnauthorizedAccessException)
{
o.WriteToLog("failure to save cache_expirations.xml");
//DO NOTHING
}
return doc;
}
/// <summary>
/// Gets all expiration records
/// </summary>
private static System.Xml.XmlDocument GetFileExpirations(ref SQLOptions o)
{
System.Xml.XmlDocument doc;
object obj;
o.WriteToLog("attempt loading cache_expirations from memory");
obj = SQLMemoryCache.GetFromMemoryCache("cache_expirations",ref o);
if(obj == null)
{
o.WriteToLog("attempt loading cache_expirations from file");
string FileName = GetCacheFilePath("cache_expirations");
if(System.IO.File.Exists(FileName))
{
doc = new System.Xml.XmlDocument();
doc.Load(FileName);
o.WriteToLog("saving cache_expirations to memory");
SQLMemoryCache.AddToMemoryCache("cache_expirations", doc,ref o,DateTime.Now.AddDays(30),false);
return doc;
}
else
{
o.WriteToLog("creating file and saving cache_expirations to memory");
doc = CreateFileExpirationsFile(null,ref o);
SQLMemoryCache.AddToMemoryCache("cache_expirations", doc,ref o, DateTime.Now.AddDays(30),false);
return doc;
}
}
else
{
return (System.Xml.XmlDocument) obj;
}
}
/// <summary>
/// Get from SQL Memory Cache
/// </summary>
public static object GetFromMemoryCache(string Key, ref SQLOptions o)
{
o.WriteToLog("searching memory cache..." + Key);
System.Web.Caching.Cache GlobalCache = System.Web.HttpContext.Current.Cache;
return(GlobalCache.Get("SQLHelper:" + Key));
}
/// <summary>
/// Add to SQL Memory Cache
/// </summary>
public static void AddToMemoryCache(string Key, object Obj, ref SQLOptions o)
{
if(System.Web.HttpContext.Current == null)
throw new Exception("Caching is not available outside of web context");
System.Web.Caching.Cache GlobalCache = System.Web.HttpContext.Current.Cache;
o.WriteToLog("adding object to memory cache... " + Key + "...Expire=" + o.Expiration.ToString() + "...FileDependency=" + o.DoFileCache);
System.Web.Caching.CacheDependency dep;
if(o.DoFileCache)
dep = new CacheDependency(SQLFileCache.GetCacheFilePath(Key));
else
dep = null;
CacheItemRemovedCallback OnCacheRemove = new CacheItemRemovedCallback(OnCacheRemoveCallback);
GlobalCache.Add("SQLHelper:" + Key, Obj, dep, o.Expiration, Cache.NoSlidingExpiration, CacheItemPriority.Normal, OnCacheRemove);
_intCacheCount++;
}
/// <summary>
/// Removes expiration record from the SQLFileCache
/// </summary>
public static void DeleteFileExpiration(string Key, ref SQLOptions o)
{
string strXMLXPath = "/SQLHelper_FileExpirations/File";
string strXMLForeignKeyAttribute = "Key";
System.Xml.XmlDocument doc = GetFileExpirations(ref o);
o.WriteToLog("deleting expiration file for this value..." +Key);
General.Debug.Trace("deleting expiration file for this value..." +Key);
System.Xml.XmlNode node = doc.SelectSingleNode(strXMLXPath + "[@" + strXMLForeignKeyAttribute + "='" + Key + "']");
if(node == null)
{
//DO NOTHING
}
else
{
o.WriteToLog("record found... deleting..." +Key);
General.Debug.Trace("record found... deleting..." +Key);
doc.DocumentElement.RemoveChild(node);
}
SaveFileExpirations(doc,ref o);
}
/// <summary>
/// Get from SQL Memory Cache
/// </summary>
public static object GetFromMemoryCache(string Key, ref SQLOptions o)
{
o.WriteToLog("searching memory cache..." + Key);
System.Web.Caching.Cache GlobalCache = System.Web.HttpContext.Current.Cache;
return(GlobalCache.Get("SQLHelper:" + Key));
}