Preventing duplicate inserts into SQL db when comparing a specific column, must use PetaPoco

Question

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

EasyE · Accepted Answer · 2017-02-21T13:39:32.243

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.

Preventing duplicate inserts into SQL db when comparing a specific column, must use PetaPoco

1 Answers1