Tuesday, June 21, 2011

Create a FAQ with a TreeView, .Net and SQL

A TreeView is only required to have one node called the “Parent” Node in order to function properly.  In this example we will look at how to create a simple FAQ system using a TreeView.  In my example, I will be using a Telerik RadTreeView control.  Taking the example from this blog, you should be able to port it over to any flavor of your favorite TreeView control.  Keeping this in mind, we will stay within the scope of the article.

The Setup:

To understand how a TreeView works, let’s look at how a TreeView is populated.  You can populate a TreeView with any source that supports IHierarchicalDataSource using the Bind() method.  So in essence, you can populate a TreeView with sources such as SiteMapDataSource and XmlDataSource.  Additionally, You can also populate the TreeView nodes on demand.  In this scenario, you have a TreeView with the boolean property PopulateOnDemand set to True.  When the user clicks a node to expand the child node, the TreeNodePopulate event triggers.  Insert some population logic and you have yourself a pretty little TreeView.  Finally, you can use Non-Declarative Data Binding using a class that derives from DataSourceControl so that you can use the Bind() method to bind the data to the TreeView control declaratively. 
So what does all that mean?  You have a multitude of ways to populate the nodes in a TreeView. 

The Nitty Gritty:

In our FAQ.aspx page (This article assumes you know VS2008 and how to add a page to the project), You simply need to add a TreeView.  Once again, in this example we are using a Telerik RadTreeView
<telerik:RadTreeView ID="rdtvFAQ" runat="server" Skin="WebBlue" DataFieldID="ItemID"
DataFieldParentID="ParentID" DataTextField="Content" Width="920px">                
</telerik:RadTreeView>



Above is a fairly simple and straight-forward TreeView.  In the code-behind, we are not doing any dynamic population, so postbacks in this particular instance is not a problem.  If we were using a paging mechanism or such, this would be a concern and we would not bind() in the Page_Load event.  In this case, it is perfectly acceptable.  Just keep in mind that if you have additional controls or some sort of refresh be mindful that you could potentially run into problems with the dataset not populating exactly as intended.

For our declarations:
Imports System.Data
Imports System.Data.SqlClient




Our Page_Load event:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim ds As DataSet = getAllFAQs()
rdtvFAQ.DataSource = ds
rdtvFAQ.DataBind()
rdtvFAQ.Attributes.Add("style", "white-space:normal")
End Sub




In our load event, we declare a DataSet as ds and instantiate it with the function getAllFAQs().  From there we return the DataSet as a DataSource to the TreeView and then Bind() The Data.  Due to the fact that the Attributes.Add Method is called in reference to the TreeView, it is applicable to the entire TreeView.  This is in contrast to the NodeCreated event where we could put a Node.Attributes.Add method and it would only effect the particular node.  The style tag specifically allows for wrapping.  Nodes in a TreeView do not automatically wrap to the next line.  This is an important note, as it can complicate and propagate problems if you use a node longer than the selected element.

getAllFAQs():
Public Function getAllFAQs() As DataSet
Dim conn As New SqlConnection(ConnectionStrings("someConnString").ToString())
Dim cmd As New SqlCommand("dbo.getAllFAQs", conn)
cmd.CommandType = CommandType.StoredProcedure
conn.Open()
Dim da As New SqlDataAdapter(cmd)
Dim ds As New DataSet()
da.Fill(ds)
conn.Close()
Return ds
End Function




Here we are getting our connection string from web.config using WebConfigurationManager.  So it is important to note that you need to make a reference to System.Web in your library (If you are using APP_Data, this is unnecessary) and add the declaration Imports System.Web.Configuration.WebConfiguration We use a stored procedure to populate our DataSet and then return it to the calling function.  Industry standards suggest using a Try/Catch/Finally block here, as our database might be unavailable or not respond in a timely manner as to cause a hard error.  In this case, we do not have the exception to be able to tell where the error originated other than to look at the hard error.  This becomes even more intricate if we are using Custom Error Pages.  However, keeping that in mind, it is beyond the scope of this article to include debugging features within the code, but I thought it was worth a mention.

SQL Table:
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FAQs](
[ItemID] [int] IDENTITY(1,1) NOT NULL,
[ParentID] [int] NULL,
[Content] [varchar](max) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

In the example above, we create a table called dbo.FAQs that has an identity field called ItemID , a ParentID, and the content.  If you were to have a visualization of the table it would look like:


ItemID

ParentID

Content

1001

NULL
FAQ question #1

1002

1001
Answer to question #1

1003

NULL
FAQ question #2

1004

1003
Answer to question #2

In the table above we can see the Parent/Child relationship that is setup between the ItemID column and ParentID column.  This is what tells the TreeView whether it is a Parent node (If the value of ParentID is NULL) or if it is a Child Node (The value of ParentID is populated).

getAllFAQs (Stored Procedure):
Create PROCEDURE [dbo].[getAllFAQs] 
-- Add the parameters for the stored procedure here

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT ItemID, ParentID, [Content]
From dbo.FAQs
END

Once you set the appropriate permissions, this is a simple Select Command that gets the dataset from the dbo.FAQs table. 

In The End?


Now that we have a fully functional (very basic) FAQ system using a TreeView, you could have added functionality such as collapsing all nodes on creation.  Additionally, you can create a menu system that has a TreeView look and feel but allows you to have the use of the dropdown functionality of the TreeView.  Expanding on this basic usage of the TreeView can give you some extremely powerful results.

Happy .Netting….. Saleh

No comments:

Post a Comment