Calling a stored-procedure from entity framework with various parameters

Question

I have 3 sprocs named:

RECEIVED
NOTRECEIVED
UPDATESTATUS

RECEIVED and NOTRECEIVED have the following params

@ProductNumber,
@ProductName

UPDATESTATUS has:

@BatchNumber

I have gone ahead and created a method which I use to execute the sproc named UPDATESTATUS. I want to make it flexible so I can call it for all of the sprocs regardless of the params and pass in the sproc name to the method.

The method is as follows:

public async Task<int> ExecuteSproc(SqlParameter[] sqlParameter)
{
   int result = await db.Database.ExecuteSqlCommandAsync("exec UPDATESTATUS @BatchNumber", sqlParameter);

   return result;
}

This is how I called method ExecuteSproc()

SqlParameter[] sqlParameter = new SqlParameter[]
{
   new SqlParameter("@BatchNumber", System.Data.SqlDbType.NVarChar){ Value = batchNumber}
};

int count = await ExecuteSproc(sqlParameter);

Can some tell me how I would achieve this please. I look at this post but the answer suggest I have to specify the param names whereas I am trying to make it a little more generic

You can pass a `KeyValuePair` instead of an array of `SqlParameter` and generate the string you need according to the post you linked. This way you can keep it a bit more generic. — Paul Karam, Feb 05 '18 at 12:36
@PaulKaram would it be possible if you can provide an example please — Izzy, Feb 05 '18 at 12:37
Your solution should also work, it depends how are you using it? Can you provide the code where you're actually calling `ExecuteSproc`? Also, did you get any error trying it? — Paul Karam, Feb 05 '18 at 12:41
@PaulKaram The error I received is `Procedure or function 'UPDATESTATUS' expects parameter '@BatchNumber', which was not supplied` — Izzy, Feb 05 '18 at 12:49
Does it work if you change `exec UPDATESTATUS @BatchNumber` to `UPDATESTATUS`? — mjwills, Feb 05 '18 at 12:54
You could get the list of parameters (https://stackoverflow.com/questions/33761/how-can-i-retrieve-a-list-of-parameters-from-a-stored-procedure-in-sql-server) - _you will want to cache this since looking it up repeatedly is expensive_. For each parameter, look in your `sqlParameter` array and see if there is an entry with the same name. If so, add it to a new `bob` array - and pass that array to your stored proc invocation (rather than `sqlParameter` as you are now). — mjwills, Feb 05 '18 at 12:58
Did you read the link I provided? What happened when you tried the multiple suggestions there? — mjwills, Feb 05 '18 at 13:05

score -1 · Answer 1 · edited Jan 30 '19 at 19:04

You need to pass stored procedure name & parameter in KeyValuePair format.

public List<V> ExecStoredProc<V>(string storedProcedureName, KeyValuePair<string, string>[] parameters)
{
    if (parameters.Any())
    {
        SqlParameter[] sqlParams = patameters.Select(x => new SqlParameter("@" + x.Key, x.Value)).ToArray();
        var result = _context.Database
                .SqlQuery<V>(storedProcedureName + " " + string.Join(" ", patameters.Select(x => "@" + x.Key)), sqlParams)
                .ToList();
        _context.Database.Connection.Close();
        return result;
    }
    else
    {
        var result = _context.Database
                .SqlQuery<V>(storedProcedureName)
                .ToList();
        _context.Database.Connection.Close();
        return result;
    }
}

Execution

var parmas = new KeyValuePair<string, string>[] { new KeyValuePair<string, string>("BatchNumber", BatchId.ToString()) };

List<User> list = ExecStoredProc<User>("UPDATESTATUS", parmas);

Calling a stored-procedure from entity framework with various parameters

1 Answers1