/*
* 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
}
}
Submit an article or tip