CODEWITHSUNDEEP
entity-frameworkexception
Manvinder
Manvinder

Reputation: 4587

Handles SQL Exception from Entity Framework

In my sql stored procedure, i do some insertion and updating which in some scenarios throws Primary Key or unique key violation.

When I try to execute this procedure from ADO.net, .net application also throws that exception and let me know that something wrong had happen.

But when I try to execute this procedure from EF, it just executes. Neither it show anything nor update anything.

How should I handle or notify user that something wrong had happen?

Ado.Net code is 

 SqlConnection sqlConnection = new SqlConnection(@"data source=database01; database=test; user id=test; password=test;");
        SqlCommand cmd = new SqlCommand("[uspUpdateTest]", sqlConnection);
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("RunID", RunID);
        cmd.Parameters.AddWithValue("RunCode", RunCode);
        sqlConnection.Open();
        var str = cmd.ExecuteNonQuery();

Entity Framework Code is

 TestEntities context = new TestEntities();
        var str=context.UpdateRun(RunID, RunCode);

Upvotes: 1

Views: 8294

Answers (4)

Aung Ko Lin
Aung Ko Lin

Reputation: 1

We can use the following way for sql raised error exception from entity framework: Let's say, we have DBContext. So that

var connection= (SqlConnection)db.Database.Connection;
if (connection != null && connection.State == ConnectionState.Closed)
        {
            connection.Open();
        }
        SqlCommand com = new SqlCommand("spname", connection);
        com.CommandType = CommandType.StoredProcedure;
com.Parameters.Add(new SqlParameter("@parameter", parameter));
                        try
                        {
                            com.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {
                            throw ex.message;
                        } `

Upvotes: 0

Aaron Newton
Aaron Newton

Reputation: 2306

First of all, make sure you're throwing an Exception in your stored procedure which we can catch in our C# code. See - http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/efea444e-6fca-4e29-b100-6f0c5ff64e59 - quote:

If you want RAISERROR to throw a SqlException, you need to set its severity above 10. Errors with a severity of 10 and below are informational, and thus don't throw exceptions.

I'll also show you the following code. I have been using this in my MVC controllers when getting data from my service layer using Entity Framework:

try
{

   try
   {
        //Entity Framework/Data operations that could throw the data exception
        //...
   } catch (DataException dex) //see http://msdn.microsoft.com/en-us/library/system.data.dataexception.aspx
   {
      //Specifically handle the DataException
      //...
   }
}
catch (Exception e)
{
   //do something (logging?) for the generic exception
   throw e;
}

You can put a breakpoint on the last catch if the first catch doesn't trigger to see Exception-type/inner-exception of 'e' and go from there. It is useful to put a breakpoint on the generic exception, as it let's me know when I haven't handled something.

Upvotes: 2

manav inder
manav inder

Reputation: 3601

I am very much sure, you must set some return type(dummy) in your function import. It makes sense most of the time, because if you don't do so, your method name does not appear in intellisense and you will no be able to access it using context.MethodName.

My suggestion for you is, remove the return type of your Function Import and set it to none. Execute your method using ExecuteFunction method of context.

Context.ExecuteFunction(FunctionName,Parameters). It'll definitely throws the exception.

Upvotes: 3

Nick
Nick

Reputation: 2325

The question here gives quite a nice summary of catching and handling the exceptions gracefully. You have several options after that for rolling back etc.

Upvotes: -1

Related Questions