/// <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>
/// 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>
/// 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>
/// 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 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);
}
}
/// <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>
/// Add to SQL Memory Cache
/// </summary>
public static void AddToMemoryCache(string Key, object Obj, ref SQLOptions o, DateTime ExpirationOverride, bool FileDependency)
{
DateTime dateTempExpire = o.Expiration;
bool boolTempFileCache = o.DoFileCache;
o.Expiration = ExpirationOverride;
o.DoFileCache = FileDependency;
AddToMemoryCache(Key,Obj,ref o);
o.DoFileCache = boolTempFileCache;
o.Expiration = dateTempExpire;
}
/// <summary>
/// Add to SQL Memory Cache
/// </summary>
public static void AddToMemoryCache(string Key, object Obj, ref SQLOptions o, DateTime ExpirationOverride, bool FileDependency)
{
DateTime dateTempExpire = o.Expiration;
bool boolTempFileCache = o.DoFileCache;
o.Expiration = ExpirationOverride;
o.DoFileCache = FileDependency;
AddToMemoryCache(Key, Obj, ref o);
o.DoFileCache = boolTempFileCache;
o.Expiration = dateTempExpire;
}
/// <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>
/// 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);
}
}
public static List<string> ExecuteList(SqlCommand cmd, bool mustCloseConnection)
{
#region Manage Connection
if (cmd == null) throw new ArgumentNullException("cmd");
if (cmd.Connection == null)
{
SQLOptions o = new SQLOptions();
cmd.Connection = DBConnection.GetOpenConnection(o.ConnectionString);
}
else if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
#endregion
// Create the DataAdapter & DataSet
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
#region Execute
DataSet ds = new DataSet();
// Fill the DataSet using default values for DataTable names, etc
da.Fill(ds);
#endregion
#region Manage Connection
if (mustCloseConnection)
cmd.Connection.Close();
#endregion
#region Populate List
List<string> objList = new List<string>();
foreach (DataRow row in ds.Tables[0].Rows)
objList.Add(row[0].ToString());
#endregion
return objList;
}
}
private static DataSet GetDatasetNoCache(SqlCommand cmd, ref SQLOptions o)
{
#region Debugging Clock
StartDebuggingClock();
#endregion
if( cmd == null ) throw new ArgumentNullException( "cmd" );
#region InsertTransactionHistory
SQLCache.InsertTransactionHistory(cmd);
#endregion
// Create the DataAdapter & DataSet
using( SqlDataAdapter da = new SqlDataAdapter(cmd) )
{
DataSet ds = new DataSet();
// Fill the DataSet using default values for DataTable names, etc
try
{
if(cmd.Connection == null)
cmd.Connection = DBConnection.GetOpenConnection(o.ConnectionString);
da.Fill(ds);
if (o.CloseConnection)
cmd.Connection.Close();
}
catch(SqlException ex)
{
StopDebuggingClock();
if ((int)ex.Class <= 16)
{
string strErr = "A SQL Server error occurred while trying to execute the command: " + cmd.CommandText + "\n";
strErr += ex.Message + "\n";
strErr += GetQueryString(cmd) + "\n";
throw new Exception(strErr, ex);
}
else if (ex.Message.ToLower().Contains("a transport-level error has occurred"))
{
if (System.Web.HttpContext.Current != null)
{
System.Web.HttpContext.Current.Session.Clear();
System.Web.HttpContext.Current.Application.Clear();
System.Web.HttpContext.Current.Response.Cookies.Clear();
System.Web.HttpContext.Current.Session["ClearLogin"] = true;
System.Web.HttpContext.Current.Response.Redirect(General.Web.WebTools.GetRequestedPage(), true);
}
else
{
throw;
//throw new Exception(ex.Message + " : " + o.ConnectionString, ex);
}
}
else
throw;
//throw new Exception(ex.Message + " : " + o.ConnectionString, ex);
}
catch(System.Exception ex)
{
StopDebuggingClock();
string strErr = "An error occured while communicating with SQL Server (" + cmd.CommandText + ").\n";
strErr += ex.Message + "\n";
strErr += GetQueryString(cmd);
throw new Exception(strErr,ex);
}
// Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
#region Debugging Clock
TimeSpan time = StopDebuggingClock();
General.Debug.Write(GetQueryString(cmd) + " (" + time.TotalMilliseconds + "ms)");
#endregion
// Return the dataset
return ds;
}
}
/// <summary>
/// Executes a SqlCommand and attaches a new connection if one hasn't been attached.
/// </summary>
/// <param name="cmd">SQLCommand - A valid command object with or without a Connection</param>
/// <returns>int - Represents the number of rows effected</returns>
public static int ExecuteNonQuery(SqlCommand cmd)
{
if (cmd.Connection == null) {
SQLOptions o = new SQLOptions();
cmd.Connection = DBConnection.GetOpenConnection(o.ConnectionString);
}
return ExecuteNonQuery(cmd, true);
}
/// <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;
}
}
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>
/// Saves expiration records
/// </summary>
public static void SaveFileExpirations(System.Xml.XmlDocument doc, ref SQLOptions o)
{
SQLMemoryCache.AddToMemoryCache("cache_expirations", doc, ref o, DateTime.Now.AddDays(30), false);
CreateFileExpirationsFile(doc, 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>
/// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(conn, cmd);
/// </remarks>
/// <param name="cmd">A valid SqlCommand object</param>
/// <param name="mustCloseConnection">Will the connection close upon completion</param>
/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(SqlCommand cmd, bool mustCloseConnection)
{
#region Start Debugging Clock
StartDebuggingClock();
#endregion
#region Manage Connection
if ( cmd == null ) throw new ArgumentNullException( "cmd" );
if (cmd.Connection == null)
{
SQLOptions o = new SQLOptions();
cmd.Connection = DBConnection.GetOpenConnection(o.ConnectionString);
}
else if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
#endregion
#region InsertTransactionHistory
SQLCache.InsertTransactionHistory(cmd);
#endregion
#region Execute
// Execute the command & return the results
object retval = cmd.ExecuteScalar();
// Detach the SqlParameters from the command object, so they can be used again
// Why do I need to do this... it just screws up Output Parameters
//cmd.Parameters.Clear();
#endregion
#region Manage Connection
if ( mustCloseConnection )
cmd.Connection.Close();
#endregion
#region Stop Debugging Clock
TimeSpan time = StopDebuggingClock();
//General.Debug.Trace("running ExecuteScalar on " + GetQueryString(cmd));
General.Debug.JQueryDebugWrite(GetQueryString(cmd) + " (" + time.TotalMilliseconds + "ms)");
#endregion
return retval;
}
/// <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>
/// 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;
}
}
/// <summary>
/// Saves expiration records
/// </summary>
public static void SaveFileExpirations(System.Xml.XmlDocument doc, ref SQLOptions o)
{
SQLMemoryCache.AddToMemoryCache("cache_expirations", doc, ref o, DateTime.Now.AddDays(30),false);
CreateFileExpirationsFile(doc, ref o);
}
/// <summary>
/// Execute a SqlCommand (that returns a resultset)
/// using the provided Command Object, and all default options.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(cmd);
/// </remarks>
/// <param name="cmd">A valid SqlCommand </param>
/// <returns>A dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(SqlCommand cmd)
{
SQLOptions o = new SQLOptions(cmd.Connection);
return ExecuteDataset(cmd,ref o); //GET DEFAULT OPTIONS
}
/// <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>
/// Executes a SqlCommand and attaches a new connection if one hasn't been attached.
/// </summary>
/// <param name="cmd">SQLCommand - A valid command object with or without a Connection</param>
/// <returns>object - An object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(SqlCommand cmd)
{
if (cmd.Connection == null)
{
SQLOptions o = new SQLOptions();
cmd.Connection = DBConnection.GetOpenConnection(o.ConnectionString);
}
return ExecuteScalar(cmd, true);
}
/// <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>
/// Execute a SqlCommand (that returns the Identity) against the specified SqlConnection
/// using the SqlCommand.
/// </summary>
/// <remarks>
/// e.g.:
/// long result = ExecuteReturnIdentity(cmd);
/// </remarks>
/// <param name="cmd">A valid SqlCommand</param>
/// <returns>A long representing Return Select_Identity()</returns>
public static long ExecuteReturnIdentity(SqlCommand cmd)
{
long retval;
if (cmd.Connection == null)
{
SQLOptions o = new SQLOptions();
cmd.Connection = DBConnection.GetOpenConnection(o.ConnectionString);
}
if( cmd == null ) throw new ArgumentNullException( "cmd" );
// Finally, execute the command
try
{
cmd.Parameters.AddWithValue("@return", SqlDbType.Int);
cmd.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
retval = Convert.ToInt64(cmd.Parameters["@return"].Value.ToString()) ;
}
catch(SqlException ex)
{
string strErr = "A SQL Server error occurred while trying to execute the command (" + cmd.CommandText + ").\n";
strErr += GetQueryString(cmd);
throw new Exception(strErr,ex);
}
catch(System.Exception ex)
{
string strErr = "An error occured while communicating with SQL Server (" + cmd.CommandText + ").\n";
strErr += GetQueryString(cmd);
throw new Exception(strErr,ex);
}
// Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
cmd.Connection.Close();
return retval;
}
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;
}
