C# - convert strings such as "uniqueidentifier", "timestamp", "image", "money", "sql_variant" etc. into .net data-types

How to convert strings such as "uniqueidentifier", "timestamp", "image", "money", "sql_variant" etc. into respective .net data-types?

I need to query a table to find out its contained fields and then accumulate those into a list List<Column>. Column is a class written by me. So I need this mechanism.

From stackoverflow

• Simply work out what you want the types to be (which may vary by your database) and use a dictionary:

  static reaodnly Dictionary<string, Type> NameToTypeMap = 
  new Dictionary<string, Type>
  { 
      { "uniqueidentifier", typeof(Guid) },
      { "timestamp", typeof(DateTimeOffset) },
      { "image", typeof(byte[]) },
      // etc
  };
  

  Note that this is assuming you're using C# 3, as it uses a collection initializer. Let me know if you're not using C# 3.

  EDIT: Here's the C# 2 code:

  static Dictionary<string, Type> NameToTypeMap = GetTypeMap();
  
  private static Dictionary<string, Type> GetTypeMap()
  { 
      Dictionary<string, Type> ret = new Dictionary<string, Type>();
      ret["uniqueidentifier"] = typeof(Guid);
      ret["timestamp"] = typeof(DateTimeOffset);
      ret["image"] = typeof(byte[]);
      // etc
      return ret;
  }
  

  JMSA : I am using .net 2.0 and vs2005.

  JMSA : Where can I find this conversion list? I.e. how did you know that timestamp should be converted into DateTimeOffset?

  Jon Skeet : Okay, will write the C# 2 code when I've had breakfast :)

  LukeH : @JMSA: Here's a handy chart showing conversions between SQL Server and CLR types: http://msdn.microsoft.com/en-us/library/bb386947.aspx#BehaviorMatrix

  JMSA : @Luke, thanks for the link.

• Run a query with the target columns using ExecuteReader to get a SqlDataReader, then use GetFieldType (returns the .NET datatype) and GetDataTypeName (returns the corresponding SQL server type name). The actual type mapping is done internally to the SqlClient- you could use reflection to access it, and hope it doesn't change between .NET versions, but I wouldn't recommend it.