Pluggable data access components - Part 2

using System;
using System.Data;
using System.Data.OleDb;
using System.Text.RegularExpressions;

namespace Opal2.Data
{
	/// <summary>
	/// A OleDb specific driver
	/// </summary>
	/// <example>
	/// This example explains how to begin a new databasesession.
	/// <code>
	///	DatabaseSessionFactory.Instance.DriverType="SQL";
	///	DatabaseSessionFactory.Instance.ConnectionString=
	///		"Server=(local);Database=Northwind;Integrated Security=SSPI";
	///		
	///	DatabaseSession session = DatabaseSessionFactory.Instance.BeginSession();
	/// </code>
	/// </example>
	public class OleDbDatabaseSession: DatabaseSession, IDisposable
	{
		private OleDbConnection connection;

		/// <summary>
		/// Initializes a new instance of OleDbDatabaseSession
		/// </summary>
		/// <param name="connectionString">Connectionstring to use</param>
		public OleDbDatabaseSession(string connectionString)
		{
			connection = new OleDbConnection(connectionString);
		}

		/// <summary>
		/// Executes a query without getting the results
		/// </summary>
		/// <param name="query">Query to execute</param>
		/// <returns>Number of affected rows</returns>
		public override int ExecuteNonQuery(string query)
		{
			OleDbCommand cmd = new OleDbCommand(query,connection);
			
			return executeNonQuery(cmd);
		}

		/// <summary>
		/// Executes a command without getting the results
		/// </summary>
		/// <param name="command">Command to execute</param>
		/// <returns>Number of affected rows</returns>
		public override int ExecuteNonQuery(DatabaseCommand command)
		{
			return executeNonQuery(buildCommand(command));
		}

		/// <summary>
		/// Executes a query and gets the results
		/// in a datareader
		/// </summary>
		/// <param name="query">Query to execute</param>
		/// <returns>Datareader containing the results</returns>
		public override IDataReader ExecuteQuery(string query)
		{
			return executeReader(new OleDbCommand(query,connection));
		}



		/// <summary>
		/// Executes a command and gets the results
		/// in a datareader
		/// </summary>
		/// <param name="command">Command to execute</param>
		/// <returns>Datareader containing the results</returns>
		public override IDataReader ExecuteQuery(DatabaseCommand command)
		{
			return executeReader(buildCommand(command));
		}

		/// <summary>
		/// Fills a dataset using a query
		/// </summary>
		/// <param name="query">The query to execute</param>
		/// <param name="ds">The dataset to fill</param>
		public override void FillDataSet(string query, DataSet ds)
		{
			fillDataSet(new OleDbCommand(query,connection),ds);
		}

		/// <summary>
		/// Fills a dataset using a command
		/// </summary>
		/// <param name="command">The command to execute</param>
		/// <param name="ds">The dataset to fill</param>
		public override void FillDataSet(DatabaseCommand command, DataSet ds)
		{
			fillDataSet(buildCommand(command),ds);
		}

		/// <summary>
		/// Fills a dataset using a query
		/// </summary>
		/// <param name="query">The query to execute</param>
		/// <param name="ds">The dataset to fill</param>
		public override void FillDataSet(string query,DataSet ds,string table) 
		{
			throw new NotImplementedException();
		}

		/// <summary>
		/// Updates a dataset in the database
		/// </summary>
		/// <param name="ds">The DataSet to update</param>
		/// <param name="table">The table to update in the DataSet</param>
		public override void UpdateDataSet(DataSet ds, string table)
		{
			OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM " + table,connection);
			OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);

			adapter.Update(ds,table);
		}


		#region Private implementation details

		/// <summary>
		/// Fills a dataset using a OleDb command
		/// </summary>
		/// <param name="cmd">Command to use</param>
		/// <param name="ds">DataSet to fill</param>
		private void fillDataSet(OleDbCommand cmd,DataSet ds) 
		{
			OleDbDataAdapter da = new OleDbDataAdapter(cmd);
			da.Fill(ds);
		}

		/// <summary>
		/// Executes a SQL command without getting the results
		/// </summary>
		/// <param name="cmd">SQL command to execute</param>
		/// <returns>Number of affected rows</returns>
		private int executeNonQuery(OleDbCommand cmd) 
		{
			OleDbTransaction tx = null;
			int affectedRows = 0;

			try 
			{
				connection.Open();
				
				tx = connection.BeginTransaction();
				cmd.Transaction = tx;

				affectedRows = cmd.ExecuteNonQuery();	
			
				tx.Commit();
			} 
			catch 
			{
				if(tx != null)
					tx.Rollback();

				throw;
			}
			finally 
			{
				connection.Close();
			}

			return affectedRows;
		}

		/// <summary>
		/// Executes a SQL Command and gets the results 
		/// in a datareader
		/// </summary>
		/// <param name="cmd">SQL Command to execute</param>
		/// <returns>DataReader containing the results</returns>
		private OleDbDataReader executeReader(OleDbCommand cmd) 
		{
			OleDbDataReader reader = null;
			OleDbTransaction tx = null;

			try 
			{
				connection.Open();
				
				tx = connection.BeginTransaction();
				cmd.Transaction = tx;

				reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);				

				tx.Commit();
			} 
			catch
			{
				if(tx != null)
					tx.Rollback();

				throw;
			}
			finally 
			{
				connection.Close();
			}

			return reader;
		}
		
		/// <summary>
		/// Builds a SQL command out of the opal command structure
		/// </summary>
		/// <param name="command">Command to convert</param>
		/// <returns>The converted command</returns>
		private OleDbCommand buildCommand(DatabaseCommand command) 
		{
			OleDbCommand cmd = new OleDbCommand(command.Query,connection);

			cmd.CommandType = command.Type;

			//Place all the parameters in the right order
			Regex regex = new Regex(@"@(\S+)");
			MatchCollection matches = regex.Matches(cmd.CommandText);
			
			foreach(Match m in matches) 
			{
				foreach(CommandParameter param in command.Parameters) 
				{
					if(param.Name == m.Value) 
					{
						//Remove the @ sign at the beginning of the parametername
						//when adding it to the command
						OleDbParameter parameter = new OleDbParameter(param.Name.Substring(1),param.Value);

						parameter.Direction = param.Type;
						
						cmd.CommandText = cmd.CommandText.Replace(m.Value,"?");
						cmd.Parameters.Add(parameter);
					}
				}
			}

			return cmd;
		}

		#endregion

		#region IDisposable Members

		/// <summary>
		/// Disposes all unmanaged resources
		/// </summary>
		public void Dispose()
		{
			connection.Dispose();
		}

		#endregion
	}
}