代码之家 › 专栏 › 技术社区 › Eric Belair

如何捕获由.asmx文件中的RAISEERROR()触发的SQL存储过程错误?

raiseerror asmx stored-procedures sql .net

0

Eric Belair · 技术社区 · 17 年前

我是非常对.NET来说是新的。到目前为止,我所做的只是通过复制和粘贴现有代码(按照上级的指示),将一些SQL存储过程调用实现为Web服务。

我正在通过电话呼叫一些命令 Database.ExecuteNonQuery()

我的asmx文件:

[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");
    }
}

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

2 回复 | 直到 17 年前

1

2

Community Mohan Dere 9 年前

问题是错误严重性太低。严重程度1-10被视为信息消息,不会中断C#app的流程。

更详尽的解释可以在 answer 从…起 Remus Rusanu

2

1

Eric Belair 17 年前

我用以下内容更新了存储过程:

/* 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

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);