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

namespace EPS.Common.Data
{
	/// <summary>
	/// Implementation of IDbhelper for SqlServer databases.
	/// </summary>
	public class SqlDbHelper : IDbHelper
	{
		/// <summary>
		/// Initializes a new instance of the SqlDbHelper class.
		/// </summary>
		/// <param name="connectionString"></param>
		public SqlDbHelper(string connectionString)
		{
			_connectionString = connectionString;
		}

		private string _connectionString;
		/// <summary>
		/// Gets the connection string to the database.
		/// </summary>
		public string ConnectionString
		{
			get
			{
				return _connectionString;
			}
		}

		/// <summary>
		/// Opens a connection to the database.
		/// </summary>
		/// <returns>An open connection to the database.</returns>
		public IDbConnection GetConnection()
		{
			return this.GetConnection(InitialConnectionStates.Open);
		}

		/// <summary>
		/// Gets a connection to the database.
		/// </summary>
		/// <param name="initialState">Indicates the state of the connection returned.</param>
		/// <returns>A connection to the database, either open or closed.</returns>
		public IDbConnection GetConnection(InitialConnectionStates initialState)
		{
			IDbConnection result = new SqlConnection(this._connectionString);
			if (initialState == InitialConnectionStates.Open)
				result.Open();			
			return result;
		}

		/// <summary>
		/// Executes the query, and returns the first column of the first row of the resultset.
		/// </summary>
		/// <typeparam name="T">The type of the data returned.</typeparam>
		/// <param name="commandText">The query to execute.</param>
		/// <returns>The first column of the first row of the result of executeing the query.</returns>
		public T ExecuteScalar<T>(string commandText)
		{
			IDbCommand cmd = new SqlCommand(commandText);
			return this.ExecuteScalar<T>(cmd);
		}

		/// <summary>
		/// Executes the command, and returns the first column of the first row of the resultset.
		/// </summary>
		/// <typeparam name="T">The type of the data returned.</typeparam>
		/// <param name="command">The command to execute.</param>
		/// <returns>The first colunm of the first row of the result of executing the command.</returns>
		public T ExecuteScalar<T>(IDbCommand command)
		{
			T result;
			using (SqlConnection cn = (this.GetConnection() as SqlConnection))
            {
				command.Connection = cn;
				result = (T)command.ExecuteScalar();
            }
			return result;
		}

		/// <summary>
		/// Executes the query and returns an array of all of the values of the first column of all rows in the resultset.
		/// </summary>
		/// <typeparam name="TItem">The type of data in the first column of each row.</typeparam>
		/// <param name="commandText">The query to execute.</param>
		/// <returns>An array of all of the values of the first column of all of the rows in the resultset.</returns>
		public TItem[] ExecuteArray<TItem>(string commandText)
		{
			SqlCommand command = new SqlCommand(commandText);
			return this.ExecuteArray<TItem>(command);
		}

		/// <summary>
		/// Executes the command and returns an array of all of the values of the first column of all rows in the resultset.
		/// </summary>
		/// <typeparam name="TItem">The type of data in the first column of each row.</typeparam>
		/// <param name="command">The command to execute.</param>
		/// <returns>An array of all of the values of the first column of all of the rows in the resultset.</returns>
		public TItem[] ExecuteArray<TItem>(IDbCommand command)
		{
			List<TItem> result = new List<TItem>();
			using (IDataReader reader = this.ExecuteReader(command))
			{
				while (reader.Read())
				{
					result.Add((TItem)reader.GetValue(0));
				}
			}
			return result.ToArray();
		}

		/// <summary>
		/// Executes the query and returns the number of rows affected.
		/// </summary>
		/// <param name="commandText">The query to execute.</param>
		/// <returns>The number of rows affected.</returns>
		public int ExecuteNonQuery(string commandText)
		{
			SqlCommand command = new SqlCommand(commandText);
			return this.ExecuteNonQuery(command);
		}

		/// <summary>
		/// Executes the command and returns the number of rows affected.
		/// </summary>
		/// <param name="command">The command to execute.</param>
		/// <returns>The number of rows affected.</returns>
		public int ExecuteNonQuery(IDbCommand command)
		{
			int result;
			using (IDbConnection cn = this.GetConnection())
            {
				command.Connection = cn;
				result = command.ExecuteNonQuery();
            }
			return result;
		}

		/// <summary>
		/// Executes the query and returns a DataTable filled with the results.
		/// </summary>
		/// <param name="commandText">The query to execute.</param>
		/// <returns>DataTable containing the results of executing the query.</returns>
		public DataTable ExecuteDataTable(string commandText)
		{
			SqlCommand command = new SqlCommand(commandText);
			return this.ExecuteDataTable(command);
		}

		/// <summary>
		/// Executes the command and returns a DataTable filled with the results.
		/// </summary>
		/// <param name="command">The command to execute.</param>
		/// <returns>DataTable containing the results of executing the command.</returns>
		public DataTable ExecuteDataTable(IDbCommand command)
		{
			DataTable result = new DataTable();
			using (IDbConnection cn = this.GetConnection())
            {
				command.Connection = cn;
				using (SqlDataAdapter da = new SqlDataAdapter((SqlCommand)command))
                {
					da.Fill(result);
                }
            }
			return result;
		}

		/// <summary>
		/// Executes a query and returns a SqlDataReader containing the resultset.
		/// </summary>
		/// <param name="commandText">The query to execute.</param>
		/// <returns>A SqlDataReader containing the resultset.</returns>
		public IDataReader ExecuteReader(string commandText)
		{
			SqlCommand command = new SqlCommand(commandText);
			return this.ExecuteReader(command);
		}

		/// <summary>
		/// Executes a command and returns a SqlDataReader containing the resultset.
		/// </summary>
		/// <param name="command">The command to execute.</param>
		/// <returns>A SqlDataReader containing the resultset.</returns>
		public IDataReader ExecuteReader(IDbCommand command)
		{
			command.Connection = this.GetConnection();
			return command.ExecuteReader();
		}
	}
}