Bubble user-defined Sql RAISERROR(...) to c# exception

EE,

Sql 2008 R2.  Unable to Bubble user-defined Sql RAISERROR(...) to c# exception.  

Simplified example usage; a SELECT correctly returns no rows because of the WHERE clause.  IF @@ROWCOUNT = 0; RAISEERROR(...) pass the error/exception to some higher level where it can be handled.

Evidence or clues.  Sql generated errors bubble to c# but Sql user-defined errors do not bubble to c#.  Both are logged in the sql server logs.

Simply toggle line ~ 28 from commented to un-commented to exhibit the issue.
"--select @firstNum + @secondNum as result --Un-comment me and watch the error come and go at the c# level???"

Simplified webform and sproc exhibiting improper behavior below.

Kind Regards
Sam

Sql:

USE [SQLUTL]
GO
/****** Object:  StoredProcedure [dbo].[Test_Sp]    Script Date: 10/26/2016 16:08:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Test_2_Sp]
       @firstNum int,
       @secondNum int
AS
BEGIN
LINENO 18

EXEC sp_addmessage   
    @msgnum = 50001,   
    @severity = 16,  
    @msgtext =   N'This is a user defined error message test_2_sp',
    @lang = 'us_english';  
    
DECLARE @ContactID_str nvarchar(16);
	BEGIN TRY
	
       --select @firstNum + @secondNum as result --Un-comment me and watch the error come and go???
       
       RAISERROR (50001, -1,-1) WITH Log; --https://msdn.microsoft.com/en-us/library/ms178592.aspx
	END TRY
	BEGIN CATCH
		declare @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int;
		select @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
		
		raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
	
	END CATCH       
EXEC sp_dropmessage 50001, 'all'; 
       
END

Open in new window


c#;

namespace SQLUTL.Code.WebForms.Public
{
    public partial class EE_Conn_Exception : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string ConnectionStringName = "SQLUTLtest";
            //string con = @"Data...";
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnectionStringName].ToString());

            using (SqlCommand cmd = new SqlCommand("Test_2_Sp", sqlcon))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@firstnum", "1");
                cmd.Parameters.AddWithValue("@secondnum", "0");
                
                sqlcon.Open();

                try
                {
                    SqlDataAdapter adp = new SqlDataAdapter(cmd);

                    DataTable dt = new DataTable();
                    adp.Fill(dt);
                    Response.Write("Hello");
                }
                catch(Exception ex)
                {
                    Response.Write(ex.Message);
                }
                finally
                {
                    Response.Write("</br>finally");
                }
            }

        }
    }
}

Open in new window

SamCashAsked:
Who is Participating?
 
Nakul VachhrajaniConnect With a Mentor Technical Architect, Capgemini IndiaCommented:
I am glad that using the DataSet helped resolve your long-pending problem.

In a simple (but a long) narrative, here's why using a DataSet works.

A DataSet is a collection of Data Tables. Hence, when filling up a DataSet, the system will wait until the entire command has completed execution and the Fill method fills up all the DataTables. This is especially useful when you have stored procedures that return multiple result sets (or DataTables as they are called on the .NET side of things).

Restricting myself to the sample SP;
- When the statement (SELECT @firstNum + @secondNum) was commented, we could capture the exceptions even when using a DataTable. This is because the SP never returned anything else - the only statement was a RAISEERROR

- When the statement was uncommented, the stored procedure actually returned a result set (one column with the sum of the two numbers). This result set was one DataTable and hence the DataTable was successfully filled by the .NET code.
Now, while the stored procedure continued executing and generated an exception with the RAISEERROR, there was nothing in the .NET code to wait for the execution to complete and "catch" the exception. This is why your code gave your varying results.

I hope the explanation above helped in understanding the internals. I tried to keep the narrative as simple as I could. Still if there is some confusion, do drop a line.

Have a good week-end!
1
 
Pawan KumarDatabase ExpertCommented:
Hi,

Will check this today and let you know.

Regards,
Pawan
0
 
SamCashAuthor Commented:
Pawan,

This is the issue we have been working on for 7 long days.  With respect I would like more eyes on this problem.  How do I get more sql/c# experts looking at this?

Regards
Sam
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
I think the code will be unable to catch SQL standard Exceptions as well. For example, if I add a SELECT 1/0 (critical divide by zero error) after performing the addition, the code will continue to populate the Data Table and not catch the exception.

Can you update your C# code to the following? Basically, rather than just filling up the DataTable, I am trying to get a DataSet first. This would fail if an exception has been generated. (I tried this on a WinForms App, hence the MessageBoxes. Request you to update the code as necessary).

            using (SqlCommand mySqlCommand = new SqlCommand("Test_2_Sp", myCustomConnection))
            {
                mySqlCommand.CommandType = CommandType.StoredProcedure;
                mySqlCommand.Parameters.AddWithValue("@firstnum", "1");
                mySqlCommand.Parameters.AddWithValue("@secondnum", "0");

                myCustomConnection.Open();

                try
                {
                    SqlDataAdapter adp = new SqlDataAdapter(mySqlCommand);

                    DataSet myDataSet = new DataSet();
                    adp.Fill(myDataSet);

                    if (myDataSet.Tables.Count > 0)
                    {
                        DataTable dt = myDataSet.Tables[0];
                    }

                    MessageBox.Show("Hello World!");
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Ouch!");
                }
                finally
                {
                    MessageBox.Show("Good Bye!");
                }
            }

Open in new window

2
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Do you really need to catch the error in SQL and in C#?
Did you try to remove the catch from SQL to check if the one in C# works?
0
 
Pawan KumarDatabase ExpertCommented:
Pls Try... ExecuteNonQuery

Fill method does not trigger InfoMessage method you are not able to capture the messages.

InfoMessageHandler is not raised within the scope of the executing command object that why it is not bubble up to command object's method. The are executing in a different thread.

--

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication13
{
    class Program
    {
        static void Main(string[] args)
        {
            test();
        }
        static void myConnection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
        {
            var error = e.Message;
        }
        static void test()
        {
            string con = @"Data Source = localhost; Initial Catalog=test; Integrated Security = true;";
            SqlConnection sqlcon = new SqlConnection(con);
            sqlcon.FireInfoMessageEventOnUserErrors = false;
            sqlcon.InfoMessage += new SqlInfoMessageEventHandler(myConnection_InfoMessage);


            DataTable dt;
            using (SqlCommand cmd = new SqlCommand("Test_2_Sp", sqlcon))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@firstNum", "1");
                cmd.Parameters.AddWithValue("@secondNum", "0");

                sqlcon.Open();

                try
                {
                    cmd.ExecuteNonQuery();

                    //SqlDataAdapter adp = new SqlDataAdapter(cmd);
                    //dt = new DataTable();

                    //adp.Fill(dt);
                }
                catch (System.InvalidOperationException ex)
                {
                    string str;
                    str = "Source:" + ex.Source;
                    str += "\n" + "Message:" + ex.Message;
                    str += "\n" + "\n";
                    str += "\n" + "Stack Trace :" + ex.StackTrace;
                    Console.WriteLine(str, "Specific Exception");
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    string str;
                    str = "Source:" + ex.Source;
                    str += "\n" + "Message:" + ex.Message;
                    Console.WriteLine(str, "Database Exception");
                }
                catch (System.Exception ex)
                {
                    string str;
                    str = "Source:" + ex.Source;
                    str += "\n" + "Message:" + ex.Message;
                    Console.WriteLine(str, "Generic Exception");
                }
                finally
                {
                    if (sqlcon.State == ConnectionState.Open)
                    {
                        Console.WriteLine("Finally block closing the connection", "Finally");
                        sqlcon.Close();
                    }
                }
            }
        }


    }
}


--

Open in new window


SP Code

--


USE [SampleDB]
GO
/****** Object:  StoredProcedure [dbo].[Test_2_Sp]    Script Date: 28-Oct-16 11:45:08 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Test_2_Sp] 
       @firstNum int,
       @secondNum int
AS
BEGIN
LINENO 18

EXEC sp_addmessage   
    @msgnum = 50001,   
    @severity = 16,  
    @msgtext =   N'This is a user defined error message test_2_sp',
    @lang = 'us_english';  
    
DECLARE @ContactID_str nvarchar(16);
       --BEGIN TRY
       
       select @firstNum + @secondNum as result --Un-comment me and watch the error come and go???
       
       RAISERROR (50001, -1,-1) WITH Log; --https://msdn.microsoft.com/en-us/library/ms178592.aspx
          
       --END TRY
       --BEGIN CATCH
       --     declare @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int;
       --     select @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
              
       --     raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
       
       --END CATCH       
EXEC sp_dropmessage 50001, 'all'; 
       
END

--[dbo].[Test_2_Sp] 1,0

--

Open in new window


Hope it helps !!
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
I believe ExecuteNonQuery() will work when you have a stored procedure that is used to update or insert data.

If you are fetching data from the server, ExecuteNonQuery() will not work because it cannot "Query" data.
0
 
Pawan KumarDatabase ExpertCommented:
Thats the incorrect information. it can be used for Update/Insert/Delete. For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.

Read more here -
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(v=vs.110).aspx
0
 
SamCashAuthor Commented:
Vitor, Pawan, Nakul,

Thank you all for your attention.  I am not sure what I can do to assist.  Other than continuing to search the internet to find an example/solution of someone's post who had the same problem, as I have been doing for 8 days now :-).  Let me know.

Vitor, yes I need a structure to catch Sql errors at the Sql level and address the error there if possible OR pass them up to a level they can be dealt with. Knowing where the error originated effects how the errors are resolved.  I am new but I read/thought/think that "catch at source and bubble up until it is resolvable" is the proper way to deal with exceptions and errors.

Thanks Again for all your assistance and sharing your experience.

Regards
Sam
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Did you try the sample code I provided (trying to populate a DataSet first, and get the DataTable from the DataSet)?
0
 
SamCashAuthor Commented:
Nakul,

Yes, just finished.  It did solve the problem.  Sorry I somehow missed this this morning.  I think it would be helpful to others If we could know why this works with a "DataSet" and not a "DataTable".

Let me know if there will be another post or not on why.  So I can properly credit and close this question.

Thanks Again this has been an eight day issue.

My Best Regards
Sam
0
 
SamCashAuthor Commented:
Nakul,

I will have a good weekend now that I won't be still working on this.  

You and everyone have a good weekend also!

Namaste~
Sam
2
 
Pawan KumarDatabase ExpertCommented:
Good Solution Nakul. :)
2
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.