Solved

Treeview moving from ADO.net to Entity Framework

Posted on 2013-12-04
12
1,256 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.
0
Comment
Question by:Niall292
  • 6
  • 6
12 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 39697309
I would assume that you already have the data context defined, and the business classes generated, so what kind of help do you need?
0
 

Author Comment

by:Niall292
ID: 39698371
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
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 39698505
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.
0
 

Author Comment

by:Niall292
ID: 39700736
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

0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 39700759
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
0
 

Author Comment

by:Niall292
ID: 39700767
I will send you a full picture shortly
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 96

Expert Comment

by:Bob Learned
ID: 39700809
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.
0
 

Author Comment

by:Niall292
ID: 39701814
We work with a lot of Class 4 data and we are told to always use stored procedures
0
 
LVL 96

Assisted Solution

by:Bob Learned
Bob Learned earned 500 total points
ID: 39702397
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
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 39702403
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
0
 

Author Comment

by:Niall292
ID: 39706754
@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
0
 

Author Closing Comment

by:Niall292
ID: 39706781
The information helped a lot.

Thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction (All good things must come to an end (http://en.wikipedia.org/wiki/All_Good_Things...)) The original MySQL API (http://php.net/manual/en/book.mysql.php) has gone away, deprecated by PHP in Version 5.5, and removed from PHP in all curre…
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…
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.
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now