title: Using parallel tasks to load flat files into database with a separate process (sql loader.dll)
body: <p>I am wondering if using parallel processing will be advantageous in the following scenario: </p>
  
  <p>I have written a basic console application that is intended to run on Windows Task Scheduler. Its main goal in life is to process n number of flat files using a utility called SQL Loader. The DBA, whose idea it was to use SQL Loader and encapsulate as much business logic in Oracle as possible, and use .NET simply for calling out to the utility, wants the ability to schedule/run multiple copies of this console app to make it faster (some of the files may be rather large and thus may take some time to process). </p>
  
  <p>Instead of running "multiple copies", I thought of running separate threads to share the workload, where the number of threads can be configured by an admin (they want as much control over this app as possible). </p>
  
  <p>I am relatively new to .NET/C# with no experience in this, so I am trying to think of an elegant way to share the work load and I came across the Task Parallel Library. </p>
  
  <p>Here is the main function that gets a list of ids (representing files) to load using the sqlldr.dll</p>
  
  <pre><code>public static void FindFilesToLoad(int monitorId)
      {
          string connectionString = string.Empty;
          string tableTypeName = string.Empty;
          int dxmtId = 0;
  
          connectionString = Helper.GetPlxConnectionString();
          using (OracleConnection connection = new OracleConnection(connectionString))
          {
              using (OracleCommand cmd = new OracleCommand("OraclePackage.getWorkToDo", CommandType.StoredProcedure))
              {
                  cmd.AddInputParameter&lt;int&gt;("P_tpmID", OracleDbType.Int16, monitorId);
                  OracleParameter p_dxmtId = cmd.AddOutputParameter&lt;OracleParameter&gt;("O_dxmtID", OracleDbType.Table, null);
                  p_dxmtId.ObjectTypeName = "Work_Table";
  
                  cmd.Connection = connection;
                  connection.Open();
                  cmd.ExecuteNonQuery();
  
                  OracleTable dxmtIds = (OracleTable)p_dxmtId.Value;
                  if (dxmtIds.Count; != 0)
                  {
                      for (int i = 0; i &lt; dxmtIds.Count;; i++)
                      {
                          try
                          {
                              dxmtId = Convert.ToInt32(dxmtIds[i]);
  
                              LoadFileIntoDatabase(monitorId, dxmtId);
                          }
                          catch (Exception e)
                          {
                              SetFileLoadStatus(FileLoadStatus.Error, monitorId, dxmtId, e.Message);
                          }
                      }
                  }
                  else
                  {
                      LogMessage(monitorId, "FindFilesToLoad", "No file records to process.");
                  }
              }
          }
      }
  </code></pre>
  
  <p>Here is the file loading part:</p>
  
  <pre><code>public static void LoadFileIntoDatabase(int monitorId,  int fileId)
      {
          int exitCode = 0;
          string myCommand = string.Empty;
          string controlFileWithPath = string.Empty;
          string controlFileName = string.Empty;
          string errorMsg = string.Empty;
          string parFileWithPath = string.Empty;
          string timeStamp = string.Empty;
  
          try
          {
              //create files for sql loader
              timeStamp = DateTime.Now.ToString("yyMMddHHmmss");
              controlFileWithPath = CreateSqlLoaderControlFile(monitorId, timeStamp);
              controlFileName = Path.GetFileName(controlFileWithPath);
              parFileWithPath = CreateParFile(monitorId, controlFileName, timeStamp);
  
              myCommand = @"CMD.EXE";
              ProcessStartInfo startInfo = new ProcessStartInfo(myCommand)
              {
                  WorkingDirectory = ConfigurationManager.AppSettings["ExportPath"].ToString(),
                  Arguments = @"/c SQLLDR CONTROL=" + controlFileWithPath + " PARFILE=" + parFileWithPath,
                  RedirectStandardOutput = true,
                  RedirectStandardError = true,
                  UseShellExecute = false
              };
  
              Process process = new Process();
              process.StartInfo = startInfo;
              process.Start();
              process.WaitForExit();
              exitCode = process.ExitCode;
  
              //0 is success, otherwise fail
              if (exitCode == 0)
              {
                  File.Delete(controlFileWithPath);
                  File.Delete(parFileWithPath);
              }
              else
              {
                  errorMsg = process.StandardError.ReadToEnd();
                  SetFileLoadStatus(FileLoadStatus.Error, monitorId, fileId, errorMsg);
                  SetLoadStatusToError(monitorId, fileId, errorMsg);
              }
          }
          catch (Exception e)
          {
              SetLoadStatusToError(monitorId, fileId, e.Message);
              SetFileLoadStatus(FileLoadStatus.Error, monitorId, fileId, e.Message);
          }
      }
  </code></pre>
  
  <p>Would something as simple as this be effective for making the file processing faster (and would this be preferable to using tasks in windows task scheduler)?: </p>
  
  <pre><code>static void Main()
  {
      //get number of parallel tasks from db
      int tasks = GetTaskCount();
      for(i=0; i&lt;tasks.count; i++)
      {
          Parallel.Invoke(() =&gt;
                      {
                          FindFilesToLoad();
                      };
      }
  
      LogMessage("Returned from Parallel.Invoke");
  
  }
  </code></pre>
  
  <p>Any advice/tips is much appreciated.</p>