using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Text;

    using MusicaToulouse.LogError;

    public class Connection
    {

        #region Fields

        private Log _loggin;
        private SqlConnection connectionDB;
        private String database;
        private String pwdDB;
        private String sourceDB;
        private String userDB;

        #endregion Fields

        #region Constructors

        public Connection()
        {

        }

        #endregion Constructors

        #region Properties

        protected SqlConnection ConnectionDB
        {
            get { return connectionDB; }
            set { connectionDB = value; }
        }

        protected String Database
        {
            get { return database; }
            set { database = value; }
        }

        protected String PwdDB
        {
            get { return pwdDB; }
            set { pwdDB = value; }
        }

        protected String SourceDB
        {
            get { return sourceDB; }
            set { sourceDB = value; }
        }

        protected String UserDB
        {
            get { return userDB; }
            set { userDB = value; }
        }

        private Log Logggin
        {
            get { return _loggin; }
            set { _loggin = value; }
        }

        #endregion Properties

        #region Methods

        public bool connect()
        {
            try
            {
                log("Connexion");

                String connectString = @"Data Source=XXXXXXXX\SQLEXPRESS;Initial Catalog=XXXXXXX;Integrated Security=SSPI;";                    
                ConnectionDB = new SqlConnection(connectString);

                ConnectionDB.Open();
                return true;
            }
            catch (Exception ex)
            {
                log("Error connexion");
                Logggin = new Log("connection.txt");
                Logggin.write(DateTime.Now, ex.Message, ex.Source, ex.StackTrace);
                return false;
            }
        }

        public bool disconnect()
        {
            try
            {
                log("Déconnexion");
                ConnectionDB.Close();
                return true;
            }
            catch (Exception ex)
            {
                log("Error déconnexion");
                Logggin = new Log("connection.txt");
                Logggin.write(DateTime.Now, ex.Message, ex.Source, ex.StackTrace);
                return false;
            }
        }

        public int procStockNonQuery(String _nomProc, List<Parameters> listParams)
        {
            int retour = 0;
            try
            {
                log("Begin non query " + _nomProc);
                SqlCommand mySqlCom = new SqlCommand(_nomProc, ConnectionDB);
                mySqlCom.CommandType = CommandType.StoredProcedure;

                if (listParams != null)
                {
                    foreach (Parameters param in listParams)
                    {
                        SqlParameter parameter = new SqlParameter(param.Param, param.DbType);
                        parameter.Value = param.Value;
                        mySqlCom.Parameters.Add(param);
                    }
                }

                retour = mySqlCom.ExecuteNonQuery();

                log("End non query " + _nomProc);
                return retour;
            }
            catch (Exception ex)
            {
                log("Error non query " + _nomProc);

                Logggin = new Log("connection.txt");
                Logggin.write(DateTime.Now, ex.Message, ex.Source, ex.StackTrace);
                return -2;
            }
        }

        public SqlDataReader procStockQuery(String _nomProc, List<Parameters> listParams)
        {
            SqlDataReader reader = null;

            try
            {
                log("Begin query " + _nomProc);
                SqlCommand mySqlCom = new SqlCommand(_nomProc, ConnectionDB);
                mySqlCom.CommandType = CommandType.StoredProcedure;

                if (listParams != null)
                {
                    foreach (Parameters param in listParams)
                    {
                        SqlParameter parameter = new SqlParameter(param.Param, param.DbType);
                        parameter.Value = param.Value;
                        mySqlCom.Parameters.Add(parameter);
                    }
                }
                reader = mySqlCom.ExecuteReader();

                log("End query " + _nomProc);
                return reader;
            }
            catch (Exception ex)
            {
                log("Error query " + _nomProc);

                Logggin = new Log("connection.txt");
                Logggin.write(DateTime.Now, ex.Message, ex.Source, ex.StackTrace);
                return null;
            }
        }

        private void log(String message)
        {
            Logggin = new Log("log.txt");
            Logggin.write(DateTime.Now.ToString("dd/MM/yyyy - HH:mm:ss.ffff") + " - " + message);
        }

        #endregion Methods
    }

    public class Parameters
    {
        #region Properties

        public SqlDbType DbType
        {
            get; set;
        }

        public String Param
        {
            get; set;
        }

        public String Value
        {
            get; set;
        }

        #endregion Properties
    }