Linq-to-SQL - Calling stored procedure throwing exception in C#

Question

I have a stored procedure that I'm calling in C# via Linq-to-SQL. I've got some code inside of my stored procedure to check the @@error value and if it's a non-zero value, I want to return a custom error message.

I am trying to intentionally sabotage my stored procedure to test how errors get handled.

For my example below, you'll see I'm trying to insert a six character value into a column that only supports five characters -- thus resulting in a truncate error.

The stored procedure will catch the issue and save the error code / description into some OUTPUT parameters, but in my C# code, however, the call to the stored procedure raises an exception and so I can't get the values of my error code / description parameters.

To illustrate this in the most simple to understand way, let's say I have the following table:

CREATE TABLE [dbo].[Test](
[itemName] [varchar](5) NOT NULL
) ON [PRIMARY]

Here's my stored procedure code:

CREATE PROCEDURE [dbo].[TestSP]
@itemName VARCHAR(5)
,@Error BIT OUTPUT
,@ErrorReason VARCHAR(1000) OUTPUT
AS

BEGIN

SET NOCOUNT ON

DECLARE @err INT

BEGIN TRANSACTION

INSERT INTO [dbo].[Test]
       ([itemName])
 VALUES
       --(@itemName)
       ('123456')

SELECT @err = @@error 
IF @err <> 0 
    BEGIN 
        ROLLBACK TRANSACTION 
        SELECT @Error = 1
        SELECT @ErrorReason = 'Error while inserting row to Test table'
        RETURN @err 
    END

--If all 3 table inserts succeed without issue then return 0
COMMIT TRANSACTION

END

Finally, here's my C# code, which throws an exception.

The exception is: String or binary data would be truncated. The statement has been terminated. (System.Data.SqlClient.SqlException)

Is there some way to have it not raise this exception so I can use my OUTPUT params?

try
{
    using (DataClasses1DataContext dbContext = new DataClasses1DataContext())
    {
        bool? error = false;
        string errorText = "";

        int result = dbContext.TestSP("test", ref error, ref errorText);
    }
}
catch (Exception ex)
{
    string err = ex.Message;
    throw;
}

Answer 1

You should use TRY/CATCH in your TSQL code inside of stored procedure. This way actual exception will be handled on DB side and will not be thrown to C# caller.

See the examples in the MSDN link, including committing/rolling back transactions.

Answer 2

You could try something like this

BEGIN TRY
BEGIN TRANSACTION
   INSERT INTO [dbo].[Test] ([itemName]) VALUES ('123456')
COMMIT TRANSACTION
END TRY
BEGIN CATCH
   ROLLBACK TRANSACTION 
   SELECT @Error = 1
   SELECT @ErrorReason = 'Error while inserting row to Test table'
   RETURN @err 
END CATCH