// .NET Service Configuration V2.0 for Design Considerations for Service-Oriented Applications based on Windows Communication Foundation. Created with Microsoft .NET Framework 3.5 and Microsoft Visual Studio. Copyright 2008, Microsoft Corporation.

//======================================================================================================
//This file contains SQLHelper logic, largely derived/modified from the MSDN Data Access Block.  It 
//has the methods that perform ALL ADO.NET operations.
//======================================================================================================

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace Trade.Utility
{

    /// <summary>
    /// The SQLHelper class is intended to encapsulate high performance, 
    /// scalable best practices for common uses of SqlClient.
    /// </summary>
    public abstract class SQLHelper
    {

        // Hashtable to store cached parameters
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());


        /// <summary>
        /// Create and execute a command to return DataReader after binding to a single parameter.
        /// </summary>
        /// <param name="conn">Connection to execute against. If not open, it will be here.</param>
        /// <param name="trans">ADO transaction.  If null, will not be attached to the command</param>
        /// <param name="cmdType">Type of ADO command; such as Text or Procedure</param>
        /// <param name="cmdText">The actual SQL or the name of the Stored Procedure depending on command type</param>
        /// <param name="singleParm">The single SqlParameter object to bind to the query.</param>
        public static SqlDataReader ExecuteReaderSingleParm(SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter singleParm)
        {
            SqlCommand cmd = new SqlCommand();
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandText = cmdText;
            cmd.Parameters.Add(singleParm);
            SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleResult);
            return rdr;
        }

        /// <summary>
        /// Create and execute a command to return a single-row DataReader after binding to a single parameter.
        /// </summary>
        /// <param name="conn">Connection to execute against. If not open, it will be here.</param>
        /// <param name="trans">ADO transaction.  If null, will not be attached to the command</param>
        /// <param name="cmdType">Type of ADO command; such as Text or Procedure</param>
        /// <param name="cmdText">The actual SQL or the name of the Stored Procedure depending on command type</param>
        /// <param name="singleParm">The single SqlParameter object to bind to the query.</param>
        public static SqlDataReader ExecuteReaderSingleRowSingleParm(SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter singleParm)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandText = cmdText;
            cmd.Parameters.Add(singleParm);
            SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow);
            return rdr;
        }

        /// <summary>
        /// Create and execute a command to return a single-row DataReader after binding to multiple parameters.
        /// </summary>
        /// <param name="conn">Connection to execute against. If not open, it will be here.</param>
        /// <param name="trans">ADO transaction.  If null, will not be attached to the command</param>
        /// <param name="cmdType">Type of ADO command; such as Text or Procedure</param>
        /// <param name="cmdText">The actual SQL or the name of the Stored Procedure depending on command type</param>
        /// <param name="cmdParms">An array of SqlParameter objects to bind to the query.</param>
        public static SqlDataReader ExecuteReaderSingleRow(SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandText = cmdText;
            PrepareCommand(cmd, cmdParms);
            SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow);
            return rdr;
        }

        /// <summary>
        /// Create and execute a command to return a DataReader, no parameters used in the command.
        /// </summary>
        /// <param name="conn">Connection to execute against. If not open, it will be here.</param>
        /// <param name="trans">ADO transaction.  If null, will not be attached to the command</param>
        /// <param name="cmdType">Type of ADO command; such as Text or Procedure</param>
        /// <param name="cmdText">The actual SQL or the name of the Stored Procedure depending on command type</param>
        public static SqlDataReader ExecuteReaderNoParm(SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandText = cmdText;
            SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleResult);
            return rdr;
        }

        /// <summary>
        /// Create and execute a command to return a DataReader after binding to multiple parameters.
        /// </summary>
        /// <param name="conn">Connection to execute against. If not open, it will be here.</param>
        /// <param name="trans">ADO transaction.  If null, will not be attached to the command</param>
        /// <param name="cmdType">Type of ADO command; such as Text or Procedure</param>
        /// <param name="cmdText">The actual SQL or the name of the Stored Procedure depending on command type</param>
        /// <param name="cmdParms">An array of SqlParameter objects to bind to the query.</param>
        public static SqlDataReader ExecuteReader(SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandText = cmdText;
            PrepareCommand(cmd, cmdParms);
            SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleResult);
            return rdr;
        }

        /// <summary>
        /// Create and execute a command to return a single scalar (int) value after binding to multiple parameters.
        /// </summary>
        /// <param name="conn">Connection to execute against. If not open, it will be here.</param>
        /// <param name="trans">ADO transaction.  If null, will not be attached to the command</param>
        /// <param name="cmdType">Type of ADO command; such as Text or Procedure</param>
        /// <param name="cmdText">The actual SQL or the name of the Stored Procedure depending on command type</param>
        /// <param name="cmdParms">An array of SqlParameter objects to bind to the query.</param>
        public static int ExecuteScalar(SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = cmdText;
            cmd.Connection = conn;
            if (trans != null)
                cmd.Transaction = trans;
            PrepareCommand(cmd, cmdParms);
            int val = Convert.ToInt32(cmd.ExecuteScalar());
            return val;
        }


        /// <summary>
        /// Create and execute a command to return a single scalar (int) value after binding to a single parameter.
        /// </summary>
        /// <param name="conn">Connection to execute against. If not open, it will be here.</param>
        /// <param name="trans">ADO transaction.  If null, will not be attached to the command</param>
        /// <param name="cmdType">Type of ADO command; such as Text or Procedure</param>
        /// <param name="cmdText">The actual SQL or the name of the Stored Procedure depending on command type</param>
        /// <param name="singleParm">A SqlParameter object to bind to the query.</param>
        public static int ExecuteScalarSingleParm(SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter singleParm)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = cmdText;
            cmd.Connection = conn;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.Parameters.Add(singleParm);
            int val = Convert.ToInt32(cmd.ExecuteScalar());
            return val;
        }

        /// <summary>
        /// Create and execute a command to return a single scalar (int) value. No parameters will be bound to the command.
        /// </summary>
        /// <param name="conn">Connection to execute against. If not open, it will be here.</param>
        /// <param name="trans">ADO transaction.  If null, will not be attached to the command</param>
        /// <param name="cmdType">Type of ADO command; such as Text or Procedure</param>
        /// <param name="cmdText">The actual SQL or the name of the Stored Procedure depending on command type</param>
        /// <param name="singleParm">A SqlParameter object to bind to the query.</param>
        public static object ExecuteScalarNoParm(SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = cmdText;
            cmd.Connection = conn;
            if (trans != null)
                cmd.Transaction = trans;
            object val = cmd.ExecuteScalar();
            return val;
        }

        /// <summary>
        /// Create and execute a command that returns no result set after binding to multiple parameters.
        /// </summary>
        /// <param name="conn">Connection to execute against. If not open, it will be here.</param>
        /// <param name="trans">ADO transaction.  If null, will not be attached to the command</param>
        /// <param name="cmdType">Type of ADO command; such as Text or Procedure</param>
        /// <param name="cmdText">The actual SQL or the name of the Stored Procedure depending on command type</param>
        /// <param name="cmdParms">An array of SqlParameter objects to bind to the query.</param>
        public static int ExecuteNonQuery(SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandText = cmdText;
            PrepareCommand(cmd, cmdParms);
            int val = cmd.ExecuteNonQuery();
            return val;
        }

        /// <summary>
        /// Create and execute a command that returns no result set after binding to a single parameter.
        /// </summary>
        /// <param name="conn">Connection to execute against. If not open, it will be here.</param>
        /// <param name="trans">ADO transaction.  If null, will not be attached to the command</param>
        /// <param name="cmdType">Type of ADO command; such as Text or Procedure</param>
        /// <param name="cmdText">The actual SQL or the name of the Stored Procedure depending on command type</param>
        /// <param name="singleParam">A SqlParameter object to bind to the query.</param>
        public static int ExecuteNonQuerySingleParm(SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter singleParam)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandText = cmdText;
            cmd.Parameters.Add(singleParam);
            int val = cmd.ExecuteNonQuery();
            return val;
        }

        /// <summary>
        /// Create and execute a command that returns no result set after binding to a single parameter.
        /// </summary>
        /// <param name="conn">Connection to execute against. If not open, it will be here.</param>
        /// <param name="trans">ADO transaction.  If null, will not be attached to the command</param>
        /// <param name="cmdType">Type of ADO command; such as Text or Procedure</param>
        /// <param name="cmdText">The actual SQL or the name of the Stored Procedure depending on command type</param>
        /// <param name="singleParam">A SqlParameter object to bind to the query.</param>
        public static int ExecuteNonQueryNoParm(SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandText = cmdText;
            int val = cmd.ExecuteNonQuery();
            return val;
        }

        /// <summary>
        /// add parameter array to the cache
        /// </summary>
        /// <param name="cacheKey">Key to the parameter cache</param>
        /// <param name="cmdParms">an array of SqlParamters to be cached</param>
        public static void CacheParameters(string cacheKey, params SqlParameter[] cmdParms)
        {
            parmCache[cacheKey] = cmdParms;
        }

        /// <summary>
        /// Retrieve cached parameters
        /// </summary>
        /// <param name="cacheKey">key used to lookup parameters</param>
        /// <returns>Cached SqlParamters array</returns>
        public static SqlParameter[] GetCacheParameters(string cacheKey)
        {
            SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];

            if (cachedParms == null)
                return null;

            SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];

            for (int i = 0, j = cachedParms.Length; i < j; i++)
                clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();

            return clonedParms;
        }

        /// <summary>
        /// Prepare a command for execution
        /// </summary>
        /// <param name="cmd">SqlCommand object</param>
        /// <param name="conn">SqlConnection object</param>
        /// <param name="trans">SqlTransaction object</param>
        /// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
        /// <param name="cmdText">Command text, e.g. Select * from Products</param>
        /// <param name="cmdParms">SqlParameters to use in the command</param>
        private static void PrepareCommand(SqlCommand cmd, SqlParameter[] cmdParms)
        {
            if (cmdParms != null)
            {
                for (int i = 0; i < cmdParms.Length; i++)
                {
                    SqlParameter parm = (SqlParameter)cmdParms[i];
                    cmd.Parameters.Add(parm);
                }
            }
        }
    }
}