Database Helper Class Library to Ease Database Operation

///////////////////////////////////////////////////////////////////////////
// Copyright 2003-2005 Falcon Soon
//
// Author: Soon Chun Boon
// Date: 5 Feb 2004
// Description: 
// This class provides static variables which holding useful info about
// database type and .NET type matching.
///////////////////////////////////////////////////////////////////////////
using System;
using System.Collections;
using System.Data;

namespace DBHelper
{
	/// <summary>
	/// This class consists of static variables which holding useful info about
	/// database type and .NET type matching.
	/// </summary>
	public sealed class DBTypeInfo
	{
        private static SortedList mslTypeToSqlType;
        private static SortedList mslTypeToSqlDbType;
        private static SortedList mslTypeToNETType;

        /// <summary>
        /// Private constructor which prevents <see cref="DBHelper.DBTypeInfo"/>
        /// from been instanced.
        /// </summary>
        private DBTypeInfo()
        {
        }

        /// <summary>
        /// Static constructor which perform static initialization.
        /// </summary>
		static DBTypeInfo()
		{
			InitTypeSqlTypeTable();
            InitTypeSqlDbTypeTable();
            InitTypeNETTypeTable();
		}

        /// <summary>
        /// Creates and initializes the TypeToSqlType sorted list, which is used
        /// to determine the .NET Sqltype for a given SQLServer DataType.
        /// </summary>
        private static void InitTypeSqlTypeTable()
        {
            // create a new sorted list, specify 128 initial items.
            mslTypeToSqlType = new SortedList(128);
            mslTypeToSqlType.Add("bigint","SqlInt64");						// SQLServer 2000+ only
            mslTypeToSqlType.Add("binary","SqlBinary");
            mslTypeToSqlType.Add("bit","SqlBoolean");
            mslTypeToSqlType.Add("char","SqlString");
            mslTypeToSqlType.Add("datetime","SqlDateTime");
            mslTypeToSqlType.Add("decimal","SqlDecimal");
            mslTypeToSqlType.Add("float","SqlDouble");
            mslTypeToSqlType.Add("image","SqlBinary");
            mslTypeToSqlType.Add("int","SqlInt32");
            mslTypeToSqlType.Add("money","SqlMoney");
            mslTypeToSqlType.Add("nchar","SqlString");
            mslTypeToSqlType.Add("ntext","SqlString");
            mslTypeToSqlType.Add("nvarchar","SqlString");
            mslTypeToSqlType.Add("numeric","SqlDecimal");
            mslTypeToSqlType.Add("real","SqlSingle");
            mslTypeToSqlType.Add("smalldatetime","SqlDateTime");
            mslTypeToSqlType.Add("smallint","SqlInt16");
            mslTypeToSqlType.Add("smallmoney","SqlMoney");
            mslTypeToSqlType.Add("sql_variant","Object");				// SQLServer 2000+ only
            mslTypeToSqlType.Add("sysname","SqlString");
            mslTypeToSqlType.Add("text","SqlString");
            mslTypeToSqlType.Add("timestamp","SqlBinary");
            mslTypeToSqlType.Add("tinyint","SqlByte");
            mslTypeToSqlType.Add("varbinary","SqlBinary");
            mslTypeToSqlType.Add("varchar","SqlString");
            mslTypeToSqlType.Add("uniqueidentifier","SqlGuid");
        }

        /// <summary>
        /// Creates and initializes the TypeToSqlDbType sorted list, which is used
        /// to determine the SqlDbType type for a given SQLServer DataType.
        /// </summary>
        private static void InitTypeSqlDbTypeTable()
        {
            // create a new sorted list, specify 128 initial items.
            mslTypeToSqlDbType = new SortedList(128);
            mslTypeToSqlDbType.Add("bigint",SqlDbType.BigInt);						// SQLServer 2000+ only
            mslTypeToSqlDbType.Add("binary",SqlDbType.Binary);
            mslTypeToSqlDbType.Add("bit",SqlDbType.Bit);
            mslTypeToSqlDbType.Add("char",SqlDbType.Char);
            mslTypeToSqlDbType.Add("datetime",SqlDbType.DateTime);
            mslTypeToSqlDbType.Add("decimal",SqlDbType.Decimal);
            mslTypeToSqlDbType.Add("float",SqlDbType.Float);
            mslTypeToSqlDbType.Add("image",SqlDbType.Image);
            mslTypeToSqlDbType.Add("int",SqlDbType.Int);
            mslTypeToSqlDbType.Add("money",SqlDbType.Money);
            mslTypeToSqlDbType.Add("nchar",SqlDbType.NChar);
            mslTypeToSqlDbType.Add("ntext",SqlDbType.NText);
            mslTypeToSqlDbType.Add("nvarchar",SqlDbType.NVarChar);
            mslTypeToSqlDbType.Add("numeric",SqlDbType.Decimal);
            mslTypeToSqlDbType.Add("real",SqlDbType.Real);
            mslTypeToSqlDbType.Add("smalldatetime",SqlDbType.SmallDateTime);
            mslTypeToSqlDbType.Add("smallint",SqlDbType.SmallInt);
            mslTypeToSqlDbType.Add("smallmoney",SqlDbType.SmallMoney);
            mslTypeToSqlDbType.Add("sql_variant",SqlDbType.Variant);					// SQLServer 2000+ only
            mslTypeToSqlDbType.Add("sysname",SqlDbType.VarChar);
            mslTypeToSqlDbType.Add("text",SqlDbType.Text);
            mslTypeToSqlDbType.Add("timestamp",SqlDbType.Timestamp);
            mslTypeToSqlDbType.Add("tinyint",SqlDbType.TinyInt);
            mslTypeToSqlDbType.Add("varbinary",SqlDbType.VarBinary);
            mslTypeToSqlDbType.Add("varchar",SqlDbType.VarChar);
            mslTypeToSqlDbType.Add("uniqueidentifier",SqlDbType.UniqueIdentifier);
        }

        /// <summary>
        /// Creates and initializes the TypeToNETType sorted list, which is used
        /// to determine the .NET Type for a given SQLServer DataType.
        /// </summary>
        private static void InitTypeNETTypeTable()
        {
            // create a new sorted list, specify 128 initial items.
            mslTypeToNETType = new SortedList(128);
            mslTypeToNETType.Add("bigint","System.Int64");						// SQLServer 2000+ only
            mslTypeToNETType.Add("binary","System.Byte[]");
            mslTypeToNETType.Add("bit","System.Boolean");
            mslTypeToNETType.Add("char","System.String");
            mslTypeToNETType.Add("datetime","System.DateTime");
            mslTypeToNETType.Add("decimal","System.Decimal");
            mslTypeToNETType.Add("float","System.Double");
            mslTypeToNETType.Add("image","System.Byte[]");
            mslTypeToNETType.Add("int","System.Int32");
            mslTypeToNETType.Add("money","System.Decimal");
            mslTypeToNETType.Add("nchar","System.String");
            mslTypeToNETType.Add("ntext","System.String");
            mslTypeToNETType.Add("nvarchar","System.String");
            mslTypeToNETType.Add("numeric","System.Decimal");
            mslTypeToNETType.Add("real","System.Single");
            mslTypeToNETType.Add("smalldatetime","System.DateTime");
            mslTypeToNETType.Add("smallint","System.Int16");
            mslTypeToNETType.Add("smallmoney","System.Decimal");
            mslTypeToNETType.Add("sql_variant","System.Object");				// SQLServer 2000+ only
            mslTypeToNETType.Add("sysname","System.String");
            mslTypeToNETType.Add("text","System.String");
            mslTypeToNETType.Add("timestamp","System.Byte[]");
            mslTypeToNETType.Add("tinyint","System.Byte");
            mslTypeToNETType.Add("varbinary","System.Byte[]");
            mslTypeToNETType.Add("varchar","System.String");
            mslTypeToNETType.Add("uniqueidentifier","System.Guid");
        }

        /// <summary>
        /// Returns TypeToSqlType sorted list.
        /// </summary>
        public static SortedList TypeToSqlType
        {
            get
            {
                return (mslTypeToSqlType);
            }
        }

        /// <summary>
        /// Returns TypeToSqlDbType sorted list.
        /// </summary>
        public static SortedList TypeToSqlDbType
        {
            get
            {
                return (mslTypeToSqlDbType);
            }
        }

        /// <summary>
        /// Returns TypeToNetType sorted list.
        /// </summary>
        public static SortedList TypeToNETType
        {
            get
            {
                return (mslTypeToNETType);
            }
        }
	}
}