?
Solved

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

Posted on 2016-10-27
14
Medium Priority
?
305 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 4
  • +1
14 Comments
 
LVL 29

Expert Comment

by:Pawan Kumar
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 14

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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 51

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 29

Expert Comment

by:Pawan Kumar
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 14

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
 
LVL 29

Expert Comment

by:Pawan Kumar
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 14

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 14

Accepted Solution

by:
Nakul Vachhrajani earned 2000 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 29

Expert Comment

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

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this article, we’ll look at how to deploy ProxySQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

765 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