Solved

Treeview moving from ADO.net to Entity Framework

Posted on 2013-12-04

2 solutions

Medium Priority

1,370 Views

Last Modified: 2013-12-09

Hi,
I have a treeview which I have working perfectly in VS 2008 using ADO but now I am writing the app again using VS 2012 and Entity Framework and could really do with some help changing it. The code I use to populate the tree follows.

 public static DataSet GetEmployeeHiearchicalInfo()
        {
            DataSet ds = new DataSet();

            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RosConn"].ConnectionString);

            try
            {

                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "Tree_Hierarchy";
                cmd.Connection = conn;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
            }
            catch (SqlException)
            {
                throw;
            }
            finally
            {

            }
            return ds;
        }

        public void PopulateTree(DataRow dataRow, JsTreeNode jsTNode, DataTable tableNodes)
        {

            jsTNode.children = new List<JsTreeNode>();
            foreach (DataRow dr in tableNodes.Rows)
            {

                if (dr != null)
                {
                    if (Convert.ToInt32(dr["level"]) == Convert.ToInt32(dataRow["level"]) + 1
                    && Convert.ToInt32(dr["MangP_ID"]) == Convert.ToInt32(dataRow["Person_ID"])
                    )
                    {

                        JsTreeNode cnode = new JsTreeNode();
                        cnode.attr = new Attributes();
                        cnode.attr.id = Convert.ToString(dr["Person_ID"]);
                        //cnode.attributes.rel = "folder" + Convert.ToString(dr["Person_ID"]);
                        cnode.data = new Data();
                        cnode.data.title = Convert.ToString(dr["Name"]);
                        cnode.data.icon = null;
                        cnode.state = "leaf";



                        cnode.attr.mdata = "{ draggable : false, max_children : 100, max_depth : 100 }";

                        jsTNode.children.Add(cnode);


                        PopulateTree(dr, cnode, tableNodes);

                    }
                }
            }
        }

Open in new window

Then this is in the Controller

public JsonResult GetEmployeeInfo()
        {
            DataSet dsNodes = RosterRepository.GetEmployeeHiearchicalInfo();
            DataTable tableNodes = dsNodes.Tables[0];
            DataRow parentDr = tableNodes.Rows[0];

            JsTreeNode rootNode = new JsTreeNode();
            rootNode.attr = new Attributes();
            rootNode.attr.id = Convert.ToString(parentDr["Person_ID"]);
            rootNode.attr.rel = "root" + Convert.ToString(parentDr["Person_ID"]);
            rootNode.data = new Data();
            rootNode.data.title = Convert.ToString(parentDr["Name"]);

            rootNode.attr.mdata = "{ icon :null : false, max_children : 100, max_depth : 100 }";

            rr.PopulateTree(parentDr, rootNode, tableNodes);



            return Json(rootNode, JsonRequestBehavior.AllowGet);
        }

Open in new window

How can I convert this to use EF
Thank you for any help.

Comment

Question by:Niall292

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

12 Comments

LVL 96

Expert Comment

by:Bob Learned

ID: 396973092013-12-04

I would assume that you already have the data context defined, and the business classes generated, so what kind of help do you need?

Author Comment

by:Niall292

ID: 396983712013-12-05

From what I have been told using EF will do away with using a Dataset so I take it I could do it all in one move instead of 2 like I do now and I just can't get my head round that way of thinking

LVL 96

Expert Comment

by:Bob Learned

ID: 396985052013-12-05

If you create an instance of the data context, then you have auto-generated business object classes that you can use to access tables.

The difference from the DataSet concept is that you need to understand how the tables are associated, and how to access different data through the navigation properties.

Relationships and Navigation Properties
http://msdn.microsoft.com/en-us/data/jj713564.aspx

If you can show me a screen shot of the .edmx designer layout, and tell me the tables that you need to work with, then we can come up with a LINQ expression to get the hierarchical view of the data that you need for the tree.

Author Comment

by:Niall292

ID: 397007362013-12-06

I apologise for taking so long to get back,
The tree is only using one table.

I have a class

public class Employee_Tree 
{
public int MangP_ID { get; set; }
public int Person_ID { get; set; }
public int level { get; set; }
public string Name {get; set;}
}

Open in new window

I populate the class from a stored procedure

ALTER PROCEDURE [dbo].[Tree_Hierarchy] 

AS
BEGIN
	
	SET NOCOUNT ON;

  With Hierarchy(MangP_ID, Person_ID,level,Name) 
AS
( SELECT MANGP_ID, PERSON_ID,0,F_NAME + ' ' + L_NAME FROM EMPLOYEE
WHERE MANGP_ID = 0 AND TERMINATION_DATE IS NULL UNION ALL 
SELECT DT.MANGP_ID, DT.PERSON_ID, LEVEL+1, DT.F_NAME + ' ' + DT.L_NAME 
FROM EMPLOYEE DT 
JOIN HIERARCHY H ON DT.MangP_ID = H.Person_ID where Termination_Date is null)
SELECT * FROM Hierarchy
END

Open in new window

LVL 96

Expert Comment

by:Bob Learned

ID: 397007592013-12-06

If you are moving to Entity Framework, I am assuming that there are other tables that you need to access, other than this stored procedure.

Stored Procedures in the Entity Framework
http://msdn.microsoft.com/en-us/data/gg699321.aspx

Author Comment

by:Niall292

ID: 397007672013-12-06

I will send you a full picture shortly

LVL 96

Expert Comment

by:Bob Learned

ID: 397008092013-12-06

I usually use views and tables with Entity Framework, but it can support stored procedures. If you are only doing stored procedures, then I wouldn't think that the Entity Framework would add much benefit.

Author Comment

by:Niall292

ID: 397018142013-12-06

We work with a lot of Class 4 data and we are told to always use stored procedures

LVL 96

Assisted Solution

by:Bob Learned

Bob Learned earned 2000 total points

ID: 397023972013-12-06

That is personal preference, but sometimes it is easier (and maybe faster), to do that work with your application. Common Table Expressions (CTEs) are good, and this article shows a comparison between a CTE and navigation properties:

Hierarchical data and Entity Framework 4
http://nosalan.blogspot.com/2012/09/hierarchical-data-and-entity-framework-4.html

Loading 1000 cat. with navigation properties took 15259 ms
Loading 1000 cat. with stored procedure took 169 ms

LVL 96

Accepted Solution

by:Bob Learned

Bob Learned earned 2000 total points

ID: 397024032013-12-06

Here is an example, using a self-referencing association in Entity Framework:

Recursive or hierarchical queries using EF Code First and Migrations
http://code.msdn.microsoft.com/windowsdesktop/Recursive-or-hierarchical-bf43a96e

Author Comment

by:Niall292

ID: 397067542013-12-09

@TheLearnedOne,
Although you didn't answer my question directly you give me plenty of information as to how to go about it so Thank you

Author Closing Comment

by:Niall292

ID: 397067812013-12-09

The information helped a lot.

Thanks

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SOLID Design in PHP Applications

Article by: Ray

Introduction A frequently used term in Object-Oriented design is "SOLID" which is a mnemonic acronym that covers five principles of OO design. These principles do not stand alone; there is interplay among them. And they are not laws, merely princ…

Bitbucket vs. GitHub: Which is Right for You?

Article by: Sofía

A publishing tool, a Version Control System, or a Collaboration Platform! These can be some of the defining words for the two very famous web-hosting Git repositories: Bitbucket and Github. Git is widely used amongst the programmers and developers f…

Migrating the Website

Video by: doug

This video teaches users how to migrate an existing Wordpress website to a new domain.

Introduction to Wufoo & Creating Online Surveys

Video by: Catherine

Wufoo.com provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.