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

namespace DotNetMock.Examples.MailingList
{
	public class MailingList
	{
		public static string INSERT_SQL = "INSERT INTO mailing_list(email_address, name) VALUES (@email, @name)";
		public static string DELETE_SQL = "DELETE FROM mailing_list WHERE email_address = @email";
		public static string SELECT_SQL = "SELECT * FROM mailing_list";

		public MailingList()
		{

		}
		public void AddMember(IDbConnection connection, string emailAddress, string name)
		{
			IDbCommand command = connection.CreateCommand();
			try 
			{
				command.CommandText = MailingList.INSERT_SQL;
				command.Parameters.Add(new SqlParameter("@email", emailAddress));
				command.Parameters.Add(new SqlParameter("@name", name));
				command.ExecuteNonQuery();
			}
			catch (ConstraintException ex)
			{
				throw new MailingListException("Email address already exists:" + ex.Message);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			finally 
			{
				connection.Close();
			}
		}
		public void RemoveMember(IDbConnection connection, string email)
		{
			IDbCommand command = connection.CreateCommand();
			try 
			{
				command.CommandText = MailingList.DELETE_SQL;
				command.Parameters.Add(new SqlParameter("@email", email));
				int rowsAffected = command.ExecuteNonQuery();
				if (rowsAffected == 0) 
				{
					throw new MailingListException("Could not find email address: " + email);
				}
			}
			finally
			{
				connection.Close();
			}
		}
		public void ApplyToAllMembers(IDbConnection connection, IListAction listAction)
		{
			IDbCommand command = connection.CreateCommand();
			try 
			{
				command.CommandText = MailingList.SELECT_SQL;
				IDataReader reader = command.ExecuteReader();

				while (reader.Read()) 
				{
					listAction.ApplyTo(reader.GetString(0), reader.GetString(1));
				}
			} 
			finally
			{
				connection.Close();
			}
		}
	}
}
