I need to load the following table in the form of a treeview in my C# WPF Application. This is my table : Drinks
DrinksId DrinksName Level1 Level2 Level3 Level4 Level5
1 Drinks 0 0 0 0 0
2 Cold 1 0 0 0 0
3 Pepsi 1 1 0 0 0
4 Light 1 1 1 0 0
5 Zero 1 1 2 0 0
6 Heavy 1 1 3 0 0
7 Cola 1 2 0 0 0
8 Zero 1 2 1 0 0
9 Light 1 2 2 0 0
10 One-Light 1 2 2 1 0
11 Two-Light 1 2 2 2 0
12 7-UP 1 3 0 0 0
13 Hot 2 0 0 0 0
14 Coffee 2 1 0 0 0
15 Black 2 1 1 0 0
16 Espresso 2 1 2 0 0
My treeview should be like this:-
Drinks
|----Cold
|--------Pepsi
|------------Light
|------------Zero
|------------Heavy
|--------Cola
|------------Zero
|------------Light
|----------------One-Light
|----------------Two-Light
|--------7-Up
|------Hot
|---------Coffee
|--------------Black
|--------------Espresso
I tried with Hierarchical data template to display the levels in my XAML. I tried binding my treeview to the Observable Collection, as I need to add, remove, update items later.
Now nothing is getting displayed. I don't know where I am going wrong. Please Help
What I have tried:
<pre><Window.Resources>
<DataTemplate x:Key="level5">
<TextBlock Text="{Binding DrinkName}"/>
</DataTemplate>
<HierarchicalDataTemplate x:Key="level4" ItemTemplate="{StaticResource level5}">
<TextBlock Text="{Binding DrinkName}"/>
</HierarchicalDataTemplate>
<HierarchicalDataTemplate x:Key="level3" ItemTemplate="{StaticResource level4}">
<TextBlock Text="{Binding DrinkName}"/>
</HierarchicalDataTemplate>
<HierarchicalDataTemplate x:Key="level2" ItemTemplate="{StaticResource level3}">
<TextBlock Text="{Binding DrinkName}"/>
</HierarchicalDataTemplate>
<HierarchicalDataTemplate x:Key="level1" ItemTemplate="{StaticResource level2}">
<TextBlock Text="{Binding DrinkName}"/>
</HierarchicalDataTemplate>
</Window.Resources>
<Grid>
<TreeView Name="treeviewdrinks" ItemsSource="{Binding Mytv,UpdateSourceTrigger=PropertyChanged}" />
</Grid>
<pre>public MainWindow()
{
InitializeComponent();
LoadTreeView();
}
private void LoadTreeView()
{
string connectionString = @"Provider=SQLOLEDB.1; Data Source=PBA-PC\SQLEXPRESS; Initial Catalog = DemoDB; Integrated Security=SSPI;";
OleDbConnection oledbconn = new OleDbConnection(connectionString);
oledbconn.Open();
OleDbDataAdapter oleda = new OleDbDataAdapter();
Mytv = new ObservableCollection<treeviewcollection>();
treeviewdrinks.DataContext = Mytv;
try
{
OleDbCommand olecmd = new OleDbCommand("Select * from Drinks Where Level1 <> 0 and Level2 = 0 and Level3 = 0 and Level4 = 0 and Level5 = 0", oledbconn);
DataSet ds = new DataSet();
oleda.SelectCommand = olecmd;
oleda.Fill(ds);
foreach (DataRow dr in ds.Tables[0].Rows)
{
Mytv.Add(new treeviewcollection { DrinkName = dr["DrinkName"].ToString() });
var step1 = "";
OleDbCommand olecmd1 = new OleDbCommand("SELECT * From Drinks Where Level1 = ? and Level2 <> 0 and Level3 = 0 and Level4 = 0 and Level5 = 0", oledbconn);
olecmd1.Parameters.AddWithValue(@step1, dr["Level1"].ToString());
OleDbDataAdapter oleda1 = new OleDbDataAdapter(olecmd1);
DataSet ds1 = new DataSet();
oleda1.Fill(ds1);
foreach (DataRow dr1 in ds1.Tables[0].Rows)
{
Mytv.Add(new treeviewcollection { DrinkName = dr1["DrinkName"].ToString() });
var step2 = "";
OleDbCommand olecmd2 = new OleDbCommand("SELECT * From Drinks Where Level1 = ? and Level2 = ? and Level3 <> 0 and Level4 = 0 and Level5 = 0", oledbconn);
olecmd2.Parameters.AddWithValue(@step1, dr["Level1"].ToString());
olecmd2.Parameters.AddWithValue(@step2, dr1["Level2"].ToString());
OleDbDataAdapter oleda2 = new OleDbDataAdapter(olecmd2);
DataSet ds2 = new DataSet();
oleda2.Fill(ds2);
foreach (DataRow dr2 in ds2.Tables[0].Rows)
{
Mytv.Add(new treeviewcollection { DrinkName = dr2["DrinkName"].ToString() });
var step3 = "";
OleDbCommand olecmd3 = new OleDbCommand("SELECT * From Drinks Where Level1 = ? and Level2 = ? and Level3 = ? and Level4 <> 0 and Level5 = 0", oledbconn);
olecmd3.Parameters.AddWithValue(@step1, dr["Level1"].ToString());
olecmd3.Parameters.AddWithValue(@step2, dr1["Level2"].ToString());
olecmd3.Parameters.AddWithValue(@step3, dr2["Level3"].ToString());
OleDbDataAdapter oleda3 = new OleDbDataAdapter(olecmd3);
DataSet ds3 = new DataSet();
oleda3.Fill(ds3);
foreach (DataRow dr3 in ds3.Tables[0].Rows)
{
Mytv.Add(new treeviewcollection { DrinkName = dr3["DrinkName"].ToString() });
var step4 = "";
OleDbCommand olecmd4 = new OleDbCommand("SELECT * From Drinks Where Level1 = ? and Level2 = ? and Level3 = ? and Level4 = ? and Level5 <> 0", oledbconn);
olecmd4.Parameters.AddWithValue(@step1, dr["Level1"].ToString());
olecmd4.Parameters.AddWithValue(@step2, dr1["Level2"].ToString());
olecmd4.Parameters.AddWithValue(@step3, dr2["Level3"].ToString());
olecmd4.Parameters.AddWithValue(@step4, dr3["Level4"].ToString());
OleDbDataAdapter oleda4 = new OleDbDataAdapter(olecmd4);
DataSet ds4 = new DataSet();
oleda4.Fill(ds4);
foreach (DataRow dr4 in ds4.Tables[0].Rows)
{
Mytv.Add(new treeviewcollection { DrinkName = dr4["DrinkName"].ToString() });
}
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
oledbconn.Close();
}
}
private ObservableCollection<treeviewcollection> _mytv;
public ObservableCollection<treeviewcollection> Mytv
{
get { return _mytv; }
set
{
_mytv = value;
OnPropertyChanged("Mytv");
}
}
public event PropertyChangedEventHandler PropertyChanged;
private void OnPropertyChanged(string propertyName)
{
if (PropertyChanged != null)
{
PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
}
}
public class treeviewcollection
{
public string DrinkName { get; set; }
}
public class level5
{
public string DrinkName { get; set; }
}
public class level4
{
public string DrinkName { get; set; }
ObservableCollection<level5> l4;
}
public class level3
{
public string DrinkName { get; set; }
ObservableCollection<level4> l3;
}
public class level2
{
public string DrinkName { get; set; }
ObservableCollection<level3> l2;
}
public class level1
{
public string DrinkName { get; set; }
ObservableCollection<level2> l1;
}
}