实体框架存储过程表值参数

我尝试调用一个接受表值参数的存储过程。我知道这在实体框架中还没有得到直接支持，但是据我所知，您可以使用 ObjectContext的 ExecuteStoreQuery命令来实现这一点。我有一个通用实体框架存储库，其中有以下 ExecuteStoredProcedure方法:

public IEnumerable<T> ExecuteStoredProcedure<T>(string procedureName, params object[] parameters)
{
StringBuilder command = new StringBuilder();
command.Append("EXEC ");
command.Append(procedureName);
command.Append(" ");


// Add a placeholder for each parameter passed in
for (int i = 0; i < parameters.Length; i++)
{
if (i > 0)
command.Append(",");


command.Append("{" + i + "}");
}


return this.context.ExecuteStoreQuery<T>(command.ToString(), parameters);
}

命令字符串的结尾如下:

EXEC someStoredProcedureName {0},{1},{2},{3},{4},{5},{6},{7}

我尝试在一个接受表值参数的存储过程上运行这个方法，但它中断了。我从 给你中读到，参数需要类型为 SqlParameter，表值参数需要将 SqlDbType设置为 Structured。所以我这样做，我得到一个错误说明:

The table type parameter p6 must have a valid type name

因此，我设置了 SqlParameter。TypeName 设置为我在数据库上创建的用户定义类型的名称，然后当我运行查询时，会得到以下真正有用的错误:

Incorrect syntax near '0'.

如果我返回到 ADO.NET 并执行一个数据读取器，我可以让查询运行，但是我希望使用数据上下文使它工作。

有没有使用 ExecuteStoreQuery传递表值参数的方法？此外，我实际上是使用实体框架代码第一和强制转换的 DbContext到 ObjectContext，以获得 ExecuteStoreQuery方法可用。这是必要的，或者我可以这样做对 DbContext以及？

102640 次浏览
小开

更改字符串连接代码以生成以下内容:

EXEC someStoredProcedureName @p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7
小开
最佳答案

更新

我已经在 Nuget Package-https://github.com/Fodsuk/EntityFrameworkExtras#nuget(EF4，EF5，EF6)中添加了对此的支持

有关代码示例，请查看 GitHub存储库。

这个问题有点离题，但对于试图将用户定义的表传递到存储过程中的人来说，仍然很有用。在研究了 Nick 的例子和其他 Stackoverflow 的帖子后，我想到了这个:

class Program
{
static void Main(string[] args)
{
var entities = new NewBusinessEntities();


var dt = new DataTable();
dt.Columns.Add("WarningCode");
dt.Columns.Add("StatusID");
dt.Columns.Add("DecisionID");
dt.Columns.Add("Criticality");


dt.Rows.Add("EO01", 9, 4, 0);
dt.Rows.Add("EO00", 9, 4, 0);
dt.Rows.Add("EO02", 9, 4, 0);


var caseId = new SqlParameter("caseid", SqlDbType.Int);
caseId.Value = 1;


var userId = new SqlParameter("userid", SqlDbType.UniqueIdentifier);
userId.Value = Guid.Parse("846454D9-DE72-4EF4-ABE2-16EC3710EA0F");


var warnings = new SqlParameter("warnings", SqlDbType.Structured);
warnings.Value= dt;
warnings.TypeName = "dbo.udt_Warnings";


entities.ExecuteStoredProcedure("usp_RaiseWarnings_rs", userId, warnings, caseId);
}
}


public static class ObjectContextExt
{
public static void ExecuteStoredProcedure(this ObjectContext context, string storedProcName, params object[] parameters)
{
string command = "EXEC " + storedProcName + " @caseid, @userid, @warnings";


context.ExecuteStoreCommand(command, parameters);
}
}

存储过程如下:

ALTER PROCEDURE [dbo].[usp_RaiseWarnings_rs]
(@CaseID int,
@UserID uniqueidentifier = '846454D9-DE72-4EF4-ABE2-16EC3710EA0F', --Admin
@Warnings dbo.udt_Warnings READONLY
)
AS

用户定义的表如下所示:

CREATE TYPE [dbo].[udt_Warnings] AS TABLE(
[WarningCode] [nvarchar](5) NULL,
[StatusID] [int] NULL,
[DecisionID] [int] NULL,
[Criticality] [int] NULL DEFAULT ((0))
)

我发现的约束包括:

  1. 传递给 ExecuteStoreCommand的参数必须与存储过程中的参数一致
  2. 必须将每个列传递到用户定义的表中，即使它们具有默认值。因此，我的 UDT 上似乎不能有 IDENTITY (1,1) NOT NULL 列
小开

我想分享一下我对这个问题的解决方案:

我有一些带有几个表值参数的存储过程，我发现如果这样调用它:

var query = dbContext.ExecuteStoreQuery<T>(@"
EXECUTE [dbo].[StoredProcedure] @SomeParameter, @TableValueParameter1, @TableValueParameter2", spParameters[0], spParameters[1], spParameters[2]);
var list = query.ToList();

你会得到一份没有记录的名单。

但是我更多的使用它，这句话给了我一个想法:

var query = dbContext.ExecuteStoreQuery<T>(@"
EXECUTE [dbo].[StoredProcedure] 'SomeParameterValue', @TableValueParameter1, @TableValueParameter2",  spParameters[1], spParameters[2]);
var list = query.ToList();

我用命令文本中的实际值 “有些参数值”更改了参数 @ Some Parameter。 而且成功了:) 这意味着，如果我们的参数中除了 结构化之外还有其他内容，那么它不会正确地传递所有内容，我们什么也得不到。 我们需要将实际参数替换为它们的值。

因此，我的解决方案如下:

public static List<T> ExecuteStoredProcedure<T>(this ObjectContext dbContext, string storedProcedureName, params SqlParameter[] parameters)
{
var spSignature = new StringBuilder();
object[] spParameters;
bool hasTableVariables = parameters.Any(p => p.SqlDbType == SqlDbType.Structured);


spSignature.AppendFormat("EXECUTE {0}", storedProcedureName);
var length = parameters.Count() - 1;


if (hasTableVariables)
{
var tableValueParameters = new List<SqlParameter>();


for (int i = 0; i < parameters.Count(); i++)
{
switch (parameters[i].SqlDbType)
{
case SqlDbType.Structured:
spSignature.AppendFormat(" @{0}", parameters[i].ParameterName);
tableValueParameters.Add(parameters[i]);
break;
case SqlDbType.VarChar:
case SqlDbType.Char:
case SqlDbType.Text:
case SqlDbType.NVarChar:
case SqlDbType.NChar:
case SqlDbType.NText:
case SqlDbType.Xml:
case SqlDbType.UniqueIdentifier:
case SqlDbType.Time:
case SqlDbType.Date:
case SqlDbType.DateTime:
case SqlDbType.DateTime2:
case SqlDbType.DateTimeOffset:
case SqlDbType.SmallDateTime:
// TODO: some magic here to avoid SQL injections
spSignature.AppendFormat(" '{0}'", parameters[i].Value.ToString());
break;
default:
spSignature.AppendFormat(" {0}", parameters[i].Value.ToString());
break;
}


if (i != length) spSignature.Append(",");
}
spParameters = tableValueParameters.Cast<object>().ToArray();
}
else
{
for (int i = 0; i < parameters.Count(); i++)
{
spSignature.AppendFormat(" @{0}", parameters[i].ParameterName);
if (i != length) spSignature.Append(",");
}
spParameters = parameters.Cast<object>().ToArray();
}


var query = dbContext.ExecuteStoreQuery<T>(spSignature.ToString(), spParameters);




var list = query.ToList();
return list;
}

代码肯定可以更优化，但我希望这将有所帮助。

小开

DataTable 方法是唯一的方法，但是构造一个 DataTable 并手动填充它是非常繁琐的。我想直接从 IEnumable 中定义 DataTable，其风格与 EF 流畅的模型构建器类似。所以:

var whatever = new[]
{
new
{
Id = 1,
Name = "Bacon",
Foo = false
},
new
{
Id = 2,
Name = "Sausage",
Foo = false
},
new
{
Id = 3,
Name = "Egg",
Foo = false
},
};


//use the ToDataTable extension method to populate an ado.net DataTable
//from your IEnumerable<T> using the property definitions.
//Note that if you want to pass the datatable to a Table-Valued-Parameter,
//The order of the column definitions is significant.
var dataTable = whatever.ToDataTable(
whatever.Property(r=>r.Id).AsPrimaryKey().Named("item_id"),
whatever.Property(r=>r.Name).AsOptional().Named("item_name"),
whatever.Property(r=>r.Foo).Ignore()
);

我已经在 dontnetfiddle: https://dotnetfiddle.net/ZdpYM3上发布了这个东西(注意，您不能在那里运行它，因为并非所有的程序集都加载到小提琴中)

小开 
var sqlp = new SqlParameter("@param3", my function to get datatable);
sqlp.SqlDbType = System.Data.SqlDbType.Structured;
sqlp.TypeName = "dbo.mytypename";


var v = entitycontext.Database.SqlQuery<bool?>("exec [MyStorProc] @param1,@param2,@param3,@param4", new SqlParameter[]
{
new SqlParameter("@param1",value here),
new SqlParameter("@param2",value here),


sqlp,
new SqlParameter("@param4",value here)


}).FirstOrDefault();
小开

好的，这是一个 二零一八年最新情况: 端到端的解决方案，它描述了如何使用 Entity Framework 没有坚果包装中的表参数调用存储过程

我使用的是 EF6.xx、 SQLServer2012和 VS2017

表值参数

假设您有一个这样定义的简单表类型(只有一列)

go
create type GuidList as table (Id uniqueidentifier)

2. 存储过程

以及具有以下几个参数的存储过程:

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 代码

在 SQL 中，您可能会使用类似的内容:

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 # 代码

下面是如何从实体框架(在 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);
}
}
}

希望能有所帮助！


提交答案