Learning New (And Secure) Tricks

Last time, I’d explained the start of my form, where I’m now adding items to a list and then adding those items to a checkboxlist. I also said in the last post that I wanted to give users the ability to delete items from the list in case any mistakes were made. This one actually wasn’t too hard to do, but it was difficult at first because I didn’t know the syntax to get it done.

On the event that the Remove button is clicked, I do a foreach loop through the checkboxlist. For each checkbox that’s selected, I remove the item from the list _items based on the checkbox item’s value. Note: when I first did this, I was trying to remove the items from the checkboxlist. That didn’t work, because every time the page reloads, all the items from _items repopulates the checkboxlist, so it just comes right back. It has to be deleted from the source for it to work.

void btn_remove_Click(object sender, EventArgs e)
        {
            ItemNumberRepository _myTempDatabase = new ItemNumberRepository();
            foreach (ListItem checkbox in UPCList.Items)
            {
                if (checkbox.Selected)
                {
                    _items.Remove(checkbox.Value);
                }
            }
            PopulateItemsList();
        }

At this point, I had the ability to add and delete. I’m done, right?… Wrong. Considering this information is pretty sensitive stuff, I wanted to be able to keep track of when items were added and by whom. Also, since two people were going to be keying this information in, I needed a centralized database for them both to add to. Thankfully, we had an instance of MS SQL that wasn’t being used heavily, so I started asking questions about how I can add my items to an SQL table. There’s some frameworks that help, but Matt Groves encouraged me to start humbly by using ADO.NET. That way I know exactly how it all works without being pampered by a framework.

I first started by getting a background of how and why ADO.NET was created. Wikipedia has a pretty good post about it, but I didn’t confirm the resources, so reader beware. I then took a look at the ADO.NET Overview on MSDN, which has many links to great resources that’ll teach about all you’d want to know about it. Also, a quick search through Pluralsight’s library can bring up many good examples and resources.

After wrapping my head around the fundamentals, I jumped in and started writing. I didn’t want to hit the database too much, so I decided to do a final submit that would send the entire list in one shot instead of sending each item individually. However, my boss wants me to have a backup in case the machine crashes in the middle of data entry, so I may go with adding individually if I can’t find a better solution. Enough about that though; on to the code! First, you need a connectionString that specifies what server and table to connect to. This can be put in the config of the project, but I’ve been lazy and just haven’t done it yet. Next, I do a for loop to run through all the items in the checkboxlist and run a command to add all the items into the table and add the account number that’s typed into the acctNum field on each field.

protected void final_submit_Click(object sender, EventArgs e)
        {
            string connectionString =
            "Data Source=server;Initial Catalog=table;"
            + "User Id=userid;"
            + "Password=password;";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                for (int i = 0; i < UPCList.Items.Count; i++)
                {
                    string acctnum = AcctNum.Text;
                    string finalSubmit =
                        "INSERT INTO Boxes (BoxNumber)"
                        + "VALUES (@boxnumber)"
                        + "INSERT INTO DestructionOrder (AccountNumber)"
                        + "VALUES (@accountNumber)";

                    SqlCommand command = new SqlCommand(finalSubmit, connection);
                    command.Parameters.AddWithValue("@boxnumber", UPCList.Items[i].ToString());
                    command.Parameters.AddWithValue("@accountNumber", acctnum.ToString());
                    command.Connection.Open();
                    command.ExecuteNonQuery();
                    command.Connection.Close();
                }
            }
        }

The variables @boxnumber amd @accountNumber are parameters that are passed in instead of the live data from the textboxes. This will stop people from being able to inject SQL into my form and mess around with my tables. More info about this and many other security issues can be found at Keith Brown’s ASP.NET Security Pluralsight course.

The insert here is pretty simple: you specify which table (Boxes) and which column (BoxNumber) the data (@boxnumber) should go into (FYI: The pluses(+) are necessary if you decide to put your query on more than one line. They’re not necessary if you put your entire query on the same line). After that, you have to open your connection, execute the command and then close the connection. I would explain why closing the connection immediately is necessary in more detail, but I think Keith Brown does an amazing job at it, so just go watch that.

Well, that’s it for this week. I’m writing this to help people out where I’ve struggled in the past, so if you know anyone that’s new to ASP.NET, webforms, or ADO.NET, please share this with them…and once again, thanks for reading!

Advertisements