context: // <file>
  //     <copyright see="prj:///doc/copyright.txt"/>
  //     <license see="prj:///doc/license.txt"/>
  //     <owner name="Luc Morin" email=""/>
  //     <version>$Revision$</version>
  // </file>
  
  using System;
  using System.Data;
  using System.Data.OleDb;
  
  using SharpQuery.Collections;
  using SharpQuery.Exceptions;
  using SharpQuery.SchemaClass;
  
  namespace SharpQuery.Connection
  {
  	//
  	// ADO .NET Wrapper (better than OleDB)
  	//
  	///<summary>
  	/// This class is associated with a class <see cref="ICSharpCode.SharpDevelop.Gui.SharpQuery.TreeView.SharpQueryNodeConnection"/>
  	///</summary>
  	public class OLEDBConnectionWrapper : AbstractSharpQueryConnectionWrapper
  	{
  		private System.Data.OleDb.OleDbConnection pOLEConnection = null;
  		private System.Data.OleDb.OleDbDataAdapter pOLEAdapter = new OleDbDataAdapter();
  
  		///<summary>
  		///  OLEDB connection String.
  		/// i use this for speed up the code writing ...
  		///</summary>
  		public override string ConnectionString
  		{
  			get
  			{
  				return base.ConnectionString;
  			}
  
  			set
  			{
  				if (IsOpen == true)
  				{
  					this.Close();
  				}
  				try
  				{
  					this.pOLEConnection.ConnectionString = value;
  					wrongConnectionString = false;
  				}
  				catch (OleDbException e)
  				{
  					string mes = this.ConnectionString + "\n\r";
  					wrongConnectionString = true;
  
  					foreach (OleDbError err in e.Errors)
  					{
  						mes += "-----------------\n\r";
  						mes += err.Message + "\n\r";
  						mes += err.NativeError + "\n\r";
  					}
  					throw new ConnectionStringException(mes);
  				}
  				catch (System.Exception e)
  				{
  					wrongConnectionString = true;
  					throw new ConnectionStringException(value
  					                                    + "\n\r"
  					                                    + "-------------------\n\r"
  					                                    + e.Message
  					                                   );
  				}
  			}
  		}
  
  		public override bool IsOpen
  		{
  			get
  			{
  				return (this.pOLEConnection.State == ConnectionState.Open);
  			}
  		}
  
  		public override object GetProperty(AbstractSharpQueryConnectionWrapper.SharpQueryPropertyEnum property)
  		{
  			object returnValue = null;
  
  			switch (property)
  			{
  				case SharpQueryPropertyEnum.Catalog:
  				case SharpQueryPropertyEnum.DataSourceName:
  					returnValue = this.pOLEConnection.Database;
  					break;
  				case SharpQueryPropertyEnum.ConnectionString:
  					returnValue = this.pOLEConnection.ConnectionString.ToString();
  					break;
  				case SharpQueryPropertyEnum.DataSource:
  					returnValue = this.pOLEConnection.DataSource;
  					break;
  				case SharpQueryPropertyEnum.DBMSName:
  					returnValue = "";
  					break;
  				case SharpQueryPropertyEnum.ProviderName:
  					//Key = "Provider Name";
  					returnValue = this.pOLEConnection.Provider.ToString();
  					break;
  				default:
  					returnValue = null;
  					break;
  			}
  
  			return returnValue;
  		}
  
  		/// <summary>
  		/// Creates a new DataConnection object
  		/// </summary>
  		public OLEDBConnectionWrapper()
  			: base()
  		{
  			this.pOLEConnection = new System.Data.OleDb.OleDbConnection();
  			this.pOLEAdapter = new System.Data.OleDb.OleDbDataAdapter();
  			this.pOLEAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
  		}
  
  		public OLEDBConnectionWrapper(string connectionString)
  			: this()
  		{
  			this.ConnectionString = connectionString;
  		}
  
  		public override bool Open()
  		{
  			try
  			{
  				if (this.IsOpen == false && wrongConnectionString == false)
  				{
  					this.pOLEConnection.Open();
  				}
  			}
  			catch (OleDbException e)
  			{
  				string mes = this.ConnectionString + "\n\r";
  				wrongConnectionString = true;
  
  				foreach (OleDbError err in e.Errors)
  				{
  					mes += "-----------------\n\r";
  					mes += err.Message + "\n\r";
  					mes += err.NativeError + "\n\r";
  				}
  				throw new OpenConnectionException(mes);
  			}
  			catch (System.Exception)
  			{
  				wrongConnectionString = true;
  				throw new OpenConnectionException(this.ConnectionString);
  			}
  
  			return this.IsOpen;
  		}
  
  		public override void Close()
  		{
  			if (this.IsOpen == true)
  			{
  				this.pOLEConnection.Close();
  			}
  		}
  
  		///<summary>
  		/// Execute a SQL command
  		/// <param name="SQLText">
  		/// SQL command to execute
  		/// </param>
  		/// <param name="rows">
  		/// Maximum number of row to extract. If is "0" then all rows are extracted.
  		/// </param>
  		/// <returns> return a <see cref="System.Data.DataTable">DataTable</see>
  		///or a <see cref="System.Data.DataSet">DataSet</see> object.
  		/// </returns>		/// </summary>
  		public override object ExecuteSQL(string SQLText, int rows)
  		{
  			OleDbCommand command = new OleDbCommand();
  			DataSet returnValues = new DataSet();
  
  			command.Connection = this.pOLEConnection;
  			command.CommandText = SQLText;
  			command.CommandType = System.Data.CommandType.Text;
  
  			// some stranges things occurs with the OleDbDataAdapter and transaction
  			command.Transaction = this.pOLEConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
  
  			try
  			{
  				this.pOLEAdapter.SelectCommand = command;
  				this.pOLEAdapter.Fill(returnValues);
  
  			}
  			catch (OleDbException e)
  			{
  				command.Transaction.Rollback();
  
  				string mes = SQLText + "\n\r";
  
  				foreach (OleDbError err in e.Errors)
  				{
  					mes += "-----------------\n\r";
  					mes += err.Message + "\n\r";
  					mes += err.NativeError + "\n\r";
  				}
  				throw new ExecuteSQLException(mes);
  			}
  			catch (System.Exception)
  			{
  				command.Transaction.Rollback();
  				throw new ExecuteSQLException(SQLText);
  			}
  			finally
  			{
  				command.Transaction.Commit();
  			}
  
  			return returnValues;
  		}
  
  		///<summary>
  		/// Execute a stocked procedure.
  		/// <param name="schema">
  		/// <see cref="SharpQuery.SchemaClass">SchemaClass</see> object.
  		/// </param>
  		/// <param name="rows">
  		/// Maximum number of row to extract. If is "0" then all rows are extracted.
  		/// </param>
  		/// <returns> return a <see cref="System.Data.DataTable">DataTable</see>
  		///or a <see cref="System.Data.DataSet">DataSet</see> object.
  		/// </returns>		/// </summary>
  		public override object ExecuteProcedure(ISchemaClass schema, int rows, SharpQuerySchemaClassCollection parameters)
  		{
  
  			DataSet returnValues = null;
  
  			if (schema == null)
  			{
  				throw new System.ArgumentNullException("schema");
  			}
  
  			OleDbCommand command = new OleDbCommand();
  			OleDbParameter para = null;
  			returnValues = new DataSet();
  
  			command.Connection = this.pOLEConnection;
  			command.CommandText = schema.Name;
  			command.CommandType = System.Data.CommandType.StoredProcedure;
  
  			if (parameters != null)
  			{
  				foreach (SharpQueryParameter classParam in parameters)
  				{
  					para = new OleDbParameter();
  					para.DbType = classParam.DataType;
  					para.Direction = (ParameterDirection)classParam.Type;
  					para.ParameterName = classParam.Name;
  					if (para.ParameterName.StartsWith("["))
  					{
  						para.ParameterName = para.ParameterName.Remove(0, 1);
  					}
  					if (para.ParameterName.EndsWith("]"))
  					{
  						para.ParameterName = para.ParameterName.Remove(para.ParameterName.Length - 1, 1);
  					}
  					para.Value = classParam.Value;
  					command.Parameters.Add(para);
  				}
  			}
  
  			//				command.Prepare();
  			command.Transaction = this.pOLEConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
  
  			try
  			{
  				this.pOLEAdapter.SelectCommand = command;
  				this.pOLEAdapter.Fill(returnValues);
  			}
  			catch (OleDbException e)
  			{
  				command.Transaction.Rollback();
  
  				string mes = schema.Name + "\n\r";
  
  				foreach (OleDbError err in e.Errors)
  				{
  					mes += "-----------------\n\r";
  					mes += err.Message + "\n\r";
  
gt: 					mes += err.NativeError + "\n\r";