• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 534
  • Last Modified:

SQL Exception in C# .NET 2005

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

 
0
ASPDEV
Asked:
ASPDEV
  • 4
  • 3
2 Solutions
 
wdosanjosCommented:
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.

0
 
jeebukarthikeyanCommented:
hi,

i think ypu can writre in this way

Try {
 }
catch (SqlException Ex) {
  }
catch (Exception ex) {
 }


Ex will hold 

"Message: " +  Ex.Errors[i].Message + "\n" +
"LineNumber: " +  Ex.Errors[i].LineNumber + "\n" +
                          "Source: " +  Ex.Errors[i].Source + "\n" +
                          "Procedure: " +  Ex.Errors[i].Procedure + "\n";

Open in new window



jeebu
0
 
ASPDEVAuthor 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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
wdosanjosCommented:
@ASPDEV, what do you want to do once you detect one of those conditions (connection failed, stored proc missing, etc)?

I suggest that you just rethrow the exception:

try
{

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

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

     throw sqlEx;
}

Open in new window

0
 
ASPDEVAuthor Commented:
Thanks!
0
 
ASPDEVAuthor 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.
0
 
ASPDEVAuthor 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....
0
 
wdosanjosCommented:
Yes, that's good practice.  I'd add a finally block for any clean up.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now