/// <summary>
/// Test CUBRIDTransaction class
/// </summary>
private static void Test_Transaction()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
TestCases.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();
TestCases.ExecuteSQL("drop table t", conn);
conn.Commit();
tablesCount = GetTablesCount("t", conn);
Debug.Assert(tablesCount == 0);
}
}
public void conn_setIsolationLevel()
{
string conn_string = "server=test-db-server;database=demodb;port=33000;user=dba;password="******"drop table if exists test_isolation";
cmd.ExecuteNonQuery();
// open another session
CUBRIDConnection conn2 = new CUBRIDConnection();
conn2.ConnectionString = conn_string;
conn2.Open();
CUBRIDCommand cmd2 = new CUBRIDCommand();
cmd2.Connection = conn2;
// set up the isolation level to
conn.SetAutoCommit(false);
conn.SetIsolationLevel(CUBRIDIsolationLevel.TRAN_REP_CLASS_COMMIT_INSTANCE);
cmd.CommandText = "create table test_isolation(a int)";
cmd.ExecuteNonQuery();
conn.Commit();
conn.Close();
}
/// <summary>
/// Test CUBRID Isolation Levels
/// </summary>
private static void Test_IsolationLevel()
{
string sqlTablesCount = "select count(*) from db_class";
int tablesCount, newTableCount;
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
TestCases.ExecuteSQL("drop table if exists isol", conn);
conn.SetIsolationLevel(CUBRIDIsolationLevel.TRAN_REP_READ);
Debug.Assert(conn.GetIsolationLevel() == CUBRIDIsolationLevel.TRAN_REP_READ);
tablesCount = (int)TestCases.GetSingleValue(sqlTablesCount, conn);
TestCases.ExecuteSQL("create table isol(id int)", conn);
newTableCount = (int)TestCases.GetSingleValue(sqlTablesCount, conn);
//Verify table was created
Debug.Assert(newTableCount == tablesCount + 1);
using (CUBRIDConnection connOut = new CUBRIDConnection())
{
connOut.ConnectionString = TestCases.connString;
connOut.Open();
newTableCount = (int)TestCases.GetSingleValue(sqlTablesCount, connOut);
//CREATE TABLE is visible from another connection
Debug.Assert(newTableCount == tablesCount + 1);
}
TestCases.ExecuteSQL("drop table if exists isol", conn);
}
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
conn.SetIsolationLevel(CUBRIDIsolationLevel.TRAN_REP_CLASS_COMMIT_INSTANCE);
Debug.Assert(conn.GetIsolationLevel() == CUBRIDIsolationLevel.TRAN_REP_CLASS_COMMIT_INSTANCE);
tablesCount = (int)TestCases.GetSingleValue(sqlTablesCount, conn);
conn.BeginTransaction();
TestCases.ExecuteSQL("create table isol(id int)", conn);
newTableCount = (int)TestCases.GetSingleValue(sqlTablesCount, conn);
//Verify table was created
Debug.Assert(newTableCount == tablesCount + 1);
using (CUBRIDConnection connOut = new CUBRIDConnection())
{
connOut.ConnectionString = TestCases.connString;
connOut.Open();
newTableCount = (int)TestCases.GetSingleValue(sqlTablesCount, connOut);
Debug.Assert(newTableCount == tablesCount + 1);
}
conn.Commit();
newTableCount = (int)TestCases.GetSingleValue(sqlTablesCount, conn);
//Verify table was created
Debug.Assert(newTableCount == tablesCount + 1);
TestCases.ExecuteSQL("drop table if exists isol", conn);
newTableCount = (int)TestCases.GetSingleValue(sqlTablesCount, conn);
Debug.Assert(newTableCount == tablesCount);
}
}
public void SetIsolationLevel_Test()
{
string sqlTablesCount = "select count(*) from db_class";
int tablesCount, newTableCount;
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = DBHelper.connString;
conn.Open();
DBHelper.ExecuteSQL("drop table if exists t", conn);
conn.SetAutoCommit(false);
tablesCount = (int)DBHelper.GetSingleValue(sqlTablesCount, conn);
DBHelper.ExecuteSQL("create table t(id int)", conn);
newTableCount = (int)DBHelper.GetSingleValue(sqlTablesCount, conn);
//Verify table was created
Assert.AreEqual(tablesCount + 1, newTableCount);
conn.Commit();
newTableCount = (int)DBHelper.GetSingleValue(sqlTablesCount, conn);
Assert.AreEqual(tablesCount + 1, newTableCount);
DBHelper.ExecuteSQL("drop table if exists t", conn);
LogTestResult();
}
}
public void Transaction_Test()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = DBHelper.connString;
conn.Open();
DBHelper.ExecuteSQL("drop table if exists t", conn);
LogTestStep("Begin a transaction, then rollback");
conn.BeginTransaction();
DBHelper.ExecuteSQL("create table t(idx integer)", conn);
int tablesCount = DBHelper.GetTablesCount("t", conn);
Assert.AreEqual(1, tablesCount);
conn.Rollback();
//Verify the table does not exist
tablesCount = DBHelper.GetTablesCount("t", conn);
Assert.AreEqual(0, tablesCount);
LogStepPass();
LogTestStep("Begin a transaction, then commit, then rollback");
conn.BeginTransaction();
DBHelper.ExecuteSQL("create table t(idx integer)", conn);
tablesCount = DBHelper.GetTablesCount("t", conn);
Assert.AreEqual(1, tablesCount);
conn.Commit();
tablesCount = DBHelper.GetTablesCount("t", conn);
Assert.AreEqual(1, tablesCount);
conn.Rollback();
tablesCount = DBHelper.GetTablesCount("t", conn);
Assert.AreEqual(1, tablesCount);
LogStepPass();
Console.WriteLine();
//revert the test db
DBHelper.ExecuteSQL("drop table t", conn);
conn.Commit();
LogTestResult();
}
}
public static void Test_BeginDbTransaction()
{
try
{
conn.SetConnectionTimeout(30);
}
catch (Exception e)
{
Debug.Assert(e.ToString() == "Not allowed to change the 'ConnectionTimeout'");
}
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
string source = conn.DataSource;
TestCases.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();
TestCases.ExecuteSQL("drop table t", conn);
conn.Commit();
tablesCount = GetTablesCount("t", conn);
Debug.Assert(tablesCount == 0);
//IsolationLevel.Chaos
try
{
conn.BeginTransaction(IsolationLevel.Chaos);
}
catch (Exception e)
{
Debug.Assert(e.Message == "Not supported in CUBRID!");
}
//IsolationLevel.ReadCommitted
conn.BeginTransaction(IsolationLevel.ReadCommitted);
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();
TestCases.ExecuteSQL("drop table t", conn);
conn.Commit();
tablesCount = GetTablesCount("t", conn);
Debug.Assert(tablesCount == 0);
//IsolationLevel.RepeatableRead
conn.BeginTransaction(IsolationLevel.RepeatableRead);
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();
TestCases.ExecuteSQL("drop table t", conn);
conn.Commit();
tablesCount = GetTablesCount("t", conn);
Debug.Assert(tablesCount == 0);
//IsolationLevel.Serializable
conn.BeginTransaction(IsolationLevel.Serializable);
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();
TestCases.ExecuteSQL("drop table t", conn);
conn.Commit();
tablesCount = GetTablesCount("t", conn);
Debug.Assert(tablesCount == 0);
//IsolationLevel.Snapshot
try
{
conn.BeginTransaction(IsolationLevel.Snapshot);
}
catch (Exception e)
{
Debug.Assert(e.Message == "Not supported in CUBRID!");
}
//IsolationLevel.Snapshot
try
{
conn.BeginTransaction(IsolationLevel.Unspecified);
}
catch (Exception e)
{
Debug.Assert(e.Message == "Unknown isolation level is not supported!");
}
try
{
conn.BeginTransaction(0);
}
catch (Exception e)
{
Debug.Assert(e.Message == "Unknown isolation level is not supported!");
}
}
}
public void CUBRIDCommand_Constructor_SQLAndConnAndTran_Test()
{
CUBRIDConnection conn = new CUBRIDConnection();
conn.ConnectionString = DBHelper.connString;
conn.Open();
conn.SetAutoCommit(false);
string sql = "drop table if exists t";
CUBRIDTransaction transaction = new CUBRIDTransaction(conn, CUBRIDIsolationLevel.TRAN_DEFAULT_ISOLATION);
CUBRIDCommand cmd = new CUBRIDCommand(sql, conn, transaction);
conn.BeginTransaction();
cmd.ExecuteNonQuery();
cmd.CommandText = "create table t (id int, name varchar(50))";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into t values(1, 'Nancy')";
cmd.ExecuteNonQuery();
conn.Commit();
cmd.CommandText = "select * from t";
CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader();
reader.Read();
Assert.AreEqual(2, reader.FieldCount);
Assert.AreEqual("1", reader.GetString(0));
Assert.AreEqual("Nancy", reader.GetString(1));
cmd.Close();
reader.Close();
conn.Close();
}