public void IsolationLevel_Transaction_Saved ()
{
conn = new SqlConnection (connectionString);
conn.Open ();
trans = conn.BeginTransaction ();
trans.Save ("SAVE1");
Assert.AreEqual (IsolationLevel.ReadCommitted, trans.IsolationLevel);
}
[Test] // Rollback ()
public void Rollback1 ()
{
string sql;
SqlCommand cmd = null;
SqlConnection connA = null;
SqlConnection connB = null;
try {
connA = new SqlConnection (connectionString);
connA.Open ();
connB = new SqlConnection (connectionString);
connB.Open ();
using (trans = connA.BeginTransaction ()) {
sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6666, 'NovellBangalore', '1989-02-11', '2005-07-22')";
cmd = new SqlCommand (sql, connA, trans);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6666", connA, trans);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
Assert.IsTrue (reader.Read (), "#A1");
Assert.AreEqual ("NovellBangalore", reader.GetString (0), "#A2");
Assert.IsFalse (reader.Read (), "#A3");
}
trans.Rollback ();
cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6666", connA);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
Assert.IsFalse (reader.Read (), "#B1");
}
cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6666", connB);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
Assert.IsFalse (reader.Read (), "#C1");
}
}
} finally {
if (cmd != null)
cmd.Dispose ();
if (connA != null)
connA.Close ();
if (connB != null)
connB.Close ();
conn = new SqlConnection (connectionString);
conn.Open ();
DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
}
try {
conn = new SqlConnection (connectionString);
conn.Open ();
trans = conn.BeginTransaction ();
sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6666, 'NovellBangalore', '1989-02-11', '2005-07-22')";
cmd = new SqlCommand (sql, conn, trans);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
trans.Save ("SAVE1");
sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6667, 'BangaloreNovell', '1999-03-10', '2006-08-23')";
cmd = new SqlCommand (sql, conn, trans);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
trans.Save ("SAVE2");
sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6668, 'Novell', '1997-04-07', '2003-06-25')";
cmd = new SqlCommand (sql, conn, trans);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
trans.Rollback ();
conn.Close ();
conn = new SqlConnection (connectionString);
conn.Open ();
cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6666", conn);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
Assert.IsFalse (reader.Read (), "#D1");
}
cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6667", conn);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
Assert.IsFalse (reader.Read (), "#E1");
}
cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6668", conn);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
Assert.IsFalse (reader.Read (), "#F1");
}
} finally {
if (cmd != null)
cmd.Dispose ();
if (trans != null)
trans.Dispose ();
if (conn != null)
conn.Close ();
conn = new SqlConnection (connectionString);
conn.Open ();
DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
}
try {
conn = new SqlConnection (connectionString);
conn.Open ();
trans = conn.BeginTransaction ();
trans.Rollback ();
} finally {
if (trans != null)
trans.Dispose ();
}
}
public void Dispose ()
{
string sql;
SqlCommand cmd = null;
try {
conn = new SqlConnection (connectionString);
conn.Open ();
trans = conn.BeginTransaction ();
sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6666, 'NovellBangalore', '1989-02-11', '2005-07-22')";
cmd = new SqlCommand (sql, conn, trans);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
trans.Save ("SAVE1");
sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6667, 'BangaloreNovell', '1999-03-10', '2006-08-23')";
cmd = new SqlCommand (sql, conn, trans);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
trans.Dispose ();
trans.Dispose ();
cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6666", conn);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
Assert.IsFalse (reader.Read (), "#1");
}
cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6667", conn);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
Assert.IsFalse (reader.Read (), "#2");
}
} finally {
if (cmd != null)
cmd.Dispose ();
if (trans != null)
trans.Dispose ();
if (conn != null)
conn.Close ();
conn = new SqlConnection (connectionString);
conn.Open ();
DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
}
}
public void IsolationLevel_Transaction_Rolledback ()
{
if (RunningOnMono)
Assert.Ignore ("NotWorking");
conn = new SqlConnection (connectionString);
conn.Open ();
trans = conn.BeginTransaction ();
trans.Rollback ();
try {
IsolationLevel iso = trans.IsolationLevel;
Assert.Fail ("#A1:" + iso);
} catch (InvalidOperationException ex) {
// This SqlTransaction has completed; it is no
// longer usable
Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
Assert.IsNull (ex.InnerException, "#A3");
Assert.IsNotNull (ex.Message, "#A4");
} finally {
trans.Dispose ();
}
trans = conn.BeginTransaction ();
trans.Save ("SAVE1");
trans.Rollback ("SAVE1");
Assert.AreEqual (IsolationLevel.ReadCommitted, trans.IsolationLevel, "#B1");
}
public void Connection_Transaction_Rolledback ()
{
if (RunningOnMono)
Assert.Ignore ("NotWorking");
conn = new SqlConnection (connectionString);
conn.Open ();
trans = conn.BeginTransaction ();
try {
trans.Rollback ();
Assert.IsNull (trans.Connection);
} finally {
trans.Dispose ();
}
trans = conn.BeginTransaction ();
trans.Save ("SAVE1");
try {
trans.Rollback ("SAVE1");
Assert.AreSame (conn, trans.Connection);
} finally {
trans.Dispose ();
}
}
public void Connection_Transaction_Saved ()
{
conn = new SqlConnection (connectionString);
conn.Open ();
trans = conn.BeginTransaction ();
trans.Save ("SAVE1");
Assert.AreSame (conn, trans.Connection);
}
public void Commit_Transaction_Rolledback ()
{
if (RunningOnMono)
Assert.Ignore ("NotWorking");
try {
conn = new SqlConnection (connectionString);
conn.Open ();
using (trans = conn.BeginTransaction ()) {
string sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6666, 'NovellBangalore', '1989-02-11', '2005-07-22')";
SqlCommand cmd = new SqlCommand (sql, conn, trans);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
trans.Rollback ();
try {
trans.Commit ();
Assert.Fail ("#A1");
} catch (InvalidOperationException ex) {
// This SqlTransaction has completed; it is no
// longer usable
Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
Assert.IsNull (ex.InnerException, "#A3");
Assert.IsNotNull (ex.Message, "#A4");
}
cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6666", conn);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
Assert.IsFalse (reader.Read (), "#B1");
}
}
using (trans = conn.BeginTransaction ()) {
string sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6666, 'NovellBangalore', '1989-02-11', '2005-07-22')";
SqlCommand cmd = new SqlCommand (sql, conn, trans);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
trans.Save ("SAVE1");
sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6667, 'BangaloreNovell', '1999-03-10', '2006-08-23')";
cmd = new SqlCommand (sql, conn, trans);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
trans.Rollback ("SAVE1");
trans.Commit ();
cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6666", conn);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
Assert.IsTrue (reader.Read (), "#D1");
Assert.AreEqual ("NovellBangalore", reader.GetString (0), "#D2");
Assert.IsFalse (reader.Read (), "#D3");
}
cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6667", conn);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
Assert.IsFalse (reader.Read (), "#E1");
}
}
} finally {
if (conn != null)
conn.Close ();
conn = new SqlConnection (connectionString);
conn.Open ();
DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
}
}
public void Save_TransactionName_Null ()
{
if (RunningOnMono)
Assert.Ignore ("NotWorking");
try {
conn = new SqlConnection (connectionString);
conn.Open ();
trans = conn.BeginTransaction ();
string sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6666, 'NovellBangalore', '1989-02-11', '2005-07-22')";
SqlCommand cmd = new SqlCommand (sql, conn, trans);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
try {
trans.Save ((string) null);
Assert.Fail ("#A1");
} catch (ArgumentException ex) {
// Invalid transaction or invalid name
// for a point at which to save within
// the transaction
Assert.AreEqual (typeof (ArgumentException), ex.GetType (), "#A2");
Assert.IsNull (ex.InnerException, "#A3");
Assert.IsNotNull (ex.Message, "#A4");
Assert.IsNull (ex.ParamName, "#A5");
}
trans.Commit ();
conn.Close ();
conn = new SqlConnection (connectionString);
conn.Open ();
cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6666", conn);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
Assert.IsTrue (reader.Read (), "#B1");
Assert.AreEqual ("NovellBangalore", reader.GetString (0), "#B2");
Assert.IsFalse (reader.Read (), "#B3");
}
} finally {
if (trans != null)
trans.Dispose ();
if (conn != null)
conn.Close ();
conn = new SqlConnection (connectionString);
conn.Open ();
DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
}
}
public void Save_Reader_Open ()
{
if (RunningOnMono)
Assert.Ignore ("NotWorking");
SqlCommand cmd;
conn = new SqlConnection (connectionString);
conn.Open ();
trans = conn.BeginTransaction ();
try {
string sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6666, 'NovellBangalore', '1989-02-11', '2005-07-22')";
cmd = new SqlCommand (sql, conn, trans);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
cmd = new SqlCommand ("select @@version", conn, trans);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
try {
trans.Save ("SAVE1");
Assert.Fail ("#1");
} catch (InvalidOperationException ex) {
// There is already an open DataReader
// associated with this Command which
// must be closed first
Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
Assert.IsNull (ex.InnerException, "#3");
Assert.IsNotNull (ex.Message, "#4");
}
}
} finally {
if (trans != null)
trans.Dispose ();
}
}
public void Save_Connection_Closed ()
{
try {
conn = new SqlConnection (connectionString);
conn.Open ();
trans = conn.BeginTransaction ();
string sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6666, 'NovellBangalore', '1989-02-11', '2005-07-22')";
SqlCommand cmd = new SqlCommand (sql, conn, trans);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
conn.Close ();
try {
trans.Save ("SAVE1");
Assert.Fail ("#A1");
} catch (InvalidOperationException ex) {
// This SqlTransaction has completed; it is no
// longer usable
Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
Assert.IsNull (ex.InnerException, "#A3");
Assert.IsNotNull (ex.Message, "#A4");
}
conn = new SqlConnection (connectionString);
conn.Open ();
cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6666", conn);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
Assert.IsFalse (reader.Read (), "#B1");
}
} finally {
if (trans != null)
trans.Dispose ();
if (conn != null)
conn.Close ();
conn = new SqlConnection (connectionString);
conn.Open ();
DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
}
}
[Test] // Rollback (String)
public void Rollback2_Transaction_Rolledback ()
{
if (RunningOnMono)
Assert.Ignore ("NotWorking");
try {
conn = new SqlConnection (connectionString);
conn.Open ();
using (trans = conn.BeginTransaction ()) {
string sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6666, 'NovellBangalore', '1989-02-11', '2005-07-22')";
SqlCommand cmd = new SqlCommand (sql, conn, trans);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
trans.Rollback ();
try {
trans.Rollback ("SAVE1");
Assert.Fail ("#A1");
} catch (InvalidOperationException ex) {
// This SqlTransaction has completed; it is no
// longer usable
Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
Assert.IsNull (ex.InnerException, "#A3");
Assert.IsNotNull (ex.Message, "#A4");
}
cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6666", conn);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
Assert.IsFalse (reader.Read (), "#B1");
}
}
using (trans = conn.BeginTransaction ()) {
string sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6666, 'NovellBangalore', '1989-02-11', '2005-07-22')";
SqlCommand cmd = new SqlCommand (sql, conn, trans);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
trans.Save ("SAVE1");
trans.Rollback ("SAVE1");
try {
trans.Rollback ("SAVE1");
Assert.Fail ("#C1");
} catch (SqlException ex) {
// Cannot roll back SAVE1. No transaction
// or savepoint of that name was found
Assert.AreEqual (typeof (SqlException), ex.GetType (), "#C2");
Assert.AreEqual ((byte) 16, ex.Class, "#C3");
Assert.IsNull (ex.InnerException, "#C4");
Assert.IsNotNull (ex.Message, "#C5");
Assert.IsTrue (ex.Message.IndexOf ("SAVE1") != -1, "#C6");
Assert.AreEqual (6401, ex.Number, "#C7");
Assert.AreEqual ((byte) 1, ex.State, "#C8");
}
cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6666", conn, trans);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
Assert.IsTrue (reader.Read (), "#D1");
}
}
} finally {
if (conn != null)
conn.Close ();
conn = new SqlConnection (connectionString);
conn.Open ();
DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
}
}
