Background info
I am currently working on a email crawler which will make records in a db of emails which I have crawled for information. Currently my company has constrained me into using Petapoco for code maintenance issue. The current issue I am running to is duplicate record are being recorded into the the db. I have been looking for examples or documentation that can give me a hint on how to achieve this but I have not been able to find any.
Issue
Currently I am able to insert records into the db with out an issue, but it also inserts duplicates as well.
Additional information
The Column I am trying to make sure is unique is [AppointmentUniqueId] , I have a primary key of Id, my table is AppointmentActivities, and the what I am trying to insert is a class model of a record .
Current Code
public static async Task<bool> InsertActivitiesData(List<Act_Appointments> recordList)
{
int recordsInserted = 0;
try
{
using (PetaPoco.Database databaseInstance = new PetaPoco.Database("PMconString"))
{
foreach (var record in recordList)
{
databaseInstance.Insert("AppointmentActivities", "Id", record);
recordsInserted++;
}
}
log4net.LogManager.GetLogger("AppInfoLogger").Info("[ ServiceRan : Insert Email Data To DB ]" + "[ Records Inserted: " + recordsInserted.ToString() + " ]");
return true;
}
catch (Exception ex)
{
log4net.LogManager.GetLogger("AppErrorLogger").Error("[ReconcileEvents]" + JsonConvert.SerializeObject(ex));
log4net.LogManager.GetLogger("EmailLogger").Error("[ReconcileEvents]" + JsonConvert.SerializeObject(ex));
return false;
}
}
Well I took the most practical way into this question. I am not sure if it is the most efficient, but due to the time constraint I really could not dwell to long on this question. I went ahead and ran a single query on the AppointmentUniqueId and if it came back null it means that the item does not exists.
public static async Task<bool> InsertActivitiesData(List<Act_Appointments> recordList)
{
int recordsInserted = 0;
int duplicates = 0;
try
{
using (PetaPoco.Database databaseInstance = new PetaPoco.Database("PMconString"))
{
foreach (var record in recordList)
{
if (databaseInstance.FirstOrDefault<Act_Appointments>("SELECT * FROM AppointmentActivities WHERE AppointmentUniqueId =@0", record.AppointmentUniqueId) == null)
{
databaseInstance.Insert("AppointmentActivities", "Id", record);
recordsInserted++;
}
else
{
duplicates++;
}
}
}
log4net.LogManager.GetLogger("AppInfoLogger").Info("[Insert Appointment Data To DB]" + "[ Records Inserted: " + recordsInserted.ToString() + " ]"+ "[ Duplicates Not Inserted: " + duplicates.ToString() + " ]");
return true;
}
catch (Exception ex)
{
log4net.LogManager.GetLogger("AppErrorLogger").Error("[ReconcileEvents]" + JsonConvert.SerializeObject(ex));
log4net.LogManager.GetLogger("EmailLogger").Error("[ReconcileEvents]" + JsonConvert.SerializeObject(ex));
return false;
}
}
PS.
I will keep this open for 10 days in order to hear feedback from the community, and to see if there has been a different approach to what I am trying to do.