CODEWITHSUNDEEP
c#databasetreeview
Brezhnews
Brezhnews

Reputation: 355

Creating treeview from database

I have a database named dtEmploye with fields

employe_id
name
surname
boss_id which has a recursive relation on employee_id where I have in code XXX

I have the code but there is some error it shows "Specified cast is not valid."

private void btntree_Click(object sender, EventArgs e)
    {
        DataTable vec = sel("SELECT * FROM dtemployee");
        treeView1.Nodes.Clear();
        foreach (DataRow dr in vec.Rows)
        {
            if ((int)dr["boss_id"] == XXX)
            {
                TreeNode parent = new TreeNode();
                parent.Text = dr["name"+"surname"].ToString();
                string value = dr["employe_id"].ToString();
                parent.Expand();
                treeView1.Nodes.Add(parent);
                sublevel(parent, value);
            }
        }
    }
    public int sublevel(TreeNode parent, string id)
    {
        DataTable ch = sel("SELECT * FROM dtEmploye WHERE boss_id=" + id);
        if (dtEmploye.Rows.Count > 0)
        {
            foreach (DataRow dr in ch.Rows)
            {
                TreeNode child = new TreeNode();
                child.Text = dr["name"+"surname"].ToString().Trim();
                string temp = dr["employe_id"].ToString();
                child.Collapse();
                parent.Nodes.Add(child);
                sublevel(child, temp);
            }
            return 0;
        }
        else
        {
            return 0;
        }
    }
    protected DataTable sel(string select)
    {
        NpgsqlDataAdapter adpt = new NpgsqlDataAdapter(select, con);
        DataTable dt2 = new DataTable();
        adpt.Fill(dt2);
        return dt2;
    }

Upvotes: 2

Views: 2575

Answers (4)

Renatas M.
Renatas M.

Reputation: 11820

The only place where I can see you getting this error is :

if ((int)dr["boss_id"] == XXX)

Try to do something like this:

if(dr["boss_id"] != null && dr["boss_id"] is int && (int)dr["boss_id"] == XXX)

Upvotes: 0

Stefan Steiger
Stefan Steiger

Reputation: 82186

Why not doing it in SQL ? You can do a recursive query and then you can add all nodes by looping through the dataset in code using sort to determine which node a subnode belongs to.

;WITH Tree (ID, [NAME], PARENT_ID, Depth, Sort) AS
(
    SELECT ID, [NAME], PARENT_ID, 0 AS Depth, CONVERT(varchar(255), [Name]) AS Sort FROM Category
    WHERE PARENT_ID = 0
    UNION ALL
    SELECT CT.ID, CT.[NAME], CT.PARENT_ID, Parent.Depth + 1 AS Depth, 
    CONVERT(varchar(255), Parent.Sort + ' | ' + CT.[NAME]) AS Sort
    FROM Category CT
    INNER JOIN Tree as Parent ON Parent.ID = CT.PARENT_ID
)

-- HERE IS YOUR TREE, Depths gives you the level starting with 0 and Sort is the Name based path
SELECT ID, [NAME], PARENT_ID, Depth, Sort FROM Tree
ORDER BY Sort

Upvotes: 1

Jonathan Henson
Jonathan Henson

Reputation: 8206

You need to be careful using casts when you don't know the object type ahead of time. Remember that a cast is only valid in an "is a" relationship. For everything else you need to do a conversion.

For example:

if you have

class Truck : Automobile

and 

class Car : Automobile

Then

Automobile truck = new Truck();
Truck refoftruck = (Truck)truck; //correct usage;

Car badrefofTruck = (Car)truck; //will not work;

Though a Car is indeed an Automobile (the type of the object truck), it is not also a truck so the cast will not work. To move from a truck to a car, you would have to write some sort of conversion.

The same is true with object and int. While almost everything inherits from object, they can diverge further down the inheritance chain. You don't know what type the object is, so you don't know whether it is an int, string, double etc... Thus a cast in this situation is usually a bad idea. All casts must be up or down the inheritance chain.

To convert a string to an int (notice the word Convert not cast) you would use:

Convert.ToInt32(strObject.ToString());

I hope this helps and clarifies. Of course, you probably need to check that the object is not null before you perform the Conversion.

Upvotes: 0

Amit
Amit

Reputation: 22076

Try this

    if ((int)dr["boss_id"] == XXX && DBNull.Value != dr["boss_id"])
    {
        TreeNode parent = new TreeNode();
        parent.Text = dr["name"].ToString() + dr["surname"].ToString();
        string value = dr["employe_id"].ToString();
        parent.Expand();
        treeView1.Nodes.Add(parent);
        sublevel(parent, value);
    }

Upvotes: 1

Related Questions