c# how to pass stored procedure parameter into api dynamically

Question

I'm creating web api using .net core7 minimal api. I create a function to call stored procedure which get from table in database.

I'm facing a problem to pass the sp parameter into the function.

Example of table(web_api.dbo.app_event):

eventKey	sp	method	param?
/getstorer	[dbo].[sGetStorer]	get	false
/getsku	[dbo].[sGetSkuByStorer]	get	true

c#

using (SqlConnection con = new(strConstr))
{
    con.Open();
    using (SqlCommand cmd = new("select * from web_api.dbo.app_event where isactive=1 and method='get'", con))
    {
        using (SqlDataReader dr = cmd.ExecuteReader())
        {
            while (dr.Read())
            {
                string _eventkey = dr["eventkey"].ToString()!;
                string _sp = dr["sp"].ToString()!;

                app.MapGet(_eventkey, () =>
                {
                    DataTable dt = new();
                    using SqlConnection connn = new(strConstr);
                    using SqlCommand cmd3 = new(_sp, connn);
                    cmd3.CommandType = CommandType.StoredProcedure;


                    connn.Open();
                    using SqlDataAdapter da = new(cmd3);
                    da.Fill(dt);
                    var j = JsonConvert.SerializeObject(dt);
                    connn.Close();
                    return j;

                });
            };
        };
    };
    con.Close();
};

sql for sGetSkuByStorer with parameter @storerkey

ALTER PROCEDURE [dbo].[sGetSkuByStorer]
    @storerkey nvarchar(18)
AS
BEGIN
    SET NOCOUNT ON;
    declare @TSQL nvarchar(max)

    select @TSQL='
    select 
    * from openquery(inf27,''
    select storerkey
        ,sku
        ,descr 
    from enterprise.sku 
    where storerkey='''''+@storerkey+'''''
    '')
    '
    exec(@TSQL)
END

I would like to put the sp parameter into the delegate handler

                app.MapGet(_eventkey, (xxxxx) =>
                {
                    DataTable dt = new();
                    using SqlConnection connn = new(strConstr);
                    using SqlCommand cmd3 = new(_sp, connn);
                    cmd3.CommandType = CommandType.StoredProcedure;


                    connn.Open();
                    using SqlDataAdapter da = new(cmd3);
                    da.Fill(dt);
                    var j = JsonConvert.SerializeObject(dt);
                    connn.Close();
                    return j;

                });

Final version

using (SqlConnection con = new(strConstr))
    {
        con.Open();
        using (SqlCommand cmd = new("select * from web_api.dbo.app_event where isactive=1 and method='get'", con))
        {
            using (SqlDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    string _eventkey = dr["eventkey"].ToString()!;
                    string _db = dr["db"].ToString()!;
                    string _sp = dr["sp"].ToString()!;
                    string sp = _db + "." + _sp;

                    app.MapGet(_eventkey, (HttpRequest reqs) =>
                    {
                        DataTable dt = new();
                        using SqlConnection connn = new(strConstr);
                        using SqlCommand cmd3 = new(sp, connn);
                        cmd3.CommandType = CommandType.StoredProcedure;

                        foreach (var p in reqs.Query)
                        {
                            cmd3.Parameters.Add(new SqlParameter("@" + p.Key.ToString(), p.Value.ToString()));
                        }
                        connn.Open();
                        using SqlDataAdapter da = new(cmd3);
                        da.Fill(dt);
                        var j = JsonConvert.SerializeObject(dt);
                        connn.Close();
                        return j;
                    });
                };
            };
        };
        con.Close();
    };

Answer 1

You can inject HttpRequest to retrieve request"s information :

app.MapGet("/foo", (HttpRequest request) =>
{
    //...

    foreach (var param in request.Query)
    {
        cmd3.Parameters.Add(new SqlParameter("@" + param.Key, param.Value.First()));
    }

    //...
})

param.Value is a collection, because is possible to call a url like :

/foo?param1=value1a&param1=value1b

Then request.Query["param1"] will be ["value1a", "value1b"].