Today, I was looking for a way to take a string representation of a SqlDbType, i.e. System.Data.SqlDbType.VarChar and actually return the enumeration value (System.Data.SqlDbType is an enumeration of valid SQL data types like VarChar, Char, Int, etc.). Of course I could have done something like:

public static SqlDbType GetSqlDbType(string sqlDbType)
{
    switch (sqlDbType)
    {
	case "varchar":
		return SqlDbType.VarChar;
	case "char":
		return SqlDbType.Char;
	case "int":
		return SqlDbType.Int;	
	case "bigint":
		return SqlDbType.BigInt;
	default:
		throw new Exception(String.Format("{0} is either an unknown or unsupported SqlDbType.", sqlDbType));
    }
}

The drawback to this solution is that I would need a giant switch statement for all of the possible SqlDbType possibilities. Plus, what if for some magical reason, a new one is added? So I turned to Google to help me find a more elegant example, and I found what I was looking for:

SqlDbType sdt = (SqlDbType)Enum.Parse(typeof(SqlDbType), "VarChar", true);

This allows you to find the SqlDbType value you are looking for by using reflection on the SqlDbType enumeration. Nice! Of course, I wanted a more complete solution for my needs, so I came up with something like this (based on the first example):

public static SqlDbType GetSqlDbType(string sqlDbType)
{
    if (!String.IsNullOrEmpty(sqlDbType))
    {
	try
	{
	    string hintText = sqlDbType.Substring(sqlDbType.LastIndexOf(".") + 1);

	    SqlDbType sdt = (SqlDbType)Enum.Parse(typeof(SqlDbType), hintText, true);
	    return sdt;
	}
	catch (ArgumentException)
	{
	    throw new Exception(String.Format("{0} is an unknown SqlDbType.", sqlDbType));
	}
	catch (Exception e)
	{
	    throw e;
	}
    }

    throw new Exception("Provided value for SqlDbType was null or empty.");
}

This lets us do a couple of important things. First, it allows for support of the complete namespace for a SqlDbType, i.e. System.Data.SqlDbType.VarChar AND just VarChar. Second, it lets us catch important errors like an unknown SqlDbType value that is passed in to GetSqlDbType.

That’s it! Enjoy! If anyone has any questions or comments, you know what to do…