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();
}
}
}
}
|