/// <summary>
/// Executes a stored procedure and returns the result as a List<TSource>
/// </summary>
/// <typeparam name="TSource">The Model for the returned Dataset</typeparam>
/// <param name="storedProcedure">Name of the stored procedure</param>
/// <param name="replaceResourceKeys">If true replaces all the tokens {#ResourceKey} with the relative resource value from the files under the Resources folder</param>
/// <param name="overwriteResultFunction">Delegate function used to overwrite/manipulate the data returned from the Stored Procedure</param>
/// <param name="commandTimeout">Sets the wait time before terminating the attempt to execute a command and generating an error. Use 0 to set infinite timeout.</param>
/// <param name="args">Any number of parameter name/value pairs and/or SQLParameter arguments</param>
/// <returns>Results as a List<TSource></returns>
public static List <TSource> ExecListProc <TSource>(string storedProcedure, bool replaceResourceKeys = true, Func <List <TSource>, List <TSource> > overwriteResultFunction = null, int commandTimeout = 30, params object[] args)
{
List <TSource> result = null;
using (AdoHelper2 db = new AdoHelper2(commandTimeout))
{
var returnValue = db.CreateParamReturnValue("returnValue");
List <object> argsList = args.ToList();
argsList.Add(returnValue);
using (DataSet ds = db.ExecDataSetProc(storedProcedure, argsList.ToArray()))
{
if (db.GetParamReturnValue(returnValue) == 0)
{
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
if (overwriteResultFunction.IsNotNull())
{
result = overwriteResultFunction(ds.Tables[0].ToList <TSource>(replaceResourceKeys));
}
else
{
result = ds.Tables[0].ToList <TSource>(replaceResourceKeys);
}
}
}
else
{
throw new Exception("The stored procedure " + storedProcedure + " returned the error code " + db.GetParamReturnValue(returnValue));
}
}
}
return(result);
}
/// <summary>
/// Executes a stored procedure while caches and returns the result as a List<TSource>
/// </summary>
/// <typeparam name="TSource">The Model for the returned Dataset</typeparam>
/// <param name="storedProcedure">Name of the stored procedure</param>
/// <param name="force">If true reloads the data from the database</param>
/// <param name="replaceResourceKeys">If true replaces all the tokens {#ResourceKey} with the relative resource value from the files under the Resources folder</param>
/// <param name="overwriteResultFunction">Delegate function used to overwrite/manipulate the data returned from the Stored Procedure</param>
/// <param name="cacheKey">If null or empty it will be equal to storedProcedure</param>
/// <param name="args">Any number of parameter name/value pairs and/or SQLParameter arguments</param>
/// <returns>Results as a List<TSource></returns>
public static List <TSource> ExecCachedListProc <TSource>(string storedProcedure, bool force = false, bool replaceResourceKeys = true, Func <List <TSource>, List <TSource> > overwriteResultFunction = null, string cacheKey = null, params object[] args)
{
HttpContext context = HttpContext.Current;
if (cacheKey.IsEmptyOrWhiteSpace())
{
cacheKey = storedProcedure;
}
if (force || context.Cache[cacheKey] == null) //Double check locking
{
lock (ThisLock)
{
if (force || context.Cache[cacheKey] == null) //Double check locking
{
using (AdoHelper2 db = new AdoHelper2())
{
var returnValue = db.CreateParamReturnValue("returnValue");
List <object> argsList = args.ToList();
argsList.Add(returnValue);
using (DataSet ds = db.ExecDataSetProc(storedProcedure, argsList.ToArray()))
{
if (db.GetParamReturnValue(returnValue) == 0)
{
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
if (overwriteResultFunction.IsNotNull())
{
context.Cache.Insert(cacheKey, overwriteResultFunction(ds.Tables[0].ToList <TSource>(replaceResourceKeys)));
}
else
{
context.Cache.Insert(cacheKey, ds.Tables[0].ToList <TSource>(replaceResourceKeys));
}
}
else
{
context.Cache.Remove(cacheKey);
}
}
else
{
context.Cache.Remove(cacheKey);
throw new Exception("The stored procedure " + storedProcedure + " returned the error code " + db.GetParamReturnValue(returnValue));
}
}
}
}
}
}
return(context.Cache[cacheKey] as List <TSource>);
}
/// <summary>
/// Checks if the database specified in the connString parameter is valid or not
/// </summary>
/// <returns></returns>
public static bool IsDatabaseValid(string connString)
{
bool result = false;
try
{
AdoHelper2 adoHelper = new AdoHelper2(connString);
result = true;
}
catch (Exception)
{
//Swallows the exception as this method is only intended to test for the Database validity
}
return(result);
}
public static List <DmDvcs> GetAllItems_DmDvcs()
{
return(AdoHelper2.ExecCachedListProc <DmDvcs>("sp_DmDvcs_select", false, false));
}
