using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml;

namespace Neo.Afx.ComponentModel
{
    /// <summary>
    /// Groups some SQL Server utility methods.
    /// </summary>
    public static class SqlUtilities
    {
        #region Fetch DataTable

        /// <summary>
        /// Executes the given Transact-SQL statement or stored procedure 
        /// with the given (optional) parameters and returns a <code>DataTable</code> 
        /// for the result set returned by the query. 
        /// </summary>
        /// <param name="connectionString">The sql connection string.</param>
        /// <param name="commandText">The Transact-SQL statement or 
        /// stored procedure to execute.
        /// </param>
        /// <param name="commandType">A value indicating how the <code>commandText</code> 
        /// value is to be interpreted.
        /// </param>
        /// <param name="parameters">The array of <code>SqlParameters</code> 
        /// to be queried. 
        /// If <code>null</code> or zero-length then no parameters are added.
        /// </param>
        /// <returns>The result of the command in a <code>DataTable</code>.</returns>
        public static DataTable FetchDataTable(string connectionString, string commandText, CommandType commandType, params SqlParameter[] parameters)
        {
            return FetchDataTable(CreateCommand(connectionString, commandText, commandType, parameters));
        }

        /// <summary>
        /// Executes the given <code>SqlCommand</code> and returns a 
        /// <code>DataTable</code> for the result set returned by the 
        /// <code>SqlCommand</code>. 
        /// <remarks>
        /// This method closes and disposes the 
        /// <code>SqlConnection</code> and disposes the <code>SqlCommand</code> 
        /// when finished.
        /// </remarks>
        /// </summary>
        /// <returns>The result of the command in a <code>DataTable</code>.</returns>
        public static DataTable FetchDataTable(this SqlCommand command)
        {
            return ExecuteReader(command, (CommandBehavior.SchemaOnly & CommandBehavior.KeyInfo),
                delegate(SqlDataReader reader)
                {
                    var table = new DataTable();
                    table.Load(reader, LoadOption.OverwriteChanges);
                    return table;
                });
        }

        #endregion

        #region Create Command

        /// <summary>
        /// Creates a <code>SqlCommand</code> object with a new <code>SqlConnection</code>
        /// using the given connection string.
        /// </summary>
        /// <param name="connectionString">The sql connection string.</param>
        /// <param name="commandText">The Transact-SQL statement or 
        /// stored procedure to execute.
        /// </param>
        /// <param name="commandType">A value indicating how the commandText 
        /// value is to be interpreted.
        /// </param>
        /// <param name="parameters">The parameters of the Transact-SQL statement 
        /// or stored procedure. If <code>null</code> or zero-length then no parameters 
        /// are added.
        /// </param>
        /// <returns>A new <code>SqlCommand</code> object.</returns>
        public static SqlCommand CreateCommand(string connectionString, string commandText, CommandType commandType, params SqlParameter[] parameters)
        {
            var cmd = new SqlCommand
            {
                Connection = new SqlConnection(connectionString),
                CommandText = commandText,
                CommandType = commandType
            };

            if(parameters != null && parameters.Length > 0)
            {
                cmd.Parameters.AddRange(parameters);
            }
            return cmd;
        }

        #endregion

        #region Execute Command

        /// <summary>
        /// Executes the given command.
        /// <remarks>
        /// This method closes and disposes the 
        /// <code>SqlConnection</code> and disposes the <code>SqlCommand</code> 
        /// when finished.
        /// </remarks>
        /// </summary>
        /// <param name="command">The <code>SqlCommand</code> to be executed; 
        /// must have a valid connection.
        /// </param>
        /// <param name="handler">The handler to be invoked.</param>
        /// <returns>The result of invoking the <see cref="CommandHandler&lt;T&gt;"/></returns>
        /// <typeparam name="T">The type of result returned.</typeparam>
        /// <exception cref="ArgumentNullException">If handler is <code>null</code>.</exception>
        public static T ExecuteCommand<T>(this SqlCommand command, CommandHandler<T> handler)
        {
            if(handler != null)
            {
                T result;

                using(command)
                {
                    using(command.Connection)
                    {
                        command.Connection.Open();
                        result = handler(command);
                    } //"using" will close the connection even in case of exception.
                }

                return result;
            }
            throw new ArgumentNullException("handler");
        }

        #endregion

        #region ExecuteNonQuery Method

        /// <summary>
        /// Executes the given Transact-SQL statement or stored procedure with 
        /// the given (optional) parameters and returns the number of rows affected.
        /// </summary>
        /// <param name="connectionString">The sql connection string.</param>
        /// <param name="commandText">The Transact-SQL statement or 
        /// stored procedure to execute.
        /// </param>
        /// <param name="commandType">A value indicating how the commandText 
        /// value is to be interpreted.
        /// </param>
        /// <param name="parameters">The array of <code>SqlParameters</code> 
        /// to be queried. 
        /// If <code>null</code> or zero-length then no parameters are added.
        /// </param>
        /// <returns>The number of rows affected.</returns>
        public static int ExecuteNonQuery(string connectionString, string commandText, CommandType commandType, params SqlParameter[] parameters)
        {
            return ExecuteNonQuery(CreateCommand(connectionString, commandText, commandType, parameters));
        }

        /// <summary>
        /// Executes the given <code>SqlCommand</code> and returns the number 
        /// of rows affected.
        /// <remarks>
        /// This method closes and disposes the 
        /// <code>SqlConnection</code> and disposes the <code>SqlCommand</code> 
        /// when finished.
        /// </remarks>
        /// </summary>
        /// <param name="command">The <code>SqlCommand</code> to be executed.
        /// </param>
        /// <returns>The number of rows affected.</returns>
        public static int ExecuteNonQuery(this SqlCommand command)
        {
            return ExecuteCommand(command, cmd => cmd.ExecuteNonQuery());
        }

        #endregion

        #region ExecuteScalar Method

        /// <summary>
        /// Executes the given Transact-SQL statement or stored procedure 
        /// with the given (optional) parameters and returns the first column 
        /// of the first row in the result set returned by the query. 
        /// All other columns and rows are ignored. 
        /// </summary>
        /// <param name="connectionString">The sql connection string.</param>
        /// <param name="commandText">The Transact-SQL statement or 
        /// stored procedure to execute.
        /// </param>
        /// <param name="commandType">A value indicating how the commandText 
        /// value is to be interpreted.
        /// </param>
        /// <param name="parameters">The array of <code>SqlParameters</code> 
        /// to be queried. 
        /// If <code>null</code> or zero-length then no parameters are added.
        /// </param>
        /// <returns>The result of the Transact-SQL statement.</returns>
        /// <typeparam name="T">The type of the result returned.</typeparam>
        public static T ExecuteScalar<T>(string connectionString, string commandText, CommandType commandType, params SqlParameter[] parameters)
        {
            return ExecuteScalar<T>(CreateCommand(connectionString, commandText, commandType, parameters));
        }

        /// <summary>
        /// Executes the given <code>SqlCommand</code> and returns the first 
        /// column of the first row in the result set returned by the query. 
        /// All other columns and rows are ignored. 
        /// <remarks>
        /// This method closes and disposes the 
        /// <code>SqlConnection</code> and disposes the <code>SqlCommand</code> 
        /// when finished.
        /// </remarks>
        /// </summary>
        /// <param name="command">The <code>SqlCommand</code> to be executed.
        /// </param>
        /// <returns>The result of the Transact-SQL statement.</returns>
        /// <typeparam name="T">The type of the result returned.</typeparam>
        public static T ExecuteScalar<T>(this SqlCommand command)
        {
            return ExecuteCommand(command,
                delegate(SqlCommand cmd)
                {
                    var scalar = cmd.ExecuteScalar();
                    if(scalar.IsNull())
                    {
                        return default(T);
                    }
                    return (T)scalar;
                });
        }

        #endregion

        #region ExecuteReader Method

        /// <summary>
        /// Invokes <code>readerHandler</code> with the <code>SqlDataReader</code> 
        /// created for the given Transact-SQL statement or stored procedure 
        /// with the given (optional) parameters. 
        /// </summary>
        /// <param name="connectionString">The sql connection string.</param>
        /// <param name="commandText">The Transact-SQL statement or 
        /// stored procedure to execute.
        /// </param>
        /// <param name="commandType">A value indicating how the commandText 
        /// value is to be interpreted.
        /// </param>
        /// <param name="commandBehavior">One of the <code>CommandBehavior</code> 
        /// values.
        /// </param>
        /// <param name="handler">A method for consuming the generated reader.
        /// </param>
        /// <param name="parameters">The array of <code>SqlParameters</code> 
        /// to be queried. 
        /// If <code>null</code> or zero-length then no parameters are added.
        /// </param>
        /// <returns>The result of the Transact-SQL statement.</returns>
        /// <typeparam name="T">The type of the result returned.</typeparam>
        /// <exception cref="ArgumentNullException">If handler is <code>null</code>.</exception>
        public static T ExecuteReader<T>(string connectionString, string commandText, CommandType commandType, CommandBehavior commandBehavior, DataReaderHandler<T> handler, params SqlParameter[] parameters)
        {
            return ExecuteReader(CreateCommand(connectionString, commandText, commandType, parameters), commandBehavior, handler);
        }

        /// <summary>
        /// Invokes <code>readerHandler</code> with the <code>SqlDataReader</code> 
        /// created for the given <code>SqlCommand</code>.
        /// <remarks>
        /// This method closes and disposes the 
        /// <code>SqlConnection</code> and disposes the <code>SqlCommand</code> 
        /// after the <code>readerHandler</code> returns.
        /// </remarks>
        /// </summary>
        /// <param name="command">The <code>SqlCommand</code> to be executed.
        /// </param>
        /// <param name="commandBehavior">One of the <code>CommandBehavior</code> values.
        /// </param>
        /// <param name="handler">A method for consuming the generated reader.
        /// </param>
        /// <returns>The results of executing <code>readerHandler</code>.</returns>
        /// <typeparam name="T">The type of the result to be returned.</typeparam>
        /// <exception cref="ArgumentNullException">If handler is <code>null</code>.</exception>
        public static T ExecuteReader<T>(this SqlCommand command, CommandBehavior commandBehavior, DataReaderHandler<T> handler)
        {
            if(handler != null)
            {
                return ExecuteCommand(command,
                    delegate(SqlCommand cmd)
                    {
                        using(var reader = cmd.ExecuteReader(commandBehavior))
                        {
                            return handler(reader);
                        }
                    });
            }
            throw new ArgumentNullException("handler");
        }

        /// <summary>
        /// Crerates a <code>SqlDataReader</code> for the given Transact-SQL statement 
        /// or stored procedure with the given (optional) parameters.
        /// </summary>
        /// <param name="connectionString">The sql connection string.</param>
        /// <param name="commandText">The Transact-SQL statement or
        /// stored procedure to execute.</param>
        /// <param name="commandType">A value indicating how the commandText
        /// value is to be interpreted.</param>
        /// <param name="commandBehavior">One of the <code>CommandBehavior</code>
        /// values.</param>
        /// <param name="parameters">The array of <code>SqlParameters</code>
        /// to be queried.
        /// If <code>null</code> or zero-length then no parameters are added.</param>
        /// <returns>
        /// The result of the Transact-SQL statement.
        /// </returns>
        public static SqlDataReader ExecuteReader(string connectionString, string commandText, CommandType commandType, CommandBehavior commandBehavior, params SqlParameter[] parameters)
        {
            return ExecuteReader(CreateCommand(connectionString, commandText, commandType, parameters), commandBehavior);
        }

        /// <summary>
        /// Creates a <code>SqlDataReader</code> for the given <code>SqlCommand</code>.
        /// </summary>
        /// <param name="command">The <code>SqlCommand</code> to be executed.</param>
        /// <param name="commandBehavior">One of the <code>CommandBehavior</code> values.</param>
        /// <returns>
        /// The <code>SqlDataReader</code>.
        /// </returns>
        public static SqlDataReader ExecuteReader(this SqlCommand command, CommandBehavior commandBehavior)
        {
            if(command.Connection.State == ConnectionState.Closed)
            {
                command.Connection.Open();
            }
            return command.ExecuteReader(commandBehavior);
        }

        #endregion

        #region ExecuteXmlReader Method

        /// <summary>
        /// Invokes <code>readerHandler</code> with the <code>XmlReader</code> 
        /// created for the given Transact-SQL statement or stored procedure 
        /// with the given (optional) parameters. 
        /// </summary>
        /// <param name="connectionString">The sql connection string.</param>
        /// <param name="commandText">The Transact-SQL statement or 
        /// stored procedure to execute.
        /// </param>
        /// <param name="commandType">A value indicating how the commandText 
        /// value is to be interpreted.
        /// </param>
        /// <param name="handler">A method for consuming the generated reader.
        /// </param>
        /// <param name="parameters">The array of <code>SqlParameters</code> 
        /// to be queried. 
        /// If <code>null</code> or zero-length then no parameters are added.
        /// </param>
        /// <returns>The result of the Transact-SQL statement.</returns>
        /// <typeparam name="T">The type of the result returned.</typeparam>
        /// <exception cref="ArgumentNullException">If handler is <code>null</code>.</exception>
        public static T ExecuteReader<T>(string connectionString, string commandText, CommandType commandType, XmlReaderHandler<T> handler, params SqlParameter[] parameters)
        {
            return ExecuteReader(CreateCommand(connectionString, commandText, commandType, parameters), handler);
        }

        /// <summary>
        /// Invokes <code>readerHandler</code> with the <code>XmlReader</code> 
        /// created for the given <code>SqlCommand</code>.
        /// <remarks>
        /// This method closes and disposes the 
        /// <code>SqlConnection</code> and disposes the <code>SqlCommand</code> 
        /// after the <code>readerHandler</code> returns.
        /// </remarks>
        /// </summary>
        /// <param name="command">The <code>SqlCommand</code> to be executed.
        /// </param>
        /// <param name="handler">A method for consuming the generated reader.
        /// </param>
        /// <returns>The results of executing <code>readerHandler</code>.</returns>
        /// <typeparam name="T">The type of the result to be returned.</typeparam>
        /// <exception cref="ArgumentNullException">If handler is <code>null</code>.</exception>
        public static T ExecuteReader<T>(this SqlCommand command, XmlReaderHandler<T> handler)
        {
            if(handler != null)
            {
                return ExecuteCommand(command,
                    delegate(SqlCommand cmd)
                    {
                        using(var reader = cmd.ExecuteXmlReader())
                        {
                            return handler(reader);
                        }
                    });
            }
            throw new ArgumentNullException("handler");
        }

        #endregion

        #region AddParameters Method

        /// <summary>
        /// Adds the given parameters to the given parameterized command.
        /// </summary>
        /// <param name="command">The parameterized command to be queried.</param>
        /// <param name="parameters">The parameters of the Transact-SQL statement 
        /// or stored procedure. May be <code>null</code> or must contain 
        /// <code>SqlParameter</code>'s or an even number of 
        /// <code>parameterName</code>-<code>parameterValue</code> pairs.
        /// </param>
        public static void AddParameters(this SqlCommand command, params object[] parameters)
        {
            if(parameters != null && parameters.Length > 0)
            {
                for(var i = 0; i < parameters.Length; i += 2)
                {
                    if(parameters[i] is SqlParameter)
                    {
                        command.Parameters.Add((SqlParameter)parameters[i]);
                        if(i < parameters.Length)
                        {
                            command.Parameters.Add((SqlParameter)parameters[i + 1]);
                        }
                    }
                    else
                    {
                        command.Parameters.AddWithValue(parameters[i].ToString(), parameters[i + 1]);
                    }
                }
            }
        }

        #endregion

        #region Convert To DbType

        /// <summary>
        /// Converts the given <code>Type</code> to a <code>DbType</code>.
        /// </summary>
        /// <param name="type">The type to be queried.</param>
        /// <returns>A <code>DbType</code>.</returns>
        public static DbType ConvertToDbType(Type type)
        {
            // HACK : ToDbType

            if(type == typeof(Int16))
            {
                return DbType.Int16;
            }
            if(type == typeof(Int32))
            {
                return DbType.Int32;
            }
            if(type == typeof(Int64))
            {
                return DbType.Int64;
            }
            if(type == typeof(Boolean))
            {
                return DbType.Boolean;
            }
            if(type == typeof(Byte))
            {
                return DbType.Byte;
            }
            if(type == typeof(DateTime))
            {
                return DbType.DateTime;
            }
            if(type == typeof(Double))
            {
                return DbType.Double;
            }
            if(type == typeof(Decimal))
            {
                return DbType.Decimal;
            }
            if(type == typeof(Guid))
            {
                return DbType.Guid;
            }
            if(type == typeof(SByte))
            {
                return DbType.SByte;
            }
            if(type == typeof(Single))
            {
                return DbType.Single;
            }
            if(type == typeof(String))
            {
                return DbType.AnsiString; // varchar
            }
            if(type == typeof(XmlDocument))
            {
                return DbType.Xml;
            }

            return DbType.Object;

            //object dummyInstance = Activator.CreateInstance(type);

            // We don't care what type of parameter we use; 
            // the parameter is going to get thrown away. 
            // Basically, we're creating a pseudointerface 
            // to code hidden inside the parameter constructor.
            //SqlParameter dummyParameter = new SqlParameter("dummyname", dummyInstance);

            //return dummyParameter.DbType;
        }

        #endregion
    }

    #region Delegate Declarations

    /// <summary>
    /// A delegate for a method that processes an <code>SqlDataReader</code> 
    /// and returns the results of that process.
    /// </summary>
    /// <param name="reader">The <code>SqlDataReader</code> to be queried.</param>
    /// <returns>The results of processing the <code>SqlDataReader</code>.</returns>
    /// <typeparam name="T">The type of the result returned.</typeparam>
    public delegate T DataReaderHandler<out T>(SqlDataReader reader);

    /// <summary>
    /// A delegate for a method that handles an <code>XmlReader</code> 
    /// and returns the results of that process.
    /// </summary>
    /// <param name="reader">The <code>XmlReader</code> to be queried.</param>
    /// <returns>The results of processing the <code>XmlReader</code>.</returns>
    /// <typeparam name="T">The type of the result returned.</typeparam>
    public delegate T XmlReaderHandler<out T>(XmlReader reader);

    /// <summary>
    /// A delegate for a method that handles an <code>IDbCommand</code> 
    /// and returns the results of that process.
    /// </summary>
    /// <param name="command">The <code>IDbCommand</code> to be executed.</param>
    /// <returns>The results of processing the <code>IDbCommand</code>.</returns>
    /// <typeparam name="T">The type of the result returned.</typeparam>
    public delegate T CommandHandler<out T>(SqlCommand command);

    #endregion
}