Wrapper Class for Parsing Fixed-Width, Multiple Section Files

using System;
using System.Xml;
using System.Xml.XPath;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Diagnostics;
using System.Configuration ;
using System.Text;
using ReportParser;

namespace ReportParserDemo
{
	/// <summary>
	/// Summary description for Importer.
	/// </summary>
	public class Importer
	{

		#region Private Variables
		private StringBuilder strLog = new StringBuilder();
		private string ConnectionString;
		private string SchemaFile ;
		#endregion

		#region Properties

		private string filename = "";
		public string FileName
		{
			get { return filename; }
			set { filename = value; }			
		}

		private string fullname = "";
		public string FullName
		{
			get { return fullname; }
			set { fullname = value; }			
		}

		private EventLog log = null;
		public EventLog Log
		{
			get { return log; }
			set { log = value; }
		}
		#endregion

		public Importer()
		{

			ConnectionString = ConfigurationSettings.AppSettings["ConnectionString"];

			SchemaFile = ConfigurationSettings.AppSettings["PrintImportMonitor.SchemaFile"];
		}

		private SqlConnection GetConnection()
		{
			SqlConnection connection = new SqlConnection(ConnectionString);
			connection.Open();
			return connection;
		}

		/// <summary>
		/// Primary Method of service; used by the ServiceMonitor to execute the service.
		/// </summary>
		/// <returns>true if no errors occured</returns>
		public bool Execute()
		{
			try
			{
				
				string filePath = fullname;
				string schemaPath = SchemaFile;

				FileParser oFP = new FileParser(filePath, schemaPath);

				try
				{
					DateTime dtStart = DateTime.Now;
					DataSet oDS = oFP.ParseToDataSet();
					DateTime dtEnd = DateTime.Now;
					TimeSpan ts= dtEnd - dtStart;
						
					log.WriteEntry(String.Format("Successfully processed File Import in {0} milliseconds.", ts.Milliseconds), EventLogEntryType.SuccessAudit);

					string sXMLFile = Path.Combine (Path.GetDirectoryName(filePath) , "rdaprnt.xml");

					// Write Out DataSet for Diagnostic Analysis.
					oDS.WriteXml(sXMLFile);
					
					// Write the Stuff to the database.
					return ImportDataSet(oDS);

				}
				catch(Exception ex)
				{
					log.WriteEntry("Error occured in Method Execute: " + ex.Message + "\nStack Trace: " + ex.StackTrace, EventLogEntryType.Error );
					return false;
				}
			}
			catch (Exception ex)
			{
				string Message = String.Format("Execute: Exception Occured: {0}"+
					"\nStack Trace: {1}", ex.Message, ex.StackTrace);
				Log.WriteEntry(Message, EventLogEntryType.Error );
				Console.WriteLine(Message);
				return false;
			}
		}

		/// <summary>
		/// ImportDataSet consolidates all Database Transactions... Runs each individual step of 
		/// import to database functionality.
		/// </summary>
		/// <param name="oDS"></param>
		/// <returns></returns>
		private bool ImportDataSet(DataSet oDS)
		{
		
			SqlConnection conn = GetConnection();
			SqlTransaction transaction;
			transaction = conn.BeginTransaction();

			try
			{
				if (!PruneDataStore(oDS, conn, transaction)) 
				{
					transaction.Rollback();
					return false;
				}
				if (!PersistDataSet(oDS, conn, transaction))
				{
					transaction.Rollback();
					return false;
				}
				transaction.Commit();
				conn.Close();
				return true;
			}
			catch (SqlException ex)
			{
				Log.WriteEntry(String.Format("ImportDataSet: SQL Exception Occured: {0}"+
					"\nStack Trace: {1}", ex.Message, ex.StackTrace), EventLogEntryType.Error );
			}
			catch (Exception ex)
			{
				Log.WriteEntry(String.Format("ImportDataSet: Exception Occured: {0}"+
					"\nStack Trace: {1}", ex.Message, ex.StackTrace), EventLogEntryType.Error );
			}
			finally
			{
				if ( !(conn == null) && (conn.State == ConnectionState.Open) )
					conn.Close();
			}
			return false;
		}

		/// <summary>
		/// PruneDataStore: Specific to each application, requires understanding of database structure.
		/// loops through Header records deleting all child data to prevent duplicates
		/// </summary>
		/// <param name="oDS"></param>
		/// <param name="conn"></param>
		/// <param name="transaction"></param>
		/// <returns></returns>
		private bool PruneDataStore(DataSet oDS, SqlConnection conn, SqlTransaction transaction)
		{

			string[] names = {"ImportedHeader", "ImportedDetails", "ImportedFooter"};
			string sSQL = "DELETE FROM {0} " + 
				"WHERE ManCode=@ManCode " + 
				"AND OtherCode=@OtherCode " ; 
			int RecordsAffected;   // put here to see in Locals window when debugging ...

			DataTable oDT = oDS.Tables["ImportedHeader"];
			foreach (DataRow  oTR in oDT.Rows)
			{
				string ManCode = oTR["ManCode"].ToString();
				string OtherCode = oTR["OtherCode"].ToString();
				foreach (string s in names)
				{
					SqlCommand cmd = new SqlCommand(string.Format(sSQL, s), conn, transaction);
					cmd.Transaction = transaction;
					cmd.Parameters.Add("@ManCode",SqlDbType.VarChar, 3).Value = ManCode;
					cmd.Parameters.Add("@OtherCode",SqlDbType.VarChar, 5).Value = OtherCode;
					// Add more parameters as necessary ...
					RecordsAffected = cmd.ExecuteNonQuery();
				}

			}
			
			return true;
		
		}

		/// <summary>
		/// PersistDataSet persists the dataset imported by ReportParser the database.
		/// </summary>
		/// <param name="oDS"></param>
		/// <param name="conn"></param>
		/// <param name="transaction"></param>
		/// <returns></returns>
		private bool PersistDataSet(DataSet oDS, SqlConnection conn, SqlTransaction transaction)
		{
			try
			{
				SqlCommand cmdHeader = new SqlCommand("SELECT * FROM " + oDS.Tables[0].TableName);
				cmdHeader.Connection = conn;
				cmdHeader.Transaction = transaction;			
				SqlDataAdapter oDAHeader = new SqlDataAdapter();
				oDAHeader.SelectCommand=cmdHeader;

				SqlCommand cmdDetails = new SqlCommand("SELECT * FROM " + oDS.Tables[1].TableName);
				cmdDetails.Connection = conn;
				cmdDetails.Transaction = transaction;			
				SqlDataAdapter oDADetails = new SqlDataAdapter();
				oDADetails.SelectCommand=cmdDetails;

				SqlCommand cmdFooter = new SqlCommand("SELECT * FROM " + oDS.Tables[2].TableName);
				cmdFooter.Connection = conn;
				cmdFooter.Transaction = transaction;			
				SqlDataAdapter oDAFooter = new SqlDataAdapter();
				oDAFooter.SelectCommand=cmdFooter;

				DataSet oChangesDS = oDS.GetChanges();
				if (oChangesDS != null)
				{
					SqlCommandBuilder oCmdBuilderHeader = new SqlCommandBuilder(oDAHeader);
					oCmdBuilderHeader.QuotePrefix = "[";
					oCmdBuilderHeader.QuoteSuffix = "]";
					oDAHeader.Update(oDS, oDS.Tables[0].TableName);
				
					SqlCommandBuilder oCmdBuilderDetails = new SqlCommandBuilder(oDADetails);
					oCmdBuilderDetails.QuotePrefix = "[";
					oCmdBuilderDetails.QuoteSuffix = "]";
					oDADetails.Update(oDS, oDS.Tables[1].TableName);
					
					SqlCommandBuilder oCmdBuilderFooter = new SqlCommandBuilder(oDAFooter);
					oCmdBuilderFooter.QuotePrefix = "[";
					oCmdBuilderFooter.QuoteSuffix = "]";
					oDAFooter.Update(oDS, oDS.Tables[2].TableName);

					oDS.AcceptChanges();
				}
				return true;
			}
			catch (Exception ex)
			{
				Log.WriteEntry(String.Format("UpdateTrafficQueue: Exception Occured: {0}", ex.Message), EventLogEntryType.Error  );
				return false;
			}
		}
	
	}
}