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

"I'm facing a problem to pass the sp parameter into the function." - so what is the problem? Your code throws? The endpoint is not mapped? — Guru Stron
– Guru Stron, Commented Jan 10, 2023 at 14:03
The problem is I don't know how to pass sp parameter dynamically. — Morris Law
– Morris Law, Commented Jan 10, 2023 at 14:30

vernou · Accepted Answer · 2023-01-10 14:48:15Z

2

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

answered Jan 10, 2023 at 14:48

vernou

7,6825 gold badges40 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Morris Law Over a year ago

Thanks! I success to put the parameter to api dynamically.

Guru Stron · Accepted Answer · 2023-01-10 14:49:29Z

0

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

answered Jan 10, 2023 at 14:49

Guru Stron

151k11 gold badges186 silver badges232 bronze badges

Collectives™ on Stack Overflow

c# how to pass stored procedure parameter into api dynamically

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related