Okay, so here is a 2018 update: end to end solution which describes how to invoke stored procedure with table parameter from Entity Framework without nuget packages
I'm using EF 6.xx, SQL Server 2012 and VS2017
1. Your Table Value prameter
Let's say you have a simple table type defined like this (just one column)
go
create type GuidList as table (Id uniqueidentifier)
2. Your Stored procedure
and a stored procedure with several parameters like:
go
create procedure GenerateInvoice
@listIds GuidList readonly,
@createdBy uniqueidentifier,
@success int out,
@errorMessage nvarchar(max) out
as
begin
set nocount on;
begin try
begin tran;
--
-- Your logic goes here, let's say a cursor or something:
--
-- declare gInvoiceCursor cursor forward_only read_only for
--
-- bla bla bla
--
-- if (@brokenRecords > 0)
-- begin
-- RAISERROR(@message,16,1);
-- end
--
-- All good!
-- Bonne chance mon ami!
select @success = 1
select @errorMessage = ''
end try
begin catch
--if something happens let's be notified
if @@trancount > 0
begin
rollback tran;
end
declare @errmsg nvarchar(max)
set @errmsg =
(select 'ErrorNumber: ' + cast(error_number() as nvarchar(50))+
'ErrorSeverity: ' + cast(error_severity() as nvarchar(50))+
'ErrorState: ' + cast(error_state() as nvarchar(50))+
'ErrorProcedure: ' + cast(error_procedure() as nvarchar(50))+
'ErrorLine: ' + cast(error_number() as nvarchar(50))+
'error_message: ' + cast(error_message() as nvarchar(4000))
)
--save it if needed
print @errmsg
select @success = 0
select @errorMessage = @message
return;
end catch;
--at this point we can commit everything
if @@trancount > 0
begin
commit tran;
end
end
go
3. SQL Code to use this stored procedure
In SQL you would use something like that:
declare @p3 dbo.GuidList
insert into @p3 values('f811b88a-bfad-49d9-b9b9-6a1d1a01c1e5')
exec sp_executesql N'exec GenerateInvoice @listIds, @CreatedBy, @success',N'@listIds [dbo].[GuidList] READONLY,@CreatedBy uniqueidentifier',@listIds=@p3,@CreatedBy='FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF'
4. C# Code to use this stored procedure
And here is how you can call that Stored Procedure from Entity Framework (inside WebAPI):
[HttpPost]
[AuthorizeExtended(Roles = "User, Admin")]
[Route("api/BillingToDo/GenerateInvoices")]
public async Task<IHttpActionResult> GenerateInvoices(BillingToDoGenerateInvoice model)
{
try
{
using (var db = new YOUREntities())
{
//Build your record
var tableSchema = new List<SqlMetaData>(1)
{
new SqlMetaData("Id", SqlDbType.UniqueIdentifier)
}.ToArray();
//And a table as a list of those records
var table = new List<SqlDataRecord>();
for (int i = 0; i < model.elements.Count; i++)
{
var tableRow = new SqlDataRecord(tableSchema);
tableRow.SetGuid(0, model.elements[i]);
table.Add(tableRow);
}
//Parameters for your query
SqlParameter[] parameters =
{
new SqlParameter
{
SqlDbType = SqlDbType.Structured,
Direction = ParameterDirection.Input,
ParameterName = "listIds",
TypeName = "[dbo].[GuidList]", //Don't forget this one!
Value = table
},
new SqlParameter
{
SqlDbType = SqlDbType.UniqueIdentifier,
Direction = ParameterDirection.Input,
ParameterName = "createdBy",
Value = CurrentUser.Id
},
new SqlParameter
{
SqlDbType = SqlDbType.Int,
Direction = ParameterDirection.Output, // output!
ParameterName = "success"
},
new SqlParameter
{
SqlDbType = SqlDbType.NVarChar,
Size = -1, // "-1" equals "max"
Direction = ParameterDirection.Output, // output too!
ParameterName = "errorMessage"
}
};
//Do not forget to use "DoNotEnsureTransaction" because if you don't EF will start it's own transaction for your SP.
//In that case you don't need internal transaction in DB or you must detect it with @@trancount and/or XACT_STATE() and change your logic
await db.Database.ExecuteSqlCommandAsync(TransactionalBehavior.DoNotEnsureTransaction,
"exec GenerateInvoice @listIds, @createdBy, @success out, @errorMessage out", parameters);
//reading output values:
int retValue;
if (parameters[2].Value != null && Int32.TryParse(parameters[2].Value.ToString(), out retValue))
{
if (retValue == 1)
{
return Ok("Invoice generated successfully");
}
}
string retErrorMessage = parameters[3].Value?.ToString();
return BadRequest(String.IsNullOrEmpty(retErrorMessage) ? "Invoice was not generated" : retErrorMessage);
}
}
catch (Exception e)
{
return BadRequest(e.Message);
}
}
}
I hope it helps!
