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
MyPage.aspx.cs
UserControl.ascx
UserControl.ascx.cs
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
>
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;
}
}