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.

Usage

  • 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;
		dtResult.Rows.Add(drResult);
 
		OrderRecursiveHelper(ref pdtSource, ref dtResult, drv.Row, 1, pIDColumn, pPIDColumn, pSortColumn);
	}
 
	pdtSource.Clear();
	foreach (DataRow drResult in dtResult.Rows)
	{
		pdtSource.Rows.Add(drResult.ItemArray);
	}
 
	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;
		pdtResult.Rows.Add(drResult);
 
		OrderRecursiveHelper(ref pdtSource, ref pdtResult, drv.Row, (pRecursiveLevel + 1), pIDColumn, pPIDColumn, pSortColumn);
	}
}

Comments

You can comment without logging in
Profile
Username
Password
Password
Email
Nickpic
Get notified on reply to own posts  (only works if you specify an email address)
Get notified on receiving a PM  (only works if you specify an email address)
Remember my username
Remember my password
signature
Words: Chars: Chars left: