DataTable Extension To Resolce Column Ordinal Positions By Name

Today I am expanding on the demonstration of Extension methods from yesterday, with the goal to increase performance of processing data from a DataTable while maintaining flexibility. Yesterday we created new methods to read columns from a DataTableReader, and handle the value if it was null by providing a default value. This modification simplified code readability and provided less opportunity for error, or not handling a null value.

However, when we used our new methods, we had to retrieve the column for the current row by providing an integer for the ordinal position. We could have used a string instead; But, that has a performance penalty, because ADO has to retrieve the ordinal position for the desired column name every time a call is made. When you have a few rows with a few columns you probably can’t measure the performance difference. When you get thousands of rows, with many columns, the performance will decrease. Is the difference measurable? It will always depend. And, it will probably only matter to you when your server is under stress.

It’s easy enough to work with friendly string names, rather than hard coding ordinal positions. The difference is, let’s determine the ordinal position once before processing the entire data table, instead of determining it for every row/column combination. Let’s do this by using the string values. In order to accomplish the ordinal position resolution I have created an extension method based on the DataTable class. My example will use an assumed DataTable instance, dt.

internal static int[] GetColumnPointers(this DataTable dt, string[] columnNameList)
{
   var itemCount = columnNameList.Length;
   var result = new int[itemCount];
   if (itemCount > 0)
   {
      for (int i = 0; i < columnNameList.Length; i++)
      {
         result[i] = dt.Columns[columnNameList[i]].Ordinal;
      }

   }
   return result;
}

This little piece of magic uses an instance of a DataTable, and returns an array of pointers to the ordinal position of the desired column names. The desired column names, and the order in which they are desired is provided in an array of strings as a parameter to the method, columnNameList. If a column name can’t be found in the table an error is raised, and your program will have to handle that issue.

To use this method, the following code will specify four columns to resolve, and will locate those columns in the Columns collection of my sample DataTable, dt. What is really cool about this method is that it doesn’t matter what order the columns are found in the data table. It only matters if the DataTable being resolved has all of the column names found in the list of column names to be resolved.

static string[] COLUMN_LIST = { "Id", "FirstName", "LastName", "Birthdate" };

var columnPointers = dt.GetColumnPointers(COLUMN_LIST);

I have a .Net Fiddle posted for you to see this in action found at https://dotnetfiddle.net/v8yujj. In that example I create two different DataTables, each with the desired column names. However, the second table has additional column names, and the original four are not all in the same ordinal position as the first DataTable. As you can see, the code doesn’t care what order is used for the columns. It just matters if all of the desired column names exist in the data table.

When you’re done you have accomplished two things. You have an implementation that provides the best performance while resolving the ordinal positions dynamically, once for the entire DataTable contents.

Cheers,

Ben