0

I have a stored procedure to insert data into a table.

This is how I call the stored procedure from the controller method:

var insert_query = entities.Database.SqlQuery<Call_Info>("exec [dbo].[insert_call_info] @call_id, @user_id, @call_arrive, @call_end, @info",
                    new SqlParameter("call_id", call_id),
                    new SqlParameter("user_id", u_id),
                    new SqlParameter("call_arrive", call_arrive),
                    new SqlParameter("call_end", call_end),
                    new SqlParameter("info", info)
                    ).ToList();

 var jsonResult = JsonConvert.SerializeObject(insert_query); // <-- using Newtonsoft.Json
 return Json(jsonResult);

If I insert a call_id value that is already stored in the table, I get this error, which is correct:

Message": "An error has occurred

Violation of UNIQUE KEY constraint 'UQ__Call_Inf__427DCE6904E3DF9B'. Cannot insert duplicate key in object 'dbo.Call_Info'. The duplicate key value is (91390).

Is there a way to catch this error in the controller?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
zinon
  • 4,427
  • 14
  • 70
  • 112
  • 1
    Modify your stored procedure to *avoid* such errors completely. Add a WHERE clause in your INSERT statement for example, or replace INSERT into a MERGE that updates any existing records – Panagiotis Kanavos May 25 '20 at 13:05
  • @PanagiotisKanavos thank you! I will use this for an update stored-procedure. – zinon May 25 '20 at 13:15

2 Answers2

2

Definitely. You can use a try catch block to capture the SqlException

try
{
    //SQL query
}
catch(SqlException ex)
{
    foreach(SqlError err in ex.Errors)
    {
        if((err.Number == 2601) //unique nonclustered index
            || (err.Number == 2627)) //unique constraint
        //handle it
    }
}
mb14
  • 460
  • 4
  • 15
1

Use 

catch(SqlException ex) 
{

}

How to catch a specific SqlException error?

Ajay Kelkar
  • 4,591
  • 4
  • 30
  • 29