/// <summary>
        /// List<MappingStruct> LoadMappingXref(string mapping)
        /// If a mapping is specified, fill in the xref
        /// </summary>
        /// <param name="mapping"></param>
        /// <returns></returns>
        private static List<MappingStruct> LoadMappingXref(string mapping)
        {
            List<MappingStruct> _mappingXref = new List<MappingStruct>();
            string _mapping = mapping;

            try
            {
                if (_mapping != "")
                {
                    // first load the structure from the mapping info.  the mapping name will be in ()
                    int _index = _mapping.LastIndexOf('(');
                    if (_index >= 0)
                    {
                        _mapping = _mapping.Substring(_index + 1);
                        _mapping = _mapping.Replace('(', ' ');
                        _mapping = _mapping.Replace(')', ' ');
                        _mapping = _mapping.Trim();
                    }

                    // now load the mapping records
                    string _sql = "Select m.Mapping_Id, m.Name, mf.Sequence, In_Name, Out_Name " +
                                  "From Mappings m inner join Mapped_Fields mf " +
                                  "      on m.mapping_id = mf.mapping_id " +
                                  "Where m.Name = '" + _mapping + "' " +
                                  "order by sequence ";

                    List<string[]> _rows = Select(_sql);

                    if (_rows.Count > 0)
                    {
                        // fill in the mapping
                        for (int n=1;n<_rows.Count;n++)
                        {
                            string[] _row = _rows[n];
                            MappingStruct _xref = new MappingStruct();
                            _xref.mappingId = CommonRoutines.ConvertToInt(_row[0]);
                            _xref.mappingName = _row[1];
                            _xref.sequence = CommonRoutines.ConvertToInt(_row[2]);
                            _xref.inFieldIndex = -1;
                            _xref.inField = _row[3];
                            _xref.outFieldIndex = -1;
                            _xref.outField = _row[4];
                            _mappingXref.Add(_xref);
                        }
                    }

                }
            }
            catch (Exception ex)
            {
                CommonRoutines.DisplayErrorMessage("$E:" + moduleName + ".LoadMappingXref > " + ex.Message);
            }

            return _mappingXref;
        }
        /// <summary>
        /// int ImportFileIntoTable(string connectionString, string dbms, string tableName, 
        ///                            string fileName, bool firstRecordHasFieldNames, 
        ///                            string fieldDelimiter, string fieldSeparator,
        ///                            bool createNewTable, bool deleteAllRows,
        ///                            string mapping, ref string results,
        ///                            bool interactive = false)
        /// Import a file into a table in the database specified in connectionString
        /// with multiple parms and criteria                                      
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="tableName"></param>
        /// <param name="fileName"></param>
        /// <param name="firstRecordHasFieldNames"></param>
        /// <param name="fieldDelimiter"></param>
        /// <param name="fieldSeparator"></param>
        /// <param name="createNewTable"></param>
        /// <param name="truncateTable"></param>
        /// <returns></returns>
        public static int ImportFileIntoTable(string connectionString, string dbms, string tableName, 
                                                 string fileName, bool firstRecordHasFieldNames, 
                                                 string fieldDelimiter, string fieldSeparator,
                                                 bool createNewTable, bool deleteAllRows,
                                                 string mapping, ref string results,
                                                 bool interactive = false)
        {
            bool _firstRecord = true;
            bool _skipRecord = false;
            //bool _continueProcess = true;
            string _result = "";
            int _numRowsInserted = 0;
            int _fieldCount = 0;
            string _fieldDelimiter = fieldDelimiter;
            string _fieldSeparator = fieldSeparator;
            List<string> _tableFields = new List<string>();
            int _numSQLErrors = 0;

            lastConnection = connectionString;

            lastStatus = true;
            lastStatusCode = "0";
            lastStatusMessage = "Success";

            try
            {

                //get SQL Error Threshold
                int _sqlErrorThreshold = CommonRoutines.ConvertToInt(ConfigRoutines.GetSetting("SQLErrorThreshold"));

                // check the delimiter
                //LAP 20130424 - Convert to char, then to int to get ascii code of char
                char _charDelim = Convert.ToChar(_fieldDelimiter);
                int _charCode = Convert.ToInt32(_charDelim);
                if (_charCode < 32 || _charCode > 127)
                {
                    // set to default
                    _fieldDelimiter = ConfigRoutines.GetSetting("ImportDelimiter");
                    _charDelim = Convert.ToChar(_fieldDelimiter);
                }
                else
                {
                    _fieldDelimiter = ((char)_charCode).ToString();
                }

                // check the separator
                //LAP 20130424 - Convert to char, then to int to get ascii code of char
                char _charSep = Convert.ToChar(_fieldSeparator);
                _charCode = Convert.ToInt32(_charSep);
                if (_charCode < 32 || _charCode > 127)
                {
                    // set to default
                    _fieldSeparator = ConfigRoutines.GetSetting("ImportSeparator");
                    _charSep = Convert.ToChar(_fieldSeparator);
                }
                else
                {
                    _fieldSeparator = ((char)_charCode).ToString();
                }

                // load any mapping
                List<MappingStruct> _mappingXref = LoadMappingXref(mapping);

                if (_mappingXref.Count == 0)
                {
                    // get the table fields to fill in the map
                    _tableFields = GetTableFields(connectionString, dbms, tableName);
                    if (_tableFields.Count == 0)
                    {
                        // can't process the request
                        _result = "$E: No map specified and unable to get fields for table " + tableName;
                        lastStatus = false;
                        lastStatusCode = "-999";
                        results = _result;
                        CommonRoutines.HideProgress();
                        return -1;
                    }
                    else
                    {
                        // fill in the map xref
                        //SES 20130423 - filled in the fields with valid values for later...  only when no mapping is selected
                        for (int n = 0; n < _tableFields.Count; n++)
                        {
                            MappingStruct _mapXref = new MappingStruct();
                            _mapXref.mappingId = 0;
                            _mapXref.sequence = n + 1;
                            _mapXref.mappingName = "Generated";
                            _mapXref.inField = _tableFields[n];
                            _mapXref.inFieldIndex = n;
                            _mapXref.outField = _tableFields[n];
                            _mapXref.outFieldIndex = n;
                            _mappingXref.Add(_mapXref);
                        }
                    }
                }

                // now open the file
                // Check to see if the file exists
                if (System.IO.File.Exists(fileName))
                {
                    // Open the file and read the records.
                    try
                    {
                        // get the actual file name...
                        string _fileNameNoPath = fileName;
                        if (_fileNameNoPath.LastIndexOf('\\') > 0)
                        {
                            _fileNameNoPath = _fileNameNoPath.Substring(_fileNameNoPath.LastIndexOf('\\'));
                        }

                        //LAP 20130528 - check count of delimiter and separator characters in first data line
                        //               (to verify that the correct delimiter and separator were selected for this file)
                        int _lineNum = 0;
                        int _dataLineNum = (firstRecordHasFieldNames ? 1 : 0);
                        int delimCount = 0;
                        int sepCount = 0;

                        //foreach (string _line in System.IO.File.ReadLines(fileName))
                        //{
                        //    if (_lineNum == _dataLineNum)
                        //    {
                        //        char[] lineChars = _line.ToCharArray();

                        //        for (int n = 0; n < lineChars.Length; n++)
                        //        {
                        //            if (lineChars[n] == _charDelim)
                        //            {
                        //                delimCount++;
                        //            }
                        //            if (lineChars[n] == _charSep)
                        //            {
                        //                sepCount++;
                        //            }
                        //        }
                        //    }

                        //    if (_lineNum > _dataLineNum)
                        //    {
                        //        break;
                        //    }

                        //    ++_lineNum;

                        //}

                        //if (delimCount < _mappingXref.Count - 1)
                        //{
                        //    // specified delimiter was not found in the file
                        //    _result += "The specified delimiter ( " + fieldDelimiter + " ) was not found (for each field mapped) in the first data line of the file.  File processing halted.";
                        //    lastStatus = false;
                        //    lastStatusCode = "-999";
                        //    results = _result;
                        //    CommonRoutines.HideProgress();
                        //    return -1;
                        //}

                        //if (sepCount > 0 && sepCount < 2)
                        //{
                        //    // specified separator was not found in the file
                        //    _result += "The specified separator ( " + fieldSeparator + " ) was not found (at least twice) in the first data line of the file.  File processing halted.";
                        //    lastStatus = false;
                        //    lastStatusCode = "-999";
                        //    results = _result;
                        //    CommonRoutines.HideProgress();
                        //    return -1;
                        //}

                        int _minRows = 0;
                        //LAP 20130517 -- get number of lines from file
                        int _maxRows = System.IO.File.ReadLines(fileName).Count();
                        CSA.continueOperation = true;
                        string _status = "Importing file " + _fileNameNoPath + " into table " + tableName;

                        // open the progress bar
                        CommonRoutines.ShowProgress(0, 0, 0, _status);

                        // now create a list of output fields for later resolving
                        string[] _inputFieldNames = {};

                        using (System.IO.StreamReader _sr = System.IO.File.OpenText(fileName))
                        {
                            String _buffer = "";
                            int _char = 0;
                            string[] _fields = { };
                            string _fieldList = "";
                            bool _continue = true;
                            stopExecution = false;

                            bool _quoted = false;

                            // read the file one char at a time and look for a \r\n.  If withing a quote, ignore and continue
                            //}
                            //while ((_buffer = _sr.ReadLine()) != null)
                            //{

                            while (_continue && CSA.continueOperation)
                            {

                                //update progress every 100 rows
                                if (_numRowsInserted % 100 == 0)
                                {
                                    CommonRoutines.ShowProgress(_minRows, _maxRows, _numRowsInserted, _status + " ... Row " + _numRowsInserted + " of " + _maxRows);
                                    Application.DoEvents();
                                }

                                _buffer = "";

                                #region [Read Bytes]
                                _char = 0;
                                while ((_char = _sr.Read()) > 0)
                                {

                                    //LAP 20130426 - check for Separator char, instead of " and '
                                    if (_char == _charSep)
                                    {
                                        _quoted = !_quoted;
                                    }
                                    //LAP 20130426 - include quote chars in buffer
                                    //else
                                    //{
                                    if (_char == 10 && !_quoted)
                                    {
                                        // a new line, end of record
                                        break;
                                    }
                                    else
                                    {
                                        if (_char >= 32 && _char <= 127)
                                        {
                                            _buffer = _buffer + (char)_char;
                                        }
                                    }
                                    //}
                                }

                                // check for end of file
                                if (_char <= 0)
                                {
                                    // process the record then exit
                                    _continue = false;
                                }

                                if (_buffer.Length == 0)
                                {
                                    break;
                                }
                                #endregion [Read Bytes]

                                _fields = CommonRoutines.Split(_buffer, _fieldDelimiter, _fieldSeparator);

                                // make the delimiter in the field list a comma if different then the passed delimiter
                                string _sql = "";
                                int _startRecord = 0;

                                // is this the first record
                                if (_firstRecord)
                                {
                                    #region [First Record]

                                    _fieldCount = _fields.Length;
                                    _fieldList = "";

                                    if (firstRecordHasFieldNames)
                                    {
                                        _skipRecord = true;
                                        _fieldCount = 0;
                                        _inputFieldNames = _fields;
                                    }

                                    // create generic fields
                                    for (int n = 0; n < _fields.Length; n++)
                                    {
                                        if (firstRecordHasFieldNames)
                                        {
                                            if (_fields[n].Trim() != "")
                                            {
                                                // make sure there are no wierd chars or spaces
                                                _fields[n] = _fields[n].Replace(' ', '_');
                                                _fields[n] = _fields[n].Replace('/', '_');
                                                _fields[n] = _fields[n].Replace('-', '_');
                                                _fields[n] = _fields[n].Replace('\\', '_');

                                                if (mapping != "")
                                                {
                                                    // use the out fields to create the field list
                                                    // see if the field is one of the mapped input field
                                                    for (int m = 0; m < _mappingXref.Count; m++)
                                                    {
                                                        MappingStruct _mapStruct = _mappingXref[m];
                                                        //LAP 20130430 -- make the same replacements on the map field as done to in field above
                                                        string mapField = _mapStruct.inField.Replace(' ', '_');
                                                        mapField = mapField.Replace('/', '_');
                                                        mapField = mapField.Replace('-', '_');
                                                        mapField = mapField.Replace('\\', '_');
                                                        if (mapField.ToUpper() == _fields[n].ToUpper())
                                                        {
                                                            // set the index
                                                            _mapStruct.inFieldIndex = n;
                                                        }
                                                        else
                                                        {
                                                            if (mapField.ToUpper().Contains("[" + _fields[n].ToUpper() + "]"))
                                                            {
                                                                //set field name and index used in expression
                                                                _mapStruct.inExprFieldIndex = n;
                                                                _mapStruct.inExprField = _fields[n];
                                                            }
                                                        }
                                                        _mappingXref[m] = _mapStruct;
                                                    }
                                                }
                                                else
                                                {
                                                    _fieldList = _fieldList + _fields[n] + ",";
                                                }
                                            }
                                            else
                                            {
                                                _fieldList = _fieldList + "Field_" + n.ToString() + ",";
                                            }
                                        }
                                        else
                                        {
                                            // first row is not field names so just set the index of the mapping xref to positional
                                            if (n < _mappingXref.Count)
                                            {
                                                MappingStruct _mapStruct = _mappingXref[n];
                                                // set the index
                                                _mapStruct.inFieldIndex = n;
                                                _mappingXref[n] = _mapStruct;
                                            }
                                        }
                                    }

                                    // if a mapping was specified, then replace the fieldlist with the output fields
                                    if (_mappingXref.Count > 0)
                                    {
                                        _fieldList = "";
                                        for (int m = 0; m < _mappingXref.Count; m++)
                                        {
                                            // if a mapped field or an expression, include the output field in the field list
                                            if (_mappingXref[m].inFieldIndex >= 0 || _mappingXref[m].inField.IndexOf("'") >= 0 || _mappingXref[m].inField.IndexOf('<') >= 0 || _mappingXref[m].inField.IndexOf('[') >= 0)
                                            {
                                                _fieldList = _fieldList + _mappingXref[m].outField + ",";
                                            }
                                        }
                                    }
                                    else
                                    {
                                        _fieldList = _fieldList.Substring(0, _fieldList.Length - 1);
                                    }

                                    // check to ensure that the fieldlist does not end in a comma
                                    _fieldList = _fieldList.Trim();
                                    if (_fieldList.Substring(_fieldList.Length - 1) == ",")
                                    {
                                        _fieldList = _fieldList.Substring(0, _fieldList.Length - 1);
                                    }

                                    // if this table is to be a new one, check to see if it exists
                                    if (createNewTable)
                                    {
                                        _result += CreateTable(connectionString, dbms, tableName, _fieldList, _fields.Length) + ";";
                                    }
                                    else
                                    {
                                        if (deleteAllRows)
                                        {
                                            string _deleteResult = TruncateTable(connectionString, dbms, tableName);
                                            if (_deleteResult != "")
                                            {
                                                // an error occurred
                                                _result += "An error occurred while deleting existing records from table " + tableName + ";";
                                                _continue = false;
                                                lastStatusMessage = _result;
                                                lastStatusCode = "-999";
                                                lastStatus = true;
                                                break;
                                            }
                                            //LAP 20130426 - Moved tableRowsDeleted flag set to right after delete SQL is executed
                                            _result += "All existing records were deleted from table " + tableName + ";";
                                            tableRowsDeleted = true;
                                        }
                                    }

                                    #endregion [First Record]
                                }

                                _firstRecord = false;
                                int _rowsInserted = 0;

                                if (!_skipRecord)
                                {
                                    // how many fields in the fieldlist?
                                    string[] _fieldNames = _fieldList.Split(',');
                                    _fieldCount = _fieldNames.Length;

                                    // insert this record
                                    _sql = "Insert Into " + tableName + " ";
                                    _sql = _sql + "(" + _fieldList + ") ";
                                    _sql = _sql + "Values(";

                                    string _values = "";
                                    string _value = "";

                                    // if a map specified, then create the values from the map
                                    if (_mappingXref.Count > 0 && firstRecordHasFieldNames)
                                    {
                                        // by column name
                                        int _inIndex = 0;
                                        for (int m = 0; m < _mappingXref.Count; m++)
                                        {
                                            //LAP 20130501 - determine if column should be excluded from insert SQL due to missing value or mapping
                                            //bool removeCol = (m >= _fields.Count());
                                            //if (!removeCol)
                                            //{
                                            //    _inIndex = _mappingXref[m].inFieldIndex;

                                            //    if (_inIndex >= 0 && _inIndex >= _fields.Count())
                                            //    {
                                            //        removeCol = true;
                                            //    }
                                            //}

                                            _inIndex = _mappingXref[m].inFieldIndex;

                                            //if (removeCol)
                                            //{
                                            //    //remove columns for missing field values from field list
                                            //    List<string> _newFields = _fieldList.Split(',').ToList();
                                            //    _newFields.RemoveRange(m, (_newFields.Count - m == 0 ? 1 : _newFields.Count - m));
                                            //    string _tempFieldList = String.Join(",", _newFields);

                                            //    // recreate first part of SQL
                                            //    _sql = "Insert Into " + tableName + " ";
                                            //    _sql = _sql + "(" + _tempFieldList + ") ";
                                            //    _sql = _sql + "Values(";

                                            //    break;
                                            //}

                                            if (_inIndex >= 0)
                                            {
                                                // grab the field from the input fields
                                                _value = _fields[_inIndex];
                                                //LAP 20130426 - check for any single quotes and make them two single quotes
                                                _value = _value.Replace("'", "''");
                                                _values = _values + "'" + _value + "',";
                                            }
                                            else
                                            {
                                                //if the field index wasn't found above, assume it's an expression
                                                if (_mappingXref[m].inField.IndexOf('<') == 0 || _mappingXref[m].inField.IndexOf("'") >= 0 || CommonRoutines.IsNumeric(_mappingXref[m].inField) || _mappingXref[m].inField.IndexOf("[") >= 0)
                                                {
                                                    // expression, number or litreal
                                                    _value = _mappingXref[m].inField;

                                                    _value = CommonRoutines.ResolveValue(_value, _inputFieldNames, _fields);
                                                    //_value = Widgets.ResolveSymbolics(_value);

                                                    _value = _value.Replace("[" + _mappingXref[m].inExprField.ToUpper() + "]", "'" + _fields[_mappingXref[m].inExprFieldIndex] + "'");

                                                    //// strip the < and >
                                                    //_inIndex = _value.IndexOf('<');
                                                    //_value = _value.Substring(_inIndex);
                                                    //_inIndex = _value.LastIndexOf('>');
                                                    //if (_inIndex > 1)
                                                    //{
                                                    //    _value = _value.Substring(0, _inIndex - 1);
                                                    //}
                                                    // add it to the values list
                                                    // is it a literal? (enclosed in quotes)
                                                    // now if it is not numeric, enclose it in quotes
                                                    _value = _value.Trim();
                                                    if (!CommonRoutines.IsNumeric(_value))
                                                    {
                                                        _value = "'" + _value + "'";
                                                    }
                                                    _values = _values + _value + ",";
                                                }
                                            }
                                        }
                                        _sql = _sql + _values;
                                    }
                                    else
                                    {
                                        // positional...
                                        for (int n = 0; n < _fieldCount; n++)
                                        {
                                            if (n < _fields.Length)
                                            {
                                                // check for any single quotes and make them two single quotes
                                                _fields[n] = _fields[n].Replace("'", "''");
                                                _fields[n] = _fields[n].Replace(fieldSeparator, "");
                                                // remove any of the separator values
                                                _sql = _sql + "'" + _fields[n].Trim() + "',";
                                            }
                                            else
                                            {
                                                _sql = _sql + "'',";
                                            }
                                        }
                                    }

                                    // get rid of last ,
                                    int _index = _sql.LastIndexOf(',');
                                    _sql = _sql.Substring(0, _index);
                                    _sql = _sql + "); ";
                                    // execute it
                                    _rowsInserted = Execute(connectionString, dbms, _sql);
                                    if (_rowsInserted < 0)
                                    {
                                        // an error occurred
                                        if (interactive)
                                        {
                                            if (!CommonRoutines.Ask("Would you like to continue importing this file?"))
                                            {
                                                _result += "One or more errors occurred while inserting records" + ";";
                                                _continue = false;
                                                break;
                                            }
                                        }
                                        else
                                        {
                                            ++_numSQLErrors;
                                            if (_numSQLErrors >= _sqlErrorThreshold)
                                            {
                                                _result += "The SQL Error Threshold (" + _sqlErrorThreshold.ToString() + ") was reached while inserting records" + ";";
                                                results = _result;
                                                lastStatus = false;
                                                lastStatusCode = "-999";
                                                CommonRoutines.HideProgress();
                                                return -1;
                                            }
                                        }
                                    }
                                    else
                                    {
                                        _numRowsInserted = _numRowsInserted + _rowsInserted;
                                    }

                                }
                                _skipRecord = false;

                                if (stopExecution)

                                {
                                    _result += "Processing was stopped by user" + ";";
                                    _continue = false;
                                }

                            }
                        }
                    }
                    catch (Exception exio)
                    {
                        _result = "An error occurred in reading the input file;";
                        lastStatus = false;
                        lastStatusCode = "-999";
                        lastStatusMessage = exio.Message;
                    }
                }
                else  //if file not found
                {
                    //LAP 20130426 - If the file was not found, set message and return
                    _result += "File " + fileName + " could not be found";
                    lastStatus = false;
                    lastStatusCode = "-999";
                    results = _result;
                    CommonRoutines.HideProgress();
                    return -1;
                }

                if (lastStatus)
                {
                    // make up the result to return
                    _result += "Number of rows inserted into table " + tableName + " was " + _numRowsInserted;
                    lastStatusMessage = "Success";
                    lastStatusCode = "0";
                    lastStatus = true;
                }
                else
                {
                    if (_result == String.Empty)
                    {
                        _result = "An unidentified error has occurred in the import process";
                    }
                }
            }
            catch (Exception ex)
            {
                CommonRoutines.HideProgress();
                _result += "An error occurred in the import process";
                lastStatus = false;
                lastStatusCode = "-999";
                lastStatusMessage = ex.Message;
                CommonRoutines.DisplayErrorMessage("$E:" + moduleName + ".ImportFileIntoTable(s,s,s,b,s,s,b,b) > " + ex.Message);
            }

            CommonRoutines.HideProgress();

            results = _result;

            return _numRowsInserted;
        }