/// <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)); }