C#
**

Stored Procedure
================

Tony wrote this code to do a bulk load using a stored procedure:

::

  using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Text;
  using System.IO;
  using System.Data;
  using System.Data.SqlClient;

  namespace InsertCodes
  {
      class Program
      {
          static void Main(string[] args)
          {
              string fPath = Environment.CurrentDirectory;
              string fExt = "*.txt";
              string fConn = "";
              string fTable = "";
              int fMax = 1;
              int fProcessed = 0;

              foreach (string arg in args)
              {
                  if (arg == "/?")
                  {
                      Showusage();
                      Environment.Exit(0);
                  }

                  else if (arg.ToLower().StartsWith("/max:"))
                  {
                      fMax = int.Parse(arg.Substring(5));
                      //break;
                  }

                  else if (arg.ToLower().StartsWith("/path:"))
                  {
                      string newpath = arg.Substring(6);
                      fPath = newpath.Replace("\"", "");
                      //break;
                  }
                  else if (arg.ToLower().StartsWith("/conn:"))
                  {
                      string newpath = arg.Substring(6);
                      fConn = newpath.Replace("\"", "");
                      //break;
                  }
                  else if (arg.ToLower().StartsWith("/table:"))
                  {
                      string newpath = arg.Substring(7);
                      fTable = newpath.Replace("\"", "");
                      //break;
                  }
                  else if (arg.ToLower().StartsWith("/filetype:"))
                  {
                      fExt = "*." + arg.Substring(10);
                      //break;
                  }
                  else
                  {
                      Console.WriteLine("Error - Unrecognised Parameter: " + arg);
                      Showusage();
                      Environment.Exit(-1);
                  }
              }

              if (fConn.Length < 1)
              {
                  Console.WriteLine("ERROR: Must specify connection string");
                  Showusage();
                  Environment.Exit(-1);
              }

              Console.WriteLine("");
              Console.WriteLine("");


              SqlConnection mySqlConnection = new SqlConnection(fConn);
              SqlCommand mySqlCommand;


              string[] AllFiles = Directory.GetFiles(fPath, fExt, SearchOption.TopDirectoryOnly);

              if (AllFiles.Length > 0)
              {
                  if (!Directory.Exists(fPath + @"\processed")) Directory.CreateDirectory(fPath + @"\processed");
                  mySqlConnection.Open();

                  if (mySqlConnection.State != ConnectionState.Open)
                  {
                      Console.WriteLine("Error connecting to SQL Database");
                      Environment.Exit(-1);
                  }
                  else
                  {
                      Console.WriteLine("Connected to SQL ...");
                  }

                  mySqlCommand = new SqlCommand();
                  mySqlCommand.Connection = mySqlConnection;
                  mySqlCommand.CommandType = CommandType.StoredProcedure;
                  mySqlCommand.CommandText = "bulk_code_insert";
                  mySqlCommand.Parameters.Add("@PathFileName", SqlDbType.VarChar);


                  if (fMax == 0) fMax = AllFiles.Length;

                  foreach (string filename in AllFiles)
                  {

                      fProcessed++;

                      if (fProcessed > fMax) break;

                      Console.WriteLine("Processing file: " + filename);

                      mySqlCommand.Parameters["@PathFileName"].Value = filename;

                      if (mySqlCommand.ExecuteNonQuery() > 0)
                      {
                          File.Move(filename, fPath + @"\processed" + filename.Substring(filename.LastIndexOf(@"\")));
                      }


                  }

                  mySqlCommand.Dispose();
                  if (mySqlConnection != null)
                  {
                      if (mySqlConnection.State == ConnectionState.Open)
                      {
                          mySqlConnection.Close();
                      }
                      mySqlConnection.Dispose();
                  }
              }
          }


          private static void Showusage()
          {
              Console.WriteLine("Usage: incode.exe [ /path:{\"path\"} /filetype:{extension} /max:{Max emails to process} ]");
              Console.WriteLine();
              Console.WriteLine(@"  /conn        SQL connection string");
              Console.WriteLine(@"  /table       Name of table to BULK INSERT to");
              Console.WriteLine(@"  /path        Full path of the folder to process (uses current working directory if not specified)");
              Console.WriteLine(@"  /filetype    Extension of files to process (default is txt)");
              Console.WriteLine(@"  /max         Maximum number of email files to process (default is 1, 0 for unlimited)");
              Console.WriteLine();
              Console.WriteLine(@"Example:   incode.exe /filetype:txt /max:20");
          }
      }
  }

...the batch file to run this application:

::

  F:\InsertCodes.exe /path:"F:\codes-split-into-chunks" /max:5 /table:codes /filetype:txt /conn:"Server=SQL.our-company;Database=codes;user ID=sa;password=my-password"