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