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"