/*
* AMS.ADO Class Library
* Version 1.0
*
* Written by Alvaro Mendez
* Copyright (c) 2005. All Rights Reserved.
*
* The AMS.ADO.OleDb namespace contains OleDb related 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;
using System.Data.OleDb;
namespace AMS.ADO.OleDb
{
/// <summary>
/// Represents an SQL statement to be executed against an OLEDB data source. </summary>
/// <remarks>
/// This class encapsulates the OleDbCommand 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 : ICommand
{
#region Fields
/// <summary>
/// OleDbCommand object used for command execution. </summary>
protected OleDbCommand m_command = new OleDbCommand();
/// <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 OleDbConnection 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>
/// SQL sql = new SQL();
/// sql.ConnectionString = connectionString;
/// sql.CommandText = "INSERT INTO ...";
/// sql.Parameters.Add("?", 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="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.Add("?", value);
/// int count = sql.ExecuteInt(); </code></example></remarks>
public SQL(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 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="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.Add("?", value);
/// string description = sql.ExecuteString(); </code></example></remarks>
public SQL(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 OleDbCommand object
/// that will execute the command. </param>
/// <remarks>
/// This constructor offers a convenient way to create the SQL object when an
/// OleDbConnection 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="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.Add("?", value);
/// int rows = sql.ExecuteNonQuery(); </code></example></remarks>
public SQL(string sql, OleDbConnection 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 OleDbCommand object
/// that will execute the command. </param>
/// <param name="transaction">
/// The transaction object to be used by the internal OleDbCommand object
/// that will execute the command. </param>
/// <remarks>
/// This constructor offers a convenient way to create the SQL object when an
/// OleDbConnection object and an OleDbTransaction 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="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.Add("?", value);
/// sql.ExecuteNonQuery(); </code></example></remarks>
public SQL(string sql, OleDbConnection connection, OleDbTransaction 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 OleDbConnection object associated with the internal OleDbCommand
/// object used to execute the command. </summary>
public OleDbConnection Connection
{
get
{
return m_command.Connection;
}
}
/// <summary>
/// Gets or sets the OleDbConnection object associated with the internal OleDbCommand
/// object used to execute the command. </summary>
IDbConnection IDbCommand.Connection
{
get
{
return m_command.Connection;
}
set
{
m_command.Connection = (OleDbConnection)value;
}
}
/// <summary>
/// Gets the OleDbTransaction object associated with the internal OleDbCommand
/// object used to execute the command. </summary>
public OleDbTransaction Transaction
{
get
{
return m_command.Transaction;
}
}
/// <summary>
/// Gets or sets the OleDbTransaction object associated with the internal OleDbCommand
/// object used to execute the command. </summary>
IDbTransaction IDbCommand.Transaction
{
get
{
return m_command.Transaction;
}
set
{
m_command.Transaction = (OleDbTransaction)value;
}
}
/// <summary>
/// Gets the collection of OleDbParameter objects associated with the
/// internal OleDbCommand object used to execute the command. </summary>
public OleDbParameterCollection Parameters
{
get
{
return m_command.Parameters;
}
}
/// <summary>
/// Gets the collection of IDataParameter objects associated with the
/// internal OleDbCommand 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 OleDbCommand 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 OleDbConnection();
m_command.Connection.ConnectionString = m_connectionString;
m_command.Connection.Open();
}
/// <summary>
/// Disposes of the internal OleDbCommand 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 temporary database connection and executes the query by
/// calling ExecuteReader on the internal OleDbCommand object.</summary>
/// <returns>
/// The return value is an OleDbDataReader 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 OleDbDataReader
/// object is closed.
/// <example>Here's an example of its usage:
/// <code>
/// SQL sql = new SQL("SELECT * FROM ...", connectionString);
/// sql.Parameters.Add("?", value);
/// using (OleDbDataReader 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(string[])" />
/// <seealso cref="ExecuteNonQuery" />
public OleDbDataReader ExecuteReader()
{
return (OleDbDataReader)((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.Add("?", value);
/// DataSet ds = sql.ExecuteDataSet(); </code></example></remarks>
/// <seealso cref="ExecuteScalar" />
/// <seealso cref="ExecuteString" />
/// <seealso cref="ExecuteInt" />
/// <seealso cref="ExecuteReader" />
/// <seealso cref="ExecuteDataSet(DataSet, string[])" />
/// <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 sql. </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.Add("?", value);
/// sql.ExecuteDataSet(ds, "SomeTable"); </code></example></remarks>
/// <seealso cref="ExecuteScalar" />
/// <seealso cref="ExecuteString" />
/// <seealso cref="ExecuteInt" />
/// <seealso cref="ExecuteReader" />
/// <seealso cref="ExecuteDataSet(string[])" />
/// <seealso cref="ExecuteNonQuery" />
public DataSet ExecuteDataSet(DataSet ds, params string[] tables)
{
// Verify a DataSet object is valid
if (ds == null)
throw new ArgumentNullException("ds");
OleDbDataAdapter da = new OleDbDataAdapter();
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 OleDbCommand 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.Add("?", value);
/// int count = (int)sql.ExecuteScalar(); </code></example></remarks>
/// <seealso cref="ExecuteString" />
/// <seealso cref="ExecuteInt" />
/// <seealso cref="ExecuteReader" />
/// <seealso cref="ExecuteDataSet(string[])" />
/// <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.Add("?", value);
/// string name = sql.ExecuteString(); </code></example></remarks>
/// <seealso cref="ExecuteScalar" />
/// <seealso cref="ExecuteInt" />
/// <seealso cref="ExecuteReader" />
/// <seealso cref="ExecuteDataSet(string[])" />
/// <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.Add("?", value);
/// int age = sql.ExecuteInt(); </code></example></remarks>
/// <seealso cref="ExecuteScalar" />
/// <seealso cref="ExecuteString" />
/// <seealso cref="ExecuteReader" />
/// <seealso cref="ExecuteDataSet(string[])" />
/// <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 OleDbCommand 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.Add("?", value);
/// int rows = sql.ExecuteNonQuery(); </code></example></remarks>
/// <seealso cref="ExecuteScalar" />
/// <seealso cref="ExecuteInt" />
/// <seealso cref="ExecuteString" />
/// <seealso cref="ExecuteReader" />
/// <seealso cref="ExecuteDataSet(string[])" />
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 OleDbCommand 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 OleDbParameter object. </summary>
public OleDbParameter CreateParameter()
{
return (OleDbParameter)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
}
/// <summary>
/// Represents a stored procedure to be executed against an OLEDB data source. </summary>
/// <remarks>
/// This class encapsulates the OleDbCommand 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.OleDb.SQL.CommandText" />
/// and <see cref="AMS.ADO.OleDb.SQL.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 = "spGetEmployees";
/// sp.Parameters.Add("?", 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.OleDb.SQL.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.Add("?", 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.OleDb.SQL.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.Add("?", 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 OleDbCommand object
/// that will execute the statement. </param>
/// <remarks>
/// This constructor offers a convenient way to create the SQL object when an
/// OleDbConnection 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.OleDb.SQL.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.Add("?", value);
/// int count = sp.ExecuteInt(); </code></example></remarks>
public StoredProcedure(string name, OleDbConnection 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 OleDbCommand object
/// that will execute the statement. </param>
/// <param name="transaction">
/// The transaction object to be used by the internal OleDbCommand object
/// that will execute the statement. </param>
/// <remarks>
/// This constructor offers a convenient way to create the SQL object when an
/// OleDbConnection object and an OleDbTransaction 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.OleDb.SQL.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, transaction);
/// sp.Parameters.Add("?", value);
/// int count = sp.ExecuteInt(); </code></example></remarks>
public StoredProcedure(string name, OleDbConnection connection, OleDbTransaction transaction)
:
base(name, connection, transaction)
{
m_command.CommandType = CommandType.StoredProcedure;
}
#endregion // Constructors
}
}
Submit an article or tip