Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Treeview moving from ADO.net to Entity Framework

Posted on 2013-12-04
12
Medium Priority
?
1,402 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
Looking for a new Web Host?

Lunarpages' assortment of hosting products and solutions ensure a perfect fit for anyone looking to get their vision or products to market. Our award winning customer support and 30-day money back guarantee show the pride we take in being the industry's premier MSP.

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This video teaches users how to migrate an existing Wordpress website to a new domain.
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.

618 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