Click here to Skip to main content
Click here to Skip to main content
Add your own
alternative version
Go to top

Database Helper Class Library to Ease Database Operation

, 14 Apr 2007
Database Helper Class Library to Ease Database Operation
article_demo.zip
DBHelperQuickRelationshipSamples
DBHelperQuickRelationshipSamples
App.ico
DBHelperQuickRelationshipSamples.csproj.user
DBHelperQuickRelationshipSamples.suo
DBHelperQuickSelectSamples
DBHelperQuickSelectSamples
DBHelperQuickSelectSamples.csproj.user
DBHelperQuickSelectSamples.suo
DBHelperQuickHelperSamples
DBHelperQuickHelperSamples
App.ico
DBHelperQuickHelperSamples.csproj.user
DBHelperQuickHelperSamples.suo
DBHelperQuickInsUpdDelSamples
DBHelperQuickInsUpdDelSamples
DBHelperQuickInsUpdDelSamples.csproj.user
DBHelperQuickInsUpdDelSamples.suo
article_src.zip
Microsoft.ApplicationBlocks.ExceptionManagement.Interfaces
Microsoft.ApplicationBlocks.ExceptionManagement.Interfaces.csproj.user
Microsoft.ApplicationBlocks.ExceptionManagement.suo
DbHelper
DBHelper.chm
DBHelper.csproj.user
DbHelper.ndoc
DBHelper.suo
Microsoft.ApplicationBlocks.ExceptionManagement
ExceptionManagerText.xsx
Microsoft.ApplicationBlocks.ExceptionManagement.csproj.user
Microsoft.ApplicationBlocks.suo
///////////////////////////////////////////////////////////////////////////
// Copyright 2003-2005 Falcon Soon
//
// Author: Soon Chun Boon
// Date: 02 Feb 2004
// Description: 
// Class that represents a table field.
// This class is modified version of clsField in LLBLGen v1.21.2003.712
// source codes.
///////////////////////////////////////////////////////////////////////////
using System;
using System.Collections;
using System.Globalization;
using System.Data;

namespace DBHelper
{

	/// <summary>
	/// class that represents a table field. Used to store field information.
	/// </summary>
	/// <remarks>Implements the IComparable interface for the fieldsort based on type.</remarks>
	public class Field : IComparable
	{
		#region Class Member Declarations
			private string			mstrFieldName, mstrDataType, mstrDefaultValue;
			private int				miOrdinalPosition, miLength, miPrecision, miScale;
			private bool			mbIsComputed, mbIsIdentity, mbIsRowGUIDColumn, mbIsForeignKey, 
							        mbIsExcluded, mbIsPrimaryKey, mbIsNullable, mbHasUniqueConstraint;
			private SortedList		mslTypeToSqlType, mslTypeToSqlDbType, mslTypeToNETType;
		#endregion

		/// <summary>
		/// Initializes a new instance of <see cref="DBHelper.Field"/> with the specified
		/// field name.
		/// </summary>
		/// <param name="strFieldName">Name of field in a table.</param>
		public Field(string strFieldName)
		{
			mstrFieldName = strFieldName;
		}


		/// <summary>
		/// Implementation of the CompareTo method of the IComparable interface.
		/// </summary>
		/// <param name="objToCompareTo">Object of type <see cref="DBHelper.Field"/> to compare 
		/// the current instance with.</param>
		/// <returns>-1 if this.DataType is less than the DataType of <i>objToCompareTo</i>
		/// 0, if this.DataType is equal to <i>objToCompareTo</i>.DataType
		/// 1, if this.DataType is greater than <i>objToCompareTo</i>.DataType </returns>
		/// <remarks>
		/// Comparison is string compare, e.g. <i>int</i> is less than <i>smallint</i>.
		/// </remarks>
		public int CompareTo(Object objToCompareTo)
		{
			Field objFieldToCompareTo = (Field) objToCompareTo;
		
			if(objFieldToCompareTo.GetType() != this.GetType())
			{
				// throw exception
				throw new ArgumentException("Object passed is not of the same type as 'Field'");
			}
			if(this.GetFieldTypeAsNETType()==objFieldToCompareTo.GetFieldTypeAsNETType())
			{
				return 0;
			}
			return mstrDataType.CompareTo(objFieldToCompareTo.DataType);
		}
				
		/// <summary>
		/// Returns true when the field requires a null value check in the SqlParameter creation code, so the
		/// code emitters have to emit a Null value check.
		/// </summary>
		/// <returns>True if the field requires a null value check, otherwise False. It will only return True when
		/// the field is nullable AND the field is of a type which requires a null value check in the SqlParameter
		/// creation code.</returns>
		public bool CheckIfNullValueCheckIsNeeded()
		{
			bool bToReturn;
		
			if(!mbIsNullable)
			{
				// field is not nullable, a null value check is redundant, since the property code already
				// contains a null value check.
				return (false);
			}
			switch(mstrDataType)
			{
				case "image":
				case "text":
				case "ntext":
					bToReturn = true;
					break;
				case "int":
				case "smallint":
				case "tinyint":
				case "bigint":
				case "bit":
				case "binary":
				case "decimal":
				case "sql_variant":
				case "sysname":
				case "datetime":
				case "smalldatetime":
				case "timestamp":
				case "float":
				case "real":
				case "money":
				case "smallmoney":
				case "uniqueidentifier":
				case "numeric":
				case "varchar":
				case "char":
				case "nvarchar":
				case "nchar":
				case "varbinary":
				default:
					bToReturn = false;
					break;
			}
			return (bToReturn);
		}
		
		/// <summary>
		/// Returns the type of the field as a Sqltype, like 'SqlDecimal'.
		/// </summary>
		/// <returns>SqlType version of the field's type.</returns>
		public string GetFieldTypeAsSqlType()
		{
			return  mslTypeToSqlType[mstrDataType].ToString();
		}

        /// <summary>
        /// Returns the type of the field as a SqlDbType, like SqlDbType.Decimal.
        /// </summary>
        /// <returns>SqlDbType version of the field's type.</returns>
        public SqlDbType GetFieldTypeAsSqlDbType()
        {
            return  (SqlDbType)mslTypeToSqlDbType[mstrDataType];
        }

		/// <summary>
		/// Returns the type of the field as a .NET type, like 'System.Decimal'.
		/// </summary>
		/// <returns>.NET version of the field's type.</returns>
		public string GetFieldTypeAsNETType()
		{
			return  mslTypeToNETType[mstrDataType].ToString();
		}
	
        /// <summary>
        /// Returns the default value of this field in .NET type.
        /// </summary>
        /// <returns>Default value in .NET Type.</returns>
        public object GetFieldNetTypeDefaultValue()
        {
            object objReturn;

            switch(mstrDataType)
            {
                case "int":
                case "smallint":
                case "tinyint":
                case "bigint":
                case "decimal":
                case "numeric":
                    objReturn = 0;
                    break;
                case "bit":
                    objReturn = false;
                    break;
                case "binary":
                case "image":
                case "timestamp":
                case "varbinary":
                    objReturn = new byte[0];
                    break;
                case "varchar":
                case "char":
                case "nvarchar":
                case "nchar":
                case "text":
                case "ntext":
                case "sql_variant":
                case "sysname":
                    objReturn = "";
                    break;
                case "datetime":
                case "smalldatetime":
                    objReturn = DateTime.Now.ToUniversalTime();
                    break;
                case "float":
                case "real":
                case "money":
                case "smallmoney":
                    objReturn = 0.0;
                    break;
                case "uniqueidentifier":
                    objReturn = Guid.NewGuid();
                    break;
                default:
                    objReturn = DBNull.Value;
                    break;
            }

            return (objReturn);
        }

		#region Class Property Declarations
        /// <summary>
        /// Returns field name.
        /// </summary>
		public string FieldName
		{
			get	{ return mstrFieldName; }
		}

        /// <summary>
        /// Specifies whether this field is excluded.
        /// </summary>
		public bool IsExcluded
		{
			get	{ return mbIsExcluded;	}
			set	{ mbIsExcluded = value; }
		}

        /// <summary>
        /// Sets the TypeToSqlType sorted list. <seealso cref="DBHelper.DBTypeInfo"/>
        /// </summary>
        
		public SortedList TypeToSqlType
		{
			set { mslTypeToSqlType=value; }
		}

        /// <summary>
        /// Sets the TypeToSqlDbType sorted list. <seealso cref="DBHelper.DBTypeInfo"/>
        /// </summary>
        public SortedList TypeToSqlDbType
        {
            set { mslTypeToSqlDbType=value; }
        }

        /// <summary>
        /// Sets the TypeToNETType sorted list. <seealso cref="DBHelper.DBTypeInfo"/>
        /// </summary>
        public SortedList TypeToNETType
        {
            set { mslTypeToNETType=value; }
        }

        /// <summary>
        /// Specifies the data type of this field.
        /// </summary>
		public string DataType
		{
			get	{ return mstrDataType; }
			set	{ mstrDataType = value; }
		}

        /// <summary>
        /// Specifies the ordinal position of this field.
        /// </summary>
		public int OrdinalPosition
		{
			get	{ return miOrdinalPosition; }
			set	{ miOrdinalPosition = value; }
		}

        /// <summary>
        /// Specifies the field's data length.
        /// </summary>
		public int Length
		{
			get	{ return miLength; }
			set	{ miLength = value; }
		}

        /// <summary>
        /// Specifies the field's precision.
        /// </summary>
		public int Precision
		{
			get	
			{ 
				//////////////
				// .NET v1.0 BUG WORKAROUND: Precision can't be > 38. SQLServer 's float has
				// a precision 53. We have to cap the precision to 38, until MS fixes this
				// bug
				/////////////
				if(miPrecision>38)
				{
					return 38;
				}
				else
				{
					return miPrecision; 
				}
			}
			set	{ miPrecision = value; }
		}

        /// <summary>
        /// Specifies the field's scale.
        /// </summary>
		public int Scale
		{
			get	
			{ 
				//////////////
				// .NET v1.0 BUG WORKAROUND: When the type is Decimal, the scale has to be > 0 otherwise
				// the .NET SqlServer driver won't save a NULL value into the field. We return 1 if it's
				// 0 AND the type is Decimal
				/////////////
				if(((mstrDataType=="decimal")||(mstrDataType=="numeric"))  &&(miScale==0))
				{
					// return 1 to workaround this bug in .NET v1.0
					return 1;
				}
				else
				{
					return miScale; 
				}
			}
			set	{ miScale = value; }
		}

        /// <summary>
        /// Specifies whether the field is computed.
        /// </summary>
		public bool IsComputed
		{
			get	{ return mbIsComputed; }
			set	{ mbIsComputed = value; }
		}

        /// <summary>
        /// Specifies whether the field is identity field.
        /// </summary>
		public bool IsIdentity
		{
			get	{ return mbIsIdentity; }
			set	{ mbIsIdentity = value; }
		}

        /// <summary>
        /// Specifies whether the field is GUID column.
        /// </summary>
		public bool IsRowGUIDColumn
		{
			get	{ return mbIsRowGUIDColumn; }
			set	{ mbIsRowGUIDColumn = value; }
		}

        /// <summary>
        /// Specifies whether the field is foreign key.
        /// </summary>
		public bool IsForeignKey
		{
			get	{ return mbIsForeignKey; }
			set	{ mbIsForeignKey = value; }
		}

        /// <summary>
        /// Specifies whether the field is primary key.
        /// </summary>
		public bool IsPrimaryKey
		{
			get { return mbIsPrimaryKey; }
			set	{ mbIsPrimaryKey = value; }
		}

        /// <summary>
        /// Specifies whether the field is nullable.
        /// </summary>
		public bool IsNullable
		{
			get	{ return mbIsNullable; }
			set { mbIsNullable = value; }
		}

        /// <summary>
        /// Specifies whether the field has unique constraint.
        /// </summary>
		public bool HasUniqueConstraint
		{
			get { return mbHasUniqueConstraint; }
			set { mbHasUniqueConstraint = value; }
		}

        /// <summary>
        /// True if the field data type is 'TimeStamp' otherwise False.
        /// </summary>
		public bool IsTimeStamp
		{
			get { return (mstrDataType.ToLower(CultureInfo.InvariantCulture) == "timestamp"); }
		}

        /// <summary>
        /// Specifies the field's default value.
        /// </summary>
        public string DefaultValue
        {
            get { return mstrDefaultValue; }
            set { mstrDefaultValue = value; }
        }
		#endregion
	}
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

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

Share

About the Author

falconsoon

Malaysia Malaysia
Had worked as analyst programmer for 4 years. Now helping in family business but still involved actively in .Net development whenever there is a free time.

| Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 14 Apr 2007
Article Copyright 2007 by falconsoon
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid