public ActionResult RemoveGenre(int id)
{
ShowGenre model = new ShowGenre();
model.showId = id;
model.show = db.Show.Find(id);
model.genres = db.Genre.ToList();
return(View(model));
}
public ActionResult RemoveGenre(ShowGenre model)
{
var genre = db.Genre.FirstOrDefault(m => m.id == model.genreId);
var show = db.Show.FirstOrDefault(m => m.id == model.showId);
show.genres.Remove(genre);
genre.shows.Remove(show);
db.SaveChanges();
return(RedirectToAction("Details/" + model.showId, "Shows", new { area = "" }));
}
internal static void DeleteEntity(ShowGenre item, SqlConnection conn)
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "delete ShowGenre where Id = @Id";
cmd.Parameters.AddWithValue("@Id", item.Id);
cmd.ExecuteNonQuery();
}
}
internal static void InsertEntity(ShowGenre item, SqlConnection conn)
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.Text;
var sql = new StringBuilder();
sql.Append("insert ShowGenre (ShowId, GenreId)");
sql.Append("values (@ShowId, @GenreId);");
sql.Append("select cast ( scope_identity() as int);");
cmd.CommandText = sql.ToString();
SetCommonParameters(item, cmd);
item.Id = (int)cmd.ExecuteScalar();
}
}
private static List <ShowGenre> LoadShowGenres(List <Show> shows)
{
var maxShowGenreId = 0;
var list = new List <ShowGenre>();
ShowGenre showGenre;
showGenre = new ShowGenre
{
Id = ++maxShowGenreId,
ShowId = 1,
GenreId = 1
};
list.Add(showGenre);
FindShowById(shows, showGenre.ShowId).ShowGenres.Add(showGenre);
showGenre = new ShowGenre
{
Id = ++maxShowGenreId,
ShowId = 2,
GenreId = 2
};
list.Add(showGenre);
FindShowById(shows, showGenre.ShowId).ShowGenres.Add(showGenre);
showGenre = new ShowGenre
{
Id = ++maxShowGenreId,
ShowId = 2,
GenreId = 6
};
list.Add(showGenre);
FindShowById(shows, showGenre.ShowId).ShowGenres.Add(showGenre);
showGenre = new ShowGenre
{
Id = ++maxShowGenreId,
ShowId = 2,
GenreId = 7
};
FindShowById(shows, showGenre.ShowId).ShowGenres.Add(showGenre);
list.Add(showGenre);
return(list);
}
internal static void UpdateEntity(ShowGenre item, SqlConnection conn)
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.Text;
var sql = new StringBuilder();
sql.Append("update ShowGenre set ");
sql.Append(" ShowId = @ShowId, ");
sql.Append(" GenreId = @GenreId ");
sql.Append("where Id = @Id");
cmd.CommandText = sql.ToString();
SetCommonParameters(item, cmd);
cmd.Parameters.AddWithValue("@Id", item.Id);
cmd.ExecuteNonQuery();
}
}
public static ShowGenre Persist(ShowGenre showGenre, SqlConnection conn)
{
if (showGenre.Id == 0 && showGenre.IsMarkedForDeletion)
{
showGenre = null;
}
else if (showGenre.IsMarkedForDeletion)
{
DeleteEntity(showGenre, conn);
showGenre = null;
}
else if (showGenre.Id == 0)
{
InsertEntity(showGenre, conn);
showGenre.IsDirty = false;
}
else if (showGenre.IsDirty)
{
UpdateEntity(showGenre, conn);
showGenre.IsDirty = false;
}
return(showGenre);
}
public IEnumerable <Show> Fetch(object criteria = null)
{
var data = new List <Show>();
var connString = ConfigurationManager
.ConnectionStrings["AppConnection"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.Text;
if (criteria == null)
{
var sql = new StringBuilder();
sql.Append("select * from Show; ");
sql.Append("select * from ShowGenre; ");
sql.Append("select * from Credit; ");
cmd.CommandText = sql.ToString();
var dr = cmd.ExecuteReader();
while (dr.Read())
{
var s = new Show();
s.Id = dr.AsInt32("Id");
s.Title = dr.AsString("Title");
s.LengthInMinutes = dr.AsNullableInt32("LengthInMinutes");
s.MpaaRatingId = dr.AsNullableInt32("MpaaRatingId");
s.ReleaseDate
= dr.AsNullableDateTime("ReleaseDate");
data.Add(s);
}
dr.NextResult();
while (dr.Read())
{
var g = new ShowGenre();
g.Id = dr.AsInt32("Id");
g.ShowId = dr.AsInt32("ShowId");
g.GenreId = dr.AsInt32("GenreId");
data.Where(o => o.Id == g.ShowId).Single().ShowGenres.Add(g);
}
dr.NextResult();
while (dr.Read())
{
var c = new Credit();
c.Id = dr.AsInt32("Id");
c.ShowId = dr.AsInt32("ShowId");
c.PersonId = dr.AsInt32("PersonId");
c.CreditTypeId = dr.AsInt32("CreditTypeId");
c.Character = dr.AsString("Character");
data.Where(o => o.Id == c.ShowId).Single().Credits.Add(c);
}
}
else if (criteria is int)
{
var sql = new StringBuilder();
sql.Append("select * from Show where Id = @Id; \r\n");
sql.Append("select * from ShowGenre where ShowId = @Id; \r\n");
sql.Append("select * from Credit where ShowId = @Id; ");
cmd.CommandText = sql.ToString();
cmd.Parameters.AddWithValue("@Id", (int)criteria);
var dr = cmd.ExecuteReader();
var s = new Show();
while (dr.Read())
{
s.Id = dr.AsInt32("Id");
s.Title = dr.AsString("Title");
s.LengthInMinutes = dr.AsNullableInt32("LengthInMinutes");
s.MpaaRatingId = dr.AsNullableInt32("MpaaRatingId");
s.ReleaseDate
= dr.AsNullableDateTime("ReleaseDate");
data.Add(s);
}
dr.NextResult();
while (dr.Read())
{
var g = new ShowGenre();
g.Id = dr.AsInt32("Id");
g.ShowId = dr.AsInt32("ShowId");
g.GenreId = dr.AsInt32("GenreId");
s.ShowGenres.Add(g);
}
dr.NextResult();
while (dr.Read())
{
var c = new Credit();
c.Id = dr.AsInt32("Id");
c.ShowId = dr.AsInt32("ShowId");
c.PersonId = dr.AsInt32("PersonId");
c.CreditTypeId = dr.AsInt32("CreditTypeId");
c.Character = dr.AsString("Character");
s.Credits.Add(c);
}
}
else
{
var msg = String.Format(
"ShowRepository: Unknown criteria type: {0}",
criteria);
throw new InvalidOperationException(msg);
}
}
}
return(data);
}
private static List<ShowGenre> LoadShowGenres(List<Show> shows)
{
var maxShowGenreId = 0;
var list = new List<ShowGenre>();
ShowGenre showGenre;
showGenre = new ShowGenre
{
ShowGenreId = ++maxShowGenreId,
ShowId = 1,
GenreId = 1
};
list.Add(showGenre);
FindShowById(shows, showGenre.ShowId).ShowGenres.Add(showGenre);
showGenre = new ShowGenre
{
ShowGenreId = ++maxShowGenreId,
ShowId = 2,
GenreId = 2
};
list.Add(showGenre);
FindShowById(shows, showGenre.ShowId).ShowGenres.Add(showGenre);
showGenre = new ShowGenre
{
ShowGenreId = ++maxShowGenreId,
ShowId = 2,
GenreId = 6
};
list.Add(showGenre);
FindShowById(shows, showGenre.ShowId).ShowGenres.Add(showGenre);
showGenre = new ShowGenre
{
ShowGenreId = ++maxShowGenreId,
ShowId = 2,
GenreId = 7
};
FindShowById(shows, showGenre.ShowId).ShowGenres.Add(showGenre);
list.Add(showGenre);
return list;
}
private static void SetCommonParameters(ShowGenre item, SqlCommand cmd)
{
cmd.Parameters.AddWithValue("@ShowId", item.ShowId);
cmd.Parameters.AddWithValue("@GenreId", item.GenreId);
}
