Entity framework user defined table type proc

Question

I have a solution that bulk loads data via a datatable from c#, like this :

private void LoadRemittanceLines(DataTable dt, EiseBEDbEntities context)
{
    var param = new SqlParameter("Payments", SqlDbType.Structured)
    {
        Value = dt,
        TypeName = "dbo.udtRAPayment",
        Direction = ParameterDirection.Input
    };
    var r = context.Database.ExecuteSqlCommand("EXEC spRAPaymentInsertBulk", param);
}

The line fires, no errors, but the table yields no results. I profiler'ed my Db, and caught the code that executes. Here is a summarized, masked example:

--THIS IS ADDED BY ME TO CLEAR THE TABLE
delete from EiseBEDb..RAPayment

declare @p3 dbo.udtRAPayment
insert into @p3 values(1234879,987654,1,123.49,'2017-09-20 00:00:00','2017-09-20 00:00:00',NULL,0,'2018-07-22 00:00:00',10,0,NULL,NULL,1,N'This is a long string',NULL,NULL,NULL,N'',N'',N'1234',N'','2017-09-12 00:00:00',NULL,NULL)

--THIS CODE YIELDS RESULTS
--EXEC spRAPaymentInsertBulk @Payments=@p3

--THIS CODE YIELDS NO RESULTS
exec sp_executesql N'EXEC spRAPaymentInsertBulk',N'@Payments [dbo].[udtRAPayment] READONLY',@Payments=@p3

--THIS IS ADDED BY ME TO CHECK THE DATA
select * from EiseBEDb..RAPayment

What am I missing, and is there a way I can get passed this? The only solution I can think of is to not use EF to pass my data, but rather just making a connection to the Db manually end calling that working line of code.

Here is spRAPaymentInsertBulk, fieldnames omitted due to confidentiality.

CREATE PROCEDURE [dbo].[spRAPaymentInsertBulk]
(
    @Payments udtRAPayment readonly
)
AS
begin
    insert into RAPayment (Field1, field2)
    select Field1, field2
    from @Payments p 

end

I'm using SQL Server 2017.

`@P3` is unlikely to be a temporary table, it's a table **variable**. These behave quite differently at times. Where/when are you checking for the data? As soon as the batch you have run is complete the variable `@P3` will no longer exist. If it were a temporary table, however, then the table would exist until the connection that created it was closed. Also, what version of SQL Server? 2018 doesnt want exist (the latest version is 2017). Thanks. — Thom A, Jul 22 '18 at 11:21
Could you clarify what you mean by "It then executes this line of code :" ? What is "it" in that sentence? Your method already executes the procedure. — Crowcoder, Jul 22 '18 at 11:23
@Larnu, apologies on that. Typo on the version, I edited it (2017). Correct, I meant a table variable. I don't think the problem relates with the temp table/table variable. Right before both commands the inserts happen, then I select from the final table. If I use the dynamic SQL, it doesn't work. If I call the proc manually, it works. — WynDiesel, Jul 22 '18 at 11:25
@Crowcoder, "it" being the code I caught from SQL profiler. Let me try to mask the data, and edit my question with it. — WynDiesel, Jul 22 '18 at 11:26
You might want to include the SQL behind `spRAPaymentInsertBulk` as well, please. — Thom A, Jul 22 '18 at 11:29
How much data are you talking about? You may want to use SqlBulkCopy. — Crowcoder, Jul 22 '18 at 11:30
@Crowcoder, 619 lines. But I don't think this is due to size. If it was, neither of the Sql Commands would have worked. Considered using SqlBulkCopy, but I don't like the way it works, and the performance and ease of use of udt's is why I'm going this route. — WynDiesel, Jul 22 '18 at 11:34
I didn't mean it was failing because of the amount of data, just that bulk copy exists for a reason, it would be more performant too. — Crowcoder, Jul 22 '18 at 11:35
How about `context.Database.ExecuteSqlCommand("spRAPaymentInsertBulk @Payments", param)`? — Ivan Stoev, Jul 22 '18 at 11:46
@IvanStoev, close! Your suggestion lead to me the right answer : "var r = context.Database.ExecuteSqlCommand("exec spRAPaymentInsertBulk @Payments", param);". Feel free to add your comment as an answer so I can accept it, please. Thank you very much. — WynDiesel, Jul 22 '18 at 11:54
I don't know, it usually works w/o `EXEC`, but you should prepend '@' to the parameter name, e.g. `new SqlParameter("@Payments", …)`. And also with named parameters syntax - https://stackoverflow.com/questions/42856480/ef6-call-stored-procs-using-sqlquery-executesqlcommand-without-regard-for-parame/42857447#42857447. Anyway, you are welcome, glad it helped :) Feel free to add self answer. Cheers. — Ivan Stoev, Jul 22 '18 at 12:04
@IvanStoev When you call a stored procedure w/o EXEC, you normally set cmd.CommandType = CommandType.StoredProcedure, which causes the SqlCommand to build the RPC based on the parameter collection. If you use the default CommandType.Text, you are responsible for binding all the parameters in the batch. — David Browne - Microsoft, Jul 22 '18 at 14:43
@DavidBrowne-Microsoft As you can see, I'm binding the parameters - `"spRAPaymentInsertBulk @Payments"`. All I'm saying was that `EXEC` / `EXECUTE` before the sp/function name is [optional in SqlServer](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/execute-transact-sql?view=sql-server-2017#syntax). — Ivan Stoev, Jul 22 '18 at 14:59
Yes. In a single-statement batch EXEC is optional. If there are any other statements in the batch, EXEC is required. — David Browne - Microsoft, Jul 22 '18 at 15:01

score 0 · Accepted Answer · answered Jul 23 '18 at 12:10

0

My problem was with the text I passed, this resolved the issue:

var r = context.Database.ExecuteSqlCommand("exec spRAPaymentInsertBulk @Payments", param);

answered Jul 23 '18 at 12:10

WynDiesel

1,104
7
38

Entity framework user defined table type proc

1 Answers1