CODEWITHSUNDEEP
c#entity-frameworkgenericsstored-proceduresasp.net-web-api
Matthew Flynn
Matthew Flynn

Reputation: 3931

Generic Stored Procedure Endpoint for WebApi with Entity Framework

I am trying to create a generic stored procedure end point for our WebApi which is running EF 6.

The idea is that we can call a stored procedure by passing the name and parameters across. I have the following;

[HttpPost]
[CustomAuthFilter]
[Route("storedproc/{storedproc}")]
public IHttpActionResult RunStoredProcedure(string storedproc, Dictionary<string,string> parameters)
{
    var response = Repo.RunSP(storedproc, parameters);
    return Ok(response);
}

where the RunSP method is;

public static string RunSP(string storedProcedure,  Dictionary<string,string> parameters)
{
    using (var dataContext = new dbContext())
    {
        var queryString = new StringBuilder();
        queryString.Append(storedProcedure);
        queryString.Append(" ");

        int count = 0;

        var param = new List<string>();

        if (parameters != null)
        {
            foreach (var pair in parameters)
            {
                queryString.Append("@" + pair.Key + " = {" + count + "}");

                count++;

                if (count < parameters.Count)
                    queryString.Append(",");

                param.Add(pair.Value);
            }
        }

        var json = dataContext.Database.SqlQuery<string>(queryString.ToString(), param);
        return json;
    }
}

Now the issue is with seralising the return to an anonymous json string at the end? is this possible and if so how? As the method is generic I cant serialise to a specific class?

If I have to use something else other than EF to get this functionality what would the best approach be?

Upvotes: 1

Views: 1749

Answers (1)

lbrahim
lbrahim

Reputation: 3810

This will not be possible using EF easily. Someone actually tried doing it I found it here. You can go raw yourself and use SqlDataReader to populate, serialize and then return you data as string like done here. The easiest solution would be to use Dapper to get your results fetched as dynamic followed by serializing it using Json.net and return it.

using(SqlConnection con = new SqlConnection(ConnectionString))
{
    var result = await con.QueryAsync("QueryString", new { param = "param" });
    return JsonConver.SerializeObject(result);
}

Upvotes: 1

Related Questions