.NET Core 7 Minimal API动态传递存储过程参数问题

我正在使用.NET Core 7 Minimal API构建Web API，编写了从数据库表读取存储过程并调用的逻辑，但遇到了无法将参数传入存储过程调用函数的问题。

示例表（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();
};

带参数@storerkey的存储过程SQL代码 #

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

期望实现：将存储过程参数传入委托处理器 #

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;

});

最终解决方案代码 #

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();
};

内容的提问来源于stack exchange，提问作者Morris Law