/// <summary>
/// Add scan results to database, checking for changes from to recent IP. Returns number of rows affected.
/// </summary>
/// <param name="results"></param>
/// <returns>Number of affected/inserted rows.</returns>
public static bool InsertScanResults(List <ScanResult> results, int maxEntriesAllowed)
{
Logging.Debug("Starting DB insert...");
if (results.Count <= 0)
{
return(false);
}
int affectedRows = 0;
int insertedRows = 0;
Logging.Debug("Getting database...");
var db = DBFactory.GetDatabase();
Logging.Debug("Got database.");
Logging.Debug("Starting transaction...");
using (var trans = db.StartTransaction())
{
Logging.Debug("Transaction started.");
try
{
foreach (ScanResult result in results)
{
if (!string.IsNullOrEmpty(result.DeviceGUID))
{
// Trim # of historical entries.
TrimHistory(result.DeviceGUID, maxEntriesAllowed, trans);
// Add a new entry if the IP has not been previously recorded,
// or update the timestamp if the IP already exists.
if (HasIP(result.DeviceGUID, result.IP))
{
Logging.Verbose($@"UPDATE: { result.DeviceGUID } - { result.IP }");
insertedRows++;
string ipId = MostRecentIPIndex(result.DeviceGUID, result.IP);
string updateQry = $@"UPDATE device_ping_history SET timestamp = '{ DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") }' WHERE device_guid = '{ result.DeviceGUID }' AND id = '{ ipId }'";
affectedRows += db.ExecuteNonQuery(updateQry, trans);
}
else
{
Logging.Verbose($@"ADD: { result.DeviceGUID } - to: { result.IP }");
insertedRows++;
string insertQry = $@"INSERT INTO device_ping_history (device_guid, ip, hostname) VALUES ('{ result.DeviceGUID }','{ result.IP }','{ result.Hostname }')";
affectedRows += db.ExecuteNonQuery(insertQry, trans);
}
}
}
if (affectedRows == insertedRows)
{
trans.Commit();
Logging.Verbose($@"{ affectedRows } entries added.");
return(true);
}
else
{
trans.Rollback();
return(false);
}
}
catch (Exception ex)
{
Logging.Error(ex.ToString());
trans.Rollback();
return(false);
}
}
}
/// <summary>
/// Add scan results to database, checking for changes from to recent IP. Returns number of rows affected.
/// </summary>
/// <param name="results"></param>
/// <returns>Number of affected/inserted rows.</returns>
public static bool InsertScanResults(List <ScanResult> results)
{
Logging.Verbose("Starting DB insert...");
if (results.Count <= 0)
{
return(false);
}
int affectedRows = 0;
int insertedRows = 0;
Logging.Verbose("Getting database...");
var db = DBFactory.GetDatabase();
Logging.Verbose("Got database.");
Logging.Verbose("Starting transaction...");
using (var trans = db.StartTransaction())
{
Logging.Verbose("Transaction started.");
try
{
foreach (ScanResult result in results)
{
if (!string.IsNullOrEmpty(result.DeviceGUID))
{
// Add a new entry if the IP has not been previously recorded,
// or update the timestamp if the IP already exists.
if (HasIP(result.DeviceGUID, result.IP))
{
Logging.Log("UPDATE: " + result.DeviceGUID + " - " + result.IP);
insertedRows++;
string ipId = MostRecentIPIndex(result.DeviceGUID, result.IP);
string updateQry = "UPDATE device_ping_history SET timestamp = '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' WHERE device_guid = '" + result.DeviceGUID + "' AND id = '" + ipId + "'";
var cmd = db.GetCommand(updateQry);
affectedRows += db.ExecuteQuery(cmd, trans);
}
else
{
var lastip = PreviousIP(result.DeviceGUID);
Logging.Log("ADD: " + result.DeviceGUID + " - " + " from: " + lastip + " to: " + result.IP);
insertedRows++;
string insertQry = "INSERT INTO device_ping_history (device_guid, ip, hostname) VALUES ('" + result.DeviceGUID + "','" + result.IP + "','" + result.Hostname + "')";
var cmd = db.GetCommand(insertQry);
affectedRows += db.ExecuteQuery(cmd, trans);
}
}
}
if (affectedRows == insertedRows)
{
trans.Commit();
Logging.Log(affectedRows + " entries added.");
return(true);
}
else
{
trans.Rollback();
return(false);
}
}
catch (Exception ex)
{
Logging.Error(ex.Message);
trans.Rollback();
return(false);
}
}
}