ASP.net C# -- error handling when stored procedure is called

We use SQL Server 2005 for db backen.
We use ASP.net 3.5 and C# for the front end.

Here in our shop I am being told that if I (the DBA) use TRY CATCH error handling in the stored procedure (that is called from the webpage) that C# error handling can't "see" the error, etc. and that I should use old style error handling by checking @@Error and letting C# do most of the rest.  I'm told that if I don't use TRY CATCH then C# can "see" and handle the errors thrown by the stored procedure.

This does not quite smell right to me as the DBA.  So, how should error handling be done when C# calls a stored procedure?  Can you point me to a resource online?  I need to know how best to do the error handling in the page itself and in the stored procedure.

Thanks!

Eric

PS:  I have googled a lot but not found an article that addresses how to handle errors in the SP when the SP is called from C#.
LVL 2
Eric3141Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jens FiedererTest Developer/ValidatorCommented:
You can do all the error handling you want in the stored procedure, but if you want to throw an error to the C# level, you can just use RAISERROR to accomplish that.
0
Eric3141Author Commented:
jensfiederer,

Thx for the feedback.  I have pasted the SP as it is right now.  This is how they have been coded in our shop to be used by ASP.net.  Here I have coded it to write 2 entries to the db error log file.  Which of the entries will ASP.net be able to "see" / handle?  The last one?

Is there any particular advantage to leaving it as it's coded here?  The programmer who used to be here seemed to think so, however, I am skeptical.
ALTER PROCEDURE [dbo].[uspCaseActionHistoryDelete]
	--input parameters
	@Original_HistoryID int

AS
BEGIN
	SET NOCOUNT OFF;

	BEGIN TRANSACTION DeleteTrans

		DELETE FROM History
		WHERE HistoryID = @Original_HistoryID;
		
		IF @@Error != 0
			-- Whoops, there was an error:
			BEGIN
				ROLLBACK TRANSACTION DeleteTrans
				 
				RAISERROR ('CaseMgt DB - stored proc = uspCaseActionHistoryDelete',16,10)WITH LOG
				RAISERROR ('Delete from CaseActionHistory table failed',16,10)WITH LOG

				RETURN
			END

	--If we reach here, success!
	COMMIT TRANSACTION DeleteTrans

END

Open in new window

0
Carl TawnSystems and Integration DeveloperCommented:
In that specific scenario your C# app would receive both errors concatenated into a single exception.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Eric3141Author Commented:
OK, do either of you know of an example (or a location on the web with one) of how to use what RAISERROR sends from the SP to ASP?

We execute SP via ObjectDataSource -- do I check for the error (from RAISERROR) in the ODS_INSERTED event handler?  

I'm sorry, but I have no idea how to do this -- any resources online you can point me to?
0
Jens FiedererTest Developer/ValidatorCommented:
Never tried this with an OBJECT datasource  (are you sure it's not SqlDataSource? ) - but if the code that IMPLEMENTS that object  executes any commands, it might look like this:

            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString1"].ToString()))
            {
                con.Open();

                try
                {
                    SqlCommand c = con.CreateCommand();
                    c.CommandType = System.Data.CommandType.StoredProcedure;
                    c.CommandText = "jens_foo";
                    c.ExecuteNonQuery();
                }
                catch (SqlException exc)
                {
                    Console.WriteLine(exc.Message);
                }
            }
0
Carl TawnSystems and Integration DeveloperCommented:
You will need to use the event handler like you said.

The eventargs object passed to the Inserted event handler will contain an Exception object, you simply need to test if this is null:


        protected void ObjectDataSource1_Inserted(object sender, ObjectDataSourceStatusEventArgs e)
        {
            if (e.Exception != null)
            {
                // do something with the exception
            }
        }

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.