Dynamically menu binding

Hi friends.

Many of guys are trying or working on menus for binding it dynamically. I've the solution for it.

Here, I'm putting sample code to create menu dynamically.

MenuTest.aspx

<asp:Menu ID="Menu1" runat="server" Orientation="Horizontal">
</asp:Menu>  

MenuTest.aspx.cs  

static string constr = "SQLOLEDB;Data Source=ABC;Integrated Security=SSPI;Initial Catalog=XYZ";
OleDbConnection cn = new OleDbConnection(constr);  

protected void Page_Load(object sender, EventArgs e)
{
  if (!Page.IsPostBack)
    PopulateRootLevel();
}  

private void PopulateRootLevel()
{
  OleDbCommand objCommand = new OleDbCommand(@"select id,title,(select count(*) FROM SampleCategories WHERE parentid=sc.id) childnodecount FROM SampleCategories sc where parentID IS NULL", cn);
  OleDbDataAdapter da = new OleDbDataAdapter(objCommand);
  DataTable dt = new DataTable();
  da.Fill(dt);
  PopulateNodes(dt, Menu1.Items);
}

private void PopulateSubLevel(int parentid, MenuItem parentMenu)
{
  OleDbCommand objCommand = new OleDbCommand(@"select id,title,(select count(*) FROM SampleCategories WHERE parentid=sc.id) childnodecount FROM SampleCategories sc where parentID="+parentid+"", cn);
  OleDbDataAdapter da = new OleDbDataAdapter(objCommand);
  DataTable dt = new DataTable();
  da.Fill(dt);
  PopulateNodes(dt, parentMenu.ChildItems);
}

private void PopulateNodes(DataTable dt, MenuItemCollection items)
{
  foreach (DataRow dr in dt.Rows)
  {
    MenuItem mi = new MenuItem 
    {
      Text = dr["title"].ToString();  
      Value = dr["id"].ToString();
    }
    items.Add(mi);
    //If node has child nodes, then enable on-demand populating
    bool flag = ((int)(dr["childnodecount"]) > 0);
    if (flag)
    {
      menuCreate(mi);
    }
  }
}
private void menuCreate(MenuItem m)
{
  MenuEventArgs e = new MenuEventArgs(m);
  PopulateSubLevel(Int32.Parse(e.Item.Value), e.Item);
}

Here is the database script:

For schema generating:

 CREATE TABLE [dbo].[SAMPLECATEGORIES](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [parentid] [int] NULL,
    [title] [nvarchar](255) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NOT NULL
)  

For testing, data samples:

INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (1, NULL, N'Category 1')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (2, NULL, N'Category 2')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (3, NULL, N'Category 3')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (4, 1, N'Category 1.1')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (5, 1, N'Category 1.2')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (6, 2, N'Category 2.1')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (7, 2, N'Category 2.2')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (8, 2, N'Category 2.3')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (9, 7, N'Category 2.2.1')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (10, 7, N'Category 2.2.2')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (11, 10, N'Category 2.2.2.1')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (12, 6, N'Category 2.1.1')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (13, 3, N'Category 3.1')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (14, 7, N'Category 2.2.3') 

I hope this article will help you.

After the day of the research I've made this code and it is very helpful to others.

Edit History: 

29-Oct-2013: Changed type to Tip/Trick and minor formatting corrections