/// <summary> /// Executes a stored function on the database. Parameters do not have to be in correct order, but they should /// </summary> /// <typeparam name="T">The type to be returned</typeparam> /// <typeparam name="TParam">The type of the Parameters</typeparam> /// <param name="con">The Connection</param> /// <param name="nameOfFunction">The name of the function</param> /// <param name="parameters">An object containing parameters (in key/value form)</param> /// <param name="checkParameters">Set this to false if you do not want this function to make an extra select on the db to get /// the parameters of the stored function. If you set this to false, ther parameters must be in correct order</param> public static async Task <T> ExecuteScalarFunctionAsync <T, TParam>(this DbConnection con, DBObjectName nameOfFunction, TParam parameters, bool checkParameters = true) { var cmd = con.CreateCommand(); cmd.CommandText = nameOfFunction.ToString(false);//We have to set this in order to make the next line work with checkPArameters cmd.AddParametersFromEntity <TParam>(parameters, checkParameters); cmd.CommandType = CommandType.Text; string[] sparams; if (checkParameters) { sparams = GetSPParameters(nameOfFunction, con); if (cmd.Parameters.Count != sparams.Length) {//We have to set default values string[] existingParameters = cmd.Parameters.OfType <DbParameter>().Select(s => s.ParameterName.ToLower()).ToArray(); foreach (var sparam in sparams) { if (!existingParameters.Contains(sparam.ToLower())) { cmd.AddCommandParameter(sparam, DBNull.Value); } } } } else { sparams = cmd.Parameters.OfType <DbParameter>().Select(s => s.ParameterName).ToArray(); } cmd.CommandText = "SELECT " + nameOfFunction.ToString(false) + "(" + string.Join(",", sparams) + ")"; using (var tbl = await cmd.ExecuteReaderAsync().ConfigureAwait(false)) { tbl.Read(); var vl = tbl.GetValue(0); if (vl is T) { return((T)vl); } else { return((T)vl);//Throw } } }
public static DbCommand CreateSPCommand(this DbConnection con, DBObjectName nameOfStoredProcedure) { var cmd = con.CreateCommand(); cmd.CommandText = nameOfStoredProcedure.ToString(false); cmd.CommandType = CommandType.StoredProcedure; return(cmd); }
/// <summary> /// Get all parameter names of a Stored Procedure /// </summary> /// <returns></returns> public string[] GetSPParameters(DBObjectName storedProcedure, bool doNoUseCachedResults = false) { if (!doNoUseCachedResults && spParameters.TryGetValue(storedProcedure.ToString(), out string[] spPrms))