Problem
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
- Grabs 2 lists of data
- Then adds 2 columns if they do not exist (prevFy, and FiscalMonth)
- Then populates the ultragridview with current datasource plus my added data and columns
- 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;
}
Solution
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();
}