CODEWITHSUNDEEP
c#mysql.netmysql-connector
Philethius
Philethius

Reputation: 87

Transaction not being honored MySql .NET connector with multiple queries

I've tried researching this topic but it doesn't make sense. I have a bit of code trying to execute an UPDATE query and then loop through another set of INSERT queries. The second query errors because I've omitted the parameters (this is intentional at the moment to induce an error). The UPDATE still gets committed even though it should not...

Here's the code....

using (MySqlConnection con = existingCon.Clone())
{
con.Open();

MySqlParameter paramTimeStamp;
MySqlParameter paramFileNo;
MySqlParameter paramDescription;
MySqlParameter paramComment;
MySqlParameter paramUserInfo;
MySqlParameter paramPaymentType;
MySqlParameter paramSeriesLink;
MySqlParameter paramOriginalSubmitDate;

using (MySqlTransaction transact = con.BeginTransaction())
{
    try
    {
        using (
            MySqlCommand cmd =
                new MySqlCommand(
                    "UPDATE Payments "
                        + "SET strStatus = 'processing-preupload' "
                        + "WHERE strStatus = 'processing-prepost';",
                    con))
        {
            cmd.Transaction = transact;
            cmd.ExecuteNonQuery();
        }
    }
    catch (Exception e)
    {
        transact.Rollback();
        throw;
    }

    foreach (Payment payment in payments)
    {
        try
        {
            using (
                MySqlCommand cmd =
                    new MySqlCommand(
                        "INSERT INTO Activity "
                            + "(dtmTimeStamp, strFileNumber, strDescription, strComment, "
                                + "strUserInfo, strPaymentType, dtmSeriesLink, dtmOriginalSubmitDate) "
                            + "VALUES (@paramTimeStamp, @paramFileNo, @paramDescription, @paramComment, "
                                + "@paramUserInfo, @paramPaymentType, @paramSeriesLink, @paramOriginalSubmitDate);",
                        con))
            {
                paramTimeStamp = new MySqlParameter("@paramTimeStamp", DateTime.Now);
                paramFileNo = new MySqlParameter("@paramFileNo", payment.strFileNumber);
                paramDescription = new MySqlParameter("@paramDescription", description);
                paramComment = new MySqlParameter("@paramComment", comment);
                paramUserInfo = new MySqlParameter("@paramUserInfo", userInfo);
                paramPaymentType = new MySqlParameter("@paramPaymentType", PAYMENT_TYPE);
                paramSeriesLink = new MySqlParameter(
                    "@paramSeriesLink",
                    payment.strSeriesLink_corrected);
                paramOriginalSubmitDate = new MySqlParameter(
                    "@paramOriginalSubmitDate",
                    payment.strSubmitDate_corrected);

                cmd.Transaction = transact;
                cmd.ExecuteNonQuery();
            }
        }
        catch (Exception e)
        {
            transact.Rollback();
            throw;
        }
    }

    transact.Commit();
}
}

Thoughts?

Upvotes: 1

Views: 1411

Answers (2)

raven
raven

Reputation: 2564

Maybe this could help someone.

Some old versions of the connector (around 6.4.4 I think) automatically rolled back the transaction after a query error. This doesn't seem to be the case with the latest version (6.9.6).

"Funny thing, though. It appeared to work at one point, but stopped again" makes me wonder.

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1064234

Two thoughts; firstly:

In MySQL, only InnoDB and BDB (BerkeleyDB) table formats support transactions. Other standard storage engines ignore transactions and can not rollback data modifications.

So... what table format are you using?

Secondly: you could try using the overload that accepts an IsolationLevel, just to be explicit - but I suspect the table-format is the real problem here.

Upvotes: 2

Related Questions