Entity framework user defined table type proc

c# datatables entity-framework-6 sql-server

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.

1
1
7/22/2018 11:32:11 AM

Accepted Answer

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

var r = context.Database.ExecuteSqlCommand("exec spRAPaymentInsertBulk @Payments", param);
0
7/23/2018 12:10:23 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow