public void TestNonQueryScalar()
{
using (ConnectionParam cnn = new ConnectionParam(myconnectionString, timeOut: 200))
{
AssertAreEqual(cnn.Connection, null, "La connexion");
AssertAreEqual(cnn.Transaction, null, "La transaction");
AssertAreEqual(cnn.TimeOut, 200, "Le timeOut");
cnn.ExecuteNonQuery(@"
IF (EXISTS (SELECT 1 FROM sys.tables WHERE name = '__test_unitaire__ConnectionParam'))
DROP TABLE __test_unitaire__ConnectionParam
");
AssertAreEqual(cnn.Connection, null, "La connexion");
AssertAreEqual(cnn.Transaction, null, "La transaction");
}
// connexion maintenue
using (ConnectionParam cnn = new ConnectionParam(myconnectionString, true))
{
AssertAreEqual(cnn.Connection, null, "La connexion");
AssertAreEqual(cnn.Transaction, null, "La transaction");
cnn.ExecuteNonQuery("CREATE TABLE __test_unitaire__ConnectionParam (id INT IDENTITY, nom VARCHAR(50))");
Assert.IsNotNull(cnn.Connection, "La connexion est perdue");
AssertAreEqual(cnn.Transaction, null, "La transaction");
List <SqlParameter> parameters = new List <SqlParameter>
{
new SqlParameter("nom", "Test1"),
new SqlParameter("toto", null),
new SqlParameter("x", SqlDbType.Int, 32, ParameterDirection.InputOutput, true, 1, 1, "s", DataRowVersion.Default, null),
new SqlParameter("y", SqlDbType.Int, 32, ParameterDirection.Input, true, 1, 1, "s", DataRowVersion.Default, null),
new SqlParameter("z", SqlDbType.Int, 32, ParameterDirection.Input, true, 1, 1, "s", DataRowVersion.Default, 2),
};
cnn.OnConnectionInfoMessage += Cnn_OnConnectionInfoMessageInsert;
cnn.ExecuteNonQuery("INSERT INTO __test_unitaire__ConnectionParam (nom) VALUES (@nom);", parameters: parameters);
cnn.OnConnectionInfoMessage -= Cnn_OnConnectionInfoMessageInsert;
Assert.IsNotNull(cnn.Connection, "La connexion est perdue");
AssertAreEqual(cnn.Transaction, null, "La transaction");
int id = cnn.ExecuteInsert("INSERT INTO __test_unitaire__ConnectionParam (nom) VALUES ('TEST2');");
AssertAreEqual(id, 2, "Id autoincrementé");
parameters = new List <SqlParameter>
{
new SqlParameter("id", 1)
};
int nb = cnn.ExecuteGetId("SELECT COUNT(*) + 3 FROM __test_unitaire__ConnectionParam WHERE id = @id", -1, System.Data.CommandType.Text, parameters);
Assert.IsNotNull(cnn.Connection, "La connexion est perdue");
AssertAreEqual(nb, 4, "Nombre de lignes + 3 remontées");
AssertAreEqual(cnn.Transaction, null, "La transaction");
}
}
public void TestGetId()
{
using (ConnectionParam cnn = new ConnectionParam(myconnectionString))
{
int nb = cnn.ExecuteGetId(@" SELECT NULL", -66);
AssertAreEqual(nb, -66, "Get ID Not Null");
}
using (ConnectionParam cnn = new ConnectionParam(myconnectionString))
{
int nb = cnn.ExecuteGetId(@" SELECT 'Robert'", -7);
AssertAreEqual(nb, -7, "Get ID Not Null");
}
}
public void TestTransaction()
{
using (ConnectionParam cnn = new ConnectionParam(myconnectionString))
{
cnn.BeginTransaction();
Assert.IsNotNull(cnn.Connection, "connexion nulle après begin trans");
AssertAreEqual(cnn.TransactionCount, 1, "Niveau de profondeur des transactions après begin trans");
cnn.ExecuteNonQuery(@"
IF (NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = '__test_unitaire__ConnectionParam_TRANSACTION'))
CREATE TABLE __test_unitaire__ConnectionParam_TRANSACTION (id int identity, nom varchar(50) NOT NULL);
");
Assert.IsNotNull(cnn.Connection, "connexion nulle après SQL");
AssertAreEqual(cnn.TransactionCount, 1, "Niveau de profondeur des transactions après sql");
cnn.CommitTransaction();
Assert.IsNull(cnn.Connection, "connexion nulle après commit");
AssertAreEqual(cnn.TransactionCount, 0, "Niveau de profondeur des transactions après commit");
}
using (ConnectionParam cnn = new ConnectionParam(myconnectionString))
{
int nbrows = cnn.ExecuteGetId("SELECT count(*) FROM __test_unitaire__ConnectionParam_TRANSACTION", 0);
cnn.BeginTransaction();
Assert.IsNotNull(cnn.Connection, "connexion nulle après begin trans N°2");
AssertAreEqual(cnn.TransactionCount, 1, "Niveau de profondeur des transactions après begin transN°2");
cnn.ExecuteNonQuery(@"INSERT INTO __test_unitaire__ConnectionParam_TRANSACTION (nom) VALUES ('toto')");
Assert.IsNotNull(cnn.Connection, "connexion nulle après SQL");
cnn.BeginTransaction();
AssertAreEqual(cnn.TransactionCount, 2, "Niveau de profondeur des transactions après begin trans Imbriqué");
cnn.ExecuteNonQuery(@"INSERT INTO __test_unitaire__ConnectionParam_TRANSACTION (nom) VALUES ('tata')");
int id = cnn.ExecuteGetId(@"SELECT SCOPE_IDENTITY()", -99);
Assert.AreNotEqual(id, -99, "SCOPE identity fail");
int nbrows2 = cnn.ExecuteGetId("SELECT count(*) FROM __test_unitaire__ConnectionParam_TRANSACTION", 0);
AssertAreEqual(nbrows + 2, nbrows2, "Nombre de lignes incohérent avant rollback");
cnn.CommitTransaction();
AssertAreEqual(cnn.TransactionCount, 1, "Niveau de profondeur des transactions après premier commit Imbriqué");
cnn.RollBackTransaction();
Assert.IsNull(cnn.Connection, "connexion nulle après roolback");
AssertAreEqual(cnn.TransactionCount, 0, "Niveau de profondeur des transactions après commit");
int nbrows3 = cnn.ExecuteGetId("SELECT count(*) FROM __test_unitaire__ConnectionParam_TRANSACTION", 0);
AssertAreEqual(nbrows, nbrows3, "Nombre de lignes incohérent après rollback");
}
// Test auto rollback (plus pour la couverture de code qu'autre chose !!
using (ConnectionParam cnn = new ConnectionParam(myconnectionString, true))
{
cnn.BeginTransaction();
}
}
