A common UI requirement is show Hierarchycal data, this data can be loaded in several ways with or without postbacks changing the property "HierarchyLoadMode". In case that the set of data to show is not great (use your criteria), you can choose load all data in the client obtaining a more responsive UI. To minimize roundtrips to database a good choice is code an stored procedure that returns multiple results and obtain a DataSet with multiple tables.
I make an example based on the AdventureWorks db to show the Departments and their respective employees.
The Stored Procedure:
This SP gets the data from Departments and Employees returning two results that share one column (DepartmentId) to make the parent/child relation.
CREATE PROCEDURE [HumanResources].[Department_Get_DepartmentAndEmployees]
AS
/* Get the departments */
SELECT HRD.DepartmentID, HRD.Name as DepartmentName
FROM HumanResournces.Department HRD
ORDER BY DepartmentName
/* Get the employees */
SELECT PC.FirstName,
PC.LastName,
HRE.Title,
HRDep.DepartmentId
FROM
HumanResources.Employee HRE
INNER JOIN Person.Contact PC ON PC.ContactId = HRE.ContactId
INNER JOIN HumanResources.EmployeeDepartmentHistory HRDep ON HRDep.EmployeeId = HRE.EmployeeId
The .aspx Code:
Note the ParentTableRelation tag uses the DepartmentId field to make the relation from the Employees table to the Department table.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<%@ Register Assembly="Telerik.Web.UI" Namespace="Telerik.Web.UI" TagPrefix="telerik" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Telerik Radgrid: showing hierarchical data using a Dataset with multiple tables</title>
</head>
<body>
<form id="form1" runat="server">
<asp:ScriptManager ID="Scriptmanager1" runat="server">
</asp:ScriptManager>
<telerik:RadGrid ID="gvEmployeesByDepartment" runat="server" Width="95%" AutoGenerateColumns="False"
GridLines="None" ShowHeader="True" Skin="Web20">
<MasterTableView DataMember="Departments" DataKeyNames="DepartmentId" Width="100%"
Name="Customers" HierarchyLoadMode="Client">
<DetailTables>
<telerik:GridTableView DataMember="Employees" DataKeyNames="DepartmentId" Width="100%"
runat="server" ShowHeader="true">
<ParentTableRelation>
<telerik:GridRelationFields DetailKeyField="DepartmentId" MasterKeyField="DepartmentId" />
</ParentTableRelation>
<RowIndicatorColumn CurrentFilterFunction="NoFilter" FilterListOptions="VaryByDataType"
Visible="False">
<HeaderStyle Width="20px"></HeaderStyle>
</RowIndicatorColumn>
<ExpandCollapseColumn CurrentFilterFunction="NoFilter" FilterListOptions="VaryByDataType"
Visible="False" Resizable="False">
<HeaderStyle Width="20px"></HeaderStyle>
</ExpandCollapseColumn>
<Columns>
<telerik:GridBoundColumn DataField="Title" UniqueName="Title" ReadOnly="true" HeaderText="Title">
</telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="FirstName" UniqueName="FirstName" ReadOnly="true"
HeaderText="First Name">
</telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="LastName" UniqueName="LastName" ReadOnly="true"
HeaderText="Last Name">
</telerik:GridBoundColumn>
</Columns>
</telerik:GridTableView>
</DetailTables>
<RowIndicatorColumn CurrentFilterFunction="NoFilter" FilterListOptions="VaryByDataType"
Visible="False">
<HeaderStyle Width="20px"></HeaderStyle>
</RowIndicatorColumn>
<ExpandCollapseColumn CurrentFilterFunction="NoFilter" FilterListOptions="VaryByDataType"
Resizable="False">
<HeaderStyle Width="20px"></HeaderStyle>
</ExpandCollapseColumn>
<Columns>
<telerik:GridBoundColumn DataField="DepartmentName" UniqueName="DepartmentName" HeaderText="Department">
</telerik:GridBoundColumn>
</Columns>
</MasterTableView>
</telerik:RadGrid>
</form>
</body>
</html>
The .cs Code:
using System;
using System.Data;
using System.Web.UI;
using System.Data.SqlClient;
public partial class _Default :System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if(!Page.IsPostBack)
BindDepartmentsAndEmployees();
}
private void BindDepartmentsAndEmployees()
{
SqlConnection conn = new SqlConnection(@"Integrated Security=SSPI;Persist Security Info=False;User ID=marketpoint;Initial Catalog=AdventureWorks;Data Source=(local)\sqlexpress");
SqlCommand cmd = new SqlCommand("[HumanResources].[Department_Get_DepartmentAndEmployees]", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet dsDepartmentAndEmployees = new DataSet();
da.Fill(dsDepartmentAndEmployees);
if(da != null)
{
// Renames Dataset table to get more clear names in grid declarative properties
dsDepartmentAndEmployees.Tables[0].TableName = "Departments";
dsDepartmentAndEmployees.Tables[1].TableName = "Employees";
gvEmployeesByDepartment.DataSource = dsDepartmentAndEmployees;
gvEmployeesByDepartment.DataBind();
}
}
}
The UI Result (Click to Enlarge):
1 comment:
Buenas Martín!
la verdad que si, las grillas de telerik estan muy buenas...
Yo tuve que hacer lo mismo pero bindearla en tres niveles con tres dataset distintos y cada nivel tiene un data source
Y tuve que adicionar a cada jerarquia el modo de carga. Si no lo ponés al hacer click en el más no te permite expandir las jerarquias.
saludos
Horacio
Post a Comment