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