REAL-WORLD COMPUTER PROGRAMMING FOR KIDS of ALL AGES
STEP 49: THE CODE & THE METHODS BEHIND ITS MADNESS AND/OR THE MADNESS BEHIND ITS METHODS
We’ve got 21 Movie Records to “play with” once we load them into our Tables. I will first show you the code on how to do that, and later (in the next Step, for the most part) explain it all (what it’s doing, why it’s doing it, and how).
After that, we will query the data to find out some exciting aspects or facets of these great 21 movies that will have been populated into the Relational Database Tables.
Following the feeding frenzy by the Pelicans below you will see the code for populating the Database, in fact the entire code contents of File1.cs. Again, we will break it all down in the next Step, section by section – except for the “using clauses” and connection string, which we will address (no pun intended) at the end of this code listing.
Photo of Pelicans courtesy of Birger Strahl, from unsplash.com
using MySql.Data.MySqlClient;using System;using System.Collections.Generic;using System.Data;using System.IO;using System.Text;using System.Windows.Forms;namespace Popul8MovieData{ public partial class Form1 : Form { MySqlConnection conn; string connstr = "server=127.0.0.1;user id=root;password=gr8OoglyMoogly; persistsecurityinfo=True;database=movies"; public Form1() { InitializeComponent(); } private void btnPopul8MovieData_Click(object sender, EventArgs e) { string selectedFile = string.Empty; string[] fileStrArray; string title = string.Empty; string mpaa_rating = string.Empty; double imdb_rating = 0.0; int movie_length = 0; string year_released = string.Empty; string director = string.Empty; string screenwriter = string.Empty; string genre1 = string.Empty; string genre2 = string.Empty; string genre3 = string.Empty; string actor1 = string.Empty; string actor2 = string.Empty; string actor3 = string.Empty; long movie_id = 0; long director_id = 0; long screenwriter_id = 0; bool alreadyExists; // false by default List<string> genreList = new List<string>(); List<string> actorsList = new List<string>(); List<string> directorNamePartsList; List<string> screenwriterNamePartsList; List<string> actorNamePartsList; const int TITLE_INDEX = 0; const int MPAA_RATING_INDEX = 1; const int IMDB_RATING_INDEX = 2; const int MOVIE_LENGTH_INDEX = 3; const int YEAR_RELEASED_INDEX = 4; const int DIRECTOR_INDEX = 5; const int SCREENWRITER_INDEX = 6; const int GENRE1_INDEX = 7; const int GENRE2_INDEX = 8; const int GENRE3_INDEX = 9; const int ACTOR1_INDEX = 10; const int ACTOR2_INDEX = 11; const int ACTOR3_INDEX = 12; using (OpenFileDialog openFileDialog = new OpenFileDialog()) { openFileDialog.InitialDirectory = @"C:\ ComputerProgrammingForKids_Substack\"; openFileDialog.Filter = "CSV files | *.csv"; try { conn = new MySqlConnection(connstr); conn.Open(); MySqlCommand comm = conn.CreateCommand(); if (openFileDialog.ShowDialog() == DialogResult.OK) { selectedFile = openFileDialog.FileName; fileStrArray = File.ReadAllLines(selectedFile, Encoding.UTF8); foreach (string line in fileStrArray) { string[] dataElements = line.Split(';'); // Assign elements from the line to the local vars title = dataElements[TITLE_INDEX].Trim(); mpaa_rating = dataElements[MPAA_RATING_INDEX]; imdb_rating = Convert.ToDouble( dataElements[IMDB_RATING_INDEX]); movie_length = Convert.ToInt32( dataElements[MOVIE_LENGTH_INDEX]); year_released = dataElements[YEAR_RELEASED_INDEX].Trim(); director = dataElements[DIRECTOR_INDEX].Trim(); screenwriter = dataElements[SCREENWRITER_INDEX].Trim(); genre1 = dataElements[GENRE1_INDEX].Trim(); genre2 = dataElements[GENRE2_INDEX].Trim(); genre3 = dataElements[GENRE3_INDEX].Trim(); // Assuming at least one genre genreList.Clear(); genreList.Add(genre1); if (!string.IsNullOrWhiteSpace(genre2)) genreList.Add(genre2); if (!string.IsNullOrWhiteSpace(genre3)) genreList.Add(genre3); actor1 = dataElements[ACTOR1_INDEX].Trim(); actor2 = dataElements[ACTOR2_INDEX].Trim(); actor3 = dataElements[ACTOR3_INDEX].Trim(); // Assuming at least one actor actorsList.Clear(); actorsList.Add(actor1); if (!string.IsNullOrWhiteSpace(actor2)) actorsList.Add(actor2); if (!string.IsNullOrWhiteSpace(actor3)) actorsList.Add(actor3); // Now to Populate the Database Tables; must get the ID vals of Director and Screenwriter before populating the Main Table // DIRECTORS Table directorNamePartsList = GetNameParts(director); alreadyExists = DirectorAlreadyExists(directorNamePartsList); if (!alreadyExists) { comm.Parameters.Clear(); comm.CommandText = "INSERT INTO Directors " + "(first_name,middle_name,last_name,suffix) " + "VALUES " + "(@first_name, @middle_name, @last_name, @suffix)"; comm.Parameters.AddWithValue("@first_name", directorNamePartsList[0]); comm.Parameters.AddWithValue("@middle_name", directorNamePartsList[1]); comm.Parameters.AddWithValue("@last_name", directorNamePartsList[2]); comm.Parameters.AddWithValue("@suffix", directorNamePartsList[3]); comm.ExecuteNonQuery(); director_id = comm.LastInsertedId; } // SCREENWRITERS screenwriterNamePartsList = GetNameParts(screenwriter); alreadyExists = ScreenwriterAlreadyExists( screenwriterNamePartsList); if (!alreadyExists) { comm.Parameters.Clear(); comm.CommandText = "INSERT INTO Screenwriters " + "(first_name, middle_name, last_name, suffix) " + "VALUES " + "(@first_name, @middle_name, @last_name, @suffix)"; comm.Parameters.AddWithValue("@first_name", screenwriterNamePartsList[0]); comm.Parameters.AddWithValue("@middle_name", screenwriterNamePartsList[1]); comm.Parameters.AddWithValue("@last_name", screenwriterNamePartsList[2]); comm.Parameters.AddWithValue("@suffix", screenwriterNamePartsList[3]); comm.ExecuteNonQuery(); screenwriter_id = comm.LastInsertedId; } // Main Table - need movie_id before the following // tables can be populated alreadyExists = MainRecordAlreadyExists(title, imdb_rating, movie_length); if (!alreadyExists) { comm.Parameters.Clear(); comm.CommandText = "INSERT INTO Movies_Main " + "(movie_title, mpaa_rating, imdb_rating, movie_length, director_id, screenwriter_id, year_released) " + "VALUES " + "(@movie_title, @mpaa_rating, @imdb_rating, @movie_length, @director_id, @screenwriter_id, @year_released)"; comm.Parameters.AddWithValue( "@movie_title", title); comm.Parameters.AddWithValue( "@mpaa_rating", mpaa_rating); comm.Parameters.AddWithValue( "@imdb_rating", Math.Round(imdb_rating, 1)); comm.Parameters.AddWithValue( "@movie_length", movie_length); comm.Parameters.AddWithValue( "@director_id", director_id); comm.Parameters.AddWithValue( "@screenwriter_id", screenwriter_id); comm.Parameters.AddWithValue( "@year_released", year_released); comm.ExecuteNonQuery(); movie_id = comm.LastInsertedId; } // GENRES Lookup Table - up to 3 foreach (var gen_desc in genreList) { alreadyExists = GenreAlreadyExists(gen_desc); if (!alreadyExists) { comm.Parameters.Clear(); comm.CommandText = "INSERT INTO Genres " + "(genre_description) " + "VALUES " + "(@genre_description)"; comm.Parameters.AddWithValue( "@genre_description", gen_desc); comm.ExecuteNonQuery(); } } // MOVIES_GENRES Many-to-Many Table foreach (var gen_desc in genreList) { //long genreID = (long) GetGenreIDForDescription(gen_desc); long genreID = Convert.ToInt32( GetGenreIDForDescription(gen_desc)); alreadyExists = PairAlreadyExistsInMoviesGenresM2Mtable( movie_id, genreID); if (!alreadyExists) { comm.Parameters.Clear(); comm.CommandText = "INSERT INTO Movies_Genres " + "(movie_id, genre_id) " + "VALUES " + "(@movie_id, @genre_id)"; comm.Parameters.AddWithValue( "@movie_id", movie_id); comm.Parameters.AddWithValue( "@genre_id", genreID); comm.ExecuteNonQuery(); } } // ACTORS Lookup Table foreach (var actor in actorsList) { actorNamePartsList = GetNameParts(actor); alreadyExists = ActorAlreadyExists( actorNamePartsList); if (!alreadyExists) { comm.Parameters.Clear(); comm.CommandText = "INSERT INTO Actors " + "(first_name, middle_name, last_name, suffix) " + "VALUES " + "(@first_name, @middle_name, @last_name, @suffix)"; comm.Parameters.AddWithValue("@first_name", actorNamePartsList[0]); comm.Parameters.AddWithValue("@middle_name", actorNamePartsList[1]); comm.Parameters.AddWithValue("@last_name", actorNamePartsList[2]); comm.Parameters.AddWithValue("@suffix", actorNamePartsList[3]); comm.ExecuteNonQuery(); } } // ACTORS_MOVIES Many-to-Many Table foreach (var actor in actorsList) { long actorID = Convert.ToInt32( GetActorIDForName(actor)); alreadyExists = PairAlreadyExistsInActorsMoviesM2Mtable( movie_id, actorID); if (!alreadyExists) { comm.Parameters.Clear(); comm.CommandText = "INSERT INTO Actors_Movies " + "(actor_id, movie_id) " + "VALUES " + "(@actor_id, @movie_id)"; comm.Parameters.AddWithValue("@movie_id", movie_id); comm.Parameters.AddWithValue("@actor_id", GetActorIDForName(actor)); comm.ExecuteNonQuery(); } } } } } finally { conn.Close(); } } } private bool PairAlreadyExistsInMoviesGenresM2Mtable( long _movieID, long _genreID) { string qry = "SELECT COUNT(genre_id) " + "FROM Movies_Genres " + "WHERE genre_id = @genreID " + "AND movie_id = @movieID"; try { conn = new MySqlConnection(connstr); conn.Open(); try { MySqlCommand comm = conn.CreateCommand(); comm.CommandText = qry; comm.Parameters.AddWithValue("@movieID", _movieID); comm.Parameters.AddWithValue("@genreID", _genreID); return (Convert.ToInt32(comm.ExecuteScalar()) > 0); } catch (Exception ex) { MessageBox.Show(ex.Message); throw; } } finally { conn.Close(); } } private bool PairAlreadyExistsInActorsMoviesM2Mtable( long _movieID, long _actorID) { string qry = "SELECT COUNT(movie_id) " + "FROM Actors_Movies " + "WHERE movie_id = @movieID " + "AND actor_id = @actorID"; try { conn = new MySqlConnection(connstr); conn.Open(); try { MySqlCommand comm = conn.CreateCommand(); comm.CommandText = qry; comm.Parameters.AddWithValue("@movieID", _movieID); comm.Parameters.AddWithValue("@actorID", _actorID); return (Convert.ToInt32(comm.ExecuteScalar()) > 0); } catch (Exception ex) { MessageBox.Show(ex.Message); throw; } } finally { conn.Close(); } } private bool GenreAlreadyExists(string gen_desc) { string qry = "SELECT COUNT(genre_id) " + "FROM Genres " + "WHERE genre_description = @genreDescription"; try { conn = new MySqlConnection(connstr); conn.Open(); try { MySqlCommand comm = conn.CreateCommand(); comm.CommandText = qry; comm.Parameters.AddWithValue("@genreDescription", gen_desc); return (Convert.ToInt32(comm.ExecuteScalar()) > 0); } catch (Exception ex) { MessageBox.Show(ex.Message); throw; } } finally { conn.Close(); } } private bool MainRecordAlreadyExists( string _title, double _imdb_rating, int _movie_length) { string qry = "SELECT COUNT(movies_id) " + "FROM Movies_Main " + "WHERE movie_title = @movieTitle " + "AND imdb_rating = @imdbRating " + "AND movie_length = @movieLength" ; try { conn = new MySqlConnection(connstr); conn.Open(); try { MySqlCommand comm = conn.CreateCommand(); comm.CommandText = qry; comm.Parameters.AddWithValue("@movieTitle", _title); comm.Parameters.AddWithValue("@imdbRating", _imdb_rating); comm.Parameters.AddWithValue("@movieLength", _movie_length); return (Convert.ToInt32(comm.ExecuteScalar()) > 0); } catch (Exception ex) { MessageBox.Show(ex.Message); throw; } } finally { conn.Close(); } } private bool ActorAlreadyExists(List<string> actorsList) { string qry = "SELECT COUNT(actor_id) " + "FROM Actors " + "WHERE first_name = @firstName " + "AND middle_name = @middleName " + "AND last_name = @lastName " + "AND suffix = @suffix"; try { conn = new MySqlConnection(connstr); conn.Open(); try { MySqlCommand comm = conn.CreateCommand(); comm.CommandText = qry; comm.Parameters.AddWithValue("@firstName", actorsList[0]); comm.Parameters.AddWithValue("@middleName", actorsList[1]); comm.Parameters.AddWithValue("@lastName", actorsList[2]); comm.Parameters.AddWithValue("@suffix", actorsList[3]); return (Convert.ToInt32(comm.ExecuteScalar()) > 0); } catch (Exception ex) { MessageBox.Show(ex.Message); throw; } } finally { conn.Close(); } } private bool ScreenwriterAlreadyExists( List<string> screenwritersNamePartsList) { string qry = "SELECT COUNT(screenwriter_id) " + "FROM Screenwriters " + "WHERE first_name = @firstName " + "AND middle_name = @middleName " + "AND last_name = @lastName " + "AND suffix = @suffix"; try { conn = new MySqlConnection(connstr); conn.Open(); try { MySqlCommand comm = conn.CreateCommand(); comm.CommandText = qry; comm.Parameters.AddWithValue("@firstName", screenwritersNamePartsList[0]); comm.Parameters.AddWithValue("@middleName", screenwritersNamePartsList[1]); comm.Parameters.AddWithValue("@lastName", screenwritersNamePartsList[2]); comm.Parameters.AddWithValue("@suffix", screenwritersNamePartsList[3]); return (Convert.ToInt32(comm.ExecuteScalar()) > 0); } catch (Exception ex) { MessageBox.Show(ex.Message); throw; } } finally { conn.Close(); } } private bool DirectorAlreadyExists(List<string> directorNamePartsList) { string qry = "SELECT COUNT(director_id) " + "FROM Directors " + "WHERE first_name = @firstName " + "AND middle_name = @middleName " + "AND last_name = @lastName " + "AND suffix = @suffix"; try { conn = new MySqlConnection(connstr); conn.Open(); try { MySqlCommand comm = conn.CreateCommand(); comm.CommandText = qry; comm.Parameters.AddWithValue("@firstName", directorNamePartsList[0]); comm.Parameters.AddWithValue("@middleName", directorNamePartsList[1]); comm.Parameters.AddWithValue("@lastName", directorNamePartsList[2]); comm.Parameters.AddWithValue("@suffix", directorNamePartsList[3]); return (Convert.ToInt32(comm.ExecuteScalar()) > 0); } catch (Exception ex) { MessageBox.Show(ex.Message); throw; } } finally { conn.Close(); } } private object GetGenreIDForDescription(string gen_desc) { try { conn = new MySqlConnection(connstr); conn.Open(); try { MySqlCommand comm = conn.CreateCommand(); comm.CommandText = "SELECT genre_id " + "FROM Genres " + "WHERE genre_description = @genreDescription"; comm.Parameters.AddWithValue("@genreDescription", gen_desc); return Convert.ToInt32(comm.ExecuteScalar()); } catch (Exception ex) { MessageBox.Show(ex.Message); throw; } } finally { conn.Close(); } } private object GetActorIDForName(string actor) { try { List<string> actorElements = new List<string>(); actorElements = GetNameParts(actor); conn = new MySqlConnection(connstr); conn.Open(); try { MySqlCommand comm = conn.CreateCommand(); comm.CommandText = "SELECT actor_id " + "FROM Actors " + "WHERE first_name = @firstName " + "AND middle_name = @middleName " + "AND last_name = @lastName " + "AND suffix = @suffix"; comm.Parameters.AddWithValue("@firstName", actorElements[0]); comm.Parameters.AddWithValue("@middleName", actorElements[1]); comm.Parameters.AddWithValue("@lastName", actorElements[2]); comm.Parameters.AddWithValue("@suffix", actorElements[3]); return Convert.ToInt32(comm.ExecuteScalar()); } catch (Exception ex) { MessageBox.Show(ex.Message); throw; } } finally { conn.Close(); } } private List<string> GetNameParts(string homosapien) { const int FIRSTNAME_INDEX = 0; const int MIDDLENAME_INDEX = 1; const int LASTNAME_INDEX = 2; const int SUFFIX_INDEX = 3; List<string> _nameParts = new List<string>(); string[] nameElements = homosapien.Split(' '); // Assuming that the cat (or chick) has a first name _nameParts.Add(nameElements[FIRSTNAME_INDEX]); int countOfElements = nameElements.Length; if (countOfElements == 2) { _nameParts.Add(" "); _nameParts.Add(nameElements[MIDDLENAME_INDEX]); _nameParts.Add(" "); return _nameParts; } //if (finished) return _dirParts; // If we get to here, there are either three or four elements. if (countOfElements == 4) { _nameParts.Add(nameElements[MIDDLENAME_INDEX]); _nameParts.Add(nameElements[LASTNAME_INDEX]); _nameParts.Add(nameElements[SUFFIX_INDEX]); } // If three, need to determine if the third element is a middle name or a suffix else if(countOfElements == 3) { if (nameElements[1].Contains(",")) { _nameParts.Add(" "); // middle name left blank _nameParts.Add(nameElements[LASTNAME_INDEX]); _nameParts.Add(nameElements[SUFFIX_INDEX]); } else // there is a middle name, but no suffix { _nameParts.Add(nameElements[MIDDLENAME_INDEX]); _nameParts.Add(nameElements[LASTNAME_INDEX]); _nameParts.Add(" "); // suffix left blank } } else if (countOfElements == 1) { _nameParts.Add(nameElements[FIRSTNAME_INDEX]); _nameParts.Add(" "); _nameParts.Add(" "); return _nameParts; } return _nameParts; } }}Earlier we discussed how programming is an artistic, or at least creative, endeavor, because any given challenge has a multitude of ways of solving it. If you gave a program’s “functional specs” (functional specifications, or what it should do and be and accomplish) to ten different programmers, they would come up with at least eleven different ways of implementing the program. Really! At least one of them would come up with two (or more) different ways.
In fact, it’s likely that you will do that yourself when you begin tackling coding projects of your own, or those assigned to you, either by an instructor or an employer or client. You will think about how to create the app and begin down one path, but then realize you have come to a dead end, backtrack, and embark on a different path. But don’t worry about it or think you have wasted time in doing that. That’s normal. You have to explore in order to see the “side passages.” And yes, you have to make mistakes sometimes in order to learn from them. Just like life itself, right? The point is that we do learn from them, and move on.
One of the ways I could have coded this project differently was to call separate methods for each Table Insert opertion, rather than have all of the Insert code in one long method (Event Handler). For example, in the Button’s Click Event Handler, we could have put this code:
Popul8Directors();Popul8Screenwriters();Popul8MainTable();Popul8Genres();Popul8Movies_Genres();Popul8Movies();Popul8Actors_Movies();Note: In the past, some people have considered it silly of me, or “too cutesy” anyway, to spell the word “Populate” as “Popul8” in code; if you have similar queasiness about these flights of fancy of mine, feel free to alter the names of the methods accordingly. But, be warned: you will never get back those 3.14 seconds it will take you to make those changes!That would have perhaps been more “elegant,” and that may, in fact, be a better way of doing it. These are the decisions you will need to make when you’re programming. There will be pros and cons to choosing this path for your code to take or that path for your code to take, and you will need to decide which is best, in the long run. Similar to choices you make in life itself, right?
But a counter-argument against going down that particular road is that we would have had to move several of the variables up to the top of the Form, making them global, rather than keeping them sequestered away within our btnPopul8MovieData_Click Event Handler method.
So you see, there are different ways of accomplishing the same thing, and usually both have both pros and cons.
Some would even (and I have done this, too) only call one method from any Event Handler code, and then that method would call the others. In that case, the Event Handler code might look like this:
private void btnPopul8MovieData_Click(object sender, EventArgs e){ InsertData();}...and the method it calls would be like this:
private void InsertData(){ Popul8Directors(); Popul8Screenwriters(); Popul8MainTable(); Popul8Genres(); Popul8Movies_Genres(); Popul8Movies(); Popul8Actors_Movies();}How you actually implement your code usually depends largely, if not solely, on yourself and your preferences (what makes the most sense to you/is easiest for you to understand), but sometimes you must take into consideration whether others will also be looking at your code, and possibly even modifying it, and what is going to be easiest for them to understand. And yes, many employers have a set of “coding standards” that they expect you to adhere to. Sometimes they are minimalistic and somewhat informal, other times they are voluminous and must be strictly followed.
Also, the thought of other people reading our code emphasizes the advantage (to others, especially) of commenting your code, to explain any “tricky” parts or things that are unusual, such as code you had to add to deal with ceertain unusual scenarios or situations. We’ll see one of these addressed in the next Step – “special-case” code that had to be added because of a person who simply goes by the name “McG” (no first name – or no last name, maybe).
And besides, commenting your code can also help you when you revisit code that you wrote, perhaps months or even years later. You may not remember why you did what you did, or perhaps your coding style has changed, and the way you attacked a challenge previously might differ from how you would confront it now; and so, by having your comments to assist you in understanding “the madness to your method,” the frustration you save may end up actually being your own.
Before we “sign off” for this Step, we will briefly go over the “using clauses” and the connection string declared at the top of the form:
using MySql.Data.MySqlClient; using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Text; using System.Windows.Forms;The “using clauses” section of the code contains references to pieces of C# code that either come with Visual Studio or that are acquired from a third party (such as the MySql.Data.MySqlClient, which we need in order to access the MySql-specific objects, such as MySqlConnection).
You can probably figure out why we need some of these (besides the MySql reference): Collections.Generic for Lists, IO because we’re reading a file, and Forms because we’re creating a Windows Forms app.
As the “real” (steppable) code begins, note, in particular, the connection string (named connstr as is typical) needed to access the database:
namespace Popul8MovieData { public partial class Form1 : Form { MySqlConnection conn; string connstr = "server=127.0.0.1;user id=root;password=gr8GooglyMoogly; persistsecurityinfo=True;database=movies";The difference between this connection string and the one we used when we were dealing with U.S. States data is the database value: it is now “movies” instead of “statesdb.” If you don’t have the correct connection string, the database will not allow you to have access!
In the next Step, we will go over the rest of the code with a rather fine-toothed comb.
Until then!
Earth-shakingly Important Notice: If you have a basic programming question (suitable to an audience of “Kids”), send it to idiolectable@gmail.com, specifying whether you would like your name and location used if it is printed in a future “Step” of this newsletter. If you are a subscriber to the newsletter, you can also leave a question at the bottom of this Step, in the “Comments” section.If you do not want to give your real name, a nickname is acceptable (the first “Letter to the Editor” of mine that was printed appeared in Rolling Stone magazine, back in the early 1970s, and I signed it “Sylvester” for some reason which I no longer remember). Finally, it’s always interesting to see where people are from, so please provide your City or Town and the State it’s in, too (or Province, or whatever the region where you live is called).To listen to this Step, the audio of it can be found here: