使用 EF ExecuteStoreCommand 进行批量插入不起作用，出了什么问题？
我正在使用 SQL 2008 和 EF 我有以下用于批量插入的存储过程
CREATE Type [dbo].[xxx] as Table (
[ErrorCode] [nvarchar](10),
[ErrorMessage] [nvarchar](300),
[FieldName] [nvarchar](50),
[FieldLable] [nvarchar](300),
)
CREATE procedure dbo.InsertAll(@Records xxx READONLY)
as
begin
insert into dbo.MyTable
select * from @Records;
end;
go
我正在传递一个具有多个记录的数据表作为参数（类型=结构化） 该过程在使用 SQLCommand.ExecuteNonQuery 调用时有效，但在使用 contextObject.ExecuteStoreCommand 调用时不执行任何操作。返回值 = 受影响的行始终为 0
出了什么问题？ EF 不支持此类过程吗？我什至没有得到任何异常:(
更新：运行 SQL 跟踪后刚刚意识到生成的 SQL 语句的差异
使用 contextObject.ExecuteStoreCommand
declare @p3 dbo.xxx
insert into @p3 values(N'M',N'ErrorMsg - 0',NULL,NULL)
insert into @p3 values(N'M',N'ErrorMsg - 1',NULL,NULL)
insert into @p3 values(N'M',N'ErrorMsg - 2',NULL,NULL)
exec sp_executesql N'InsertAll',N'@Records [xxx]
READONLY',@Records=@p3
使用 SQLCommand.ExecuteNonQuery 时
declare @p1 dbo.xxx
insert into @p1 values(N'M',N'ErrorMsg - 0',NULL,NULL)
insert into @p1 values(N'M',N'ErrorMsg - 1',NULL,NULL)
insert into @p1 values(N'M',N'ErrorMsg - 2',NULL,NULL)
exec InsertAll @Records=@p1
如何让 contextObject.ExecuteStoreCommand 执行与 SQLCommand 相同的 SQL stmt .执行非查询？
I am using SQL 2008 and EF
I have following stored proc for bulk insert
CREATE Type [dbo].[xxx] as Table (
[ErrorCode] [nvarchar](10),
[ErrorMessage] [nvarchar](300),
[FieldName] [nvarchar](50),
[FieldLable] [nvarchar](300),
)
CREATE procedure dbo.InsertAll(@Records xxx READONLY)
as
begin
insert into dbo.MyTable
select * from @Records;
end;
go
I am passing a Datatable as parameter (Type=structured) that has multiple records
This proc works when called using SQLCommand.ExecuteNonQuery, but does not do anything when called using contextObject.ExecuteStoreCommand. The return value = affected rows is always 0
Whats wrong? are such procedures not supported with EF? I am not even getting any exception :(
Update: After running SQL trace just realized the difference in the SQL statements being generated
When using contextObject.ExecuteStoreCommand
declare @p3 dbo.xxx
insert into @p3 values(N'M',N'ErrorMsg - 0',NULL,NULL)
insert into @p3 values(N'M',N'ErrorMsg - 1',NULL,NULL)
insert into @p3 values(N'M',N'ErrorMsg - 2',NULL,NULL)
exec sp_executesql N'InsertAll',N'@Records [xxx]
READONLY',@Records=@p3
When using SQLCommand.ExecuteNonQuery
declare @p1 dbo.xxx
insert into @p1 values(N'M',N'ErrorMsg - 0',NULL,NULL)
insert into @p1 values(N'M',N'ErrorMsg - 1',NULL,NULL)
insert into @p1 values(N'M',N'ErrorMsg - 2',NULL,NULL)
exec InsertAll @Records=@p1
How can I get contextObject.ExecuteStoreCommand to execute the same SQL stmt like SQLCommand.ExecuteNonQuery?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我发现当实体框架根本无法实现我想要的功能时，有必要提供扩展方法。好的部分是向 Context 对象添加扩展通常会让您不必深入研究 web.config 或 app.config 来获取连接字符串。那么参数和返回值就可以是通用的。我个人见过不少使用这种策略的雄辩解决方案。
I have found it necessary to provide extension methods when the Entity Framework simply will not work for what I want it to do. The good part is adding an extension to the Context object will usually keep you from having to dig into the web.config or app.config for a connection string. Then parameters and return values can be generic. I have personally seen quite a few eloquent solutions using this strategy.