1
\$\begingroup\$

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;
}
\$\endgroup\$
  • \$\begingroup\$ Look into ways to bind a grid to for instance a Dataset or a BindingList<T>. \$\endgroup\$ – BCdotWEB Oct 2 at 15:52
  • \$\begingroup\$ I edited it, The grid already has a datasource, these 2 other columns are added in code. \$\endgroup\$ – Knowledge Oct 2 at 17:33
  • \$\begingroup\$ Then why not add these columns to the datasource? \$\endgroup\$ – BCdotWEB Oct 2 at 18:14
  • \$\begingroup\$ The months I can, but the previous I can't. It needs to be fetched per user and against their search request. So I am calling in Epicor a BAQ to fetch me the data from the appropriate table \$\endgroup\$ – Knowledge Oct 2 at 19:50
  • 1
    \$\begingroup\$ It might be better if you provide the entire class(es) so that we could provide a better review. \$\endgroup\$ – pacmaninbw Oct 5 at 14:56
2
\$\begingroup\$

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();
}
\$\endgroup\$
  • \$\begingroup\$ I have tried adding these but it doesn't make it much faster still in between 7-8 seconds \$\endgroup\$ – Knowledge Oct 7 at 12:11
  • \$\begingroup\$ @Knowledge pity, it would have been so easy.. can you profile the code then? I can't run it \$\endgroup\$ – harold Oct 7 at 12:13
  • \$\begingroup\$ I wouldn't know how to profile this because it is built within Epicor which is a ERP. \$\endgroup\$ – Knowledge Oct 7 at 12:18
  • \$\begingroup\$ I have added the list methods I used however. \$\endgroup\$ – Knowledge Oct 7 at 12:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.