Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
342 views
in Technique[技术] by (71.8m points)

c# - 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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...