What is the C# equivalent to VB.NET's CType Function for use with generic types?

Question

Assume I have a table called MyTable in a SQL Server database called MyDB with the following three records:

|ID|MyVarcharColumn|
--------------------
|1|123|
|2|2014-10-01 9:58 AM|
|3|True|

Now assume I have the following VB.NET code:

Public Shared Sub Test()
    Dim myIntValue As Integer = GetValue(Of Integer)(1)
    Dim myDateValue As Date = GetValue(Of Date)(2)
    Dim myBooleanValue As Boolean = GetValue(Of Boolean)(3)
End Sub

Public Shared Function GetValue(Of T)(ByVal id As Integer) As T
    Dim ds As System.Data.DataSet = GetSQLDataSet(String.Format("SELECT MyVarcharColumn FROM MyTable WHERE ID={0}", id))
    Return CType(ds.Tables(0).Rows(0).Item("MyVarcharColumn"), T)
End Function

Public Shared Function GetSQLDataSet(ByVal ForQuery As String) As DataSet
    GetSQLDataSet = New DataSet()
    Dim conn As New System.Data.SqlClient.SqlConnection("Data Source=MyDBServer;Initial Catalog=MyDB;Integrated Security=True")
    Dim cmd As New System.Data.SqlClient.SqlCommand(ForQuery, conn)
    conn.Open()
    Dim da As New System.Data.SqlClient.SqlDataAdapter(cmd)
    da.Fill(GetSQLDataSet)
    conn.Close()
End Function

I can call Test() and everything runs fine. Notably, the use of VB.NET's CType function somehow succeeds in converting (not casting, because the underlying database column value is a string) into the correct type.

Now if I'm to believe things like Correct C# conversion counterpart for VB's CTYPE() and How do I translate VB.Net's CType() to C#, I should be able to use the following C# code:

static void Main(string[] args)
{
    int myIntValue = GetValue<int>(1);
    DateTime myDateValue = GetValue<DateTime>(2);
    bool myBooleanValue = GetValue<bool>(3);
}

static T GetValue<T>(int id)
{
    System.Data.DataSet ds = GetSQLDataSet(String.Format("SELECT MyVarcharColumn FROM MyTable WHERE ID={0}", id));
    return (T)ds.Tables[0].Rows[0]["MyVarcharColumn"];
}

public static System.Data.DataSet GetSQLDataSet(string forQuery)
{
    System.Data.DataSet ret = new System.Data.DataSet();
    System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("Data Source=MyDBServer;Initial Catalog=MyDB;Integrated Security=True");
    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(forQuery, conn);
    System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
    conn.Open();
    da.Fill(ret);
    conn.Close();
    return ret;
}

However, I'm getting an error at runtime that fails on return (T)ds.Tables[0].Rows[0]["MyVarcharColumn"]; saying "Specified cast is not valid".

I understand that I'm not casting because the return type is a string, so what I actually need is a conversion. In VB.NET, the CType function serves both roles; however, in C#, it would seem I'm stuck using one of the explicitly-typed conversion functions like Convert.ToInt32(). Since I'm dealing with a generic type here, that doesn't work.

I guess if I could boil this down into a single question, it would be: in C#, how do I convert a string to a generic type like VB.NET's CType does?

Answer 1

If you only need to support a very specific set of types that are supported by it, then Convert.ChangeType may be okay:

object value = (string) ds.Tables[0].Rows[0]["MyVarcharColumn"];
return (T) Convert.ChangeType(value, typeof(T));

You might want to consider specifying a format provider as well. I would strongly encourage you to avoid this sort of code though. What representation are you expecting for double values, for example? What about DateTime? Ideally, you would have these stored in different columns in SQL so that you could avoid having to do any conversion yourself - but if you must use a string, I'd probably make it very explicit for both the formatting and parsing, e.g. using ISO-8601 format for DateTime values. Your current approach makes it very fragile to cultural issues.

I'd also strongly advise you to stop building SQL dynamically. Even for integers it's not ideal as you're not specifying the format provider, but using parameterized SQL in all cases is just a good habit to get into.