title: Importing an Excel Sheet and Validate the Imported Data with Loosely Coupled
body: <p>I am trying to develop a module which will read excel sheets (possibly from other data sources too, so it should be loosely coupled) and convert them into Entities so to save.</p>
  
  <p>The logic will be this:</p>
  
  <ol>
  <li>The excel sheet can be in different format, for example column names in Excel sheet can be different so my system needs to be able to map different fields to my entities.</li>
  <li>For now I will be assuming the format defined above will be same and hardcoded for now instead of coming from database dynamically after set on a configuration mapping UI kinda thing.</li>
  <li>The data needs to be validated before even get mapped. So I should be able validate it beforehand against something. We're not using like XSD or something else so I should validate it against the object structure I am using as a template for importing.</li>
  </ol>
  
  <p>The problem is, I put together some things together but I don't say I liked what I did. <strong>My Question is how I can improve the code below and make things more modular and fix the validation issues.</strong></p>
  
  <p><strong>The code below is a mock-up and is not expected to work, just to see some structure of the design.</strong></p>
  
  <p>This is code I've come up with so far, and I've realized one thing that I need to improve my design patterns skills but for now I need your help, if you could help me:</p>
  
  <pre><code>//The Controller, a placeholder
  class UploadController
  {
      //Somewhere here we call appropriate class and methods in order to convert
      //excel sheet to dataset
  }
  </code></pre>
  
  <p>After we uploaded file using an MVC Controller, there could be different controllers specialized to import certain behaviors, in this example I will uploading person related tables,</p>
  
  <pre><code>interface IDataImporter
  {
      void Import(DataSet dataset);
  }
  </code></pre>
  
  <p>//We can use many other importers besides PersonImporter
  class PersonImporter : IDataImporter
  {
      //We divide dataset to approprate data tables and call all the IImportActions
      //related to Person data importing
      //We call inserting to database functions here of the DataContext since this way
      //we can do less db roundtrip.</p>
  
  <pre><code>public string PersonTableName {get;set;}
  public string DemographicsTableName {get;set;}
  
  public Import(Dataset dataset)
  {
      CreatePerson();
      CreateDemograhics();
  }
  
  //We put different things in different methods to clear the field. High cohesion.
  private void CreatePerson(DataSet dataset)
  {   
      var personDataTable = GetDataTable(dataset,PersonTableName);
      IImportAction addOrUpdatePerson = new AddOrUpdatePerson();
      addOrUpdatePerson.MapEntity(personDataTable);
  }
  
  private void CreateDemograhics(DataSet dataset)
  {
      var demographicsDataTable = GetDataTable(dataset,DemographicsTableName);
      IImportAction demoAction = new AddOrUpdateDemographic(demographicsDataTable);
      demoAction.MapEntity();
  }
  
  private DataTable GetDataTable(DataSet dataset, string tableName)
  {
      return dataset.Tables[tableName];
  }
  </code></pre>
  
  <p>}</p>
  
  <p>I have <code>IDataImporter</code> and specialized concrete class <code>PersonImporter</code>. However, I am not sure it looks good so far since things should be SOLID so basically easy to extend later in the project cycle, this will be a foundation for future improvements, lets keep going:</p>
  
  <p><code>IImportActions</code> are where the magic mostly happens. Instead of designing things table based, I am developing it behavior based so one can call any of them to import things in more modular model. For example a table may have 2 different actions.</p>
  
  <pre><code>interface IImportAction
  {
      void MapEntity(DataTable table);
  }
  
  //A sample import action, AddOrUpdatePerson
  class AddOrUpdatePerson : IImportAction
  {
      //Consider using default values as well?
      public string FirstName {get;set;}
      public string LastName {get;set;}
      public string EmployeeId {get;set;}
      public string Email {get;set;}
  
      public void MapEntity(DataTable table)
      {
          //Each action is producing its own data context since they use
          //different actions.
          using(var dataContext = new DataContext())
          {
              foreach(DataRow row in table.Rows)
              {
                  if(!emailValidate(row[Email]))
                  {
                      LoggingService.LogWarning(emailValidate.ValidationMessage);
                  }
  
                  var person = new Person(){
                      FirstName = row[FirstName],
                      LastName = row[LastName],
                      EmployeeId = row[EmployeeId],
                      Email = row[Email]
                  };
  
                  dataContext.SaveObject(person);
              }
  
              dataContext.SaveChangesToDatabase();
          }
      }   
  }
  
  class AddOrUpdateDemographic: IImportAction
  {
      static string Name {get;set;}
      static string EmployeeId {get;set;}
  
      //So here for example, we will need to save dataContext first before passing it in 
      //to get the PersonId from Person (we're assuming that we need PersonId for Demograhics)    
      public void MapEntity(DataTable table)
      {
          using(var dataContext = new DataCOntext())
          {
              foreach(DataRow row in table.Rows)
              {
                  var demograhic = new Demographic(){
                      Name = row[Name],
                      PersonId = dataContext.People.First(t =&gt; t.EmployeeId = int.Parse(row["EmpId"]))
                  };
  
                  dataContext.SaveObject(person);
              }
  
              dataContext.SaveChangesToDatabase();
          }
      }
  }
  </code></pre>
  
  <p>And the validation, which mostly where I suck at unfortunately. The validation needs to be easy to extend and loosely coupled and also I need to be able to call this validation beforehand instead of adding everything.</p>
  
  <pre><code>public static class ValidationFactory
  {
      public static Lazy&lt;IFieldValidation&gt; PhoneValidation = new Lazy&lt;IFieldValidation&gt;(()=&gt;new PhoneNumberValidation());
      public static Lazy&lt;IFieldValidation&gt; EmailValidation = new Lazy&lt;IFieldValidation&gt;(()=&gt;new EmailValidation());
      //etc.
  }
  
  interface IFieldValidation
  {
      string ValidationMesage{get;set;}
      bool Validate(object value);
  }
  
  class PhoneNumberValidation : IFieldValidation
  {
      public string ValidationMesage{get;set;}
      public bool Validate(object value)
      {   
          var validated = true; //lets say...
          var innerValue = (string) value;
          //validate innerValue using Regex or something
          //if validation fails, then set ValidationMessage propert for logging.
          return validated;
      }
  }
  
  class EmailValidation : IFieldValidation
  {
      public string ValidationMesage{get;set;}
      public bool Validate(object value)
      {   
          var validated = true; //lets say...
          var innerValue = (string) value;
          //validate innerValue using Regex or something
          //if validation fails, then set ValidationMessage propert for logging.
          return validated;
      }
  }
  </code></pre>