Monday, April 20, 2009

Populate an ASP .Net Treeview from a SQL self-join table

I had last week an ASP .net TreeView control to fill from a self-join table. I looked on the Internet to find an Algorithm but I did not find any solution using C# or VB .Net but a solution in Code Project using SQL or other using DataSets. I then decided to post the following Algorithm that might help somebody else.
Here is the table I used in the example and the generated treeview image on the right.


Location.ID Location.Name Location.ParentID
1 Americas 0
2 Europe 0
3 Asia 0
4 Northern America 1
5 Western Europe 2
6 Western Asia 3
7 Eastern Asia 3
8 South-Eastern Asia 3
9 Northern Europe 2
10 Southern Europe 2
11 Canada 4
12 United States 4
13 Netherlands 9
14 France 9
15 Israel 5
16 Japan 6
17 Spain 10
18 United Kingdom 9
19 Vietnam 8
20 Quebec 11
21 California 12
22 District of Columbia 12
23 England 18
24 Hefa 15
25 Midi Pyrenees 14
26 Nara 16
27 New Jersey 12
28 Noord-Brabant 13
29 Ontario 11
30 Santa Cruz De Tenerife 17

 

Here is the code I used to generate the treeview:

<%@ Assembly Name="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Page Language="C#" %>

 

<%@ Import Namespace="System.Data.SqlClient" %>

<%@ Import Namespace="Microsoft.SharePoint" %>

<%@ Import Namespace="System.Collections.Generic" %>

<form runat="server">

<asp:treeview runat="server" id="TreeView1" />

</form>

 

<script runat="server">

   

    public class Location

    {

        private string _locationName;

        private int _locationId;

        private int _parentLocationId;

 

        public string LocationName

        {

            get { return _locationName; }

            set { _locationName = value; }

        }

 

        public int LocationId

        {

            get { return _locationId; }

            set { _locationId = value; }

        }

 

        public int ParentLocationId

        {

            get { return _parentLocationId; }

            set { _parentLocationId = value; }

        }

    }

        //list of all nodes

        SortedList<int, Location> myLocations = new SortedList<int, Location>();

        //list of all created nodes

        SortedList<int, TreeNode> myCreatedNodes = new SortedList<int, TreeNode>();

 

    public void Page_Load(object sender, EventArgs e)

    {

        string result="<br>start loading  page : " + DateTime.Now.Second + " " + DateTime.Now.Millisecond;

        try

        {

            System.Data.SqlClient.SqlConnection myCon = new System.Data.SqlClient.SqlConnection("Server=localhost;Database=myCompany;integrated security=SSPI");

 

            myCon.Open();

            SqlCommand myCommand = new SqlCommand();

 

            myCommand.CommandText = @"select ID,

                                        Name,

                                        ParentID

                                        from Location

                                        ";         

           

            myCommand.Connection = myCon;

            SqlDataReader myReader = myCommand.ExecuteReader();

            Location myLocation = null;

            while (myReader.Read())

            {

                myLocation = new Location();

                myLocation.LocationName = Convert.ToString(myReader[1]);

                myLocation.LocationId = Convert.ToInt32(myReader[0]);

                myLocation.ParentLocationId = Convert.ToInt32(myReader[2]);

                myLocations.Add(myLocation.LocationId, myLocation);

            }

 

            myCon.Close();

 

            TreeNode aNode = null;

            result+="<br>start processing treeview : " + DateTime.Now.Second + " " + DateTime.Now.Millisecond;

           

            foreach (int aKey in myLocations.Keys)

            {

                string code = myLocations[aKey].LocationId.ToString();

                aNode = new TreeNode(myLocations[aKey].LocationName, code);

                CreateNode(aNode);

            }

            result += "<br>end processing treeview : " + DateTime.Now.Second + " " + DateTime.Now.Millisecond;

        }

        catch (Exception ex)

        {

            Response.Write("error<BR>" + ex.Message);

            Response.Write("Trace<BR>" + ex.StackTrace);

        }

        Response.Write(result);

    }

 

    public void CreateNode(TreeNode aNode)

    {

        //This list stores all the nodes id from the current node to the ultimate parent

        List<int> myPath = new List<int>();

        if (!myCreatedNodes.ContainsValue(aNode))//if the node was not alreazdy created

        {

            int nodeId=1001;

            nodeId = Convert.ToInt32(aNode.Value);

            //Building the current node path untill the ultimate parent

            myPath.Add(nodeId);

            while (nodeId != 0)

            {

               if(nodeId!=0){

               nodeId= myLocations[nodeId].ParentLocationId;

               myPath.Add(nodeId);

                }

            }

        }

       

        //descending from Ultimate parent until the node

        //if the current node does not exists we create it and add it to created nodes collection.

        //if it has not a parent we add it to the treeview

        //if it has a parent,the parent was already created because we come from it, so we add the current node to the parent.

        TreeNode nodeToAdd = null, ParentNodeTofind = null;

        for (int j = myPath.Count - 1; j > -1; j--)

        {

            if (myPath[j] != 0)

            {

                //checking for each path if the nodes was already created

                if (!myCreatedNodes.Keys.Contains(myLocations[myPath[j]].LocationId))

                {

                    //creating the node and adding it to the created nodes collection.

                    nodeToAdd = new TreeNode(myLocations[myPath[j]].LocationName, myLocations[myPath[j]].LocationId.ToString());

                    myCreatedNodes.Add(myLocations[myPath[j]].LocationId, nodeToAdd);

 

                    int parentId = myLocations[myPath[j]].ParentLocationId;

                    //checking if the node has a parent

                    if (parentId == 0)//this node has no parent we add it to the tree view

                    {

                        TreeView1.Nodes.Add(nodeToAdd);

                    }

                    else//this node has a parent

                    {

                        //rerieving parent node (sure to find it)

                        ParentNodeTofind = myCreatedNodes[myLocations[myPath[j]].ParentLocationId];

                        //we add the node to its parent childNodes

                        ParentNodeTofind.ChildNodes.Add(nodeToAdd);

                    }

                }

            }

        }

    }

</script>

 

 

Sunday, April 19, 2009

Use Linq inside a Script Block of an Application Page - SharePoint minimal configuration for Linq


When developing for SharePoint you maybe use Linq. Assume you are developing a new piece of code and to improve your development speed you have decided to test this code in an Application Page inside a Script Block.

Suddenly, you want to use a Linq instruction but you remind that your page is called by a web site of a new Web Application and this Web Application web.config has not been modified to use .Net 3.5 new features.
Actually, at this point it is very fast to activate the use of Linq.

  1. Modify your Application Page to have intellisense.

    You have two page directives to paste:

    the Assembly Page Directive to add a reference to the System.Core.dll (there is just one version of this dll the3.5 version)

    <%@ Assembly Name="System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089" %>



    the Import Page Directive to specify the System.Linq Namespace 

    <%@ Import Namespace="System.Linq" %>


    From this point you will have Linq Intellisense in your Visual Studio 2008 Editor.



    So, you keep writing your code and obtain this page:



    <%@ Page Language="C#" %>

     

    <%@ Assembly Name="System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089" %>

    <%@ Assembly Name="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

    <%@ Import Namespace="Microsoft.SharePoint" %>

    <%@ Import Namespace="System.Linq" %>

    <html xmlns="http://www.w3.org/1999/xhtml">

    <head>

        <title></title>

    </head>

    <body>

        <script runat="server">

            void Page_Load(object sender, EventArgs e)

            {

                SPListCollection myListCollection = SPContext.Current.Web.Lists;

                var listQuery = from SPList aList in myListCollection where (aList.Title) == "Customers" select aList;

     

                if (listQuery.Count() > 0)

                {

                    Response.Write("<br>How many Customers ? <br>" + listQuery.ElementAt<SPList>(0).ItemCount);

                }

                else

                {

                    Response.Write("<br>" + "There is no list with this name");

                }

            } 

        </script>

    </body>

    </html>

     



     But if you try to execute the page you will get a compilation Exception from SharePoint.



  2. Modify your web.config

    To fix the Linq compilation issue open your Web Application web.config and paste this xml code just under the ending Tag of System.Web.


        </webParts>

        <machineKey validationKey="E150B1AD042FFFD037943E670D1ED5EE50FCA3A12F8782BE" decryptionKey="F3790C833070BFBE30590B9CC6380A0592DF229F0DA7E3E7" validation="SHA1" />

        <sessionState mode="SQLServer" timeout="60" allowCustomSqlDatabase="true" partitionResolverType="Microsoft.Office.Server.Administration.SqlSessionStateResolver, Microsoft.Office.Server, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" />

      </system.web>

      <!--Start adding Linq Compilation Instruction - Marc -->

      <system.codedom>

        <compilers>

          <compiler language="c#;cs;csharp" extension=".cs" warningLevel="4"

                    type="Microsoft.CSharp.CSharpCodeProvider, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">

            <providerOption name="CompilerVersion" value="v3.5"/>

            <providerOption name="WarnAsError" value="false"/>

          </compiler>

          <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" warningLevel="4"

                    type="Microsoft.VisualBasic.VBCodeProvider, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">

            <providerOption name="CompilerVersion" value="v3.5"/>

            <providerOption name="OptionInfer" value="true"/>

            <providerOption name="WarnAsError" value="false"/>

          </compiler>

        </compilers>

      </system.codedom>

      <!--End adding Linq Compilation Instruction - Marc -->

      <runtime>

        <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">

          <dependentAssembly>



    And you're done!