1

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3885927
  • 3,363
  • 2
  • 22
  • 42

1 Answers1

1

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.

Francisco Goldenstein
  • 13,299
  • 7
  • 58
  • 74
  • I tried the old way but didn't raise any exception either. Regarding InfoMessageEventHandler, where do I put this code? – user3885927 Jul 06 '15 at 21:43
  • It's not an exception, it's an information message, that's why you need to subscribe to the event InfoMessage. You should put this code in your Data Layer but for testing purposes, you can subscribe to the event where you create the connection object. – Francisco Goldenstein Jul 06 '15 at 21:50
  • As I mentioned in my question I am using DbContext. There is no InfoMessage on DbContext.Database.Connection – user3885927 Jul 06 '15 at 22:04
  • That's because you need to cast your connection to SqlConnection like this: var sqlConnection = dbContext.Database.Connection as SqlConnection; I added it to my answer. – Francisco Goldenstein Jul 06 '15 at 22:06
  • Tried and it didn't fire – user3885927 Jul 06 '15 at 22:44
  • Could you please show me the code you wrote? The code I shared with you is in production and totally working. You can see the same example in http://stackoverflow.com/questions/2401874/capture-stored-procedure-print-output-in-net-different-model – Francisco Goldenstein Jul 06 '15 at 22:52
  • The code is same as what you have in your answer. I added it in ins_item method that I have in my question. It builds fine but the InfoMessageEventHandler never fires. – user3885927 Jul 06 '15 at 23:00
  • Make sure you are using the same connection object for subscribing to the event and executing the query. Also, make sure that the stored procedure you are calling is actually calling RAISERROR with an error code greater than 10. For example: RAISERROR ( 'The location must have '', '' format.' , 11 , 1 ) – Francisco Goldenstein Jul 06 '15 at 23:20