REAL-WORLD COMPUTER PROGRAMMING FOR KIDS of ALL AGES

STEP 50: BREAKING IT DOWN BY BREAKING IT UP

In the last Step, we saw “the whole enchilada,” as they say – all the code we wrote. Now we will take it one “enchilada” at a time.

Photo of Enchiladas courtesy of Alexandra Golovac, from unsplash.com

Now I did it. I made myself hungry with that delectable image.

Anyway (focus, Clay, focus!) ... We will take the code literally from the top and break it down, bit by bit, by method and by sections within the longer methods. We’ll also discuss some tradeoffs in alternate ways the code could have been written. In other words, why did I do it the way I did, and what are some other ways it could have been done; and what are the advantages and disadvantages of choosing one way over the other.

The code starts out with the “using clauses” and connection string stuff, but we covered that already, in the last Step. We will now dive into the deep end of the pool, so to speak, into the meat of the code:

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;  

    List<string> genreList = new List<string>();

    List<string> actorsList = new List<string>();

    List<string> directorNamePartsList;

    List<string> screenwriterNamePartsList;

    List<string> actorNamePartsList;

These are the variables we will need. Notice that we have several string Lists (List<string> data type). The first two store all the genres for a movie and all the actors that appear in a movie – anywhere from 1 to 3, anyway. In our future expansion/Refactoring of the way the data is populated (in the upcoming 3rd major part of our Journey, into Web Technology), the number of Actors will not be limited to a measly three, but that’s all for now.

Also of note are the string Lists that hold names. These are to hold the four possible elements, or parts, of a name: First name, Middle name (optional), Last name, and suffix (also optional; if used, such as Sr., Jr., II, III, Esq., etc.)

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;

These constant declarations correspond to the thirteen elements (0..12) in the Semicolon-delimited file we read in. As we’ve seen before, named constants for integers make the code easier to read.

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);

This is the usual code for opening a file and reading its contents. We’ve seen its like before.

foreach (string line in fileStrArray)

{

    string[] dataElements = line.Split(';');  

    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();

The first seven items from the line of movie data which we got from the CSV file and are now assigning to local variables (above) are all pretty straightforward and similar to code we’ve seen in the past. The only slightly unusual thing here is that the call to Split() is splitting the elements in the line from the file on semicolons (“;”) instead of commas (“,”).

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);

This is a little different: We don’t know how many genres the movie will have assigned to it. It could be 1, 2, or 3. So the first entry is assigned to the string List genreList, but the other two are only added to the string List if they are not empty strings. When would they be empty strings?

If the “genres” part of the line looked like this:

Comedy; Drama; ;

...then genre3 would be empty. Similarly, if the “genres” part of the line looked like this:

Documentary; ; ;

...then both genre3 and genre2 would be empty.

So, although genre2 and genre3 will always have something assigned to them (even if it’s just an empty string), the string List (genreList) will only contain as many items as there are non-empty values. Hence the “if (!string.IsNullOrWhiteSpace()” code.

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);

The code for actors follows the same exact logic as that for genres.

Now we start populating the Tables. What would be the most logical Table to populate first – the main Table, that is movies_main? That seems logical, however (here we go with the “however” jazz again) it’s pretty much impossible.

Why?

Because movies_main takes an ID value for both Director and Screenwriter. And do we know the IDs for those yet? No. Why not? Because they are not in the file that we loaded and read from. Where do the IDs for Director and Screenwriter come from, then? As we’ll see right now, we get them only after inserting those values into their Tables.

// 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); " +

				"select last_insert_id();";

    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;

}

The first thing done above is the helper method GetNameParts is called. We’ll see the code later, but what it does is break the name passed into it into up to four parts. Often the name consists of only two parts, such as Billygoat Butthead (first name and last name); it could also be three, though, as in B. Billygoat Butthead or Billygoat B. Butthead (first initial or name, second initial or name, last name); or it could even be Billygoat Butthead, Jr. (first name, last name, suffix); four parts is also possible: Billygoat Beavis Butthead, Esq. (first name, middle name, last name, suffix).

The next thing the code above does is pass this string List (consisting of the parts of the Director’s name) to a method called DirectorAlreadyExists. We will also see this code later, but it does just as its name states: it returns a boolean value, true or false, indicating whether that name is already contained in the directors Table.

Then, if the name does not (!) exist in the Table already, the record is added. Often, some of the columns populated will be blank, but that doesn’t matter – what matters is that they are all in their alloted locations (last name should always be in the third spot, at Index 2, and the first name should always be in the first spot, at Index 0, etc.).

The last interesting piece of code in the snippet above, which we are encountering now for the first time, is the assigning of the LastInsertedId value to the director_id variable. Thus we have the ID for the Director “in hand” for when we need to insert it into the main (movies_main) table.

// 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); " +

				"select last_insert_id();";

    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;

}

The code for Screenwriters is exactly the same as that for Directors (except for the obvious difference of it being for the screenwriter value and ID rather than Director), so there’s no need to comment on it.

// 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; " +

		"select last_insert_id();";

	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;

}

Now the main table can be populated, because we have the Director ID value and the Screenwriter ID value. But we need something from this (Movies_Main) Table before we can populate the other tables, namely the Movie ID (movie_id).

Note the first line of code for this part above:

comm.Parameters.Clear();

We’ve seen it before, but have been focused on other things. It is a call to clear the Parameter values before assigning them again. This is not busywork. It needs to be done.

The other noteworthy line is this one:

comm.Parameters.AddWithValue("@imdb_rating", Math.Round(imdb_rating, 1));

See how the imdb_rating is encased in a Math.Round(<val>, 1) function? The reason for this is that otherwise this line of code:

alreadyExists =  MainRecordAlreadyExists(title, imdb_rating, movie_length);

...causes alreadyExists to always be assigned false – even when a seemingly identical set of values for title, imdb_rating, and movie_length already appear in the database. Notice how this was happening (the columns tested are rectangled):

It looks like the same Record got inserted in the database multiple times, right? Both rows have the same title (“The Princess Bride”), the same movie_length (98) and seemingly the same imdb_rating (8.1000).

But notice how I said seemingly the same imdb_rating? In actuality, the original code to assign that value was:

comm.Parameters.AddWithValue("@imdb_rating", imdb_rating, 1);

In other words, initially the Math.Round() function was not used. But once I added it, the code began to work fine – no more duplicate Rows. What was the problem? Why did adding Math.Round() fix it?

The reason is that what was really contained in one copy of the imdb_rating value may have been, say, 8.10001 whereas the other copy of that value may have been, say, 8.10002. Or they could have even been 8.100000000000000000002 and 8.100000000000000000003, and still been viewed as being different, thus not the same Record -- thus the other “different” one was added.

You may have two questions about this: Why was that happening? And How did using Math.Round(<val>, 1) solve the problem?

The answer to the first question is that the precision of real numbers (like float and double and such) has its limits. So you may not care that the two values differ by 0.00000000000000000001, but when you ask the computer, “Are these two values the same?” it looks at them, sees the (albeit miniscule) difference, and answers, “No, they’re not the same!”

Computers can’t think or reason. They don’t take context into consideration. They don’t know that we don’t care about a miniscule difference like that, that we consider 8.100000000000000000002 and 8.100000000000000000003 to be, for all practical purposes, the same number.

Until we tell it to consider context. This we do by using Math.Round(<val>, 1), which instructs the compiler to only “care about” one digit after the integral part of the number. So now, the compiler “sees” both values as simply 8.1 (the same), not 8.100000000000000000002 and 8.100000000000000000003 (different).

So that is the answer to the second question – we cure the compiler of its persnickitiness by telling it, “Just worry about the first decimal value, nothing beyond that.”

And that’s certainly reasonable, as the actual IMDB Rating is simply 8.1, with no zeroes after it. The extra zeroes were simply something added by the computer because it didn’t know how many decimal points we wanted to use.

// 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();

    }

}

Here is our first Lookup Table. It simply contains two columns: an ID value, and a textual (VarChar) Description. Since we’re comparing the Description with what’s already in the Table, it works fine.

// MOVIES_GENRES Many-to-Many Table

foreach (var gen_desc in genreList)

{

    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();

    }

}

And here is our first Many-to-Many Table. It contains two ID values, both of which are PKs (Primary Keys) in “their” Table and FKs (Foreign Keys) in this Table. For this Table, we have to verify that there are no Records/Rows that contain both of these values already. This is done by calling PairAlreadyExistsInMoviesGenresM2Mtable, which we’ll look at when we get to it. Many-to-Many Tables contain many of the same values in a given column, but the pair of values in a Row need to be unique. In other words, there may be many Rows in it with a movie_id of 29, and there may be many Rows in it with a genre_id of 67, but there should be (at most) only one Row where movie_id is 29 andgenre_id is 67.

// 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();

    }    

}

This is another Lookup Table, so the code is very similar in concept to the one we discussed earlier for the Genres Table. Notice, though, that it calls GetNameParts, which is also called in several other places in the code. They each pass in the name of a person and call it something specific in the argument (such as “actor” above), but in that method, the incoming argument is named homosapien (because it is called for Directors, Screenwriters, and Actors – who all happen to be homo sapiens, thus the more general name).

// 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();

    }

    . . .

This code is also virtually identical to that for the other Many-to-Many Table, Movies_Genres, so there’s really nothing more to discuss here.

Note: Where I put the ellipsis dots (“...”) throughout this Step, it just indicates that some rather “generic” (uninteresting to us at this time) bit of code has been elided.

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();

    }

}

There is nothing new in this method.

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();

    }

}

Nothing new here, either.

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);

        }

        . . .

Nothing new.

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);

        }

        . . .

This is the method that was always returning false due to the new imdb_rating value appearing different to the compiler than other very similar values. Even when the difference was as insignificant as that between one copy of a document you print out and the next copy of the same exact document – sure, one may have slightly more ink than the other, or some other almost microscopic difference, but still different – but not from our perspective or purposes, so I had to modify the code as explained above where the Insert into the Movies_Main Table is discussed (you can find it by searching for “Math.Round”).

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);

        }

        . . .

Same code (concepts) as other similar methods.

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);

        }

        . . .

This differs from some of the earlier “AlreadyExists” methods in that it passes in a string List as a parameter/argument. The concept is the same, though, as the others.

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);

	  }

	  . . .

Virtually identical to ScreenwriterAlreadyExists.

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());

	  }

	  . . .

}

There is nothing new about this code that needs to be examined.

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());

        }

        . . .

Basically the same thing as GetGenreIDForDescription above, except in this case all the elements of the name are used to find an exact match. At least one, if not two, of these values will usually be blank, or empty strings, but matching those are just as important in determining which ID to return to the caller.

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 (countOfElements == 4)

    {

        _nameParts.Add(nameElements[MIDDLENAME_INDEX]);

        _nameParts.Add(nameElements[LASTNAME_INDEX]);

        _nameParts.Add(nameElements[SUFFIX_INDEX]);

    }

    // If 3, 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;  

    . . .

Now this is an interesting, and somewhat complex, and maybe even convoluted, method. It is also an example, no doubt, of a piece of code that would be handled or approached differently by different programmers. What it does is takes a name, in the homosapien parameter, and breaks it out into the up to four elements that a name can have, namely first name, middle name, last name, and suffix.

To explain that, let’s say the name passed has only two elements. The assumption is made that they are a first name and a last name. So if the value passed in is “Rory Gallagher,” the first element (first name) is assigned “Rory”, the second element (middle name) is assigned “ ” (an empty string), the third element (last name) is assigned “Gallagher” and the final element (suffix) is assigned an empty string.

If there are four elements, it is easy: one element for each possible part of a name (first name, middle name, last name, suffix, in order of their appearance in the name).

But if there are three elements, it gets tricky. Is the third element a middle name or a suffix (we’re assuming two of them are first name and last name). What I have expected from the data is that if a suffix is used, a comma (“,”) precedes it. So “James Weldon Johnson” would be seen as first name, middle name, last name, whereas “Beeb Birtles, bassist” would be seen as first name, last name, and suffix.

Notice that this logic has a “nested if” statement. That is, there is the “if three elements” section, but within that, there is the “if comma exists” and “else” (comma does not exist) sections of code. Check it out:

else if(countOfElements == 3)

{

    if (nameElements[1].Contains(",")) // “nested if” – an if within an if

    {

        _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

    }

}

There’s even one cat (who goes simply by “McG”) who threw a spanner into the works* by only using that one name.

* Note: “Spanner” is British-ese for “Wrench”; you may have noticed that I like to use archaic and countrified words and phrases -- and British-ese -- to add flavour, spice, and quaintness to my prose (“flavour” is the weird British way of spelling “flavor”). Sometimes those Brits are almost as wasteful of ink as the French are, with their myriads of silent letters.

Try to figure out how all these situations were accounted for in the code above. I’ll give you a couple of minutes. . . . Take your time. ...

Okay, here’s some thoughts about the code I’d like to share:

The passed-in value is split on spaces (not commas, or semicolons, or anything else). That way the name “Rupert Pupkin” becomes the string List containing two items:

 Rupert

 Pupkin

Assuming that there will always be one, the first name is always added, right off:

 _nameParts.Add(nameElements[FIRSTNAME_INDEX]);  

Then the most common situation is dealt with (that the passed-in string contains two elements, first name and last name). In this case, a blank is added to the middle name part, the second element to the last name part, and a blank to the suffix part.

Then the easiest remaining situation is dealth with: if the passed-in string (homosapien) has all four elements.

Next is the “three-element scenario,” already discussed: the existence of a comma is what we go by to determine whether the third element is a suffix (when there is a comma in the passed-in value) or a middle name (when there is not a comma).

Finally, there is code to handle a situation where there is only one element to homosapien (such as “McG” or “Prince” or “Cher” or “Elvis” or “Freebo” or so).

In the next Step, we will query the data.

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 (with me seeming to talk really fast, because I had to compress 20 minutes down to 15), the audio of it can be found here: