using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

namespace StrongCoders.Data
{
    public class Library
    {
        public static void AddDataParametersToDbCommand(IDbCommand command, IDataParameter[] parameters)
        {
            foreach (IDataParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
        }

        public static TParameter CreateInputParameter<TParameterValue, TParameter>(string parameterName, TParameterValue parameterValue)
            where TParameter : IDbDataParameter, new()
        {
            TParameter parameter = new TParameter();
            parameter.ParameterName = parameterName;
            parameter.Value = parameterValue;
            parameter.Direction = ParameterDirection.Input;
            return parameter;
        }

        public static TParameter CreateInputParameterWithCheck<TParameterValue, TParameter>(string parameterName, TParameterValue valueToCheckFor, TParameterValue parameterValue)
            where TParameterValue : IComparable<TParameterValue>
            where TParameter : IDbDataParameter, new()
        {
            TParameter parameter = new TParameter();
            parameter.ParameterName = parameterName;
            parameter.Direction = ParameterDirection.Input;
            if (parameterValue == null || parameterValue.CompareTo(valueToCheckFor) == 0)
            {
                parameter.Value = DBNull.Value;
            }
            else
            {
                parameter.Value = parameterValue;
            }
            return parameter;
        }

        public static TParameter CreateOutputParameter<TParameter>(string parameterName, DbType dbType)
            where TParameter : IDbDataParameter, new()
        {
            TParameter parameter = new TParameter();
            parameter.ParameterName = parameterName;
            parameter.DbType = dbType;
            parameter.Direction = ParameterDirection.Output;
            return parameter;
        }

        public static void ExecuteNonQuery<TConnection, TCommand, TParameter>(string commandText, bool isProcedure, string connectionString)
            where TConnection : IDbConnection, new()
            where TCommand : IDbCommand, new()
            where TParameter : DbParameter, new()
        {
            ExecuteNonQuery<TConnection, TCommand, TParameter>(commandText, isProcedure, connectionString, null);
        }

        public static void ExecuteNonQuery<TConnection, TCommand, TParameter>(string commandText, bool isProcedure, string connectionString, params TParameter[] parameters)
            where TConnection : IDbConnection, new()
            where TCommand : IDbCommand, new()
            where TParameter : DbParameter, new()
        {
            using (TConnection connection = new TConnection())
            {
                connection.ConnectionString = connectionString;
                using (TCommand command = new TCommand())
                {
                    command.CommandText = commandText;
                    command.Connection = connection;
                    if (isProcedure)
                    {
                        command.CommandType = CommandType.StoredProcedure;
                    }
                    if (!IsArrayNullOrEmpty(parameters))
                    {
                        AddDataParametersToDbCommand(command, parameters);
                    }
                    command.Connection.Open();
                    command.ExecuteNonQuery();
                    command.Connection.Close();
                }
            }
        }

        public static TReturnValueType ExecuteScalar<TConnection, TCommand, TParameter, TReturnValueType>(string commandText, bool isProcedure, string connectionString)
            where TConnection : IDbConnection, new()
            where TCommand : IDbCommand, new()
            where TParameter : DbParameter, new()
        {
            return ExecuteScalar<TConnection, TCommand, TParameter, TReturnValueType>(commandText, isProcedure, connectionString, null);
        }

        public static TReturnValueType ExecuteScalar<TConnection, TCommand, TParameter, TReturnValueType>(string commandText, bool isProcedure, string connectionString, params TParameter[] parameters)
            where TConnection : IDbConnection, new()
            where TCommand : IDbCommand, new()
            where TParameter : DbParameter, new()
        {
            using (TConnection connection = new TConnection())
            {
                connection.ConnectionString = connectionString;
                using (TCommand command = new TCommand())
                {
                    command.CommandText = commandText;
                    command.Connection = connection;
                    TReturnValueType returnValue = default(TReturnValueType);
                    if (isProcedure)
                    {
                        command.CommandType = CommandType.StoredProcedure;
                    }
                    if (!IsArrayNullOrEmpty(parameters))
                    {
                        AddDataParametersToDbCommand(command, parameters);
                    }
                    command.Connection.Open();
                    returnValue = (TReturnValueType)command.ExecuteScalar();
                    command.Connection.Close();
                    return returnValue;
                }
            }
        }

        public static void FillDataAdapter<TCommand, TDataAdapter>(DataTable dataSource, TCommand selectCommand)
            where TCommand : DbCommand
            where TDataAdapter : DbDataAdapter, new()
        {
            TDataAdapter dataAdapter = new TDataAdapter();
            dataAdapter.SelectCommand = selectCommand;
            dataAdapter.Fill(dataSource);
        }

        public static void FillDataAdapter<TCommand, TDataAdapter>(DataSet dataSource, TCommand selectCommand)
            where TCommand : DbCommand
            where TDataAdapter : DbDataAdapter, new()
        {
            TDataAdapter dataAdapter = new TDataAdapter();
            dataAdapter.SelectCommand = selectCommand;
            dataAdapter.Fill(dataSource);
        }

        public static DataTable FillDataTable<TConnection, TCommand, TParameter, TDataAdapter>(string commandText, string connectionString, bool isProcedure)
            where TConnection : DbConnection, new()
            where TCommand : DbCommand, new()
            where TParameter : DbParameter, new()
            where TDataAdapter : DbDataAdapter, new()
        {
            return FillDataTable<TConnection, TCommand, TParameter, TDataAdapter>(commandText, isProcedure, string.Empty, connectionString, null);
        }

        public static DataTable FillDataTable<TConnection, TCommand, TParameter, TDataAdapter>(string commandText, bool isProcedure, string connectionString, params TParameter[] parameters)
            where TConnection : DbConnection, new()
            where TCommand : DbCommand, new()
            where TParameter : DbParameter, new()
            where TDataAdapter : DbDataAdapter, new()
        {
            return FillDataTable<TConnection, TCommand, TParameter, TDataAdapter>(commandText, isProcedure, string.Empty, connectionString, parameters);
        }

        public static DataTable FillDataTable<TConnection, TCommand, TParameter, TDataAdapter>(string commandText, bool isProcedure, string tableName, string connectionString)
            where TConnection : DbConnection, new()
            where TCommand : DbCommand, new()
            where TParameter : DbParameter, new()
            where TDataAdapter : DbDataAdapter, new()
        {
            return FillDataTable<TConnection, TCommand, TParameter, TDataAdapter>(commandText, isProcedure, tableName, connectionString, null);
        }

        public static DataTable FillDataTable<TConnection, TCommand, TParameter, TDataAdapter>(string commandText, bool isProcedure, string tableName, string connectionString, params TParameter[] parameters)
            where TConnection : DbConnection, new()
            where TCommand : DbCommand, new()
            where TParameter : DbParameter, new()
            where TDataAdapter : DbDataAdapter, new()
        {
            using (TConnection connection = new TConnection())
            {
                connection.ConnectionString = connectionString;
                using (TCommand command = new TCommand())
                {
                    command.CommandText = commandText;
                    command.Connection = connection;
                    DataTable dataTable;
                    if (!string.IsNullOrEmpty(tableName))
                    {
                        dataTable = new DataTable(tableName);
                    }
                    else
                    {
                        dataTable = new DataTable();
                    }
                    if (isProcedure)
                    {
                        command.CommandType = CommandType.StoredProcedure;
                    }
                    if (!IsArrayNullOrEmpty(parameters))
                    {
                        AddDataParametersToDbCommand(command, parameters);
                    }
                    FillDataAdapter<TCommand, TDataAdapter>(dataTable, command);
                    return dataTable;
                }
            }
        }

        public static void FillDataSet<TConnection, TCommand, TParameter, TDataAdapter>(string commandText, bool isProcedure, string connectionString, DataSet dataSet)
            where TConnection : DbConnection, new()
            where TCommand : DbCommand, new()
            where TParameter : DbParameter, new()
            where TDataAdapter : DbDataAdapter, new()
        {
            FillDataSet<TConnection, TCommand, TParameter, TDataAdapter>(commandText, isProcedure, string.Empty, dataSet, connectionString, null);
        }

        public static void FillDataSet<TConnection, TCommand, TParameter, TDataAdapter>(string commandText, bool isProcedure, DataSet dataSet, string connectionString, params TParameter[] parameters)
            where TConnection : DbConnection, new()
            where TCommand : DbCommand, new()
            where TParameter : DbParameter, new()
            where TDataAdapter : DbDataAdapter, new()
        {
            FillDataSet<TConnection, TCommand, TParameter, TDataAdapter>(commandText, isProcedure, string.Empty, dataSet, connectionString, parameters);
        }

        public static void FillDataSet<TConnection, TCommand, TParameter, TDataAdapter>(string commandText, bool isProcedure, string sourceTable, DataSet dataSet, string connectionString, params TParameter[] parameters)
            where TConnection : DbConnection, new()
            where TCommand : DbCommand, new()
            where TParameter : DbParameter, new()
            where TDataAdapter : DbDataAdapter, new()
        {
            using (TConnection connection = new TConnection())
            {
                connection.ConnectionString = connectionString;
                using (TCommand command = new TCommand())
                {
                    command.CommandText = commandText;
                    command.Connection = connection;
                    if (isProcedure)
                    {
                        command.CommandType = CommandType.StoredProcedure;
                    }
                    if (!IsArrayNullOrEmpty(parameters))
                    {
                        AddDataParametersToDbCommand(command, parameters);
                    }
                    TDataAdapter adapter = new TDataAdapter();
                    adapter.SelectCommand = command;
                    if (!string.IsNullOrEmpty(sourceTable))
                    {
                        adapter.Fill(dataSet, sourceTable);
                    }
                    else
                    {
                        adapter.Fill(dataSet);
                    }
                }
            }
        }

        public static DataSet FillDataSet<TConnection, TCommand, TParameter, TDataAdapter>(string commandText, bool isProcedure, string connectionString)
            where TConnection : DbConnection, new()
            where TCommand : DbCommand, new()
            where TParameter : DbParameter, new()
            where TDataAdapter : DbDataAdapter, new()
        {
            return FillDataSet<TConnection, TCommand, TParameter, TDataAdapter>(commandText, isProcedure, string.Empty, connectionString, null as TParameter);
        }

        public static DataSet FillDataSet<TConnection, TCommand, TParameter, TDataAdapter>(string commandText, bool isProcedure, string connectionString, params TParameter[] parameters)
            where TConnection : DbConnection, new()
            where TCommand : DbCommand, new()
            where TParameter : DbParameter, new()
            where TDataAdapter : DbDataAdapter, new()
        {
            return FillDataSet<TConnection, TCommand, TParameter, TDataAdapter>(commandText, isProcedure, string.Empty, connectionString, parameters);
        }

        public static DataSet FillDataSet<TConnection, TCommand, TParameter, TDataAdapter>(string commandText, bool isProcedure, string dataSetName, string connectionString)
            where TConnection : DbConnection, new()
            where TCommand : DbCommand, new()
            where TParameter : DbParameter, new()
            where TDataAdapter : DbDataAdapter, new()
        {
            return FillDataSet<TConnection, TCommand, TParameter, TDataAdapter>(commandText, isProcedure, dataSetName, connectionString, null as TParameter);
        }

        public static DataSet FillDataSet<TConnection, TCommand, TParameter, TDataAdapter>(string commandText, bool isProcedure, string dataSetName, string connectionString, params TParameter[] parameters)
            where TConnection : DbConnection, new()
            where TCommand : DbCommand, new()
            where TParameter : DbParameter, new()
            where TDataAdapter : DbDataAdapter, new()
        {
            using (TConnection connection = new TConnection())
            {
                connection.ConnectionString = connectionString;
                using (TCommand command = new TCommand())
                {
                    command.CommandText = commandText;
                    command.Connection = connection;
                    DataSet dataSet;
                    if (!string.IsNullOrEmpty(dataSetName))
                    {
                        dataSet = new DataSet(dataSetName);
                    }
                    else
                    {
                        dataSet = new DataSet();
                    }
                    if (isProcedure)
                    {
                        command.CommandType = CommandType.StoredProcedure;
                    }
                    if (!IsArrayNullOrEmpty(parameters))
                    {
                        AddDataParametersToDbCommand(command, parameters);
                    }
                    FillDataAdapter<TCommand, TDataAdapter>(dataSet, command);
                    return dataSet;
                }
            }
        }

        public static Dictionary<string, string> GetDatabaseFields<TConnection, TCommand, TParameter, TDataAdapter>(string commandText, bool isStoredProcedure, string connectionString, params string[] databaseFields)
            where TConnection : DbConnection, new()
            where TCommand : DbCommand, new()
            where TParameter : DbParameter, new()
            where TDataAdapter : DbDataAdapter, new()
        {
            return GetDatabaseFields<TConnection, TCommand, TParameter, TDataAdapter>(commandText, isStoredProcedure, null, connectionString, databaseFields);
        }

        public static Dictionary<string, string> GetDatabaseFields<TConnection, TCommand, TParameter, TDataAdapter>(string commandText, bool isStoredProcedure, TParameter[] parameters, string connectionString, params string[] databaseFields)
            where TConnection : DbConnection, new()
            where TCommand : DbCommand, new()
            where TParameter : DbParameter, new()
            where TDataAdapter : DbDataAdapter, new()
        {
            if (!IsArrayNullOrEmpty(databaseFields))
            {
                DataRow dataRow;
                if (isStoredProcedure)
                {
                    if (!IsArrayNullOrEmpty(parameters))
                    {
                        dataRow = FillDataTable<TConnection, TCommand, TParameter, TDataAdapter>(commandText, true, connectionString, parameters).Rows[0];
                    }
                    else
                    {
                        dataRow = FillDataTable<TConnection, TCommand, TParameter, TDataAdapter>(commandText, connectionString, true).Rows[0];
                    }
                }
                else
                {
                    if (!IsArrayNullOrEmpty(parameters))
                    {
                        dataRow = FillDataTable<TConnection, TCommand, TParameter, TDataAdapter>(commandText, false, connectionString, parameters).Rows[0];
                    }
                    else
                    {
                        dataRow = FillDataTable<TConnection, TCommand, TParameter, TDataAdapter>(commandText, false, connectionString).Rows[0];
                    }

                }
                Dictionary<string, string> fieldsAndValues = new Dictionary<string, string>();
                foreach (string databaseField in databaseFields)
                {
                    fieldsAndValues.Add(databaseField, Convert.ToString(dataRow[databaseField]));
                }
            }
            return null;
        }

        public static DataSet PopulateDataSet(params DataTable[] dataTables)
        {
            DataSet dataSet = new DataSet();
            if (!IsArrayNullOrEmpty(dataTables))
            {
                foreach (DataTable dataTable in dataTables)
                {
                    dataSet.Tables.Add(dataTable);
                }
            }
            return dataSet;
        }

        private static bool IsArrayNullOrEmpty(Array array)
        {
            return array == null || array.Length == 0;
        }
    }
}