CODEWITHSUNDEEP
c#sql-serverentity-frameworkstored-procedures
Michael
Michael

Reputation: 13616

Passing parameters to stored procedure using EF6

I use sql-server 2012 in my project.

I have stored procedure, I fire and passing parameters to the stored procedure with help of entity framework 6 method called ExecuteSqlCommand.

 _context.database.ExecuteSqlCommand("SP_GetRecords", here all params)

One of the parameters that I try to sent to stored procedure is list of integers.

I was advised to create table-valued parameters on sql-server and pass to it array or list.

But don't know how to pass this list or array of integers using ExecuteSqlCommand method to table-valued parameter.

Any idea how to pass list or array type inside ExecuteSqlCommand?

Upvotes: 1

Views: 3579

Answers (2)

Dima Grigoriev
Dima Grigoriev

Reputation: 357

We must create NpgsqlParameter and set parametr's name and type

        var param1 = new NpgsqlParameter("param1", NpgsqlDbType.Array | NpgsqlDbType.Bigint) { Value = Enumerable.Range(0, 10).ToArray()};

        var param2 = new NpgsqlParameter("param2", NpgsqlDbType.Bigint){ Value = 1}

        return DataSet
            .FromSql("select function_name(@param1, @param2) as \"SomeName\"", parameters: new object[] { param1, param2 })
            .FirstAsync();
    }

And that's all

Upvotes: 2

Niyoko
Niyoko

Reputation: 7672

Create your parameter

 SqlParameter parameter = new SqlParameter();
 parameter.ParameterName = "@myData";
 parameter.SqlDbType = System.Data.SqlDbType.Structured;
 parameter.TypeName = "dbo.YourCustomTableType";
 parameter.Value = myDataTable;

Execute as usual

ctx.Database.ExecuteSqlCommand("EXEC SP_GetData @myData", parameter);

Upvotes: 4

Related Questions