Determine if DataColumn is numeric

Question

Is there a better way than this to check if a DataColumn in a DataTable is numeric (coming from a SQL Server database)?

  Database db = DatabaseFactory.CreateDatabase();
  DbCommand cmd = db.GetStoredProcCommand("Get_Some_Data");
  DataSet ds = db.ExecuteDataSet(cmd);

  foreach (DataTable tbl in ds.Tables) {
    foreach (DataColumn col in tbl.Columns) {
      if (col.DataType == typeof(System.Single)
        || col.DataType == typeof(System.Double)
        || col.DataType == typeof(System.Decimal)
        || col.DataType == typeof(System.Byte)
        || col.DataType == typeof(System.Int16)
        || col.DataType == typeof(System.Int32)
        || col.DataType == typeof(System.Int64)) {
        // this column is numeric
      } else {
        // this column is not numeric
      }
    }
  }

Dmytrii Nagirniak · Accepted Answer · 2009-11-13T00:41:03.537

44

There is no good way to check if the type is numeric except comparing it to the actual types.
This is especially true if the definition of numeric is a bit different (in your case, according to code, - unsigned integers are not numerics).

Another thing is that DataColumn.DataType according to MSDN only supports following types:

Boolean
Byte
Char
DateTime
Decimal
Double
Int16
Int32
Int64
SByte
Single
String
TimeSpan
UInt16
UInt32
UInt64
Byte[]

The bolded types are numerics (as I define it) so you need to make sure you check them.

I personally would write an extension method for the DataColumn type (not for the TYPE!).
I hate the if...then..else thing so instead I use a SETS-based approach, like this:

public static bool IsNumeric(this DataColumn col) {
  if (col == null)
    return false;
  // Make this const
  var numericTypes = new [] { typeof(Byte), typeof(Decimal), typeof(Double),
        typeof(Int16), typeof(Int32), typeof(Int64), typeof(SByte),
        typeof(Single), typeof(UInt16), typeof(UInt32), typeof(UInt64)};
  return numericTypes.Contains(col.DataType);
}

And the usage would be:

if (col.IsNumeric()) ....

which is easy enough for me

edited Nov 13 '09 at 00:41

answered Nov 12 '09 at 23:04

Dmytrii Nagirniak

23,696
13
75
130

I didn't include the unsigned integer types because they weren't listed in http://msdn.microsoft.com/en-us/library/ms131092%28SQL.90%29.aspx but I do like your approach. – JustinStolle Nov 12 '09 at 23:11
@JustinStolle, I would better include the unsigned types according to the MSDN page I provided. The page you reference is the SQL Server 2005 specific. – Dmytrii Nagirniak Nov 12 '09 at 23:14
1

@Dmitriy, makes sense, thank you! FYI, you are missing a comma after "typeof(Double)" in your code sample. – JustinStolle Nov 12 '09 at 23:20
Fixed that. That's the copy-paste's fault, not mine :) – Dmytrii Nagirniak Nov 13 '09 at 00:42
Targeting .NET 2.0 I had to wrap the Array in an ArrayList to use the Contains method. – Vincent De Smet Jan 06 '10 at 03:44
2

@Vincent, you don't need ArrayList. You can just do `Array.IndexOf(numericTypes, col.DataType) != -1` – Dmytrii Nagirniak Jan 10 '10 at 22:02
This saved me some sanity! I also'd like to note, depending on your implementation and the source of the data, you may wish to add this before the second return statement: `if (col.DataType.IsArray) return numericTypes.Contains(col.DataType.GetElementType());` For example, if I have a DataTable filled with the results of an `ODBCDataReader` that came from querying an SQL Server 08 database, a column typed as `timestamp` on the server is translated into a `System.Byte[]` in the DataTable. – p0lar_bear Jun 20 '12 at 19:53

score 2 · Answer 2 · answered Sep 23 '13 at 10:53

Another method without using Arrays, just by one line of code:

return col != null && "Byte,Decimal,Double,Int16,Int32,Int64,SByte,Single,UInt16,UInt32,UInt64,".Contains(col.DataType.Name + ",");

This line of code can be used either as a normal helper method or as an extension method.

score 1 · Answer 3 · answered Nov 12 '09 at 22:51

1

Maybe you could make it shorter with:

System.Type theType = col.DataType AS System.Type
if(theType  == System.Single || theType  == System.Double...) {}

answered Nov 12 '09 at 22:51

Jonathan

11,809
5
57
91

Determine if DataColumn is numeric

3 Answers3

Linked