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