Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Transactions


A database transaction groups CUBRID queries into a unit of consistency (for ensuring valid results in multi-user environment) and restore (for making the results of committed transactions permanent and ensuring that the aborted transactions are canceled in the database despite any failure, such as system failure). A transaction is a collection of one or more queries that access and update the database. See more information in the Database Transaction manual page.

Transactions are a fundamental concept in databases and our CUBRID .NET data provider implements support for transactions in a similar way with direct-SQL transactions support. See Transaction Commit and Transaction Rollback.

Here is a code example showing how to use transactions:

conn.BeginTransaction();

string sql = "create table t(idx integer)";
using (CUBRIDCommand command = new CUBRIDCommand(sql, conn))
{
    command.ExecuteNonQuery();
}

conn.Rollback();

conn.BeginTransaction();

sql = "create table t(idx integer, a varchar(20))";
using (CUBRIDCommand command = new CUBRIDCommand(sql, conn))
{
    command.ExecuteNonQuery();
}

conn.Commit();

Here is a complete example:

using CUBRID.Data.CUBRIDClient;
using System.Diagnostics;

namespace TransactionExample
{
    class Program
    {
        private static void ExecuteSQL(string sql, CUBRIDConnection conn)
        {
            using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
            {
                cmd.ExecuteNonQuery();
            }
        }

        private static int GetTablesCount(string tableName, CUBRIDConnection conn)
        {
            int count = 0;
            string sql = "select count(*) from db_class where class_name = '" + tableName + "'";

            using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
            {
                count = (int)cmd.ExecuteScalar();
            }

            return count;
        }

        static void Main(string[] args)
        {
            CUBRIDConnectionStringBuilder sb = new CUBRIDConnectionStringBuilder("localhost", "demodb", "public", "", "33000");
            using (CUBRIDConnection conn = new CUBRIDConnection(sb.GetConnectionString()))
            {
                conn.Open();
                ExecuteSQL("drop table if exists t", conn);

                conn.BeginTransaction();

                string sql = "create table t(idx integer)";
                using (CUBRIDCommand command = new CUBRIDCommand(sql, conn))
                {
                    command.ExecuteNonQuery();
                }

                int tablesCount = GetTablesCount("t", conn);
                Debug.Assert(tablesCount == 1);

                conn.Rollback();

                //Verify the table does not exist
                tablesCount = GetTablesCount("t", conn);
                Debug.Assert(tablesCount == 0);

                conn.BeginTransaction();

                sql = "create table t(idx integer)";
                using (CUBRIDCommand command = new CUBRIDCommand(sql, conn))
                {
                    command.ExecuteNonQuery();
                }

                tablesCount = GetTablesCount("t", conn);
                Debug.Assert(tablesCount == 1);

                conn.Commit();

                tablesCount = GetTablesCount("t", conn);
                Debug.Assert(tablesCount == 1);

                conn.BeginTransaction();

                ExecuteSQL("drop table t", conn);

                conn.Commit();

                tablesCount = GetTablesCount("t", conn);
                Debug.Assert(tablesCount == 0);
                conn.Close();
            }
        }
    }
}

comments powered by Disqus
Page info
viewed 1157 times
translations en
Author
posted 2 years ago by
CUBRID
Contributors
updated 2 years ago by
View revisions
Share this article