public bool ImportFile(string sFile, DTDataDestination dest, bool bSilent) { bool bOK = false; System.Data.DataTable tblSrc = null; System.IO.FileStream stream = null; IFormatter formatter = new BinaryFormatter(); try { stream = new System.IO.FileStream(sFile, System.IO.FileMode.Open, System.IO.FileAccess.Read); tblSrc = (System.Data.DataTable)formatter.Deserialize(stream); bOK = true; } catch (Exception er) { _error = ExceptionLimnorDatabase.FormExceptionText(er, "Error importing file [{0}].", sFile); TraceLogClass.TraceLog.ShowMessageBox = !bSilent; TraceLogClass.TraceLog.Log(er); } finally { if (stream != null) { stream.Close(); } } if (bOK) { _error = dest.ReceiveData(tblSrc, bSilent); } return(bOK); }
public string ReceiveData(DataTable tblSrc, bool bSilent) { string error = string.Empty; try { if (TableName.Length > 0 && _fields != null && connect != null && tblSrc != null) { if (_fields.Count > 0 && tblSrc.Columns.Count >= _fields.Count) { if (tblSrc.Rows.Count > 0) { bool bNeedUpdate = false; bool bNeedUpdateRun = false; int i; for (i = 0; i < _fields.Count; i++) { if (_fields[i].Indexed) { bNeedUpdate = true; break; } } EnumParameterStyle pStyle = connect.ConnectionObject.ParameterStyle; string n1 = connect.ConnectionObject.NameDelimiterBegin; string n2 = connect.ConnectionObject.NameDelimiterEnd; string sInsert = StringUtility.FormatInvString( "INSERT INTO {0}{1}{2} ({0}{3}{2}", n1, TableName, n2, _fields[0].Name); string sValues = _fields[0].GetParameterName(pStyle); string sUpdate = StringUtility.FormatInvString( "UPDATE {0}{1}{2} SET ", n1, TableName, n2); string sExist; if (connect.ConnectionObject.IsJet || connect.ConnectionObject.IsMSSQL) { sExist = StringUtility.FormatInvString( "SELECT TOP 1 1 FROM {0}{1}{2} WHERE ", n1, TableName, n2); } else { sExist = StringUtility.FormatInvString( "SELECT 1 FROM {0}{1}{2} WHERE ", n1, TableName, n2); } string sWhere = ""; int k = 0, n = 0; if (bNeedUpdate) { if (_fields[0].Indexed) { sWhere = StringUtility.FormatInvString( "{0}{1}{2}={3}", n1, _fields[0].Name, n2, _fields[0].GetParameterName(pStyle)); } else { sUpdate += StringUtility.FormatInvString("{0}{1}{2}={3}", n1, _fields[0].Name, n2, _fields[0].GetParameterName(pStyle)); k++; } for (i = 0; i < _fields.Count; i++) { if (_fields[i].Indexed) { if (n == 0) { sExist += StringUtility.FormatInvString("{0}{1}{2}={3}", n1, _fields[i].Name, n2, _fields[i].GetParameterName(pStyle)); } else { sExist += StringUtility.FormatInvString(" AND {0}{1}{2}={3}", n1, _fields[i].Name, n2, _fields[i].GetParameterName(pStyle)); } n++; } } } for (i = 1; i < _fields.Count; i++) { sInsert += StringUtility.FormatInvString(",{0}{1}{2}", n1, _fields[i].Name, n2); sValues += "," + _fields[i].GetParameterName(pStyle); if (bNeedUpdate) { if (_fields[i].Indexed) { if (sWhere.Length == 0) { sWhere = StringUtility.FormatInvString("{0}{1}{2}={3}", n1, _fields[i].Name, n2, _fields[i].GetParameterName(pStyle)); } else { sWhere += StringUtility.FormatInvString(" AND {0}{1}{2}={3}", n1, _fields[i].Name, n2, _fields[i].GetParameterName(pStyle)); } } else { if (k == 0) { sUpdate += StringUtility.FormatInvString("{0}{1}{2}={3}", n1, _fields[i].Name, n2, _fields[i].GetParameterName(pStyle)); } else { sUpdate += StringUtility.FormatInvString(",{0}{1}{2}={3}", n1, _fields[i].Name, n2, _fields[i].GetParameterName(pStyle)); } k++; } } } if (bNeedUpdate && k > 0) { bNeedUpdateRun = true; } sInsert += ") VALUES ("; sInsert += sValues; sInsert += ")"; // connect.ConnectionObject.Open(); // if (connect.ConnectionObject.IsMSSQL) { dbWrapper dbHelp = new dbWrapper(); dbHelp.CreateCommand(connect.ConnectionObject); string sHelp = string.Format(CultureInfo.InvariantCulture, "SET IDENTITY_INSERT {0} ON", TableName); dbHelp.SetCommandText(sHelp); dbHelp.ExecuteNonQuery(); } // dbWrapper dbUpdate = null; dbWrapper dbExists = null; dbWrapper dbInsert = new dbWrapper(); dbInsert.CreateCommand(connect.ConnectionObject); dbInsert.SetCommandText(sInsert); // object vExist; bool bInsert; // for (i = 0; i < _fields.Count; i++) { dbInsert.AddCommandParameter(_fields[i], pStyle); } // if (bNeedUpdate) { if (bNeedUpdateRun) { dbUpdate = new dbWrapper(); dbUpdate.CreateCommand(connect.ConnectionObject); dbUpdate.SetCommandText(StringUtility.FormatInvString("{0} WHERE {1}", sUpdate, sWhere)); } dbExists = new dbWrapper(); dbExists.CreateCommand(connect.ConnectionObject); dbExists.SetCommandText(sExist); for (i = 0; i < _fields.Count; i++) { if (_fields[i].Indexed) { dbExists.AddCommandParameter(_fields[i], pStyle); } else { if (bNeedUpdateRun) { dbUpdate.AddCommandParameter(_fields[i], pStyle); } } } if (bNeedUpdateRun) { for (i = 0; i < _fields.Count; i++) { if (_fields[i].Indexed) { dbUpdate.AddCommandParameter(_fields[i], pStyle); } } } } for (int m = 0; m < tblSrc.Rows.Count; m++) { bInsert = true; if (bNeedUpdate) { k = 0; for (i = 0; i < _fields.Count; i++) { if (_fields[i].Indexed) { dbExists.SetParameterValue(k, tblSrc.Rows[m][i]); k++; } } vExist = dbExists.ExecuteScalar(); if (vExist != null && vExist != System.DBNull.Value) { bInsert = false; } if (!bInsert) { if (bNeedUpdateRun) { k = 0; for (i = 0; i < _fields.Count; i++) { if (!_fields[i].Indexed) { if (dbUpdate.Parameters[k].DbType == System.Data.DbType.Binary) { object v = tblSrc.Rows[m][i]; if (v == null || v == System.DBNull.Value) { dbUpdate.Parameters[k].Value = System.DBNull.Value; } else { string s = v.ToString(); byte[] bs = StringUtility.StringToBytes(s); dbUpdate.Parameters[k].Value = bs; } } else { dbUpdate.Parameters[k].Value = tblSrc.Rows[m][i]; } k++; } } for (i = 0; i < _fields.Count; i++) { if (_fields[i].Indexed) { if (dbUpdate.Parameters[k].DbType == System.Data.DbType.Binary) { object v = tblSrc.Rows[m][i]; if (v == null || v == System.DBNull.Value) { dbUpdate.Parameters[k].Value = System.DBNull.Value; } else { string s = v.ToString(); byte[] bs = StringUtility.StringToBytes(s); dbUpdate.Parameters[k].Value = bs; } } else { dbUpdate.Parameters[k].Value = tblSrc.Rows[m][i]; } k++; } } dbUpdate.ExecuteNonQuery(); } } } if (bInsert) { for (i = 0; i < _fields.Count; i++) { object v = tblSrc.Rows[m][i]; if (v == null || v == System.DBNull.Value) { dbInsert.Parameters[i].Value = System.DBNull.Value; } else { if (dbInsert.Parameters[i].DbType == System.Data.DbType.Binary) { string s = v.ToString(); byte[] bs = StringUtility.StringToBytes(s); dbInsert.Parameters[i].Value = bs; } else { bool b; object v2 = VPLUtil.ConvertObject(v, EPField.ToSystemType(dbInsert.Parameters[i].DbType), out b); dbInsert.Parameters[i].Value = v2; } } } dbInsert.ExecuteNonQuery(); } } if (connect.ConnectionObject.IsMSSQL) { dbWrapper dbHelp = new dbWrapper(); dbHelp.CreateCommand(connect.ConnectionObject); string sHelp = string.Format(CultureInfo.InvariantCulture, "SET IDENTITY_INSERT {0} OFF", TableName); dbHelp.SetCommandText(sHelp); dbHelp.ExecuteNonQuery(); } connect.ConnectionObject.Close(); // } } } } catch (Exception er) { error = ExceptionLimnorDatabase.FormExceptionText(er, "Error saving data to database"); TraceLogClass.TraceLog.ShowMessageBox = !bSilent; TraceLogClass.TraceLog.Log(er); } finally { connect.ConnectionObject.Close(); } return(error); }
/// <summary> /// 1. download files to workFolder. /// 2. call ImportFiles to process all files in workFolder. /// 3. move processed files to workFolder\backup. /// </summary> /// <param name="name"></param> /// <param name="dest"></param> /// <param name="bSilent"></param> /// <returns></returns> public bool ReceiveFile(string name, DTDataDestination dest, bool bSilent) { bool bOK = false; if (ParametersOK(true)) { int i; _error = null; if (TransferMethod == enumDTMethod.LAN) { string[] sFiles = System.IO.Directory.GetFiles(LanFolder, string.Format(CultureInfo.InvariantCulture, "{0}_*.dt", name)); if (sFiles != null) { bOK = true; for (i = 0; i < sFiles.Length; i++) { string tgt = Path.Combine(WorkFolder, System.IO.Path.GetFileName(sFiles[i])); try { System.IO.File.Move(sFiles[i], tgt); } catch (Exception er) { _error = ExceptionLimnorDatabase.FormExceptionText(er, "ReceiveFile. Error moving file [{0}] to [{1}].", sFiles[i], tgt); TraceLogClass.TraceLog.ShowMessageBox = !bSilent; TraceLogClass.TraceLog.Log(er); bOK = false; break; } } } } else if (TransferMethod == enumDTMethod.FTP) { FtpClient objFTP = Ftp.CreateFtpClient(); if (objFTP.GetContents(Ftp.Folder)) { FtpFileInfo[] sFiles = objFTP.FileList; if (sFiles != null) { for (i = 0; i < sFiles.Length; i++) { if (IsDataFile(sFiles[i].Filename, name)) { if (objFTP.Download(WorkFolder, sFiles[i].Filename)) { objFTP.DeleteFile(sFiles[i].Filename); } else { _error = string.Format(CultureInfo.InvariantCulture, "Cannot download file [{0}] from the FTP server [{1}]. {2}", sFiles[i].Filename, Ftp.Host, objFTP.ErrorMessage); break; } } } } } else { _error = string.Format(CultureInfo.InvariantCulture, "Cannot get file list from the FTP server [{0}]. {1} ", Ftp.Host, objFTP.ErrorMessage); } bOK = string.IsNullOrEmpty(_error); if (!bOK) { TraceLogClass.TraceLog.ShowMessageBox = !bSilent; TraceLogClass.TraceLog.Log(new Exception(_error)); } } } bool bImport = ImportFiles(name, dest, bSilent); if (bOK) { bOK = bImport; } return(bOK); }
public bool ImportFiles(string name, DTDataDestination dest, bool bSilent) { bool bOK = true; string sBackupDir = Path.Combine(WorkFolder, "Backup"); if (!System.IO.Directory.Exists(sBackupDir)) { try { System.IO.Directory.CreateDirectory(sBackupDir); } catch (Exception er) { _error = ExceptionLimnorDatabase.FormExceptionText(er, "Error creating backup folder [{0}].", sBackupDir); TraceLogClass.TraceLog.ShowMessageBox = !bSilent; TraceLogClass.TraceLog.Log(er); return(false); } } string[] sFiles = System.IO.Directory.GetFiles(WorkFolder, string.Format(CultureInfo.InvariantCulture, "{0}_*.dt", name)); if (sFiles != null) { //sort file names string s; int i; for (i = 1; i < sFiles.Length; i++) { if (sFiles[i].CompareTo(sFiles[i - 1]) > 0) { for (int k = i; k > 0; k--) { if (sFiles[k].CompareTo(sFiles[k - 1]) > 0) { s = sFiles[k]; sFiles[k] = sFiles[k - 1]; sFiles[k - 1] = s; } else { break; } } } } for (i = 0; i < sFiles.Length; i++) { bOK = ImportFile(sFiles[i], dest, bSilent); if (bOK) { string tgt = Path.Combine(sBackupDir, System.IO.Path.GetFileName(sFiles[i])); try { System.IO.File.Move(sFiles[i], tgt); } catch (Exception er) { _error = ExceptionLimnorDatabase.FormExceptionText(er, "Error moving file [{0}] to [{1}].", sFiles[i], tgt); TraceLogClass.TraceLog.ShowMessageBox = !bSilent; TraceLogClass.TraceLog.Log(er); bOK = false; break; } } else { break; } } } return(bOK); }
public bool SendFile(System.Data.DataTable tblSrc, string name, bool bSilent) { if (ParametersOK(true)) { bool bOK = false; string sFile = makeFilename(name); string wkFile = Path.Combine(WorkFolder, sFile); System.IO.FileStream stream = null; IFormatter formatter = new BinaryFormatter(); try { stream = new System.IO.FileStream(wkFile, System.IO.FileMode.Create, System.IO.FileAccess.Write); formatter.Serialize(stream, tblSrc); bOK = true; } catch (Exception er) { _error = ExceptionLimnorDatabase.FormExceptionText(er, "Error creating data to [{0}]", wkFile); TraceLogClass.TraceLog.ShowMessageBox = !bSilent; TraceLogClass.TraceLog.Log(er); } finally { if (stream != null) { stream.Close(); } } if (bOK) { if (TransferMethod == enumDTMethod.LAN) { string sLanFile = Path.Combine(LanFolder, sFile); if (string.Compare(wkFile, sLanFile, StringComparison.OrdinalIgnoreCase) != 0) { try { System.IO.File.Copy(wkFile, sLanFile, true); } catch (Exception er) { _error = ExceptionLimnorDatabase.FormExceptionText(er, "Error sending [{0}] to [{1}]", wkFile, sLanFile); TraceLogClass.TraceLog.ShowMessageBox = !bSilent; TraceLogClass.TraceLog.Log(er); bOK = false; } } } else if (TransferMethod == enumDTMethod.FTP) { string sErr; bOK = SendByFTP(wkFile, out sErr); if (!bOK) { _error = string.Format(CultureInfo.InvariantCulture, "Error sending [{0}] via FTP. [{1}]", wkFile, sErr); TraceLogClass.TraceLog.ShowMessageBox = !bSilent; TraceLogClass.TraceLog.Log(new Exception(sErr)); } } } return(bOK); } return(false); }
public string ReceiveData(DataTable tblSrc, bool bSilent) { string error = null; System.IO.StreamWriter sw = null; if (tblSrc == null) { return("Source data is null"); } if (tblSrc.Columns.Count == 0) { return("Source data is empty"); } try { string sf = _links.GetValue(FILENAME) as string; bool bWriteHeader = HasHeader; if (bWriteHeader) { if (System.IO.File.Exists(sf)) { System.IO.FileInfo fi = new System.IO.FileInfo(sf); if (fi.Length > 0) { bWriteHeader = false; } } } System.Text.StringBuilder sb; // sw = new System.IO.StreamWriter(sf, _bAppend, EncodeUtility.GetEncoding(EncodingType), BufferSize); if (bWriteHeader) { sb = new System.Text.StringBuilder(); sb.Append(DataTransferConvert.Encode(_delimiter, tblSrc.Columns[0].ColumnName)); for (int i = 1; i < tblSrc.Columns.Count; i++) { sb.Append(DataTransferConvert.Delimiter(_delimiter)); sb.Append(DataTransferConvert.Encode(_delimiter, tblSrc.Columns[i].ColumnName)); } sw.WriteLine(sb.ToString()); } for (int r = 0; r < tblSrc.Rows.Count; r++) { sb = new System.Text.StringBuilder(); sb.Append(DataTransferConvert.Encode(_delimiter, StringUtility.ToString(tblSrc.Rows[r][0]))); for (int i = 1; i < tblSrc.Columns.Count; i++) { sb.Append(DataTransferConvert.Delimiter(_delimiter)); sb.Append(DataTransferConvert.Encode(_delimiter, StringUtility.ToString(tblSrc.Rows[r][i]))); } sw.WriteLine(sb.ToString()); } } catch (Exception er) { error = ExceptionLimnorDatabase.FormExceptionText(er, "Error saving data to file {0}", Filename); TraceLogClass.TraceLog.ShowMessageBox = !bSilent; TraceLogClass.TraceLog.Log(er); } finally { if (sw != null) { sw.Close(); } } return(error); }