How can I capture a SQL Stored Procedure Error triggered by RAISEERROR() in .asmx file?

Question

I'm very new to .NET. All I've done so far is implement some SQL Stored Procedure calls as WebServices by copying and pasting existing code (as instructed by my superior).

I am calling some commands via Database.ExecuteNonQuery(). In the SQL Stored Procedure, I am performing some security checks based on the LoginId passed in (a user can only edit items of which they are the owner). If the security check fails, I am calling RAISEERROR() to trigger an error, but my .asmx file is not seeing this as an exception, and simply exits the Stored Procedure.

My asmx file:

[WebMethod]
public XmlDocument UpdateSomeItem(Int32 ItemIdNmb, String Foo)
{
    try
    {

        // Create the Database object, using the default database service. The
        // default database service is determined through configuration.
        Database db = DatabaseFactory.CreateDatabase();
        DbParameter param;

        string sqlCommand = "updateSomeItem";
        DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

        db.AddInParameter(dbCommand, "ItemIdNmb", DbType.Int32, ItemIdNmb);
        db.AddInParameter(dbCommand, "Foo", DbType.String, Foo);
        db.AddInParameter(dbCommand, "UpdateLoginIdNmb", DbType.Int32, SessionLoginIdNmb);

        #region Return Parameter
        param = dbCommand.CreateParameter();
        param.ParameterName = "Return";
        param.Direction = ParameterDirection.ReturnValue;
        param.DbType = DbType.Int32;

        dbCommand.Parameters.Add(param);
        #endregion

        // Execute the Command
        db.ExecuteNonQuery(dbCommand);

        myDataSet = new DataSet();

        myDataSet.DataSetName = "DataSet";
        myDataSet.Tables.Add(errorDataTable);

        statusDataRow["Status"] = "Success";
        statusDataTable.Rows.Add(statusDataRow);
        myDataSet.Tables.Add(statusDataTable);

        returncodeDataRow["ReturnCode"] = dbCommand.Parameters["Return"].Value;
        returncodeDataTable.Rows.Add(returncodeDataRow);
        myDataSet.Tables.Add(returncodeDataTable);

        xmlReturnDoc.LoadXml(myDataSet.GetXml());

        return xmlReturnDoc;
    }
    // THIS IS WHERE I WANT MY RAISE ERROR TO GO
    catch (Exception e) 
    {
        return ReturnClientError(e, "someerror");
    }
}

My SQL Stored Procedure:

CREATE PROCEDURE updateSomeItem
(
    @ItemIdNmb             INT,
    @Foo                   VARCHAR(100),
    @UpdateLoginIdNmb      INT
)

AS

SET NOCOUNT ON

/* If the user performing the action did not create the Item in question, 
    raise an error. */
IF NOT EXISTS
    (
    SELECT 1
    FROM Items
    WHERE IdNmb = @ItemIdNmb
        AND LoginIdNmb = @UpdateLoginIdNmb
    )
    RAISERROR (
        'User action not allowed - User is not the owner of the specified Item', 
        10, 
        1)

UPDATE Items
SET Foo = @Foo
WHERE IdNmb = @ItemIdNmb

RETURN 0

SET NOCOUNT OFF
GO

Answer 1

The problem is that the Error Severity it too low. Severities 1 - 10 are treated as information messages and do not break the flow of a C# app.

A more thorough explanation can be found in an answer from Remus Rusanu.

Answer 2

I updated my Stored Procedure with the following:

/* If the user performing the action did not create the Item in question, 
    return a code other than 0. */
IF NOT EXISTS
    (
    SELECT 1
    FROM Items
    WHERE IdNmb = @ItemIdNmb
        AND LoginIdNmb = @UpdateLoginIdNmb
    )
    RETURN 1

And I handle in my .asmx file like this:

int returnValue = (int)dbCommand.Parameters["Return"].Value;

if (returnValue == 0)
    statusDataRow["Status"] = "Success";
/* Stored Procedure will return a value of "1" if the user is 
    attempting to update an Item that they do not own. */
else
    statusDataRow["Status"] = "Error";

statusDataTable.Rows.Add(statusDataRow);
myDataSet.Tables.Add(statusDataTable);

returncodeDataRow["ReturnCode"] = dbCommand.Parameters["Return"].Value;
returncodeDataTable.Rows.Add(returncodeDataRow);
myDataSet.Tables.Add(returncodeDataTable);

Answer 3

Hey guys apart from that you should consider this nice post of microsoft and its day-by-day bugs of SQL Server

http://support.microsoft.com/kb/152135/en-us/