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