Saturday 16 May 2009

Populate a Data Bound TreeView in .Net using C#

A nice and clear Treeview navigator can dramatically improve customers’ experience of using our software. Putting the Treeview at left hand side of the screen with two panel at right divided by a horizontal panel splitter, a typical outlook style user interface, has become very popular in business application.

Visual Studio 2008 offers some out-of-the-box features that we can easily create a Treeview control in the form designer without writing much code. However if we need to (Most of time I believe we do) programmatically populate a Treeview menu, particularly if we want to populate a Treeview based on a database table, this does require some codes. In this demo, I am demonstrating how we can populate a Treeview control with database tables. The backend database is Oracle 10g R2, I use the employee table under HR schema. It shouldn’t matter which database you are using, as long as your table or view in hierarchy structure, for instance like:

ID EMP_NAME PARENT_ID
100 Steven
101 Neena 100
102 Lex 100
103 Alexander 102
104 Bruce 103
105 David 103
106 Valli 103
107 Diana 103
108 Nancy 101

- Firstly in the form designer, we drag a Treeview control and make it dock left.
- Drag a Splitter control to the right hand side of the form and make it dock left as shown below.

- Drag a Panel control to the right hand side and make it dock top.
- Drag a Splitter control to the bottom right and make it dock top.
- Drag a third Panel to the bottom right and make it fill the rest of the screen as shown below.

- To populate the TreeViewNode, I am using a recursive function. The tag property is used to store the level of the hierarchy.

using System;
using System.Data;
using System.Windows.Forms;
using System.Data.OracleClient;

namespace TreeViewDemo
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

DataSet dsTreeView = new DataSet();

// Populate public DataSet
private DataSet PopulateDataTable()
{
string connectionString = "Data Source=orcl;Persist Security Info=True; "
+ "User ID=hr;Password=hr;Unicode=True";

string queryString = "select * from employees";

OracleConnection connection = new OracleConnection(connectionString);
OracleCommand command = new OracleCommand(queryString, connection);
OracleDataAdapter da = new OracleDataAdapter(command);

try
{
connection.Open();
if (!dsTreeView.Tables.Contains("emp"))
dsTreeView.Tables.Add("emp");
da.Fill(dsTreeView, "emp");
}
catch (OracleException oex)
{
throw new Exception(oex.Message);
}
finally
{
connection.Close();
}
return dsTreeView;
}
// The level of tree
int intLevel = 0;

// Recursive function to populate Treeview node
public void FillChildren(TreeNode _Parent, int _ParentID)
{
intLevel++;
DataView Data = new DataView(dsTreeView.Tables["emp"]);
Data.RowFilter = "manager_id = " + _ParentID;

// Define the image index in the lowest level
if (Data.Count == 0)
{
_Parent.ImageIndex = 2;
_Parent.SelectedImageIndex = 2;
}

foreach (System.Data.DataRowView foo in Data)
{
TreeNode t = BuildNode((string)foo["last_name"]);
t.Tag = intLevel;
FillChildren(t, int.Parse(foo["employee_id"].ToString()));
_Parent.Nodes.Add(t);
System.Diagnostics.Trace.WriteLine("Added node at level " + intLevel.ToString());
}
intLevel--;
}

private TreeNode BuildNode(string Text)
{
return new TreeNode(Text);
}

private void LoadMenuTreeView()
{
PopulateDataTable();
DataView Data = new DataView(dsTreeView.Tables["emp"]);
Data.RowFilter = "manager_id is null ";
foreach (System.Data.DataRowView foo in Data)
{
TreeNode t = BuildNode((string)foo["last_name"]);
t.Tag = 0;
FillChildren(t, int.Parse(foo["employee_id"].ToString()));
this.treeView1.Nodes.Add(t);
}

this.treeView1.ImageList = this.imageList1;
}

private void Form1_Load(object sender, EventArgs e)
{
LoadMenuTreeView();
}

private void treeView1_AfterExpand(object sender, TreeViewEventArgs e)
{
e.Node.ImageIndex = 1;
}

private void treeView1_AfterCollapse(object sender, TreeViewEventArgs e)
{
e.Node.ImageIndex = 0;
}
}
}

Run the application, we will see a data bound treeview as shown below.

You can easily add some nice features, such as passing a parameter from a treeview to a DataGridView in the right screen or using a status bar at the bottom to display a tree node full path. Hopefully you find it useful. Happy coding.

Thursday 7 May 2009

How to Compare two DataTables in ADO.net

Sometimes it’s very useful to compare two DataTables and return the different records to a third DataTable. I came across this when I needed to handle my own data concurrency control. Basically my logic is:

- Populate a DataTable of a DataSet and Copy the content of the DataTable to another DataTable immediately after this. (For example we populate a DataTable A and then make a copy to DataTable B.)
- When user update the DataTable A in my application, the event handler firstly populate to a DataTable C from the database.
- Comparing the DataTable C with the DataTable B, if they are same means underlying data hasn’t been changed, then proceed the update otherwise user can choose either to overwrite the data or cancel the operation.

Unfortunately you can’t do a straight comparison of two DataTables. For instance if you write something like: DataTable 1 == DataTable2 or DataTable1.GetHashCode() == DataTable2..GetHashCode() or DT1.Equals(DT2), none of this would work. Below is the code I am using to compare to DataTables, pretty self-explanatory. Hopefully you find it helpful.

private void button1_Click(object sender, EventArgs e)
{
// Something to do with the Initialization of the FirstDataTable and SecondDataTable

DataTable dt;
dt = getDifferentRecords(FirstDataTable, SecondDataTable);

if (dt.Rows.Count == 0)
MessageBox.Show("Equal");
else
MessageBox.Show("Not Equal");
}

#region Compare two DataTables and return a DataTable with DifferentRecords
public DataTable getDifferentRecords(DataTable FirstDataTable, DataTable SecondDataTable)
{
//Create Empty Table
DataTable ResultDataTable = new DataTable("ResultDataTable");

//use a Dataset to make use of a DataRelation object
using (DataSet ds = new DataSet())
{
//Add tables
ds.Tables.AddRange(new DataTable[] { FirstDataTable.Copy(), SecondDataTable.Copy() });

//Get Columns for DataRelation
DataColumn[] firstColumns = new DataColumn[ds.Tables[0].Columns.Count];
for (int i = 0; i < firstColumns.Length; i++)
{
firstColumns[i] = ds.Tables[0].Columns[i];
}

DataColumn[] secondColumns = new DataColumn[ds.Tables[1].Columns.Count];
for (int i = 0; i < secondColumns.Length; i++)
{
secondColumns[i] = ds.Tables[1].Columns[i];
}

//Create DataRelation
DataRelation r1 = new DataRelation(string.Empty, firstColumns, secondColumns, false);
ds.Relations.Add(r1);

DataRelation r2 = new DataRelation(string.Empty, secondColumns, firstColumns, false);
ds.Relations.Add(r2);

//Create columns for return table
for (int i = 0; i < FirstDataTable.Columns.Count; i++)
{
ResultDataTable.Columns.Add(FirstDataTable.Columns[i].ColumnName, FirstDataTable.Columns[i].DataType);
}

//If FirstDataTable Row not in SecondDataTable, Add to ResultDataTable.
ResultDataTable.BeginLoadData();
foreach (DataRow parentrow in ds.Tables[0].Rows)
{
DataRow[] childrows = parentrow.GetChildRows(r1);
if (childrows == null || childrows.Length == 0)
ResultDataTable.LoadDataRow(parentrow.ItemArray, true);
}

//If SecondDataTable Row not in FirstDataTable, Add to ResultDataTable.
foreach (DataRow parentrow in ds.Tables[1].Rows)
{
DataRow[] childrows = parentrow.GetChildRows(r2);
if (childrows == null || childrows.Length == 0)
ResultDataTable.LoadDataRow(parentrow.ItemArray, true);
}
ResultDataTable.EndLoadData();
}

return ResultDataTable;
}
#endregion