Prevent insertion of duplicate records in SQL c# programatically

Question

I have a code in which I am trying to insert the records. All the records are inserted but with duplicates. I want to stop duplication of same record.

public int SaveReadingsInCIS(Reading pReading)
{
    SqlConnection con = new SqlConnection("Data Source=RND-FAISAL;Initial Catalog=hesco;Integrated Security=True;MultipleActiveResultSets=True;");

    int result = -1;
    string dt = pReading.XTimeStamp.Replace("T"," ");
    string fdt = dt.Remove(dt.Length-6);
    if (con.State != ConnectionState.Open)
    {
        con.Open();
    }

    SqlCommand insert_cmd = new SqlCommand("INSERT INTO [dbo].[billing]([unique_id],[ref_no],[msn],"+
                                           "[kWh1],[kVarh1],[kWh2],[kVarh2],[datetime])VALUES(@uniqueid,@refno,@msn,@kwh1,@kvarh1,@kwh2,@kvarh2,@datetime)",con);

    insert_cmd.CommandType = CommandType.Text;
    insert_cmd.Parameters.AddWithValue("@uniqueid",pReading.App_no);
    insert_cmd.Parameters.AddWithValue("@refno",pReading.Ref_no);
    insert_cmd.Parameters.AddWithValue("@msn",pReading.Serial_no);
    insert_cmd.Parameters.AddWithValue("@kwh1",pReading.KWH1);
    insert_cmd.Parameters.AddWithValue("@kvarh1",pReading.KVARH1);
    insert_cmd.Parameters.AddWithValue("@kwh2",pReading.KWH2);
    insert_cmd.Parameters.AddWithValue("@kvarh2",pReading.KVARH2);
    insert_cmd.Parameters.AddWithValue("@datetime", fdt.ToString());

    try
    {
        result = insert_cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        result = ex.HResult;
    }
    finally
    {
        if (con != null && con.State == ConnectionState.Open)
        {
            con.Close();
        }
    }
    return result;

}

In above code there is a date time fdt. There may be duplicate msn but of different date time. I want to stop duplicate insertion of same date time and msn

Any help would be highly appreciated

Add a unique constraint in the database. No amount of pre-checking you do in code can prevent some other code racing with you and inserting a row *after* you've completed your checks but *before* you insert your row. — Damien_The_Unbeliever, Mar 29 '19 at 06:48
if you don't want to prevent to send the new data into your DB a "inesrt-or-update" could be a solution: https://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server — kara, Mar 29 '19 at 06:55
@Damien_The_Unbeliever if I add unique constraint for both `msn` and `datetime` would it work under my conditions. i.e. it will only allow different date time enteries for same msn ? — Moeez, Mar 29 '19 at 07:28
Yes, you add one unique constraint that contains all of the columns that, taken together, should be unique. — Damien_The_Unbeliever, Mar 29 '19 at 07:29

Prevent insertion of duplicate records in SQL c# programatically

0 Answers0