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(); } }