We help IT Professionals succeed at work.

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

1,567 Views
1 Endorsement
Last Modified: 2016-11-03
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

Comment
Watch Question

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Hi,

Will check this today and let you know.

Regards,
Pawan

Author

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
Nakul VachhrajaniSenior Manager
CERTIFIED EXPERT

Commented:
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

Vitor Montalv√£oIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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?
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
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 !!
Nakul VachhrajaniSenior Manager
CERTIFIED EXPERT

Commented:
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.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
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

Author

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
Nakul VachhrajaniSenior Manager
CERTIFIED EXPERT

Commented:
Did you try the sample code I provided (trying to populate a DataSet first, and get the DataTable from the DataSet)?

Author

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
Senior Manager
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

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
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Good Solution Nakul. :)
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.