Simulating Stored Procedures in Microsoft Access using Enterprise Library Application Blocks

//===============================================================================
// Microsoft patterns & practices Enterprise Library
// Data Access Application Block
//===============================================================================
// Copyright � Microsoft Corporation. All rights reserved.
// Adapted from ACA.NET with permission from Avanade Inc.
// ACA.NET copyright � Avanade Inc. All rights reserved.
// THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
// OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
// LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
// FITNESS FOR A PARTICULAR PURPOSE.
//===============================================================================

using System;
using System.Collections;
using System.Data;
using System.Data.OleDb;

namespace Microsoft.Practices.EnterpriseLibrary.Data.OleDb
{
	/// <summary>
	/// <para>Represents a SQL statement or stored procedure to execute against a OleDb Server database.</para>
	/// </summary>   
	public class OleDbCommandWrapper : DBCommandWrapper
	{
		private OleDbCommand command;
		private int rowsAffected;
		private object[] parameterValues;
		private bool needsParameters = false;
		private char parameterToken;

		/// <summary>
		/// <para>Initialize a new instance of the <see cref="OleDbCommandWrapper"/> class with the text of a query and the command type.</para>
		/// </summary>
		/// <param name="commandText"><para>The stored procedure name or SQL sting the command represents.</para></param>
		/// <param name="commandType"><para>One of the <see crer="CommandType"/> values.</para></param>
		/// <param name="parameterToken"><para>The parameter delimeter for database commands.</para></param>
		internal OleDbCommandWrapper(string commandText, CommandType commandType, char parameterToken)
		{
			this.parameterToken = parameterToken;
			// *** I added parameterValues
			this.command = CreateCommand(commandText, commandType, parameterValues);
		}

		/// <summary>
		/// <para>Initialize a new instance of the <see cref="OleDbCommandWrapper"/> class with the text of a query the command type, and the parameter values.</para>
		/// </summary>        
		/// <param name="commandText"><para>The stored procedure name or SQL sting the command represents.</para></param>
		/// <param name="commandType"><para>One of the <see crer="CommandType"/> values.</para></param>
		/// <param name="parameterToken"><para>The parameter delimeter for database commands.</para></param>
		/// <param name="parameterValues"><para>The parameter values to assign in positional order.</para></param>'
		/// internal OleDbCommandWrapper(string commandText, CommandType commandType, char parameterToken, object[] parameterValues) : this(commandText, commandType, parameterToken)
		internal OleDbCommandWrapper(string commandText, CommandType commandType, char parameterToken, object[] parameterValues) 
		{
			// This is line 53 in oledbcommandwrapper.cs
			// *** I have Parameters here but here is where I lose them
			this.parameterValues = parameterValues;
			if (commandType == CommandType.StoredProcedure)
			{
				this.needsParameters = true;
				this.command = CreateCommand(commandText, commandType, parameterValues);
			}
		}

		/// <summary>
		/// <para>Gets the underlying <see cref="IDbCommand"/>.</para>
		/// </summary>
		/// <value>
		/// <para>The underlying <see cref="IDbCommand"/>. The default is <see langword="null"/>.</para>
		/// </value>
		/// <remarks>
		/// <para>This command is a <see cref="OleDbCommand"/></para>        
		/// </remarks>        
		/// <seealso cref="OleDbCommand"/>
		public override IDbCommand Command
		{
			get { return this.command; }
		}

		/// <summary>
		/// <para>Gets or sets the rows affected by this command.</para>
		/// </summary>
		/// <value>
		/// <para>The rows affected by this command.</para>
		/// </value>
		public override int RowsAffected
		{
			get { return this.rowsAffected; }
			set { this.rowsAffected = value; }
		}

		/// <summary>
		/// <para>Gets or sets the wait time before terminating the attempt to execute a command and generating an error.</para>
		/// </summary>
		/// <value>
		/// <para>The wait time before terminating the attempt to execute a command and generating an error.</para>
		/// </value>
		public override int CommandTimeout
		{
			get { return this.command.CommandTimeout; }
			set { this.command.CommandTimeout = value; }
		}

		/// <summary>
		/// <para>Adds a new instance of an <see cref="OleDbParameter"/> object to the command.</para>
		/// </summary>
		/// <param name="name"><para>The name of the parameter.</para></param>
		/// <param name="dbType"><para>One of the <see cref="DbType"/> values.</para></param>
		/// <param name="size"><para>The maximum size of the data within the column.</para></param>
		/// <param name="direction"><para>One of the <see cref="ParameterDirection"/> values.</para></param>
		/// <param name="nullable"><para>Avalue indicating whether the parameter accepts null values.</para></param>
		/// <param name="precision"><para>The maximum number of digits used to represent the <paramref name="value"/>.</para></param>
		/// <param name="scale"><para>The number of decimal places to which <paramref name="value"/> is resolved.</para></param>
		/// <param name="sourceColumn"><para>The name of the source column mapped to the DataSet and used for loading or returning the <paramref name="value"/>.</para></param>
		/// <param name="sourceVersion"><para>One of the <see cref="DataRowVersion"/> values.</para></param>
		/// <param name="value"><para>The value of the parameter.</para></param>       
		public override void AddParameter(string name, DbType dbType, int size, ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)
		{
			this.command.Parameters.Add(CreateParameter(name, dbType, size, direction, nullable, precision, scale, sourceColumn, sourceVersion, value));
		}

		/// <summary>
		/// <para>Adds a new instance of an <see cref="OleDbParameter"/> object to the command.</para>
		/// </summary>
		/// <param name="name"><para>The name of the parameter.</para></param>
		/// <param name="dbType"><para>One of the <see cref="DbType"/> values.</para></param>        
		/// <param name="direction"><para>One of the <see cref="ParameterDirection"/> values.</para></param>                
		/// <param name="sourceColumn"><para>The name of the source column mapped to the DataSet and used for loading or returning the <paramref name="value"/>.</para></param>
		/// <param name="sourceVersion"><para>One of the <see cref="DataRowVersion"/> values.</para></param>
		/// <param name="value"><para>The value of the parameter.</para></param>    
		public override void AddParameter(string name, DbType dbType, ParameterDirection direction, string sourceColumn, DataRowVersion sourceVersion, object value)
		{
			OleDbParameter param = CreateParameter(name, dbType, 0, direction, false, 0, 0, sourceColumn, sourceVersion, value);
			this.command.Parameters.Add(param);
		}

		/// <summary>
		/// <para>Adds a new instance of an <see cref="OleDbParameter"/> object to the command set as <see cref="ParameterDirection"/> value of Output.</para>
		/// </summary>
		/// <param name="name"><para>The name of the parameter.</para></param>
		/// <param name="dbType"><para>One of the <see cref="DbType"/> values.</para></param>        
		/// <param name="size"><para>The maximum size of the data within the column.</para></param>        
		public override void AddOutParameter(string name, DbType dbType, int size)
		{
			AddParameter(name, dbType, size, ParameterDirection.Output, true, 0, 0, String.Empty, DataRowVersion.Default, DBNull.Value);
		}

		/// <summary>
		/// <para>Adds a new instance of an <see cref="OleDbParameter"/> object to the command set as <see cref="ParameterDirection"/> value of Input.</para>
		/// </summary>
		/// <param name="name"><para>The name of the parameter.</para></param>
		/// <param name="dbType"><para>One of the <see cref="DbType"/> values.</para></param>                
		/// <remarks>
		/// <para>This version of the method is used when you can have the same parameter object multiple times with different values.</para>
		/// </remarks>        
		public override void AddInParameter(string name, DbType dbType)
		{
			AddParameter(name, dbType, ParameterDirection.Input, String.Empty, DataRowVersion.Default, null);
		}

		/// <summary>
		/// <para>Adds a new instance of an <see cref="OleDbParameter"/> object to the command set as <see cref="ParameterDirection"/> value of Input.</para>
		/// </summary>
		/// <param name="name"><para>The name of the parameter.</para></param>
		/// <param name="dbType"><para>One of the <see cref="DbType"/> values.</para></param>                
		/// <param name="value"><para>The value of the parameter.</para></param>      
		public override void AddInParameter(string name, DbType dbType, object value)
		{
			AddParameter(name, dbType, ParameterDirection.Input, String.Empty, DataRowVersion.Default, value);
		}

		/// <summary>
		/// <para>Adds a new instance of an <see cref="OleDbParameter"/> object to the command set as <see cref="ParameterDirection"/> value of Input.</para>
		/// </summary>
		/// <param name="name"><para>The name of the parameter.</para></param>
		/// <param name="dbType"><para>One of the <see cref="DbType"/> values.</para></param>                
		/// <param name="sourceColumn"><para>The name of the source column mapped to the DataSet and used for loading or returning the value.</para></param>
		/// <param name="sourceVersion"><para>One of the <see cref="DataRowVersion"/> values.</para></param>
		public override void AddInParameter(string name, DbType dbType, string sourceColumn, DataRowVersion sourceVersion)
		{
			AddParameter(name, dbType, 0, ParameterDirection.Input, true, 0, 0, sourceColumn, sourceVersion, null);
		}

		/// <summary>
		/// <para>Returns the value of the parameter for the given <paramref name="name"/>.</para>
		/// </summary>
		/// <param name="name"><para>The name of the parameter to get the value.</para></param>
		/// <returns><para>The value of the parameter.</para></returns>
		public override object GetParameterValue(string name)
		{
			return this.command.Parameters[BuildParameterName(name)].Value;
		}

		/// <summary>
		/// <para>Sets the value of a parameter for the given <paramref name="name"/>.</para>
		/// </summary>
		/// <param name="name"><para>The name of the parameter to set the value.</para></param>
		/// <param name="value"><para>The new value of the parameter.</para></param>
		public override void SetParameterValue(string name, object value)
		{
			this.command.Parameters[BuildParameterName(name)].Value = (value == null) ? DBNull.Value : value;
		}

		/// <summary>
		/// <para>Clean up resources.</para>
		/// </summary>
		public override void Dispose()
		{
			this.command.Dispose();
		}

		/// <summary>
		/// <para>Dicover the parameters for a stored procedure using a separate connection and command.</para>
		/// </summary>
		/// <param name="parameterToken"><para>The parameter delimeter for database commands.</para></param>
		protected override void DoDiscoverParameters(char parameterToken)
		{
			this.parameterToken = parameterToken;
			using (OleDbCommand newCommand = CreateNewCommandAndConnectionForDiscovery())
			{
				OleDbCommandBuilder.DeriveParameters(newCommand);

				foreach (IDataParameter parameter in newCommand.Parameters)
				{
					IDataParameter cloneParameter = (IDataParameter)((ICloneable)parameter).Clone();
					cloneParameter.ParameterName = BuildParameterName(cloneParameter.ParameterName);
					this.command.Parameters.Add(cloneParameter);
				}
				newCommand.Connection.Close();
			}
		}

		/// <summary>
		/// <para>Assign the values provided by a user to the command parameters discovered in positional order.</para>
		/// </summary>
		/// <exception cref="InvalidOperationException">
		/// <para>The number of parameters does not match number of values for stored procedure.</para>
		/// </exception>
		protected override void DoAssignParameterValues()
		{
			if (SameNumberOfParametersAndValues() == false)
			{
				throw new InvalidOperationException(SR.ExceptionMessageParameterMatchFailure);
			}

			int returnParameter = 1;
			for (int i = 0; i < this.parameterValues.Length; i++)
			{
				IDataParameter parameter = this.command.Parameters[i + returnParameter];

				// There used to be code here that checked to see if the parameter was input or input/output
				// before assigning the value to it. We took it out because of an operational bug with
				// deriving parameters for a stored procedure. It turns out that output parameters are set
				// to input/output after discovery, so any direction checking was unneeded. Should it ever
				// be needed, it should go here, and check that a parameter is input or input/output before
				// assigning a value to it.
				SetParameterValue(parameter.ParameterName, this.parameterValues[i]);
			}
		}

		/// <summary>
		/// <para>Determine if a stored procedure is using parameter discovery.</para>
		/// </summary>
		/// <returns>
		/// <para><see langword="true"/> if further preparation is needed.</para>
		/// </returns>
		protected override bool DoIsFurtherPreparationNeeded()
		{
			return this.needsParameters;
		}

		/// <devdoc>
		/// Create a parameter.
		/// </devdoc>        
		private OleDbParameter CreateParameter(string name, DbType type, int size, ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)
		{
			OleDbParameter param = this.command.CreateParameter();
			param.ParameterName = BuildParameterName(name);
			param.DbType = type;
			param.Size = size;
			param.Direction = direction;
			param.IsNullable = nullable;
			param.Precision = precision;
			param.Scale = scale;
			param.SourceColumn = sourceColumn;
			param.SourceVersion = sourceVersion;
			param.Value = (value == null) ? DBNull.Value : value;

			return param;
		}

		private bool SameNumberOfParametersAndValues()
		{
			int returnParameterCount = 1;
			int numberOfParametersToStoredProcedure = this.command.Parameters.Count - returnParameterCount;
			int numberOfValuesProvidedForStoredProcedure = this.parameterValues.Length;
			return numberOfParametersToStoredProcedure == numberOfValuesProvidedForStoredProcedure;
		}

		/// <devdoc>
		/// Discovery has to be done on its own connection to allow for the case of the
		/// connection being used being enrolled in a transaction. The OleDbCommandBuilder.DeriveParameters
		/// method creates a new OleDbCommand internally to communicate to the database, and it
		/// reuses the same connection that is passed in on the command object. If this command
		/// object has a connection that is enrolled in a transaction, the DeriveParameters method does not
		/// honor that transaction, and the call fails. To avoid this, create your own connection and
		/// command, and use them. 
		/// 
		/// You then have to clone each of the IDataParameter objects before it can be transferred to 
		/// the original command, or another exception is thrown.
		/// </devdoc>
		private OleDbCommand CreateNewCommandAndConnectionForDiscovery()
		{
			OleDbConnection clonedConnection = (OleDbConnection)((ICloneable)this.command.Connection).Clone();
			clonedConnection.Open();
			OleDbCommand newCommand = CreateCommand(this.command.CommandText, this.command.CommandType, parameterValues);
			newCommand.Connection = clonedConnection;

			return newCommand;
		}
		// *** I added object[] parameterValues
		private static OleDbCommand CreateCommand(string commandText, CommandType commandType, Object[] parameterValues)
		{
			OleDbCommand newCommand = new OleDbCommand();
			newCommand.CommandText = commandText;
			newCommand.CommandType = commandType;

			// *** If it is a stored procedure Loop through the parameters
			// *** and add them
		
			if ((commandType == CommandType.StoredProcedure) & (parameterValues != null))
			{
				Array myArray = parameterValues;
				OleDbParameterCollection myParamCollection = newCommand.Parameters;
				// Add the Parameters
				int intCountofParameters = parameterValues.Length + 1;
				int intCount;
				for (intCount = 1; intCount < intCountofParameters; intCount++) 
				{
					string myName = "[Parameter" + (intCount - 1).ToString() + "]";
					string myValue = myArray.GetValue(intCount - 1).ToString();

					OleDbParameter myParameter = new OleDbParameter(myName, OleDbType.VarChar);
					myParameter.Value = myValue;

					object myParm = myParameter;
					newCommand.Parameters.Add(myParm);
				}
			}
	
			return newCommand;
		}

		private string BuildParameterName(string name)
		{
			//System.Diagnostics.Debug.Assert(parameterToken != 0x0000);
			if (name[0] != this.parameterToken)
			{
				return name.Insert(0, new string(this.parameterToken, 1));
			}
			return name;
		}
	}
}