在使用MySqlTransaction时需要帮助 [英] Need help in using MySqlTransaction

问题描述

您好，我想在自己的需求中使用MySqlTransaction.实际上，我对此感到怀疑，即根据我的要求，我将不得不从数据库中删除不同的值.

我正在做的过程如下.假设我有2个EmpID，其中该EmpID将持有可能是多个的不同值.我将使用Dictionary存储该特定EmpID的对应值，然后将它们保存到与EmpID对应的列表中.

假设我具有如下列表元素

对于EmpID 1，我将有1,2.我将检查此列表中datbase中的最大值(如果存在)，我想从数据库中删除此EmpID.

对于EmpID 2，我将为1,2.但是在我的数据库中，我将有3个最大值.所以这一次失败了.我想回滚以前删除的项目.

如果可以的话，可以进行一项交易吗?

我的示例代码

Hi all i would like to use MySqlTransaction in my requirement. Actually i am having a doubt regarding that i.e as per my requirement i will have to delete different values from database.

The process i am doing is as follows. Assume that i am having 2 EmpIDs where this EmpID will hold different values which may be multiple. I will store the corresponding values for that particular EmpID using Dictionary and then i will save them to a list corresponding to the EmpID.

Assume that i am having list element as follows

For EmpID 1 i will have 1,2. I will check for the maximum value from the datbase in this list if exists i would like to delete this EmpID from the database.

For EmpID 2 i will have 1,2. But in my database i will have 3 as maximum values. So this one fails . I would like to rollback the previously deleted item .

Is it possible to do with a transaction if so can any one help me in solving this

Sample i code

if(findMax(lst,iEmpID)
  {
       obj.delete("storeprocname");   // this will occur when my list has maximum value
  }
 else
 {
     //Here i would like to rollback my previous one referring to the delete method in class file
 }

public bool deletePayRoll(string storeproc,bool result)
        {
if(result==true)
{
            m_bFlag = false;
            this.m_oConn = Utilities.GetConnection();
            m_oCmd = new MySqlCommand(storeproc, m_oConn);
            m_oCmd.CommandType = CommandType.StoredProcedure;

            m_oCmd.Parameters.AddWithValue("_EmpId", EmpID);
            m_oCmd.Parameters.AddWithValue("_FedTaxID", FedTaxID);
            m_oCmd.Parameters.AddWithValue("_payperiodnumber", Payperiodnumber);
            m_oCmd.Parameters.AddWithValue("_payyear", PayYear);
            m_oCmd.Parameters.AddWithValue("_paymentdate", PaymentDate);

            try
            {
                if (m_oConn.State != ConnectionState.Open)
                {
                    m_oConn.Open();

                }
                if (m_oCmd.ExecuteNonQuery() > 0)
                {
                    m_bFlag = true;
                }
            }
            catch (MySqlException oSqlEx)
            {
                m_sbErrMsg.Length = 0;
                m_sbErrMsg = Utilities.SqlErrorMessage(oSqlEx);
                //DB write the Error Log
                m_oErrlog.Add(m_sbErrMsg.ToString(), DateTime.Now);
            }
            catch (Exception oEx)
            {

                m_sbErrMsg = Utilities.ErrorMessage(oEx);
                //DB write the Error Log
                m_oErrlog.Add(m_sbErrMsg.ToString(), DateTime.Now);
            }

            finally
            {
                m_oConn.Close();
            }
}else
{
// Here i would like to rollback is it correct process 
}
            return m_bFlag;
        }

嗯，我认为没人能清楚地理解我的问题，所以在简单的情况下，我说的是

我有一个表单，当我单击按钮时，它将有2个文本框和一个按钮，我想将在文本框中输入的文本作为单独的行插入数据库.

假设如果我将插入的第一个项目设置为true，则在插入时我将传递一个bool值，将第二个要插入的项目设置为false.因为我有错误，所以我不想插入这个，所以我也必须回滚前一个

示例代码

Hmm i think no one understand my question clearly so in simple scenario i am saying

i am having a form which will have 2 text boxes and button when i click on button i would like to insert the text entered in the text boxes to database as separate rows.

Assume i will have a bool value to pass while inserting if i give true for the first item it will get inserted, i will give false to the second one which i would like to insert. As i am having false i don''t want to insert this so i have to roll back the previous one too

a sample code

button click
{
string str="Dora";
string str1="Babu";
bool flag=true;
i=1,j=1;
if(i==1 && flag==true)
inser str;
flag=false;
if(j==1 && flag==false)
rollback 
}

推荐答案

是的-事务正是您所需要的.一旦开始事务，就不会提交任何永久更改，直到您提交更改为止，这与常规操作不同，在常规操作中，一旦执行命令便无法撤消更改.通过交易，您可以改变主意"并回滚或取消您已执行的操作.

这里有MySql中的事务处理指南: http://www .devshed.com/c/a/MySQL/Using-Transactions-In-MySQL-Part-1/ [

Yes - and transactions are exactly what you need. Once you start a transaction, no changes are permanent until you commit them, unlike normal operations where they happen irrevocably as soon as the command is executed. With a transaction, you can "change your mind" and rollback or cancel the actions you have taken.

There is a guide to transactions in MySql here: http://www.devshed.com/c/a/MySQL/Using-Transactions-In-MySQL-Part-1/[^]

正如OriginalGriff指出的，交易是正确的做到这一点的方法.还可以查看文档: MySqlTransaction [ ^ ].

另一件事，如果我正确地理解了您的情况，是您计算最大值并可能插入数据以便使用MAX + 1是类似的事情.如果您具有这种结构，则不建议在数据库中维护此类订单号.每行应该有一个唯一的键，但是您不应该在程序中设置它.而是使用 AUTO_INCREMENT [

As OriginalGriff pointed out, transaction is the correct way to do this. Also have a look at the documentation: MySqlTransaction[^].

Another thing, if I understood your scenario correctly, are you calculating maximum values and perhaps inserting data so that you use MAX+1 is something similar. If you have that kind of structure, it''s not advisable to maintain such order numbers in the database. Each row should have a unique key, but you shouldn''t set it in the program. Instead use AUTO_INCREMENT[^]

这篇关于在使用MySqlTransaction时需要帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！