This is a migrated thread and some comments may be shown as answers.

RadGrid in UserControl is slower

4 Answers 150 Views

Grid

This is a migrated thread and some comments may be shown as answers.

Robert asked on 14 Feb 2012, 03:32 PM

In our application, we are using radgrid to display data from many tables from our database. Each table is on a different aspx page with a new radgrid on each page. Every time we want to make a change to the way the grid looks or acts we have to change each grid on each page. I thought it would be a good idea to instead place the grid in a user control so that we could just make one change for all the grids. I had to figure out a way to pass the columns into the user control and I did this using a List<string[]> where each string[] contains certain parameters I needed to create the row and then the row is processed inside the user control. This seems to work fine, however, I am having two problems.

The first is choosing between the NeedDataSource and the ObjectDataSource. When I use the ObjectDataSource my export only exports the page of data that is being displayed. I am able to get around this with some code and a rebind when using the NeedDataSource, however, when using the NeedDataSource, my sums only sum the data from the current page!

The second problem is speed. When using either DataSource, my grid is still slower then the original (without the user control) grid. Paging does not seem to be effective as it take the same time or longer to load each page, even when I included the filter and the startIndexNumber and maximumNumber in my query to only pull a specific amount of rows. It was taking over 10 seconds per page when the original grid took 2. Bothe grids take just over a second when the count is less then 40 or so but as soon as the count is larger the UserControl is considerably slower. I have already enabled RadCompression and various other optimization techniques found in the telerik help. Any other suggestions?

MyPage.aspx

<%@ Register TagPrefix="grid" TagName="dataGrid" Src="~/UserControls/WebUserControl1.ascx" %>

<grid:dataGrid runat="server" ID="GridSpot" Fixed="true" ShowFooter = "true" ExportName="DetailedHourlyData - " GridDataSourceID="detailed_hr_report" />

<asp:ObjectDataSource ID="detailed_hr_report" runat="server" EnablePaging="true" OnSelecting="ods_Selecting"

SelectMethod="GetData" SelectCountMethod="GetCount" TypeName="ObjectDS.GetDetailedHourData" >

<SelectParameters>

<asp:ControlParameter ControlID="UnitDropDown" Name="Unit_id" PropertyName="SelectedValue" DefaultValue="" />

<asp:ControlParameter ControlID="rangeDemoStart" Name="Start_time" PropertyName="Text" Type="DateTime" />

<asp:ControlParameter ControlID="rangeDemoFinish" Name="End_time" PropertyName="Text" Type="DateTime" />

</SelectParameters>

</asp:ObjectDataSource>

MyPage.aspx.cs

protected void Page_PreInit(object sender, EventArgs e)

{

List<string[]> ac = GridSpot.AllColumns;

ac.Add(new string[6] { "DATE_TIME", "Date/Time", "date", "color1", "first", "Sum:" });

ac.Add(new string[4] { "OPER_DATA", "Oper Data Min", "smallInt", "color2" });

ac.Add(new string[4] { "OPER_VALID_NOX", "Valid NOx", "smallInt", "color2" });

ac.Add(new string[4] { "OPER_VALID_SO2", "Vaild SO2", "smallInt", "color2" });

}

UserControl.ascx

<div class="MessageDiv"> //Used to pass back messages for testing

    <asp:Label ID="TestBox" CssClass="FilterText" runat="server" Style="color: Red; font-size: 14px;">

        Filter: OFF

    </asp:Label>

</div>

<telerik:RadAjaxManager ID="RadAjaxManager1" runat="server">

    <ClientEvents OnRequestStart="RequestStart" OnResponseEnd="ResponseEnd" />

    <AjaxSettings>

        <telerik:AjaxSetting AjaxControlID="RadGrid1">

            <UpdatedControls>

                <telerik:AjaxUpdatedControl ControlID="RadGrid1" />

                <telerik:AjaxUpdatedControl ControlID="TestBox" />

            </UpdatedControls>

        </telerik:AjaxSetting>

        <telerik:AjaxSetting AjaxControlID="SubmitButton">

            <UpdatedControls>

                <telerik:AjaxUpdatedControl ControlID="RadGrid1" />

                <telerik:AjaxUpdatedControl ControlID="TestBox" />

            </UpdatedControls>

        </telerik:AjaxSetting>

        <telerik:AjaxSetting AjaxControlID="UnitDropDown">

            <UpdatedControls>

                <telerik:AjaxUpdatedControl ControlID="RadGrid1" />

                <telerik:AjaxUpdatedControl ControlID="TestBox" />

            </UpdatedControls>

        </telerik:AjaxSetting>

        <telerik:AjaxSetting AjaxControlID="rangeDemoToday">

            <UpdatedControls>

                <telerik:AjaxUpdatedControl ControlID="RadGrid1" />

                <telerik:AjaxUpdatedControl ControlID="TestBox" />

            </UpdatedControls>

        </telerik:AjaxSetting>

    </AjaxSettings>

</telerik:RadAjaxManager>

<asp:PlaceHolder ID=

"RadControl" runat="server"

></asp:PlaceHolder>

// +Some javascript

UserControl.ascx.cs

public partial class WebUserControl1 : UserControl

{

public Boolean ShowFooter = false;

public Boolean Fixed = true;

public String ExportName;

public Boolean UseODS = true;

public String GridDataSourceID;

public List<string[]> AllColumns = new List<string[]>();

public RadGrid RadGrid1 = new RadGrid();

public Boolean ExportAll = false;

public Boolean ReCount = false;

public Boolean reAdjust = false;

public SiteMaster Master

{

get

{

return (SiteMaster)Page.Master;

}

protected void Page_Init(object sender, EventArgs e)

{

ReCount = true;

ImageButton ExportToExcel = (ImageButton)Page.Master.FindControl("DownloadExcel");

ExportToExcel.Click += new ImageClickEventHandler(ExportButton_Click);

ImageButton SubmitButton = (ImageButton)Page.Master.FindControl("SubmitButton");

SubmitButton.Click += new ImageClickEventHandler(RadGrid_ReBinder);

ImageButton TodayButton = (ImageButton)Page.Master.FindControl("rangeDemoToday");

TodayButton.Click += new ImageClickEventHandler(RadGrid_ReBinder);

DropDownList UnitDropDown = (DropDownList)Page.Master.FindControl("UnitDropDown");

UnitDropDown.SelectedIndexChanged += new EventHandler(RadGrid_ReBinder);

RadGrid1.ID = "RadGrid1";

//User Settings (Can be different for each grid)

if (UseODS) { RadGrid1.DataSourceID = GridDataSourceID; }

RadGrid1.ShowFooter = false;

RadGrid1.EnableViewState = true;

//Grid Events

RadGrid1.ItemCreated += new GridItemEventHandler(RadGrid1_ItemCreated);

RadGrid1.ItemUpdated += new GridUpdatedEventHandler(RadGrid1_ItemUpdated);

RadGrid1.NeedDataSource += new GridNeedDataSourceEventHandler(RadGrid1_NeedDataSource);

RadGrid1.ItemDataBound +=new GridItemEventHandler(RadGrid1_ItemDataBound);

RadGrid1.ItemCommand += new GridCommandEventHandler(RadGrid1_ItemCommand);

RadGrid1.PreRender += new EventHandler(RadGrid1_PreRender);

RadGrid1.PagerStyle.AlwaysVisible = true;

RadGrid1.PagerStyle.Mode = GridPagerMode.NextPrevAndNumeric;

RadGrid1.PagerStyle.CssClass = "RadPager";

//Grid Set-Up

RadGrid1.CellSpacing = 1;

RadGrid1.PageSize = 336;

RadGrid1.AllowPaging = UseODS;

RadGrid1.AllowCustomPaging = false;

RadGrid1.AllowSorting = true;

RadGrid1.EnableHeaderContextMenu = true;

RadGrid1.Width = Unit.Percentage(99.99);

RadGrid1.GridLines = GridLines.None;

//Export Settings

RadGrid1.ExportSettings.OpenInNewWindow = true;

RadGrid1.ExportSettings.ExportOnlyData = true;

RadGrid1.ExportSettings.Excel.Format = GridExcelExportFormat.ExcelML;

RadGrid1.ExportSettings.Excel.FileExtension = "xls";

//Client Settings

RadGrid1.ClientSettings.AllowColumnsReorder = true;

RadGrid1.ClientSettings.ReorderColumnsOnClient = true;

RadGrid1.ClientSettings.ColumnsReorderMethod = GridClientSettings.GridColumnsReorderMethod.Reorder;

//RadGrid1.ClientSettings.Selecting.AllowRowSelect = true;

RadGrid1.ClientSettings.Scrolling.AllowScroll = true;

RadGrid1.ClientSettings.Scrolling.UseStaticHeaders = true;

//RadGrid1.ClientSettings.Scrolling.SaveScrollPosition = true;

RadGrid1.ClientSettings.Resizing.AllowColumnResize = true;

RadGrid1.ClientSettings.Resizing.ResizeGridOnColumnResize = false;

RadGrid1.ClientSettings.Resizing.ClipCellContentOnResize = false;

RadGrid1.ClientSettings.Resizing.AllowResizeToFit = true;

//Table Set-Up

RadGrid1.MasterTableView.CommandItemDisplay = GridCommandItemDisplay.None;

RadGrid1.MasterTableView.Enabled = true;

RadGrid1.MasterTableView.Width = Unit.Percentage(99.99);

RadGrid1.MasterTableView.AllowFilteringByColumn = true;

RadGrid1.MasterTableView.IsFilterItemExpanded = false;

RadGrid1.MasterTableView.ShowHeadersWhenNoRecords = true;

RadGrid1.MasterTableView.AutoGenerateColumns = false;

RadGrid1.MasterTableView.ItemStyle.BorderStyle = BorderStyle.Solid;

RadGrid1.MasterTableView.ItemStyle.Wrap = false;

RadGrid1.MasterTableView.ItemStyle.HorizontalAlign = HorizontalAlign.Center;

RadGrid1.MasterTableView.AlternatingItemStyle.Wrap = false;

//Styles

RadGrid1.ItemStyle.HorizontalAlign = HorizontalAlign.Center;

//RadGrid1.ItemStyle.Wrap = false;

RadGrid1.AlternatingItemStyle.HorizontalAlign = HorizontalAlign.Center;

RadGrid1.AlternatingItemStyle.Wrap = false;

RadGrid1.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;

RadGrid1.HeaderStyle.Font.Size = FontUnit.XXSmall;

RadGrid1.HeaderStyle.Font.Bold = true;

RadGrid1.HeaderStyle.Wrap = true;

RadGrid1.FooterStyle.HorizontalAlign = HorizontalAlign.Center;

RadGrid1.FooterStyle.Font.Bold = true;

RadGrid1.FooterStyle.ForeColor = System.Drawing.Color.Black;

RadGrid1.FilterItemStyle.HorizontalAlign = HorizontalAlign.Center;

RadGrid1.FilterMenu.EnableImageSprites = false;

RadGrid1.EnableLinqExpressions = false;

RadGrid1.ActiveItemStyle.HorizontalAlign = HorizontalAlign.Center;

//Make columns

foreach (string[] column in AllColumns)

{

MakeColumn(column);

}

if (!Fixed)

{

foreach (GridColumn column in RadGrid1.MasterTableView.Columns)

{

column.HeaderStyle.Reset();

}

RadGrid1.MasterTableView.TableLayout = GridTableLayout.Auto;

RadGrid1.ClientSettings.Scrolling.FrozenColumnsCount = 1;

}

TestBox.Text += "Ctrl_Init: ";

RadControl.Controls.Add(RadGrid1);

}

public void ExportButton_Click(object sender, ImageClickEventArgs e)

{

ExportAll = true;

RadGrid1.Rebind();

RadGrid1.ExportSettings.FileName = ExportName + "Unit ID " + Master.UnitId + " - " + Master.StartTime.Replace("/", "").Replace(":", "") + " - " + Master.EndTime.Replace("/", "").Replace(":", "");

RadGrid1.MasterTableView.ExportToExcel();

}

public void RadGrid_ReBinder(object sender, EventArgs e)

{

ReCount = true;

RadGrid1.Rebind();

}

protected void RadGrid1_ItemUpdated(object source, Telerik.Web.UI.GridUpdatedEventArgs e)

{

//if (e.Exception != null)

//{

// e.KeepInEditMode = true;

// e.ExceptionHandled = true;

//}

}

protected void RadGrid1_ItemCreated(object sender, GridItemEventArgs e)

{

}

protected void RadGrid1_ItemCommand(object sender, GridCommandEventArgs e)

{

reAdjust = (e.CommandName == RadGrid.FilterCommandName);

ReCount = (e.CommandName == RadGrid.FilterCommandName);

}

protected void RadGrid1_ItemDataBound(object sender, GridItemEventArgs e)

{

if (e.Item is GridPagerItem)

{

RadComboBox PageSizeCombo = (RadComboBox)e.Item.FindControl("PageSizeComboBox");

PageSizeCombo.Width = Unit.Pixel(100);

PageSizeCombo.Items[0].Text = "One Day";

PageSizeCombo.Items[0].Value = "24";

PageSizeCombo.Items[1].Text = "One Week";

PageSizeCombo.Items[1].Value = "168";

PageSizeCombo.Items[2].Text = "Two Weeks";

PageSizeCombo.Items[2].Value = "336";

PageSizeCombo.Items[3].Text = "One Month";

PageSizeCombo.Items[3].Value = "744";

PageSizeCombo.FindItemByValue(e.Item.OwnerTableView.PageSize.ToString()).Selected = true;

}

protected void RadGrid1_PreRender(object sender, EventArgs e)

{

if (reAdjust || RadGrid1.MasterTableView.CssClass == "readjust")

{

int i = 0;

foreach (string[] column in AllColumns)

{

string[] colProperties = new string[6] { "", "", "", "", "", "" };

for (int x = 0; x < column.Length; x++)

{

colProperties[x] = column[x];

}

GridColumn thisCol = RadGrid1.MasterTableView.Columns.FindByUniqueName(colProperties[0]);

if (RadGrid1.MasterTableView.CssClass == "readjust")

{

thisCol.CurrentFilterFunction = GridKnownFunction.NoFilter;

thisCol.CurrentFilterValue = String.Empty;

}

if (!String.IsNullOrEmpty(thisCol.CurrentFilterValue.ToString()))

{

thisCol.HeaderStyle.BackColor = System.Drawing.Color.DodgerBlue;

thisCol.HeaderStyle.CssClass = "removeBackImg";

}

else

{

thisCol.HeaderStyle.Reset();

AdjustHeaderStyle(thisCol, colProperties);

}

i++;

}

if (RadGrid1.MasterTableView.CssClass == "readjust")

{

RadGrid1.MasterTableView.FilterExpression = String.Empty;

RadGrid1.MasterTableView.CssClass = String.Empty;

}

protected void RadGrid1_NeedDataSource(object source, GridNeedDataSourceEventArgs e)

{

string filter = RadGrid1.MasterTableView.FilterExpression.ToString().Replace("[", "").Replace("]", "");

TestBox.Text = "Filter: ";

TestBox.Text += (!String.IsNullOrEmpty(filter) ? filter : "OFF"

);

RadGrid1.AllowPaging = !ExportAll;

RadGrid1.MasterTableView.AllowPaging = !ExportAll;

try

{

RadGrid1.DataSource = ThisData(Convert.ToDateTime(Master.StartTime), Convert.ToDateTime(Master.EndTime), int.Parse(Master.UnitId), filter);

}

catch (ApplicationException ex)

{

TestBox.Text = "DB Error: ";

TestBox.Text += ex.Message.ToString();

}

private void MakeColumn(string[] col)

{

//Fill in missing properties with blanks

string[] colProperties = new string[6] {"","","","","",""};

for (int x = 0; x < col.Length; x++)

{

colProperties[x] = col[x];

}

//Use Template Column

if (colProperties[2] == "check")

{

GridTemplateColumn checkColumn = new CheckColumnTemplate();

RadGrid1.MasterTableView.Columns.Add(checkColumn);

checkColumn.DataField = colProperties[0];

checkColumn.UniqueName = colProperties[0];

checkColumn.FilterControlAltText = "Filter " + colProperties[0] + " column";

checkColumn.DataType = typeof(System.Decimal);

checkColumn.HeaderText = colProperties[1];

checkColumn.ItemStyle.CssClass = "color1";

checkColumn.ItemTemplate = new CheckTemplate(colProperties[0]);

AdjustHeaderStyle(checkColumn, colProperties);

}

//Use Normal Column

else

{

GridBoundColumn Column = new GridBoundColumn();

RadGrid1.MasterTableView.Columns.Add(Column);

Column.DataField = colProperties[0];

Column.SortExpression = colProperties[0];

Column.UniqueName = colProperties[0];

Column.FilterControlAltText = "Filter " + colProperties[0] + " column";

Column.HeaderText = colProperties[1];

Column.ItemStyle.Wrap = false;

Column.FilterControlWidth = 50;

switch (colProperties[2])

{

case "date":

Column.DataType = typeof(System.DateTime);

Column.DataFormatString = "{0:MM/dd/yyyy HH:mm}";

break;

case "smallInt":

Column.DataType = typeof(System.Int16);

Column.FilterControlWidth = Unit.Pixel(20);

break;

case "largeInt":

Column.DataType = typeof(System.Int16);

break;

case "smallDec":

Column.DataType = typeof(System.Decimal);

break;

case "largeDec":

Column.DataType = typeof(System.Decimal);

break;

case "text":

Column.DataType = typeof(System.String);

break;

default:

Column.DataType = typeof(System.Decimal);

break;

}

if (!String.IsNullOrEmpty(colProperties[3]))

{

Column.ItemStyle.CssClass = colProperties[3];

}

else

{

Column.ItemStyle.CssClass = "color1";

}

if (colProperties[4] == "sum")

{

Column.Aggregate = GridAggregateFunction.Sum;

Column.FooterText = "";

Column.FooterAggregateFormatString = "{0:F1}";

Column.FooterStyle.CssClass = "footer";

}

else if (colProperties[4] == "avg")

{

Column.Aggregate = GridAggregateFunction.Avg;

Column.FooterText = "";

Column.FooterAggregateFormatString = "{0:F1}";

Column.FooterStyle.CssClass = "footer";

}

else if (colProperties[4] == "first")

{

Column.FooterText = colProperties[5];

Column.FooterStyle.CssClass = "footer";

Column.FilterTemplate = new ClearAllFilterTemplate();

}

if (!String.IsNullOrEmpty(colProperties[5]))

{

Column.HeaderTooltip = colProperties[5];

}

AdjustHeaderStyle(Column, colProperties);

}

private void AdjustHeaderStyle(GridColumn col, string[] colProperties)

{

if (colProperties[2] == "check")

{

GridTemplateColumn checkColumn = (GridTemplateColumn)col;

checkColumn.HeaderStyle.CssClass = "check";

checkColumn.HeaderStyle.Width = Unit.Pixel(24);

}

else

{

GridBoundColumn Column = (GridBoundColumn)col;

switch (colProperties[2])

{

case "date":

Column.HeaderStyle.CssClass = "date";

if (Fixed)

{

Column.HeaderStyle.Width = Unit.Pixel(120);

}

break;

case "smallInt":

Column.HeaderStyle.CssClass = "smallInt";

if (Fixed)

{

Column.HeaderStyle.Width = Unit.Pixel(30);

}

break;

case "largeInt":

if (Fixed)

{

Column.HeaderStyle.Width = Unit.Pixel(50);

}

break;

case "smallDec":

if (Fixed)

{

Column.HeaderStyle.Width = Unit.Pixel(50);

}

break;

case "largeDec":

if (Fixed)

{

Column.HeaderStyle.Width = Unit.Pixel(70);

}

break;

case "text":

if (Fixed)

{

Column.HeaderStyle.Width = Unit.Pixel(150);

}

break;

default:

if (Fixed)

{

Column.HeaderStyle.Width = Unit.Pixel(int.Parse(colProperties[2]));

}

break;

}

private class ClearAllFilterTemplate : ITemplate

{

protected Button btn;

protected GridFilteringItem container;

protected GridTableView RadGrid1;

public void InstantiateIn(System.Web.UI.Control container)

{

btn = new Button();

btn.ID = "clearFilter_btn";

btn.Text = "Clear Filter";

btn.UseSubmitBehavior = false;

btn.Click += new EventHandler(clearFilter_Click);

container.Controls.Add(btn);

}

void clearFilter_Click(object sender, EventArgs e)

{

container = (GridFilteringItem)btn.NamingContainer;

RadGrid1 = container.OwnerTableView;

//RadGrid1.FilterExpression = "readjust";

RadGrid1.FilterExpression = String.Empty;

foreach (GridColumn column in RadGrid1.Columns)

{

column.CurrentFilterFunction = GridKnownFunction.NoFilter;

column.CurrentFilterValue = String.Empty;

}

RadGrid1.CssClass = "readjust";

RadGrid1.Rebind();

}

private class CheckColumnTemplate : GridTemplateColumn

{

protected DropDownList DDl;

protected override void SetupFilterControls(TableCell cell)

{

base.SetupFilterControls(cell);

cell.Controls.RemoveAt(0);

DDl = new DropDownList();

DDl.ID = "ddl_" + this.DataField;

ListItem blankItem = new ListItem("", "");

ListItem trueItem = new ListItem("True", "1");

ListItem falseItem = new ListItem("False", "0");

DDl.Items.Add(blankItem);

DDl.Items.Add(trueItem);

DDl.Items.Add(falseItem);

DDl.AutoPostBack = true;

DDl.SelectedIndexChanged += new EventHandler(FilterCombo_SelectedIndexChanged);

cell.Controls.AddAt(0, DDl);

cell.Controls.RemoveAt(1);

DDl.DataTextField = this.DataField;

DDl.DataValueField = this.DataField;

}

protected void FilterCombo_SelectedIndexChanged(object sender, EventArgs e)

{

DropDownList DDl = (DropDownList)sender;

string filterType = DDl.SelectedValue == "" ? "NoFilter" : "EqualTo";

((GridFilteringItem)(DDl.NamingContainer)).FireCommandEvent("Filter", new Pair(filterType, DDl.NamingContainer.ToString()));

}

protected override void SetCurrentFilterValueToControl(TableCell cell)

{

((DropDownList)cell.Controls[0]).SelectedValue = this.CurrentFilterValue;

}

protected override string GetCurrentFilterValueFromControl(TableCell cell)

{

string currentValue = ((DropDownList)cell.Controls[0]).SelectedValue;

this.CurrentFilterFunction = currentValue == "" ? GridKnownFunction.NoFilter : GridKnownFunction.EqualTo;

return currentValue;

}

private class CheckTemplate : ITemplate

{

protected Image img;

private string colname;

public CheckTemplate(string cName)

{

colname = cName;

}

public void InstantiateIn(System.Web.UI.Control container)

{

img = new Image();

img.ID = colname + "_img";

img.ImageAlign = ImageAlign.AbsMiddle;

img.DataBinding += new EventHandler(boolValue_DataBinding);

container.Controls.Add(img);

}

void boolValue_DataBinding(object sender, EventArgs e)

{

Image img = (Image)sender;

GridDataItem container = (GridDataItem)img.NamingContainer;

if ((((DataRowView)container.DataItem)[colname].ToString()) != "0")

{

img.ImageUrl = "~/images/CemsLive/radio_checked.png";

}

else

{

img.ImageUrl = "~/images/CemsLive/radio_unchecked.png";

}

public DataSet ThisData(DateTime Start_time, DateTime End_time, Int32 Unit_id, String Filter)

{

int dataCount;

string Connection = DBConnect.Connect();

OracleConnection oCon = new OracleConnection(Connection);

DataSet ds = new DataSet();

string CountQuery = "SELECT COUNT(*) " +

"FROM FLINK.CEMS_HR_SOURCE hrdata " +

"WHERE (UNIT_ID = :UNIT_ID) " +

"AND (DATE_TIME >= :DATE_TIME) " +

"AND (DATE_TIME <= :DATE_TIME2) ";

if (!String.IsNullOrEmpty(Filter))

{

CountQuery += "AND " + Filter;

}

OracleCommand cCmd = new OracleCommand(CountQuery, oCon);

cCmd.CommandType = System.Data.CommandType.Text;

OracleParameter count_Unit_id = new OracleParameter("UNITID", OracleDbType.Int32);

count_Unit_id.Direction = ParameterDirection.Input;

count_Unit_id.Value = Unit_id;

cCmd.Parameters.Add(count_Unit_id);

OracleParameter count_Start_time = new OracleParameter("DATE_TIME", OracleDbType.Date);

count_Start_time.Direction = ParameterDirection.Input;

count_Start_time.Value = Start_time;

cCmd.Parameters.Add(count_Start_time);

OracleParameter count_End_time = new OracleParameter("DATE_TIME2", OracleDbType.Date);

count_End_time.Direction = ParameterDirection.Input;

count_End_time.Value = End_time;

cCmd.Parameters.Add(count_End_time);

try

{

oCon.Open();

if (ReCount)

{

RadGrid1.CurrentPageIndex = 0;

dataCount = Convert.ToInt32(cCmd.ExecuteScalar());

RadGrid1.VirtualItemCount = dataCount;

RadGrid1.MasterTableView.VirtualItemCount = dataCount;

}

else

{

try

{

dataCount = int.Parse(Session["RowCount"].ToString());

}

catch

{

RadGrid1.CurrentPageIndex = 0;

dataCount = Convert.ToInt32(cCmd.ExecuteScalar());

RadGrid1.VirtualItemCount = dataCount;

RadGrid1.MasterTableView.VirtualItemCount = dataCount;

}

Session["RowCount"] = dataCount;

int startRowIndex = (ExportAll ? 0 : RadGrid1.CurrentPageIndex * RadGrid1.PageSize);

int maximumRows = (ExportAll ? dataCount : RadGrid1.PageSize);

/*Move selected bottle to current location */

string NewQuery = "SELECT DATE_TIME, OPER_DATA, OPER_VALID_NOX, OPER_VALID_SO2, OPER_VALID_FLOW_STK, VALID_NOX_15MIN, " +

"FLOW_GAS_TIME, FLOW_AUX_TIME, FLOW_OIL_TIME, LOAD_RANGE, CALIB_IN_PROG, MAINT, ANZR_OUT_CTL, " +

"COMM_ALARM, ROUND(MWH_GROSS,2) AS MWH_GROSS, ROUND(MWH_NET,2) AS MWH_NET, ROUND(FLOW_GAS,2) AS FLOW_GAS, " +

"ROUND(FLOW_AUX,2) AS FLOW_AUX, ROUND(FLOW_OIL,2) AS FLOW_OIL, ROUND(O2_RAW,2) AS O2_RAW, " +

"ROUND(NOX_RAW,2) AS NOX_RAW, ROUND(NOX_RAW_BIAS_ADJ,2) AS NOX_RAW_BIAS_ADJ, ROUND(NOX_COR,2) AS NOX_COR, " +

"ROUND(NH3_RAW,2) AS NH3_RAW, ROUND(NH3_COR,2) AS NH3_COR, ROUND(CO_RAW,2) AS CO_RAW, ROUND(CO_COR,2) AS CO_COR, " +

"ROUND(FLOW_STACK,2) AS FLOW_STACK, ROUND(FLOW_STACK_BIAS_ADJ,2) AS FLOW_STACK_BIAS_ADJ, " +

"ROUND(HEAT_TOTAL,2) AS HEAT_TOTAL, ROUND(HEAT_GAS,2) AS HEAT_GAS, ROUND(HEAT_AUX,2) AS HEAT_AUX, " +

"ROUND(HEAT_OIL,2) AS HEAT_OIL, ROUND(F_FACTOR,2) AS F_FACTOR, ROUND(NOX_MASS_HR,2) AS NOX_MASS_HR, " +

"ROUND(NOX_MASS_HR_BIAS_ADJ,2) AS NOX_MASS_HR_BIAS_ADJ, ROUND(NOX_MASS_BTU,2) AS NOX_MASS_BTU, " +

"ROUND(NOX_MASS_BTU_BIAS_ADJ,2) AS NOX_MASS_BTU_BIAS_ADJ, ROUND(SO2_MASS_HR,2) AS SO2_MASS_HR, " +

"ROUND(SO2_MASS_HR_BIAS_ADJ,2) AS SO2_MASS_HR_BIAS_ADJ, ROUND(SO2_MASS_HR_GAS,2) AS SO2_MASS_HR_GAS, " +

"ROUND(SO2_MASS_HR_AUX,2) AS SO2_MASS_HR_AUX, ROUND(SO2_MASS_HR_OIL,2) AS SO2_MASS_HR_OIL, " +

"ROUND(CO_MASS_HR_BIAS_ADJ,2) AS CO_MASS_HR_BIAS_ADJ, ROUND(CO2_MASS_HR,2) AS CO2_MASS_HR, " +

"ROUND(HEAT_RATE,2) AS HEAT_RATE, AVAIL_NOX, METHOD_NOX, METHOD_CO2, METHOD_GAS, METHOD_AUX, METHOD_OIL " +

"FROM ( " +

"SELECT ROW_NUMBER() OVER (ORDER BY DATE_TIME) AS rn, hrdata.* " +

"FROM FLINK.CEMS_HR_SOURCE hrdata " +

"WHERE (UNIT_ID = :UNIT_ID) " +

"AND (DATE_TIME >= :DATE_TIME) " +

"AND (DATE_TIME <= :DATE_TIME2) ";

if (!String.IsNullOrEmpty(Filter))

{

NewQuery += "AND " + Filter;

}

//Insert filter here

NewQuery += " ) WHERE rn BETWEEN :startRowIndex + 1 AND :startRowIndex + :maximumRows " +

"ORDER BY DATE_TIME";

OracleCommand nCmd = new OracleCommand(NewQuery, oCon);

nCmd.CommandType = System.Data.CommandType.Text;

nCmd.BindByName = true;

OracleParameter param_Unit_id = new OracleParameter("UNITID", OracleDbType.Int32);

param_Unit_id.Direction = ParameterDirection.Input;

param_Unit_id.Value = Unit_id;

nCmd.Parameters.Add(param_Unit_id);

OracleParameter param_Start_time = new OracleParameter("DATE_TIME", OracleDbType.Date);

param_Start_time.Direction = ParameterDirection.Input;

param_Start_time.Value = Start_time;

nCmd.Parameters.Add(param_Start_time);

OracleParameter param_End_time = new OracleParameter("DATE_TIME2", OracleDbType.Date);

param_End_time.Direction = ParameterDirection.Input;

param_End_time.Value = End_time;

nCmd.Parameters.Add(param_End_time);

OracleParameter param_StartRowIndex = new OracleParameter("startRowIndex", OracleDbType.Int32);

param_StartRowIndex.Direction = ParameterDirection.Input;

param_StartRowIndex.Value = startRowIndex;

nCmd.Parameters.Add(param_StartRowIndex);

OracleParameter param_maximumRows = new OracleParameter("maximumRows", OracleDbType.Int32);

param_maximumRows.Direction = ParameterDirection.Input;

param_maximumRows.Value = maximumRows;

nCmd.Parameters.Add(param_maximumRows);

OracleDataAdapter da = new OracleDataAdapter(nCmd);

da.Fill(ds);

}

catch (Exception ex)

{

throw ex;

}

finally

{

//clean up the connection object

oCon.Close();

oCon.Dispose();

}

return ds;

}

4 Answers, 1 is accepted

answered on 17 Feb 2012, 04:40 PM

Hello Robert,

For the first issue, try using IgnorePaging when exporting to get the full data exported:

<ExportSettings IgnorePaging="true">

As for performance, I noticed you have a PageSize of 336 for the grid which is a very large number of items to be viewed simultaneously - many controls are loaded, a lot of client script runs and the browser is also quite loaded by the large output. My advice is to reduce the page size to at most 100. Also, you can consider using CustomPaging if the datasource itself is very large:
http://demos.telerik.com/aspnet-ajax/grid/examples/programming/custompaging/defaultcs.aspx

Let us know how this goes for you.

Regards,
Tsvetina
the Telerik team

Sharpen your .NET Ninja skills! Attend Q1 webinar week and get a chance to win a license! Book your seat now >>

answered on 19 Feb 2012, 01:18 AM

Using the NeedDataSource with custom paging and lowering my page size down by half I was able to speed up the page enough. With the NeedDataSource I am able to export the whole query using:

public void ExportButton_Click(object sender, ImageClickEventArgs e)

{

ExportAll = true;

RadGrid1.Rebind();

RadGrid1.MasterTableView.ExportToExcel();

}

I was also able to get around the limitation of the aggregates only summing what was on the page by summing in the same query that does the count, since it only needs to be refreshed every time the count changes, and storing the results in a data table in a session state and then retrieving them on the custom aggregate function.

The only problem that I am now having is with the Export to Excel. The export button is in the master page. I trigger the export in the user control like so:

protected void Page_Init(object sender, EventArgs e)

{

ImageButton ExportToExcel = (ImageButton)Page.Master.FindControl("DownloadExcel");

ExportToExcel.Click += new ImageClickEventHandler(ExportButton_Click);

.......(More code)

}

The grid exports and I was able to customize the output style of the excel file, however, after the grid exports the rest of the grid stops working. When I change query parameters, in my case start and end time, the grid stops rebinding. The AJAX onRequestStart javascript still triggers but the grid does not change. I tried adding this in the onRequestStart and it did not make a difference:

if (args.get_eventTarget().indexOf("DownloadExcel") != -1) {

args.set_enableAjax(false);

}

else {

args.set_enableAjax(true);

}

The grid stays unresponsive until I refresh the page and if I click the export again I get this error:

The webpage at http://localhost:61219/pages/data/HourDetailed.aspx might be temporarily down or it may have moved permanently to a new web address.

Error 103 (net::ERR_CONNECTION_ABORTED): Unknown error.

answered on 22 Feb 2012, 04:38 PM

Hello Robert,

Thank you for getting back to us.

I am still not sure if the problem is caused by AJAX, so can you please check whether the same behavior is observed if you disable AJAX on the page?
If this is so, you can then debug the value of args.get_eventTarget() when the button is clicked to see why the body of the if-statement is not executed.
Also, if the export executes correctly, the state of the grid in the page should not be changed, since the response from the server is actually the exported file and no part of it can be aimed to refresh the rendered page content.

If the problem is not related to AJAX and you do not notice more details about the problem, would it be possible that you send us a live url or try to isolate the problemmatic part of your scenario into a runnable sample. Another option is to provide a set of steps for recreating the scenario - how and where to place the export button and what is the code that triggers the export, I will then try to recreate a similar scenario and see how it goes on my side.

Thank you in advance for your cooperation.

All the best,
Tsvetina
the Telerik team

If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.