public ControlledQueryStatement(DbConnection cnx, string sql, params object[] args)
{
_cmd = cnx.CreateCommand();
var statment = new PrepareStatement(cnx.GetProvider(), sql, args);
statment.Setup(Command);
}
public static DbCommand CreateAndSetupCommand(this DbConnection cnx, string sql, params object[] args)
{
var provider = cnx.GetProvider();
var cmd = cnx.CreateCommand();
var statement = new PrepareStatement(provider, sql, args);
statement.Setup(cmd);
return(cmd);
}
private static SProcResult HandleProc(this DbConnection db, Action <SProcInput> input, Func <DbCommand, SProcResult> exec)
{
var data = new SProcInput();
input(data);
var output = new List <DbParameter>();
var provider = db.Provider();
DbParameter par = null;
DbParameter returnValue = null;
var cmdConfig = new CommandConfiguration();
cmdConfig.IsStoredProcedure = true;
cmdConfig.SqlText = data.ProcName;
cmdConfig.ApplyOptions = data.Apply;
SProcResult result;
using (var cmd = db.CreateAndSetupCommand(cmdConfig))
{
var args = PrepareStatement.CreateParamsDictionary(data.Arguments);
args.ForEach(kv =>
{
par = cmd.AddParam(provider, kv.Key, kv.Value);
if (IsOutput(kv.Key))
{
par.ParameterName = par.ParameterName[0] + kv.Key.Substring(1);
par.Direction = ParameterDirection.Output;
output.Add(par);
}
});
returnValue = cmd.AddParam(provider, "ReturnValue", 0);
returnValue.Direction = ParameterDirection.ReturnValue;
result = exec(cmd);
}
result.ReturnValue = returnValue.Value.ConvertTo <int>();
var outv = (IDictionary <string, object>)result.OutputValues;
foreach (var param in output)
{
//first char of parameter name is db specific param prefix
outv[param.ParameterName.Substring(1)] = param.Value;
}
return(result);
}
/// <summary>
/// Executes sproc
/// </summary>
/// <param name="cnx"></param>
/// <param name="sprocName"></param>
/// <param name="arguments">Arguments as an anonymous object, output parameters names must be prefixed with _ </param>
/// <example>
/// ExecuteStoredProcedure("sprocName",new{Id=1,_OutValue=""})
/// </example>
/// <returns></returns>
public static StoredProcedureResult ExecuteStoredProcedure(this DbConnection cnx, string sprocName,
object arguments)
{
using (var cmd = cnx.CreateCommand())
{
var output = new List <IDbDataParameter>();
sprocName.MustNotBeEmpty();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = sprocName;
var provider = cnx.GetProvider();
var paramDict = PrepareStatement.CreateParamsDictionary(arguments);
var allp = cmd.Parameters;
IDbDataParameter p = null;
p = cmd.CreateParameter();
provider.SetupParameter(p, "ReturnValue", 0);
allp.Add(p);
var returnParam = p;
p.Direction = ParameterDirection.ReturnValue;
foreach (var kv in paramDict)
{
p = cmd.CreateParameter();
var pname = kv.Key;
if (kv.Key.StartsWith("_"))
{
pname = kv.Key.Substring(1);
p.Direction = ParameterDirection.Output;
}
provider.SetupParameter(p, pname, kv.Value);
allp.Add(p);
output.Add(p);
}
cmd.Execute();
var rez = new StoredProcedureResult();
rez.ReturnValue = returnParam.Value.ConvertTo <int>();
foreach (var param in output)
{
//first char of parameter name is db specific param prefix
rez.OutputValues[param.ParameterName.Substring(1)] = param.Value;
}
return(rez);
}
}
