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

namespace DataTools
{
    public class DataAccess : IDisposable
    {
        private string conStr, server, user, pass, database;
        private SqlConnection con;
        private SqlDataAdapter da;
        private SqlCommand com;

        /// <summary>
        /// Gets or sets the server's name.
        /// </summary>
        public string Server
        {
            set
            {
                server = value;
                InitializeConnection();
            }
            get { return server; }
        }

        /// <summary>
        /// Gets or sets the username to connect to the server.
        /// </summary>
        public string Username
        {
            set
            {
                user = value;
                InitializeConnection();
            }
            get { return user; }
        }

        /// <summary>
        /// Gets or sets the password of the username.
        /// </summary>
        public string Password
        {
            set
            {
                pass = value;
                InitializeConnection();
            }
            get { return pass; }
        }

        /// <summary>
        /// Gets or sets the current database of the connection.
        /// </summary>
        public string Database
        {
            set
            {
                database = value;
                InitializeConnection();
            }
            get { return database; }
        }

        /// <summary>
        /// Initializes a new instance of DataAccess, in addition initializes the connection.
        /// </summary>
        /// <param name="server">SQLServer name or direction.</param>
        /// <param name="user">Username to connect.</param>
        /// <param name="pass">Password.</param>
        /// <param name="database">Initial DataBase name.</param>
        public DataAccess(string server, string user, string pass, string database)
        {
            this.server = server;
            this.user = user;
            this.pass = pass;
            this.database = database;
            InitializeConnection();
        }

        /// <summary>
        /// Opens the connection.
        /// </summary>
        public void OpenConnection()
        {
            try
            {
                con.Open();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Closes the connection.
        /// </summary>
        public void CloseConnection()
        {
            try
            {
                con.Close();
            }
            catch(SqlException ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Executes the select statement.
        /// </summary>
        /// <param name="query">Query string.</param>
        /// <returns>A DataTable.</returns>
        public DataTable ExecuteQuery(string query)
        {
            DataSet ds = new DataSet();
            da = new SqlDataAdapter(query, con);

            try
            {
                OpenConnection();
                da.Fill(ds);
                CloseConnection();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                da.Dispose();
                da = null;
                com.Dispose();
                com = null;
            }

            return ds.Tables[0];
        }

        /// <summary>
        /// Executes a sql statement (insert, update, delete).
        /// </summary>
        /// <param name="query">The sql statement.</param>
        /// <returns>The affected rows.</returns>
        public int ExecuteNonQuery(string query)
        {
            int r = 0;
            SqlCommand com = new SqlCommand(query, con);

            try
            {
                r = com.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }

            return r;
        }

        /// <summary>
        /// Executes an StoreProcedure with an insert, delete or update statement.
        /// </summary>
        /// <param name="storeProc">The StoreProcedure Object.</param>
        /// <returns>The affected rows.</returns>
        public int ExecuteStoreProcedureNonQuery(StoreProcedure storeProc)
        {
            int r = 0;
            com = new SqlCommand(storeProc.Name, con);
            com.CommandType = CommandType.StoredProcedure;

            try
            {
                foreach (SqlParameter param in storeProc.Parameters)
                {
                    com.Parameters.Add(param);
                }
                con.Open();
                r = com.ExecuteNonQuery();
                con.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                com.Dispose();
                com = null;
            }

            return r;
        }

        /// <summary>
        /// Executes the given StoreProcedure.
        /// </summary>
        /// <param name="storeProc">The StoreProcedure object.</param>
        /// <returns>A DataTable.</returns>
        public DataTable ExecuteStoreProcedure(StoreProcedure storeProc)
        {
            da = new SqlDataAdapter();
            DataSet ds = new DataSet();

            com = new SqlCommand(storeProc.Name, con);
            com.CommandType = CommandType.StoredProcedure;

            foreach (SqlParameter param in storeProc.Parameters)
                com.Parameters.Add(param);

            da.SelectCommand = com;

            try
            {
                da.Fill(ds);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                da.Dispose();
                da = null;
                com.Dispose();
                com = null;
            }

            return ds.Tables[0];
        }

        // Initialize the connection
        private void InitializeConnection()
        {
            conStr = "Server=" + server + ";UID=" + user + ";PWD=" + pass + ";Database=" + database;
            con = new SqlConnection(conStr);
        }

        #region IDisposable Members

        private bool disposed = false;

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!disposed)
            {
                if (da != null)
                {
                    da.Dispose();
                    da = null;
                }
                if (com != null)
                {
                    com.Dispose();
                    com = null;
                }
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                    con = null;
                }
            }

            disposed = true;
        }

        #endregion
    }
}