Miscellany

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Transactions;
using System.Configuration;
using System.Data.SqlTypes;
using DumbCompany.Diagnostics.Exceptions.Services;
using DumbCompany.Diagnostics.Logging;

namespace DumbCompany.DataSource.Base
{
	/// <summary>
	/// This class serves as the base for all data controller objects, providing
	/// access to the defined data sources.
	/// </summary>
	public abstract class BaseDataSourceController
	{
		#region Enumerations
		/// <summary>
		/// Specify the execution type of the database function passed.
		/// </summary>
		protected enum ExecutionType
		{
			/// <summary>
			/// The command to be executed is a Read command.
			/// </summary>
			Read,

			/// <summary>
			/// The command to be executed is an Insert command.
			/// </summary>
			Insert,

			/// <summary>
			/// The command to be executed is an Update command.
			/// </summary>
			Update,

			/// <summary>
			/// The command to be executed is a Delete command.
			/// </summary>
			Delete
		}
		#endregion

		#region Constants

		/// <summary>
		/// This is the name of the connection string configuration settings
		/// for connecting to a Sql database.
		/// </summary>
		protected const string CONNECTION_STRING_SQL = "SqlDatabaseConnection";

        /// <summary>
        /// The name of the table containing the primary results of the executed
        /// command.
        /// </summary>
        protected const string TABLE_PRIMARY = "PrimaryResults";

		#endregion

		#region Protected Members

		protected string _sqlDatabaseConnectionString = string.Empty;

		/// <summary>
		/// The connection to the Sql database.
		/// </summary>
		protected SqlConnection _sqlConnection = null;

		#endregion

		#region Constructor
		/// <summary>
		/// Default constructor.
		/// </summary>
		public BaseDataSourceController()
		{
			//call method to setup the connection string(s)
			setupConnectionStrings();
		}

		/// <summary>
		/// This method will retreive all strings from the configuration file.
		/// </summary>
		private void setupConnectionStrings()
		{
			ConnectionStringSettings settings = null;

			//Sql connection string
			settings = ConfigurationManager.ConnectionStrings[CONNECTION_STRING_SQL];
			if (settings != null)
			{
				_sqlDatabaseConnectionString = settings.ConnectionString;
			}
		}
		#endregion

		#region Sql Execution
		/// <summary>
		/// Execute a Sql Command against the Sql Database.
		/// </summary>
		/// <param name="sqlCommand">
		/// The command to be executed.</param>
		/// <param name="executionType">
		/// The type of command being executed.</param>
		/// <returns>
		/// Populated dataset with results of the command's execution.</returns>
		protected DataSet sqlCommandExecute(SqlCommand sqlCommand, ExecutionType executionType)
		{
			//write the command data for logging
			Information.WriteCommandInfo(sqlCommand);

			//TEST: throw exception
			//Error.Write(new Exception("Testing error logging to event log."));
			//throw new ApplicationException("Testing thrown exception logging.");

			//instantiate return object for filling
			DataSet dataSet = new DataSet();
            dataSet.Tables.Add(TABLE_PRIMARY);

            //configure the dataset for performance
            dataSet.EnforceConstraints = false;

			try
			{
				//create connection and assign it to the command
				_sqlConnection = new SqlConnection(_sqlDatabaseConnectionString);
				sqlCommand.Connection = _sqlConnection;

				//check type of command
				if (executionType == ExecutionType.Read)
				{
                    //configure the table for performance - begin load
                    dataSet.Tables[0].BeginLoadData();

                    //create adapter and fill the dataset
                    SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCommand);
                    sqlAdapter.Fill(dataSet.Tables[0]);

                    //end load of data in table
                    dataSet.Tables[0].EndLoadData();
				}
                else if (executionType == ExecutionType.Update ||
                    executionType == ExecutionType.Insert ||
                    executionType == ExecutionType.Delete)
                {
                    //open the connection
                    _sqlConnection.Open();

                    //execute the function
                    sqlCommand.ExecuteNonQuery();

                    //TODO: implement functionality to return updated or inserted data
                    //into dataset for resulting object population
                }
			}
			catch (Exception ex)
			{
				//throw a data source exception with the command details
				throw new DataSourceException(DataSourceException.ParseFailedCommand(sqlCommand, ex), ex);
			}
			finally
			{
				//close and dispose of the connection
				if (_sqlConnection != null)
				{
					_sqlConnection.Close();
					_sqlConnection.Dispose();
				}
			}

			//return the completed data set
			return dataSet;
		}


        /// <summary>
        /// Execute a Sql Command against the Sql Database which is expected to return
        /// XML data..
        /// </summary>
        /// <param name="sqlCommand">
        /// The command to be executed.</param>
        /// <param name="executionType">
        /// The type of command being executed.</param>
        /// <returns>
        /// Populated SqlXml object with results of the command's execution.</returns>
        protected SqlXml sqlXmlCommandExecute(SqlCommand sqlCommand, ExecutionType executionType)
        {
			//write the command data for logging
			Information.WriteCommandInfo(sqlCommand);

            //instantiate return object
            SqlXml xmlData = null;

            try
            {
                //create connection and assign it to the command
                _sqlConnection = new SqlConnection(_sqlDatabaseConnectionString);
                sqlCommand.Connection = _sqlConnection;

                //check type of command
                if (executionType == ExecutionType.Read)
                {
                    //xml data is to be returned - open the connection and execute it
                    _sqlConnection.Open();
                    SqlDataReader dataReader = sqlCommand.ExecuteReader();
                    if (dataReader.Read())
                    {
                        if (!dataReader.IsDBNull(0))
                            xmlData = dataReader.GetSqlXml(0);
                    }
                }
                else if (executionType == ExecutionType.Update ||
                    executionType == ExecutionType.Insert ||
                    executionType == ExecutionType.Delete)
                {
                   //TODO: implement when necessary
                    throw new NotImplementedException("XmlCommand execution for Update, Insert and Delete is not implemented.");
                }
            }
            catch (Exception ex)
            {
				//throw a data source exception with the command details
				throw new DataSourceException(DataSourceException.ParseFailedCommand(sqlCommand, ex), ex);
            }
            finally
            {
                //close and dispose of the connection
                if (_sqlConnection != null)
                {
                    _sqlConnection.Close();
                    _sqlConnection.Dispose();
                }
            }

            //return the completed data set
            return xmlData;
        }
		#endregion
	}
}