using System;
using System.Data;
using System.Data.OleDb;
using NUnit.Framework;
using AMS.ADO.OleDb;
namespace AMS.ADO.Test
{
/// <summary>
/// Test class for the OleDb classes. </summary>
[TestFixture]
public class OleDb : TestBase
{
private string connectionString = OLEDB_CS;
/// <summary>
/// A test case for SQL() </summary>
[Test]
public void Constructor()
{
SQL target = new SQL();
Assert.IsNull(target.ConnectionString);
Assert.IsNull(target.Connection);
Assert.AreEqual("", target.CommandText);
}
/// <summary>
/// A test case for SQL(string) </summary>
[Test]
public void Constructor_sql()
{
SQL target = new SQL("SELECT * FROM Test1");
Assert.IsNull(target.ConnectionString);
Assert.IsNull(target.Connection);
Assert.IsNotNull(target.CommandText);
}
/// <summary>
/// A test case for SQL(string, OleDbConnection) </summary>
[Test]
public void Constructor_sqlAndConnection()
{
string sql = "SELECT * FROM Test1";
OleDbConnection connection = new OleDbConnection();
SQL target = new SQL(sql, connection);
Assert.IsNull(target.ConnectionString);
Assert.IsNotNull(target.Connection);
Assert.IsNotNull(target.CommandText);
}
/// <summary>
/// A test case for SQL(string, OleDbConnection, OleDbTransaction) </summary>
[Test]
public void Constructor_sqlConnectionAndTransaction()
{
string sql = "SELECT * FROM Test1";
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
connection.Open();
using (OleDbTransaction trans = connection.BeginTransaction())
{
SQL target = new SQL(sql, connection, trans);
Assert.IsNull(target.ConnectionString);
Assert.IsNotNull(target.Connection);
Assert.IsNotNull(target.CommandText);
}
}
}
/// <summary>
/// A test case for SQL(string, string) </summary>
[Test]
public void Constructor_sqlAndConnectionString()
{
string sql = "SELECT * FROM Test1";
SQL target = new SQL(sql, connectionString);
Assert.IsNotNull(target.ConnectionString);
Assert.IsNull(target.Connection);
Assert.IsNotNull(target.CommandText);
}
/// <summary>
/// A test case for ExecuteScalar() </summary>
[Test]
public void ExecuteScalar_connectionString()
{
string sql = "SELECT * FROM Test1";
SQL target = new SQL(sql, connectionString);
object result = target.ExecuteScalar();
Assert.AreEqual(connectionString, target.ConnectionString);
Assert.IsNotNull(target.Connection);
Assert.AreEqual(ConnectionState.Closed, target.Connection.State);
Assert.IsTrue(result is int);
Assert.AreEqual(1, (int)result);
target.CommandText = "SELECT COUNT(*) FROM Test1";
result = target.ExecuteScalar();
Assert.AreEqual(connectionString, target.ConnectionString);
Assert.IsNotNull(target.Connection);
Assert.AreEqual(ConnectionState.Closed, target.Connection.State);
Assert.IsTrue(result is int);
Assert.AreEqual(2, (int)result);
}
/// <summary>
/// A test case for ExecuteScalar() </summary>
[Test]
public void ExecuteScalar_connection()
{
string sql = "SELECT Description FROM Test1 WHERE ID = 2";
OleDbConnection conn = new OleDbConnection(connectionString);
SQL target = new SQL(sql, conn);
object result = target.ExecuteScalar();
Assert.AreEqual(ConnectionState.Closed, conn.State);
Assert.AreEqual(connectionString, conn.ConnectionString);
Assert.IsTrue(result is string);
Assert.AreEqual("This table may be deleted", (string)result);
conn.Open();
target.CommandText = "SELECT ID FROM Test1 WHERE ID = -1";
result = target.ExecuteScalar();
Assert.AreEqual(ConnectionState.Open, conn.State);
Assert.IsNull(result);
result = target.ExecuteScalar();
Assert.IsNull(result);
Assert.AreEqual(ConnectionState.Open, conn.State);
conn.Close();
result = target.ExecuteScalar();
Assert.IsNull(result);
Assert.AreEqual(ConnectionState.Closed, conn.State);
}
/// <summary>
/// A test case for ExecuteDataSet(params string[]) </summary>
[Test]
public void ExecuteDataSet_table()
{
SQL target = new SQL("SELECT * FROM Test1", connectionString);
DataSet result = target.ExecuteDataSet();
Assert.AreEqual(connectionString, target.ConnectionString);
Assert.IsNotNull(target.Connection);
Assert.AreEqual(ConnectionState.Closed, target.Connection.State);
Assert.AreEqual(1, result.Tables.Count);
Assert.AreEqual("Table", result.Tables[0].TableName);
Assert.AreEqual(2, result.Tables[0].Rows.Count);
Assert.AreEqual(1, result.Tables[0].Rows[0][0]);
target.CommandText = "SELECT COUNT(*) FROM Test1";
result = target.ExecuteDataSet();
Assert.AreEqual(connectionString, target.ConnectionString);
Assert.IsNotNull(target.Connection);
Assert.AreEqual(ConnectionState.Closed, target.Connection.State);
Assert.AreEqual(1, result.Tables.Count);
Assert.AreEqual("Table", result.Tables["Table"].TableName);
Assert.AreEqual(1, result.Tables[0].Rows.Count);
Assert.AreEqual(2, result.Tables[0].Rows[0][0]);
OleDbConnection conn = new OleDbConnection(connectionString);
target = new SQL("SELECT Description FROM Test1 WHERE ID = ?", conn);
target.Parameters.Add("?", 2);
result = target.ExecuteDataSet("T1");
Assert.AreEqual(ConnectionState.Closed, conn.State);
Assert.AreEqual(connectionString, conn.ConnectionString);
Assert.AreEqual(1, result.Tables.Count);
Assert.AreEqual("T1", result.Tables[0].TableName);
Assert.AreEqual(1, result.Tables["T1"].Rows.Count);
Assert.AreEqual("This table may be deleted", result.Tables[0].Rows[0][0]);
conn.Open();
target.CommandText = "SELECT ID FROM Test1 WHERE ID = -1";
result = target.ExecuteDataSet();
Assert.AreEqual(ConnectionState.Open, conn.State);
Assert.IsNotNull(result);
Assert.AreEqual(1, result.Tables.Count);
Assert.AreEqual("Table", result.Tables[0].TableName);
Assert.AreEqual(0, result.Tables[0].Rows.Count);
result = target.ExecuteDataSet("Test1");
Assert.AreEqual(ConnectionState.Open, conn.State);
Assert.IsNotNull(result);
Assert.AreEqual(1, result.Tables.Count);
Assert.AreEqual("Test1", result.Tables[0].TableName);
Assert.AreEqual(0, result.Tables["Test1"].Rows.Count);
conn.Close();
result = target.ExecuteDataSet();
Assert.AreEqual(ConnectionState.Closed, conn.State);
Assert.IsNotNull(result);
Assert.AreEqual(1, result.Tables.Count);
Assert.AreEqual("Table", result.Tables[0].TableName);
Assert.AreEqual(0, result.Tables[0].Rows.Count);
}
/// <summary>
/// A test case for ExecuteDataSet(DataSetClass, params string[]) </summary>
[Test]
public void ExecuteDataSet_dataSetAndTables()
{
SQL target = new SQL("SELECT * FROM Test1", connectionString);
DataSet result = target.ExecuteDataSet();
Assert.AreEqual(connectionString, target.ConnectionString);
Assert.IsNotNull(target.Connection);
Assert.AreEqual(ConnectionState.Closed, target.Connection.State);
Assert.AreEqual(1, result.Tables.Count);
Assert.AreEqual("Table", result.Tables[0].TableName);
Assert.AreEqual(2, result.Tables[0].Rows.Count);
Assert.AreEqual(1, result.Tables[0].Rows[0][0]);
target.CommandText = "SELECT * FROM Test2";
DataSet result2 = target.ExecuteDataSet(result);
Assert.AreEqual(connectionString, target.ConnectionString);
Assert.AreSame(result2, result);
Assert.AreEqual(1, result.Tables.Count);
Assert.AreEqual("Table", result.Tables[0].TableName);
Assert.AreEqual(6, result.Tables[0].Rows.Count);
Assert.AreEqual(DBNull.Value, result.Tables[0].Rows[0][2]);
target.CommandText = "SELECT * FROM Test2 ORDER BY 3";
result2 = target.ExecuteDataSet(result, "T2");
Assert.AreEqual(connectionString, target.ConnectionString);
Assert.AreSame(result2, result);
Assert.AreEqual(2, result.Tables.Count);
Assert.AreEqual("T2", result.Tables[1].TableName);
Assert.AreEqual(4, result.Tables[1].Rows.Count);
Assert.AreEqual(20, result.Tables[1].Rows[2][2]);
target = new StoredProcedure("spGetTests1And2", connectionString);
result = target.ExecuteDataSet("T1", "T2");
Assert.AreEqual(connectionString, target.ConnectionString);
Assert.AreEqual(2, result.Tables.Count);
Assert.AreEqual("T1", result.Tables[0].TableName);
Assert.AreEqual("T2", result.Tables[1].TableName);
target = new SQL("SELECT COUNT(*) FROM Test2", connectionString);
target.ExecuteDataSet(result, "T3");
Assert.AreEqual(connectionString, target.ConnectionString);
Assert.IsNotNull(target.Connection);
Assert.AreEqual(ConnectionState.Closed, target.Connection.State);
Assert.AreEqual(3, result.Tables.Count);
Assert.AreEqual("T3", result.Tables[2].TableName);
Assert.AreEqual(1, result.Tables[2].Rows.Count);
Assert.AreEqual(4, result.Tables["T3"].Rows[0][0]);
}
/// <summary>
/// A test case for ExecuteInt() </summary>
[Test]
public void ExecuteInt()
{
string sql = "SELECT * FROM Test1";
SQL target = new SQL(sql, connectionString);
int result = target.ExecuteInt();
Assert.AreEqual(connectionString, target.ConnectionString);
Assert.AreEqual(1, result);
target.CommandText = "SELECT COUNT(*) FROM Test1";
result = target.ExecuteInt();
Assert.AreEqual(connectionString, target.ConnectionString);
Assert.AreEqual(2, result);
target.CommandText = "SELECT * FROM Test1 WHERE ID = -1";
result = target.ExecuteInt();
Assert.AreEqual(connectionString, target.ConnectionString);
Assert.IsNotNull(target.Connection);
Assert.AreEqual(ConnectionState.Closed, target.Connection.State);
Assert.AreEqual(0, result);
target.CommandText = "SELECT Amount FROM Test2 WHERE Amount IS NULL";
result = target.ExecuteInt();
Assert.AreEqual(connectionString, target.ConnectionString);
Assert.IsNotNull(target.Connection);
Assert.AreEqual(ConnectionState.Closed, target.Connection.State);
Assert.AreEqual(0, result);
target.CommandText = "SELECT Description FROM Test1";
result = target.ExecuteInt();
Assert.AreEqual(connectionString, target.ConnectionString);
Assert.IsNotNull(target.Connection);
Assert.AreEqual(ConnectionState.Closed, target.Connection.State);
Assert.AreEqual(0, result);
}
/// <summary>
/// A test case for ExecuteString() </summary>
[Test]
public void ExecuteString()
{
string sql = "SELECT Description FROM Test1";
SQL target = new SQL(sql, connectionString);
string result = target.ExecuteString();
Assert.AreEqual(connectionString, target.ConnectionString);
Assert.AreEqual("Testing", result);
target.CommandText = "SELECT COUNT(*) FROM Test1";
result = target.ExecuteString();
Assert.AreEqual(connectionString, target.ConnectionString);
Assert.AreEqual("2", result);
target.CommandText = "SELECT * FROM Test1 WHERE ID = -1";
result = target.ExecuteString();
Assert.AreEqual(connectionString, target.ConnectionString);
Assert.IsNotNull(target.Connection);
Assert.AreEqual(ConnectionState.Closed, target.Connection.State);
Assert.IsNull(result);
target.CommandText = "SELECT Description FROM Test2 WHERE Description IS NULL";
result = target.ExecuteString();
Assert.AreEqual(connectionString, target.ConnectionString);
Assert.IsNotNull(target.Connection);
Assert.AreEqual(ConnectionState.Closed, target.Connection.State);
Assert.AreEqual("", result);
}
/// <summary>
/// A test case for ExecuteReader() </summary>
[Test]
public void ExecuteReader()
{
SQL target = new SQL("SELECT * FROM Test1", connectionString);
// Internal connection stays open until reader is closed
OleDbDataReader result = target.ExecuteReader();
Assert.AreEqual(connectionString, target.ConnectionString);
Assert.IsNotNull(target.Connection);
Assert.AreEqual(ConnectionState.Open, target.Connection.State);
result.Close();
Assert.AreEqual(connectionString, target.ConnectionString);
Assert.IsNotNull(target.Connection);
Assert.AreEqual(ConnectionState.Closed, target.Connection.State);
// Internal connection stays open until reader is disposed
using (result = target.ExecuteReader())
{
Assert.AreEqual(connectionString, target.ConnectionString);
Assert.IsNotNull(target.Connection);
Assert.AreEqual(ConnectionState.Open, target.Connection.State);
}
Assert.AreEqual(connectionString, target.ConnectionString);
Assert.IsNotNull(target.Connection);
Assert.AreEqual(ConnectionState.Closed, target.Connection.State);
// Explicit connection stays open until reader is closed
OleDbConnection conn = new OleDbConnection(connectionString);
target = new SQL("SELECT Description FROM Test1 WHERE ID = ?", conn);
target.Parameters.Add("?", 2);
result = target.ExecuteReader();
Assert.IsNull(target.ConnectionString);
Assert.IsNotNull(target.Connection);
Assert.AreEqual(ConnectionState.Open, target.Connection.State);
Assert.IsTrue(result.Read());
Assert.AreEqual("This table may be deleted", result.GetString(0));
result.Close();
Assert.IsNotNull(target.Connection);
Assert.AreEqual(ConnectionState.Closed, target.Connection.State);
// Explicit open connection stays open after reader is closed
conn.Open();
result = target.ExecuteReader();
Assert.IsNull(target.ConnectionString);
Assert.IsNotNull(target.Connection);
Assert.AreEqual(ConnectionState.Open, target.Connection.State);
result.Close();
Assert.IsNull(target.ConnectionString);
Assert.IsNotNull(target.Connection);
Assert.AreEqual(ConnectionState.Open, target.Connection.State);
conn.Close();
Assert.AreEqual(ConnectionState.Closed, target.Connection.State);
// Explicit connection is closed after query is disposed
using (target = new SQL("SELECT Description FROM Test1", conn))
{
result = target.ExecuteReader();
Assert.IsNull(target.ConnectionString);
Assert.IsNotNull(target.Connection);
Assert.AreEqual(ConnectionState.Open, target.Connection.State);
}
Assert.AreEqual(ConnectionState.Closed, target.Connection.State);
Assert.IsTrue(result.IsClosed);
// Internal connection is closed after query is disposed
using (target = new StoredProcedure("spGetTest1", connectionString))
{
result = target.ExecuteReader();
Assert.AreEqual(connectionString, target.ConnectionString);
Assert.IsNotNull(target.Connection);
Assert.AreEqual(ConnectionState.Open, target.Connection.State);
Assert.IsTrue(result.Read());
Assert.AreEqual("Testing", result.GetString(1));
Assert.IsTrue(result.Read());
Assert.AreEqual("This table may be deleted", result.GetString(1));
Assert.IsFalse(result.Read());
}
Assert.AreEqual(ConnectionState.Closed, target.Connection.State);
Assert.IsTrue(result.IsClosed);
}
/// <summary>
/// A test case for ExecuteNonQuery() </summary>
[Test]
public void ExecuteNonQuery()
{
string sql = "INSERT INTO Test1 VALUES('Inserted value')";
SQL target = new SQL(sql, connectionString);
int result = target.ExecuteNonQuery();
Assert.AreEqual(connectionString, target.ConnectionString);
Assert.IsNotNull(target.Connection);
Assert.AreEqual(ConnectionState.Closed, target.Connection.State);
Assert.AreEqual(1, result);
target.CommandText = "UPDATE Test2 SET Description = 'All Records'";
result = target.ExecuteNonQuery();
Assert.AreEqual(connectionString, target.ConnectionString);
Assert.IsNotNull(target.Connection);
Assert.AreEqual(ConnectionState.Closed, target.Connection.State);
Assert.AreEqual(4, result);
target.CommandText = "UPDATE Test2 SET Description = 'All Records' WHERE ID = -1";
result = target.ExecuteNonQuery();
Assert.AreEqual(connectionString, target.ConnectionString);
Assert.IsNotNull(target.Connection);
Assert.AreEqual(ConnectionState.Closed, target.Connection.State);
Assert.AreEqual(0, result);
sql = "DELETE FROM Test1 WHERE ID = 3";
OleDbConnection conn = new OleDbConnection(connectionString);
target = new SQL(sql, conn);
result = target.ExecuteNonQuery();
Assert.AreEqual(ConnectionState.Closed, conn.State);
Assert.AreEqual(connectionString, conn.ConnectionString);
Assert.AreEqual(1, result);
conn.Open();
result = target.ExecuteNonQuery();
Assert.AreEqual(ConnectionState.Open, conn.State);
Assert.AreEqual(0, result);
result = target.ExecuteNonQuery();
Assert.AreEqual(0, result);
Assert.AreEqual(ConnectionState.Open, conn.State);
conn.Close();
Assert.AreEqual(ConnectionState.Closed, conn.State);
result = target.ExecuteNonQuery();
Assert.AreEqual(0, result);
Assert.AreEqual(ConnectionState.Closed, conn.State);
using (conn)
{
conn.Open();
OleDbTransaction trans = conn.BeginTransaction();
target = new StoredProcedure("spCreateTest1", conn, trans);
target.Parameters.Add("?", "Inserted value");
result = target.ExecuteNonQuery();
Assert.AreEqual(ConnectionState.Open, conn.State);
Assert.AreEqual(1, result);
trans.Commit();
}
target = new SQL("UPDATE Test1 SET Description = 'All Records'", connectionString);
result = target.ExecuteNonQuery();
Assert.AreEqual(ConnectionState.Closed, target.Connection.State);
Assert.AreEqual(3, result);
}
}
}