Click here to Skip to main content
15,867,308 members
Articles / Programming Languages / C#

Clr Type To SqlDbType Mapper for C#

Rate me:
Please Sign up or sign in to vote.
4.25/5 (3 votes)
14 Dec 2015CPOL 11.3K   4   2
As part of the refactoring work for my stored procedure framework project, I needed to extract the conversion of CLR Types to `SqlDbTypes` out into a dedicated helper class. this has given me a simple mapper class which I'd like to share.

As part of the refactoring work for my stored procedure framework project, I needed to extract the conversion of CLR Types to `SqlDbTypes` out into a dedicated helper class. this has given me a simple mapper class which I'd like to share.

The class is a static class with a single public member `GetSqlDbTypeFromClrType` which takes a `Type` and returns a `SqlDbType`. Most of the common DataTypes have been covered but feel free to take the code and extend as you feel fit.

using System;
using System.Collections.Generic;
using System.Data;

namespace Dibware.StoredProcedureFramework.Helpers
{
    public static class ClrTypeToSqlDbTypeMapper
    {
        #region Constructors

        /// <summary>
        /// Initializes the <see cref="ClrTypeToSqlDbTypeMapper"/> class.
        /// </summary>
        static ClrTypeToSqlDbTypeMapper()
        {
            CreateClrTypeToSqlTypeMaps();
        }

        #endregion

        #region Public  Members

        /// <summary>
        /// Gets the mapped SqlDbType for the specified CLR type.
        /// </summary>
        /// <param name="clrType">The CLR Type to get mapped SqlDbType for.</param>
        /// <returns></returns>
        public static SqlDbType GetSqlDbTypeFromClrType(Type clrType)
        {
            if (!_clrTypeToSqlTypeMaps.ContainsKey(clrType))
            {
                throw new ArgumentOutOfRangeException("clrType", @"No mapped type found for " + clrType);
            }

            SqlDbType result;
            _clrTypeToSqlTypeMaps.TryGetValue(clrType, out result);
            return result;
        }

        #endregion

        #region Private Members

        private static void CreateClrTypeToSqlTypeMaps()
        {
            _clrTypeToSqlTypeMaps = new Dictionary<Type, SqlDbType>
            {
                {typeof (Boolean), SqlDbType.Bit},
                {typeof (Boolean?), SqlDbType.Bit},
                {typeof (Byte), SqlDbType.TinyInt},
                {typeof (Byte?), SqlDbType.TinyInt},
                {typeof (String), SqlDbType.NVarChar},
                {typeof (DateTime), SqlDbType.DateTime},
                {typeof (DateTime?), SqlDbType.DateTime},
                {typeof (Int16), SqlDbType.SmallInt},
                {typeof (Int16?), SqlDbType.SmallInt},
                {typeof (Int32), SqlDbType.Int},
                {typeof (Int32?), SqlDbType.Int},
                {typeof (Int64), SqlDbType.BigInt},
                {typeof (Int64?), SqlDbType.BigInt},
                {typeof (Decimal), SqlDbType.Decimal},
                {typeof (Decimal?), SqlDbType.Decimal},
                {typeof (Double), SqlDbType.Float},
                {typeof (Double?), SqlDbType.Float},
                {typeof (Single), SqlDbType.Real},
                {typeof (Single?), SqlDbType.Real},
                {typeof (TimeSpan), SqlDbType.Time},
                {typeof (Guid), SqlDbType.UniqueIdentifier},
                {typeof (Guid?), SqlDbType.UniqueIdentifier},
                {typeof (Byte[]), SqlDbType.Binary},
                {typeof (Byte?[]), SqlDbType.Binary},
                {typeof (Char[]), SqlDbType.Char},
                {typeof (Char?[]), SqlDbType.Char}
            };
        }

        private static Dictionary<Type, SqlDbType> _clrTypeToSqlTypeMaps; // = new 

        #endregion
    }
}

The tests I have written for this class are shown below.
 

using System;
using System.Data;
using System.Text;
using Dibware.StoredProcedureFramework.Helpers;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace Dibware.StoredProcedureFramework.Tests.UnitTests.Helpers
{
    [TestClass]
    public class ClrTypeToSqlTypeMapperTests
    {
        [TestMethod]
        public void GetSqlDbTypeFromClrType_WhenGivenBooleanType_ReturnsBitSqlDbType()
        {
            // ARRANGE
            Type value = typeof (Boolean);
            const SqlDbType expectedSqlDbType = SqlDbType.Bit;

            // ACT
            SqlDbType actual = ClrTypeToSqlDbTypeMapper.GetSqlDbTypeFromClrType(value);
            
            // ASSERT
            Assert.AreEqual(expectedSqlDbType, actual);
        }

        [TestMethod]
        public void GetSqlDbTypeFromClrType_WhenGivenNullableBooleanType_ReturnsBitSqlDbType()
        {
            // ARRANGE
            Type value = typeof(Boolean?);
            const SqlDbType expectedSqlDbType = SqlDbType.Bit;

            // ACT
            SqlDbType actual = ClrTypeToSqlDbTypeMapper.GetSqlDbTypeFromClrType(value);

            // ASSERT
            Assert.AreEqual(expectedSqlDbType, actual);
        }

        [TestMethod]
        public void GetSqlDbTypeFromClrType_WhenGivenByteType_ReturnsTinyIntSqlDbType()
        {
            // ARRANGE
            Type value = typeof (Byte);
            const SqlDbType expectedSqlDbType = SqlDbType.TinyInt;

            // ACT
            SqlDbType actual = ClrTypeToSqlDbTypeMapper.GetSqlDbTypeFromClrType(value);
            
            // ASSERT
            Assert.AreEqual(expectedSqlDbType, actual);
        }

        [TestMethod]
        public void GetSqlDbTypeFromClrType_WhenGivenNullableByteType_ReturnsTinyIntSqlDbType()
        {
            // ARRANGE
            Type value = typeof(Byte?);
            const SqlDbType expectedSqlDbType = SqlDbType.TinyInt;

            // ACT
            SqlDbType actual = ClrTypeToSqlDbTypeMapper.GetSqlDbTypeFromClrType(value);

            // ASSERT
            Assert.AreEqual(expectedSqlDbType, actual);
        }

        [TestMethod]
        public void GetSqlDbTypeFromClrType_WhenGivenStringType_ReturnsNVarCharSqlDbType()
        {
            // ARRANGE
            Type value = typeof (String);
            const SqlDbType expectedSqlDbType = SqlDbType.NVarChar;

            // ACT
            SqlDbType actual = ClrTypeToSqlDbTypeMapper.GetSqlDbTypeFromClrType(value);

            // ASSERT
            Assert.AreEqual(expectedSqlDbType, actual);
        }

        [TestMethod]
        public void GetSqlDbTypeFromClrType_WhenGivenDateTimeType_ReturnsDateTimeSqlDbType()
        {
            // ARRANGE
            Type value = typeof (DateTime);
            const SqlDbType expectedSqlDbType = SqlDbType.DateTime;

            // ACT
            SqlDbType actual = ClrTypeToSqlDbTypeMapper.GetSqlDbTypeFromClrType(value);

            // ASSERT
            Assert.AreEqual(expectedSqlDbType, actual);
        }

        [TestMethod]
        public void GetSqlDbTypeFromClrType_WhenGivenNullableDateTimeType_ReturnsDateTimeSqlDbType()
        {
            // ARRANGE
            Type value = typeof(DateTime?);
            const SqlDbType expectedSqlDbType = SqlDbType.DateTime;

            // ACT
            SqlDbType actual = ClrTypeToSqlDbTypeMapper.GetSqlDbTypeFromClrType(value);

            // ASSERT
            Assert.AreEqual(expectedSqlDbType, actual);
        }

        [TestMethod]
        public void GetSqlDbTypeFromClrType_WhenGivenInt16Type_ReturnsSmallIntSqlDbType()
        {
            // ARRANGE
            Type value = typeof (Int16);
            const SqlDbType expectedSqlDbType = SqlDbType.SmallInt;

            // ACT
            SqlDbType actual = ClrTypeToSqlDbTypeMapper.GetSqlDbTypeFromClrType(value);

            // ASSERT
            Assert.AreEqual(expectedSqlDbType, actual);
        }

        [TestMethod]
        public void GetSqlDbTypeFromClrType_WhenGivenNullableInt16Type_ReturnsSmallIntSqlDbType()
        {
            // ARRANGE
            Type value = typeof(Int16?);
            const SqlDbType expectedSqlDbType = SqlDbType.SmallInt;

            // ACT
            SqlDbType actual = ClrTypeToSqlDbTypeMapper.GetSqlDbTypeFromClrType(value);

            // ASSERT
            Assert.AreEqual(expectedSqlDbType, actual);
        }

        [TestMethod]
        public void GetSqlDbTypeFromClrType_WhenGivenInt32Type_ReturnsIntSqlDbType()
        {
            // ARRANGE
            Type value = typeof (Int32);
            const SqlDbType expectedSqlDbType = SqlDbType.Int;

            // ACT
            SqlDbType actual = ClrTypeToSqlDbTypeMapper.GetSqlDbTypeFromClrType(value);

            // ASSERT
            Assert.AreEqual(expectedSqlDbType, actual);
        }

        [TestMethod]
        public void GetSqlDbTypeFromClrType_WhenGivenNullableInt32Type_ReturnsIntSqlDbType()
        {
            // ARRANGE
            Type value = typeof(Int32?);
            const SqlDbType expectedSqlDbType = SqlDbType.Int;

            // ACT
            SqlDbType actual = ClrTypeToSqlDbTypeMapper.GetSqlDbTypeFromClrType(value);

            // ASSERT
            Assert.AreEqual(expectedSqlDbType, actual);
        }

        [TestMethod]
        public void GetSqlDbTypeFromClrType_WhenGivenInt64Type_ReturnsBigIntSqlDbType()
        {
            // ARRANGE
            Type value = typeof (Int64);
            const SqlDbType expectedSqlDbType = SqlDbType.BigInt;

            // ACT
            SqlDbType actual = ClrTypeToSqlDbTypeMapper.GetSqlDbTypeFromClrType(value);

            // ASSERT
            Assert.AreEqual(expectedSqlDbType, actual);
        }

        [TestMethod]
        public void GetSqlDbTypeFromClrType_WhenGivenNullableInt64Type_ReturnsBigIntSqlDbType()
        {
            // ARRANGE
            Type value = typeof(Int64?);
            const SqlDbType expectedSqlDbType = SqlDbType.BigInt;

            // ACT
            SqlDbType actual = ClrTypeToSqlDbTypeMapper.GetSqlDbTypeFromClrType(value);

            // ASSERT
            Assert.AreEqual(expectedSqlDbType, actual);
        }

        [TestMethod]
        public void GetSqlDbTypeFromClrType_WhenGivenDecimalType_ReturnsDecimalSqlDbType()
        {
            // ARRANGE
            Type value = typeof(Decimal);
            const SqlDbType expectedSqlDbType = SqlDbType.Decimal;

            // ACT
            SqlDbType actual = ClrTypeToSqlDbTypeMapper.GetSqlDbTypeFromClrType(value);

            // ASSERT
            Assert.AreEqual(expectedSqlDbType, actual);
        }

        [TestMethod]
        public void GetSqlDbTypeFromClrType_WhenGivenNullableDecimalType_ReturnsDecimalSqlDbType()
        {
            // ARRANGE
            Type value = typeof(Decimal?);
            const SqlDbType expectedSqlDbType = SqlDbType.Decimal;

            // ACT
            SqlDbType actual = ClrTypeToSqlDbTypeMapper.GetSqlDbTypeFromClrType(value);

            // ASSERT
            Assert.AreEqual(expectedSqlDbType, actual);
        }

        [TestMethod]
        public void GetSqlDbTypeFromClrType_WhenGivenDoubleType_ReturnsFloatSqlDbType()
        {
            // ARRANGE
            Type value = typeof(Double);
            const SqlDbType expectedSqlDbType = SqlDbType.Float;

            // ACT
            SqlDbType actual = ClrTypeToSqlDbTypeMapper.GetSqlDbTypeFromClrType(value);

            // ASSERT
            Assert.AreEqual(expectedSqlDbType, actual);
        }

        [TestMethod]
        public void GetSqlDbTypeFromClrType_WhenGivenNullableDoubleType_ReturnsFloatSqlDbType()
        {
            // ARRANGE
            Type value = typeof(Double?);
            const SqlDbType expectedSqlDbType = SqlDbType.Float;

            // ACT
            SqlDbType actual = ClrTypeToSqlDbTypeMapper.GetSqlDbTypeFromClrType(value);

            // ASSERT
            Assert.AreEqual(expectedSqlDbType, actual);
        }

        [TestMethod]
        public void GetSqlDbTypeFromClrType_WhenGivenSingleType_ReturnsRealSqlDbType()
        {
            // ARRANGE
            Type value = typeof(Single);
            const SqlDbType expectedSqlDbType = SqlDbType.Real;

            // ACT
            SqlDbType actual = ClrTypeToSqlDbTypeMapper.GetSqlDbTypeFromClrType(value);

            // ASSERT
            Assert.AreEqual(expectedSqlDbType, actual);
        }

        [TestMethod]
        public void GetSqlDbTypeFromClrType_WhenGivenNullableSingleType_ReturnsRealSqlDbType()
        {
            // ARRANGE
            Type value = typeof(Single?);
            const SqlDbType expectedSqlDbType = SqlDbType.Real;

            // ACT
            SqlDbType actual = ClrTypeToSqlDbTypeMapper.GetSqlDbTypeFromClrType(value);

            // ASSERT
            Assert.AreEqual(expectedSqlDbType, actual);
        }

        [TestMethod]
        public void GetSqlDbTypeFromClrType_WhenGivenTimeSpanType_ReturnsTimeSqlDbType()
        {
            // ARRANGE
            Type value = typeof (TimeSpan);
            const SqlDbType expectedSqlDbType = SqlDbType.Time;

            // ACT
            SqlDbType actual = ClrTypeToSqlDbTypeMapper.GetSqlDbTypeFromClrType(value);

            // ASSERT
            Assert.AreEqual(expectedSqlDbType, actual);
        }

        [TestMethod]
        public void GetSqlDbTypeFromClrType_WhenGivenGuidType_ReturnsUniqueIdentifierSqlDbType()
        {
            // ARRANGE
            Type value = typeof (Guid);
            const SqlDbType expectedSqlDbType = SqlDbType.UniqueIdentifier;

            // ACT
            SqlDbType actual = ClrTypeToSqlDbTypeMapper.GetSqlDbTypeFromClrType(value);

            // ASSERT
            Assert.AreEqual(expectedSqlDbType, actual);
        }

        [TestMethod]
        public void GetSqlDbTypeFromClrType_WhenGivenNullableGuidType_ReturnsUniqueIdentifierSqlDbType()
        {
            // ARRANGE
            Type value = typeof(Guid?);
            const SqlDbType expectedSqlDbType = SqlDbType.UniqueIdentifier;

            // ACT
            SqlDbType actual = ClrTypeToSqlDbTypeMapper.GetSqlDbTypeFromClrType(value);

            // ASSERT
            Assert.AreEqual(expectedSqlDbType, actual);
        }

        [TestMethod]
        public void GetSqlDbTypeFromClrType_WhenGivenByteArrayType_ReturnsBinarySqlDbType()
        {
            // ARRANGE
            Type value = typeof (Byte[]);
            const SqlDbType expectedSqlDbType = SqlDbType.Binary;

            // ACT
            SqlDbType actual = ClrTypeToSqlDbTypeMapper.GetSqlDbTypeFromClrType(value);

            // ASSERT
            Assert.AreEqual(expectedSqlDbType, actual);
        }

        [TestMethod]
        public void GetSqlDbTypeFromClrType_WhenGivenNullableByteArrayType_ReturnsBinarySqlDbType()
        {
            // ARRANGE
            Type value = typeof(Byte?[]);
            const SqlDbType expectedSqlDbType = SqlDbType.Binary;

            // ACT
            SqlDbType actual = ClrTypeToSqlDbTypeMapper.GetSqlDbTypeFromClrType(value);

            // ASSERT
            Assert.AreEqual(expectedSqlDbType, actual);
        }

        [TestMethod]
        public void GetSqlDbTypeFromClrType_WhenGivenCharArrayType_ReturnsCharSqlDbType()
        {
            // ARRANGE
            Type value = typeof (Char[]);
            const SqlDbType expectedSqlDbType = SqlDbType.Char;

            // ACT
            SqlDbType actual = ClrTypeToSqlDbTypeMapper.GetSqlDbTypeFromClrType(value);

            // ASSERT
            Assert.AreEqual(expectedSqlDbType, actual);
        }

        [TestMethod]
        public void GetSqlDbTypeFromClrType_WhenGivenNullableCharArrayType_ReturnsCharSqlDbType()
        {
            // ARRANGE
            Type value = typeof(Char?[]);
            const SqlDbType expectedSqlDbType = SqlDbType.Char;

            // ACT
            SqlDbType actual = ClrTypeToSqlDbTypeMapper.GetSqlDbTypeFromClrType(value);

            // ASSERT
            Assert.AreEqual(expectedSqlDbType, actual);
        }

        [TestMethod]
        [ExpectedException(typeof(ArgumentOutOfRangeException))]
        public void GetSqlDbTypeFromClrType_WhenGivenUnexpectedType_THEN()
        {
            // ARRANGE
            Type value = typeof(StringBuilder);

            // ACT
            ClrTypeToSqlDbTypeMapper.GetSqlDbTypeFromClrType(value);

            // ASSERT
            // Exception should have been thrown by here
        }
    }
}

Gist

The full class and tests code is available at my `ClrTypeToSqlDbTypeMapper for C# .Net` Gist.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
United Kingdom United Kingdom
Duane has worked in a commercial software development environment for 9 years, with all but three for a global fashion retailer.

He is proficient in ASP.Net, MVC, C#, HTML, CSS, JavaScript, SQL Server TSQL.

Comments and Discussions

 
SuggestionCode dump Pin
GerVenson15-Dec-15 1:42
professionalGerVenson15-Dec-15 1:42 
Please ... less code but more article text!

PraiseRe: Code dump Pin
dibley197315-Dec-15 3:34
dibley197315-Dec-15 3:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.