Solved

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

Posted on 2016-10-27
14
118 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

1
Comment
Question by:SamCash
  • 4
  • 4
  • 4
  • +1
14 Comments
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41863200
Hi,

Will check this today and let you know.

Regards,
Pawan
0
 

Author Comment

by:SamCash
ID: 41863205
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
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41863369
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41863510
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
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41863610
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
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41863740
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41864171
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
 

Author Comment

by:SamCash
ID: 41864216
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
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41864293
Did you try the sample code I provided (trying to populate a DataSet first, and get the DataTable from the DataSet)?
0
 

Author Comment

by:SamCash
ID: 41864436
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
 
LVL 11

Accepted Solution

by:
Nakul Vachhrajani earned 500 total points
ID: 41864539
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
 

Author Closing Comment

by:SamCash
ID: 41864570
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
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41871718
Good Solution Nakul. :)
2

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now