Click here to Skip to main content
15,881,173 members
Search within:


Articles / Programming Languages / SQL
 

Stop writing connection management code every time you access the database

Rate me:
Please Sign up or sign in to vote.
3.88/5 (18 votes)
6 Jan 20068 min read 161.2K   2.3K   80  
A simple class library for database access without the pervasive and often-fragile connection management details.
 
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);
		}
	}
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Alvaro Mendez
Web Developer
United States United States
I've done extensive work with C++, MFC, COM, and ATL on the Windows side. On the Web side, I've worked with VB, ASP, JavaScript, and COM+. I've also been involved with server-side Java, which includes JSP, Servlets, and EJB, and more recently with ASP.NET/C#.

Comments and Discussions

Permalink
Advertise
Privacy
Cookies
Terms of Use
Layout: fixed | fluid

Posted 6 Jan 2006

Article Copyright 2006 by Alvaro Mendez
Everything else Copyright © CodeProject, 1999-2024

Web03 2.8:2024-04-02:1