Dinner

dinner.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web.Mvc;

namespace NerdDinner.Models
{
    public partial class Dinner
    {
        public Dinner() { RSVPs = new List<RSVP>(); }

        public int DinnerID { get; set; }
        public string Title{get;set;}
        public DateTime EventDate{get; set;}
        public string Description{get; set;}
        public string HostedBy{get; set;}
        public string ContactPhone{get;set;}
        public string Address{get; set;}
        public string Country{get; set;}
        public double Latitude{get;set;}
        public double Longitude{get; set;}
        public List<RSVP> RSVPs { get; set; }

    }
}

dinnerrepository.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Configuration;
using System.Data;
using NerdDinner.Helpers;
using System.Data.SqlClient;

namespace NerdDinner.Models
{
    public class DinnerRepository : IDinnerRepository
    {
        private string connectionString; 

        public DinnerRepository()
        {
            const string connectionStringKey = "NerdDinnerConnectionString";
            this.connectionString = ConfigurationManager.ConnectionStrings[connectionStringKey].ConnectionString;
        }

        public IEnumerable<Dinner> FindAllDinners()
        {
            using (var connection = new SqlConnection(this.connectionString))
            {
                var commandText = 
                @"
                    select d.DinnerID, d.Title, d.EventDate, d.[Description], d.HostedBy, 
                        d.ContactPhone, d.[Address], d.Country, d.Latitude, d.Longitude  
                    from Dinners d
                    
                    select r.RsvpID, r.DinnerID, r.AttendeeName from RSVP r
                        inner join Dinners d on d.DinnerID = r.DinnerID
                    order by r.DinnerID, r.RsvpID
                ";

                var command = new SqlCommand(commandText, connection);
                connection.Open();
                return GetDinners(command);
            }
        }

        public IEnumerable<Dinner> FindByLocation(float latitude, float longitude)
        {
            using (var connection = new SqlConnection(this.connectionString))
            {
                var commandText =
                @"
                    select d.DinnerID, d.Title, d.EventDate, d.[Description], d.HostedBy, 
                        d.ContactPhone, d.[Address], d.Country, d.Latitude, d.Longitude  
                    from Dinners d
                    inner join dbo.NearestDinners(@Latitude,@Longitude) nd on
                        d.DinnerID = nd.DinnerID
                    where @CurrentDate <= d.EventDate
                    order by d.DinnerID

                    select r.RsvpID, r.DinnerID, r.AttendeeName from RSVP r
                    inner join Dinners d on
                    	d.DinnerID = r.DinnerID
                    inner join dbo.NearestDinners(@Latitude,@Longitude) nd on
                        d.DinnerID = nd.DinnerID
                    where @CurrentDate <= d.EventDate
                    order by r.DinnerID, r.RsvpID
                ";
                var command = new SqlCommand(commandText, connection);
                var parameters = new[]{
                        new SqlParameter{ParameterName = "Latitude", DbType = DbType.Double, Value = latitude},
                        new SqlParameter{ParameterName = "Longitude", DbType = DbType.Double, Value = longitude},
                        new SqlParameter{ParameterName = "CurrentDate", DbType = DbType.Date, Value = DateTime.Now}};

                command.Parameters.AddRange(parameters);    

                connection.Open();
                return GetDinners(command);
            }
        }

        public IEnumerable<Dinner> FindUpcomingDinners()
        {
            using (var connection = new SqlConnection(this.connectionString))
            {
                var commandText =
                @"
                    select d.DinnerID, d.Title, d.EventDate, d.[Description], d.HostedBy, 
                        d.ContactPhone, d.[Address], d.Country, d.Latitude, d.Longitude  
                    from Dinners d
                    where @CurrentDate <= d.EventDate
                    order by d.DinnerID

                    select r.RsvpID, r.DinnerID, r.AttendeeName from RSVP r
                    inner join Dinners d on
                    	d.DinnerID = r.DinnerID
                    where @CurrentDate <= d.EventDate
                    order by r.DinnerID, r.RsvpID
                ";

                var command = new SqlCommand(commandText, connection);
                command.Parameters.Add(
                    new SqlParameter{ParameterName="CurrentDate", DbType = DbType.DateTime, Value = DateTime.Now});

                connection.Open();
                return GetDinners(command);
            }
        }

        public Dinner GetDinner(int id)
        {
            using (var connection = new SqlConnection(this.connectionString))
            {
                var commandText =
                @"
                    select d.DinnerID, d.Title, d.EventDate, d.[Description], d.HostedBy, d.ContactPhone, d.[Address], d.Country, d.Latitude, d.Longitude  from Dinners d
                    where DinnerID = @DinnerID
                    
                    select r.RsvpID, r.DinnerID, r.AttendeeName from RSVP r
                    where r.DinnerID = @DinnerID
                    order by r.RsvpID
                ";

                var command = new SqlCommand(commandText, connection);
                command.Parameters.Add(
                    new SqlParameter { ParameterName = "DinnerID", DbType = DbType.Int32, Value = id });

                connection.Open();
                return GetDinners(command)[0];
            }
        }

        public void AddDinner(Dinner dinner)
        {
            using (var connection = new SqlConnection(this.connectionString))
            {
                var commandText = @"
                    insert into Dinners
                    (
                        Title,
                        Description,
                        Address,
                        Country,
                        ContactPhone,
                        Latitude,
                        Longitude,
                        EventDate,
                        HostedBy
                    )
                    values
                    (
                        @Title,
                        @Description,
                        @Address,
                        @Country,
                        @ContactPhone,
                        @Latitude,
                        @Longitude,
                        @EventDate,
                        @HostedBy
                    )
                    
                    select cast(SCOPE_IDENTITY() as Integer) as DinnerID   
                ";

                var command = new SqlCommand(commandText, connection);
                var parameters = GetDinnerObjectParameters(dinner);
                command.Parameters.AddRange(parameters);

                connection.Open();
                dinner.DinnerID = (int)command.ExecuteScalar();
            }
        }

        public void UpdateDinner(Dinner dinner)
        {
            using (var connection = new SqlConnection(this.connectionString))
            {
                var commandText = @"
                    update Dinners
                    set Title = @Title,
                        Description = @Description,
                        Address = @Address,
                        Country = @Country,
                        ContactPhone = @ContactPhone,
                        Latitude = @Latitude,
                        Longitude = @Longitude,
                        EventDate = @EventDate,
                        HostedBy = @HostedBy
                    where DinnerID = @DinnerID
                ";
                var command = new SqlCommand(commandText, connection);

                command.Parameters.Add(
                    new SqlParameter { ParameterName = "DinnerID", DbType = DbType.Int32, Value = dinner.DinnerID });
                
                var dinnerObjectParameters = GetDinnerObjectParameters(dinner);
                command.Parameters.AddRange(dinnerObjectParameters);

                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public void DeleteDinner(int id)
        {
            using (var connection = new SqlConnection(this.connectionString))
            {
                var commandText =
                @"
                    delete from Rsvp where DinnerID = @DinnerID
                    delete from Dinners where DinnerID = @DinnerID
                ";

                var command = new SqlCommand(commandText, connection);
                command.Parameters.Add(
                    new SqlParameter { ParameterName = "DinnerID", DbType = DbType.Int32, Value = id });

                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public void AddDinnerRsvp(int dinnerID, RSVP rsvp)
        {
            using (var connection = new SqlConnection(this.connectionString))
            {
                var commandText =
                @"
                    insert into Rsvp
                    (
                        DinnerID,
                        AttendeeName
                    )
                    values
                    (
                        @DinnerID,
                        @AttendeeName
                    )

                    select cast(SCOPE_IDENTITY() as Integer) as RsvpID 
                ";

                var command = new SqlCommand(commandText, connection);
                var parameters = new[]{
                    new SqlParameter{ParameterName="DinnerID", DbType=DbType.Int32, Value=dinnerID},
                    new SqlParameter{ParameterName="AttendeeName", DbType=DbType.String, Value=rsvp.AttendeeName}};

                command.Parameters.AddRange(parameters);
                connection.Open();
                rsvp.RsvpID = (int)command.ExecuteScalar();
            }
        }


        private SqlParameter[] GetDinnerObjectParameters(Dinner dinner)
        {
            return new[]{
                new SqlParameter{ParameterName="Title", DbType=DbType.String, Value=dinner.Title},
                new SqlParameter{ParameterName="Description", DbType=DbType.String, Value=dinner.Description},
                new SqlParameter{ParameterName="EventDate", DbType=DbType.DateTime, Value=dinner.EventDate},
                new SqlParameter{ParameterName="Address", DbType=DbType.String, Value=dinner.Address},
                new SqlParameter{ParameterName="ContactPhone", DbType=DbType.String, Value=dinner.ContactPhone},
                new SqlParameter{ParameterName="Country", DbType=DbType.String, Value=dinner.Country},
                new SqlParameter{ParameterName="Latitude", DbType=DbType.Double, Value=dinner.Latitude},
                new SqlParameter{ParameterName="Longitude", DbType=DbType.Double, Value=dinner.Longitude},
                new SqlParameter{ParameterName="HostedBy", DbType=DbType.String, Value=dinner.HostedBy}};
        }


        private List<Dinner> GetDinners(SqlCommand command)
        {
            var returnDinners = new List<Dinner>();
            using (var reader = command.ExecuteReader())
            {
                //Project first result set into a collection of Dinner Objects

                while (reader.Read())
                {
                    var dinner = new Dinner()
                    {
                        DinnerID = (int)reader["DinnerID"],
                        Title = (string)reader["Title"],
                        Description = (string)reader["Description"],
                        Address = (string)reader["Address"],
                        ContactPhone = (string)reader["ContactPhone"],
                        Country = (string)reader["Country"],
                        HostedBy = (string)reader["HostedBy"],
                        EventDate = (DateTime)reader["EventDate"],
                        Latitude = (double)reader["Latitude"],
                        Longitude = (double)reader["Longitude"]
                    };
                    returnDinners.Add(dinner);
                }

                //Project second result set into Rsvp objects. Associate them with 
                //their parent dinner object.

                reader.NextResult();

                int? dinnerID = null;
                Dinner parentDinner = null;

                while (reader.Read())
                {
                    var rsvp = new RSVP()
                    {
                        RsvpID = (int)reader["RsvpID"],
                        DinnerID = (int)reader["DinnerID"],
                        AttendeeName = (string)reader["AttendeeName"]
                    };

                    if (dinnerID != rsvp.DinnerID)
                    {
                        dinnerID = rsvp.DinnerID;
                        parentDinner = returnDinners.Where(dinner => dinner.DinnerID == dinnerID).First();
                    }

                    parentDinner.RSVPs.Add(rsvp);
                }
            }

            return returnDinners;
        }

    }
}

idinnerrepository.cs

using System;
using System.Linq;
using NerdDinner.Models;
using System.Collections.Generic;

namespace NerdDinner.Models {

    public interface IDinnerRepository {

        //Data Access Methods

        IEnumerable<Dinner> FindAllDinners();
        IEnumerable<Dinner> FindByLocation(float latitude, float longitude);
        IEnumerable<Dinner> FindUpcomingDinners();
        Dinner GetDinner(int id);

        void AddDinner(Dinner dinner);
        void UpdateDinner(Dinner dinner);
        void DeleteDinner(int id);

        void AddDinnerRsvp(int dinnerID, RSVP rsvp);

        //Object Creation Methods

      
    }
}