gps.xml.utils/GPS.XML.Utils/Utils/DBParams.cs

using System.Data;
using System.Data.Common;

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 DbCommand or an DbCommand. 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>Int</summary>
            Int,
            /// <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 DbCommand Command;
        /// <summary>
        /// Oracle type equivalent of the oleType function. Returns the SqlDbType from the general
        /// DBParams.DataType.
        /// </summary>
        /// <param name="eDT">A non-provider specific datatype.</param>
        /// <returns>An SqlDbType.</returns>
        private DbType _ODACType(DataType eDT)
        {
            switch (eDT)
            {
                case DataType.DateTime: return DbType.DateTime;
                case DataType.Int: return DbType.Int32;
                case DataType.Long: return DbType.Int64;
                case DataType.Double: return DbType.Decimal;
                case DataType.LongVarChar: return DbType.String;
                case DataType.BLOB: return DbType.Binary;
                case DataType.Clob: return DbType.String;
                default: return DbType.String;
            }
        }

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

        /// <summary>
        /// Add Parameter method for the DbCommand
        /// </summary>
        /// <param name="zParamName"></param>
        /// <param name="eDT"></param>
        public void add(String zParamName, DataType eDT, object value)
        {
            //Command.Parameters.Add(new DbParameter(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;

            //Adds the parameter...
            DbParameter dbParam = Command.CreateParameter();
            dbParam.ParameterName = zParamName;
            dbParam.DbType = _ODACType(eDT);
            dbParam.Size = iSize;
            dbParam.Value = oValue;

            Command.Parameters.Add(dbParam);

        }

        /// <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...
            DbParameter dbParam = Command.CreateParameter();
            dbParam.ParameterName = zParamName;
            dbParam.DbType = _ODACType(eDT);
            dbParam.Size = iSize;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
            Command.Parameters.Add(dbParam);
        }

        /// <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...
            var dbParam = Command.CreateParameter();
            dbParam.ParameterName = zParamName;
            dbParam.DbType = _ODACType(eDT);

            Command.Parameters.Add(dbParam);
        }


        /// <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(string zParam, Object oValue, DbType iType)
        {
            DbParameter oParam = null;
            if (Command.Parameters.Contains(zParam)) {
                oParam = Command.Parameters[zParam];
                oParam.Value = oValue;
            }
            else
            {
                oParam = Command.CreateParameter();
                oParam.ParameterName = zParam;
                if (iType != null)
                    oParam.DbType = iType;
                oParam.Value = oValue;
                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...
            var dbParam = Command.CreateParameter();
            dbParam.ParameterName = zParamName;
            dbParam.DbType = _ODACType(eDT);
            dbParam.Size = iSize;
            dbParam.Direction = ParameterDirection.Output;
        }

        /// <summary>
        /// Exposes the Parameters for the DbCommand.
        /// </summary>
        /// <returns>DbParameterCollection</returns>
        public DbParameterCollection 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 DbCommand AsDbCommand()
        {    
            return Command;
        }

    }
}