Click here to Skip to main content
Click here to Skip to main content
Search within:




Articles » Database » Database » Databases » Downloads
 
Comments & Discussions

Stop writing connection management code every time you access the database

By , 6 Jan 2006
 
ams_ado_doc.zip
AMS.ADO.chm
ams_ado_src.zip
ADO.csproj
AMS.ADO.sln
bin
Release
AMS.ADO.dll
AMS.ADO.xml
ClassDiagram.cd
Command.cs
NET1.1
ADO.NET1.1.csproj
AMS.ADO.NET1.1.sln
AssemblyInfo.cs
bin
Release
AMS.ADO.dll
AMS.ADO.xml
Command.cs
Odbc.cs
OleDb.cs
OracleClient.cs
SqlClient.cs
Odbc.cs
OleDb.cs
OracleClient.cs
Properties
AssemblyInfo.cs
SqlClient.cs
Test
AssemblyInfo.cs
Base.cs
Odbc.cs
OleDb.cs
SqlClient.cs
Test.NET1.1.csproj
 
using System;
using System.Data;
using System.Data.Odbc;
using NUnit.Framework;
using AMS.ADO.Odbc;

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

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

			OdbcConnection conn = new OdbcConnection(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
			OdbcDataReader 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
			OdbcConnection conn = new OdbcConnection(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";
			OdbcConnection conn = new OdbcConnection(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();
				OdbcTransaction trans = conn.BeginTransaction();

				target = new StoredProcedure("{call 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 use 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

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

About Article
A simple class library for database access without the pervasive and often-fragile connection management details.
Type Article
Licence 
First Posted 6 Jan 2006
Views 106,380
Bookmarked 76 times

, +
Top News

Skills that self-taught computer programmers lack

Get the Insider News free each morning.
Related Videos
Using ADO.NET for beginners
LightSwitch Timetable Management Application
Database Helper v 2.0.0
A simple guide to WMI providers
NHibernate Best Practices with ASP.NET, 1.2nd Ed.
Exploring Session in ASP.NET
Watchdog Management
The Missing Link
Using ADO.NET in MFC Projects
RECEME - Remote Command Execution through eMail Exchange
Introduction to Object-Oriented Tiered Application Design
13 disasters for production web sites and their solutions
Automatic Object Persistency – Part I
Simulate a Windows Service using ASP.NET to run scheduled jobs
Use SQL Parameters to Overcome Ad Hoc Performance Issues
Windows XP IIS Manager v1.7
Object Oriented Design Principles
Speeding up database access - Part 1: Missing indexes
CRasMonitor v1.41
Find "Leaked" Database Connections in ASP.NET Web Applications
Permalink | Advertise | Privacy | Mobile
Web01 | 2.6.130617.1 | Last Updated 6 Jan 2006
Article Copyright 2006 by Alvaro Mendez
Everything else Copyright © CodeProject, 1999-2013
Terms of Use
Layout: fixed | fluid