如何将许多 SQL 查询作为事务进行? [英] How do I do many SQL queries as transactions?

问题描述

我一直在做删除、更新、插入, SELECT 在学校的数据库中.问题是它只能同时执行一个查询.像这样:

I have been doing codes that do DELETE, UPDATE, INSERT, SELECT in Databases in school. The problem is that it can do just one query simultaneously. Something like this:

OleDbConnection con = DAL.GetConnection();
con.Open();

if (con.State == ConnectionState.Open) //si la conexion esta abierta...
{
    string sql = string.Format(" INSERT INTO lol (...)");
    //----->I shortened this above because it's not important <-----
    OleDbCommand cmd = new OleDbCommand();
    cmd.Connection = con;
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = sql;
    int num = cmd.ExecuteNonQuery();
    con.Close();

    if (num == 0)
    {
         Response.Redirect("register.aspx?err=Error");
    }
    else
    {
         Session["id"] = MyDB.GetUserId(uname);
         Response.Redirect("home.aspx");
    }
}

有一次我的老师告诉我，有一种方法可以这样操作:您可以执行所有 SQL 查询，或者不执行任何操作.我想知道如何做到这一点，这真的很有帮助.

Once my teacher said me that there's something, that works like this: you do all the SQL queries, or no one. I would want to know how to this, it would be really helpful.

谢谢！

推荐答案

你的老师指的是 Transactions.大多数关系数据库都支持事务，包括 MySQL*.事务允许多个 CRUD 操作的原子行为.这意味着如果一个操作失败，数据库将回滚所做的任何更改，就好像这些操作从未发生过一样.

Your teacher is referring to Transactions. Most relational databases support transactions, including MySQL*. Transactions allow for atomic behavior of multiple CRUD operations. This means that if one operation fails the database will roll back any changes that were made as if none of the operations ever occurred.

请记住，它们按顺序运行，不是同时运行.但是，由于它们是原子的，因此感觉就像在单个操作中运行所有内容一样.

Keep in mind that they are running sequentially, not simultaneously. However, since they are atomic it feel feel similar to it running everything in a single operation.

为了使用 OleDbConnection 类使用 C# 你可以从你的连接对象创建一个事务，假设它是打开的.请记住，与存储过程不同，您需要手动提交或回滚事务.

In order to run a transaction using the OleDbConnection class using C# you can create a transaction from your connection object, assuming that it's open. Keep in mind though that unlike with a stored procedure you need to manually commit or rollback the transaction.

提交事务会使该组操作对数据库永久".提交后不可回滚.

Committing a transaction makes that set of operations 'permanent' to the database. After committing it cannot be rolled back.

回滚是指将数据库重置为开始事务之前存在的状态.

A rollback is when you reset the database to the state that existed prior to starting the transaction.

下面是一个从 OleDbConnection 对象创建事务并执行提交的示例，以及您可能想要回滚的两种情况:

Below is an example of creating a transaction from an OleDbConnection object along with performing a commit and two cases where you may want to rollback:

using(OleDbConnection con = DAL.GetConnection())
{
    OleDbTransaction transaction = null;
    try
    {
        con.Open();
        transaction = con.BeginTransaction()

        string queryString1 = //SQL string
        OleDbCommand cmd1 = new OleDbCommand();
        {
            Connection = con,
            CommandType = CommandType.Text,
            CommandText = queryString1
        };

        string queryString2 = //SQL string
        OleDbCommand cmd2 = new OleDbCommand();
        {
            Connection = con,
            CommandType = CommandType.Text,
            CommandText = queryString2
        };

        int num1 = cmd.ExecuteNonQuery();
        int num2 = cmd.ExecuteNonQuery();

        if (num1 == 0 || num2 == 0)
        {
            //We didn't expect something to return 0, lets roll back
            transaction.Rollback();
            //send error message
            Response.Redirect("register.aspx?err=Error");
        }
        else
        {
             //everything seems good, lets commit the transaction!
             transaction.Commit();
             Session["id"] = MyDB.GetUserId(uname);
             Response.Redirect("home.aspx");
        }
    }
    catch(OleDbException ex)
    {
         try
         {
             //something bad happened, lets roll everything back
             transaction.Rollback();
             Response.Redirect("register.aspx?err=Error");
         }
         catch
         {
             //we don't really care about this catch statement
         }
    }
}

这是关于 OleDbConnection.BeginTransaction 方法，其通用示例类似于我上面发布的内容.

Here's the MSDN article on the the OleDbConnection.BeginTransaction method with a generic example similar to what I posted above.

编辑:
*正如@Clockwork-Muse 在评论中指出的那样，MySQL 支持事务的能力取决于所使用的底层引擎.MySQL 引擎有很多，但主要的两个是 InnoDB 和 MyISAM.InnoDB 可以支持事务，但 MyISAM 不.

EDIT:
*As @Clockwork-Muse pointed out in the comments MySQL's ability to support transactions depends on the underlying engine being used. There are many MySQL engines, but the two main ones are InnoDB and MyISAM. InnoDB CAN support transactions, but MyISAM does NOT.

这篇关于如何将许多 SQL 查询作为事务进行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！