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

Reputation: 113

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

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?

Upvotes: 8

Views: 9505

Answers (3)

Geens
Geens

Reputation: 159

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 , ','))

Upvotes: 7

jbl
jbl

Reputation: 15413

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

Upvotes: 13

gerryM
gerryM

Reputation: 113

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

Upvotes: 0

Related Questions