How to Display data in treeview from DataBase?

Question

Hi, This is sarath Chandra ...

I want to display data in treeview, Data from Database. I am using SQL Server 2005, We are not useing any Linq concept in our project. Treeview should be 2 level. I am very much new to WPF.

Answer 1

There are 2 steps:

The first step: Get data from database server.

The second step: Populate the data to UI.

In WPF both of them are easy.

1
If you do not use Linq, you can use traditional method to get data from DataBase.
For you used SqlSever 2005, you could build a SqlConnection and open the connection.
Then construct SQL  script and submit to Server to get the return data.
If you use System.Data.SqlClient, you could use SqlDataAdapter to apply the Fill() method to get DataSet.

For example:

   public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            SqlCommand cmd = new SqlCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                DataSet ds = new DataSet();
                da.Fill(ds);
                cmd.Parameters.Clear();
                if (mustCloseConnection)
                    connection.Close();
                return ds;
            }
        }
        private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
            // If the provided connection is not open, we will open it
            if (connection.State != ConnectionState.Open)
            {
                mustCloseConnection = true;
                connection.Open();
            }
            else
            {
                mustCloseConnection = false;
            }
            command.Connection = connection;
            command.CommandText = commandText;
            if (transaction != null)
            {
                if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
                command.Transaction = transaction;
            }
            command.CommandType = commandType;
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }
            return;
        }
        private static void AttachParameters(SqlCommand command, SqlParameter[] commandParamete

2
You can use HierarchicalDataTemplate as ItemTemplate of TreeView.
Below is a simple demo project:
When you run the program, it will look like:

XAML

<Window x:Class="BindingTreeViewToDataSet.Window1"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:local="clr-namespace:BindingTreeViewToDataSet" 
    Title="Binding TreeView To DataSet" 
    Width="300" Height="300"
    FontSize="13"
    >
  <Window.Resources>
    <!-- Creates a DataSet with 
         two related DataTables. -->
    <ObjectDataProvider 
      x:Key="dataSetProvider" 
      MethodName="CreateDataSet" 
      ObjectType="{x:Type local:DataSetCreator}" 
      />
    <!-- Displays a row in the 'Detail' table 
         (i.e. the child table). -->
    <DataTemplate x:Key="DetailTemplate">
      <TextBlock Text="{Binding Info}" />
    </DataTemplate>
    <!-- Displays a row in the 'Master' table 
         (i.e. the parent table). Pulls it's 
         child items from the 'Master2Detail' 
         DataRelation in the DataSet.  
         Each child row is displayed via the 
         'DetailTemplate'. -->
    <HierarchicalDataTemplate 
      x:Key="MasterTemplate" 
      ItemsSource="{Binding Master2Detail}" 
      ItemTemplate="{StaticResource DetailTemplate}"
      >
      <TextBlock Text="{Binding Name}" />
    </HierarchicalDataTemplate>    
  </Window.Resources>
  <Grid>
    <TreeView 
      DataContext="{StaticResource dataSetProvider}" 
      ItemsSource="{Binding Master}"
      ItemTemplate="{StaticResource MasterTemplate}" 
      />
  </Grid>
</Window>

C#

using System;
using System.Data;
using System.Data.SqlClient;
namespace BindingTreeViewToDataSet
{
	public partial class Window1 : System.Windows.Window
	{
		public Window1()
		{
			InitializeComponent();
		}
	}
    public static class DataSetCreator
    {
        public static DataSet CreateDataSet()
        {
            DataSet ds = new DataSet();
            // Create the parent table.
            // ***************************************
            DataTable tbl = new DataTable("Master");
            tbl.Columns.Add("ID", typeof(int));
            tbl.Columns.Add("Name");
            for (int i = 0; i < 3; ++i)
            {
                DataRow row = tbl.NewRow();
                row["ID"] = i;
                row["Name"] = "Master #" + i;
                tbl.Rows.Add(row);
            }
            ds.Tables.Add(tbl);
            // Create the child table.
            // ***************************************
            tbl = new DataTable("Detail");
            tbl.Columns.Add("MasterID", typeof(int));
            tbl.Columns.Add("Info");
            for (int i = 0; i < 9; ++i)
            {
                DataRow row = tbl.NewRow();
                row["MasterID"] = i % 3;
                row["Info"] = String.Format(
                    "Detail Info #{0} for Master #{1}",
                    (i / 3), (i % 3));
                tbl.Rows.Add(row);
            }
            ds.Tables.Add(tbl);
            // Associate the tables.
            // ***************************************
            ds.Relations.Add(
                "Master2Detail",
                ds.Tables["Master"].Columns["ID"],
                ds.Tables["Detail"].Columns["MasterID"]);
            return ds;
        }
    }
}

Answer 2

Thanks for your reply Tao Liang,

But i need some more clareification, We need to display treeview which has 3 levels. And When ever user clicks on the node we have to get the id for that paricular node. And we have to bind the images for the treeview. And We have to implement drag drop for the treeview. i.e User will drag the node he places into another treeview. Please help me.....

Answer 3

Thanks for your reply Tao Liang,

But i need some more clareification, We need to display treeview which has 3 levels. And When ever user clicks on the node we have to get the id for that paricular node. And we have to bind the images for the treeview. And We have to implement drag drop for the treeview. i.e User will drag the node he places into another treeview. Please help me.....

Below is an example for 3-tire TreeView using data binding.
XAML

<Window x:Class="_3TireTreeView.Window1"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    Title="Window1" Height="300" Width="300">
    <Grid>
        <TreeView x:Name="tree">
        </TreeView>
    </Grid>
</Window>

C#

using System.Windows;
using System.Windows.Controls;
using System.Data;
namespace _3TireTreeView
{
    public partial class Window1 : Window
    {
        public DataSet data;
        public Window1()
        {
            InitializeComponent();
            data = CreateData();
            LoadData();
        }
        public DataSet CreateData()
        {
            #region
            DataSet ds = new DataSet();
            DataTable dtCustom = new DataTable("Custom");
            dtCustom.Columns.Add("ID", typeof(string) );
            dtCustom.Columns.Add("NAME", typeof(string));
            DataRow dr;
            dr = dtCustom.NewRow();
            dr["ID"] = "1";
            dr["NAME"] = "Tom";
            dtCustom.Rows.Add(dr);
            dr = dtCustom.NewRow();
            dr["ID"] = "2";
            dr["NAME"] = "Jerry";
            dtCustom.Rows.Add(dr);

            DataTable dtItems = new DataTable("Items");
            dtItems.Columns.Add("ID", typeof(string));
            dtItems.Columns.Add("NAME", typeof(string));
            dr = dtItems.NewRow();
            dr["ID"] = "1";
            dr["NAME"] = "Car";
            dtItems.Rows.Add(dr);
            dr = dtItems.NewRow();
            dr["ID"] = "2";
            dr["NAME"] = "Bicycle";
            dtItems.Rows.Add(dr);

            DataTable dtOrders = new DataTable("Orders");
            dtOrders.Columns.Add("ID", typeof(string));
            dtOrders.Columns.Add("CustomID", typeof(string));
            dtOrders.Columns.Add("ItemID", typeof(string));
            dr = dtOrders.NewRow();
            dr["ID"] = "1";
            dr["CustomID"] = "1";
            dr["ItemID"] = "1";
            dtOrders.Rows.Add(dr);
   
            dr = dtOrders.NewRow();
            dr["ID"] = "2";
            dr["CustomID"] = "1";
            dr["ItemID"] = "2";
            dtOrders.Rows.Add(dr);
            dr = dtOrders.NewRow();
            dr["ID"] = "3";
            dr["CustomID"] = "2";
            dr["ItemID"] = "1";
            dtOrders.Rows.Add(dr);
            ds.Tables.Add(dtCustom);
            ds.Tables.Add(dtItems);
            ds.Tables.Add(dtOrders);
            ds.Relations.Add("rs_CustomOrder", ds.Tables["Custom"].Columns["ID"] , ds.Tables["Orders"].Columns["CustomID"]);
            ds.Relations.Add("rs_ItemsOrder", ds.Tables["Items"].Columns["ID"], ds.Tables["Orders"].Columns["ItemID"]);
          
            
            return ds;
            #endregion
        }
        public void LoadData()
        {
            foreach (DataRow dr in data.Tables["Items"].Rows )
            {
                TreeViewItem root = new TreeViewItem();
                root.Header = dr["NAME"].ToString();
                tree.Items.Add(root);
                ExpandCustom(dr, root);
            }
        }
        public void ExpandCustom(DataRow dr, TreeViewItem pNode)
        {
           

            foreach (DataRow row in dr.GetChildRows("rs_ItemsOrder"))
            {
                TreeViewItem cChild = new TreeViewItem();
                dr.GetChildRows("rs_ItemsOrder");
                string customID = row["CustomID"].ToString();
                string itemID = dr["ID"].ToString();
                cChild.Header = GetCustomName(customID);
                pNode.Items.Add(cChild);
                ExpandOrder(customID, itemID, cChild);
            }
        }
        public void ExpandOrder(string customID, string itemID, TreeViewItem pNode )
        {
            DataRow[] drs = data.Tables["Orders"].Select(" CustomID='" + customID + "'" + "  and ItemID = '" + itemID + "' ");            
            for (int i = 1; i <= drs.Length; i++)
            {
                TreeViewItem cChild = new TreeViewItem();
                cChild.Header = "order" + i.ToString();
                pNode.Items.Add(cChild);
            }
        
        }
        public string GetCustomName( string id )
        {
            string name = "";
            DataRow[] drs = data.Tables["Custom"].Select( "id='"+id+"'" );
            if (drs.Length > 0)
            {
                name = drs[0]["NAME"].ToString();
            }
            return name;
          
        }
    }
}

Answer 4

Thanks for your reply Tao Liang,

But i need some more clareification, We need to display treeview which has 3 levels. And When ever user clicks on the node we have to get the id for that paricular node. And we have to bind the images for the treeview. And We have to implement drag drop for the treeview. i.e User will drag the node he places into another treeview. Please help me.....

You can use DataItem to bind image in TreeView.

For example:

<DataTemplate>
                                <Image Width="50">
                                    <Image.Source>
                                        <Binding Path="ImgPath"  ></Binding>
                                    </Image.Source>
                                </Image>
                            </DataTemplate>

Answer 5

Thanks for your reply Tao Liang,

But i need some more clareification, We need to display treeview which has 3 levels. And When ever user clicks on the node we have to get the id for that paricular node. And we have to bind the images for the treeview. And We have to implement drag drop for the treeview. i.e User will drag the node he places into another treeview. Please help me.....

There are much to do if you want to implement Drag and Drop.

You could chew Josh Smith's Drag and Drop Sample:

http://www.codeproject.com/KB/WPF/ListViewDragDropManager.aspx

Answer 6

Thanks Mr.Tao Liang it will very helpfull for bind 3 level tree view.

Thnaks a  lot......

I have to bind one image for parent node and one image for child node  like that ......

Can u help me ........

Answer 7

Thanks Mr. Tao Liang,

Here I have to find the selected item id in the treeview can u help me ..........

Answer 8

Thanks Mr. Tao Liang,

Here I have to find the selected item id in the treeview can u help me ..........

You could bind the ID path to the Tag.

Tag="{Binding ID}"