/// <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;
}