I am a danish programmer living in Bangkok.
Read more about me @ rasmus.rummel.dk.
Webmodelling Home > ASP.NET > C# Utility Functions > DataTable Order Recursive

DataTable Order Recursive

Getting recursive data is such a common task that it gives good meaning to encapsulate it into reusable utilities. The typical way of getting data recursively ordered is by using either a stored procedure, using multiple queries or do the ordering client-side and while the stored procedure may get the data faster if you need a sub hierarchy of a big data set, in my use it is most often advantegeous to do it client-side as it is database backend indifferent as well as easier to reuse between projects than a stored procedure.


  • Example
    • Categories are often recursive in nature and indeed I use this function to order the category menu in my Discussions WebControl.
  • Example Code
    • DataTable myTable = GetData(); //your custom function
      Utils.DataTable.OrderRecursive(ref myTable, "ID""PID""SortOrder");

The OrderRecursive function :

public static void OrderRecursive(ref DataTable pdtSource, string pIDColumn, string pPIDColumn, string pSortColumn)
	pdtSource.Columns.Add(new DataColumn("RecursiveLevel"Type.GetType("System.Int32")));
	DataTable dtResult = pdtSource.Clone();
	DataView dv = new DataView(pdtSource);
	dv.RowFilter = pPIDColumn + " IS NULL OR " + pPIDColumn + " = 0";
	dv.Sort = pSortColumn;
	foreach (DataRowView drv in dv)
		DataRow drResult = dtResult.NewRow();
		drResult.ItemArray = drv.Row.ItemArray;
		drResult["RecursiveLevel"] = 0;
		OrderRecursiveHelper(ref pdtSource, ref dtResult, drv.Row, 1, pIDColumn, pPIDColumn, pSortColumn);
	foreach (DataRow drResult in dtResult.Rows)
	pdtSource = dtResult;
private static void OrderRecursiveHelper(ref DataTable pdtSource, ref DataTable pdtResult, DataRow pdrCurrent, int pRecursiveLevel, string pIDColumn, string pPIDColumn, string pSortColumn)
	int currentID = (int)pdrCurrent["ID"];
	DataView dv = new DataView(pdtSource);
	dv.RowFilter = pPIDColumn + " = " + currentID;
	dv.Sort = pSortColumn;
	foreach (DataRowView drv in dv)
		DataRow drResult = pdtResult.NewRow();
		drResult.ItemArray = drv.Row.ItemArray;
		drResult["RecursiveLevel"] = pRecursiveLevel;
		OrderRecursiveHelper(ref pdtSource, ref pdtResult, drv.Row, (pRecursiveLevel + 1), pIDColumn, pPIDColumn, pSortColumn);


You can comment without logging in
 B  U  I  S 
Words: Chars: Chars left: 
      report  reply