.NET System Type to SqlDbType

Question

I was looking for a smart conversion between .Net System.Type and SqlDbType. What I found it was the following idea:

private static SqlDbType TypeToSqlDbType(Type t)
{
    String name = t.Name;
    SqlDbType val = SqlDbType.VarChar; // default value
    try
    {
        if (name.Contains("16") || name.Contains("32") || name.Contains("64"))
            {
                name = name.Substring(0, name.Length - 2);
            }
            val = (SqlDbType)Enum.Parse(typeof(SqlDbType), name, true);
        }
        catch (Exception)
        {
            // add error handling to suit your taste
        }

        return val;
    }

The code above is not really nice and is a code smell, which is why I wrote the following, naïve, not smart, but useful function, based on https://msdn.microsoft.com/en-us/library/cc716729(v=vs.110).aspx:

   public static SqlDbType ConvertiTipo(Type giveType)
    {
       var typeMap = new Dictionary<Type, SqlDbType>();

        typeMap[typeof(string)] = SqlDbType.NVarChar;
        typeMap[typeof(char[])] = SqlDbType.NVarChar;
        typeMap[typeof(int)] = SqlDbType.Int;
        typeMap[typeof(Int32)] = SqlDbType.Int;
        typeMap[typeof(Int16)] = SqlDbType.SmallInt;
        typeMap[typeof(Int64)] = SqlDbType.BigInt;
        typeMap[typeof(Byte[])] = SqlDbType.VarBinary;
        typeMap[typeof(Boolean)] = SqlDbType.Bit;
        typeMap[typeof(DateTime)] = SqlDbType.DateTime2;
        typeMap[typeof(DateTimeOffset)] = SqlDbType.DateTimeOffset;
        typeMap[typeof(Decimal)] = SqlDbType.Decimal;
        typeMap[typeof(Double)] = SqlDbType.Float;
        typeMap[typeof(Decimal)] = SqlDbType.Money;
        typeMap[typeof(Byte)] = SqlDbType.TinyInt;
        typeMap[typeof(TimeSpan)] = SqlDbType.Time;

        return typeMap[(giveType)];
     }

Does someone have idea of how to get the same result in a cleaner, better and nice way?

Making dictionary conversion is OK. Done *once* in a life time. :) (less there is a change) — Ian, Mar 02 '16 at 10:58

Anders Marzi Tornblad · Accepted Answer · 2017-03-23T08:59:17.370

Your approach is a good start, but populating that dictionary should only be done once, as Ian says in a comment.

There is a GIST here that is based on the same idea, although it doesn't convert between the same sets of types: https://gist.github.com/abrahamjp/858392

Caveat

I have a working example below, but you need to be aware that this approach does have a few problems. For example:

For a string, how do you pick the correct one between Char, NChar, VarChar, NVarChar, Text or NText (or even Xml, maybe)?
And for blobs like byte[], should you use Binary, VarBinary or Image?
For decimal, float and double, should you go for Decimal, Float, Money, SmallMoney or Real?
For a DateTime, do you need DateTime2, DateTimeOffset, DateTime, or SmallDateTime?
Are you using Nullable types, like int?? Those should most likely give the same SqlDbType as the underlying type.

Also, just providing a Type tells you nothing of other constraints, like field size and precision. Making the right decision is also about how the data is used in your application and how it is stored in the database.

The best thing to do is really to let an ORM do this for you.

Code

public static class SqlHelper
{
    private static Dictionary<Type, SqlDbType> typeMap;

    // Create and populate the dictionary in the static constructor
    static SqlHelper()
    {
        typeMap = new Dictionary<Type, SqlDbType>();

        typeMap[typeof(string)]         = SqlDbType.NVarChar;
        typeMap[typeof(char[])]         = SqlDbType.NVarChar;
        typeMap[typeof(byte)]           = SqlDbType.TinyInt;
        typeMap[typeof(short)]          = SqlDbType.SmallInt;
        typeMap[typeof(int)]            = SqlDbType.Int;
        typeMap[typeof(long)]           = SqlDbType.BigInt;
        typeMap[typeof(byte[])]         = SqlDbType.Image;
        typeMap[typeof(bool)]           = SqlDbType.Bit;
        typeMap[typeof(DateTime)]       = SqlDbType.DateTime2;
        typeMap[typeof(DateTimeOffset)] = SqlDbType.DateTimeOffset;
        typeMap[typeof(decimal)]        = SqlDbType.Money;
        typeMap[typeof(float)]          = SqlDbType.Real;
        typeMap[typeof(double)]         = SqlDbType.Float;
        typeMap[typeof(TimeSpan)]       = SqlDbType.Time;
        /* ... and so on ... */
    }

    // Non-generic argument-based method
    public static SqlDbType GetDbType(Type giveType)
    {
        // Allow nullable types to be handled
        giveType = Nullable.GetUnderlyingType(giveType) ?? giveType;

        if (typeMap.ContainsKey(giveType))
        {
            return typeMap[giveType];
        }

        throw new ArgumentException($"{giveType.FullName} is not a supported .NET class");
    }

    // Generic version
    public static SqlDbType GetDbType<T>()
    {
        return GetDbType(typeof(T));
    }
}

And this is how you would use it:

var sqlDbType = SqlHelper.GetDbType<string>();
// or:
var sqlDbType = SqlHelper.GetDbType(typeof(DateTime?));
// or:
var sqlDbType = SqlHelper.GetDbType(property.PropertyType);

Looks good! I would only add a check to see if the type exists (`ContainsKey`) in the dictionary and if not throw a `NotSupportedException` (or custom exception) with your own detailed message instead of the default `KeyNotFoundException`. This might make for easier troubleshooting later if a non supported type is ever passed in. — Igor, Mar 02 '16 at 11:37
Thanks for the tip. I have edited the answer to throw an `ArgumentException`, as the `NotSupportedException` is not meant for this type of thing. — Anders Marzi Tornblad, Mar 02 '16 at 12:49

score 11 · Answer 2 · answered Dec 12 '17 at 18:54

It appears that this sort of lookup table is already available, albeit not in System.Data (or .Object or .Type) but rather in System.Web.

Project -> Add Reference -> System.Web -> OK

Then https://msdn.microsoft.com/en-us/library/system.data.sqldbtype(v=vs.110).aspx also says

When setting command parameters, the SqlDbType and DbType are linked. Therefore, setting the DbType changes the SqlDbType to a supporting SqlDbType.

So, this should theoretically work;)

using Microsoft.SqlServer.Server; // SqlDataRecord and SqlMetaData
using System;
using System.Collections; // IEnumerator and IEnumerable
using System.Collections.Generic; // general IEnumerable and IEnumerator
using System.Data; // DataTable and SqlDataType
using System.Data.SqlClient; // SqlConnection, SqlCommand, and SqlParameter
using System.Web.UI.WebControls; // for Parameters.Convert... functions

private static SqlDbType TypeToSqlDbType(Type t) {
    DbType dbtc = Parameters.ConvertTypeCodeToDbType(t.GetTypeCodeImpl());
    SqlParameter sp = new SqlParameter();
    // DbParameter dp = new DbParameter();
    // dp.DbType = dbtc;
    sp.DbType = dbtc;
    return sp.SqlDbType;
}

Thank you this is perfect information for what I needed! – SnookerC Jun 08 '18 at 15:58 — SnookerC, Jun 08 '18 at 15:58

score 3 · Answer 3 · answered Mar 11 '19 at 23:34

3

My office mate gave me the idea to try a property of SqlParameter:

Func<Object, SqlDbType> getSqlType = val => new SqlParameter("Test", val).SqlDbType;
Func<Type, SqlDbType> getSqlType2 = type => new SqlParameter("Test", type.IsValueType?Activator.CreateInstance(type):null).SqlDbType;

//returns nvarchar...
Object obj = "valueToTest";
getSqlType(obj).Dump();
getSqlType2(typeof(String)).Dump();

//returns int...
obj = 4;
getSqlType(obj).Dump();
getSqlType2(typeof(Int32)).Dump();

//returns bigint...
obj = Int64.MaxValue;
getSqlType(obj).Dump();
getSqlType2(typeof(Int64)).Dump();

https://dotnetfiddle.net/8heM4H

answered Mar 11 '19 at 23:34

b_levitt

7,059
2
41
56

Note that this doesn't handle nullable types. – Ian Kemp Jun 03 '20 at 14:25
1

In what way? I casted some of the literals (ie (int?)4) and it seems to work - still returns int as the sql data type. Remember this is getting the equivalent data type in sql. Unlike .net where you really have two types, nullability is inherint in the data type and then is denied via contraint. – b_levitt Jun 03 '20 at 15:41

Mladen Prajdic · Answer 4 · 2016-03-02T13:31:12.887

-1

Edit: I was thinking about and this works for System.Data.SqlTypes types. I'll leave it here just in case it helps someone in the future.

I do something like this:

object objDbValue = DbReader.GetValue(columnIndex);
Type sqlType = DbReader.GetFieldType(columnIndex);
Type clrType = null;

if (sqlType.Name.StartsWith("Sql"))
{   
    var objClrValue = objDbValue.GetType()
                                .GetProperty("Value")
                                .GetValue(objDbValue, null);
    clrType = objClrValue.GetType();
}

Because every SqlDbType has a .Value property which is the actual underlying CLR type I use reflection to get it. It's too bad SqlDbType doesn't have some interface that would hold this .Value property and reflection wouldn't be needed.
It's not perfect but you don't have to manually create, maintain or populate a dictionary. You can just look up a type in an existing dict, and if it doesn't exist use the upper method to add the mapping automatically. Pretty much auto-generated.
Also takes care of any new types that SQL Server might receive in the future.

edited Mar 02 '16 at 13:31

answered Mar 02 '16 at 13:04

Mladen Prajdic

15,457
2
43
51

"Edited: Not sure where the comment I was replying to went." ah, you're right, for the other direction i don't have a better answer than a pre-populated dictionary. Although usually the use case is from sql type to clr type because one sql type can map to multiple clr types. – Mladen Prajdic Mar 02 '16 at 13:14
1

Looks like [SqlDbType](https://msdn.microsoft.com/en-us/library/system.data.sqldbtype(v=vs.110).aspx) is an enumeration so I am not sure how that holds additional information about a CLR Type. Also for creating queries it would not work, only for translating results from a query to the correct CLR type. – Igor Mar 02 '16 at 13:15
Sorry, I deleted my comment right away because I wanted to rethink a little. My initial comment was *"Doesn't this go the opposite direction?"*. Now I'm more with @Igor, as the desired `SqlDbType` is an enumeration. – Anders Marzi Tornblad Mar 02 '16 at 13:17
you're right, I was constantly thinking of System.Data.SqlTypes namespace. https://msdn.microsoft.com/en-us/library/system.data.sqltypes(v=vs.110).aspx my bad. – Mladen Prajdic Mar 02 '16 at 13:29

.NET System Type to SqlDbType

4 Answers4

Linked