How to handle stored procedure raiserror in mvc controller

Question

I am developing an asp.net mvc site using database first approach.

I have a simple stored procedure like this:

ALTER PROCEDURE [dbo].[ins_item]
    -- Add the parameters for the stored procedure here
    -- item supplied must be in the reference db
    @item varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    select item 
    from reference.dbo.all_items 
    where item_name = @item

    if @@ROWCOUNT !=1
    begin
        raiserror('supplied item is not found in the master item list.', 11,1)
            return -1
    end
    --Now you know the item is in the reference, insert and handle any errors
END

When I execute this in SQL Server Management Studio with an item that doesn't exist in the reference db, I get this error as expected:

Msg 50000, Level 11, State 1, Procedure ins_item, Line xx
supplied item is not found in the master item list.

In my asp.net mvc application, I imported this stored procedure into my model and in one of my controllers I have this code:

public ActionResult Create(item item)
{
    if (ModelState.IsValid)
    {
        try
        {
            var insertresult =  db.ins_item(item.item1);

            db.SaveChanges();
        }
        catch (Exception e)
        {
            ViewBag.error = e;
        }

        return RedirectToAction("Index");
    }

    return View(item);
}

db.ins_item looks like this inside my model's database context that inherits from DbContext class:

public virtual ObjectResult<string> ins_item(string item)
{
    var itemParameter = item!= null ?
                new ObjectParameter("item", item) :
                new ObjectParameter("item", typeof(string));

    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<string>("ins_item", itemParameter);
}

The issue is I don't get an exception when this action gets executed. How do I catch the raiserror that the stored procedure returns in the above controller action?

could you check the return value (-1) in your case and handle it as an exception — Krishna

Francisco Goldenstein Francisco Goldenstein · Accepted Answer · 2015-07-06T21:37:20

I don't use ObjectContext but DataContext which is the recommended way. In order to get those messages you need to add this:

var sqlConnection = dbContext.Database.Connection as SqlConnection;
if (sqlConnection != null)
{
    sqlConnection.InfoMessage += InfoMessageEventHandler;
}

private void InfoMessageEventHandler(object sender, SqlInfoMessageEventArgs e)
{
    // code to save error or warning message 
}

Take into account that this event is fired at the end of the stored procedure so you don't get live notifications.

It's important to highlight that this is not managed as an exception and that's why we need to explicitly subscribe to InfoMessage event.

On the other hand, you can execute Stored Procedures using DbContext instead of ObjectContext (old way). For example:

dbContext.Database.CommandTimeout = 240;
return dbContext.Database.SqlQuery<string>("spName {0}", value);

Instead of value you can pass a list of SqlParameters too.

How to handle stored procedure raiserror in mvc controller

1 Answers