在实体框架中使用存储过程进行n层数据同步

following question, i have a n-Tier synchronisation service that works fine when i insert new data into my sql database directly, but not if i do so in my web application.

下面的问题,我有一个n层同步服务,当我直接将新数据插入我的SQL数据库时工作正常,但如果我在我的Web应用程序中这样做,则不行。

i came to the solution that my inserts in the web application are not done through the stored procedures i defined for the desired table in my sql database.

我找到了解决方案,我在Web应用程序中的插入不是通过我为sql数据库中的所需表定义的存储过程完成的。

so i tried to implement these stored procedures in the entity framework, but i get the following error:

所以我试图在实体框架中实现这些存储过程,但我收到以下错误:

Error 2047: A mapping function binding specifies a function straschuInventarModel.Store.sp_tblInventar_applyinsert with an unsupported parameter: sync_row_count. Output parameters may only be mapped through the RowsAffectedParameter property. Use result bindings to return values from a function invocation.

错误2047:映射函数绑定使用不受支持的参数指定函数straschuInventarModel.Store.sp_tblInventar_applyinsert:sync_row_count。输出参数只能通过RowsAffectedParameter属性进行映射。使用结果绑定从函数调用返回值。

the stored procedure got implemented by updating database from model.

通过从模型更新数据库来实现存储过程。

The error exists by the sql feature @@rowcount that can't be asigned to sync_row_count in the designer.

sql功能@@ rowcount存在错误,该错误无法在设计器中与sync_row_count对齐。

stored procedure for insert into tbl

用于插入tbl的存储过程

ALTER procedure [dbo].[sp_tbl_applyinsert] (
@sync_last_received_anchor binary(8) , 
    @sync_client_id_hash int ,
    @sync_row_count int out,
    @idInventar varchar(5) = NULL ,
    @Aktiv bit = NULL)  

as
insert into [tbl] ([idInventar],[Aktiv]
    ,[update_originator_id]) 
    values (@idInventar, @Aktiv, @sync_client_id_hash)
set @sync_row_count = @@rowcount

any help would be appreciative!

任何帮助都会很感激!

1 个解决方案

#1

If you’re not interested in the actual value but rather use sync_row_count for optimistic concurrency checking, you may simply check the Rows Affected Parameter checkbox in the parameters mapping.

如果您对实际值不感兴趣,而是使用sync_row_count进行乐观并发检查,则可以在参数映射中选中Rows Affected Parameter复选框。

Apart from the case mentioned above, output parameters are not supported in EF entity model mapping functions. If you want the value be set to an entity’s property you may remove the parameter and return the value as a result instead:

除上述情况外,EF实体模型映射函数不支持输出参数。如果您希望将值设置为实体的属性,则可以删除该参数并返回该值作为结果:

ALTER procedure [dbo].[sp_tbl_applyinsert] (
    @sync_last_received_anchor binary(8), 
    @sync_client_id_hash int,
    @idInventar varchar(5) = NULL,
    @Aktiv bit = NULL)
as
    insert into [tbl] ([idInventar],[Aktiv],[update_originator_id]) 
    values (@idInventar, @Aktiv, @sync_client_id_hash)

    select @@rowcount as sync_row_count

The mapping in your entity model for sync_row_count then moves from the parameters mappings to result column bindings.

然后,实体模型中sync_row_count的映射从参数映射移动到结果列绑定。

If that’s not convenient either you may still expose the stored procedure via function import as normal method of your DbContext instance. This way you are free to use any kind of parameter you like.

如果这不方便您仍然可以通过函数import将存储过程作为DbContext实例的常规方法公开。这样您就可以*使用任何您喜欢的参数。

#1