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

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.
/*
 * AMS.ADO Class Library
 * Version 1.0
 * 
 * Written by Alvaro Mendez
 * Copyright (c) 2005. All Rights Reserved.
 * 
 * The AMS.ADO namespace contains ADO related template classes.
 * 
 * The code is thoroughly documented, however, if you have any questions, 
 * feel free to email me at alvaromendez@consultant.com.  Also, if you 
 * decide to this in a commercial application I would appreciate an email 
 * message letting me know.
 *
 * This code may be used in compiled form in any way you desire. This
 * file may be redistributed unmodified by any means providing it is 
 * not sold for profit without the authors written consent, and 
 * providing that this notice and the authors name and all copyright 
 * notices remains intact. This file and the accompanying source code 
 * may not be hosted on a website or bulletin board without the author's 
 * written permission.
 * 
 * This file is provided "as is" with no expressed or implied warranty.
 * The author accepts no liability for any damage/loss of business that
 * this product may cause.
 *
 * Last Updated: Dec. 22, 2005
 */

using System;
using System.Data;

namespace AMS.ADO
{
    /// <summary>
    ///   Generic representation of an SQL command to be executed against a database. </summary>
    public interface ICommand : IDbCommand 
    {
		#region Properties

		/// <summary>
		///   Gets or sets the connection string to use when connecting to the 
		///   database where the command will be executed. </summary>
		string ConnectionString
		{
			get;
			set;
		}

		#endregion // Properties

		#region Execute Methods

		/// <summary>
		///   Opens a temporary database connection, executes the query, and 
		///   returns the results inside a DataSet object. </summary>
		/// <param name="tables">
		///   The array of names of the source tables to use for table mapping. </param>
		/// <returns>
		///   The return value is a DataSet object. </returns>
        DataSet ExecuteDataSet(params string[] tables);

		/// <summary>
		///   Opens a temporary database connection, executes the query, and 
		///   appends the results to a DataSet object. </summary>
		/// <param name="ds">
		///   The DataSet object to be filled with the results of the cmd. </param>
		/// <param name="tables">
		///   The array of names of the source tables to use for table mapping. </param>
        /// <returns>
        ///   The return value is the same DataSet object passed in as a parameter. </returns>
        DataSetClass ExecuteDataSet<DataSetClass>(DataSetClass ds, params string[] tables) where DataSetClass : DataSet;
		
		/// <summary>
        ///   Calls <see cref="IDbCommand.ExecuteScalar" /> and casts the result to a string. </summary>
		/// <returns>
		///   The return value is the first column of the first row in the result set
		///   converted to a string.  If the first column of the first row is DBNull.Value, the return
		///   value is an empty string.  If the result set is empty, the return value is null. </returns>
		string ExecuteString();

		/// <summary>
        ///   Calls <see cref="IDbCommand.ExecuteScalar" /> and casts the result to an int. </summary>
		/// <returns>
		///   The return value is the first column of the first row in the result set
		///   cast to an int.  If the first column of the first row is DBNull.Value or cannot be
		///   converted to an int, the return value is 0.  If the result set is empty, 
		///   the return value is 0.  </returns>
		int ExecuteInt();

		#endregion // Execute Methods
    }


	/// <summary>
	///   Generic class used to encapsulate an SQL command to be executed against a database. </summary>
    public class Command<ConnectionClass, TransactionClass, CommandClass, ParameterClass, ParameterCollectionClass, DataReaderClass, DataAdapterClass> : ICommand
		where ConnectionClass : class, IDbConnection, new()
		where TransactionClass : class, IDbTransaction
		where CommandClass : class, IDbCommand, new()
        where ParameterClass : class, IDbDataParameter
        where ParameterCollectionClass : class, IDataParameterCollection
        where DataReaderClass : class, IDataReader
        where DataAdapterClass : class, IDbDataAdapter, new()
    {
		#region Fields

		/// <summary>
		///   CommandClass object used for command execution. </summary>
		protected CommandClass m_command = new CommandClass();

		/// <summary>
		///   Indicator of whether the connection is already open when the command gets executed
		///   so that it can be left alone when Dispose is called. </summary>
		protected bool m_connectionWasOpen;

		/// <summary>
		///   String used by the ConnectionClass object to connect to the database. </summary>
		private string m_connectionString;

		#endregion // Fields

		#region Constructors

		/// <summary>
		///   Constructs the object. </summary>
		/// <remarks>
		///   Creating the object this way requires that the <see cref="CommandText" /> 
		///   and <see cref="ConnectionString" /> properties be set before the command 
		///   can be executed. 
		///	  <example>Here's an example:
		///   <code> 
		///     Command cmd = new Command();
		///	    cmd.ConnectionString = connectionString;
		///     cmd.CommandText = "INSERT INTO ...";
		///     cmd.Parameters.AddWithValue("@P1", value);
		///     cmd.ExecuteNonQuery(); </code></example></remarks>
		public Command()
		{
		}

		/// <summary>
		///   Constructs the object and sets the text for the command. </summary>
		/// <param name="sql">
		///   The text for the command to be executed. </param>
		/// <remarks>
		///   Creating the object this way requires that the <see cref="ConnectionString" /> 
		///   property be set before the command can be executed. 
		///   <example>Here's an example:
		///   <code> 
		///     Command cmd = new Command("SELECT COUNT(*) FROM ...");
		///	    cmd.ConnectionString = connectionString;
		///     cmd.Parameters.AddWithValue("@P1", value);
		///     int count = cmd.ExecuteInt(); </code></example></remarks>
		public Command(string sql)
		{
			m_command.CommandText = sql;
		}

		/// <summary>
		///   Constructs the object and sets the text for the command
		///   and the connection string. </summary>
		/// <param name="sql">
		///   The text for the command to be executed. </param>
		/// <param name="connectionString">
		///   The connection string to use when connecting to the database where
		///   the command will be executed. </param>
		/// <remarks>
		///   This constructor offers the most convenient way to create the Command object.  The
		///   SQL and connection string are passed together and commands may then be executed
		///   without worrying about connection management issues.
		///   After creating the object, use the <see cref="Parameters" /> property to 
		///   assign values to any of its parameters and one of the Execute 
		///   methods to run it.
		///   <example>Here's an example of its usage:
		///   <code> 
		///     Command cmd = new Command("SELECT Description FROM ...", connectionString);
		///     cmd.Parameters.AddWithValue("@P1", value);
		///     string description = cmd.ExecuteString(); </code></example></remarks>
		public Command(string sql, string connectionString)
			:
			this(sql)
		{
			ConnectionString = connectionString;
		}

		/// <summary>
		///   Constructs the object and sets the text for the command
		///   and the connection to use. </summary>
		/// <param name="sql">
		///   The text for the command to be executed. </param>
		/// <param name="connection">
		///   The connection object to be used by the internal CommandClass object
		///   that will execute the command. </param>
		/// <remarks>
		///   This constructor offers a convenient way to create the Command object when a
		///   ConnectionClass object is already available.  The Command object will automatically
		///   open the connection if necessary and return it to its original state when finished with it.
		///   After creating the object, use the <see cref="Parameters" /> property to 
		///   assign values to any of its parameters and one of the Execute 
		///   methods to run it.
		///   <example>Here's an example of its usage:
		///   <code> 
		///     Command cmd = new Command("UPDATE ...", connection);
		///     cmd.Parameters.AddWithValue("@P1", value);
		///     int rows = cmd.ExecuteNonQuery(); </code></example></remarks>
		public Command(string sql, ConnectionClass connection)
			:
			this(sql)
		{
			m_command.Connection = connection;
		}

		/// <summary>
		///   Constructs the object and sets the text for the command
		///   and the connection and transaction to use. </summary>
		/// <param name="sql">
		///   The text for the command to be executed. </param>
		/// <param name="connection">
		///   The connection object to be used by the internal CommandClass object
		///   that will execute the command. </param>
		/// <param name="transaction">
		///   The transaction object to be used by the internal CommandClass object
		///   that will execute the command. </param>
		/// <remarks>
		///   This constructor offers a convenient way to create the Command object when a
		///   ConnectionClass object and a TransactionClass object are already available.  The Command 
		///   object will automatically open the connection if necessary and return it to its original state when finished with it.
		///   After creating the object, use the <see cref="Parameters" /> property to 
		///   assign values to any of its parameters and one of the Execute 
		///   methods to run it.
		///   <example>Here's an example of its usage:
		///   <code> 
		///     Command cmd = new Command("INSERT INTO ...", connection, transaction);
		///     cmd.Parameters.AddWithValue("@P1", value);
		///     cmd.ExecuteNonQuery(); </code></example></remarks>
		public Command(string sql, ConnectionClass connection, TransactionClass transaction)
			:
			this(sql, connection)
		{
			m_command.Transaction = transaction;
		}

		#endregion // Constructors

		#region Properties

		/// <summary>
		///   Gets or sets the text for the command to be executed. </summary>
		public string CommandText
		{
			get
			{
				return m_command.CommandText;
			}
			set
			{
				m_command.CommandText = value;
			}
		}

		/// <summary>
		///   Gets or sets the type for the command to be executed. </summary>
		public CommandType CommandType
		{
			get
			{
				return m_command.CommandType;
			}
			set
			{
				m_command.CommandType = value;
			}
		}

		/// <summary>
		///   Gets or sets the timeout for the command to be executed. </summary>
		public int CommandTimeout
		{
			get
			{
				return m_command.CommandTimeout;
			}
			set
			{
				m_command.CommandTimeout = value;
			}
		}

		/// <summary>
		///   Gets or sets the connection string to use when connecting to the 
		///   database where the command will be executed. </summary>
		public string ConnectionString
		{
			get
			{
				return m_connectionString;
			}
			set
			{
				m_connectionString = value;
			}
		}

		/// <summary>
		///   Gets the ConnectionClass object associated with the internal CommandClass 
		///   object used to execute the command. </summary>
		public ConnectionClass Connection
		{
			get
			{
				return (ConnectionClass)m_command.Connection;
			}
		}

        /// <summary>
        ///   Gets or sets the IDbConnection object associated with the internal IDbCommand 
        ///   object used to execute the command. </summary>
        IDbConnection IDbCommand.Connection
        {
            get
            {
                return m_command.Connection;
            }
            set
            {
                m_command.Connection = value;
            }
        }

		/// <summary>
		///   Gets the TransactionClass object associated with the internal CommandClass 
		///   object used to execute the command. </summary>
		public TransactionClass Transaction
		{
			get
			{
				return (TransactionClass)m_command.Transaction;
			}
		}

		/// <summary>
		///   Gets or sets the IDbTransaction object associated with the internal IDbCommand 
		///   object used to execute the command. </summary>
        IDbTransaction IDbCommand.Transaction
		{
			get
			{
				return m_command.Transaction;
			}
            set
            {
                m_command.Transaction = value;
            }
        }

		/// <summary>
		///   Gets the collection of IDataParameter objects associated with the
		///   internal CommandClass object used to execute the command. </summary>
		public ParameterCollectionClass Parameters
		{
			get
			{
				return (ParameterCollectionClass)m_command.Parameters;
			}
		}

		/// <summary>
		///   Gets the collection of IDataParameter objects associated with the
		///   internal IDbCommand object used to execute the command. </summary>
        IDataParameterCollection IDbCommand.Parameters
		{
			get
			{
				return m_command.Parameters;
			}
		}

        /// <summary>
        ///   Gets or sets how command results are applied to the DataRow when 
        ///   used by the Update method of a DbDataAdapter. </summary>
        public UpdateRowSource UpdatedRowSource 
        {
            get
            {
                return m_command.UpdatedRowSource;
            }
            set
            {
                m_command.UpdatedRowSource = value;
            }
        }

		#endregion // Properties

		#region Connect and Dispose Methods

		/// <summary>
		///   Opens the connection of the internal CommandClass object, based on
		///   the <see cref="ConnectionString" /> or connection previously set. </summary>
		/// <exception cref="NullReferenceException">Both ConnectionString and Connection are null. </exception>
		protected virtual void Connect()
		{
			m_connectionWasOpen = false;

			// If the connection string is null, the connection must already exist
			if (m_connectionString == null)
			{
				// Verify a Connection object is available
				if (m_command.Connection == null)
					throw new NullReferenceException("Both ConnectionString and Connection are null; one of them is required for database connectivity.");

				// Open it if it's closed; otherwise remember that it's open
				if (m_command.Connection.State == ConnectionState.Closed)
					m_command.Connection.Open();
				else
					m_connectionWasOpen = true;
				return;
			}

			// Create and open the connection
			m_command.Connection = new ConnectionClass();
			m_command.Connection.ConnectionString = m_connectionString;
			m_command.Connection.Open();
		}

		/// <summary>
		///   Disposes of the internal CommandClass object's connection, if it was created locally;
		///   otherwise it is left in the same state as it was found. </summary>
		public virtual void Dispose()
		{
			if (m_command.Connection == null)
				return;

			// If the connection was previously open, leave it open
			// Otherwise, the connection was either created locally or originally closed; close it
			if (m_connectionWasOpen == false && m_command.Connection.State != ConnectionState.Closed)
				m_command.Connection.Close();
		}

		#endregion // Connect and Dispose Methods

		#region Execute Methods

		/// <summary>
		///   Opens a database connection and executes the query by
		///   calling ExecuteReader on the internal CommandClass object.</summary>
		/// <returns>
		///   The return value is a DataReaderClass object. </returns>
		/// <remarks>
		///   If a temporary connection is created or a connection was passed in 
		///   a Closed state, it will be automatically closed when the DataReaderClass
		///   object is closed. 
		///   <example>Here's an example of its usage:
		///   <code> 
        ///     SQL sql = new SQL("SELECT * FROM ...", connectionString);
        ///     sql.Parameters.AddWithValue("@P1", value);
        ///     using (SqlDataReader reader = sql.ExecuteReader())
		///     {
		///       ...
		///     }  // disposes of reader and the query's connection </code></example></remarks>
		/// <seealso cref="ExecuteScalar" />
		/// <seealso cref="ExecuteString" />
		/// <seealso cref="ExecuteInt" />
		/// <seealso cref="ExecuteDataSet" />
		/// <seealso cref="ExecuteNonQuery" />
		public DataReaderClass ExecuteReader()
		{
            return (DataReaderClass)((IDbCommand)this).ExecuteReader();
		}

        /// <summary>
        ///   Opens a database connection and executes the query by
        ///   calling ExecuteReader on the internal IDbCommand object.</summary>
        /// <returns>
        ///   The return value is an IDataReader object. </returns>
        /// <remarks>
        ///   If a temporary connection is created or a connection was passed in 
        ///   a Closed state, it will be automatically closed when the IDataReader
        ///   object is closed. </remarks> 
        IDataReader IDbCommand.ExecuteReader()
        {
            Connect();
            return m_command.ExecuteReader(m_connectionWasOpen ? CommandBehavior.Default : CommandBehavior.CloseConnection);
        }

        /// <summary>
        ///   Opens a database connection and executes the query by
        ///   calling ExecuteReader on the internal IDbCommand object.</summary>
        /// <param name="behavior">
        ///   One of the CommandBehavior values. </param>
        /// <returns>
        ///   The return value is an IDataReader object. </returns>
        IDataReader IDbCommand.ExecuteReader(CommandBehavior behavior)
        {
            Connect();
            return m_command.ExecuteReader(behavior);
        }

		/// <summary>
		///   Opens a temporary database connection, executes the query, and 
		///   returns the results inside a DataSet object. </summary>
		/// <param name="tables">
		///   The array of names of the source tables to use for table mapping. </param>
		/// <returns>
		///   The return value is a DataSet object. </returns>
		/// <remarks>
		///   If a temporary connection is created, it is automatically disposed of; otherwise
		///   it is opened and left in the same state as it was found. 
		///   <example>Here's an example of its usage:
		///   <code> 
        ///     SQL sql = new SQL("SELECT * FROM ...", connectionString);
		///     sql.Parameters.AddWithValue("@P1", value);
		///     DataSet ds = sql.ExecuteDataSet(); </code></example></remarks>
		/// <seealso cref="ExecuteScalar" />
		/// <seealso cref="ExecuteString" />
		/// <seealso cref="ExecuteInt" />
		/// <seealso cref="ExecuteReader" />
		/// <seealso cref="ExecuteNonQuery" />
		public DataSet ExecuteDataSet(params string[] tables)
		{
			return ExecuteDataSet(new DataSet(), tables);
		}

		/// <summary>
		///   Opens a temporary database connection, executes the query, and 
		///   appends the results to a DataSet object. </summary>
		/// <param name="ds">
		///   The DataSet object to be filled with the results of the cmd. </param>
		/// <param name="tables">
		///   The array of names of the source tables to use for table mapping. </param>
        /// <returns>
        ///   The return value is the same DataSet object passed in as a parameter. </returns>
        /// <remarks>
		///   If a temporary connection is created, it is automatically disposed of; otherwise
		///   it is opened and left in the same state as it was found. 
		///   <example>Here's an example of its usage:
		///   <code> 
		///     DataSet ds = new DataSet();
		///     ...
        ///     SQL sql = new SQL("SELECT * FROM SomeTable", connectionString);
		///     sql.Parameters.AddWithValue("@P1", value);
		///     sql.ExecuteDataSet(ds, "SomeTable"); </code></example></remarks>
		/// <seealso cref="ExecuteScalar" />
		/// <seealso cref="ExecuteString" />
		/// <seealso cref="ExecuteInt" />
		/// <seealso cref="ExecuteReader" />
		/// <seealso cref="ExecuteDataSet" />
		/// <seealso cref="ExecuteNonQuery" />
		public DataSetClass ExecuteDataSet<DataSetClass>(DataSetClass ds, params string[] tables) where DataSetClass : DataSet
		{
			// Verify a DataSet object is valid
			if (ds == null)
				throw new ArgumentNullException("ds");

			DataAdapterClass da = new DataAdapterClass();
			da.SelectCommand = m_command;

			// Add the table mappings, if any
			if (tables != null)
			{
				ITableMappingCollection mappings = da.TableMappings;
				string name = "Table";

				foreach (string table in tables)
				{
					mappings.Add(name, table);
					name = "Table" + mappings.Count;
				}
			}

			Connect();
			using (this)
			{
				da.Fill(ds);
				return ds;
			}
		}

		/// <summary>
		///   Opens a temporary database connection and executes the query by
		///   calling ExecuteScalar on the internal CommandClass object.</summary>
		/// <returns>
		///   The return value is the first column of the first row in the result set, 
		///   or null if the result set is empty. </returns>
		/// <remarks>
		///   If a temporary connection is created, it is automatically disposed of; otherwise
		///   it is opened and left in the same state as it was found. 
		///	  <example>Here's an example of its usage:
		///   <code> 
        ///     SQL sql = new SQL("SELECT COUNT(*) FROM ...", connectionString);
		///     sql.Parameters.AddWithValue("@P1", value);
		///     int count = (int)sql.ExecuteScalar(); </code></example></remarks>
		/// <seealso cref="ExecuteString" />
		/// <seealso cref="ExecuteInt" />
		/// <seealso cref="ExecuteReader" />
		/// <seealso cref="ExecuteDataSet" />
		/// <seealso cref="ExecuteNonQuery" />
		public object ExecuteScalar()
		{
			Connect();
			using (this)
				return m_command.ExecuteScalar();
		}

		/// <summary>
		///   Calls <see cref="ExecuteScalar" /> and casts the result to a string. </summary>
		/// <returns>
		///   The return value is the first column of the first row in the result set
		///   converted to a string.  If the first column of the first row is DBNull.Value, the return
		///   value is an empty string.  If the result set is empty, the return value is null. </returns>
		/// <remarks>
		///   If a temporary connection is created, it is automatically disposed of; otherwise
		///   it is opened and left in the same state as it was found. 
		///	  <example>Here's an example of its usage:
		///   <code> 
        ///     SQL sql = new SQL("SELECT name FROM ...", connectionString);
		///     sql.Parameters.AddWithValue("@P1", value);
		///     string name = sql.ExecuteString(); </code></example></remarks>
		/// <seealso cref="ExecuteScalar" />
		/// <seealso cref="ExecuteInt" />
		/// <seealso cref="ExecuteReader" />
		/// <seealso cref="ExecuteDataSet" />
		/// <seealso cref="ExecuteNonQuery" />
		public string ExecuteString()
		{
			object value = ExecuteScalar();
			return (value == null) ? null : value.ToString();
		}

		/// <summary>
		///   Calls <see cref="ExecuteScalar" /> and casts the result to an int. </summary>
		/// <returns>
		///   The return value is the first column of the first row in the result set
		///   cast to an int.  If the first column of the first row is DBNull.Value or cannot be
		///   converted to an int, the return value is 0.  If the result set is empty, 
		///   the return value is 0.  </returns>
		/// <remarks>
		///   If a temporary connection is created, it is automatically disposed of; otherwise
		///   it is opened and left in the same state as it was found. 
		///	  <example>Here's an example of its usage:
		///   <code> 
        ///     SQL sql = new SQL("SELECT age FROM ...", connectionString);
		///     sql.Parameters.AddWithValue("@P1", value);
		///     int age = sql.ExecuteInt(); </code></example></remarks>
		/// <seealso cref="ExecuteScalar" />
		/// <seealso cref="ExecuteString" />
		/// <seealso cref="ExecuteReader" />
		/// <seealso cref="ExecuteDataSet" />
		/// <seealso cref="ExecuteNonQuery" />
		public int ExecuteInt()
		{
			object value = ExecuteScalar();
			try
			{
				return Convert.ToInt32(value);
			}
			catch
			{
				return 0;
			}
		}

		/// <summary>
		///   Opens a temporary database connection and executes the command by
		///   calling ExecuteNonQuery on the internal CommandClass object. </summary>
		/// <returns>
		///   The return value is the number of rows affected. </returns>
		/// <remarks>
		///   If a temporary connection is created, it is automatically disposed of; otherwise
		///   it is opened and left in the same state as it was found. 
		///   <example>Here's an example of its usage:
		///   <code> 
        ///     SQL sql = new SQL("INSERT INTO ...", connection);
		///     sql.Parameters.AddWithValue("@P1", value);
		///     int rows = sql.ExecuteNonQuery(); </code></example></remarks>
		/// <seealso cref="ExecuteScalar" />
		/// <seealso cref="ExecuteInt" />
		/// <seealso cref="ExecuteString" />
		/// <seealso cref="ExecuteReader" />
		/// <seealso cref="ExecuteDataSet" />
		public int ExecuteNonQuery()
		{
			Connect();
			using (this)
				return m_command.ExecuteNonQuery();
		}

		#endregion // Execute Methods

        #region Other Methods

        /// <summary>
        ///   Attempts to cancels the execution of the internal CommandClass object. </summary>
        /// <remarks>
        ///   If there is nothing to cancel, nothing happens. However, if there is a command 
        ///   in process, and the attempt to cancel fails, no exception is generated. </remarks>
        public void Cancel()
        {
            m_command.Cancel();
        }

        /// <summary>
        ///   Creates a new instance of a ParameterClass object. </summary>        
        public ParameterClass CreateParameter()
        {
            return (ParameterClass)m_command.CreateParameter();
        }

        /// <summary>
        ///   Creates a new instance of an IDbDataParameter object. </summary>        
        IDbDataParameter IDbCommand.CreateParameter()
        {
            return m_command.CreateParameter();
        }

        /// <summary>
        ///   Opens a database connection and creates a prepared 
        ///   (or compiled) version of the command on the data source. </summary>
        public void Prepare()
        {
            Connect();
            m_command.Prepare();
        }

        #endregion // Other Methods
    }
}

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 | Terms of Use | Mobile
Web01 | 2.8.141216.1 | Last Updated 6 Jan 2006
Article Copyright 2006 by Alvaro Mendez
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid