Converting `Type` to `Nullable`

Question

I am working on reading a set of results, but running into issues where a database may return a nullable version of a type, such as a double or int.

I am wondering if it's possible to use the schema information from the reader to convert the type definition to a nullable version. such as double? or int??

All the SQL stuff aside, is there a way to do this kind of type conversion in general? From a Type object to a Nullable<Type>.

using (SqlConnection connection = new SqlConnection("... connection string here ..."))
using (SqlCommand command = connection.CreateCommand())
{
    command.CommandText = ".... some sql here ....";

    var results = new DataTable(schema.TableName);

    using (var reader = await command.ExecuteReaderAsync())
    using (var schema = reader.GetSchemaTable())
    {
        for (int i = 0; i < schema.Rows.Count; i++)
        {
            var name = (string)schema.Rows[i]["ColumnName"];
            var type = (Type)schema.Rows[i]["DataType"];
            var allowNulls = (bool)schema.Rows[i]["AllowDBNull"];

            if (allowNulls)
            {
                // --- How do we turn `type` into a nullable version?
                //  Int32 => Nullable<Int32>
                //  Double => Nullable<Double>
                //  ... etc ...
            }

            var column = new DataColumn(name, type);
            results.Columns.Add(column);
        }
    }
}

Close, but not quite -- I'm trying to create accurate type information from the provided schema. The schema returns `double` as the type, when really `double?` would be more accurate. — jocull, Jul 07 '15 at 01:24

score 5 · Accepted Answer · answered Jul 07 '15 at 01:33

5

Please use the following function:

public Type GetNullableTypeFrom(Type type)
{
    if (!type.IsValueType || type.IsGenericType)
        return type;

    var nullableType = typeof(Nullable<>).MakeGenericType(type);

    return nullableType;
}

It will convert your type into a nullable one if the source type isn't, otherwise just leave it as it is.

if (allowNulls)
{
    type = GetNullableTypeFrom(type);
}

answered Jul 07 '15 at 01:33

constantine g

169
5

This is the proper way to do this. However, it turns out that you can't store nullable types in a `DataTable` anyways. I suppose you are supposed to save them as `DBNull.Value` instead. See this post: http://stackoverflow.com/a/701261/97964 – jocull Sep 08 '15 at 17:47

Eric · Answer 2 · 2015-07-08T01:26:56.050

typeof(Nullable<>).MakeGenericType(type); is the key to get the nullable type

for (int i = 0; i < schema.Rows.Count; i++)
{
    var name = (string)schema.Rows[i]["ColumnName"];
    var allowNulls = (bool)schema.Rows[i]["AllowDBNull"];
    Type type = (Type)schema.Rows[i]["DataType"];

    // Add a condition to check value type. e.g. string should be non-nullable
    // SQL data type should be all non-generic, skip check
    if (allowNulls && type.IsValueType)
    {
         type = typeof(Nullable<>).MakeGenericType(type);
    }

}

Converting `Type` to `Nullable`

2 Answers2