/*
* 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<SqlConnection, SqlTransaction, SqlCommand, SqlParameter, SqlParameterCollection, SqlDataReader, SqlDataAdapter>.CommandText" />
/// and <see cref="AMS.ADO.Command<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<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<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<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<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<SqlConnection, SqlTransaction, SqlCommand, SqlParameter, SqlParameterCollection, SqlDataReader, SqlDataAdapter>.ExecuteScalar" />
/// <seealso cref="AMS.ADO.Command<SqlConnection, SqlTransaction, SqlCommand, SqlParameter, SqlParameterCollection, SqlDataReader, SqlDataAdapter>.ExecuteString" />
/// <seealso cref="AMS.ADO.Command<SqlConnection, SqlTransaction, SqlCommand, SqlParameter, SqlParameterCollection, SqlDataReader, SqlDataAdapter>.ExecuteInt" />
/// <seealso cref="AMS.ADO.Command<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<SqlConnection, SqlTransaction, SqlCommand, SqlParameter, SqlParameterCollection, SqlDataReader, SqlDataAdapter>.CommandText" />
/// and <see cref="AMS.ADO.Command<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<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<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<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<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
}
}
Submit an article or tip