c＃具有多语句的事务 [英] c# Transaction with multi-statements

问题描述

大家好，



当我从数据表中逐行插入数据库时​​，我试图添加一个事务，这是我的代码：

Hi guys,

I tried to add a transaction when I insert row by row from a datatable into my database, here is my code:

public string addNewItems(DataView pDataView, string pTableName, int pTableId, string pUserName)
     {
         string returnString = string.Empty;
         Database db = AseDatabase;

         using (DbConnection connection = db.CreateConnection())
         {
             connection.Open();
             DbTransaction transaction = connection.BeginTransaction();
             //OdbcParameter parameter;
             DbCommand cmd;
             try
             {
                 cmd = db.GetSqlStringCommand("SET CHAINED OFF");
                 db.ExecuteNonQuery(cmd);

                 foreach (DataRowView pRowView in pDataView)
                 {
                    //I build the string sqlcmd

                    //cmd = db.GetSqlStringCommand(sqlcmd);

                    // I add parameters

                    // I execute the cmd in transaction:
                    // db.ExecuteNonQuery(cmd, transaction);


                 }

                 transaction.Commit();
             }
             catch (Exception ex)
             {
                 returnString = ex.Message;
                 transaction.Rollback();
                 throw;
             }

             return returnString;
         }
     }

问题来自于当我尝试插入时，它显示错误：

The problem comes from the fact that when I try to insert, it shows an error :

ERROR [ZZZZZ][Sybase][ODBC Driver][Adaptive Server Enterprise]The résidente SQL procedure ''psi_spy'' may be run only in unchained transaction mode. The ''SET CHAINED OFF'' command will cause the current session to use unchained transaction mode.	

这真的很奇怪，因为当我尝试使用简单创建的测试表运行我的代码时，它运行良好。但是下面的错误显示当我用我的工作表运行我的代码时...



我还检查了我工作表的选项插入，看起来效果很好...



有人能帮帮我吗？







PS：



正如你所看到的，我在下面设置了链接。



真正的问题是，如果我在交易中设置如下：

It is really strange, since when I tried to run my code with a simple created test table, it goes well. But the below error shows just when I run my code with my working tables...

I also checked the option insert of my working table, it seems it works well...

Could anyone help me please?



P.S:

As you can see, I "set chained off" below.

The real problem is, if I set chined off in the transaction like this:

cmd = db.GetSqlStringCommand("SET CHAINED OFF");
                    db.ExecuteNonQuery(cmd,transaction);

那么交易就行不通..



如果我在交易之外设置如下：

then the transaction just doesn''t work..

if I set chined off outside the transaction like this:

cmd = db.GetSqlStringCommand("SET CHAINED OFF");
                    db.ExecuteNonQuery(cmd);

然后错误显示...







提前谢谢!!!

then the error just shows...



thank you in advance!!!

推荐答案

你好，



如果在链接模式关闭的情况下创建存储过程，则会报告错误。然后你需要关闭链接模式来执行存储过程。



检查这个帖子： http://www.sybaseteam.com/set-chained-off-t-2092.html



祝你好运

jafc

Hello,

The error is reported if a stored procedure is created with chained mode switched off. You need to then execute the stored procedure with chained mode switched off too.

Check this thread: http://www.sybaseteam.com/set-chained-off-t-2092.html

good luck
jafc

这个问题来自需要在非连锁模式下运行的存储过程。解决此问题的最佳方法是直接在数据库中设置procanymode，如下所示：



> exec sp_procxmode< stored proc =name =>，anymode

> GO





请确认设置anymode时一切正常。

This problem comes from a stored procedure need to be run at unchained mode. The best way to solve this problem is to set the proc "anymode" directly in the database like this:

>exec sp_procxmode <stored proc="" name="">, "anymode"
>GO


Please be sure of that if everything is ok when the "anymode" is setted.

这篇关于c＃具有多语句的事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！