SelfHierarchy Rad with ServerOnDemand for HierarchyLoadMode

[Robert Clavelle]

  Hi,
  I'm working with ASP.NET 2.0, Windows [2000,XP,Server2003], Internet Explorer 6.0.2900.5512.xpsp_sp3_gdr.080814-1236, Telerik RadControls for ASP.NET AJAX Q3 2005 with Visual Studio 2005.  I have a huge self-referencing Oracle table, and I want a RadGrid with both SelfHierarchySettings and HierarchyLoadMode="ServerOnDemand".
  I can get the behavior I'd like with HierarchyLoadMode="Client" and the datasource and radgrid below.  And then when you click on the item whose ccattrseq=1, it expands and shows all its children (whose pid=1).  But the table, even too a depth of 2 is too big too load all at once, and what I really want is to have HierarchyLoadMode="ServerOnDemand".  I've tried and tried, but all I get is no children when I click on expand.
  I've read these articles, but I don't think they're doing what I need:
http://www.telerik.com/community/code-library/aspnet-ajax/grid/separate-scrolls-for-inner-tables-in-hierarchical-grid.aspx
http://www.telerik.com/community/code-library/aspnet-ajax/grid/select-items-in-hierarchy-depending-on-selection-in-inner-levels.aspx
 The closest article I could find doing what I need was here (but it isn't ASP.NET AJAX forums).  The poster seemed to be satisfied that he'd found, but I can't re-create it with the details provided:
http://www.telerik.com/community/forums/aspnet/grid/self-hierarchy-problems.aspx#719305

      <asp:SqlDataSource ID="OracleTest" runat="server" ConnectionString="Data Source=testville;Persist Security Info=True;User ID=testuser;Password=testpassword;Unicode=True"
          ProviderName="System.Data.OracleClient" SelectCommand='SELECT "CCATTRSEQ","SECTION","VALSTR", "PID" FROM "CCATTRIBUTES" WHERE "PID"=1 OR "PID"=-1'>
      </asp:SqlDataSource>

    <telerik:RadGrid ID="TopLevelNodes" Skin="Outlook" runat="server" PageSize="5"
      AllowSorting="True" BackColor="#E0E0E0" DataSourceID="OracleTest"
      OnColumnCreated="TopLevelNodes_ColumnCreated" OnItemCreated="TopLevelNodes_ItemDataBound"
      OnNeedDataSource="TopLevelNodes_NeedDataSource" AutoGenerateColumns="False"
      EnableAJAX="True" GridLines="None" Width="321px" CellPadding="1" Height="200px">
        <ClientSettings EnablePostBackOnRowClick="True">
            <Selecting AllowRowSelect="True" EnableDragToSelectRows="False" />
            <Scrolling AllowScroll="True" UseStaticHeaders="True" />
        </ClientSettings>
        <MasterTableView
          HierarchyLoadMode="Client" DataKeyNames="CCATTRSEQ,PID,SECTION"
          DataSourceID="OracleTest">
          <SelfHierarchySettings ParentKeyName="PID" KeyName="CCATTRSEQ" />
          <SortExpressions>
            <telerik:GridSortExpression FieldName="VALSTR" SortOrder="Descending" />            
          </SortExpressions>
          <Columns>
            <telerik:GridClientSelectColumn  UniqueName="column">
              <HeaderStyle Width="27px" />
            </telerik:GridClientSelectColumn>
            <telerik:GridExpandColumn Visible="True" >
              <HeaderStyle Width="27px" />
            </telerik:GridExpandColumn>                                                           
            <telerik:GridBoundColumn DataField="VALSTR" HeaderText="Section" SortExpression="VALSTR"
              UniqueName="VALSTR">
              <ItemStyle Wrap="True" />
                <HeaderStyle Width="70px" />
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="CCATTRSEQ" HeaderText="CCATTRSEQ" SortExpression="CCATTRSEQ"
              UniqueName="CCATTRSEQ" Display="False">
              <ItemStyle Wrap="False" />
            </telerik:GridBoundColumn>            
            <telerik:GridBoundColumn DataField="SECTION" HeaderText="Section Number" SortExpression="SECTION"
              UniqueName="SECTION" Display="False">
              <ItemStyle Wrap="False" />
            </telerik:GridBoundColumn>                        
            <telerik:GridBoundColumn DataField="PID" HeaderText="PID" SortExpression="PID"
              UniqueName="PID" Display="False">
              <ItemStyle Wrap="False" />
            </telerik:GridBoundColumn>                        
          </Columns>
            <ExpandCollapseColumn UniqueName="ExpandColumn1">
                <HeaderStyle Width="20px" />
            </ExpandCollapseColumn>
            <RowIndicatorColumn>
                <HeaderStyle Width="20px" />
            </RowIndicatorColumn>
        </MasterTableView>
        <SelectedItemStyle BackColor="#C00000" />
        <AlternatingItemStyle BackColor="Silver" />
        <HeaderContextMenu EnableTheming="True" Skin="Outlook">
            <CollapseAnimation Duration="200" Type="OutQuint" />
        </HeaderContextMenu>
        <FilterMenu EnableTheming="True" Skin="Outlook">
            <CollapseAnimation Duration="200" Type="OutQuint" />
        </FilterMenu>
    </telerik:RadGrid>

 

[Robert Clavelle]

  I've tried these two approaches for instance each results in all the top-level nodes being childless (and I have to put in a condition in the where clause of OracleTest to make it only PID=-1 [top-level nodes only]).  The code for the code-behind pages are all similar to examples given for this type of problem (i.e. I have non-empty PreRender, PreRenderComplete, CreateExpandCollapseButton).

    <telerik:RadGrid ID="TopLevelNodes" runat="server"  
      AllowSorting="True" BackColor="#E0E0E0" DataSourceID="OracleTest"
      OnItemCreated="TopLevelNodes_ItemDataBound"
      OnNeedDataSource="TopLevelNodes_NeedDataSource"
      AutoGenerateColumns="False" GridLines="None" Height="200px">
      <ClientSettings EnablePostBackOnRowClick="true"  Selecting-AllowRowSelect="true"  Scrolling-AllowScroll="true" AllowExpandCollapse="true" />
        <MasterTableView HierarchyDefaultExpanded="False"
          EnableNoRecordsTemplate="true"
          DataKeyNames="CCATTRSEQ,PID,SECTION"
          HierarchyLoadMode="ServerOnDemand"  
          DataSourceID="OracleTest" Width="100%" AllowPaging="false">          
          <SelfHierarchySettings ParentKeyName="PID" KeyName="CCATTRSEQ" MaximumDepth="1"/>
           <Columns>
            <telerik:GridClientSelectColumn  UniqueName="column1" ButtonType="PushButton" ConfirmDialogType="RadWindow">
              <HeaderStyle Width="27px" />
                <ItemStyle VerticalAlign="Top" />
            </telerik:GridClientSelectColumn>
            <telerik:GridExpandColumn  Visible="True" UniqueName="ExpandColumnSec" >
              <HeaderStyle Width="27px" />
            </telerik:GridExpandColumn>                                                           
            <telerik:GridBoundColumn DataField="VALSTR" HeaderText="Section"  SortExpression="VALSTR"
              UniqueName="VALSTR">
              <ItemStyle Wrap="True" />
                <HeaderStyle Width="190px" />
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="CCATTRSEQ" HeaderText="CCATTRSEQ" SortExpression="CCATTRSEQ"
              UniqueName="CCATTRSEQ" Display="False">
              <ItemStyle Wrap="False" />
            </telerik:GridBoundColumn>            
            <telerik:GridBoundColumn DataField="SECTION" HeaderText="Section Number" SortExpression="SECTION"
              UniqueName="SECTION" Display="False">
              <ItemStyle Wrap="False" />
            </telerik:GridBoundColumn>                        
            <telerik:GridBoundColumn DataField="PID" HeaderText="PID" SortExpression="PID"
              UniqueName="PID" Display="False">
              <ItemStyle Wrap="False" />
            </telerik:GridBoundColumn>                        
          </Columns>
        </MasterTableView>
        <SelectedItemStyle BackColor="#C00000" />
        <AlternatingItemStyle BackColor="Silver" />
        <HeaderContextMenu EnableTheming="True" Skin="Outlook">
            <CollapseAnimation Duration="200" Type="OutQuint" />
        </HeaderContextMenu>
    </telerik:RadGrid>

      This doesn't work either:

    <telerik:RadGrid ID="TopLevelNodes" Skin="Outlook" runat="server" PageSize="5"
      AllowSorting="True" BackColor="#E0E0E0"
      DataSourceID="OracleTest"
      AutoGenerateColumns="False"
      GridLines="None" CellPadding="1" Height="200px" >
        <ClientSettings EnablePostBackOnRowClick="True">
            <Selecting AllowRowSelect="True" EnableDragToSelectRows="False" />
            <Scrolling AllowScroll="True" UseStaticHeaders="True" />
        </ClientSettings>
        <MasterTableView DataKeyNames="CCATTRSEQ,PID,SECTION"
          HierarchyLoadMode="ServerOnDemand" DataSourceID="OracleTest">                   
            <DetailTables>
              <telerik:GridTableView DataKeyNames="CCATTRSEQ" DataSourceID="FirstLevelDetailTable" Width="100%">
                <ParentTableRelation>
                  <telerik:GridRelationFields DetailKeyField="CCATTRSEQ" MasterKeyField="CCATTRSEQ" />
                </ParentTableRelation>
      <Columns>
        <telerik:GridClientSelectColumn  UniqueName="column1" ButtonType="PushButton" ConfirmDialogType="RadWindow">
          <HeaderStyle Width="27px" />
            <ItemStyle VerticalAlign="Top" />
        </telerik:GridClientSelectColumn>
        <telerik:GridExpandColumn Visible="True" UniqueName="ExpandColumnSec" >
          <HeaderStyle Width="27px" />
        </telerik:GridExpandColumn>                                                           
        <telerik:GridBoundColumn DataField="VALSTR" HeaderText="Section" SortExpression="VALSTR"
                                 UniqueName="VALSTR">
          <ItemStyle Wrap="True" />
          <HeaderStyle Width="190px" />
        </telerik:GridBoundColumn>
        <telerik:GridBoundColumn DataField="CCATTRSEQ" HeaderText="CCATTRSEQ" SortExpression="CCATTRSEQ"
                                 UniqueName="CCATTRSEQ" Display="False">
          <ItemStyle Wrap="False" />
        </telerik:GridBoundColumn>            
        <telerik:GridBoundColumn DataField="SECTION" HeaderText="Section Number" SortExpression="SECTION"
                                 UniqueName="SECTION" Display="False">
          <ItemStyle Wrap="False" />
        </telerik:GridBoundColumn>                        
        <telerik:GridBoundColumn DataField="PID" HeaderText="PID" SortExpression="PID"
                                 UniqueName="PID" Display="False">
          <ItemStyle Wrap="False" />
        </telerik:GridBoundColumn>                        
        <telerik:GridEditCommandColumn HeaderButtonType="PushButton">
          <HeaderStyle Font-Bold="False" Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
                       Font-Underline="False" VerticalAlign="Top" Wrap="True" />
          <ItemStyle Font-Bold="False" Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
                     Font-Underline="False" VerticalAlign="Top" Wrap="True" />
        </telerik:GridEditCommandColumn>
        <telerik:GridButtonColumn CommandName="Delete" Text="Delete" UniqueName="column">
        </telerik:GridButtonColumn>
      </Columns>
      <SortExpressions>
        <telerik:GridSortExpression FieldName="VALSTR" SortOrder="Descending"></telerik:GridSortExpression>
      </SortExpressions>
    </telerik:GridTableView>
  </DetailTables>
          <SortExpressions>
            <telerik:GridSortExpression FieldName="VALSTR" SortOrder="Descending" />            
          </SortExpressions>
          <Columns>
            <telerik:GridClientSelectColumn  UniqueName="column1" ButtonType="PushButton" ConfirmDialogType="RadWindow">
              <HeaderStyle Width="27px" />
                <ItemStyle VerticalAlign="Top" />
            </telerik:GridClientSelectColumn>
            <telerik:GridExpandColumn Visible="True" UniqueName="ExpandColumnSec" >
              <HeaderStyle Width="27px" />
            </telerik:GridExpandColumn>                                                           
            <telerik:GridBoundColumn DataField="VALSTR" HeaderText="Section" SortExpression="VALSTR"
              UniqueName="VALSTR">
              <ItemStyle Wrap="True" />
                <HeaderStyle Width="190px" />
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="CCATTRSEQ" HeaderText="CCATTRSEQ" SortExpression="CCATTRSEQ"
              UniqueName="CCATTRSEQ" Display="False">
              <ItemStyle Wrap="False" />
            </telerik:GridBoundColumn>            
            <telerik:GridBoundColumn DataField="SECTION" HeaderText="Section Number" SortExpression="SECTION"
              UniqueName="SECTION" Display="False">
              <ItemStyle Wrap="False" />
            </telerik:GridBoundColumn>                        
            <telerik:GridBoundColumn DataField="PID" HeaderText="PID" SortExpression="PID"
              UniqueName="PID" Display="False">
              <ItemStyle Wrap="False" />
            </telerik:GridBoundColumn>                        
              <telerik:GridEditCommandColumn HeaderButtonType="PushButton">
                  <HeaderStyle Font-Bold="False" Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
                      Font-Underline="False" VerticalAlign="Top" Wrap="True" />
                  <ItemStyle Font-Bold="False" Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
                      Font-Underline="False" VerticalAlign="Top" Wrap="True" />
              </telerik:GridEditCommandColumn>
              <telerik:GridButtonColumn CommandName="Delete" Text="Delete" UniqueName="column">
              </telerik:GridButtonColumn>
          </Columns>
            <ExpandCollapseColumn>
                <HeaderStyle Width="20px" />
            </ExpandCollapseColumn>
            <RowIndicatorColumn>
                <HeaderStyle Width="20px" />
            </RowIndicatorColumn>
        </MasterTableView>
        <SelectedItemStyle BackColor="#C00000" />
        <AlternatingItemStyle BackColor="Silver" />
        <HeaderContextMenu EnableTheming="True" Skin="Outlook">
            <CollapseAnimation Duration="200" Type="OutQuint" />
        </HeaderContextMenu>
        <FilterMenu EnableTheming="True" Skin="Outlook">
            <CollapseAnimation Duration="200" Type="OutQuint" />
        </FilterMenu>
        <EditItemStyle Font-Bold="False" Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
            Font-Underline="False" VerticalAlign="Top" Wrap="True" />
        <CommandItemStyle VerticalAlign="Top" />
    </telerik:RadGrid>

[Georgi Krustev]

Hello Robert,

To attain the functionality you are looking for, please verify that you are using Advanced data - binding. Otherwise the self-referencing hierarchy will not work. I attached a test project which uses Northwind database and implements on demand server bind.

You can review it and try to apply the same logic in your project.

Best regards,
Georgi Krustev
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

[Robert Clavelle]

  I want to see a SelfHierarchy RadGrid control done with HierarchyLoadMode=ServerOnDemand on an ORACLE database or a statement saying you don't support this configuration. 
  -Robert

[Georgi Krustev]

Hello Robert Clavelle,

If you do not mind we will continue the communication in the support thread you started on this issue in order to avoid duplicate discussions. In this way you will be in touch how the resolution of the problem goes. The number of the support ticket is 185829.

Here I am pasting my reply from the support thread to make it public for other community members as well:

Hello Robert,

Thank you for contacting us.

I reviewed your test project attached to this support thread. I found that the problem is not caused because of a different data provider. In both cases if the database is a MS SQL or Oracle the returned business object will be the same.

When the HierarchyLoadMode property is set to "ServerOnDemand", you cannot use the approach given in our live demo. As you noticed, you cannot hide the expand/collapse column and add buttons which perform client side actions and perform ServerOnDemand bind of the grid at the same time. When a custom button is inserted in a grid cell, you need to handle the binding with your custom code if it is done on server.

So to attain the functionality you are looking for, you should not hide the entire column and if you want you can hide only the buttons in the column, when the current item does not have children.

Please examine again the example attached to my last message and the one attached to the following KB article. You do not need to care what the data provider is, it does not matter in this case.

For your convenience I am pasting the code from the Default.aspx.cs file:

namespace AVLWebEditor4
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            //fromTopLevelNodes.MasterTableView.FilterExpression = "PID = -1";
            //fromTopLevelNodes.MasterTableView.DataKeyValues;
            string theEventName = e.ToString();
            string theSenderName = sender.ToString();

            if (!Page.IsPostBack)
            {
                //
            }
            else
            {
                Type senderType = sender.GetType();
                string senderTypeName = senderType.Name;
                TypeAttributes stta = senderType.Attributes;

                Type eventType = e.GetType();
                string eventTypeName = eventType.Name;
                TypeAttributes evta = eventType.Attributes;

                HttpRequest thisRequest = Page.Request;
                System.Collections.Specialized.NameValueCollection theParams = thisRequest.Params;

                //Code below is very handy
                /*
                foreach (object eachParam in theParams)
                {
                    System.Type myType = eachParam.GetType();
                    string keyName = eachParam.ToString();
                    string[] ParamValue = theParams.GetValues(keyName);
                }
                */
                string[] paramValue = theParams.GetValues("ScriptManager1");
                string eventTypeString = paramValue[0];

                //fromTopLevelNodes_NeedDataSource(sender, e);

                //System.Web.SessionState.HttpSessionState = Page.Session;
                //if (sender.GetType  == TypeOf(Button)
                //Button theSender = (Button)sender;
                //string buttonName = theSender.CommandName;


            }

        }

        protected void Page_PreRenderComplete(Object sender, EventArgs e)
        {
            HideExpandColumnRecursive(fromTopLevelNodes.MasterTableView);
        }

        protected void fromTopLevelNodes_ColumnCreated(object sender, GridColumnCreatedEventArgs e)
        {
            //object inspect = fromTopLevelNodes.SelectedValue;
            //GridItemCollection inpsect2 = fromTopLevelNodes.SelectedItems;

        }

        protected void fromTopLevelNodes_ItemCreated(object sender, GridItemEventArgs e)
        {
            GridItemEventInfo details = e.EventInfo;
            CreateExpandCollapseButton(e.Item, "CCATTRSEQ");
            if (e.Item is GridHeaderItem && e.Item.OwnerTableView != fromTopLevelNodes.MasterTableView)
            {
                e.Item.Style["display"] = "none";
            }
            if (e.Item is GridNestedViewItem)
            {
                e.Item.Cells[0].Visible = false;
            }
        }
        protected void fromTopLevelNodes_ItemDataBound(object sender, GridItemEventArgs e)
        {
            GridItemEventInfo details = e.EventInfo;
        }


        public void HideExpandColumnRecursive(GridTableView tableView)
        {
            GridItem[] nestedViewItems = tableView.GetItems(GridItemType.NestedView);
            foreach (GridNestedViewItem nestedViewItem in nestedViewItems)
            {
                foreach (GridTableView nestedView in nestedViewItem.NestedTableViews)
                {
                    nestedView.Style["border"] = "0";

                    Button MyExpandCollapseButton = (Button)nestedView.ParentItem.FindControl("MyExpandCollapseButton");
                    if (nestedView.Items.Count == 0)
                    {
                        if (MyExpandCollapseButton != null)
                        {
                            MyExpandCollapseButton.Style["visibility"] = "hidden";
                        }
                        nestedViewItem.Visible = false;
                    }
                    else
                    {
                        if (MyExpandCollapseButton != null)
                        {
                            MyExpandCollapseButton.Style.Remove("visibility");
                        }
                    }

                    if (nestedView.HasDetailTables)
                    {
                        HideExpandColumnRecursive(nestedView);
                    }

                }
            }
        }

        protected void fromTopLevelNodes_NeedDataSource(object source, Telerik.Web.UI.GridNeedDataSourceEventArgs e)
        {
            fromTopLevelNodes.DataSource = GetDataTable("SELECT * FROM CCATTRIBUTES WHERE PID = 1");
        }

        public DataTable GetDataTable(string query)
        {
            //DataSet myDataTable;
            OracleConnection con;
            OracleCommand cmd;

            con = new OracleConnection();
            con.ConnectionString = "Data Source=sbllnk;Persist Security Info=True;User ID=lldmdev05;Password=termini;Unicode=True";


            con.Open();
            //Create the command
            cmd = con.CreateCommand();

            //Set the SQL query
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = query;

            DataTable myDataTable = new DataTable();
            //myDataTable = new DataSet();

            try
            {
                OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                //myDataTable = new DataSet();
                adapter.Fill(myDataTable);
            }
            finally
            {
                con.Close();
            }
            //DataTable dsAsTable = new DataTable();
            //dsAsTable = (System.Data.DataTable) myDataTable;
            //return dsAsTable;
            return myDataTable;
        }


    }
}

Kind regards,
Georgi Krustev
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.