Passing parameters to stored procedure using EF6

Question

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?

Niyoko · Accepted Answer · 2016-10-24T01:09:35.737

4

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

edited Oct 24 '16 at 01:09

answered Oct 24 '16 at 00:31

Niyoko

7,512
4
32
59

reall!?,is it so simple? and I can pass myData to table-valued parameters ? – Michael Oct 24 '16 at 00:46
In this row: parameter.Value = myDataTable what is type of myDataTable? – Michael Oct 24 '16 at 00:49
DataTable (https://msdn.microsoft.com/en-us/library/system.data.datatable(v=vs.110).aspx) – Niyoko Oct 24 '16 at 00:56

score 2 · Answer 2 · answered Oct 25 '16 at 05:29

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

Passing parameters to stored procedure using EF6

2 Answers2