public List<Workstation> GetRecentlyUpdatedWorkstations(int minutes)
{
List<Workstation> result = new List<Workstation>();
log.Info("Started GetRecentlyUpdatedWorkstations)");
OpenDBConnection();
string selectRecentItemsQuery =String.Format(
@"SELECT [WorkstationID], [MAC], [HostName], [IPAddress], [Comments], [ListeningPort], [TimeStamp]
FROM [Workstations]
WHERE DATEADD(minute, {0}, [TimeStamp]) >= GetDate();", minutes);
log.Debug("selectRecentItemsQuery:\r\n\r\n" + selectRecentItemsQuery);
SqlCommand command = new SqlCommand(selectRecentItemsQuery, DbConnection);
try
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Workstation ws = new Workstation()
{
Comments = reader["Comments"].ToString(),
HostName = reader["HostName"].ToString(),
IPAddress = reader["IPAddress"].ToString(),
MAC = reader["MAC"].ToString(),
ListeningPort = Convert.ToInt32(reader["ListeningPort"].ToString()),
TimeStamp = Convert.ToDateTime(reader["TimeStamp"].ToString()),
WorkstationID = Convert.ToInt64(reader["WorkstationID"].ToString())
};
result.Add(ws);
}
}
}
catch (SqlException exception)
{
log.ErrorException("Query FAILED; Exception:\r\n" + exception.Message, exception);
}
catch(Exception exception)
{
log.ErrorException("Unexpected reader/data convesion exception has occurred:\r\n" + exception.Message, exception);
}
finally
{
CloseDBConnection();
}
log.Info("Finished GetRecentlyUpdatedWorkstations");
return result;
}
public bool InsertOrUpdate(Workstation workstation)
{
log.Info("Started InsertOrUpdate(Workstation workstation)");
bool IsRecordUpdated = true;
OpenDBConnection();
string insertOrUpdateNewItemQuery =
@" IF (SELECT COUNT(*) FROM Workstations WHERE MAC=@MAC) > 0
UPDATE Workstations
SET [HostName] = @HostName,
[IPAddress] = @IPAddress,
[TimeStamp] = @TimeStamp,
[ListeningPort] = @ListeningPort
WHERE MAC=@MAC;
ELSE
INSERT INTO Workstations ([MAC], [HostName], [IPAddress], [Comments], [ListeningPort], [TimeStamp])
VALUES (@MAC, @HostName, @IPAddress, '', @ListeningPort, @TimeStamp); ";
log.Debug("insertOrUpdateNewItemQuery:\r\n\r\n" + insertOrUpdateNewItemQuery);
SqlCommand command = new SqlCommand(insertOrUpdateNewItemQuery, DbConnection);
command.Parameters.AddRange(PrepareParametersCollection(workstation));
log.Info("Inser/Update command execution)");
try
{
command.ExecuteNonQuery();
log.Info("OK; Inser/Update command execution)");
}
catch (SqlException exception)
{
IsRecordUpdated = false;
log.ErrorException("FAILED; Inser/Update command execution: )" + exception.Message, exception);
}
finally
{
CloseDBConnection();
}
log.Info("Finished InsertOrUpdate(); IsRecordUpdated=" + IsRecordUpdated);
return IsRecordUpdated;
}
public string SendDataAboutClient(string MACAddress, string IPAddress, string hostName, int listeningPort)
{
log.Info("Started SendDataAboutClient(string MACAddress, string IPAddress, string hostName)");
log.Info(String.Format("MACAddress={0}\r\nIPAddress={1}\r\nhostName={2})", MACAddress, IPAddress, hostName));
WorkstationsManager wmanager = new WorkstationsManager();
Workstation workstation = new Workstation() {
Comments = String.Empty,
HostName = hostName,
IPAddress = IPAddress,
MAC = MACAddress,
ListeningPort = listeningPort
};
log.Info(String.Format("wmanager.InsertOrUpdate"));
bool IsSuccess = wmanager.InsertOrUpdate(workstation);
if (!IsSuccess) log.Error("wmanager.InsertOrUpdate failed");
log.Info("Finished SendDataAboutClient");
return Config.GetInstance().GetLatestUpdateVersion(Server.MapPath("~"));
}
private SqlParameter[] PrepareParametersCollection(Workstation workstation)
{
List<SqlParameter> parameters = new List<SqlParameter>();
parameters.Add(new SqlParameter("@WorkstationID", workstation.@WorkstationID));
parameters.Add(new SqlParameter("@MAC", workstation.@MAC));
parameters.Add(new SqlParameter("@HostName", workstation.@HostName));
parameters.Add(new SqlParameter("@IPAddress", workstation.@IPAddress));
parameters.Add(new SqlParameter("@Comments", workstation.@Comments));
parameters.Add(new SqlParameter("@ListeningPort", workstation.@ListeningPort));
parameters.Add(new SqlParameter("@TimeStamp", DateTime.Now));
return parameters.ToArray();
}