How To #3: Display Data from a Database

SmartChartPro and SmartChartLite can display data from several data sources including a database. To display data from a database you can create a custom XML document from the database data or fill a .NET DataSet object and bind it to the control. Because databases don't store data hierarchically, you'll need to create relationships in the DataSet so that hierarchies are shown properly by the SmartChart control. If you'll be displaying data from a database the main table will need to contain a unique id for the row as well as an id for the parent of that row. The SmartWebControls.com trial download contains a simple database that demonstrates this type of structure.

Let's look at the process of binding data in a database to the SmartChart control.

Step 1: Define the Control

Once you've added the control's assembly to the bin folder and registered it in the aspx page (click here for information on doing that), you can define the control in the page. Here's an example of defining the control:

<swc:SmartChartPro ID="SmartChart1" Title="Org Chart" runat="server" OutputType="Html" DataTitleFields="Name,Title"
Font-Size="8" DrawBorder="False" MaxTextLength="30" Width="872px" Height="250px"
DataNodeName="Employee" DataKeyField="PersonID" DataFields="Name,Title" BoxColor="Gainsboro"
BoxGradient="True" ChartDepth="5" Font-Bold="false" MaxChildrenPerLevelGroup="6"
HasParentImagePath="../Images/up.gif" HasChildrenImagePath="../Images/down.gif"
DrillDownType="SmartChartImage">
</swc:SmartChartPro>

This code tells the control that data will be found in Employee nodes (the DataNodeName property) and that data contained in the Name and Title fields (the DataFields property) should be displayed by the control.

Step 2: Query the Database, Create Relationships and Bind Data

Once the control is defined in the page code can be written to query the database and create relationships between the parent and child rows as shown next:

protected void Page_Load(object sender, System.EventArgs e)
{
if (!Page.IsPostBack)
{
SmartChart1.DataSource = GetDataSource();
SmartChart1.DataBind();
}
}

private DataSet GetDataSource()
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["YourConnectionString"].ConnectionString);
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM People", conn);
DataSet ds = new DataSet();
da.Fill(ds, "Employee");

//Create parent/child relationships based upon ID and parentID fields
DataColumn colParent = ds.Tables[0].Columns["PersonID"];
DataColumn colChild = ds.Tables[0].Columns["ParentID"];
DataRelation relation = new DataRelation("ParentChildRel", colParent, colChild);
relation.Nested = true;
ds.Relations.Add(relation);
return ds;
}


This code queries a table named People and selects all columns from it. The data is dumped into a DataSet object and the table within the DataSet is named Employee (recall that the SmartChart control's DataNodeName property shown earlier was set to a value of Employee). Next, the PersonID field is located within the DataSet table (the parent column) along with the ParentID field (the child column). These two columns are used to create a DataRelation object which provides the hierarchical sturcture needed by the SmartChart control to display an OrgChart. The DataRelation's Nested property is set to true so that child rows are nested under the appropriate parent rows and this relationship is then added into the DataSet's Relations collection.

Once the DataSet object is filled with data and relations are created the DataSet can be bound to the SmartChart control. You can view an example of this code in action here.

Download a fully functional trial version of SmartChartPro or SmartChartLite here.


Tags: OrgChart, organization chart, ASP.NET Server Control