Take the 2-minute tour ×

How can I "convert" C# datatype to SQL Server datatype (the SqlDbType is known)


C# -> "String"
SQL Server -> N'String'
share|improve this question
share the code where you need this. –  Romil Jun 12 '12 at 13:20
Except you have some specific code generation application based on a SQL Server database, this is usually not needed : ADO.NET do it automatically when adding parameters to a query. Please share the code so we can check this. –  Larry Jun 12 '12 at 13:22
what are you trying to achieve? are you looking to generate your DB from classes? If so, look at Entity Framework, Code-First. codeproject.com/Articles/318010/… and look at FluentAPI. This is a very powerful way to create a DB from your classes. –  christiandev Jun 12 '12 at 13:23
Does you found problem insert different language data in database ? –  Jayesh Sorathia Jun 12 '12 at 13:24
Why you don't use parameters. Then you don't have to think about this issue –  hwcverwe Jun 12 '12 at 13:26
show 1 more comment

3 Answers

First, you can get the mapping list from MSDN. They can be found here.

Then, simply create a hash table (HashTable) to look up one type and convert it to the relevant SqlDbType. Something like this:

private static types = new HashTable<Type, SqlDbType>();
public static SqlDbType GetSqlDbType(Type type)
    if (types == null)
        var types = new HashTable<Type, SqlDbType>();
        types.Add(int.GetType(), SqlDbType.Int);
        // And so forth...

    return types[type];


Or words to that effect. Naturally, refactor for StyleCop compliance and maintainability. (I wrote this off the top of my head without an IDE and after only one cup of coffee.)


Note that this can get fuzzy when dealing with resolution of .NET strings. Is it a varchar, an nvarchar, a text, or a memo? In those cases, this method won't have any way of knowing, and you will likely have to make a more informed decision after invoking the method based on the table and column name (or procedure and parameter name).

share|improve this answer
Mike nice answer but he probably wants to know how to use a value in a query. So NVarChar uses N'yourvalue' an Integer is just 1 a boolean must be converted to a Bit and so forth –  hwcverwe Jun 12 '12 at 13:43
What you are assuming he's asking and what he is asking are two very different things. He explicitly asked for a conversion between a CLR type and a SqlDbType. That's what I gave him. –  Mike Hofer Jun 12 '12 at 16:11
add comment

I don't know how to do conversions like that but probably there is a better way:

I assume you want to insert a value using C# into a SQL query.

Why you don't use parameters?

Instead of:

SqlCommand command = 
    new SqlCommand(String.Format(
       "SELECT * FROM MyTable WHERE mycolumn = {0}", 
// ConvertValueToSql is what you are asking basically

Try to do this:

// Conversion list of used types
Dictionary<Type, SqlDbType> typeConversion = new Dictionary<Type, SqlDbType>();
typeConversion.Add(typeof(String), SqlDbType.NVarChar);
typeConversion.Add(typeof(Int32), SqlDbType.Integer);
// you can even do this if you want
typeConversion.Add(typeof(MyCustomImageClass), SqlDbType.VarBinary);
typeConversion.Add(typeof(MyOtherNiceClass), SqlDbType.NVarChar);

// In the method
SqlCommand command = new SqlCommand("SELECT * FROM MyTable WHERE mycolumn = @Value");
command.Parameters.Add(new SqlParameter("@Value", typeConversion[whateverobject.GetType()]) { Value = whateverobject } );
share|improve this answer
add comment

Try This: Its a Extension class, so on the file you want to use these methods on add:

using Utility;

Here is the code:

using System;
using System.Data;
using Microsoft.SqlServer.Server;

namespace Utility
    public static class TypeExtension
        public static SqlDbType ToSqlDbType(this Type clrType)
            var s = new SqlMetaData("", SqlDbType.NVarChar, clrType);
            return s.SqlDbType;

        public static Type ToClrType(SqlDbType sqlType)
            switch (sqlType)
                case SqlDbType.BigInt:
                    return typeof (long?);

                case SqlDbType.Binary:
                case SqlDbType.Image:
                case SqlDbType.Timestamp:
                case SqlDbType.VarBinary:
                    return typeof (byte[]);

                case SqlDbType.Bit:
                    return typeof (bool?);

                case SqlDbType.Char:
                case SqlDbType.NChar:
                case SqlDbType.NText:
                case SqlDbType.NVarChar:
                case SqlDbType.Text:
                case SqlDbType.VarChar:
                case SqlDbType.Xml:
                    return typeof (string);

                case SqlDbType.DateTime:
                case SqlDbType.SmallDateTime:
                case SqlDbType.Date:
                case SqlDbType.Time:
                case SqlDbType.DateTime2:
                    return typeof (DateTime?);

                case SqlDbType.Decimal:
                case SqlDbType.Money:
                case SqlDbType.SmallMoney:
                    return typeof (decimal?);

                case SqlDbType.Float:
                    return typeof (double?);

                case SqlDbType.Int:
                    return typeof (int?);

                case SqlDbType.Real:
                    return typeof (float?);

                case SqlDbType.UniqueIdentifier:
                    return typeof (Guid?);

                case SqlDbType.SmallInt:
                    return typeof (short?);

                case SqlDbType.TinyInt:
                    return typeof (byte?);

                case SqlDbType.Variant:
                case SqlDbType.Udt:
                    return typeof (object);

                case SqlDbType.Structured:
                    return typeof (DataTable);

                case SqlDbType.DateTimeOffset:
                    return typeof (DateTimeOffset?);

                    throw new ArgumentOutOfRangeException("sqlType");
share|improve this answer
add comment

Your Answer


