Homework 8

The Premise

The project required us to create an art gallery site from scratch with the following requirements:

Generate a Database, complete with ER Diagram.
Use that to generate the Models.
Use the Models to create a CRUD interface.
Create a List View for Artists, Artworks & Classifications.
Modify the Layout page to reflect our Links, must use Razor loops.
Modify the Index page to be a welcome page.
Index must have buttons for genres that access the Artworks & Artists in them via an AJAX call.

Having failed to get Homework 7 fully functional due to my scant undersstanding of AJAX, I was weary at the outset on this one - especially when it was announced that THIS was the exact format for the final.

The Database

Admittedly, I love making databases so the first thing I happily rushed to do was draw up my ER Diagram. I had to identify the entities and their relationships before I could write any SQL for it.

Now that I had that drawn up, it was time to create my tables in SQL. This time I opted to use Visual Studio itself to generate my Database and its code since the functionality is there.

CREATE TABLE [dbo].[Artist] (
        [ArtistID]     INT            NOT NULL,
        [Name]         NVARCHAR (150) NOT NULL,
        [Birthdate]    DATE       NOT NULL,
        [BirthCountry] NVARCHAR (150) NOT NULL,
        [BirthCity]    NVARCHAR (100) NOT NULL,
        PRIMARY KEY CLUSTERED ([ArtistID] ASC)
        );

        CREATE TABLE [dbo].[Artwork] (
            [ArtworkID] INT            NOT NULL,
            [ArtistID]  INT            NOT NULL,
            [Title]     NVARCHAR (100) NOT NULL,
            PRIMARY KEY CLUSTERED ([ArtworkID] ASC),
            FOREIGN KEY ([ArtistID]) REFERENCES [dbo].[Artist] ([ArtistID])
            ON DELETE CASCADE
            ON UPDATE CASCADE
        );

        CREATE TABLE [dbo].[Genre] (
            [GenreID] INT NOT NULL,
            [Name]    NVARCHAR (50) NOT NULL,
            PRIMARY KEY CLUSTERED ([GenreID] ASC)
        );

        CREATE TABLE [dbo].[Classification] (
            [ClassificationID] INT NOT NULL,
            [ArtworkID]        INT NOT NULL,
            [GenreID]       INT NOT NULL,
            PRIMARY KEY CLUSTERED ([ClassificationID] ASC),
            FOREIGN KEY ([ArtworkID]) REFERENCES [dbo].[Artwork] ([ArtworkID])
                ON DELETE CASCADE
                ON UPDATE CASCADE,
            FOREIGN KEY ([GenreID]) REFERENCES [dbo].[Genre] ([GenreID])
                ON DELETE CASCADE
                ON UPDATE CASCADE
        );

It bears noting at this point that I ran into a few new lessons and complications after creating this database. Firstly, it turns out there are two very different ways to generate the date: DATE or DATETIME - the latter is actually not very good for storing dates in the past since it cannot store a date earlier than Jan 1, 1753. Secondly, it seemed all of my fellow students accomplished this lab without the need for Genre to have an ID property so there was a stint where I kept trying to modify it to be more like theres in the hope it would work - SPOILER ALERT: It didn't. Lastly, I learned that storing IDs and string literals is in no way helpful.

Seed Data

Next I created the required starting dataset for the database and put that, and the creat table code, into my markUp file.

INSERT INTO Artist(ArtistID, Name, Birthdate, BirthCountry, BirthCity)
        VALUES
            (1, 'M.C. Esher', '07/17/1898', 'Netherlands', 'Leeuwarden'),
            (2, 'Leonardo Da Vinci', '05/02/1591', 'Italy', 'Vinci'),
            (3, 'Hatip Mehmed Efendi', '11/18/1680', 'Unknown', 'Unknown'),
            (4, 'Salvador Dali', '05/11/1904', 'Spain', 'Figueres');

        INSERT INTO Artwork(ArtworkID, ArtistID, Title)
        VALUES
            (1, 1, 'Circle Limit III'),
            (2, 1, 'Twon Tree'), 
            (3, 2, 'Mona Lisa'), 
            (4, 2, 'The Vitruvian Man'), 
            (5, 3, 'Ebru'),
            (6, 4, 'Honey Is Sweeter Than Blood');

        INSERT INTO Genre(GenreID, Name)
        VALUES
            (1, 'Tesselation'), 
            (2, 'Surrealism'), 
            (3, 'Portrait'), 
            (4, 'Renaissance');

        INSERT INTO Classification(ClassificationID, ArtworkID, GenreID)
        VALUES
            (1, 1, 1), 
            (2, 2, 1),
            (3, 2, 2), 
            (4, 3, 3), 
            (5, 3, 4), 
            (6, 4, 4), 
            (7, 5, 1), 
            (8, 6, 2);

Auto Generation

Next, I created the Models using the ADO.net functionality for Code First implimentation. This created my necessary Model classes based off my newly minted database.

Similiarly, I then used Scaffolding to auto generate my Views and Controller. In the begining it only provided me one List View (Artists) and then a Create, Delete, Detials and Index page.

It is important to note you must build your project at least once before you can impliment the CRUD interface though. It also didn't give me the list view for the other Models as required - so I went in and auto generated a List View for each.

I also had to remember to add the View methods to the Controller for the List Views since they were created after the fact.

Mid-Git

At this point we are required to stop and push to the branch. While not explicitly told to you at the start of this page, we are still on the "create a feature branch and work there" methodology for this project. To show we can effectively use Git, this is where we make sure to push at least once to the branch.

git checkout -b HW8

        git add HW8
        git commit HW8 -m "Required Push for Assignment."
        git push origin HW8

A Few Constraints

We were asked to impliment two constaints for the data. The first was tht you could not set the birthday to any time in the future. The second was that the Artist's Name could not be longer than 50 letters. I accomplished this with the following code:

            if (artist.Name.Length > 50) {
                ViewBag.Error2 = "Name must be shorter than 50 characters.";
            }

            if (artist.Birthdate > DateTime.Now) {
                ViewBag.Error = "Birthdate cannot be in the future.";
            }

The REAL Battle Begins

The above is a picture of my mental state through the rest of this project. Our last task, daunting as it was, was to create buttons that called an AJAX script to show all Artworks and Artists affiliated with a specific Genre.

As you might expecct, this is where it all fell apart. After days of struggling with the AJAX call, however, I finally cracked it's Da Vinci Code!

function callFunction(selectedGenre) {
            $("#list").empty();//Clear old data before ajax

            $.ajax({
                url: "/Home/GetGenre",
                type: "POST",
                data: { genre : selectedGenre },
                success: function (data) {
                    console.log(data);
                    data.arr.forEach(function (item) {
                        $('#list').append(item);
                    });
                },
                error: function () {
                    alert('There was an error.');
                }
            });
        }

Issue? It would go into the controller and get hung up on the SQL return commands I created. Frustratingly, these same commands returned exactly what I wanted when run in LINQ pad but failed to do so due to some error about anonymous calls in Visual Studio.

The Hang Up

Sadly, this is where I stay stuck again. Almost every student I tried to get help from had gone to pain staking lengths NOT to use JSON as a way to return their data since they hated JSON so much. This meant they weren't really able to help me get on track - and using their complicated methods with multiple ViewModels and such seemed like a great way to confuse myself further so I didn't pursue that option. Instead, I've tried everything to make the following code work and unless my instructor pops up with a miracle answer by tonight... this is where I had to call it quits since its due tomorrow and I still have to deploy to Azure for Homework 9.

        [HttpPost]
        public JsonResult GetGenre(int? genre)
        {
            var artwork = db.Genres.FirstOrDefault(n => n.GenreID == genre).Classifications.ToList().OrderBy(t => t.Artwork.Title).Select(a => new { aw = a.ArtworkID, awa = a.Artwork.ArtistID }).ToList();
            string[] artworkArtist = new string[artwork.Count()];
            for (int i = 0; i < artworkArtist.Length; ++i)
            {
                var artistName = db.Artists.Where(awa => awa.ArtistID == artwork[i].awa).Select(a => a.Name).ToList();
                var artworkName = db.Artworks.Where(aw => aw.ArtworkID == artwork[i].aw).Select(a => a.Title).ToList();
                artworkArtist[i] = $"<td>{artworkName}</td><td>{artistName}</td>";
            }
            var data = new
            {
                arr = artworkArtist
            };
            return Json(data, JsonRequestBehavior.AllowGet);
                    }

In Operation

Here are some screenshots of the site in operation - natually sans the button functionality.

Homework 8: