public void TestTransaction()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
try
{
conn.BeginTransaction(IsolationLevel.ReadUncommitted);
Assert.Fail();
}
catch (SnowflakeDbException e)
{
Assert.AreEqual(270009, e.ErrorCode);
}
IDbTransaction tran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
IDbCommand command = conn.CreateCommand();
command.Transaction = tran;
command.CommandText = "create or replace table testtransaction(cola string)";
command.ExecuteNonQuery();
command.Transaction.Commit();
command.CommandText = "show tables like 'testtransaction'";
IDataReader reader = command.ExecuteReader();
Assert.IsTrue(reader.Read());
Assert.IsFalse(reader.Read());
// start another transaction to test rollback
tran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
command.Transaction = tran;
command.CommandText = "insert into testtransaction values('test')";
command.ExecuteNonQuery();
command.CommandText = "select * from testtransaction";
reader = command.ExecuteReader();
Assert.IsTrue(reader.Read());
Assert.AreEqual("test", reader.GetString(0));
command.Transaction.Rollback();
// no value will be in table since it has been rollbacked
command.CommandText = "select * from testtransaction";
reader = command.ExecuteReader();
Assert.IsFalse(reader.Read());
conn.Close();
}
}
public void TestConnectionDispose()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
// Setup
conn.ConnectionString = ConnectionString;
conn.Open();
IDbCommand command = conn.CreateCommand();
command.CommandText = "create or replace table testConnDispose(c int)";
command.ExecuteNonQuery();
IDbTransaction t1 = conn.BeginTransaction();
IDbCommand t1c1 = conn.CreateCommand();
t1c1.Transaction = t1;
t1c1.CommandText = "insert into testConnDispose values (1)";
t1c1.ExecuteNonQuery();
}
using (IDbConnection conn = new SnowflakeDbConnection())
{
// Previous connection would be disposed and
// uncommitted txn would rollback at this point
conn.ConnectionString = ConnectionString;
conn.Open();
IDbCommand command = conn.CreateCommand();
command.CommandText = "SELECT * FROM testConnDispose";
IDataReader reader = command.ExecuteReader();
Assert.IsFalse(reader.Read());
// Cleanup
command.CommandText = "DROP TABLE IF EXISTS testConnDispose";
command.ExecuteNonQuery();
}
}
// Test that when a transaction is disposed, rollback would be sent out
public void TestTransactionDispose()
{
var conn = new SnowflakeDbConnection();
try
{
conn.ConnectionString = ConnectionString;
conn.Open();
IDbCommand command = conn.CreateCommand();
command.CommandText = "create or replace table testTransactionDispose(c int)";
command.ExecuteNonQuery();
using (IDbTransaction t1 = conn.BeginTransaction())
{
IDbCommand t1c1 = conn.CreateCommand();
t1c1.Transaction = t1;
t1c1.CommandText = "insert into testTransactionDispose values (1)";
t1c1.ExecuteNonQuery();
}
// Transaction t1 would be disposed and rollback at this point, tuple inserted is not visible
IDbCommand c2 = conn.CreateCommand();
c2.CommandText = "SELECT * FROM testTransactionDispose";
IDataReader reader2 = c2.ExecuteReader();
Assert.IsFalse(reader2.Read());
}
finally
{
IDbCommand command = conn.CreateCommand();
command.CommandText = "DROP TABLE IF EXISTS testTransactionDispose";
command.ExecuteNonQuery();
conn.Close();
}
}
public void CDC() // Put in arguments from form selection as parameters
{
using (IDbConnection snowConn = new SnowflakeDbConnection())
{
// Open the connection
snowConn.ConnectionString = new Connection().SnowConnectInfo;
snowConn.Open();
// Declare the command and transactions which will be used throughout the entire batch job.
IDbCommand cmd = snowConn.CreateCommand();
IDbTransaction transaction;
// Start the transaction
transaction = snowConn.BeginTransaction();
// Must assign both transaction object and connection
// to Command object for a pending local transaction
cmd.Connection = snowConn;
cmd.Transaction = transaction;
SnowLog.LoggingInfo loginfo = new SnowLog.LoggingInfo("", "", "", "", "", "", 0);
try
{
var op = new CdcSQL();
op.SetJobstart(cmd);
logger.SetLogStart(cmd);
loginfo.Step = "Step 1";
loginfo.StepRowsAffected = op.HdsDelete(cmd, "Customer");
logger.SuccessLog(snowConn, loginfo);
logger.SetLogStart(cmd);
loginfo.Step = "Step 2";
loginfo.StepRowsAffected = op.HdsUpdate(cmd, "Customer");
logger.SuccessLog(snowConn, loginfo);
logger.SetLogStart(cmd);
loginfo.Step = "Step 3";
loginfo.StepRowsAffected = op.HdsAdd(cmd, "Customer");
logger.SuccessLog(snowConn, loginfo);
MessageBox.Show("Add");
op.TruncateLanding(cmd, "Customer");
transaction.Commit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
loginfo.StepRowsAffected = 0;
logger.FailLog(snowConn, loginfo);
transaction.Rollback();
}
snowConn.Close();
}
}
// Constructor - Make the necessary table for log storage if it does not exist.
public SnowLog(string connectInfo)
{
using (IDbConnection c = new SnowflakeDbConnection())
{
c.ConnectionString = connectInfo;
c.Open();
// Declare the command and transactions which will be used throughout the entire batch job.
IDbCommand cmd = c.CreateCommand();
IDbTransaction logTransaction;
// Start the transaction
logTransaction = c.BeginTransaction();
// Must assign both transaction object and connection
// to Command object for a pending local transaction
cmd.Connection = c;
cmd.Transaction = logTransaction;
try
{
// Ensure the Datetime columns will have the proper data type.
cmd.CommandText = "ALTER SESSION SET timestamp_type_mapping = timestamp_ltz;";
cmd.ExecuteReader();
cmd.CommandText = "CREATE TABLE IF NOT EXISTS Log.TransactionJobTracking ( " +
"BatchID Date, " +
"JobDatabase string, " +
"JobName string, " +
"JobRunStart TIMESTAMP, " +
"Step string, " +
"StepLabel string, " +
"StepDescription string, " +
"StepTargetSchema string, " +
"StepTargetTable string, " +
"StepRowsAffected int, " +
"StepStatus string, " +
"StepStartDatetime TIMESTAMP, " +
"StepEndDatetime TIMESTAMP, " +
"StepDuration int); ";
cmd.ExecuteReader();
logTransaction.Commit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
logTransaction.Rollback();
}
c.Close();
}
}
private void DeleteHDSButton_Click(object sender, EventArgs e)
{
using (IDbConnection snowConn = new SnowflakeDbConnection())
{
// Open the connection
snowConn.ConnectionString = new Connection().SnowConnectInfo;
snowConn.Open();
// Declare the command and transactions which will be used throughout the entire batch job.
IDbCommand cmd = snowConn.CreateCommand();
IDbTransaction transaction;
// Start the transaction
transaction = snowConn.BeginTransaction();
// Must assign both transaction object and connection
// to Command object for a pending local transaction
cmd.Connection = snowConn;
cmd.Transaction = transaction;
try
{
MessageBox.Show(DateTime.Now.ToString());
cmd.CommandText = "INSERT INTO Log.Date " +
"VALUES (" + DateTime.Now.ToString() + ") ";
cmd.ExecuteReader();
transaction.Commit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
transaction.Rollback();
}
snowConn.Close();
}
}