1

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);
Manvinder
  • 4,495
  • 16
  • 53
  • 100
  • calling code?.................. – Mitch Wheat Nov 02 '12 at 05:05
  • 1
    Are you absolutely sure that the EF code should throw an exception? Did you run this in debug mode with "break when an exception is thrown"? – Gert Arnold Nov 02 '12 at 20:06
  • Good Advice from Gert Arnold here. You can also place a breakpoint early in the code, then use `f10` to step over it line by line (`f11` if you want to enter the method you're calling). – Aaron Newton Nov 03 '12 at 04:46

4 Answers4

3

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.

manav inder
  • 3,531
  • 16
  • 45
  • 62
2

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.

Aaron Newton
  • 2,124
  • 1
  • 28
  • 31
  • The code is not going to catch block because it does not throw any exception, this is the problem exception raised by sql stored procedure has not been traced back to Entity Framework – Manvinder Nov 03 '12 at 05:17
  • Did you take a look at the link I posted at the start of my answer? If you're not getting any exceptions, this could be one reason why. Modify your stored procedure to start with the `RAISERROR` (or create a new procedure for testing) in order to test the error-raising mechanism back to your C# code. Once you get this working, comment it out and go from there. – Aaron Newton Nov 04 '12 at 12:02
  • Sorry, for clarity what I meant here was modify your stored procedure so that it will always throw an exception, then call the SQL stored procedure using your C# code to make sure that you can catch any exception before you try and catch your specific exception. See http://msdn.microsoft.com/en-us/library/ms177497(v=sql.105).aspx for `RAISERROR` usage. Specifically note the comments regarding severity: _A RAISERROR severity of 11 to 19 executed in the TRY block of a TRY…CATCH construct causes control to transfer to the associated CATCH block._ – Aaron Newton Nov 04 '12 at 12:18
  • You may also find this thread helpful - http://stackoverflow.com/questions/3990288/catch-the-error-from-stored-procedure-in-c-sharp – Aaron Newton Nov 04 '12 at 12:20
  • Thanks Aaron but the issue was not in procedure, it was in my function import, my procedure was not returning any thing, but just to get my function from the context in EF, i choose a dummy return type and that was causing the problem. Solution from MSingh works for me. Thanks for your efforts.:-) – Manvinder Nov 05 '12 at 05:14
0

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;
                        } `
Aung Ko Lin
  • 1
-1

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.

Community
  • 1
  • 1
Nick
  • 2,285
  • 2
  • 14
  • 26