cloudsoft.net/Utils/DBParams.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;

using System.IO;

namespace com.cloudsoft.utils
{
    /// <summary>
    /// Database Parameters class.
    /// Allows parameters to be added to a DBHelper.
    /// Can only be instatiated from a DBHelper.
    /// JF 12/2007 - DBParams can now represent either an OleDbCommand or an OleDbCommand. Which one 
    /// is decided when it is created by DBHelper. Once it is created though the client doesn't need to 
    /// know which one it is dealing with.
    /// </summary>
    public class DBParams
    {
        /// <summary>
        /// DataType enumerator. 
        /// </summary>
        /// <remarks>
        /// <list type="bullet">
        /// <item><term>VarChar</term><description> - Varchar (string)</description></item>
        /// <item><term>Long</term><description> - Long integer</description></item>
        /// <item><term>Double</term><description> - Double (float)</description></item>
        /// <item><term>DateTime</term><description> - Date</description></item>
        /// <item><term>LongVarChar</term><description> - Clob</description></item>
        /// </list>
        /// </remarks>
        public enum DataType {
            /// <summary>Varchar</summary>
            VarChar,
            /// <summary>Long</summary>
            Long,
            /// <summary>Double</summary>
            Double,
            /// <summary>DateTime</summary>
            DateTime,
            /// <summary>LongVarChar</summary>
            LongVarChar,
            /// <summary>BLOB</summary>
            BLOB,
            /// <summary>Clob</summary>
            Clob,
            /// <summary>Xml</summary>
            Xml
        };

        internal OleDbCommand Command;
        /// <summary>
        /// Oracle type equivalent of the oleType function. Returns the OleDbType from the general
        /// DBParams.DataType.
        /// </summary>
        /// <param name="eDT">A non-provider specific datatype.</param>
        /// <returns>An OleDbType.</returns>
        private OleDbType _ODACType(DataType eDT)
        {
            switch (eDT)
            {
                case DataType.DateTime: return OleDbType.Date;
                case DataType.Long: return OleDbType.Integer;
                case DataType.Double: return OleDbType.Double;
                case DataType.LongVarChar: return OleDbType.LongVarChar;
                case DataType.BLOB: return OleDbType.LongVarBinary;
                case DataType.Clob: return OleDbType.LongVarChar;
                default: return OleDbType.VarChar;
            }
        }

        /// <summary>
        /// OleDbCommand based DBParams constructor.
        /// </summary>
        /// <param name="oCommand">The OleDbCommand to wrap.</param>
        internal DBParams(OleDbCommand oCommand)
        {
            Command = oCommand;
        }

        /// <summary>
        /// Add Parameter method for the OleDbCommand
        /// </summary>
        /// <param name="zParamName"></param>
        /// <param name="eDT"></param>
        public void add(String zParamName, DataType eDT, object value)
        {
            //Command.Parameters.Add(new OleDbParameter(zParamName, _ODACType(eDT), value, System.Data.ParameterDirection.Input));
        }

        /// <summary>
        /// Adds a Input Parameter to the DBHelper
        /// </summary>
        /// <param name="zParamName">Parameter Name</param>
        /// <param name="eDT">DataType</param>
        /// <param name="iSize">Size of the parameter</param>
        /// <param name="oValue">Value of the Parameter</param>
        public void add(String zParamName, DataType eDT, int iSize, Object oValue)
        {
            
            //Set the size to 1 for longvarchars...
            if ((eDT == DataType.LongVarChar) && (oValue == null))
                iSize = 1;
        
            //Ass the parameter...
            Command.Parameters.Add(zParamName, _ODACType(eDT), iSize).Value = oValue;

        }

        /// <summary>
        /// Adds a Input Parameter to the DBHelper without a value
        /// </summary>
        /// <param name="zParamName">Parameter Name</param>
        /// <param name="eDT">DataType</param>
        /// <param name="iSize">Size of the parameter</param>
        public void add(String zParamName, DataType eDT, int iSize)
        {
            //Add the parameter...
            Command.Parameters.Add(zParamName, _ODACType(eDT), iSize);
        }

        /// <summary>
        /// Adds a Input Parameter to the DBHelper without a value or size
        /// </summary>
        /// <param name="zParamName">Parameter Name</param>
        /// <param name="eDT">DataType</param>
        /// <param name="iSize">Size of the parameter</param>
        public void add(String zParamName, DataType eDT)
        {
            //Add the parameter...
            Command.Parameters.Add(zParamName, _ODACType(eDT));
        }


        /// <summary>
        /// Sets the input parameter value for a given parameter name
        /// </summary>
        /// <param name="zParamName">Parameter Name</param>
        /// <param name="oValue">Value to set the input parameter to</param>
        public void set(String zParamName, Object oValue)
        {
            //Set the parameter value...
            Command.Parameters[zParamName].Value = oValue;
        }

        /// <summary>
        /// Sets the input parameter value for a given parameter index
        /// </summary>
        /// <param name="index">Parameter Index</param>
        /// <param name="oValue">Value to set the input parameter to</param>
        public void set(int index, Object oValue)
        {
            //Set the parameter value...
            Command.Parameters[index].Value = oValue;
        }

        public void set(int iParam, Object oValue, OleDbType iType)
        {
            OleDbParameter oParam = null;
            if (Command.Parameters.Contains(iParam.ToString())) {
                oParam = Command.Parameters[iParam.ToString()];
                oParam.Value = oValue;
            }
            else
            {
                oParam = new OleDbParameter(iParam.ToString(), oValue);
                if (iType != null)
                    oParam.OleDbType = iType;
                Command.Parameters.Add(oParam);
            }
            
        }

        /// <summary>
        /// Adds an Output Parameter
        /// </summary>
        /// <param name="zParamName">Parameter Name</param>
        /// <param name="eDT">Data Type</param>
        /// <param name="iSize">Size of the parameter</param>
        public void addOutput(String zParamName, DataType eDT, int iSize)
        {
            //Add the output parameter...
            Command.Parameters.Add(zParamName, _ODACType(eDT), iSize).Direction = System.Data.ParameterDirection.Output;
        }

        /// <summary>
        /// Exposes the Parameters for the OleDbCommand.
        /// </summary>
        /// <returns>OleDbParameterCollection</returns>
        public OleDbParameterCollection ODACParameters()
        {
            return Command.Parameters;
        }

        /// <summary>
        /// Closes the appropriate underlying connection.
        /// </summary>
        public void CloseConnection()
        {
            Command.Connection.Close();
        }

        /// <summary>
        /// Closes the appropriate underlying command.
        /// </summary>
        public void CloseCommand()
        {
            Command.Connection.Dispose();
            Command.Dispose();
        }

        /// <summary>
        /// Executes the appropriate underlying command.
        /// </summary>
        /// <returns>int</returns>
        public int ExecuteNonQuery()
        {
            return Command.ExecuteNonQuery();
        }


        public OleDbCommand AsOleDbCommand()
        {    
            return Command;
        }

    }
}