C#学习教程:在SQL Azure上调用exec sp并使用EF6抛出时,存储过程正常工作分享


在SQL Azure上调用exec sp并使用EF6抛出时,存储过程正常工作

我有这个存储过程

CREATE PROCEDURE [dbo].[sp_RebuildIndexes] AS BEGIN DECLARE @TableName varchar(255) DECLARE TableCursor CURSOR FOR (SELECT '[' + IST.TABLE_SCHEMA + '].[' + IST.table_name + ']' AS [TableName] FROM INFORMATION_SCHEMA.TABLES IST WHERE IST.TABLE_TYPE = 'BASE TABLE') OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@fetch_status = 0 BEGIN PRINT ('Rebuilding Indexes on ' + @TableName) BEGIN TRY EXEC ('ALTER INDEX ALL ON ' + @TableName + ' REBUILD with (ONLINE=ON)') END TRY BEGIN CATCH PRINT ('Cannot do rebuild with Online=On option, taking table ' + @TableName + ' down for doing rebuild') EXEC ('ALTER INDEX ALL ON ' + @TableName + ' REBUILD') END CATCH FETCH NEXT FROM TableCursor INTO @TableName END CLOSE TableCursor DEALLOCATE TableCursor END 

如果我用SQL查询执行它

 exec [dbo].[sp_RebuildIndexes] 

它工作正常。

现在使用此代码从EF6调用它会抛出SQL Azure,但适用于localdb:

 var sqlConnection = (SqlConnection) _context.Database.Connection; sqlConnection.InfoMessage += (s, m) => messages = m.Message; _context.Database.ExecuteSqlCommand("exec [dbo].[sp_RebuildIndexes]"); 

例外:

 The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. Uncommittable transaction is detected at the end of the batch. The transaction is rolled back. [SqlException (0x80131904): The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. 

你有什么问题的线索吗?

编辑:

只有在无法使用ONLINE = ON重建索引的情况下才会出现此问题

编辑2:如果我使用此sp与SqlConnection对象,它的工作原理。

最后这里是修复:

我怀疑是一个交易问题,经过几次调查后问题是:如果你在这里查看http://msdn.microsoft.com/en-us/data/dn456843

从EF6 Database.ExecuteSqlCommand()开始,默认情况下会将命令包装在事务中(如果尚未存在)。 此方法存在重载,允许您根据需要覆盖此行为。 同样在EF6中,通过API(例如ObjectContext.ExecuteFunction())执行模型中包含的存储过程也会执行相同的操作(除了此时不能覆盖默认行为)。

所以更换

 _context.Database.ExecuteSqlCommand("exec [dbo].[sp_RebuildIndexes]"); 

 _context.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, "exec [dbo].[sp_RebuildIndexes]"); 

它的工作原理!

上述就是C#学习教程:在SQL Azure上调用exec sp并使用EF6抛出时,存储过程正常工作分享的全部内容,如果对大家有所用处且需要了解更多关于C#学习教程,希望大家多多关注—猴子技术宅(www.ssfiction.com)

本文来自网络收集,不代表猴子技术宅立场,如涉及侵权请点击右边联系管理员删除。

如若转载,请注明出处:https://www.ssfiction.com/ckf/1031829.html

发表评论

邮箱地址不会被公开。 必填项已用*标注