public HttpResponseMessage Get(string name)
{
HttpResponseMessage responseMessage;
using (var context = new ChinookEntities())
{
var artists = context.Artists.Where(a => a.Name.Contains(name)).AsEnumerable();
var artistsDTO = new List <ArtistDTO>();
if (artists != null)
{
foreach (var artist in artists)
{
artistsDTO.Add(new ArtistDTO {
ArtistId = artist.ArtistId, Name = artist.Name
});
}
responseMessage = Request.CreateResponse <List <ArtistDTO> >(HttpStatusCode.OK, artistsDTO);
}
else
{
responseMessage = Request.CreateErrorResponse(HttpStatusCode.NotFound, new HttpError("Artist not found!"));
}
}
return(responseMessage);
}
public ActionResult Input1(string EmployeeFirst, string EmployeeLast, string CustomerFirst, string CustomerLast, string date1, string date2)
{
string since = date1;
string till = date2;
string customerF = CustomerFirst; //roberto
string customerL = CustomerLast; //"Almeida";
string employeeF = EmployeeFirst;
string employeeL = EmployeeLast;
if (since == "")
{
since = "2009-01-01";
}
if (till == "")
{
till = "2100-01-01";
}
using (var context = new ChinookEntities())
{
var data = context.Customer.SqlQuery("[dbo].[query5] @StartDate, @StopDate, " +
"@customerFirst, @CustomerLast ," +
"@employeeFirst ,@EmployeeLast",
new SqlParameter("StartDate", since),
new SqlParameter("StopDate", till),
new SqlParameter("customerFirst", customerF),
new SqlParameter("CustomerLast", customerL),
new SqlParameter("employeeFirst", employeeF),
new SqlParameter("EmployeeLast", employeeL)).ToList();
TempData["data"] = data;
return(RedirectToAction("show1"));
}
}
// PUT api/<controller>/5
public HttpResponseMessage Put(int id, TrackDTO track)
{
HttpResponseMessage responseMessage;
using (var context = new ChinookEntities())
{
var oldTrack = context.Tracks.Find(id);
if (oldTrack != null)
{
int newGenreId = context.Genres.Where(g => g.Name == track.Genre).SingleOrDefault().GenreId;
oldTrack.Name = track.Name;
oldTrack.GenreId = newGenreId;
oldTrack.Milliseconds = track.Milliseconds;
oldTrack.UnitPrice = track.UnitPrice;
try
{
context.SaveChanges();
responseMessage = Request.CreateResponse <TrackDTO>(HttpStatusCode.OK, track);
}
catch (Exception ex)
{
responseMessage = Request.CreateErrorResponse(HttpStatusCode.BadRequest,
new HttpError("Track wasn't updated!"));
}
}
else
{
responseMessage = Request.CreateErrorResponse(HttpStatusCode.BadRequest,
new HttpError("Track wasn't found!"));
}
}
return(responseMessage);
}
public async Task <IEnumerable <Album> > GetAlbums()
{
using (var context = new ChinookEntities())
{
return(await context.Album.Include(x => x.Artist).ToListAsync());
}
}
public HttpResponseMessage Get(string albumId)
{
HttpResponseMessage responseMessage;
using (var context = new ChinookEntities())
{
int id = Int32.Parse(albumId);
List <TrackDTO> tracksDTO = new List <TrackDTO>();
var tracks = context.Tracks.Where(t => t.AlbumId == id).AsEnumerable();
if (tracks.Count() > 0)
{
foreach (var track in tracks)
{
tracksDTO.Add(new TrackDTO
{
TrackId = track.TrackId,
Name = track.Name,
Genre = track.Genre.Name,
Milliseconds = track.Milliseconds,
UnitPrice = track.UnitPrice
});
}
responseMessage = Request.CreateResponse <List <TrackDTO> >(HttpStatusCode.OK, tracksDTO);
}
else
{
responseMessage = Request.CreateErrorResponse(HttpStatusCode.NotFound, new HttpError("No tracks found for this album"));
}
}
return(responseMessage);
}
private void btn_Add_Click(object sender, EventArgs e)
{
if (String.IsNullOrEmpty(txt_Type.Text))
{
errorProvider.SetError(txt_Type, "Enter type");
}
else
{
using (var context = new ChinookEntities())
{
var newType = new MediaType()
{
Name = txt_Type.Text
};
if (!context.MediaTypes.Any(t => t.Name == newType.Name))
{
context.MediaTypes.Add(newType);
}
context.SaveChanges();
}
this.Hide();
var form = new AddItemForm();
form.ShowDialog();
this.Close();
}
}
public async Task <IEnumerable <Artist> > GetArtists()
{
using (var context = new ChinookEntities())
{
return(await context.Artist.ToListAsync());
}
}
public ActionResult Input(int?count, string date1, string date2)
{
int? X = count;
string since = date1;
string till = date2;
if ((since == "" && till == "" && X == null) || (till == "" && X == null) || (since == "" && X == null))
{
return(RedirectToAction("Input"));
}
if (since == "")
{
since = "2009-01-01";
}
if (till == "")
{
till = "2100-01-01";
}
if (X == null)
{
return(RedirectToAction("Input"));
}
using (var context = new ChinookEntities())
{
var data = context.Artist.SqlQuery("[dbo].[query1] @X, @StartDate, @StopDate",
new SqlParameter("X", count),
new SqlParameter("StartDate", since),
new SqlParameter("StopDate", till)).ToList();
TempData["data"] = data;
return(RedirectToAction("show"));
}
}
public HttpResponseMessage Get(string artistId)
{
HttpResponseMessage responseMessage;
using (var context = new ChinookEntities())
{
int id = Int32.Parse(artistId);
var albums = context.Albums.Where(a => a.ArtistId == id).AsEnumerable();
var albumsDTO = new List <AlbumDTO>();
if (albums != null)
{
foreach (var album in albums)
{
albumsDTO.Add(new AlbumDTO {
AlbumId = album.AlbumId, Title = album.Title
});
}
responseMessage = Request.CreateResponse <List <AlbumDTO> >(HttpStatusCode.OK, albumsDTO);
}
else
{
responseMessage = Request.CreateErrorResponse(HttpStatusCode.NotFound, new HttpError("No albums found!"));
}
}
return(responseMessage);
}
private void CreateNewInvoice()
{
using (var context = new ChinookEntities())
{
var newInvoice = new Invoice()
{
BillingCountry = Customer.Country,
BillingCity = Customer.City,
CustomerId = Customer.CustomerId,
BillingAddress = Customer.Address,
BillingPostalCode = Customer.PostalCode,
BillingState = Customer.State,
InvoiceDate = DateTime.Now,
};
context.Invoices.Add(newInvoice);
context.SaveChanges();
foreach (var line in InvoiceLines)
{
line.Invoice = newInvoice;
context.InvoiceLines.Add(line);
}
newInvoice.Total = newInvoice.InvoiceLines.Sum(i => i.UnitPrice * i.Quantity);
context.SaveChanges();
}
}
public async Task <Track> GetTrack(long id)
{
using (var context = new ChinookEntities())
{
return(await context.Track.Include(x => x.Album).FirstOrDefaultAsync());
}
}
public void GenerateData()
{
DbContext context = new ChinookEntities();
List <string> entityNames = GetEntityNames(context);
foreach (var entityName in entityNames)
{
string template = @"using MyLibrary;
namespace Chinook.Data
{{
public class {0}Data : EntityData<{0}>
{{
}}
}}
";
string code = string.Format(template, entityName);
string filePath = $@"D:\sync\402R\sources\Chsarp\Chinook\Chinook.Data\Data\{entityName}Data.cs";
if (File.Exists(filePath))
{
continue;
}
File.WriteAllText(filePath, code);
}
}
public ActionResult Input(string date1, string date2)
{
string since = date1;
string till = date2;
if (since == "" && till == "")
{
return(RedirectToAction("Input"));
}
else if (since == "")
{
since = "2009-01-01";
}
else if (till == "")
{
till = "2100-01-01";
}
using (var context = new ChinookEntities())
{
var data = context.Customer.SqlQuery("[dbo].[query4] @StartDate, @StopDate",
new SqlParameter("StartDate", since),
new SqlParameter("StopDate", till)).ToList();
TempData["data"] = data;
return(RedirectToAction("show"));
}
}
private void btn_Add_Click(object sender, EventArgs e)
{
errorProvider.Clear();
if (String.IsNullOrEmpty(txt_AuthorName.Text))
{
errorProvider.SetError(txt_AuthorName, "Enter author name");
}
else
{
using (var context = new ChinookEntities())
{
var newArtist = new Artist()
{
Name = txt_AuthorName.Text
};
if (!context.Artists.Any(a => a.Name == newArtist.Name))
{
context.Artists.Add(newArtist);
}
context.SaveChanges();
}
this.Hide();
var form = new AddItemForm();
form.ShowDialog();
this.Close();
}
}
public TestInvoice()
{
_ChinookEntities = new ChinookEntities();
_InvoiceRepo = new InvoiceRepository(_ChinookEntities);
_CustomerRepo = new CustomerRepository(_ChinookEntities);
_TrackRepo = new TrackRepository(_ChinookEntities);
}
public IEnumerable <Song> GetSongsByArtist(string name)
{
using (var context = new ChinookEntities())
{
context.Tracks.MergeOption = MergeOption.NoTracking;
return(GetSongsByArtistQuery.Invoke(context, name).ToList());
}
}
public Artist GetArtistById(int id)
{
using (var context = new ChinookEntities())
{
context.Artists.MergeOption = MergeOption.NoTracking;
return(GetArtistByIdQuery.Invoke(context, id).FirstOrDefault());
}
}
public TestCustomer()
{
_ChinookEntities = new ChinookEntities();
_CustomerRepo = new CustomerRepository(_ChinookEntities);
_TrackRepo = new TrackRepository(_ChinookEntities);
_PlayListRepo = new PlaylistRepository(_ChinookEntities);
}
public ActionResult Details(int id)
{
using (var ctx = new ChinookEntities())
{
var artist = ctx.Artists.Single(t => t.ArtistId == id);
return(View(artist));
}
}
//
// GET: /Artist/
public ActionResult Index()
{
using (var ctx = new ChinookEntities())
{
var artistCollection = ctx.Artists.OrderByDescending(t => t.ArtistId).ToList();
return(View(artistCollection));
}
}
public ActionResult Top3()
{
using (var context = new ChinookEntities())
{
var data = context.Genre.SqlQuery("[dbo].[query3]").ToList();
return(View(data));
}
}
public async Task <IODataQueryable <Album> > GetAlbums(IODataQuery <Album> query)
{
using (var context = new ChinookEntities())
{
var r = query.ApplyTo(context.Album.Include(x => x.Artist));
return(await r.ToListAsync());
}
}
public void TestChinookModelCreation()
{
using (var entities = new ChinookEntities())
{
var provider = new DbProviderInfo("System.Data.SqlClient", "2008");
var model = ChinookModel.CreateModel(provider);
Assert.NotNull(model);
}
}
public async Task <IODataQueryable <Track> > GetTracks(IODataQuery <Track> query)
{
using (var context = new ChinookEntities())
{
var r = query.ApplyTo(context.Track.Include(x => x.Album));
return(await r.ToListAsync());
}
}
public async Task <IEnumerable <Artist> > GetArtists(IODataQuery <Artist> query)
{
using (var context = new ChinookEntities())
{
var r = query.ApplyTo(context.Artist);
return(await r.ToListAsync());
}
}
public ActionResult Delete(int id)
{
using (var ctx = new ChinookEntities())
{
var artist = ctx.Artists.Single(t => t.ArtistId == id);
ctx.Artists.Remove(artist);
ctx.SaveChanges();
return(RedirectToAction("Index"));
}
}
public ActionResult Edit(Artist artist)
{
using (var ctx = new ChinookEntities())
{
var a = ctx.Artists.Single(t => t.ArtistId == artist.ArtistId);
a.Name = artist.Name;
ctx.SaveChanges();
return(RedirectToAction("Index"));
}
}
public ActionResult Create(Artist artist)
{
using (var ctx = new ChinookEntities())
{
var id = ctx.Artists.OrderByDescending(t => t.ArtistId).First().ArtistId;
artist.ArtistId = ++id;
ctx.Artists.Add(artist);
ctx.SaveChanges();
return(RedirectToAction("Index"));
}
}
private bool IsInDatabase(Customer customer)
{
using (var context = new ChinookEntities())
{
return(!context.Customers
.Any(c => c.FirstName == customer.FirstName &&
c.LastName == customer.LastName &&
c.Address == customer.Address &&
c.Country == customer.Country &&
c.City == customer.City) ? true : false);
}
}
public Artist GetArtistById(int id)
{
using (var context = new ChinookEntities())
{
var result = context.ExecuteStoreQuery <Artist>("SELECT ArtistId, Name FROM Artist WHERE Artist.ArtistId=@id",
new SqlParameter
{
ParameterName = "@id",
Value = id
});
return(result.FirstOrDefault());
}
}
