Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » Databases » Downloads
 
Add your own
alternative version
Go to top

Stop writing connection management code every time you access the database

, 6 Jan 2006
A simple class library for database access without the pervasive and often-fragile connection management details.
using System;
using System.Xml;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using NUnit.Framework;
using AMS.ADO.SqlClient;

namespace AMS.ADO.Test 
{
    /// <summary>
    ///   Test class for the SqlClient classes. </summary>
    [TestFixture]
    public class SqlClient : TestBase
	{
		private string connectionString = SQLCLIENT_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, SqlConnection) </summary>
		[Test]
		public void Constructor_sqlAndConnection()
		{
			string sql = "SELECT * FROM Test1";
			SqlConnection connection = new SqlConnection();

			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, SqlConnection, SqlTransaction) </summary>
		[Test]
		public void Constructor_sqlConnectionAndTransaction()
		{
			string sql = "SELECT * FROM Test1";
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				connection.Open();
				using (SqlTransaction 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";
			SqlConnection conn = new SqlConnection(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]);

			SqlConnection conn = new SqlConnection(connectionString);
			target = new SQL("SELECT Description FROM Test1 WHERE ID = @Value", conn);
			target.Parameters.Add("@Value", 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
			SqlDataReader 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
			SqlConnection conn = new SqlConnection(connectionString);
			target = new SQL("SELECT Description FROM Test1 WHERE ID = @Value", conn);
			target.Parameters.Add("@Value", 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 ExecuteXmlReader() </summary>
		[Test]
		public void ExecuteXmlReader()
		{
			SQL target = new SQL("SELECT * FROM Test1 FOR XML AUTO", connectionString);

			// Internal connection stays open until reader is closed
			XmlReader result = target.ExecuteXmlReader();
			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.Open, target.Connection.State);

			// Internal connection stays open until reader is closed
			result = target.ExecuteXmlReader();
			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.Open, target.Connection.State);
			target.Dispose();
			Assert.AreEqual(ConnectionState.Closed, target.Connection.State);

			// Explicit connection stays open until reader is closed
			SqlConnection conn = new SqlConnection(connectionString);
			target = new SQL("SELECT Description FROM Test1 WHERE ID = @Value FOR XML AUTO", conn);
			target.Parameters.Add("@Value", 2);
			result = target.ExecuteXmlReader();
			Assert.IsNull(target.ConnectionString);
			Assert.IsNotNull(target.Connection);
			Assert.AreEqual(ConnectionState.Open, target.Connection.State);
			result.Close();
			Assert.IsNotNull(target.Connection);
			Assert.AreEqual(ConnectionState.Open, target.Connection.State);

			// Explicit open connection stays open after reader is closed
			result = target.ExecuteXmlReader();
			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 FOR XML AUTO", conn))
			{
				result = target.ExecuteXmlReader();
				Assert.IsNull(target.ConnectionString);
				Assert.IsNotNull(target.Connection);
				Assert.AreEqual(ConnectionState.Open, target.Connection.State);
			}
			Assert.AreEqual(ConnectionState.Closed, target.Connection.State);

			// Internal connection is closed after query is disposed
			using (target = new SQL("SELECT Description FROM Test1 FOR XML AUTO", connectionString))
			{
				result = target.ExecuteXmlReader();
				Assert.AreEqual(connectionString, target.ConnectionString);
				Assert.IsNotNull(target.Connection);
				Assert.AreEqual(ConnectionState.Open, target.Connection.State);
			}
			Assert.AreEqual(ConnectionState.Closed, target.Connection.State);
		}

		/// <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";
			SqlConnection conn = new SqlConnection(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();
				SqlTransaction trans = conn.BeginTransaction();
				target = new StoredProcedure("spCreateTest1", conn, trans);
				target.Parameters.Add("@Description", "Inserted value");

				result = target.ExecuteNonQuery();
				Assert.AreEqual(ConnectionState.Open, conn.State);
				Assert.AreEqual(1, result);
				trans.Save("Test");

				result = target.ExecuteNonQuery();
				Assert.AreEqual(ConnectionState.Open, conn.State);
				Assert.AreEqual(1, result);

				trans.Rollback("Test");
				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

Share

About the Author

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#.

| Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 6 Jan 2006
Article Copyright 2006 by Alvaro Mendez
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid