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.SqlClient namespace contains SqlClient related classes
 * derived from AMS.ADO.Command.
 * 
 * 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.Data;
using System.Data.SqlClient;
using System.Xml;

namespace AMS.ADO.SqlClient
{
	/// <summary>
	///   Represents an SQL statement to be executed against an SQL Server database. </summary>
	/// <remarks>
	///   This class encapsulates the SqlCommand class with the added convenience of connection 
	///   management.  It automatically opens the connection if it needs to, uses it, and closes
	///   it if it's no longer needed.  This can significantly cut down the amount of repetitive 
	///   code normally required for connection management and helps to ensure that a connection 
	///   is not left open inadvertently. </remarks>
	public class SQL : AMS.ADO.Command<SqlConnection, SqlTransaction, SqlCommand, SqlParameter, SqlParameterCollection, SqlDataReader, SqlDataAdapter>
	{
		#region Constructors

		/// <summary>
		///   Constructs the object. </summary>
		/// <remarks>
		///   Creating the object this way requires that the <see cref="AMS.ADO.Command&lt;SqlConnection, SqlTransaction, SqlCommand, SqlParameter, SqlParameterCollection, SqlDataReader, SqlDataAdapter>.CommandText" /> 
		///   and <see cref="AMS.ADO.Command&lt;SqlConnection, SqlTransaction, SqlCommand, SqlParameter, SqlParameterCollection, SqlDataReader, SqlDataAdapter>.ConnectionString" /> properties be set before the command 
		///   can be executed. 
		///	  <example>Here's an example:
		///   <code> 
		///     SQL sql = new SQL();
		///	    sql.ConnectionString = connectionString;
		///     sql.CommandText = "INSERT INTO ...";
		///     sql.Parameters.AddWithValue("@P1", value);
		///     sql.ExecuteNonQuery(); </code></example></remarks>
		public SQL()
		{
		}

		/// <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="AMS.ADO.Command&lt;SqlConnection, SqlTransaction, SqlCommand, SqlParameter, SqlParameterCollection, SqlDataReader, SqlDataAdapter>.ConnectionString" /> 
		///   property be set before the command can be executed. 
		///   <example>Here's an example:
		///   <code> 
		///     SQL sql = new SQL("SELECT COUNT(*) FROM ...");
		///	    sql.ConnectionString = connectionString;
		///     sql.Parameters.AddWithValue("@P1", value);
		///     int count = sql.ExecuteInt(); </code></example></remarks>
		public SQL(string sql)
			:
			base(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 SQL 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="AMS.ADO.Command&lt;SqlConnection, SqlTransaction, SqlCommand, SqlParameter, SqlParameterCollection, SqlDataReader, SqlDataAdapter>.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> 
		///     SQL sql = new SQL("SELECT Description FROM ...", connectionString);
		///     sql.Parameters.AddWithValue("@P1", value);
		///     string description = sql.ExecuteString(); </code></example></remarks>
		public SQL(string sql, string connectionString)
			:
			base(sql, connectionString)
		{
		}

		/// <summary>
		///   Constructs the object and sets the text for the statement
		///   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 SqlCommand object
		///   that will execute the statement. </param>
		/// <remarks>
		///   This constructor offers a convenient way to create the SQL object when an
		///   SqlConnection object is already available.  The SQL 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="AMS.ADO.Command&lt;SqlConnection, SqlTransaction, SqlCommand, SqlParameter, SqlParameterCollection, SqlDataReader, SqlDataAdapter>.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> 
		///     SQL sql = new SQL("UPDATE ...", connection);
		///     sql.Parameters.AddWithValue("@P1", value);
		///     int rows = sql.ExecuteNonQuery(); </code></example></remarks>
		public SQL(string sql, SqlConnection connection)
			:
			base(sql, connection)
		{
		}

		/// <summary>
		///   Constructs the object and sets the text for the statement
		///   and the connection and transaction to use. </summary>
		/// <param name="sql">
		///   The text for the statement to be executed. </param>
		/// <param name="connection">
		///   The connection object to be used by the internal SqlCommand object
		///   that will execute the statement. </param>
		/// <param name="transaction">
		///   The transaction object to be used by the internal SqlCommand object
		///   that will execute the statement. </param>
		/// <remarks>
		///   This constructor offers a convenient way to create the SQL object when an
		///   SqlConnection object and an SqlTransaction object are already available.  The SQL 
		///   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="AMS.ADO.Command&lt;SqlConnection, SqlTransaction, SqlCommand, SqlParameter, SqlParameterCollection, SqlDataReader, SqlDataAdapter>.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> 
		///     SQL sql = new SQL("INSERT INTO ...", connection, transaction);
		///     sql.Parameters.AddWithValue("@P1", value);
		///     sql.ExecuteNonQuery(); </code></example></remarks>
		public SQL(string sql, SqlConnection connection, SqlTransaction transaction)
			:
			base(sql, connection, transaction)
		{
		}

		#endregion // Constructors

		#region Execute Methods

		/// <summary>
		///   Opens a temporary database connection and executes the query by
		///   calling ExecuteXmlReader on the internal SqlCommand object.</summary>
		/// <returns>
		///   The return value is an XmlReader object. </returns>
		/// <remarks>
		///   If a connection is created internally or a connection object was passed in 
		///   a Closed state, it will be automatically closed when the SqlDataReader
		///   object is closed. 
		///   <example>Here's an example of its usage:
		///   <code> 
        ///     using (SQL sql = new SQL("SELECT * FROM ... FOR XML AUTO", connectionString))
		///     {
		///       sql.Parameters.Add("@P1", value);
        ///       using (XmlReader reader = sql.ExecuteXmlReader())
		///       {
		///         ...
		///       } // disposed of reader 
		///     } // disposed of the query's connection </code></example></remarks>
		/// <seealso cref="AMS.ADO.Command&lt;SqlConnection, SqlTransaction, SqlCommand, SqlParameter, SqlParameterCollection, SqlDataReader, SqlDataAdapter>.ExecuteScalar" />
		/// <seealso cref="AMS.ADO.Command&lt;SqlConnection, SqlTransaction, SqlCommand, SqlParameter, SqlParameterCollection, SqlDataReader, SqlDataAdapter>.ExecuteString" />
		/// <seealso cref="AMS.ADO.Command&lt;SqlConnection, SqlTransaction, SqlCommand, SqlParameter, SqlParameterCollection, SqlDataReader, SqlDataAdapter>.ExecuteInt" />
		/// <seealso cref="AMS.ADO.Command&lt;SqlConnection, SqlTransaction, SqlCommand, SqlParameter, SqlParameterCollection, SqlDataReader, SqlDataAdapter>.ExecuteDataSet" />
		public XmlReader ExecuteXmlReader()
		{
			Connect();
			return m_command.ExecuteXmlReader();
		}

		#endregion // Execute Methods
	}

	/// <summary>
	///   Represents a stored procedure to be executed against an SQL Server database. </summary>
	/// <remarks>
	///   This class encapsulates the SqlCommand class (configured to execute a stored procedure) 
	///   with the added convenience of connection management.  It automatically opens the connection 
	///   if it needs to, uses it, and closes it if it's no longer needed.  This can significantly cut 
	///   down the amount of repetitive code normally required for connection management and helps 
	///   to ensure that a connection is not left open inadvertently. </remarks>
	public class StoredProcedure : SQL
	{
		#region Constructors

		/// <summary>
		///   Constructs the object. </summary>
		/// <remarks>
		///   Creating the object this way requires that the <see cref="AMS.ADO.Command&lt;SqlConnection, SqlTransaction, SqlCommand, SqlParameter, SqlParameterCollection, SqlDataReader, SqlDataAdapter>.CommandText" /> 
		///   and <see cref="AMS.ADO.Command&lt;SqlConnection, SqlTransaction, SqlCommand, SqlParameter, SqlParameterCollection, SqlDataReader, SqlDataAdapter>.ConnectionString" /> properties be set before the command 
		///   can be executed. 
		///	  <example>Here's an example:
		///   <code> 
		///     StoredProcedure sp = new StoredProcedure();
		///	    sp.ConnectionString = connectionString;
		///     sp.CommandText = "SELECT COUNT(*) FROM ...";
		///     sp.Parameters.AddWithValue("@P1", value);
		///     int count = sp.ExecuteInt(); </code></example></remarks>
		public StoredProcedure()
			:
			this("")
		{
		}

		/// <summary>
		///   Constructs the object and sets the text for the command. </summary>
		/// <param name="name">
		///   The name of the stored procedure to be executed. </param>
		/// <remarks>
		///   Creating the object this way requires that the <see cref="AMS.ADO.Command&lt;SqlConnection, SqlTransaction, SqlCommand, SqlParameter, SqlParameterCollection, SqlDataReader, SqlDataAdapter>.ConnectionString" /> 
		///   property be set before the command can be executed. 
		///   <example>Here's an example:
		///   <code> 
		///     StoredProcedure sp = new StoredProcedure("spGetEmployees");
		///	    sp.ConnectionString = connectionString;
		///     sp.Parameters.AddWithValue("@P1", value);
		///     int count = sp.ExecuteInt(); </code></example></remarks>
		public StoredProcedure(string name)
			:
			base(name)
		{
			m_command.CommandType = CommandType.StoredProcedure;
		}

		/// <summary>
		///   Constructs the object and sets the text for the command
		///   and the connection string. </summary>
		/// <param name="name">
		///   The name of the stored procedure 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 StoredProcedure 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="AMS.ADO.Command&lt;SqlConnection, SqlTransaction, SqlCommand, SqlParameter, SqlParameterCollection, SqlDataReader, SqlDataAdapter>.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> 
		///     StoredProcedure sp = new StoredProcedure("spGetEmployees", connectionString);
		///     sp.Parameters.AddWithValue("@P1", value);
		///     int count = sp.ExecuteInt(); </code></example></remarks>
		public StoredProcedure(string name, string connectionString)
			:
			base(name, connectionString)
		{
			m_command.CommandType = CommandType.StoredProcedure;
		}

		/// <summary>
		///   Constructs the object and sets the text for the statement
		///   and the connection to use. </summary>
		/// <param name="name">
		///   The name of the stored procedure to be executed. </param>
		/// <param name="connection">
		///   The connection object to be used by the internal SqlCommand object
		///   that will execute the statement. </param>
		/// <remarks>
		///   This constructor offers a convenient way to create the SQL object when an
		///   SqlConnection object is already available.  The SQL 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="AMS.ADO.Command&lt;SqlConnection, SqlTransaction, SqlCommand, SqlParameter, SqlParameterCollection, SqlDataReader, SqlDataAdapter>.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> 
		///     StoredProcedure sp = new StoredProcedure("spGetEmployees", connection);
		///     sp.Parameters.AddWithValue("@P1", value);
		///     int count = sp.ExecuteInt(); </code></example></remarks>
		public StoredProcedure(string name, SqlConnection connection)
			:
			base(name, connection)
		{
			m_command.CommandType = CommandType.StoredProcedure;
		}

		/// <summary>
		///   Constructs the object and sets the text for the statement
		///   and the connection and transaction to use. </summary>
		/// <param name="name">
		///   The name of the stored procedure to be executed. </param>
		/// <param name="connection">
		///   The connection object to be used by the internal SqlCommand object
		///   that will execute the statement. </param>
		/// <param name="transaction">
		///   The transaction object to be used by the internal SqlCommand object
		///   that will execute the statement. </param>
		/// <remarks>
		///   This constructor offers a convenient way to create the SQL object when an
		///   SqlConnection object and an SqlTransaction object are already available.  The SQL 
		///   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="AMS.ADO.Command&lt;SqlConnection, SqlTransaction, SqlCommand, SqlParameter, SqlParameterCollection, SqlDataReader, SqlDataAdapter>.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> 
		///     StoredProcedure sp = new StoredProcedure("SELECT COUNT(*) FROM ...", connection, transaction);
		///     sp.Parameters.AddWithValue("@P1", value);
		///     int count = sp.ExecuteInt(); </code></example></remarks>
		public StoredProcedure(string name, SqlConnection connection, SqlTransaction transaction)
			:
			base(name, connection, transaction)
		{
			m_command.CommandType = CommandType.StoredProcedure;
		}

		#endregion // Constructors
	}
}

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
Web03 | 2.8.141220.1 | Last Updated 6 Jan 2006
Article Copyright 2006 by Alvaro Mendez
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid