using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Configuration;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Common;
using System.Data.Entity;
using System.Data.SqlClient.SqlGen;

namespace CMS.BLL
{
    public class SqlSiteMapProvider : StaticSiteMapProvider
    {
        private string connectionString;
        private string providerName;
        private string storedProcedure;
        private bool initialized = false;
        public override void Initialize(string name, System.Collections.Specialized.NameValueCollection attributes)
        {
            if (!IsInitialized)
            {
                base.Initialize(name, attributes);
                providerName = attributes["providerName"];
                connectionString = attributes["connectionString"];
                storedProcedure = attributes["storedProcedure"];
                if (string.IsNullOrEmpty(providerName))
                {
                    throw new ArgumentException("The provider name was not found");
                }
                else if (string.IsNullOrEmpty(connectionString))
                {
                    throw new ArgumentException("The provider name was not found");
                }
                else if (string.IsNullOrEmpty(storedProcedure))
                {
                    throw new ArgumentException("The provider name was not found");
                }
                initialized = true;
            }
        }
        public virtual bool IsInitialized
        {
            get { return initialized; }
        }
        private SiteMapNode rootNode;
        public override SiteMapNode BuildSiteMap()
        {
          
               
                    Clear();
                    DbProviderFactory provider = DbProviderFactories.GetFactory(providerName);
                    DbConnection con = provider.CreateConnection();

                    con.ConnectionString = connectionString;

                    DbCommand cmd = provider.CreateCommand();
                    cmd.CommandText = storedProcedure;
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Connection = con;

                    DbDataAdapter adapter = provider.CreateDataAdapter();
                    adapter.SelectCommand = cmd;

                    System.Data.DataSet ds = new System.Data.DataSet();
                    adapter.Fill(ds, "Products");
                    DataTable dtSiteMap = ds.Tables["Products"];
                    DataRow rowRoot = dtSiteMap.Select("ParentLocationId IS NULL")[0];
                  
                    rootNode = new SiteMapNode(this,
                        rowRoot["Url"].ToString(),
                        rowRoot["Url"].ToString(),
                        rowRoot["Name"].ToString(),
                        rowRoot["Descr"].ToString());
                    
                    string RootID = rowRoot["Id"].ToString();
                    AddNode(rootNode);
                    AddChildren(rootNode, RootID, dtSiteMap);
                  
                
            
            return rootNode;
        }
        private void AddChildren(SiteMapNode rootNode, string RootID, DataTable dtSiteMap)
        {
            DataRow[] childRows = dtSiteMap.Select("ParentLocationId = " + RootID);
            foreach (DataRow row in childRows)
            {
                SiteMapNode childNode = new SiteMapNode(this,
                       row["Url"].ToString(),
                        row["Url"].ToString(),
                        row["Name"].ToString(),
                        row["Descr"].ToString());
                string rowID = row["Id"].ToString();
                AddNode(childNode, rootNode);
                AddChildren(childNode, rowID, dtSiteMap);
            }
        }
        protected override SiteMapNode GetRootNodeCore()
        {
            return BuildSiteMap();
        }
        public override SiteMapNode RootNode
        {
            get
            {
                return BuildSiteMap();
            }
        }
        protected override void Clear()
        {
            lock (this)
            {
                rootNode = null;
                base.Clear();
            }
        }
    }
}

   <siteMap defaultProvider="SqlSiteMapProvider">
     <providers>
       <add name="SqlSiteMapProvider" type="CMS.BLL.SqlSiteMapProvider" providerName="System.Data.SqlClient" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Bitrix.mdf;Integrated Security=True;User Instance=True" storedProcedure="GetSiteMap"/>
     </providers>
   </siteMap>
  </system.web>

    <asp:TreeView ID="TreeNav" runat="server" DataSourceID="SiteMapDataSource1"></asp:TreeView>
    <asp:SiteMapDataSource ID="SiteMapDataSource1" runat="server" StartFromCurrentNode="false" ShowStartingNode="false"/>