REAL-WORLD COMPUTER PROGRAMMING FOR KIDS of ALL AGES

STEP 43: SHOWING ALL THE DATA, INCLUDING IMAGES

Good whatever, everybody (morning, afternoon, or evening)!

Today, we will create another app that will display not only the data we’ve already looked at in DBWB (data displayed as text only), but also the images that are stored in the states Table’s bookImagePath column, and the amazon pages that the data stored in the bookURL column point to.

To do so, follow these steps:

0) Open Visual Studio and start a New Project, naming it “StatesData”

1) In the Properties pane, set the Size.Width of the Form to 924 and its Height to 760

2) From the Toolbox, drag two Panels onto the Form (from the “Containers” category), sizing them to take up most of the top half of the Form (on the first Panel) and most of the bottom half of the Form (on the second Panel)

Your form should now look something like this:

The top half of the form will contain a Data GridView control (kind of like a spreadsheet), and the bottom half will contain controls that will display the contents of the current Row (the one selected in the DataGridView by the user); this will include the image which, when clicked, will open the corresponding URL to the book’s amazon page.

3) From the “Data” category of the Toolbox, drag a DataGridView Control onto the Panel on the top half of the Form

4) Set the MultiSelect property of the DataGrid to false (this is to prevent more Rows than one to be selected/highlighted at a time)

5) Set the SelectionMode property of the DataGrid to FullRowSelect so that clicking within a Row causes the entire Row to be selected/highlighted (not just the clicked-on Cell within the Row).

6) 2-click the Form to open the code window inside the default event for the Form, Form1_Load

7) Add the following line of code within the Form1_Load Event Handler:

dataGridView1.DataSource = GetStatesData();

8) Create the GetStatesData method as follows:

private DataTable GetStatesData()

{

    try

    {

        conn = new MySqlConnection(connstr);

        conn.Open();

        try

        {

            DataTable dtStates = new DataTable();

            MySqlCommand comm = conn.CreateCommand();

            comm.CommandText = "SELECT * FROM states";

            MySqlDataReader rdr = comm.ExecuteReader();

            dtStates.Load(rdr);

            return dtStates;

        }

        catch (Exception ex)

        {

            MessageBox.Show(ex.Message);

            throw;

        }

    }

    finally

    {

        conn.Close();

    }

}

Something new here is the try...catch block within a try...finally block (instead of try...catch...finally it’s try...try...catch...finally).

You can probably by now pretty much understand what the code is doing by reading it over. Note that the DataGridView is populated with a Load() method. The now-populated DataTable is then passed back to the caller (the Form’s Load Event Handler), assigning it as the DataSource of the DataGridView, thus populating the DataGridView.

Note: You will again need to right-click the MySqlCommand object and allow it to Find and Install the necessary NuGet MySQL package. This will also, as it did in a previous Step, add the using clause “using MySql.Data.MySqlClient;”

I also had to add this code at the top of the Form code file (Form1.cs):

MySqlConnection conn;

string connstr = "server=127.0.0.1;user id=root;password=gr8GooglyMoogly;persistsecurityinfo=True;database=statesdb";

In context:

public partial class Form1 : Form

{

    MySqlConnection conn;

    string connstr = "server=127.0.0.1;user       id=root;password=gr8GooglyMoogly;persistsecurityinfo=True;database=statesdb";

public Form1()

{

    InitializeComponent();

}

. . .

The Form now looks like this at Runtime:

Populating the Bottom Half of the Form with the Currently Selected Row

The next thing we will do is add Controls to the bottom half of the Form, and populate them with the values from the currently selected Row, or Record. This will prevent you from having to scroll the DataGrid to see all the columns. Plus, the data will be larger and thus easier to read.

First, we need to drop several Controls on the Form. There are seven values from each Database Row which can be displayed in TextBoxes; there is one Date Value, which will be displayed in a DateTimePicker Control; and one path to an Image, which (Image, not its path) we will display in a PictureBox Control. Also, we will go ahead and add the Controls that the user will later use to populate the haveResidedThere, haveVisited, and notes fields/columns of the states Table.

So, drag and drop the following Controls and Componenets onto the Panel at the bottom half of the Form:

7 TextBox Controls

1 DateTimePicker Control

1 PictureBox Control

1 RichTextBox Control

2 CheckBox Controls

1 Button

9 Label Components, one next to each Control to describe each value being displayed -- except for the picture, which should be obvious, and the CheckBox Controls, which have a built-in Text Property that you can use for them.

Note: When you are adding a bunch of a certain type of Control or Component onto a Form, you can drag and drop one, then copy it and paste it as many times as you need to. This will save you some of that tedious drag, drop, select the Toolbox; drag, drop, select the Toolbox, etc. etc. ad infinitum ad nauseum. You then just drag the “clones” of the first Control you dragged and dropped to where you want them on the Form.

Lay them out however you want to. I have arranged them like so:

Now that you’ve got them arranged, or laid out, on the Form, give the Controls descriptive names. You don’t need to name the Label Components, as you won’t be referencing them in code. Change the Text value of the Labels, of course, but leave the Name property the default value (Label1, etc.). However (you remember “However,” right?), there is one exception to this: the Label used above the “Notes” RichTextBox should be named. A fitting name is lblNotes. You will see why this exception is made soon.

Name the TextBoxes using a consistent pattern, such as txtbxThis and txtbxThat, such as txtbxStateName, txtbxSquareMiles, etc. As there is only one DateTimePicker, and only one PictureBox, you can elect to change their default Name or not.

You doubtless noticed that some of the Controls and Components have a different appearance. This is to make them stand out as being the elements that the user can change. These are the two Checkboxes and the Notes RichTextBox Control. All of these Controls and Components are made invisible for now, as we won’t get into that part of it until later. That being the case, here is how the Form looks at Runtime (at this time):

Now for some code. We want all of the user-modifiable Controls to be invisible for now, but later we will make them visible. So I added this code to accomplish that:

private void Form1_Load(object sender, EventArgs e)

{

    VisibilizeUserControls(false); // Later we will change the argument to "true"

    dataGridView1.DataSource = GetStatesData();

}

private void VisibilizeUserControls(bool Visiblize)

{

    richTextBoxNotes.Visible = Visiblize;

    ckbxLived.Visible = Visiblize;

    ckbxVisited.Visible = Visiblize;

    lblNotes.Visible = Visiblize;

    btnUpdate.Visible = Visiblize;

}

It’s easier to do this in code than to tediously change all of the Controls’ Visibile properties to false in the Designer (and later back to true). This method (VisibilizeUserControls) takes a bool argument, so is called either as VisibilizeUserControls(false) or VisibilizeUserControls(true). When false is passed, it assigns that variable to each Control named. Later, I will change the argument to true (when we are ready to have the user update the Database).

This is the beauty of boolean logic and using the bool data type – rather than change a bunch of “false” assignments to “true,” we simply change the argument passed when calling the method from “false” to “true” and the Visibilize value, which is assigned to each Control specified in the method, is assigned accordingly. The value of Visibilize is changed based on the argument passed. So, just to be clear, as of now the first line of the code in the method equates to “set the Visible property of the RichTextBox Control to false”; later, after we change the value of the argument passed to the method, it will be the opposite (set it to true).

But as you can see, the Controls are not displaying data from the Database. The only Control showing any value at all is the DateTimePicker, which is showing us the current date (at the time I wrote this, of course). So now let’s try to get the Controls to do that.

SHOWING DATA FROM THE states TABLE IN THE CONTROLS

To grab data from the DataGridView’s currently selected Row and populate the Controls with that data, follow these steps:

0) In the Form Designer, 2-click the DataGridView

1) This will open the DataGridView‘s CellContentClick Event Handler:

2) Add the following code:

private void dataGridView1_CellContentClick(object sender,   DataGridViewCellEventArgs e)

{

    const int AREA_COLUMN = 2;

    const int POP_COLUMN = 3;

    const int DATE_COLUMN = 4;

    const int AREARANK_COLUMN = 5;

    const int POPRANK_COLUMN = 6;

    const int DATERANK_COLUMN = 7;

    const int IMAGEPATH_COLUMN = 8;

    const int BOOKURL_COLUMN = 9;

    const int COMBINEDRANK_COLUMN = 13

    int combinedRankValue = 0;

    int combinedRankValueRank = 0;

    if (dataGridView1.Rows[e.RowIndex].Cells[STATE_COLUMN].Value != null)

    {

        txtbxStateName.Text = dataGridView1.Rows[e.RowIndex]. Cells[STATE_COLUMN].Value.ToString();

        txtbxArea.Text = dataGridView1.Rows[e.RowIndex]. Cells[AREA_COLUMN].Value.ToString();

        txtbxPopulation.Text = dataGridView1.Rows[e.RowIndex].  Cells[POP_COLUMN].Value.ToString();

        dateTimePicker1.Value = (DateTime)dataGridView1.Rows[e.RowIndex]. Cells[DATE_COLUMN].Value;

        txtbxAreaRank.Text = dataGridView1.Rows[e.RowIndex]. Cells[AREARANK_COLUMN].Value.ToString();

        txtbxPopulationRank.Text = dataGridView1.Rows[e.RowIndex]. Cells[POPRANK_COLUMN].Value.ToString();

        txtbxDateRank.Text = dataGridView1.Rows[e.RowIndex]. Cells[DATERANK_COLUMN].Value.ToString();

        combinedRankValue = (int)dataGridView1.Rows[e.RowIndex]. Cells[COMBINEDRANK_COLUMN].Value;

        combinedRankValueRank = GetCombinedRankRank(combinedRankValue) + 1;

        txtbxCombinedRank.Text = string.Format("{0} ({1})", combinedRankValue, combinedRankValueRank);

        pictureBox1.ImageLocation = dataGridView1.Rows[e.RowIndex]. Cells[IMAGEPATH_COLUMN].Value.ToString();

        currentBookURL = dataGridView1.Rows[e.RowIndex]. Cells[BOOKURL_COLUMN].Value.ToString();

    }

}

Now to dissect this code a bit, section by section:

const int AREA_COLUMN = 2;

const int POP_COLUMN = 3;

const int DATE_COLUMN = 4;

const int AREARANK_COLUMN = 5;

const int POPRANK_COLUMN = 6;

const int DATERANK_COLUMN = 7;

const int IMAGEPATH_COLUMN = 8;

const int BOOKURL_COLUMN = 9;

const int COMBINEDRANK_COLUMN = 13

Here we declare constants for the index of the columns. This way we can better understand which column we’re working with, rather than just using an integer value which provides no real meaning by itself.

You might wonder, though: what about the stateName column? Good question. The reason it’s not here is because it is declared above, near the start of the Form’s code, shown here in context:

public partial class Form1 : Form

{

    MySqlConnection conn;

    string connstr = "server=127.0.0.1;user id=root;password=gr8OoglyMoogly;persistsecurityinfo=True;database=statesdb";

    const int STATE_COLUMN = 1;

    string currentBookURL = string.Empty;

public Form1()

{

    InitializeComponent();

}

It is declared up there, apart from its brethren, because it is referenced in another method, also, and remembering the DRY principle, it is moved to the “global” area. You will see the other place it is referenced later.

int combinedRankValue = 0;

int combinedRankValueRank = 0;

The variables above are declared because they are needed in conjunction with the GetCombinedRankValueRank method that is called from this Event Handler, which we will discuss in a little bit.

if (dataGridView1.Rows[e.RowIndex].Cells[STATE_COLUMN].Value != null)

{

    txtbxStateName.Text = dataGridView1.Rows[e.RowIndex]. Cells[STATE_COLUMN].Value.ToString();

    . . .

    dateTimePicker1.Value = (DateTime)dataGridView1.Rows[e.RowIndex].  Cells[DATE_COLUMN].Value;

    . . .

    combinedRankValue = (int)dataGridView1.Rows[e.RowIndex]. Cells[COMBINEDRANK_COLUMN].Value;

    combinedRankValueRank = GetCombinedRankValueRank(combinedRankValue) + 1;

    txtbxCombinedRank.Text = string.Format("{0} ({1})", combinedRankValue, combinedRankValueRank);

    pictureBox1.ImageLocation = dataGridView1.Rows[e.RowIndex]. Cells[IMAGEPATH_COLUMN].Value.ToString();

    currentBookURL = dataGridView1.Rows[e.RowIndex]. Cells[BOOKURL_COLUMN].Value.ToString();

This is the meat of the code that populates the Controls with Data from the DataGridView. Most of the Controls grab their value from the current (selected) Row (that’s what e.rowIndex is). The “e” stands for “EventArgs.” When the DataGridView is clicked, it knows which of its Rows have been selected, and exposes that data in e.RowIndex. As for the column, we just tell it which one we want to look at, such as STATE_COLUMN, which is column 1 (the second one from the left, as the first one is column 0). To get the combinedRankValue, we need to “cast” the returned value as an int. The data in any given Cell can be any Data type the Database supports, so we must tell the compiler what data type it is, so it knows it’s “cool” to assign that to the int variable combinedRankValue.

Then the GetCombinedRankValueRank method is called, passing that value. The reason for ranking the combinedRankValue is that the number itself doesn’t tell us much. For example, Georgia has a combinedRankValue of 33, but what does that mean? Is it good? Missouri has a combinedRankValue of 60, but what does that mean? By ranking the combinedRank number, which means little until its compared with the other States’ numbers, we see where they stand in relation to each another. For example, Georgia with 33 points is #1. Missouri with 60 is #12, etc.

We will look at the SQL needed to calculate this ranking soon. Also, the code behind the GetCombinedRankValueRank method (and, when we’re through with this app, all the code will be published for your viewing pleasure and usage).

Next we have the code to load the Image into the PictureBox. To do this, we assign the value from the bookImagePath column to the PictureBox’s ImageLocation property.

Note: To get the entire image to display within the confines we gave the PictureBox, set the PictureBox’s SizeMode property to StretchImage. Sometimes, in the interest of commenting your code, even things like the settings of Properties, you might want to set these in Code, such as in the Form’s Load event or Show event. But Visual Studio does show you which Properties you changed from their default values (in the Properties pane) by bolding them. See, my habit of bolding things that are new are done by Visual Studio, too.

The corresponding image (the book cover of the book dedicated to the currently selected State) thus displays:

Finally (for this section of the code), there is the assignment to the currentBookURL variable. You may have noticed that it was declared globally at the top of the form, too. This is because the value is assigned in one place, and referenced in another place (in the PictureBox’s Click event). We will examine that code later – actually in the next Step. We will also then examine the code in the GetCombinedRankValueRank method as well as the code in the PictureBox’s Click Event Handler, which causes the URL for the book shown in the image to open in your Browser.

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

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/

To listen to this Step, the audio of it can be found here: