locked
How to call different procedures from common call RRS feed

  • Question

  • User-635033152 posted

    I am using EF6 in my application and use the below code to call stored procedure.

    YourDbContext.Database.ExecuteSqlCommand("exec StoreProcedure_Name @ParametterWithNummvalue, @In_Parameter, @Out_Parameter", params);

    Is there a way to make this line generic so that it works for different procedures and different set of parameters. Please suggest.

    Tuesday, March 1, 2016 6:05 PM

Answers

  • User-986267747 posted

    Hi Abhimanyuk,

    Abhimanyuk

    Is there a way to make this line generic so that it works for different procedures and different set of parameters. Please suggest.

    I suggest that you could define a static method like below, then you could call the method by passing different parameters.

    public static void ExecSp(string spName, string spParName, params Object[] parameters)
    
            {
    
                using (var db = new DemoEntities())
    
                {
    
                    int i = db.Database.ExecuteSqlCommand(spName + spParName, parameters);
    
                }
    
            }
    

    Then you could call it like below:

    string spName = "GetProductsByParam ";
    
    string spParName = " @sid, @name";
    
    var id = new SqlParameter("sid",SqlDbType.Int);
    
    id.Value = 2;
    
    var name = new SqlParameter("name", "P2");
    
    ExecSp(spName, spParName, id, name);
    

    There is a discussion about this problem, you could refer to the following link to get more detailed information

     https://forums.asp.net/p/2071275/5982203.aspx

    Best regards,

    Klein Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 2, 2016 6:24 AM

All replies

  • User-986267747 posted

    Hi Abhimanyuk,

    Abhimanyuk

    Is there a way to make this line generic so that it works for different procedures and different set of parameters. Please suggest.

    I suggest that you could define a static method like below, then you could call the method by passing different parameters.

    public static void ExecSp(string spName, string spParName, params Object[] parameters)
    
            {
    
                using (var db = new DemoEntities())
    
                {
    
                    int i = db.Database.ExecuteSqlCommand(spName + spParName, parameters);
    
                }
    
            }
    

    Then you could call it like below:

    string spName = "GetProductsByParam ";
    
    string spParName = " @sid, @name";
    
    var id = new SqlParameter("sid",SqlDbType.Int);
    
    id.Value = 2;
    
    var name = new SqlParameter("name", "P2");
    
    ExecSp(spName, spParName, id, name);
    

    There is a discussion about this problem, you could refer to the following link to get more detailed information

     https://forums.asp.net/p/2071275/5982203.aspx

    Best regards,

    Klein Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 2, 2016 6:24 AM
  • User-635033152 posted

    Thanks a lot Klein, it worked like a charm !!!

    Wednesday, March 2, 2016 3:11 PM
  • User-1576985591 posted

    Another approach:

    You  could add something like the following to your own DbContext decendant. It takes a sproc name, and a dictionary of parm name/values and returns a result set as a DataTable that you can use .AsEnumerable() against.

            public DataTable ExecStoredProcUsingAdo(string storedProcName, Dictionary<string, object> parms = null)
            {
                var dt = new DataTable();
    
                try
                {
                    using (var cn = new SqlConnection(_cs))
                    {
                        var cmd = cn.CreateCommand();
                        cmd.CommandText    = storedProcName;
                        cmd.CommandType    = CommandType.StoredProcedure;
                        cmd.CommandTimeout = 600;
    
                        if (null != parms)
                        {
                            foreach (var kvp in parms)
                            {
                                //_log.DebugFormat("Parm: {0} = {1}", kvp.Key, kvp.Value);
    
                                var parameter = new SqlParameter
                                {
                                    ParameterName = $"@{kvp.Key}",
                                    Direction     = ParameterDirection.Input,
                                    Value         = kvp.Value,
                                };
                                cmd.Parameters.Add(parameter);
                            }
                        }
    
                        cn.Open();
    
                        using (var da = new SqlDataAdapter(cmd))
                        {
                            da.Fill(dt);
                        }
                    }
                }
                catch (Exception ex)
                {
                    _log.Error(ex.Message, ex);
                    throw;
                }
    
                return dt;
            }

    alternatively, could have it return a typed value (which is slightly slower):

            public IList<T>  ExecStoredProc<T>(string storedProcName, Dictionary<string, object> parms)
            {
                List<T> spResults;
    
                var idx = 0;
                var sqlParams = new object[parms.Count];
                var sql = new StringBuilder("EXEC " + storedProcName + " ");
    
                foreach (var kvp in parms)
                {
                    //_log.DebugFormat("Parm: {0} = {1}", kvp.Key, kvp.Value);
    
                    sql.Append("@" + kvp.Key + ", ");
    
                    var p = new SqlParameter
                    {
                        ParameterName = "@" + kvp.Key,  // or ParameterName = $"@{kvp.Key}" in 4.6
                        Direction     = ParameterDirection.Input,
                        Value         = kvp.Value,
                    };
    
                    sqlParams[idx] = p;
                    idx++;
                }
    
                try
                {
                    spResults = Database.SqlQuery<T>(sql.ToString().Trim().TrimEnd(','), sqlParams).ToList();
                }
                catch (Exception ex)
                {
                    _log.ErrorFormat(ex.Message);
                    throw;
                }
    
                return spResults;
            }

    You create a standard Dictionary object, stuff it with the param you need and feed that to context classs method. Works for pretty much any kind of sproc call, and it does all the parm conversions for you.

    Thursday, March 3, 2016 4:51 PM