Passing array of ints to T-SQL stored proc via entity framework

Question

I've read many posts and think I understand the concepts, but my small array of ints fails to pass from a C#/EF module to a SQL Server stored proc. Hoping other eyes can spot the problem.

I'm using EF6, 4.5 .Net Framework, SQL Server 2014

In the database I've created these types/procs:

CREATE TYPE [dbo].[IntsTTV] AS TABLE(
  [Id] [int] NOT NULL
)

Note that a table named 'Person' exists with columns 'Id' (int) and 'LastName' (nvarchar), and has data.

CREATE PROCEDURE [dbo].[GetUsers]
@UserIds dbo.IntsTTV READONLY
AS
BEGIN
  SELECT p.LastName
  FROM [dbo].[Person] p
  INNER JOIN @UserIds ids On p.Id = ids.Id;
END

// C# code

SqlMetaData[] columns = new SqlMetaData[1];
columns[0] = new SqlMetaData("Id", SqlDbType.Int);

SqlDataRecord row = new SqlDataRecord(columns);
row.SetInt32(0, 1);  // Id of '1' is valid for the Person table

SqlDataRecord[] table = new SqlDataRecord[1];
table[0] = row;

SqlParameter parameter = new SqlParameter();
parameter.SqlDbType = SqlDbType.Structured;
parameter.ParameterName = "@UserIds";
parameter.TypeName = "dbo.IntsTTV";
parameter.Direction = ParameterDirection.Input;
parameter.Value = table;

SqlParameter[] parameters = new SqlParameter[1];
parameters[0] = parameter;


var res = _db.Database.SqlQuery<string>("GetUsers", parameters).ToList();

The code does successfully call the proc, and if I hard code the proc to simply return a select of LastName's then the C# code does receive that. This tells me what is working.

If I call the proc from other T-SQL code, passing in a prepared table-valued parameter (IntsTTV) of ints, it works.

In the proc, if I select count of rows of the passed parameter table I get zero when calling from the C# code, but I get a correct count when calling from T-SQL code.

What am I missing, please?

jbl · Accepted Answer · 2017-10-12 20:01:54Z

13

This is how I call stored procedure with table valued parameter. The main difference being that I use a DataTable parameter.

I remember having issues with parameter name bindings, but I don't remeber exactly what they were. This explains the change I made in the syntax of the procedure call. I know this one should be working.

var dataTable = new DataTable();
dataTable.TableName = "dbo.IntsTTV";
dataTable.Columns.Add("Id", typeof(int));
dataTable.Rows.Add(1); // Id of '1' is valid for the Person table

SqlParameter parameter = new SqlParameter("UserIds", SqlDbType.Structured);
parameter.TypeName = dataTable.TableName;
parameter.Value = dataTable;

var res = _db.Database.SqlQuery<string>("EXEC GetUsers @UserIds", parameter).ToList();

edited Oct 12, 2017 at 20:01

answered Oct 12, 2017 at 14:07

jbl

15.5k3 gold badges37 silver badges104 bronze badges

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

Comments

Geens · Accepted Answer · 2017-12-07 15:15:25Z

8

I know this has been answered by found a different way that might help someone else out there

STRING_SPLIT

declare @intArray nvarchar(1000)
set @intArray = '1,2,3,4,5'
select value from STRING_SPLIT(@intArray , ',')

This will return a new table with the numbers in your @intArray

Then you just need to use it as a normal table

select * from myMainTable where Id in (select value from STRING_SPLIT(@intArray , ','))

answered Dec 7, 2017 at 15:15

Geens

1691 silver badge4 bronze badges

2 Comments

renakre Over a year ago

nice technique!

ghigad Over a year ago

Please, note that you will require SQL Server 2016 with compatibility level set to 130 for this to work... See learn.microsoft.com/en-us/sql/t-sql/functions/….

gerryM · Accepted Answer · 2017-10-12 18:04:52Z

0

That worked! For the sake of others I'll post the precise code here, which I had to tweak slightly.

var dataTable = new DataTable();
dataTable.TableName = "dbo.IntsTTV";
dataTable.Columns.Add("Id", typeof(int));
dataTable.Rows.Add(1); // Id of '1' is valid for the Person table

SqlParameter parameter = new SqlParameter("UserIds", SqlDbType.Structured);
parameter.TypeName = "dbo.IntsTTV";
parameter.Value = dataTable;
var res = _db.Database.SqlQuery<string>("EXEC dbo.GetUsers @UserIds", parameter).ToList();

answered Oct 12, 2017 at 18:04

gerryM

1131 silver badge6 bronze badges

1 Comment

Zach Mierzejewski Over a year ago

The only difference I can find is changing GetUsers to dbo.GetUsers. It took me a bit to compare them. Could you emphasize the specific changes for others?

Collectives™ on Stack Overflow

Passing array of ints to T-SQL stored proc via entity framework

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related