}//eom
public void Add_TrackToPLaylist(string playlistname, string username, int trackid)
{
using (var context = new ChinookContext())
{
//Transaction(TRX)
//Query the Playlist table to see if the playlist name exists for the user
//If not
// Create instance of Playlist
// Load
// Add
// Set trackNumber to 1
//if yes
// Query Playlist track for trackID
// If found
// Throw an error
// If not found
// Query Playlist tracks for max tracknumber, then increment++
// Create an instance of PlayListTrack
// Load
// Add
// Save Changes
List <string> errors = new List <string>();
int tracknumber = 0;
PlaylistTrack newtrack = null;
Playlist exists = (from x in context.Playlists
where x.Name.Equals(playlistname) &&
x.UserName.Equals(username)
select x).FirstOrDefault();
if (exists == null)
{
//new playlist
exists = new Playlist();
exists.Name = playlistname;
exists.UserName = username;
context.Playlists.Add(exists);
tracknumber = 1;
}
else
{
//existing playlist
//This basically will make newtrack be null if there is NO instance of that track id in the playlist.
newtrack = (from x in context.PlaylistTracks
where x.Playlist.Name.Equals(playlistname) &&
x.Playlist.UserName.Equals(username) &&
x.TrackId == trackid
select x).FirstOrDefault();
if (newtrack == null)
{
//Adding the new track to the playlist.
//This selects the highest tracknumber
tracknumber = (from x in context.PlaylistTracks
where x.Playlist.Name.Equals(playlistname) &&
x.Playlist.UserName.Equals(username)
select x.TrackNumber).Max();
tracknumber++;
}
else
{
//Track on playlist.
//Business rule states no duplicates
//Throw an exception
//throw new Exception("Track already on the playlist. Duplicates not allowed.");
//throw a business rule exception.
//collect the errors into a List<String>
//After all validation is done, test the collection (List<t>) for
// having any messages, if so, throw new BusinessRuleException()
errors.Add("Track already on the playlist. Duplicates not allowed.");
}
}
//All validation of Playlist and Playstlisttrack is complete
if (errors.Count() > 0)
{
throw new BusinessRuleException("Adding a Track", errors);
}
else
{
//create & load & add a PlaylistTrack
newtrack = new PlaylistTrack();
newtrack.TrackId = trackid;
newtrack.TrackNumber = tracknumber;
exists.PlaylistTracks.Add(newtrack); //stages ONLY, USE THE PARENT
context.SaveChanges(); //physical addition
}
}
}//eom
}//eom
public void MoveTrack(string username, string playlistname, int trackid, int tracknumber, string direction)
{
using (var context = new ChinookContext())
{
var exists = (from x in context.Playlists where x.Name.Equals(playlistname) && x.UserName.Equals(username) select x).FirstOrDefault();
if (exists == null)
{
throw new Exception("Playlist has been removed from the files");
}
else
{
PlaylistTrack moveTrack = (from x in exists.PlaylistTracks where x.TrackId.Equals(trackid) select x).FirstOrDefault();
if (moveTrack == null)
{
throw new Exception("Playlist track has been removed from the files");
}
else
{
//create an instance pointer to be used to point to the other track involved in the move (above or below)
PlaylistTrack otherTrack = null;
if (direction.Equals("up"))
{
//up
//recheck that the track is not the first track
//if so, throw an error
//otherwise, move the track
if (moveTrack.TrackNumber == 1)
{
throw new Exception("Playlist track already at top");
}
else
{
otherTrack = (from x in exists.PlaylistTracks where x.TrackNumber == moveTrack.TrackNumber - 1 select x).FirstOrDefault();
if (otherTrack == null)
{
throw new Exception("Switching track is missing");
}
else
{
moveTrack.TrackNumber -= 1;
otherTrack.TrackNumber += 1;
}
}
}
else
{
//down
if (moveTrack.TrackNumber == exists.PlaylistTracks.Count())
{
throw new Exception("Playlist track already at bottom");
}
else
{
otherTrack = (from x in exists.PlaylistTracks where x.TrackNumber == moveTrack.TrackNumber + 1 select x).FirstOrDefault();
if (otherTrack == null)
{
throw new Exception("Switching track is missing");
}
else
{
moveTrack.TrackNumber += 1;
otherTrack.TrackNumber -= 1;
}
}
}
//save the changes to the data
//we are saving 2 different entities
//indicate the property to save for a particular entity instance
context.Entry(moveTrack).Property(y => y.TrackNumber).IsModified = true;
context.Entry(otherTrack).Property(x => x.TrackNumber).IsModified = true;
//commit your changes
context.SaveChanges();
}
}
}
}//eom
public List <TrackList> List_TracksForPlaylistSelection(string tracksby, int argid)
{
using (var context = new ChinookContext())
{
List <TrackList> results = null;
switch (tracksby)
{
case "Artist":
{
results = (from x in context.Tracks
orderby x.Name
where x.Album.ArtistId == argid
select new TrackList
{
TrackID = x.TrackId,
Name = x.Name,
Title = x.Album.Title,
MediaName = x.MediaType.Name,
GenreName = x.Genre.Name,
Composer = x.Composer,
Milliseconds = x.Milliseconds,
Bytes = x.Bytes,
UnitPrice = x.UnitPrice
}).ToList();
break;
}
case "MediaType":
{
results = (from x in context.Tracks
orderby x.Name
where x.MediaType.MediaTypeId == argid
select new TrackList
{
TrackID = x.TrackId,
Name = x.Name,
Title = x.Album.Title,
MediaName = x.MediaType.Name,
GenreName = x.Genre.Name,
Composer = x.Composer,
Milliseconds = x.Milliseconds,
Bytes = x.Bytes,
UnitPrice = x.UnitPrice
}).ToList();
break;
}
case "Genre":
{
results = (from x in context.Tracks
orderby x.Name
where x.Genre.GenreId == argid
select new TrackList
{
TrackID = x.TrackId,
Name = x.Name,
Title = x.Album.Title,
MediaName = x.MediaType.Name,
GenreName = x.Genre.Name,
Composer = x.Composer,
Milliseconds = x.Milliseconds,
Bytes = x.Bytes,
UnitPrice = x.UnitPrice
}).ToList();
break;
}
default:
{
results = (from x in context.Tracks
orderby x.Name
where x.AlbumId == argid
select new TrackList
{
TrackID = x.TrackId,
Name = x.Name,
Title = x.Album.Title,
MediaName = x.MediaType.Name,
GenreName = x.Genre.Name,
Composer = x.Composer,
Milliseconds = x.Milliseconds,
Bytes = x.Bytes,
UnitPrice = x.UnitPrice
}).ToList();
break;
}
}//eos
return(results);
}
}
}//eom
public void MoveTrack(string username, string playlistname, int trackid, int tracknumber, string direction)
{
using (var context = new ChinookContext())
{
var exists = (from x in context.Playlists
where x.UserName.Equals(username) &&
x.Name.Equals(playlistname)
select x).FirstOrDefault();
if (exists == null)
{
throw new Exception("Play list has been removed from the file");
}
else
{
PlaylistTrack moveTrack = (from x in exists.PlaylistTracks
where x.TrackId == trackid
select x).FirstOrDefault();
if (moveTrack == null)
{
throw new Exception("Play list song has been removed from the file.");
}
else
{
//up
PlaylistTrack otherTrack = null;
if (direction.Equals("up"))
{
if (moveTrack.TrackNumber == 1)
{
throw new Exception("Play list song already at the top");
}
else
{
//move it
otherTrack = (from x in exists.PlaylistTracks
where x.TrackNumber == moveTrack.TrackNumber - 1
select x).FirstOrDefault();
if (otherTrack == null)
{
throw new Exception("Other play list song is missing.");
}
else
{
//good to move
moveTrack.TrackNumber -= 1;
otherTrack.TrackNumber += 1;
}
}
}
else
{
//down
if (moveTrack.TrackNumber == exists.PlaylistTracks.Count)
{
throw new Exception("Play list song already at the bottom");
}
else
{
//move it
otherTrack = (from x in exists.PlaylistTracks
where x.TrackNumber == moveTrack.TrackNumber + 1
select x).FirstOrDefault();
if (otherTrack == null)
{
throw new Exception("Other play list song is missing.");
}
else
{
//good to move
moveTrack.TrackNumber += 1;
otherTrack.TrackNumber -= 1;
}
}
}
//update database
//a field update NOT an entity update --> does field by field because POCOS.
//if you're doing CRUD (change a field or any number) --> do an entity update. Here we only have one field to change so we do one field instead
context.Entry(moveTrack).Property(y => y.TrackNumber).IsModified = true;
context.Entry(otherTrack).Property(y => y.TrackNumber).IsModified = true;
//commit transaction
context.SaveChanges();
}
}
}
}//eom
}//eom
public void MoveTrack(string username, string playlistname, int trackid, int tracknumber, string direction)
{
using (var context = new ChinookContext())
{
//get Playlist ID
var exists = (from x in context.Playlists
where x.UserName.Equals(username, StringComparison.OrdinalIgnoreCase) &&
x.Name.Equals(playlistname, StringComparison.OrdinalIgnoreCase)
select x).FirstOrDefault();
if (exists == null)
{
throw new Exception("Playlist does not exist.");
}
else
{
PlaylistTrack moveTrack = (from x in exists.PlaylistTracks
where x.TrackId == trackid
select x).FirstOrDefault();
if (moveTrack == null)
{
throw new Exception("Playlist does not exist.");
}
else
{
PlaylistTrack otherTrack = null;
//up or down
if (direction.Equals("up"))
{
//up
if (tracknumber == 1)
{
throw new Exception("Track 1 cannot be moved up.");
}
else
{
//find the other track
otherTrack = (from x in exists.PlaylistTracks
where x.TrackNumber == moveTrack.TrackNumber - 1
select x).FirstOrDefault();
if (otherTrack == null)
{
throw new Exception("Play list is corrupt. Fetch play list again.");
}
else
{
moveTrack.TrackNumber -= 1;
otherTrack.TrackNumber += 1;
}
}
}
else
{
//down
if (tracknumber == exists.PlaylistTracks.Count())
{
throw new Exception("Last track cannot be moved down.");
}
else
{
//find the other track
otherTrack = (from x in exists.PlaylistTracks
where x.TrackNumber == moveTrack.TrackNumber + 1
select x).FirstOrDefault();
if (otherTrack == null)
{
throw new Exception("Play list is corrupt. Fetch play list again.");
}
else
{
moveTrack.TrackNumber += 1;
otherTrack.TrackNumber -= 1;
}
}
}//eof up or down
//staging
context.Entry(moveTrack).Property(y => y.TrackNumber).IsModified = true;
context.Entry(otherTrack).Property(y => y.TrackNumber).IsModified = true;
//commit
context.SaveChanges();
}
}
}
}//eom
}//eom
public void MoveTrack(string username, string playlistname, int trackid, int tracknumber, string direction)
{
using (var context = new ChinookContext())
{
//code to go here
//query to get playlistid
var exists = (from x in context.Playlists
where x.UserName.Equals(username) &&
x.Name.Equals(playlistname)
select x).FirstOrDefault();
if (exists == null)
{
throw new Exception("Playlist has been removed");
}
else
{
//limit your search to the particular playlist
PlaylistTrack movetrack = (from x in exists.PlaylistTracks
where x.TrackId == trackid
select x).FirstOrDefault();
if (movetrack == null)
{
throw new Exception("Playlist track has been removed");
}
else
{
//up
PlaylistTrack othertrack = null;
if (direction.Equals("up"))
{
if (movetrack.TrackNumber == 1)
{
throw new Exception("Playlist track cannot be moved");
}
else
{
//get the other track
othertrack = (from x in exists.PlaylistTracks
where x.TrackNumber == movetrack.TrackNumber - 1
select x).FirstOrDefault();
if (othertrack == null)
{
throw new Exception("Playlist track cannot be moved");
}
else
{
// at this point you can exchange track numbers
movetrack.TrackNumber -= 1;
othertrack.TrackNumber += 1;
}
}
}
else
{//down
if (movetrack.TrackNumber == exists.PlaylistTracks.Count)
{
throw new Exception("Playlist track cannot be moved down");
}
else
{
//get the other track
othertrack = (from x in exists.PlaylistTracks
where x.TrackNumber == movetrack.TrackNumber + 1
select x).FirstOrDefault();
if (othertrack == null)
{
throw new Exception("Playlist track cannot be moved down");
}
else
{
// at this point you can exchange track numbers
movetrack.TrackNumber += 1;
othertrack.TrackNumber -= 1;
}
}
}// end of up/down
//stage changes for SaveChanges()
//indicate only the field tjat meeds to be updated
context.Entry(movetrack).Property(y => y.TrackNumber).IsModified = true;
context.Entry(othertrack).Property(y => y.TrackNumber).IsModified = true;
context.SaveChanges();
}
}
}
}//eom
}//eom
public void Add_TrackToPLaylist(string playlistname, string username, int trackid)
{
using (var context = new ChinookContext())
{
//Use the businessRuleExeption to throw errors to the Web Page
List <string> reasons = new List <string>();
PlaylistTrack newTrack = null;
int tracknumber = 0;
//Part One:
//Determine if Playlist exists
//query the table using the playlist name and username
//if the playlist exists, one will get a record
//if the playlist does not exist, one will get a NULL
//to ensure this results the query will be wrap in a .FirstOrDefault()
//Playlist exists = context.Playlists
// .Where(x => x.UserName.Equals(username, StringComparison.OrdinalIgnoreCase)
// && x.Name.Equals(playlistname, StringComparison.OrdinalIgnoreCase))
// .Select(x => x)
// .FirstOrDefault();
Playlist exists = (from x in context.Playlists
.Where(x => x.UserName.Equals(username, StringComparison.OrdinalIgnoreCase) &&
x.Name.Equals(playlistname, StringComparison.OrdinalIgnoreCase))
select x).FirstOrDefault();
//Does the Playlist exists
if (exists == null)
{
//this is a new playlist
//create the Playlist record
exists = new Playlist();
exists.Name = playlistname;
exists.UserName = username;
//stage the add
exists = context.Playlists.Add(exists);
//since this is a new playlist, the track number will be one
tracknumber = 1;
}
else
{
//Since the playlist exists, so may the track exist on the olaylisttracks
newTrack = exists.PlaylistTracks.SingleOrDefault(x => x.TrackId == trackid);
if (newTrack == null)
{
tracknumber = exists.PlaylistTracks.Count() + 1;
}
else
{
reasons.Add("Track Already Exists on Playlist");
}
}
//Part Two:
//Create the playlisttrack entry
//if there are any reasons not to create, then throw the businessRuleExeption
if (reasons.Count() > 0)
{
//issue with adding the track
throw new BusinessRuleException("Adding track to playlist", reasons);
}
else
{
//Use the Playlist navigation to playlisttracks to do the add to playlisttracks
newTrack = new PlaylistTrack();
newTrack.TrackId = trackid;
newTrack.TrackNumber = tracknumber;
//Note: the PK for playlistId may not yet exist
//using the navigation property on the PlayList entity one can let HshSet handle the PlayListId PKey
//value ti be properly created on PlayList ABD placed correctly in the "child" record of PlayListTracks
//What is wrong to the attempt: newTrack.PlayListId = exists.PlayListId;
exists.PlaylistTracks.Add(newTrack); //PlayListTrack staging
//Physically add any/all data to the database
//commit
context.SaveChanges();
}
}
}//eom
}//eom
public void MoveTrack(string username, string playlistname, int trackid, int tracknumber, string direction)
{
using (var context = new ChinookContext())
{
var exists = (from x in context.Playlists
where x.UserName == username && x.Name.Equals(playlistname)
select x).FirstOrDefault();
if (exists == null)
{
throw new Exception("Playlist no longer exist.");
}
else
{
//limit serach to specific playlist
PlaylistTrack movedtrack = (from x in exists.PlaylistTracks where x.TrackId == trackid select x).FirstOrDefault();
if (movedtrack == null)
{
throw new Exception("Track no longer exists on this playlist");
}
else
{
PlaylistTrack collateraltrack = null;
if (direction == "up")
{
//not necessary
if (movedtrack.TrackNumber == 1)
{
throw new Exception("Track cannot get any higher. Track cannot moved.");
}
else
{
collateraltrack = (from x in exists.PlaylistTracks where x.TrackNumber == movedtrack.TrackNumber - 1 select x).FirstOrDefault();
if (collateraltrack == null)
{
throw new Exception("playlist track cannot move up");
}
else
{
//at this point we can switch track numbers
movedtrack.TrackNumber -= 1;
collateraltrack.TrackNumber += 1;
}
}
}
else
{
if (movedtrack.TrackNumber == exists.PlaylistTracks.Count)
{
throw new Exception("Track cannot get any lower. Track cannot moved.");
}
else
{
collateraltrack = (from x in exists.PlaylistTracks where x.TrackNumber == movedtrack.TrackNumber + 1 select x).FirstOrDefault();
if (collateraltrack == null)
{
throw new Exception("playlist track cannot move down");
}
else
{
//at this point
movedtrack.TrackNumber += 1;
collateraltrack.TrackNumber -= 1;
}
}
}
//stage for update
//indicate only thr filed that needs an update
context.Entry(movedtrack).Property(y => y.TrackNumber).IsModified = true;
context.Entry(collateraltrack).Property(y => y.TrackNumber).IsModified = true;
context.SaveChanges();
}
}
}
}//eom
public List <TrackList> List_TracksForPlaylistSelection(string tracksby, string arg)
{
using (var context = new ChinookContext())
{
//we have to consider that there is a string value and the type for the artist
//for album we also have a string and the type so when we do teh lookup, my where clause, value contains....whatever my string is
//and of a table type= x , but we can't do that with the genre or the media type, because even though it comes in as a string, I need an int and tabletype
//the where clause will be similar pkey=int and the table type =p and we will have another one, pkey=int and tabletype =k.
//probelm is that we want 1 return with 2 different reports.
//check the incoming parameters and if needed set to a default value, ODS's fire when your page comes up
if (string.IsNullOrEmpty(tracksby))
{
tracksby = ""; //mainly to catch the null situation
}
if (string.IsNullOrEmpty(arg))
{
arg = "";
} //now we know we have an object, this is not going to be empty.
//next, we set these values to a particular item (id/string)
//create 2 local variables representing the argument as a) an integer b) as a string
int argid = 0;
string argstring = "zyxzz"; //<--selected something that would likely never exist as my default value
//determine if incoming argument should be integer or string
if (tracksby.Equals("Genre") || tracksby.Equals("MediaType"))
{
argid = int.Parse(arg);
}
else
{
argstring = arg.Trim();
}
var results = (from x in context.Tracks
where (x.GenreId == argid && tracksby.Equals("Genre")) ||
(x.MediaTypeId == argid && tracksby.Equals("MediaType"))
select new TrackList
{
TrackID = x.TrackId,
Name = x.Name,
Title = x.Album.Title,
ArtistName = x.Album.Artist.Name,
MediaName = x.MediaType.Name,
GenreName = x.Genre.Name,
Composer = x.Composer,
Milliseconds = x.Milliseconds,
Bytes = x.Bytes,
UnitPrice = x.UnitPrice
}
).Union(
from x in context.Tracks
//going to change the style in which we ask this, so now the ternary operator is up!
where tracksby.Equals("Artist") ? x.Album.Artist.Name.Contains(argstring) :
tracksby.Equals("Album") ? x.Album.Title.Contains(argstring) : false
select new TrackList
{
TrackID = x.TrackId,
Name = x.Name,
Title = x.Album.Title,
ArtistName = x.Album.Artist.Name,
MediaName = x.MediaType.Name,
GenreName = x.Genre.Name,
Composer = x.Composer,
Milliseconds = x.Milliseconds,
Bytes = x.Bytes,
UnitPrice = x.UnitPrice
}
);
return(results.ToList());
}
} //eom
public List <TrackList> List_TracksForPlaylistSelection(string tracksby, string arg)
{
using (var context = new ChinookContext())
{
if (string.IsNullOrEmpty(tracksby))
{
tracksby = "";
}
if (string.IsNullOrEmpty(arg))
{
arg = "";
}
int argid = 0;
string argstring = "zyezz";
if (tracksby.Equals("Genre") || tracksby.Equals("MediaType"))
{
argid = int.Parse(arg);
}
else
{
argstring = arg.Trim();
}
var results = (from x in context.Tracks where (x.GenreId == argid && tracksby.Equals("Genre")) ||
(x.MediaTypeId == argid && tracksby.Equals("MediaType"))
select new TrackList
{
TrackID = x.TrackId,
Name = x.Name,
Title = x.Album.Title,
ArtistName = x.Album.Artist.Name,
MediaName = x.MediaType.Name,
GenreName = x.Genre.Name,
Composer = x.Composer,
Milliseconds = x.Milliseconds,
Bytes = x.Bytes,
UnitPrice = x.UnitPrice
}
)
.Union(from x in context.Tracks
where tracksby.Equals("Artist")? x.Album.Artist.Name.Contains(argstring):
tracksby.Equals("Album") ? x.Album.Title.Contains(argstring):false
select new TrackList
{
TrackID = x.TrackId,
Name = x.Name,
Title = x.Album.Title,
ArtistName = x.Album.Artist.Name,
MediaName = x.MediaType.Name,
GenreName = x.Genre.Name,
Composer = x.Composer,
Milliseconds = x.Milliseconds,
Bytes = x.Bytes,
UnitPrice = x.UnitPrice
});
return(results.ToList());
}
} //eom
}//eom
//this method is an OLTP conplex method
//this method may alter multiple tracks
//the method
public void Add_TrackToPLaylist(string playlistname, string username, int trackid)
{
//the using sets up the transaction environment
//if the logic does not reach a .SaveChanges() method
//all work is rolled back.
//a list of string to be used to handle any number of errors
//generated while doing the transaction
//all errors can then be returned to the MessageUserControl
List <String> resons = null;
using (var context = new ChinookContext())
{
//code to go here
//Part One
//determine if a new playlist is needed
//determine the tracknumber dependent of if a playlist already exists.
Playlist exists = context.Playlists.Where(x => x.UserName.Equals(username, StringComparison.OrdinalIgnoreCase) &&
x.Name.Equals(playlistname, StringComparison.OrdinalIgnoreCase))
.Select(x => x).FirstOrDefault();
//create an instance for PlaylistTrack
PlaylistTrack newTrack = null;
//initialize a local tracknumber
int tracknumber = 0;
if (exists == null)
{
//this is a new playlist being created
exists = new Playlist();
exists.Name = playlistname;
exists.UserName = username;
exists = context.Playlists.Add(exists);
tracknumber = 1;
}
else
{
//this is an existing playlist
//calculate the new proposed tracknumber
tracknumber = exists.PlaylistTracks.Count() + 1;
//business rule: track may only exists once on a playlist
//it may exists on many different playlist
//.SingleOrDefault expects a single instance to be returned
newTrack = exists.PlaylistTracks.SingleOrDefault(
x => x.TrackId == trackid);
if (newTrack != null)
{
resons.Add("Track already exists on the playlist.");
}
}
if (resons.Count() > 0)
{
//issue the BusinessRuleExpection(title, list of error strings)
throw new BusinessRuleException("Adding track to playlist", resons);
}
else
{
//Part Two : Add the track
newTrack = new PlaylistTrack();
newTrack.TrackId = trackid;
newTrack.TrackNumber = tracknumber;
//what about the playlistid?
//Note: the Pkey for playlistId may not yet exists
//using navigatiion one can let HashSet handle the expected
//playlist Pkey vlaue
exists.PlaylistTracks.Add(newTrack);
//at this point all records are in staged state
//physicall add all data for the transaction to
//the database and commit
context.SaveChanges();
}
}
}//eom
public List <TrackList> List_TracksForPlaylistSelection(string tracksby, int argid)
{
using (var context = new ChinookContext())
{
IEnumerable <TrackList> results = null;
//code to go here.
switch (tracksby)
{
case "Artist":
{
results = from x in context.Tracks
orderby x.Name
where x.Album.ArtistId == argid
select new TrackList
{
TrackID = x.TrackId,
Name = x.Name,
Title = x.Album.Title,
MediaName = x.MediaType.Name,
GenreName = x.Genre.Name,
Composer = x.Composer,
Milliseconds = x.Milliseconds,
Bytes = x.Bytes,
UnitPrice = x.UnitPrice
};
break;
}
case "MediaType":
{
results = from x in context.Tracks
orderby x.Name
where x.MediaTypeId == argid
select new TrackList
{
TrackID = x.TrackId,
Name = x.Name,
Title = x.Album.Title,
MediaName = x.MediaType.Name,
GenreName = x.Genre.Name,
Composer = x.Composer,
Milliseconds = x.Milliseconds,
Bytes = x.Bytes,
UnitPrice = x.UnitPrice
};
break;
}
case "Genre":
{
results = from x in context.Tracks
orderby x.Name
where x.GenreId == argid
select new TrackList
{
TrackID = x.TrackId,
Name = x.Name,
Title = x.Album.Title,
MediaName = x.MediaType.Name,
GenreName = x.Genre.Name,
Composer = x.Composer,
Milliseconds = x.Milliseconds,
Bytes = x.Bytes,
UnitPrice = x.UnitPrice
};
break;
}
default:
{
results = from x in context.Tracks
orderby x.Name
where x.AlbumId == argid
select new TrackList
{
TrackID = x.TrackId,
Name = x.Name,
Title = x.Album.Title,
MediaName = x.MediaType.Name,
GenreName = x.Genre.Name,
Composer = x.Composer,
Milliseconds = x.Milliseconds,
Bytes = x.Bytes,
UnitPrice = x.UnitPrice
};
break;
}
//results = from x in context.Tracks
// orderby x.Name
// where tracksby.Equals... to be finished
//}
}
return(results.ToList());
}
}//eom
}//eom
public void MoveTrack(string username, string playlistname, int trackid, int tracknumber, string direction)
{
using (var context = new ChinookContext())
{
//code to go here
var exists = (from x in context.Playlists
where x.UserName.Equals(username) &&
x.Name.Equals(playlistname)
select x).FirstOrDefault();
if (exists == null)
{
throw new Exception("Playlist has been removed from the file");
}
else
{
PlaylistTrack moveTrack = (from x in exists.PlaylistTracks
where x.TrackId == trackid
select x).FirstOrDefault();
if (moveTrack == null)
{
throw new Exception("Playlist song has been removed from the file.");
}
else
{
PlaylistTrack otherTrack = null;
if (direction.Equals("up"))
{
if (moveTrack.TrackNumber == 1)
{
throw new Exception("Playlist song already at the top");
}
else
{
otherTrack = (from x in exists.PlaylistTracks
where x.TrackNumber == moveTrack.TrackNumber - 1
select x).FirstOrDefault();
if (otherTrack == null)
{
throw new Exception("Other playlist song is missing");
}
else
{
moveTrack.TrackNumber -= 1;
otherTrack.TrackNumber += 1;
}
}
}
else
{
if (moveTrack.TrackNumber == exists.PlaylistTracks.Count)
{
throw new Exception("Playlist song is already at the bottom");
}
else
{
otherTrack = (from x in exists.PlaylistTracks
where x.TrackNumber == moveTrack.TrackNumber + 1
select x).FirstOrDefault();
if (otherTrack == null)
{
throw new Exception("Other playlist song is missing");
}
else
{
moveTrack.TrackNumber += 1;
otherTrack.TrackNumber -= 1;
}
}
}
//We are only updating the field not the ENTITY
context.Entry(moveTrack).Property(y => y.TrackNumber).IsModified = true;
context.Entry(otherTrack).Property(y => y.TrackNumber).IsModified = true;
//Commit Transaction
context.SaveChanges();
}
}
}
}//eom
public List <TracksForPlaylistSelection> Get_TracksForPlaylistSelection(int id, string fetchby)
{
List <TracksForPlaylistSelection> results = null;
using (var context = new ChinookContext())
{
switch (fetchby)
{
case "Artist":
{
results = (from x in context.Tracks
where x.Album.ArtistId == id
select new TracksForPlaylistSelection
{
TrackId = x.TrackId,
Name = x.Name,
Title = x.Album.Title,
MediaName = x.MediaType.Name,
GenreName = x.Genre.Name,
Composer = x.Composer,
Milliseconds = x.Milliseconds,
Bytes = x.Bytes,
UnitPrice = x.UnitPrice
}).ToList();
break;
}
case "Media":
{
results = (from x in context.Tracks
where x.MediaType.MediaTypeId == id
select new TracksForPlaylistSelection
{
TrackId = x.TrackId,
Name = x.Name,
Title = x.Album.Title,
MediaName = x.MediaType.Name,
GenreName = x.Genre.Name,
Composer = x.Composer,
Milliseconds = x.Milliseconds,
Bytes = x.Bytes,
UnitPrice = x.UnitPrice
}).ToList();
break;
}
case "Genre":
{
results = (from x in context.Tracks
where x.Genre.GenreId == id
select new TracksForPlaylistSelection
{
TrackId = x.TrackId,
Name = x.Name,
Title = x.Album.Title,
MediaName = x.MediaType.Name,
GenreName = x.Genre.Name,
Composer = x.Composer,
Milliseconds = x.Milliseconds,
Bytes = x.Bytes,
UnitPrice = x.UnitPrice
}).ToList();
break;
}
default:
{
results = (from x in context.Tracks
where x.Album.AlbumId == id
select new TracksForPlaylistSelection
{
TrackId = x.TrackId,
Name = x.Name,
Title = x.Album.Title,
MediaName = x.MediaType.Name,
GenreName = x.Genre.Name,
Composer = x.Composer,
Milliseconds = x.Milliseconds,
Bytes = x.Bytes,
UnitPrice = x.UnitPrice
}).ToList();
break;
}
}
}
return(results);
}
public void Add_TrackToPlaylist(string playlistname, string username, int trackid)
{
using (var context = new ChinookContext())
{
//does the playlist already exist
Playlist exists = (from x in context.Playlists
where x.UserName.Equals(username) &&
x.Name.Equals(playlistname)
select x).FirstOrDefault();
int tracknumber = 0;
PlaylistTracks newtrack = null;
if (exists == null)
{
//create the new Playlist
exists = new Playlist();
exists.Name = playlistname;
exists.UserName = username;
exists = context.Playlists.Add(exists);
tracknumber = 1;
}
else
{
//the playlist already exists
//and the query has given as the instance of that playlist from the DB
//generate the next tracknumber
tracknumber = exists.PlaylistTracks.Count();
//on our sample, playlist tracks for a playlist
//are unique
newtrack = exists.PlaylistTracks.SingleOrDefault(x => x.TrackId == trackid);
if (newtrack != null)
{
throw new Exception("Playlist already has a requested track.");
}
}
//this is a boom test
//remove after testing
//if (playlistname.Equals("Boom"))
//{
// throw new Exception("forced abort, check DB for Boom playlist");
//}
//you have a playlist
//you know the track will be unique
//create the new track
newtrack = new PlaylistTracks();
newtrack.TrackId = trackid;
newtrack.TrackNumber = tracknumber;
//since i am using the navigation property of the
//playlist to get to playlisttrack
//the savechanges will fill the playlistID
//from either the hashset or from the existing instance
exists.PlaylistTracks.Add(newtrack);
context.SaveChanges();
}
}
}//eom
public void MoveTrack(string username, string playlistname, int trackid, int tracknumber, string direction)
{
using (var context = new ChinookContext())
{
//get playlist id
var exists = (from x in context.Playlists
where x.UserName.Equals(username, StringComparison.OrdinalIgnoreCase) && x.Name.Equals(playlistname, StringComparison.OrdinalIgnoreCase)
select x).FirstOrDefault();
if (exists == null)
{
throw new Exception("Playlist does not exist.");
}
else
{
//get track id
PlaylistTrack moveTrack = (from x in exists.PlaylistTracks
where x.TrackId == trackid
select x).FirstOrDefault();
if (moveTrack == null)
{
throw new Exception("Track does not exist on playlist.");
}
else
{
PlaylistTrack otherTrack = null;
//check direction
if (direction.Equals("up"))
{
if (tracknumber == 1)
{
throw new Exception("Track 1 cannot be moved higher.");
}
else
{
otherTrack = (from x in exists.PlaylistTracks
where x.TrackNumber == moveTrack.TrackNumber - 1
select x).FirstOrDefault();
if (otherTrack == null)
{
throw new Exception("Cannot move these tracks; playlist has been corrupted. Try fetching playlist again.");
}
else
{
moveTrack.TrackNumber -= 1;
otherTrack.TrackNumber += 1;
}
}
}
else
{
if (tracknumber == exists.PlaylistTracks.Count())
{
throw new Exception("Track is already at the bottom.");
}
else
{
otherTrack = (from x in exists.PlaylistTracks
where x.TrackNumber == moveTrack.TrackNumber + 1
select x).FirstOrDefault();
if (otherTrack == null)
{
throw new Exception("Cannot move these tracks; playlist has been corrupted. Try fetching playlist again.");
}
else
{
moveTrack.TrackNumber += 1;
otherTrack.TrackNumber -= 1;
}
}
}//end of direction code
context.Entry(moveTrack).Property(y => y.TrackNumber).IsModified = true;
context.Entry(otherTrack).Property(y => y.TrackNumber).IsModified = true; //staged changes
context.SaveChanges(); //committed
}
}
}
}//eom
}//eom
public void MoveTrack(string username, string playlistname, int trackid, int tracknumber, string direction)
{
using (var context = new ChinookContext())
{
//get playlistID
var exists = (from x in context.Playlists
where x.UserName.Equals(username, StringComparison.OrdinalIgnoreCase) && x.Name.Equals(playlistname, StringComparison.OrdinalIgnoreCase)
select x
).FirstOrDefault();
if (exists == null)
{
throw new Exception("Playlist does not exists.");
}
else
{
PlaylistTrack moveTrack = (from x in exists.PlaylistTracks
where x.TrackId == trackid
select x).FirstOrDefault();
if (moveTrack == null)
{
throw new Exception("Playlist track does not exist.");
}
else
{
PlaylistTrack otherTrack = null;
if (direction.Equals("up"))
{
if (tracknumber == 1)
{
throw new Exception("Track is already on the top");
}
else
{
//find another track
otherTrack = (from x in exists.PlaylistTracks
where x.TrackNumber == moveTrack.TrackNumber - 1
select x).FirstOrDefault();
if (otherTrack == null)
{
throw new Exception("Track is already on the top,refresh the playlist");
}
else
{
moveTrack.TrackNumber -= 1;
otherTrack.TrackNumber += 1;
}
}
}
else
{
if (tracknumber == exists.PlaylistTracks.Count())
{
throw new Exception("Track is already on the bottom");
}
else
{
//find another track
otherTrack = (from x in exists.PlaylistTracks
where x.TrackNumber == moveTrack.TrackNumber + 1
select x).FirstOrDefault();
if (otherTrack == null)
{
throw new Exception("Track is already on the bottom,refresh the playlist");
}
else
{
moveTrack.TrackNumber += 1;
otherTrack.TrackNumber -= 1;
}
}
}//eoif up down
context.Entry(moveTrack).Property(y => y.TrackNumber).IsModified = true;
context.Entry(otherTrack).Property(y => y.TrackNumber).IsModified = true;
context.SaveChanges();
}
}
}
}//eom
}//eom
public void Add_TrackToPLaylist(string playlistname, string username, int trackid)
{
using (var context = new ChinookContext())
{
//throw errors with BusinessRuleException
List <string> reasons = new List <string>();
PlaylistTrack newTrack = null;
int trackNumber = 0;
//find if playlist exists using .IfFirstOrDefault
Playlist exists = context.Playlists
.Where(x => x.UserName.Equals(username, StringComparison.OrdinalIgnoreCase) &&
x.Name.Equals(playlistname, StringComparison.OrdinalIgnoreCase))
.Select(x => x)
.FirstOrDefault();
////or in query syntax:
//(from x in context.Playlists
// where(x => x.UserName.Equals(username, StringComparison.OrdinalIgnoreCase)
// && x.Name.Equals(playlistname, StringComparison.OrdinalIgnoreCase))
// select x).FirstOrDefault();
if (exists == null)
{
//create new playlist
exists = new Playlist();
exists.Name = playlistname;
exists.UserName = username;
exists = context.Playlists.Add(exists); //staged
trackNumber = 1;
}
else
{
//find new track number
newTrack = exists.PlaylistTracks.SingleOrDefault(x => x.TrackId == trackid); //null if doesn't exist
if (newTrack == null)
{
trackNumber = exists.PlaylistTracks.Count() + 1;
}
else
{
reasons.Add("Track already on playlist.");
}
}
//create playlist tracks after checking for errors in creation
if (reasons.Count() > 0)
{
throw new BusinessRuleException("Adding track to playlist", reasons);
}
else
{
//navigate through Playlist to PlaylistTracks
newTrack = new PlaylistTrack();
newTrack.TrackId = trackid;
newTrack.TrackNumber = trackNumber;
//if PK for Playlist doesn't exist, going through navigational properties on Playlist and using the Hashset will find the correct ID and add to any child records. exists.PlaylistID has a bad PK; don't use it.
exists.PlaylistTracks.Add(newTrack); //stages playlist track
context.SaveChanges(); //commits the playlist and all tracks
}
}
}//eom
}//eom
public void MoveTrack(string username, string playlistname, int trackid, int tracknumber, string direction)
{
using (var context = new ChinookContext())
{
var exists = (from x in context.Playlists where x.UserName.Equals(username, StringComparison.OrdinalIgnoreCase) &&
x.Name.Equals(playlistname, StringComparison.OrdinalIgnoreCase) select x).FirstOrDefault();
if (exists == null)
{
throw new Exception("playlist does not exist");
}
else
{
PlaylistTrack movetrack = (from x in context.PlaylistTracks where x.TrackId == trackid select x).FirstOrDefault();
if (movetrack == null)
{
throw new Exception("track can not be empty");
}
else
{
PlaylistTrack othertrack = null;
if (direction.Equals("up"))
{
if (tracknumber == 1)
{
throw new Exception("track 1 cannot be moved up");
}
else
{
othertrack = (from x in exists.PlaylistTracks where x.TrackNumber == movetrack.TrackNumber - 1 select x).FirstOrDefault();
if (othertrack == null)
{
throw new Exception("play list is corrupt.fecth play list again");
}
else
{
movetrack.TrackNumber -= 1;
othertrack.TrackNumber += 1;
}
}
}
else
{
if (tracknumber == exists.PlaylistTracks.Count())
{
throw new Exception("track 1 cannot be moved down");
}
else
{
othertrack = (from x in exists.PlaylistTracks where x.TrackNumber == movetrack.TrackNumber - 1 select x).FirstOrDefault();
if (othertrack == null)
{
throw new Exception("play list is corrupt.fecth play list again");
}
else
{
movetrack.TrackNumber += 1;
othertrack.TrackNumber -= 1;
}
}
}
context.Entry(movetrack).Property(y => y.TrackNumber).IsModified = true;
context.Entry(othertrack).Property(y => y.TrackNumber).IsModified = true;
context.SaveChanges();
}
}
}
}//eom
}//eom
public void MoveTrack(string username, string playlistname, int trackid, int tracknumber, string direction)
{
using (var context = new ChinookContext())
{
var existingTrack = (
from x in context.Playlists
where
x.Name.Equals(playlistname)
&&
x.UserName.Equals(username)
select x)
.FirstOrDefault();
if (existingTrack == null)
{
throw new Exception("Playlist not found on file.");
}
else
{
PlaylistTrack movedTrack = (
from x in existingTrack.PlaylistTracks
where x.TrackId == trackid
select x)
.FirstOrDefault();
if (movedTrack == null)
{
throw new Exception("Playlist track not found on file.");
}
else
{
PlaylistTrack otherTrack;
// Track movement
if (direction.Equals("up"))
{
// Ensure track is not already the first track
if (movedTrack.TrackNumber == 1)
{
throw new Exception("Playlist track already at the top.");
}
else
{
// Find the track being switched as part of the move
otherTrack = (
from x in existingTrack.PlaylistTracks
where x.TrackNumber == movedTrack.TrackNumber - 1 // Track below current
select x)
.FirstOrDefault();
if (otherTrack == null)
{
throw new Exception("Track to be switched during the move operation does not exist.");
}
else // Tracks moved
{
movedTrack.TrackNumber -= 1;
otherTrack.TrackNumber += 1;
}
}
}
else
{
// Ensure track is not already the final track
if (movedTrack.TrackNumber >= existingTrack.PlaylistTracks.Count)
{
throw new Exception("Playlist track already at the bottom.");
}
else
{
// Find the track being switched as part of the move
otherTrack = (
from x in existingTrack.PlaylistTracks
where x.TrackNumber == movedTrack.TrackNumber + 1 // Track above current
select x)
.FirstOrDefault();
if (otherTrack == null)
{
throw new Exception("Track to be switched during the move operation does not exist.");
}
else // Tracks moved
{
movedTrack.TrackNumber += 1;
otherTrack.TrackNumber -= 1;
}
}
}
// Save changes to the data (two entities)
context.Entry(movedTrack).Property(y => y.TrackNumber).IsModified = true;
context.Entry(otherTrack).Property(y => y.TrackNumber).IsModified = true;
context.SaveChanges();
}
}
}
}//eom
}//eom
public void Add_TrackToPLaylist(string playlistname, string username, int trackid)
{
using (var context = new ChinookContext())
{
//determine if the playlist exists
//do a query to find the playlist
//test results == null
// yes
// create an instance of playlist
// load
// add
// set tracknumber to 1
//no
// query to find track exists
// test results == null
// yes
// throw an exception
// no
// query to find max tracknumber
// tracknumber++
//create an instance of playlisttrack
//load
//add
//SaveChange
int tracknumber = 0;
PlaylistTrack newtrack = null;
//what would happen if there is no match for the
// incoming parameter values
//we need to ensure that the results have a valid value
//this value will be resolved by the query either as null
// (not found) or an IEnumerable collection
//we are looking for a single occurance to match the where
//to achieve a valid value we encapsulate the query in a
// (query).FirstOrDefault();
Playlist exists = (from x in context.Playlists
where x.Name.Equals(playlistname) &&
x.UserName.Equals(username)
select x).FirstOrDefault();
if (exists == null)
{
//new playlist
exists = new Playlist();
exists.Name = playlistname;
exists.UserName = username;
context.Playlists.Add(exists);
tracknumber = 1;
}
else
{
//existing playlist
newtrack = (from x in context.PlaylistTracks
where x.Playlist.Name.Equals(playlistname) &&
x.Playlist.UserName.Equals(username) &&
x.TrackId == trackid
select x).FirstOrDefault();
if (newtrack == null)
{
//not found can be added
tracknumber = (from x in context.PlaylistTracks
where x.Playlist.Name.Equals(playlistname) &&
x.Playlist.UserName.Equals(username)
select x.TrackNumber).Max();
tracknumber++;
}
else
{
//found violates business rule
//two ways of handling the message
//a) single error
//b) multiple business rule errors
//a)
//throw new Exception("Song already exists on playlist. Choose something else.");
//b) use the BusinessRuleException class to throw the error
// this technique can be used in your BLL and onyour Web page
// to this technique, you will collect your errors within
// a List<string>; then throw the BusinessRuleException
// along with the List<string> errors
//
errors.Add("**Song already exists on playlist. Choose something else.");
}
}
//finish all possible business rule validation
if (errors.Count > 0)
{
throw new BusinessRuleException("Adding Track", errors);
}
//add the new playlist track record
newtrack = new PlaylistTrack();
//when you do an .Add(xxx) to a entity, the record
// is ONLY staged AND NOT yet on the database
//ANY expected pkey value DOES NOT yet exist
//newtrack.PlaylistId = exists.PlaylistId; removed
newtrack.TrackId = trackid;
newtrack.TrackNumber = tracknumber;
exists.PlaylistTracks.Add(newtrack);
//SaveChange is what actually affects the database
context.SaveChanges();
}
}//eom
}//eom
public void Add_TrackToPLaylist(string playlistname, string username, int trackid)
{
// List of all business rule exceptions
List <string> Exceptions = new List <string>();
using (var context = new ChinookContext())
{
// Attempt to find an existing playlist (given passed parameters)
Playlist existingPlaylist = context.Playlists
.Where(x =>
x.Name.Equals(playlistname, StringComparison.OrdinalIgnoreCase)
&&
x.UserName.Equals(username, StringComparison.OrdinalIgnoreCase))
.Select(x => x)
.FirstOrDefault();
PlaylistTrack playlistTrack;
int trackNumber;
if (existingPlaylist == null)
{
// In the event there is no existing playlist create a new instance
existingPlaylist = new Playlist();
// Set playlist attributes
existingPlaylist.Name = playlistname;
existingPlaylist.UserName = username;
// Add playlist to database
existingPlaylist = context.Playlists.Add(existingPlaylist);
trackNumber = 1;
}
else // Playlist exists
{
trackNumber = existingPlaylist.PlaylistTracks
.Count() + 1;
playlistTrack = existingPlaylist.PlaylistTracks
.SingleOrDefault(x => x.TrackId == trackid);
// Note: A track can only appear once per playlist
if (playlistTrack == null)
{
if (Exceptions.Count() > 0)
{
throw new BusinessRuleException("Add playlist track", Exceptions);
}
else
{
}
// Create a new track instance
playlistTrack = new PlaylistTrack();
playlistTrack.TrackNumber = trackNumber;
playlistTrack.TrackId = trackid;
// Note: Parent entity (Playlist) has a HashSet...
existingPlaylist.PlaylistTracks.Add(playlistTrack);
// Commit changes made
context.SaveChanges();
}
else // The track already exists on the playlist
{
Exceptions.Add("Playlist track already exists on the current playlist");
}
}
}
}//eom
}//eom
public void Add_TrackToPLaylist(string playlistname, string username, int trackid)
{
using (var context = new ChinookContext())
{
//dtermine if playlist exists
// do a query to find playlist
//test results = null
//if yes,
//create and instance of a playlist
//load
//add
//set tracknumber to 1
//no
//query to find max track number.
//tracknumber ++
//query to find track exists
//test results == null
//yes
//throw exception
//create an instance of playlisttrack.
//load
//add
//SaveChange
//what would happen if there is no match for the incoming parameter values?
//we need to ensure that the results have a valid value
//this value will be resolved by the query either as null
// (not found) or an IEnumerable collection.
//we are looking for a single occurence to match the where.
//to achieve a valid value we encapsulate the query in a
//(query).FirstOrDefault();
int tracknumber = 0;
PlaylistTrack newtrack = null;
Playlist exists = (from x in context.Playlists
where x.Name.Equals(playlistname) && x.UserName.Equals(username)
select x).FirstOrDefault();
if (exists == null)
{
//new playlist
exists = new Playlist();
exists.Name = playlistname;
exists.UserName = username;
context.Playlists.Add(exists);
tracknumber = 1;
}
else
{
//existing playlist
newtrack = (from x in context.PlaylistTracks
where x.Playlist.Name.Equals(playlistname) && x.Playlist.UserName.Equals(username) &&
x.TrackId == trackid
select x).FirstOrDefault();
if (newtrack == null)
{
//not found can be added:
tracknumber = (from x in context.PlaylistTracks
where x.Playlist.Name.Equals(playlistname) && x.Playlist.UserName.Equals(username)
select x.TrackNumber).Max();
tracknumber++; //increment by 1.
}
else
{
//found. violates business rule where a track can only appear on a playlist once:
//There are two ways of handling the message:
//a) single possible error
//b) multiple business rule errors that it oculd catch.
//a)
//throw new Exception("Song already exists on playlist. Please choose another.");
//b)use the BusinessRuleException class to throw the error. This technique can be used in the BLL and on the WebPage.
//to use this technique you will collect the errors within a List<string>; Then throw the BusinessRuleException along with the List<string> errors.
errors.Add("@#Song already exists on playlist. Please choose another.");
}
}
//finish all possible business rule validation.
if (errors.Count > 0)
{
throw new BusinessRuleException("Adding Track", errors);
}
//add the new Playlist Track record:
newtrack = new PlaylistTrack();
//when you do a .Add() to an entity the record is only STAGED and NOT YET in the DataBase. Any Expected PKey value does not yet exist until we use .SaveChanges()
//newtrack.PlaylistId = exists.PlaylistId; removed
newtrack.TrackId = trackid;
newtrack.TrackNumber = tracknumber;
//By using "existing" instead of context, the parent will automatically added and generate the playlistId based on entityFramework. That way, the child can be associated with the parent and added accordingly.
exists.PlaylistTracks.Add(newtrack);
context.SaveChanges();
}
}//eom
}//eom
public void MoveTrack(string username, string playlistname, int trackid, int tracknumber, string direction)
{
using (var context = new ChinookContext())
{
//code to go here
var exists = (from x in context.Playlists
where x.UserName.Equals(username) &&
x.Name.Equals(playlistname)
select x).FirstOrDefault();
if (exists == null)
{
throw new Exception("Play list has been removed from the file.");
}
else
{
PlaylistTrack moveTrack = (from x in exists.PlaylistTracks
where x.TrackId == trackid
select x).FirstOrDefault();
if (moveTrack == null)
{
throw new Exception("Play list track has been removed from the file.");
}
else
{
PlaylistTrack otherTrack = null;
if (direction.Equals("up"))
{
//up
if (moveTrack.TrackNumber == 1)
{
throw new Exception("Play list track already at top.");
}
else
{
otherTrack = (from x in exists.PlaylistTracks
where x.TrackNumber == moveTrack.TrackNumber - 1
select x).FirstOrDefault();
if (otherTrack == null)
{
throw new Exception("Other Play list track is missing.");
}
else
{
moveTrack.TrackNumber -= 1;
otherTrack.TrackNumber += 1;
}
}
}
else
{
//down
if (moveTrack.TrackNumber == exists.PlaylistTracks.Count)
{
throw new Exception("Play list track already at bottom.");
}
else
{
otherTrack = (from x in exists.PlaylistTracks
where x.TrackNumber == moveTrack.TrackNumber + 1
select x).FirstOrDefault();
if (otherTrack == null)
{
throw new Exception("Other Play list track is missing.");
}
else
{
moveTrack.TrackNumber += 1;
otherTrack.TrackNumber -= 1;
}
}
}//eof up/down
//staging
context.Entry(moveTrack).Property(y => y.TrackNumber).IsModified = true;
context.Entry(otherTrack).Property(y => y.TrackNumber).IsModified = true;
//saving (apply update to database
context.SaveChanges();
}
}
}
}//eom
}//eom
public void Add_TrackToPLaylist(string playlistname, string username, int trackid)
{
using (var context = new ChinookContext())
{
//use the BusinessRuleException to throw errors to the web page
List <string> reasons = new List <string>();
PlaylistTrack newTrack = null;
int tracknumber = 0;
//Part One
//determine if the playlist exists
//query the table using the playlistname and username
//if the playlist exist, one will get a record,
//if the playlist does not exist, one will get a null
//to ENSURE these results the query will be wrap in a .FirstOrDefault()
//Playlist exists = context.Playlists
// .Where(x => x.UserName.Equals(username, StringComparison.OrdinalIgnoreCase)
// && x.Name.Equals(playlistname, StringComparison.OrdinalIgnoreCase))
// .Select(x => x)
// .FirstOrDefault();
Playlist exists = (from x in context.Playlists
where x.UserName.Equals(username, StringComparison.OrdinalIgnoreCase) &&
x.Name.Equals(playlistname, StringComparison.OrdinalIgnoreCase)
select x).FirstOrDefault();
//does the playlist exist
if (exists == null)
{
//this is a new playlist
//create the Playlist record
exists = new Playlist();
exists.Name = playlistname;
exists.UserName = username;
//stage the add
exists = context.Playlists.Add(exists);
//since this is a new playlist
//the tracknumber will be 1
tracknumber = 1;
}
else
{
//since the playlist exists, so may the track exist
// on the playlisttracks
newTrack = exists.PlaylistTracks
.SingleOrDefault(x => x.TrackId == trackid);
if (newTrack == null)
{
tracknumber = exists.PlaylistTracks.Count() + 1;
}
else
{
reasons.Add("Track already exists on playlist");
}
}
//Part Two
//create the PlaylistTrack entry
//if there are any reasons NOT to create then
//throw the BusinessRuleException
if (reasons.Count() > 0)
{
//issue with adding the track
throw new BusinessRuleException("Adding track to playlist",
reasons);
}
else
{
//use the Playlist navigation to PlaylistTracks to
// do the add to PlaylistTracks
newTrack = new PlaylistTrack();
newTrack.TrackId = trackid;
newTrack.TrackNumber = tracknumber;
//how do I fill the PlayListID IF the playlist is brand new
//a brand new playlist DOES NOT YET have an id
//Note: the pkey for PlaylistID may not yet exist
// using the navigation property on the PlayList entity
// one can let HashSet handle the PlaylistId pkey value
// to be be properly created on PlayList AND placed
// correctly in the "child" record of PlaylistTracks
// what is wrong to the attempt:
// newTrack.PlaylistId = exists.PlaylistId;
exists.PlaylistTracks.Add(newTrack); //playlist track staging
//physically add any/all data to the database
//commit
context.SaveChanges();
}
}
}//eom
}//eom
public List <UserPlaylistTrack> Add_TrackToPLaylist(string playlistname, string username, int trackid)
{
using (var context = new ChinookContext())
{
//code to go here
//Part One:
//query to get the playlist id
var exists = (from x in context.Playlists
where x.UserName.Equals(username) &&
x.Name.Equals(playlistname)
select x).FirstOrDefault();
//initialize the tracknumber
int tracknumber = 0;
//I will need to create an instance of PlaylistTrack
PlaylistTrack newtrack = null;
//determine if a playlist "parent" instances needs to be
// created
if (exists == null)
{
//this is a new playlist
//create an instance of playlist to add to Playlist tablge
exists = new Playlist();
exists.Name = playlistname;
exists.UserName = username;
exists = context.Playlists.Add(exists);
//at this time there is NO phyiscal pkey
//the psuedo pkey is handled by the HashSet
tracknumber = 1;
}
else
{
//playlist exists
//I need to generate the next track number
tracknumber = exists.PlaylistTracks.Count() + 1;
//validation: in our example a track can ONLY exist once
// on a particular playlist
newtrack = exists.PlaylistTracks.SingleOrDefault(x => x.TrackId == trackid);
if (newtrack != null)
{
throw new Exception("Playlist already has requested track.");
}
}
//Part Two: Add the PlaylistTrack instance
//use navigation to .Add the new track to PlaylistTrack
newtrack = new PlaylistTrack();
newtrack.TrackId = trackid;
newtrack.TrackNumber = tracknumber;
//NOTE: the pkey for PlaylistId may not yet exist
// using navigation one can let HashSet handle the PlaylistId
// pkey value
exists.PlaylistTracks.Add(newtrack);
//physically add all data to the database
//commit
context.SaveChanges();
return(List_TracksForPlaylist(playlistname, username));
}
}//eom
}//eom
public void Add_TrackToPLaylist(string playlistname, string username, int trackid)
{
using (var context = new ChinookContext())
{
//this list of strings will be used with the BusinessRuleException
List <string> reasons = new List <string>();
//Part One
//optional add of the new playlist
//validate track is not on the existing playlist
//determine if the playlist already exists on the database
Playlist exists = context.Playlists.Where(x => x.Name.Equals(playlistname, StringComparison.OrdinalIgnoreCase) && x.UserName.Equals(username, StringComparison.OrdinalIgnoreCase)).Select(x => x).FirstOrDefault();
PlaylistTrack newTrack = null;
int tracknumber = 0;
if (exists == null)
{
//add the parent record (Playlist record)
//no tracks exists yet for the new playlist
//therefore, the track number is 1
exists = new Playlist();
exists.Name = playlistname;
exists.UserName = username;
exists = context.Playlists.Add(exists);
tracknumber = 1;
}
else
{
//the playlist exists on the database
//the playlist may or may not have any tracks
//adjust the track number to be the next track
tracknumber = exists.PlaylistTracks.Count() + 1;
//will this be a duplicate track?
//look up the tracks of the playlist testing for the incoming trackid
newTrack = exists.PlaylistTracks.SingleOrDefault(x => x.TrackId == trackid);
//validation rule: track may only exist once on the playlist
if (newTrack != null)
{
//rule is violated
//track already exists on playlist
//throw exception to stop OLTP processing (rollback)
//this example will demonstrate using the BusinessRuleException
reasons.Add("Track already exists on the playlist");
}
}
//Part two
//check if any errors were found
if (reasons.Count > 0)
{
//issue a BusinessRuleException
//a BusinessRuleException is an object that has been designed to hold multiple errors
throw new BusinessRuleException("Adding track to playlist", reasons);
}
else
{
//add the track to the PlaylistTracks
newTrack = new PlaylistTrack();
newTrack.TrackNumber = tracknumber;
newTrack.TrackId = trackid;
//what about the foreign key to playlist?
//the parent entity has been set up with a Hashset
//therefore, if you add a child via the navigational property,
//the Hashset will take care of filling the foreign key with the appropriate pKey value during .SaveChanges()
//add the new track to the playlist using the navigational property
exists.PlaylistTracks.Add(newTrack);
//physically place the record(s) on the database and commit the transaction (using) with .SaveChanges()
context.SaveChanges();
}
}
}//eom
public List <TrackList> List_TracksForPlaylistSelection(string tracksby,
int argid)
{
using (var context = new ChinookContext())
{
IEnumerable <TrackList> results = null;
//code to go here
//determine which lookup needs to be done : tracksby
//switch (tracksby)
//{
// case "Artist":
// {
// results = from x in context.Tracks
// orderby x.Name
// where x.Album.ArtistId == argid
// select new TrackList
// {
// TrackID = x.TrackId,
// Name = x.Name,
// Title = x.Album.Title,
// MediaName = x.MediaType.Name,
// GenreName = x.Genre.Name,
// Composer = x.Composer,
// Milliseconds = x.Milliseconds,
// Bytes = x.Bytes,
// UnitPrice = x.UnitPrice
// };
// break;
// }
// case "MediaType":
// {
// results = from x in context.Tracks
// orderby x.Name
// where x.MediaTypeId==argid
// select new TrackList
// {
// TrackID = x.TrackId,
// Name = x.Name,
// Title = x.Album.Title,
// MediaName = x.MediaType.Name,
// GenreName = x.Genre.Name,
// Composer = x.Composer,
// Milliseconds = x.Milliseconds,
// Bytes = x.Bytes,
// UnitPrice = x.UnitPrice
// };
// break;
// }
// case "Genre":
// {
// results = from x in context.Tracks
// orderby x.Name
// where x.GenreId == argid
// select new TrackList
// {
// TrackID = x.TrackId,
// Name = x.Name,
// Title = x.Album.Title,
// MediaName = x.MediaType.Name,
// GenreName = x.Genre.Name,
// Composer = x.Composer,
// Milliseconds = x.Milliseconds,
// Bytes = x.Bytes,
// UnitPrice = x.UnitPrice
// };
// break;
// }
// default:
// {
// results = from x in context.Tracks
// orderby x.Name
// where x.AlbumId == argid
// select new TrackList
// {
// TrackID = x.TrackId,
// Name = x.Name,
// Title = x.Album.Title,
// MediaName = x.MediaType.Name,
// GenreName = x.Genre.Name,
// Composer = x.Composer,
// Milliseconds = x.Milliseconds,
// Bytes = x.Bytes,
// UnitPrice = x.UnitPrice
// };
// break;
// }
//}
//using an inline if
results = from x in context.Tracks
orderby x.Name
where tracksby.Equals("Artist") ? x.Album.ArtistId == argid :
tracksby.Equals("MediaType") ? x.MediaTypeId == argid :
tracksby.Equals("Genre") ? x.GenreId == argid :
x.AlbumId == argid
select new TrackList
{
TrackID = x.TrackId,
Name = x.Name,
Title = x.Album.Title,
MediaName = x.MediaType.Name,
GenreName = x.Genre.Name,
Composer = x.Composer,
Milliseconds = x.Milliseconds,
Bytes = x.Bytes,
UnitPrice = x.UnitPrice
};
return(results.ToList());
}
}//eom
public List <TrackList> List_TracksForPlaylistSelection(string tracksby, string arg)
{
using (var context = new ChinookContext())
{
//check the incoming parameters and id NEEDED set to a default value
if (string.IsNullOrEmpty(tracksby))
{
tracksby = "";
}
if (string.IsNullOrEmpty(arg))
{
arg = "";
}
//create two local variables representing the argument as
//a) an integer
//b) a string
int argid = 0;
string argstring = "zxczxc";
//determin if incoming argument should be interger of string
if (tracksby.Equals("Genre") || tracksby.Equals("MediaType"))
{
argid = int.Parse(arg);
}
else
{
argstring = arg.Trim();
}
var results = (from x in context.Tracks
where (x.GenreId == argid && tracksby.Equals("Genre")) || (x.MediaTypeId == argid && tracksby.Equals("MediaType"))
select new TrackList
{
TrackID = x.TrackId,
Name = x.Name,
Title = x.Album.Title,
ArtistName = x.Album.Artist.Name,
MediaName = x.MediaType.Name,
GenreName = x.Genre.Name,
Composer = x.Composer,
Milliseconds = x.Milliseconds,
Bytes = x.Bytes,
UnitPrice = x.UnitPrice
}
)
.Union(
from x in context.Tracks
where tracksby.Equals("Artist") ? x.Album.Artist.Name.Contains(argstring) : tracksby.Equals("Album") ? x.Album.Title.Contains(argstring) : false
select new TrackList
{
TrackID = x.TrackId,
Name = x.Name,
Title = x.Album.Title,
ArtistName = x.Album.Artist.Name,
MediaName = x.MediaType.Name,
GenreName = x.Genre.Name,
Composer = x.Composer,
Milliseconds = x.Milliseconds,
Bytes = x.Bytes,
UnitPrice = x.UnitPrice
}
);
return(results.ToList());
}
} //eom
}//eom
public void MoveTrack(string username, string playlistname, int trackid, string direction)
{
using (var context = new ChinookContext())
{
//Business rules need to be executed within our BLL
var exists = (from x in context.Playlists
where x.UserName.Equals(username) &&
x.Name.Equals(playlistname)
select x).FirstOrDefault();
if (exists == null)
{
throw new Exception("Play list has been removed from the system.");
}
else
{
//is the track still there
var moveTrack = (from x in exists.PlaylistTracks
where x.TrackId == trackid
select x).FirstOrDefault();
if (moveTrack == null)
{
throw new Exception("Track has been removed from the system.");
}
else
{
PlaylistTrack otherTrack = null;
//try moving
//check movement is still possible
//determine direction
if (direction.Equals("up"))
{
//up
if (moveTrack.TrackNumber == 1)
{
throw new Exception("Song is at the top of the play list. Cannot move further up");
}
else
{
//prep for move
otherTrack = (from x in exists.PlaylistTracks
where x.TrackNumber == moveTrack.TrackNumber - 1
select x).FirstOrDefault();
if (otherTrack == null)
{
throw new Exception("Missing required other song track record.");
}
else
{
moveTrack.TrackNumber -= 1;
otherTrack.TrackNumber += 1;
}
}
}
else
{
//down
if (moveTrack.TrackNumber == exists.PlaylistTracks.Count)
{
throw new Exception("Song is at the bottom of the play list. Cannot move further down");
}
else
{
//prep for move
otherTrack = (from x in exists.PlaylistTracks
where x.TrackNumber == moveTrack.TrackNumber + 1
select x).FirstOrDefault();
if (otherTrack == null)
{
throw new Exception("Missing required other song track record.");
}
else
{
moveTrack.TrackNumber += 1;
otherTrack.TrackNumber -= 1;
}
}
}
//alter the database
//this is NOT a CRUD update where multiple fields on a record
// could be altered and you do not know which fields to alter
//The only field being altered is Tracknumber
//Transaction
context.Entry(moveTrack).Property("TrackNumber").IsModified = true;
context.Entry(otherTrack).Property("TrackNumber").IsModified = true;
context.SaveChanges();
}
}
}
}//eom