Solved

Treeview moving from ADO.net to Entity Framework

Posted on 2013-12-04

2 solutions

1,353 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 500 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 500 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

PHP Sessions: Simpler Than You May Think!

Article by: Ray

A Change in PHP Behavior with Session Write Short Circuit (http://php.net/manual/en/book.session.php#116217) (Winter 2014)** With the release of PHP 5.6 the session handler changed in a way that many think should be considered a bug. See the note …

Pros and Cons to using New Relic for performance monitoring

Article by: Jeffrey

New Relic: Our company recently started researching several products to figure out what were the best ways for us to increase our web page speed and to quickly identify performance problems that we may be having. One of the products we evaluated wa…

Web Applications

Creating a Website from Scratch

Video by: doug

This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…

How to Accept PayPal Payments on a Wufoo Form

Video by: Leslie

Learn how to set-up PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.