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):

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;

                });

CodePudding user response：

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"].

CodePudding user response：

Create a function which will return Func with needed parameters. For example handler function parsing p query parameter and returning string can look like this:

Func<string, string> GetFuncForSp(string sp) => p =>
{
   // call sp:
   DataTable dt = new();
   using SqlConnection connn = new(strConstr);
   using SqlCommand cmd3 = new(sp, connn);
   // ...
}

And usage:

app.MapGet(_eventkey, GetFuncForSp(_sp));