public static bool BulktoDB(DataTable elTable, BulkCopyOptions Options)
{
using (Options.Connection)
using (var bulkCopy = new SqlBulkCopy(Options.ConnectionString, SqlBulkCopyOptions.UseInternalTransaction))
{
for (var i = 1;; i++)
{
try
{
Options.Connection.Open();
}
catch
{
Thread.Sleep(Settings.Default.SleepTime);
}
if (Options.Connection.State == ConnectionState.Open)
{
if (Settings.Default.WriteToConsole)
{
Console.WriteLine(" DB connection established");
}
break;
}
}
var sigID = "";
if (elTable.Rows.Count > 0)
{
var row = elTable.Rows[0];
sigID = row[0].ToString();
}
if (Options.Connection.State == ConnectionState.Open)
{
bulkCopy.BulkCopyTimeout = Settings.Default.BulkCopyTimeout;
bulkCopy.BatchSize = Settings.Default.BulkCopyBatchSize;
if (Settings.Default.WriteToConsole)
{
bulkCopy.SqlRowsCopied +=
OnSqlRowsCopied;
bulkCopy.NotifyAfter = Settings.Default.BulkCopyBatchSize;
}
var tablename = Settings.Default.EventLogTableName;
bulkCopy.DestinationTableName = tablename;
if (elTable.Rows.Count > 0)
{
try
{
bulkCopy.WriteToServer(elTable);
if (Settings.Default.WriteToConsole)
{
Console.WriteLine(" !!!!!!!!!!!!!!!!!!!!!!!! ");
Console.WriteLine(" The bulk insert executed ");
Console.WriteLine(" !!For Signal " + sigID + " !!!!!! ");
Console.WriteLine(" !!!!!!!!!!!!!!!!!!!!!!!! ");
Console.WriteLine(" !!!!!!!!!!!!!!!!!!!!!!!! ");
}
Options.Connection.Close();
return(true);
}
catch (SqlException ex)
{
if (ex.Number == 2601)
{
if (Settings.Default.WriteToConsole)
{
Console.WriteLine("****There is a permission error!*****");
}
}
else
{
if (Settings.Default.WriteToConsole)
{
Console.WriteLine(ex);
}
}
Options.Connection.Close();
return(false);
}
}
public static bool DecodeASC3File(string FileName, string signalId, BulkCopyOptions Options)
{
var encoding = Encoding.ASCII;
try
{
using (var BR = new BinaryReader(File.Open(FileName, FileMode.Open), encoding))
{
var elTable = new DataTable();
var custUnique =
new UniqueConstraint(new[]
{
elTable.Columns["SignalID"],
elTable.Columns["Timestamp"],
elTable.Columns["EventCode"],
elTable.Columns["EventParam"]
});
elTable.Constraints.Add(custUnique);
if (BR.BaseStream.Position + 21 < BR.BaseStream.Length)
{
//Find the start Date
var dateString = "";
for (var i = 1; i < 21; i++)
{
var c = BR.ReadChar();
dateString += c;
}
//Console.WriteLine(dateString);
var StartTime = new DateTime();
if (DateTime.TryParse(dateString, out StartTime) &&
BR.BaseStream.Position < BR.BaseStream.Length)
{
//find line feed characters, that should take us to the end of the header.
// First line break is after Version
// Second LF is after FileName
// Third LF is after Interseciton number, which isn't used as far as I can tell
// Fourth LF is after IP address
// Fifth is after MAC Address
// Sixth is after "Controller data log beginning:," and then the date
// Seven is after "Phases in use:," and then the list of phases, seperated by commas
var i = 0;
while (i < 7 && BR.BaseStream.Position < BR.BaseStream.Length)
{
var c = BR.ReadChar();
//Console.WriteLine(c.ToString());
if (c == '\n')
{
i++;
}
}
//The first record alwasy seems to be missing a timestamp. Econolite assumes the first even occures at the same time
// the second event occurs. I am going to tag the first event with secondevet.timestamp - 1/10 second
var firstEventCode = new int();
var firstEventParam = new int();
if (BR.BaseStream.Position + sizeof(char) < BR.BaseStream.Length)
{
firstEventCode = Convert.ToInt32(BR.ReadChar());
}
if (BR.BaseStream.Position + sizeof(char) < BR.BaseStream.Length)
{
firstEventParam = Convert.ToInt32(BR.ReadChar());
}
var firstEventEntered = false;
//MOE.Common.Business.ControllerEvent firstEvent = new MOE.Common.Business.ControllerEvent(SignalID, StartTime, firstEventCode, firstEventParam);
//After that, we can probably start reading
while (BR.BaseStream.Position + sizeof(byte) * 4 <= BR.BaseStream.Length
) //we need ot make sure we are more that 4 characters from the end
{
var EventTime = new DateTime();
var EventCode = new int();
var EventParam = new int();
//MOE.Common.Business.ControllerEvent controllerEvent = null;
for (var EventPart = 1; EventPart < 4; EventPart++)
{
//getting the time offset
if (EventPart == 1)
{
var rawoffset = new byte[2];
//char[] offset = new char[2];
rawoffset = BR.ReadBytes(2);
Array.Reverse(rawoffset);
int offset = BitConverter.ToInt16(rawoffset, 0);
var tenths = Convert.ToDouble(offset) / 10;
EventTime = StartTime.AddSeconds(tenths);
}
//getting the EventCode
if (EventPart == 2)
{
EventCode = Convert.ToInt32(BR.ReadByte());
}
if (EventPart == 3)
{
EventParam = Convert.ToInt32(BR.ReadByte());
}
}
//controllerEvent = new MOE.Common.Business.ControllerEvent(SignalID, EventTime, EventCode, EventParam);
if (EventTime <= DateTime.Now && EventTime > Settings.Default.EarliestAcceptableDate)
{
if (!firstEventEntered)
{
try
{
elTable.Rows.Add(signalId, EventTime.AddMilliseconds(-1), firstEventCode,
firstEventParam);
}
catch
{
}
try
{
elTable.Rows.Add(signalId, EventTime, EventCode, EventParam);
}
catch
{
}
firstEventEntered = true;
}
else
{
try
{
elTable.Rows.Add(signalId, EventTime, EventCode, EventParam);
}
catch
{
}
}
}
}
}
//this is what we do when the datestring doesn't parse
else
{
return(TestNameAndLength(FileName));
}
}
else
{
return(TestNameAndLength(FileName));
}
if (BulktoDB(elTable, Options))
{
return(true);
}
return(SplitBulkToDB(elTable, Options));
}
}
catch
{
return(false);
}
}
public static bool SplitBulkToDb(DataTable elTable, BulkCopyOptions options, string tableName)
{
if (elTable.Rows.Count > 0)
{
var topDt = new DataTable();
var bottomDt = new DataTable();
var top = Convert.ToInt32(elTable.Rows.Count * .1);
var bottom = elTable.Rows.Count - top;
var dtTop = new DataTable();
try
{
dtTop = elTable.Rows.Cast <DataRow>().Take(elTable.Rows.Count / 2).CopyToDataTable();
}
catch (Exception ex)
{
return(false);
}
if (dtTop.Rows.Count > 0)
{
topDt.Merge(dtTop);
if (dtTop.Rows.Count > 0)
{
if (BulktoDb(topDt, options, tableName))
{
}
else
{
var elTable2 = new DataTable();
elTable2.Merge(topDt.Copy());
LineByLineWriteToDb(elTable2);
}
}
topDt.Clear();
topDt.Dispose();
dtTop.Clear();
dtTop.Dispose();
}
var dtBottom = new DataTable();
try
{
dtBottom = elTable.Rows.Cast <DataRow>().Take(elTable.Rows.Count / 2).CopyToDataTable();
}
catch (Exception ex)
{
return(false);
}
if (dtBottom.Rows.Count > 0)
{
bottomDt.Merge(dtBottom);
if (bottomDt.Rows.Count > 0)
{
if (BulktoDb(bottomDt, options, tableName))
{
}
else
{
var elTable2 = new DataTable();
elTable2.Merge(bottomDt.Copy());
LineByLineWriteToDb(elTable2);
}
}
bottomDt.Clear();
bottomDt.Dispose();
dtBottom.Clear();
dtBottom.Dispose();
}
return(true);
}
return(false);
}
public static bool GetCurrentRecords(string Server, string SignalId, string User, string Password,
string LocalDir, string RemoteDir, bool DeleteFilesAfterFTP, int SNMPRetry, int SNMPTimeout, int SNMPPort,
bool ImportAfterFTP, bool activemode, int waitbetweenrecords, BulkCopyOptions Options,
int FTPTimeout) //, CancellationToken Token)
{
var recordsComplete = false;
var errorRepository = ApplicationEventRepositoryFactory.Create();
//Initialize the FTP object
var RetryFiles = new List <string>();
var FTP = new FTPSClient();
var Cred = new NetworkCredential(User, Password);
var SSLMode = ESSLSupportMode.ClearText;
var DM = EDataConnectionMode.Passive;
if (activemode)
{
DM = EDataConnectionMode.Active;
}
var connected = false;
var FilePattern = ".dat";
try
{
try
{
var tokenSource = new CancellationTokenSource(TimeSpan.FromSeconds(30));
var token2 = tokenSource.Token;
Task task = Task.Factory.StartNew(
() => FTP.Connect(Server, 21, Cred, SSLMode, null, null, 0, 0, 0, FTPTimeout, true, DM)
, token2);
task.Wait(token2);
if (token2.IsCancellationRequested)
{
token2.ThrowIfCancellationRequested();
}
}
catch (AggregateException)
{
errorRepository.QuickAdd("FTPFromAllcontrollers", "Signal", "ConnectToController",
ApplicationEvent.SeverityLevels.Medium, "The connection task timed out for signal " + Server);
}
{
Console.WriteLine("Connection Failure");
}
connected = true;
}
//If there is an error, Print the error and go on to the next file.
catch (FTPException ex)
{
errorRepository.QuickAdd("FTPFromAllcontrollers", "Signal", "ConnectToController",
ApplicationEvent.SeverityLevels.Medium, Server + " " + ex.Message);
Console.WriteLine(ex.Message);
}
catch (AggregateException)
{
Console.WriteLine("Connection Failure");
}
catch (SocketException ex)
{
errorRepository.QuickAdd("FTPFromAllcontrollers", "Signal", "ConnectToController",
ApplicationEvent.SeverityLevels.Medium, Server + " " + ex.Message);
Console.WriteLine(ex.Message);
}
catch (IOException ex)
{
errorRepository.QuickAdd("FTPFromAllcontrollers", "Signal", "ConnectToController",
ApplicationEvent.SeverityLevels.Medium, Server + " " + ex.Message);
Console.WriteLine(ex.Message);
}
catch (Exception ex)
{
errorRepository.QuickAdd("FTPFromAllcontrollers", "Signal", "ConnectToController",
ApplicationEvent.SeverityLevels.Medium, Server + " " + ex.Message);
Console.WriteLine(ex.Message);
}
if (connected)
{
try
{
//if (Token.IsCancellationRequested)
//{
// Token.ThrowIfCancellationRequested();
//}
FTP.SetCurrentDirectory("..");
FTP.SetCurrentDirectory(RemoteDir);
}
catch (AggregateException)
{
Console.WriteLine("Connection Failure for " + Server);
}
catch (Exception ex)
{
errorRepository.QuickAdd("FTPFromAllcontrollers", "Signal", "GetCurrentRecords",
ApplicationEvent.SeverityLevels.Medium, Server + " " + ex.Message);
Console.WriteLine(ex.Message);
}
try
{
IList <DirectoryListItem> RemoteFiles = null;
var tokenSource = new CancellationTokenSource(TimeSpan.FromSeconds(30));
var token = tokenSource.Token;
Task task = Task.Factory.StartNew(() => RemoteFiles = FTP.GetDirectoryList(RemoteDir)
, token);
task.Wait(token);
if (token.IsCancellationRequested)
{
token.ThrowIfCancellationRequested();
}
var RetrievedFiles = new List <string>();
//errorRepository.QuickAdd("FTPFromAllcontrollers","Signal", "GetFTPFileList", Models.ApplicationEvent.SeverityLevels.Information, "Retrevied File list from " + Server);
if (RemoteFiles != null)
{
foreach (var FTPFile in RemoteFiles)
{
if (!FTPFile.IsDirectory && FTPFile.Name.Contains(FilePattern))
{
try
{
//if (Token.IsCancellationRequested)
//{
// Token.ThrowIfCancellationRequested();
//}
//If there are no errors, get the file, and add the filename to the retrieved files array for deletion later
var token2 = tokenSource.Token;
var task2 = Task.Factory.StartNew(
() => TransferFiles(FTP, FTPFile.Name, LocalDir, RemoteDir, Server)
, token2);
task2.Wait(token2);
if (token2.IsCancellationRequested)
{
token2.ThrowIfCancellationRequested();
}
else
{
RetrievedFiles.Add(FTPFile.Name);
recordsComplete = true;
}
}
//If there is an error, Print the error and try the file again.
catch (AggregateException)
{
errorRepository.QuickAdd("FTPFromAllcontrollers", "Signal", "TransferFiles",
ApplicationEvent.SeverityLevels.Medium, "Transfer Task Timed Out");
}
catch (Exception ex)
{
var errorMessage = "Exception:" + ex.Message + " While Transfering file: " +
FTPFile + " from " + RemoteDir + " on " + Server + " to " +
LocalDir;
Console.WriteLine(errorMessage);
errorRepository.QuickAdd("FTPFromAllcontrollers", "Signal", "TransferFiles",
ApplicationEvent.SeverityLevels.Medium, errorMessage);
RetryFiles.Add(FTPFile.Name);
}
Thread.Sleep(waitbetweenrecords);
}
}
}
//Delete the files we downloaded. We don't want ot have to deal with the file more than once. If we delete the file form the controller once we capture it, it will reduce redundancy.
if (DeleteFilesAfterFTP)
{
DeleteFilesFromFTPServer(FTP, RetrievedFiles, waitbetweenrecords, RemoteDir,
Server); //, Token);
}
}
catch (Exception ex)
{
errorRepository.QuickAdd("FTPFromAllcontrollers", "Signal", "RetrieveFiles",
ApplicationEvent.SeverityLevels.Medium, Server + " " + ex.Message);
Console.WriteLine(ex.Message);
}
FTP.Close();
FTP.Dispose();
//**********************************************************
//I don't think this is doing much good. -SJ 11-22-2016
//Try to get the missing files again with a new FTP connection
//if (RetryFiles.Count > 1)
//{
// foreach (string FTPFile in RetryFiles)
// {
// try
// {
// Thread.Sleep(waitbetweenrecords);
// FTPSClient _FTP = new FTPSClient();
// _FTP.Connect(Server, 21, Cred, SSLMode, null, null, 0, 0, 0, 6000, false, DM);
// TransferFiles(_FTP, FTPFile, LocalDir, RemoteDir, Server);
// if (DeleteFilesAfterFTP)
// {
// try
// {
// _FTP.DeleteFile(FTPFile);
// }
// catch (Exception ex)
// {
// Console.WriteLine("Exception:" + ex.Message + " While Deleting file: " + FTPFile + " from " + RemoteDir + " on " + Server + " to " + LocalDir);
// }
// }
// _FTP.Close();
// _FTP.Dispose();
// }
// //If there is an error, Print the error and move on.
// catch (Exception ex1)
// {
// Console.WriteLine("Exception:" + ex1.Message + " While Transfering file: " + FTPFile + " from " + RemoteDir + " on " + Server + " to " + LocalDir);
// }
// }
// }
// RetryFiles.Clear();
//**************************************
//Turn Logging off.
//The ASC3 controller stoploggin if the current file is removed. to make sure logging comtinues, we must turn the loggin feature off on the
//controller, then turn it back on.
try
{
TurnOffASC3LoggingOverSNMP(SNMPRetry, SNMPPort, SNMPTimeout, Server);
Thread.Sleep(SNMPTimeout);
TurnOnASC3LoggingOverSNMP(SNMPRetry, SNMPPort, SNMPTimeout, Server);
}
catch
{
}
}
return(recordsComplete);
}
public static bool BulktoDb(DataTable elTable, BulkCopyOptions options, string tableName)
{
MOE.Common.Models.Repositories.IApplicationEventRepository errorRepository = MOE.Common.Models.Repositories.ApplicationEventRepositoryFactory.Create();
using (options.Connection)
{
using (var bulkCopy =
new SqlBulkCopy(options.ConnectionString, SqlBulkCopyOptions.UseInternalTransaction))
{
for (var i = 1; ; i++)
{
try
{
options.Connection.Open();
}
catch
{
Thread.Sleep(Settings.Default.SleepTime);
}
if (options.Connection.State == ConnectionState.Open)
{
if (Settings.Default.WriteToConsole)
{
Console.WriteLine("DB connection established");
}
break;
}
}
var sigId = "";
if (elTable.Rows.Count > 0)
{
var row = elTable.Rows[0];
sigId = row[0].ToString();
}
if (options.Connection.State == ConnectionState.Open)
{
bulkCopy.BulkCopyTimeout = Settings.Default.BulkCopyTimeout;
bulkCopy.BatchSize = Settings.Default.BulkCopyBatchSize;
if (Settings.Default.WriteToConsole)
{
Console.WriteLine(" For Signal {0} There are rows {1}", sigId, elTable.Rows.Count);
bulkCopy.SqlRowsCopied +=
OnSqlRowsCopied;
//Console.WriteLine( " For Signal {0}", sigId);
bulkCopy.NotifyAfter = Settings.Default.BulkCopyBatchSize;
}
bulkCopy.DestinationTableName = tableName;
if (elTable.Rows.Count > 0)
{
try
{
bulkCopy.WriteToServer(elTable);
if (Settings.Default.WriteToConsole)
{
Console.WriteLine("!!!!!!!!! The bulk insert executed for Signal " + sigId +
" !!!!!!!!!");
}
options.Connection.Close();
return(true);
}
catch (SqlException ex)
{
if (ex.Number == 2601)
{
if (Properties.Settings.Default.WriteToConsole)
{
errorRepository.QuickAdd("FTPFromAllcontrollers", "Signal", "BulktoDB_SQL.ex",
Models.ApplicationEvent.SeverityLevels.Medium,
"There is a permission error - " + sigId + " - " + ex.Message);
Console.WriteLine("**** There is a permission error - " + sigId + " *****");
}
}
else
{
if (Properties.Settings.Default.WriteToConsole)
{
//Console.WriteLine("****DATABASE ERROR*****");
errorRepository.QuickAdd("FTPFromAllcontrollers", "Signal", "BulktoDB_SQL.ex",
Models.ApplicationEvent.SeverityLevels.Medium,
"General Error - " + sigId + " - " + ex.Message);
Console.WriteLine("DATABASE ERROR - " + sigId + " - " + ex.Message);
}
}
options.Connection.Close();
return(false);
}
catch (Exception ex)
{
errorRepository.QuickAdd("FTPFromAllcontrollers", "Signal", "BulktoDB_Reg.ex",
Models.ApplicationEvent.SeverityLevels.Medium,
"General Error - " + sigId + " - " + ex.Message);
Console.WriteLine(ex);
return(false);
}
}
else
{
options.Connection.Close();
return(false);
}
}
else
{
return(false);
}
}
}
}