Vfrdtyky

I have a program that updates two DataGridView objects every minute with a MySQL query from our internal orders database so we can keep track of older orders and make sure they're being handled.

It works well, but if you leave the program on it starts to get slower and slower and you really notice it after a day or so. Restarting the program fixes this, but I'd really like to know why I can't just leave this running.

Here is the timer code and the function it calls:

    private void timer1_Tick(object sender, EventArgs e)

    {

        UpdateOrderDisplay();

    }



        private void UpdateOrderDisplay()

    {

        grdOrderItems.Rows.Clear();

        grdHoldOrders.Rows.Clear();

        string strsql;

        string CustomerName;

        string MyConString = "SERVER=**********;" + "DATABASE=***********;" + "UID=************;" + "PASSWORD=***********;";

        using (MySqlConnection connection = new MySqlConnection(MyConString))

        {

            MySqlCommand command = connection.CreateCommand();

            MySqlDataReader Reader;

            strsql = "select * from orders where ship_reference=0 and OnHold =0 order by order_id asc";

            command.CommandText = strsql;

            connection.Open(); Reader = command.ExecuteReader();

            while (Reader.Read())

            {

                if (Reader["payment_date"].ToString() != "")

                {

                    if (Reader["custom"].ToString().Contains("~*"))

                    {

                        CustomerName = Reader["custom"].ToString().Substring(0, Reader["custom"].ToString().IndexOf("~"));

                    }

                    else

                    {

                        CustomerName = Reader["shipping_address_name"].ToString();

                    }

                    grdOrderItems.Invoke(new MethodInvoker(() => grdOrderItems.Rows.Add(Convert.ToDateTime(Reader["payment_date"].ToString().Substring(0, 21)).ToString(), Reader["txn_id"].ToString(), CustomerName, Reader["mc_gross"].ToString(), Reader["memo"].ToString(), Reader["order_id"].ToString())));

                }

            }

            connection.Close();

            strsql = "select * from orders where OnHold =1 order by order_id asc";

            command.CommandText = strsql;

            connection.Open(); Reader = command.ExecuteReader();

            while (Reader.Read())

            {

                if (Reader["payment_date"].ToString() != "")

                {

                    if (Reader["custom"].ToString().Contains("~*"))

                    {

                        CustomerName = Reader["custom"].ToString().Substring(0, Reader["custom"].ToString().IndexOf("~"));

                    }

                    else

                    {

                        CustomerName = Reader["shipping_address_name"].ToString();

                    }

                    grdHoldOrders.Invoke(new MethodInvoker(() => grdHoldOrders.Rows.Add(Convert.ToDateTime(Reader["payment_date"].ToString().Substring(0, 21)).ToString(), Reader["txn_id"].ToString(), CustomerName, Reader["mc_gross"].ToString(), Reader["memo"].ToString(), Reader["order_id"].ToString(),Reader["Hold_Review_Date"].ToString().Substring(0,Reader["Hold_Review_Date"].ToString().IndexOf(" ")),Reader["payer_email"].ToString())));

                }

            }











            connection.Close();

        }



    }

Also, in case it's relevant, here is some code I use to color code the lines of the DataGridView object so we can easily tell which order is older:

    private void grdOrderItems_RowPrePaint(object sender, DataGridViewRowPrePaintEventArgs e)

    {

        grdOrderItems.RowPrePaint += new System.Windows.Forms.DataGridViewRowPrePaintEventHandler(this.grdOrderItems1_RowPrePaint);



    }

    private void grdOrderItems1_RowPrePaint(object sender, DataGridViewRowPrePaintEventArgs e)

    {

        if (e.RowIndex <= grdOrderItems.Rows.Count - 1)

        {

            string StringNow = DateTime.Now.ToString();

            string NowTime = StringNow.Substring(StringNow.IndexOf(" ")+1, StringNow.Length- StringNow.IndexOf(" ")-1);

            string OrderDateTime = grdOrderItems.Rows[e.RowIndex].Cells[0].Value.ToString().Substring(0, grdOrderItems.Rows[e.RowIndex].Cells[0].Value.ToString().IndexOf(" ")+1) + NowTime;

            if ((Convert.ToDateTime(StringNow) - Convert.ToDateTime(OrderDateTime)).Days > 2)

            {

                grdOrderItems.Rows[e.RowIndex].DefaultCellStyle.BackColor = Color.Tomato;

            }

            else

            {

                if ((Convert.ToDateTime(StringNow) - Convert.ToDateTime(OrderDateTime)).Days > 1)

                {

                    grdOrderItems.Rows[e.RowIndex].DefaultCellStyle.BackColor = Color.Yellow;

                }

                else

                {

                    if ((Convert.ToDateTime(StringNow) - Convert.ToDateTime(OrderDateTime)).Days > 0)

                    {

                        grdOrderItems.Rows[e.RowIndex].DefaultCellStyle.BackColor = Color.Tan;

                    }



                }

            }

        }

    }

You probably have a MySqlDataReader leak. When you're done with each Reader object, dispose of it. This is a good way to make that happen; it's resilient against exceptions, and the reader variable goes out of scope at the end of the using.

    using (var reader = command.ExecuteReader()

    {

        while (reader.Read())

        {

           /* your per-row logic here */

        }

    }

You can also do this with Close():

    MySqlDataReader Reader; 

      ...

    Reader = command.ExecuteReader()

    while (Reader.Read())

    {

       /* your per-row logic here */

    }

    Reader.Close();

You are closing and reopening the Connection for each query. That's not necessary. Open it once, use it for both queries, and then close it.

You might consider keeping your Connection open for longer than a single timer tick. (If you have a connection pool, ignore this advice.)

Use the Task Manager to see how much cpu% and memory your program takes up (in the morning and again in the evening). If the memory is growing, you have some kind of leak. If the cpu% is growing, you have some sort of list processing going on where the list gets longer with every tick.

I don't have enough info for a complete answer, except to say this smells like a large object heap issue, where one of the fields you retrieve from the database will occasionally be longer than the 85K LOH threshold.

What I can do, and it might even help, is show you how to greatly simplify and improve the prepaint method like this:

private void grdOrderItems1_RowPrePaint(object sender, DataGridViewRowPrePaintEventArgs e)

{

    if (e.RowIndex > grdOrderItems.Rows.Count - 1) return;

    var row = grdOrderItems.Rows[e.RowIndex];



    TimeSpan timeOfDay = DateTime.Now - DateTime.Today;

    //It *looks like* Cells[0].Value is already a DateTime, but I'm not 100% on this

    // If I'm wrong and it's a string, its worth it to parse just that one value to a DateTime here, and still plug that DateTime value into this code instead of the Cell.

    DateTime OrderDateTime = ((DateTime)row.Cells[0].Value).Date + timeOfDay;



    var days = (DateTime.Now - OrderDateTime).TotalDays;

    if (days > 2)

    {

        row.DefaultCellStyle.BackColor = Color.Tomato;

    }

    else if (days > 1)

    {

        row.DefaultCellStyle.BackColor = Color.Yellow;

    }

    else if (days > 0)

    {

        row.DefaultCellStyle.BackColor = Color.Tan;

    }

}

This reduces nesting and extra blocks, making it a lot easier to understand, and it probably runs in less than half the time because of the reduced back-and-forth between string and DateTime values. The cultural/internationalization issues make converting between DateTime and string in either direction an inherently slow and error-prone operation.