CODEWITHSUNDEEP
c#entity-frameworkexceptionerror-handling
user15741
user15741

Reputation: 1412

Database.ExecuteSqlCommand - capture SQL exceptions

====Correction====

SQL Exceptions are thrown from Database.ExecuteSqlCommand. I was expecting an exception but was actually getting no change from my stored proc. In debugging I saw another exception and confused the two behaviors. I can't delete the question because it has an answer.

Original Question

In EF 5 (and maybe others), Database.ExecuteSqlCommand returns -1 when a SQL exception occurs. When debugging in Visual Studio the exception is visible, but try/catch blocks don't see the error.

There is no property on the Database object that holds errors or state. The result of the method is an integer. How do you access and capture the error?

Corrected example

        try
        {
            int result = ctx.Database.ExecuteSqlCommand("EXEC dbo.Proc @prm", param );
        }
        catch(Exception ex)
        {
            //This works
            throw ex;
        }

Upvotes: 0

Views: 4068

Answers (1)

Bassam Alugili
Bassam Alugili

Reputation: 17033

You have to throw the error in the SQL server, for example:

1) Add the the following stored procedure to your database:

CREATE PROCEDURE myTest   

AS   
 print 'hello'
 RAISERROR('error', 16, 1, 11, 'Take care')
GO

When you called from SSM you

"Msg 50000, Level 16, State 1, Procedure myTest, Line 14" error

2) In your code call the stored procedure:

try
{
  var s =   myDbContext.Database.ExecuteSqlCommand("myTest44");
  Console.WriteLine(s);
}
catch (Exception ex)
{
   Console.WriteLine(ex);
   throw;
}

In the output of the console you will see the error message "error" from the SQL Server. as shown in the image below:

enter image description here

Conclusion:

  • You need a structured exception handling in your stored procedure, so that the errors can be redirected to .Net.

  • Normmaly the exceptions will be normal thrown, for example if the SP not exists in the database then you will get an SqlException with message {"Could not find stored procedure XXX"}.

Use the SQL server Try/Catch to raise the error for the custom messages.

Upvotes: 1

Related Questions