Shorten the time it is taking to fill data in an ultragrid

Question

lastbudget and lastactual are both Lists

These lists populate 2 columns in an infragistics ultragrid

• PrevFY
• Actual_c

So the grid has defined datasource already. These 2 columns I added programmaticly onto the grid which has a datasource.

(dgvBudget is the name of the ultragrid)

int rowIndex = dgvBudget.Rows.Count;


if (half == "Second Half Budget")
{
    for (int i = 6; i < rowIndex; i++)
    {
        var row = dgvBudget.Rows[i];
        row.Cells["PrevFY"].Value = lastbudget[i];
        row.Cells["Actual_c"].Value = lastactual[i];
    }
    for (int i = 0; i < 6; i++)
    {
        var row = dgvBudget.Rows[i];
        row.Cells["PrevFY"].Value = lastactual[i];
        row.Cells["Actual_c"].Value = 0;
    }
}
else
{
    for (int i = 0; i < rowIndex; i++)
    {
        var row = dgvBudget.Rows[i];
        row.Cells["PrevFY"].Value = lastbudget[i];
        row.Cells["Actual_c"].Value = lastactual[i];
    }
}

My goal is to create a new datasource and import the binded data to the the datagridview with the 3 new columns I have created to cut down on MS when executing.

At the moment it is taking 8-10 seconds to execute

private void bringPreviousData()
{
    Stopwatch stopwatch = Stopwatch.StartNew();
    List<string> lastactual = new List<string>();
    List<string> lastbudget = new List<string>();
    Control cmbBudgetCode = csm.GetNativeControlReference("17dd127e-7b02-48e9-a7bb-e98164aea713");
    EpiDataView bhView = (EpiDataView)(oTrans.EpiDataViews["GLBudgetHd"]);
    if (!dgvBudget.DisplayLayout.Bands[0].Columns.Exists("PrevFY"))
    {
        dgvBudget.DisplayLayout.Bands[0].Columns.Add("PrevFY", "Previous FY Budgeted");
        dgvBudget.DisplayLayout.Bands[0].Columns["PrevFY"].Style = Infragistics.Win.UltraWinGrid.ColumnStyle.Currency;
    }

    if (!dgvBudget.DisplayLayout.Bands[0].Columns.Exists("FiscalMonth"))
    {
        dgvBudget.DisplayLayout.Bands[0].Columns.Add("FiscalMonth", "Month");
        SetColumnReadOnly("FiscalMonth", true);
    }
    else
    {
        string[] monthNames = { "April", "May", "June", "July", "August", "September", "October", "November", "December", "January", "February", "March" };
        for (int i = 0; i < monthNames.Length; i++)
        {
            dgvBudget.Rows[i].Cells["FiscalMonth"].Value = monthNames[i];
        }
    }

    string half = cmbBudgetCode.Text;
    lastactual = GetLastActual(half);
    lastbudget = GetLastBudget(half);
    int rowIndex = dgvBudget.Rows.Count;
    if (half == "Second Half Budget")
    {
        for (int i = 6; i < rowIndex; i++)
        {
            var row = dgvBudget.Rows[i];
            row.Cells["PrevFY"].Value = lastbudget[i];
            row.Cells["Actual_c"].Value = lastactual[i];
        }
        for (int i = 0; i < 6; i++)
        {
            var row = dgvBudget.Rows[i];
            row.Cells["PrevFY"].Value = lastactual[i];
            row.Cells["Actual_c"].Value = 0;
        }
    }
    else
    {
        for (int i = 0; i < rowIndex; i++)
        {
            var row = dgvBudget.Rows[i];
            row.Cells["PrevFY"].Value = lastbudget[i];
            row.Cells["Actual_c"].Value = lastactual[i];
        }
    }

    decimal total = 0m;
    foreach (UltraGridRow row in dgvBudget.Rows)
    {
        total += Convert.ToDecimal(row.Cells["PrevFY"].Value);
    }
    if (Config.typeofAccount != "Overtime")
    {
        if (GetAcctType(bhView.CurrentDataRow["SegValue1"].ToString()))
        {
            nbrPrevStatTotal.Value = total;
        }
        else
        {
            nbrPrevTotal.Value = total;
        }
    }
    else
    {
        nbrPrevTotal.Value = total;
    }
    stopwatch.Stop();
    MessageBox.Show(stopwatch.ElapsedMilliseconds.ToString());
}

So at the moment what this code is doing is

1. Grabs 2 lists of data
2. Then adds 2 columns if they do not exist (prevFy, and FiscalMonth)
3. Then populates the ultragridview with current datasource plus my added data and columns
4. Then Sums the values in the cells to show in a text box

Could I please have some help on how I can speed this up because right now it works it does, it's just so sloooooooooooow thank you!

EDIT**

The class is 1000+ lines so I can include the methods that get the lists. I've also added the begin update and ending update aswell as synchronization.

private List<string> GetLastBudget(string half)
{
    // string list is returned
    List<string> lastbudget = new List<string>();

    // BAQ gets the Budgeted ammount for last year
    DynamicQueryAdapter dqa = new DynamicQueryAdapter(oTrans);
    dqa.BOConnect();
    string baq = "ko_test";
    QueryExecutionDataSet qeds = dqa.GetQueryExecutionParametersByID(baq);

    EpiDataView bhView = (EpiDataView)(oTrans.EpiDataViews["GLBudgetHd"]);
    string fy = bhView.CurrentDataRow["FiscalYear"].ToString();
    string balacct = bhView.CurrentDataRow["BalanceAcct"].ToString();
    string acct = bhView.CurrentDataRow["SegValue1"].ToString();
    string budgetcode = "Main";

    // If it's first half, get data for last year.  
    // Second half still needs this year's "Main" Budget data
    if (half == "Main")
    {
        int fyint = (Convert.ToInt32(fy) - 1);
        fy = fyint.ToString();
    }

    qeds.ExecutionParameter.Clear();
    qeds.ExecutionParameter.AddExecutionParameterRow("FiscalYear", fy, "int", false, Guid.NewGuid(), "A");
    qeds.ExecutionParameter.AddExecutionParameterRow("BalanceAcct", balacct, "nvarchar", false, Guid.NewGuid(), "A");
    qeds.ExecutionParameter.AddExecutionParameterRow("BudgetCode", budgetcode, "nvarchar", false, Guid.NewGuid(), "A");

    dqa.ExecuteByID(baq, qeds);

    LastBudgetData = dqa.QueryResults.Tables["Results"].AsEnumerable();


    if (dqa.QueryResults.Tables["Results"].Rows.Count > 0)
    {
        Config.stat = GetAcctType(acct);
        // Expense
        if (Config.stat == false)
        {
            foreach (var item in LastBudgetData)
            {
                lastbudget.Add(item["GLBudgetDtl_BudgetAmt"].ToString());
            }

        }
        // Statistical
        else
        {
            foreach (var item in LastBudgetData)
            {
                lastbudget.Add(item["GLBudgetDtl_BudgetStatAmt"].ToString());
            }
        }

    }
    // If there are no results from the query set to 0s
    else
    {
        for (int i = 0; i < 12; i++)
        {
            lastbudget.Add("0");
        }
    }
    return lastbudget;
}

And here is the list to get the actual

private List<string> GetLastActual(string half)
{
    // string list is returned
    List<string> lastactual = new List<string>();

    EpiDataView bhView = (EpiDataView)(oTrans.EpiDataViews["GLBudgetHd"]);
    string fy = bhView.CurrentDataRow["FiscalYear"].ToString();
    string balacct = bhView.CurrentDataRow["BalanceAcct"].ToString();
    string acct = bhView.CurrentDataRow["SegValue1"].ToString();
    string budgetcode = "Main";

    // If it's first half, get data for last year.  
    // Second half still needs this year's "Main" Budget data
    if (half == "Main")
    {
        int fyint = (Convert.ToInt32(fy) - 1);
        fy = fyint.ToString();
    }

    // GLTracker gets the actual spent for the year
    GLTrackerAdapter gladapter = new GLTrackerAdapter(oTrans);
    gladapter.BOConnect();

    int year = Convert.ToInt32(fy);
    int startIndex = 0;
    decimal balance;
    DataSet glds = gladapter.GetRows("MainBook", "D", false, balacct, year, "", budgetcode, out balance);

    if (glds.Tables["GLTracker"].Rows.Count > 0)
    {
        bool stat = GetAcctType(acct);
        // Expense
        if (stat == false)
        {
            SetColumnReadOnly("PrevFY", true);
            for (int i = 0; i < 12; i++)
            {
                DataRow row = glds.Tables["GLTracker"].Rows[i];
                decimal cDebit = decimal.Parse(row["DebitAmt"].ToString());
                decimal cCredit = decimal.Parse(row["CreditAmt"].ToString());
                decimal cUsed = cDebit - cCredit;

                lastactual.Add(cUsed.ToString());
            }
        }
        // Statistical
        else
        {
            for (int i = 0; i < 12; i++)
            {
                DataRow row = glds.Tables["GLTracker"].Rows[i];
                decimal cDebit = decimal.Parse(row["DebitStatAmt"].ToString());

                lastactual.Add(cDebit.ToString());
            }
        }
    }
    // If there are no results from the query set to 0s
    else
    {
        for (int i = 0; i < 12; i++)
        {
            lastactual.Add("0");
        }
    }
    return lastactual;
}

Answer 1

As is typical of "bulk controls", this grid has the methods:

• BeginUpdate, stops the control from refreshing on modifications.
• EndUpdate, re-enables refreshing on modification.

Surrounding a batch of modifications with those methods makes it faster and prevents flickering of the control while the modifications are being performed. It is commonly recommended to use try/finally to prevent the control staying frozen in case of a mishap:

try
{
    dgvBudget.BeginUpdate();

    // do updates
}
finally
{
    dgvBudget.EndUpdate();
}