REAL-WORLD COMPUTER PROGRAMMING FOR KIDS of ALL AGES
STEP 42: PROGRAMMATICALLY POPULATING THE NEW COLUMN
Hello again, everyone!
In the last Step, we showed how to create a “Combined Ranking” score of the three rankings stored in the states Database, namely of Population, Size (in Square Miles), and Date Admitted to the Union. But that was a temporary deal – it created a temporary, or on-the-fly, column. Let’s make the column permanent now, and populate it by again updating the states Table with C# in our Utility App.
So, if you’re playing along, follow these steps:
0) Open DBWB, 2-click the states Table, and go to the Columns tab
1) Add a new Column by clicking the “Add New Column” icon here:
A new column will be added at the bottom of the current list.
2) Modify its rather spartan definition by changing it to this:
Now you can see on the DDL tab that it has been updated to match the column you just added.
3) Click the “Create/Alter Table” icon rectangled below:
4) Start Visual Studio and open the Utility App.
5) Drag and drop a Button from the Toolbox to the Form
6) Give the Button a descriptive name, such as btnCombinedRanking and descriptive Text, such as “Add Combined Ranking Column”
7) 2-click the Button to open up the code window
8) Add the following code to the Button’s Click Event Handler:
private void btnCombinedRanking_Click(object sender, EventArgs e){ int _areaRank = 0; int _populationRank = 0; int _dateAdmittedRank = 0; string _stateName = string.Empty; try { conn = new MySqlConnection(connstr); conn.Open(); MySqlCommand comm = conn.CreateCommand(); comm.CommandText = "SELECT stateName, areaRank, populationRank, dateAdmittedRank FROM states"; MySqlDataReader rdr = comm.ExecuteReader(); while (rdr.Read()) { _stateName = rdr.GetString(0); _areaRank = rdr.GetInt32(1); _populationRank = rdr.GetInt32(2); _dateAdmittedRank = rdr.GetInt32(3); InsertCombinedRankValue(_stateName, _areaRank, _populationRank, _dateAdmittedRank); } } finally { conn.Close(); }}
It is plain that this code differs fairly dramatically from what we have put in the Utility up until now, in that it does not open a file and then loop through its contents. Instead, it queries the database, then loops through the result set, adding together the values of all three previously-existing “Rank” columns, then updates the new column (combinedRank) with that calculated value.
As some of this code is new to you, I will now go through the unfamiliar parts of the code directly above block-by-block, explaining what it is that you are seeing and what is happening:
int _areaRank = 0;int _populationRank = 0;int _dateAdmittedRank = 0;string _stateName = string.Empty;These variables will hold the corresponding values I read from the Database.
comm.CommandText = "SELECT stateName, areaRank, populationRank, dateAdmittedRank FROM states";MySqlDataReader rdr = comm.ExecuteReader();The above differs from our previous SQL/MySQL code in that it is not an INSERT command, but a SELECT query. And it uses a MySQLDataReader.
while (rdr.Read()){ _stateName = rdr.GetString(0); _areaRank = rdr.GetInt32(1); _populationRank = rdr.GetInt32(2); _dateAdmittedRank = rdr.GetInt32(3); InsertCombinedRankValue(_stateName, _areaRank, _populationRank, _dateAdmittedRank);}
I don’t think we’ve talked about the while statement yet. I’ll tell you about that in awhile.
Now’s the time. A while statement runs the following block of code while a certain condition is true. In this case, the condition implied is that while there are still records to be read by the MySQLDataReader object from the result set, run the following code. A similar fairly common while statement is “while not eof” which means “while not end of file” which means “while not at the end of the file.”
Here’s where the MySQLDataReader loops through the result set (everything returned from the SELECT query). It looks at each Row, and assigns the values read into the appropriate variables. The piece of data at Index 0 (which is the name of the State, as that is the first column requested in the SELECT query) is assigned to the _stateName variable; the item inhabiting Index 1 in the result set is the Area Rank, and it is assigned to the _areaRank variable, etc. Since the first item assigned is a String, we call MySQLDataReader’s GetString method to retreive it. Since the others are all ints, we call its GetInt32 method. Then, after those values are assigned, we call a new method named InsertCombinedRankValue, and we pass that method the variables we have assigned in this code.
So we should look at that code, too:
private void InsertCombinedRankValue(string stateName, int areaRank, int populationRank, int dateAdmittedRank){ int combinedRank = areaRank+populationRank+dateAdmittedRank; try { conn = new MySqlConnection(connstr); conn.Open(); MySqlCommand comm = conn.CreateCommand(); comm.CommandText = "UPDATE states SET combinedRank = @combinedRank WHERE stateName = @stateName"; comm.Parameters.AddWithValue("@combinedRank", combinedRank); comm.Parameters.AddWithValue("@stateName", stateName); comm.ExecuteNonQuery(); } finally { conn.Close(); }}
This is simpler than our previous INSERT code – we’re not reading a file or looping through anything. We simply add up the “Rank” values passed in, this way:
int combinedRank = areaRank+populationRank+dateAdmittedRank;...and then insert that calculated value into the Database, locating the correct Row to update based on it having the same value for stateName as appears in the WHERE part of the UPDATE command and the stateName Parameter.
9) Now run that code (start the Utility App in Visual Studio and click the “Add Combined Ranking Column” Button).
This code worked, and updated the states Table with the combinedRank values. To verify that the correct values were calculated and inserted, we can run the following Query in DBWB:
SELECT stateName, areaRank, populationRank, dateAdmittedRank, combinedRankFROM states
ORDER BY combinedRankWe see this:
Some simple math (such as, when looking at Texas, 2+2+28 = 32; or California, where 3+1+31 = 35, or Missouri, where 18+18+24 = 60) assures us that yes, it worked as designed. We now know who wins the State Triathlon: Texas. And to think that up to now whenever I thought of Texas what came to mind was large donuts, Alamo dog food, Cowboys, and ZZ Top!
Speaking of the Cowboys, what’s left to tackle with this states data? In the next Step, we will create another app that will display not only the data we’ve already looked at, but also the images that are stored in the bookImagePath column, and the amazon pages that the data stored in the bookURL column point to.
Until then!
This entire leg of the journey has already been completed (although not released via the newsletter in whole yet). If you would like to get “the rest of the story” right away, the book is available as a paperback here: https://www.amazon.com/Real-World-Computer-Programming-Kids-Ages/dp/B08J21B69H/ and in Kindle format here: https://www.amazon.com/Real-World-Computer-Programming-Kids-Ages-ebook/dp/B08J2ZH66R/
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: