We help IT Professionals succeed at work.

SQL Exception in C# .NET  2005

583 Views
Last Modified: 2012-05-11
Hello Experts,

I'm currently working on SQLMethod to do data retrieval from SQL database. how can I determine whether received exception is SQL Exception or general exception.


Code:
-----
try
{

.................................

}
catch (System.Data.SqlClient.SqlException sqlEx)
            {
                if (sqlEx.Class == 14 && sqlEx.Number == 2627)
                {
                    //Duplicate Record
                    return 1;
                }

                else
                {
                   
                    return 0;
                }
            }


How can catch regular exceptions like DBConnection failed,Stored procedure doesn't exists,....

 
Comment
Watch Question

Top Expert 2011

Commented:
The conditions you mentioned (DB Connection failed, Store Procedure doesn't exist) will raise SqlException.  You need to inspect the exception object (Class and Number properties) to determine the underline problem.

For example:

Class=20; Number=-1; The server was not found
Class=11; Number=4060; Cannot open database
Class=16; Number=2812; Could not find stored procedure

I hope this helps.

Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
jeebu,

I tried your Logic, but the Exception "hit" was SQL SqlException  and there is no Message:But it has DBException.

And it did not "hit" catch (Exception ex), since I was returning value in the First SQL Exception block. I need to catch exception, if it is an SQL Exception(since I'm looking for Duplicates) then Ignore this messages and move forward. Else if it is Normal exception I said earlier can be anything overflow, string mismatch,no stored procedure found,conn failed,...etc. It should show that exception.

Thanks.
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks!

Author

Commented:
Wdosanjos,

In my case, can I do this:

catch (System.Data.SqlClient.SqlException sqlEx)
{

try
{
if (sqlEx.Class == 14 && sqlEx.Number == 2627)
    {
        //Duplicate Record
        return 1;
    }

     throw sqlEx;
}
catch (Exception ex) {
 }

}



can I do try catch within Catch Exception handling???  since when "throw"  it is out of scope and go to next level of code.

Author

Commented:
I tried this:

catch (System.Data.SqlClient.SqlException sqlEx)
{

try
{
if (sqlEx.Class == 14 && sqlEx.Number == 2627)
    {
        //Duplicate Record
        return 1;
    }

     throw sqlEx;
}
catch (Exception ex) {
 }

}

It works, is it a good way of programming....
Top Expert 2011

Commented:
Yes, that's good practice.  I'd add a finally block for any clean up.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*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.