C# data types to SQL Server data types

Question

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

i.e:

C# -> "String"
SQL Server -> N'String'

Answer 1

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?);

                default:
                    throw new ArgumentOutOfRangeException("sqlType");
            }
        }
    }
}

Answer 2

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.)

EDIT

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).