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

Answer 3

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(whateverobject))); 
// 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 } );

Answer 4

This is an old, but still valid, question, so here's a different way. Someone already did this coding. If you execute a query similar to this...

select

    @@serverName  as [Varchar],
    GetDate()     as [DateTime],
    3.14159       as [Decimal],
    256           as [Int],
    :
    :
    -- And So On

...your database driver will do the data type mapping for you. You just have to retrieve the data and access the resulting column definitions. The column name will be the DBMS data type (name) and the column data type will be the data type for whatever language in which you are working. Your query can be limited to the specific DBMS data types you care about. Note that this is not limited to any particular database management system. One caveat is that this doesn't tell you which data types require length parameters.