?
Solved

Treeview moving from ADO.net to Entity Framework

Posted on 2013-12-04
12
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.
0
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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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
 
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 2000 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 2000 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

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…
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…
This video teaches users how to migrate an existing Wordpress website to a new domain.
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.

801 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