Adding Controls during Scroll Event (Improved)

Yesterday I posted a solution for dynamically adding controls onto a form when the canvas is constantly changing the coordinates (i.e. during the scroll event). Well, to put it bluntly, that solution sucks. It works perfect as long as the thread is busy and you can’t scroll while its adding the controls, but the minute you move it to a background worker so you can offer continuous scrolling it works only intermittently. The problem is actually easy to see, and I should have seen it…the canvas changing is not in sync with where you think the next image should be placed.

I have to admit that I didn’t test my solution out completely before posting it yesterday. I even added a comment (that I later removed, once I implemented it in my project) at the end of the article that basically said if you need this during the scroll event then put it in a background event so the user would have smoother scrolling and the UI wouldn’t lock up while the controls where being created and added. So, today I have a better solution (one that I like better anyway), and I have tested it more. Yesterday’s solution would work if you were using a button instead of the scroll event, but today’s solution is a little more elegant…I think anyway.

Today we will use the same example as yesterday, but with the better solution.

Step one – create a windows form with a panel in it.

Because I went over the details (and example of) the problem yesterday, I am just going to post my final code and then I’ll explain what we’re doing. I’ll even include the background worker in this example and remove the limitation on the scroll event, so you will need to add a background worker to your form also.

You will need the following using statements:

using System;
using System.Drawing;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Threading;

And the following variables in your global scope:

//counter so we know what column we are in.
int counter = 0;

//we need to keep track of the number of the control.
int number = 0;

//the number of controls we can place on one line to avoid
// horizontal scrollbars.
int rowLength = 0;

//since we are using a background worker we need a list
// that can be accessed by both threads.
List<Label> labelList = new List<Label>();

//since our list is shared between threads, and we are using
// the scroll event, we need to know who is using it when
// so we don’t get any exceptions if they both try to use it
bool listInUse = false;

And inside your load event:

//Get the number of controls we can place per row. 
//60 is the width of our control + a 5px padding.
rowLength = (int)Math.Floor((double)Panel1.Width / 60);
AddControls();

And the AddControls function:

if (!backgroundWorker1.IsBusy)
{
    backgroundWorker1.RunWorkerAsync();
}

And inside the panel’s scroll event:

//only add new controls if the vertical scrollbar scrolls
if (e.ScrollOrientation == ScrollOrientation.VerticalScroll)
{
    //gives a little bit of a cushion…
    //not perfect but works
    if ((e.NewValue - e.OldValue) >= 15)
    {
        AddControls();
    }
}

And the background worker DoWork event:

//if our shared list is in use, wait until it is not anymore
while (listInUse == true)
{
    //wait 500ms and check again
    Thread.Sleep(500);
}

//set listInUse to true so the main thread doesn’t try to use it
listInUse = true;

//clear the list
labelList.Clear();

//add 500 labels to our list
for (int i = 0; i < 500; i++)
{
    //create the label and set some properties
    Label lbl = new Label();
    lbl.Text = i.ToString();

    //size is important with labels
    lbl.Size = new Size(55, 13);

    //border so we can see the bounds of the object
    lbl.BorderStyle = BorderStyle.FixedSingle;

    //add the label to the list
    labelList.Add(lbl);
}

//free up the list for our main thread
listInUse = false;

Inside the background worker RunWorkerCompleted function:

//if our shared list is in use, wait until it is not anymore
while (listInUse == true)
{
    //wait 500ms and check again
    Thread.Sleep(500);
}

//set listInUse to true so the other thread doesn’t try to use it
listInUse = true;

foreach (Label lbl in labelList)
{
    //create a point at 0,0
    Point location = new Point(0, 0);

    //we need to know if we are adding the first one or not
    if (this.number > 0)
    {
        //get the last control on the form
        Label lastLbl = (Label)Panel1.Controls["lbl" + (number-1).ToString()];

        //set the location so that it is where our last control is
        location = lastLbl.Location;

        //are we about to use the last column in this row?
        if (counter == (this.rowLength - 1))
        {
            //set the label to the left side of the panel
            location.X = 0;

            //move down one row
            location.Y += lastLbl.Size.Height;

            //reset our column counter
            counter = 0;
        }
        else
        {
            //same row, to the right of the last label
            location.X += lastLbl.Size.Width;
            counter++;
        }
    }

    //we need to name our label so we can find it later
    //we could just use the default name
    //but you can use anything, as long as it includes the number
    lbl.Name = “lbl” + number.ToString();

    //set the location, add to the panel and then paint
    lbl.Location = location;
    Panel1.Controls.Add(lbl);
    Application.DoEvents();
    number++;
}

//free up the list
listInUse = false;

Now, once we scrolled a few times we get the expected results:

Adding Controls during Scroll Event

So I have a need to add controls (Picture Boxes) to a panel during runtime. Easy enough, create a picture box, set the properties and add to the panel. Getting started was really easy, but when I wanted to add more controls as the user scrolled the panel, things began going bad fast.

First, create a form with a panel inside it. Be sure to set the Autoscroll property to true on the panel.

Then add 500 controls to your panel. For this example we will use a label with the text “1,2,3,4,5”.
    //Get the number of controls we can place per row.   
    //60 is the width of our control + a 5px padding.
    int rowLength = (int)Math.Floor((double)panel1.Width / 60);

    //keep a tally of our location 
    int x = 0;
    int y = 0;

    //counter so we know what column we are in.
    int counter = 0;

    //add 500 labels to our panel, one right after another
    for (int i = 0; i < 500; i++)
    {
        //create the label and set some properties
        Label lbl = new Label();
        lbl.Text = "1,2,3,4,5";

        //size is important with labels
        lbl.Size = new Size(55, 13);

        //border so we can see the bounds of the object
        lbl.BorderStyle = BorderStyle.FixedSingle;

        if (counter == rowLength)
        {
            //we are at the end of the row so set the new y coordinate 
            //(height of the control + padding)

            y += 15; //height + 2px padding
            x = 0;

            //reset x to 0 so we will start back at the left side
            counter = 0; //reset our column counter
        }

        //now that we know out coordinates we can set the location
        lbl.Location = new Point(x, y);

        //add the width of the control + padding to x
        x += 60; //width + 5 px padding

        panel1.Controls.Add(lbl); //add the control to the panel
        counter++; //increment our row counter

This gives us 500 labels as we would expect.

Now it gets really fun. How do we add more of the controls as we are scrolling? Well, my first thought was to keep track of our x, y, and counter variables by moving them outside the scope of our function. We also need to extract our code to its own function so we can call that in our scroll event. For the purposes of this demo we use a flag (cont) so that we only call our AddControls function once on scroll. Otherwise I could never show you what the problem is, and why.

private void panel1_Scroll(object sender, ScrollEventArgs e)
{
     //cont is a bool that is initially set to true so 
     //that we don't get stuck in a loop. 
     if (cont)  
     {
          //only add new controls if the vertical scrollbar scrolls
          if (e.ScrollOrientation == ScrollOrientation.VerticalScroll)
          {
               //gives a little bit of a window...not perfect but works
               if ((e.NewValue - e.OldValue) >= 15)
               {
                    AddControls();
                    cont = false;
               }
          }
     }
}

Clearly you can see the problem. There is some unusual white-space between the last control and the first control of the new call. After stepping through the code several dozen time I realized the problem wasn’t with my code, per se, but with my background as a web app developer. In a Windows Forms app the location is always the visible location. For example, 0, 0 is always the VISIBLE top left corner. This means that as we scroll our location logic becomes wrong.

In order to keep track of our next location, we can use a placeholder. Place a label on your form and set the size to (0, 0) and its location to (0, 0). Name the label placeHolder. Now, in your form’s load event set the visibility to false for the placeHolder.

Now, inside our AddControls function set the location of the placeholder to x and y after each control is added.

//now that we know out coordinates we can set the location
lbl.Location = new Point(x, y);

//add the width of the control + padding to x
//width + 5 px padding
x += 60; 

//add the control to the panel
Panel1.Controls.Add(lbl); 

//increment our row counter
counter++; 

this.placeHolder.Location = new Point(x, y);

Now, at the top of our AddControls function we set x and y to the x and y of the placeholder.

x = this.placeHolder.Location.X; 
y = this.placeHolder.Location.Y;

So, now our AddControls function looks like this:

private void AddControls()         
{
    x = this.placeHolder.Location.X;
    y = this.placeHolder.Location.Y;

    //add 500 labels to our panel, one right after another
    for (int i = 0; i < 500; i++)
    {             
        //create the label and set some properties    
        Label lbl = new Label();  
        lbl.Text = i.ToString();    

        //size is important with labels       
        lbl.Size = new Size(55, 13); 

        //border so we can see the bounds of the object      
        lbl.BorderStyle = BorderStyle.FixedSingle;        

        if (counter == rowLength)
        {
            //we are at the end of the row so set the new y coordinate  
            //(height of the control + padding)
            y += 15; //height + 2px padding      
            x = 0;       

            //reset x to 0 so we will start back at the left side       
            counter = 0; //reset our column counter  
        }

        //now that we know out coordinates we can set the location   
        lbl.Location = new Point(x, y);   

        //add the width of the control + padding to x     
        x += 60; //width + 5 px padding     

        Panel1.Controls.Add(lbl); //add the control to the panel     

        counter++; //increment our row counter     
        this.placeHolder.Location = new Point(x, y);  
    } 
}

I changed the text of our new labels from “1, 2, 3, 4, 5” to the value of i so we can see the fix in action.

Edit (8/21/2011): After I published this and implemented it into my own project, I realized it is a terrible solution, well once you add it to a background worker anyway.  I came up with a better solution that I think is more elegant and I would suggest it over this one for every situation.  It can be found here.

Google is buying Motorola Mobile

I found out yesterday that Google is buying Motorola Mobile for $12.5 Billion, reportedly for patent reasons.  I heard an analyst on the radio this morning say that acquisitions of this type only succeed 20% of the time, while the other 80% of the time it ends in failure.  The analyst said that if Google shuts down Motorola then it will be a a very good thing for Android, but if they keep the company going it could be the end of Android.

Now, Google has said that they plan on keeping Motorola operational, but they plan on keeping themselves very distant at the same time.  Motorola, for example, will still have to pay Google for the use of the Android software, and they will still have to put in a bid for Nexus.

Samsung reported yesterday that they welcome the purchase and Apple has no intentions of changing their game plan.

I’m not an industry analyst, or an expert by any means so I can not give an estimation on what will come about, but it will be very interesting to see how this will effect the smart phone landscape in the year(s) to come.  I would love for Windows Phone 7 to take over the playing field and become the dominant player, but I don’t want it to come about because Android fails due to this acquisition.  I want it to be because people start seeing the great things Microsoft does for the country, and because they fall in love with the software that Microsoft produces.  Besides, competition drives innovation, creates jobs, and keeps prices competitive.

There is nothing really profound or meaningful in my post this morning, I just wanted to share some of my thoughts about the deal.

Pushing multiple results into one column

 

It is often the case with relational databases that you have multiple many-to-one relationships between tables in your database.  For example, you might have a table that has order information (order id, customer id, payment details) and another table that simply lists the items purchased during a particular order (order id, product id).

We can do a simple join and get our results back, but if the customer purchased more than one item (e.g. a belt sander, a circular saw, and some #2 pencils) then you will get multiple rows back, like this:

Order ID: Customer ID: Products: Subtotal: Tax:
6549879 1256 Belt Sander $156.78 $25
6549879 1256 Circular Saw $156.78 $25
6549879 1256 #2 Pencils $156.78 $25

It would be better to get your results back in one row, like this:

Order ID: Customer ID: Products: Subtotal: Tax:
6549879 1256 Belt Sander, Circular Saw, #2 Pencils $156.78 $25

The query that I use to get the results into one row is:

SELECT DISTINCT
      [o].[OrderId] AS [Order ID:],
      [o].[CustomerId] AS [Customer ID:],
      (STUFF((SELECT (', ' + [p].[ProductName])
            FROM [dbo].[Products] AS [p]
            INNER JOIN [dbo].[SoldItems] AS [i]
            ON [p].[ProductId] = [i].[ProductId]
            WHERE [i].[OrderId] = [o].[OrderId]
            FOR XML PATH('')), 1, 2, '')) AS [Products:],
      [o].[Subtotal] AS [Subtotal:],
      [o].[Tax] AS [Tax:]
FROM
      [dbo].[Orders] AS [o]
WHERE
[o].[CustomerId] = 1256

 

As far as I know (as far as .net 4.0) there is no equivalent of “STUFF” or “FOR XML PATH” in LINQ.  If you know of an equivalent, I’d love to hear from you.

The other thing you need to know is that this will not work in SQL Server 2000.

Please share any comments you have!