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

Question

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.

Answer 1

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;
}

Answer 2

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.