using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;
using System.Threading;

public class SigmaSportDAL2 : IDisposable
{


    private DbConnection _DbConnection;
    private DbCommand _DbCommand;
    private ConnectionStringSettings _DbConnectionString;
    private DbProviderFactory _DbFactory = null;
    private DbTransaction _DbTransaction = null;
    private DbDataAdapter _DbDataAdapter = null;
    private DbConnectionStringBuilder _DbCnxStringBuilder = null;

    /// <summary>
    /// Creates a new instance of CRMDbClient
    /// </summary>
    /// <param name="CurrentConnectionString"></param>
    public SigmaSportDAL2()
    {

        
        _DbConnectionString = ConfigurationManager.ConnectionStrings["SigmaSport"];        
        _DbFactory = DbProviderFactories.GetFactory(_DbConnectionString.ProviderName);
        _DbConnection = _DbFactory.CreateConnection();
        _DbCnxStringBuilder = _DbFactory.CreateConnectionStringBuilder();
        _DbCnxStringBuilder.ConnectionString = _DbConnectionString.ConnectionString;
        _DbCommand = _DbConnection.CreateCommand();
        _DbConnection.ConnectionString = _DbCnxStringBuilder.ConnectionString;
        OpenConnection();
    }

    //OpenConnection();


    #region Destruction classe
    /// <summary>
    /// Destroy the current instance
    /// </summary>
    public void Dispose()
    {
        CloseConnection();
        
        _DbConnectionString = null;
        _DbDataAdapter = null;
        _DbFactory = null;
        _DbTransaction = null;
        _DbCommand = null;
        _DbConnection = null;

    }
    /// <summary>
    /// Destroy the current instance
    /// </summary>
    ~SigmaSportDAL2()
    {
        this.Dispose();
        GC.Collect();
    }

    #endregion


    #region Properties
    /// <summary>
    /// Gets or sets the command timeout in seconds
    /// </summary>
    public int CommandTimeout
    {
        get { return _DbCommand.CommandTimeout; }
        set { _DbCommand.CommandTimeout = value; }
    }
    /// <summary>
    /// Gets the database provider
    /// </summary>
    public string DataBaseProvider
    {
        get { return _DbConnectionString.ProviderName; }
    }
    /// <summary>
    /// Gets or set the 
    /// </summary>
    public int ConnectionTimeout
    {
        get { return _DbConnection.ConnectionTimeout; }
    }

    #endregion
    /// <summary>
    /// Begins transaction
    /// </summary>
    public void BeginTransaction()
    {
        _DbTransaction = _DbConnection.BeginTransaction();
    }
    /// <summary>
    /// Commit transaction
    /// </summary>
    public void CommitTransaction()
    {
        if (_DbTransaction != null)
        {
            _DbTransaction.Commit();
            _DbTransaction.Dispose();
            _DbTransaction = null;
            _DbCommand.Transaction = null;

        }
        else
        {
            throw new SigmaSportDALException("BeginTransaction must be called before commit or rollback. No open transactions found");
        }
    }
    /// <summary>
    /// Rollbacks transaction
    /// </summary>
    public void RollbackTransaction()
    {
        try
        {
            _DbTransaction.Rollback();
        }
        catch (InvalidOperationException invexcept)
        {
            throw new SigmaSportDALException(invexcept.Message, invexcept);
        }
        catch (Exception e)
        {
            throw new SigmaSportDALException(e.Message, e);
        }
        finally
        {
            //dispose _dbTransaction
            if (_DbTransaction != null)
                _DbTransaction.Dispose();

            _DbTransaction = null;
        }
    }

    /// <summary>
    /// Open the connection with de server
    /// </summary>
    private void OpenConnection()
    {
        if ((_DbConnection.State & ConnectionState.Open) != ConnectionState.Open)
            _DbConnection.Open();
    }
    /// <summary>
    /// Closes the connection with the server
    /// </summary>
    private void CloseConnection()
    {
        //if ((_DbConnection.State) != ConnectionState.Closed)
        if(_DbConnection != null)
            _DbConnection.Close();
    }
    /// <summary>
    /// Executes a scalar query
    /// </summary>
    /// <param name="sql">Represents the query text</param>
    /// <param name="CmdType">Represents the command type</param>
    /// <returns>Object containing the scalar result</returns>
    public object ExecuteScalar(string QueryText, CommandType CmdType)
    {
        try
        {
            _DbCommand.Parameters.Clear();
            _DbCommand.CommandText = QueryText; //set que querytext
            _DbCommand.CommandType = CmdType;

            OpenConnection();
            object val = _DbCommand.ExecuteScalar(); //runs que query

            return val;
        }
        catch (Exception ex)
        {

            throw ex;
        }
        finally
        {
            CloseConnection();
        }
    }
    /// <summary>
    /// Executes a scalar query
    /// </summary>
    /// <param name="QueryText">Represents the query text</param>
    /// <param name="CmdType">Represents the command type</param>
    /// <param name="QueryParameter">Quary parameter</param>
    /// <returns>Object containing the scalar result</returns>
    public object ExecuteScalar(string QueryText, CommandType CmdType, DbParameter QueryParameter)
    {
        try
        {
            _DbCommand.Parameters.Clear();
            _DbCommand.CommandText = QueryText; //set que querytext
            _DbCommand.CommandType = CmdType;
            _DbCommand.Parameters.Add(QueryParameter);
            OpenConnection();
            object val = _DbCommand.ExecuteScalar(); //runs que query

            return val;
        }
        catch (Exception ex)
        {

            throw ex;
        }
        finally
        {
            CloseConnection();
        }
    }
    /// <summary>
    /// Executes a scalar query
    /// </summary>
    /// <param name="QueryText">Represents the query text</param>
    /// <param name="CmdType">Represents the command type</param>
    /// <param name="QueryParameters">Quary parameters list</param>
    /// <returns>Object containing the scalar result</returns>
    public object ExecuteScalar(string QueryText, CommandType CmdType, DbParameter[] QueryParameters)
    {
        try
        {
            _DbCommand.Parameters.Clear();
            _DbCommand.CommandText = QueryText; //set que querytext
            _DbCommand.CommandType = CmdType;
            _DbCommand.Parameters.AddRange(QueryParameters);


            OpenConnection();
            object val = _DbCommand.ExecuteScalar(); //runs que query

            return val;
        }
        catch (Exception ex)
        {

            throw ex;
        }
        finally
        {
            CloseConnection();
        }
    }
    /// <summary>
    /// Executes a query and retourns the result into a datatable.
    /// </summary>
    /// <param name="QueryText">Query text</param>
    /// <param name="CmdType">Command type</param>
    /// <returns>Datatable containing the result</returns>
    public DataTable ExecuteReader(string QueryText, CommandType CmdType)
    {

        try
        {
            DataTable dt = new DataTable();
            _DbCommand.Parameters.Clear();
            _DbCommand.CommandText = QueryText;
            _DbCommand.CommandType = CmdType;

            OpenConnection();
            dt.Load(_DbCommand.ExecuteReader(CommandBehavior.CloseConnection));

            return dt;
        }
        catch (Exception ex)
        {
            
            throw ex;
        }
        finally{
            CloseConnection();
        }
    }
    /// <summary>
    /// Executes a query and retourns the result into a datatable.
    /// </summary>
    /// <param name="QueryText">Query text</param>
    /// <param name="CmdType">Command type</param>
    /// <param name="QueryParameters">Quary parameters list</param>
    /// <returns>Datatable containing the result</returns>
    public DataTable ExecuteReader(string QueryText, CommandType CmdType, DbParameter[] QueryParameters)
    {
        try
        {
            DataTable dt = new DataTable();
            _DbCommand.Parameters.Clear();
            _DbCommand.CommandText = QueryText;
            _DbCommand.CommandType = CmdType;
            _DbCommand.Parameters.AddRange(QueryParameters);

            OpenConnection();
            dt.Load(_DbCommand.ExecuteReader(CommandBehavior.CloseConnection));

            return dt;
        }
        catch (Exception ex)
        {

            throw ex;
        }
        finally
        {
            CloseConnection();
        }
    }
    /// <summary>
    /// Executes a query and retourns the result into a datatable.
    /// </summary>
    /// <param name="QueryText">Query text</param>
    /// <param name="CmdType">Command type</param>
    /// <param name="QueryParameter">Quary parameter</param>
    /// <returns>Datatable containing the result</returns>
    public DataTable ExecuteReader(string QueryText, CommandType CmdType, DbParameter QueryParameter)
    {
        try
        {
            DataTable dt = new DataTable();
            _DbCommand.Parameters.Clear();
            _DbCommand.CommandText = QueryText;
            _DbCommand.CommandType = CmdType;
            _DbCommand.Parameters.Add(QueryParameter);

            OpenConnection();
            dt.Load(_DbCommand.ExecuteReader(CommandBehavior.CloseConnection));

            return dt;
        }
        catch (Exception ex)
        {

            throw ex;
        }
        finally
        {
            CloseConnection();
        }
    }
    /// <summary>
    /// Executes a query and retourns affected rows number
    /// </summary>
    /// <param name="QueryText">Query text</param>
    /// <param name="CmdType">Command Type</param>
    /// <returns>Integer value representing affected rows</returns>
    public int ExecuteNonQuery(string QueryText, CommandType CmdType)
    {
        try
        {
            _DbCommand.Parameters.Clear();
            _DbCommand.CommandText = QueryText;
            _DbCommand.CommandType = CmdType;

            OpenConnection();
            int val = _DbCommand.ExecuteNonQuery();

            return val;
        }
        catch (Exception ex)
        {

            throw ex;
        }
        finally
        {
            CloseConnection();
        }
    }
    /// <summary>
    /// Executes a query and retourns affected rows number
    /// </summary>
    /// <param name="QueryText">Qurey text</param>
    /// <param name="CmdType">Command type</param>
    /// <param name="QueryParameters">Quary parameters list</param>
    /// <returns>Integer value representing affected rows</returns>
    public int ExecuteNonQuery(string QueryText, CommandType CmdType, DbParameter[] QueryParameters)
    {

        try
        {
            _DbCommand.Parameters.Clear();
            _DbCommand.CommandText = QueryText;
            _DbCommand.CommandType = CmdType;
            _DbCommand.Parameters.AddRange(QueryParameters);

            OpenConnection();
            int val = _DbCommand.ExecuteNonQuery();

            return val;
        }
        catch (Exception ex)
        {

            throw ex;
        }
        finally
        {
            CloseConnection();
        }
    }
    /// <summary>
    /// Executes a query and retourns affected rows number
    /// </summary>
    /// <param name="QueryText">Qurey text</param>
    /// <param name="CmdType">Command type</param>
    /// <param name="QueryParameter">Quary parameter</param>
    /// <returns>Integer value representing affected rows</returns>
    public int ExecuteNonQuery(string QueryText, CommandType CmdType, DbParameter QueryParameter)
    {

        try
        {
            _DbCommand.Parameters.Clear();
            _DbCommand.CommandText = QueryText;
            _DbCommand.CommandType = CmdType;
            _DbCommand.Parameters.Add(QueryParameter);
            OpenConnection();
            int val = _DbCommand.ExecuteNonQuery();

            return val;
        }
        catch (Exception ex)
        {

            throw ex;
        }
        finally
        {
            CloseConnection();
        }
    }
    /// <summary>
    /// Creates a parameter for the database
    /// </summary>
    /// <returns>The created parameter</returns>
    public DbParameter CreateParameter()
    {
        return _DbCommand.CreateParameter();        
    }
    /// <summary>
    /// Creates a parameter for the database
    /// </summary>
    /// <param name="ParamName">The Parameter name</param>
    /// <param name="Value">The parameter value</param>
    /// <returns>The created parameter</returns>
    public DbParameter CreateParameter(String ParamName, object Value)
    { 
        DbParameter p = _DbCommand.CreateParameter();
        p.ParameterName = ParamName;
        p.Value = Value;
        return p;
    }

    /// <summary>
    /// Creates a new DBParameter
    /// </summary>
    /// <param name="Name">The Name of the Parameter</param>
    /// <param name="Value">The value of the Parameter</param>
    /// <param name="Direction">Specify the direction of the pramaeter</param>
    /// <returns>DBParameter representing the new data base parameter</returns>
    public DbParameter CreateParameter(string Name, object Value, ParameterDirection Direction)
    {
        DbParameter p = _DbCommand.CreateParameter();
        p.ParameterName = Name;
        p.Value = Value;
        p.Direction = Direction;
        return p;

    }
}