public void TestUpdateWithGeneratedKeys()
{
using (NuoDbConnection connection = new NuoDbConnection(connectionString))
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
DbCommand updateCommand = connection.CreateCommand();
updateCommand.CommandText = "update hockey set number = 99 where team = 'Bruins'";
DbDataReader reader = updateCommand.ExecuteReader();
Assert.IsNotNull(reader, "The command should return a generated keys recordset");
Assert.IsFalse(reader.Read(), "The generated keys recordset should be empty");
transaction.Rollback();
}
}
public void TestPreparedInsertWithGeneratedKeys2()
{
using (NuoDbConnection connection = new NuoDbConnection(connectionString))
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
DbCommand maxIdCmd = connection.CreateCommand();
maxIdCmd.CommandText = "select max(id) from hockey";
int maxId = (int)maxIdCmd.ExecuteScalar();
DbCommand updateCommand = connection.CreateCommand();
updateCommand.CommandText = "insert into hockey (number, name) values (?, ?)";
updateCommand.Prepare();
updateCommand.Parameters[0].Value = 99;
updateCommand.Parameters[1].Value = "xxx";
DbDataReader reader = updateCommand.ExecuteReader();
Assert.IsNotNull(reader, "The command should return a generated keys recordset");
Assert.IsTrue(reader.Read(), "There must be at least one ID in the generated keys recordset");
int lastId = (int)reader.GetValue(0);
Assert.IsTrue(lastId > maxId, "The generated ID must be greater than the existing ones");
DbCommand selectCommand = connection.CreateCommand();
selectCommand.CommandText = "select name from hockey where id = ?";
selectCommand.Parameters.Add(lastId);
string value = (string)selectCommand.ExecuteScalar();
Assert.AreEqual("xxx", value);
transaction.Rollback();
}
}
public void TestTransactions()
{
using (NuoDbConnection connection = new NuoDbConnection(connectionString))
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
DbCommand countCommand = connection.CreateCommand();
countCommand.CommandText = "select count(*) from hockey";
DbCommand updateCommand = connection.CreateCommand();
updateCommand.CommandText = "insert into hockey (number, name) values (99, 'xxxx')";
int count1 = (int)countCommand.ExecuteScalar();
updateCommand.ExecuteNonQuery();
int count2 = (int)countCommand.ExecuteScalar();
Assert.AreEqual(count2, count1 + 1);
transaction.Rollback();
int count3 = (int)countCommand.ExecuteScalar();
Assert.AreEqual(count3, count1);
}
}
public void TestInsertWithGeneratedKeys()
{
using (NuoDbConnection connection = new NuoDbConnection(connectionString))
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
DbCommand maxIdCmd = connection.CreateCommand();
maxIdCmd.CommandText = "select max(id) from hockey";
int maxId = (int)maxIdCmd.ExecuteScalar();
DbCommand updateCommand = connection.CreateCommand();
updateCommand.CommandText = "insert into hockey (number, name) values (99, 'xxxx')";
DbDataReader reader = updateCommand.ExecuteReader();
Assert.IsNotNull(reader, "The command should return a generated keys recordset");
Assert.IsTrue(reader.Read(), "There must be at least one ID in the generated keys recordset");
int lastId = (int)reader.GetValue(0);
Assert.IsTrue(lastId > maxId, "The generated ID must be greater than the existing ones");
transaction.Rollback();
}
}
