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>
/// 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>
/// <returns></returns>
public static T ExecuteScalarFunction <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 = cmd.ExecuteReader())
{
tbl.Read();
var vl = tbl.GetValue(0);
if (vl is T)
{
return((T)vl);
}
else
{
return((T)vl);//Throw
}
}
}
/// <summary>
/// Gets Parameters from procedure
/// Calls the Database
/// </summary>
public static Task <string[]> GetSPParametersAsync(DBObjectName storedProcedure, DbConnection connection, bool doNoUseCachedResults = false)
{
return(DatabaseInformation.Create(connection).GetSPParametersAsync(storedProcedure, doNoUseCachedResults));
}
public static DbCommand CreateSP(this DbConnection con, DBObjectName nameOfStoredProcedure) => CreateSPCommand(con, nameOfStoredProcedure);
/// <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))
