Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ADO.Net error handling

Posted on 2010-09-17
6
Medium Priority
?
496 Views
Last Modified: 2013-12-16
I am looking for some advice or guidance on catching errors when working with ADO.net and SQL server.  Most examples and books, show code like

try
{
}
catch (Exception ex)
{
}

That works fine, however, I have never liked catching "Exception".  What is the best practice when catching exceptions in regard to ADO.Net?  
0
Comment
Question by:shanemay
[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
6 Comments
 
LVL 41

Expert Comment

by:guru_sami
ID: 33702953
you can catch provider specific exceptions like below:

try
{
  //Data access code
}
catch (SqlException sqlex) // more specific
{
}
catch (Exception ex) // less specific
{
}
finally{
 //if you are not having using construct don't forget to close connection and reader objects here
}

Check Exceptions handling here: http://msdn.microsoft.com/en-us/library/ee817654.aspx
0
 
LVL 4

Expert Comment

by:thepanch
ID: 33702965
i suggest you to throw the exception on the main class and then in the view catch it like this

public class clAlumno
{
 public DataTable ObtenerDataTableReporteAspirante(long ID_Alumno)
    {
        DataTable dt;
       
        try
        {
            oCon.OpenConnection();
            oCon.clearParameters();
            oCon.addParameter("@ID_Alumno", ID_Alumno);
            dt = oCon.getDataTableSP("ProcRptFichaAspirante");          
        }
        catch (Exception ex) { throw new Exception(ex.Message); }
        finally { oCon.CloseConnection(); }
        return dt;
    }
}

and then in the view, only do the try catch
like the code you show
0
 
LVL 16

Expert Comment

by:kris_per
ID: 33704286

Catch exception only if you have a purpose to do something in the catch. For example:

1. Catch exception in the ui/form code - for the Purpose of: showing the error message for example in a msg box to the user.

UI Code:
private void buttonCreate_Click(...)
{
      try
      {
           da.CreateCustomer(....);
       }
       catch(Exception ex)
       {
            MessageBox.Show(ex.Message);
        }
}

2. Catch exception in non-ui code for the Purpose of: wrapping the exception in an application specific exception class and rethrow. Because there can be different types of exception in the lower layer (for example Data Access layer) and top ui layer can not expect all the types of exception; instead if the data access layer throws a common application specific custom exception (e.g. OurDataAccessException), then ui layer can specifically catch this exception.

Data Access Code:

public bool CreateCustomer(...)
{
    try
    {
          // some sql operation here
    }
    catch(SqlException sqlEx)
    {
         // wrap the SqlException in our custom exception so that higher layer code can specifically catch our exception, because higher layer sometimes wont know what are all the lower layer exceptions it can expect.
           
         throw new OurDataAccessException("Error creating customer...", sqlex );
     }

}

3. Catch exception anywhere for the purpose of logging the exception and rethrow the same exception.

    try
    {
          // some sql operation here
    }
    catch(Exception ex)
    {
         LogException(ex)
         
         throw ex; // we wanted only to log; rethrow the same exception
    }

If there is no specific purpose, then dont catch; let it go to the higher level code....

0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 9

Expert Comment

by:Rahul Goel ITIL
ID: 33707595
You can use Enterprise Library in .Net Handling...and you can define the policies for the SQL Exceptions.
Using this framework you can wrap the SQL Exception or any unhandled exception into a custom Exception without wring single line of code.
http://entlib.codeplex.com 
 
0
 
LVL 13

Accepted Solution

by:
Naman Goel earned 2000 total points
ID: 33707852
I am also not in favor of catching exception but we have to handle some of the sql exception like connection problems and report the same to user.

Use error code to handle different type of error which will be returned by Sql server

use this type of exception handling



try
{
// some ado.net code
}
catch(SQLException e)
{
     ReformatSQLException(e, serverName, databaseName)
}

 /// <summary>
    /// Class for Custom exception messages
    /// </summary>
	public class CustomException : Exception
	{
		public string Details { get; private set; }

		public CustomException(string customMessage, string details) :
			base(customMessage)
		{
			Details = details;
		}

	}

	public static Exception ReformatSQLException(SqlException sqlException, string serverName, string repositoryName)
		{
			string customMessage = String.Empty;

			switch (sqlException.Number)
			{
				case SqlExcepetionNumbers.CannotOpenDB:
					customMessage = String.Format(!String.IsNullOrEmpty(repositoryName) ? Localized.CannotOpenDB : Localized.CannotOpenDB2, serverName, repositoryName);
					break;

				case SqlExcepetionNumbers.NetworkDown:
				case SqlExcepetionNumbers.NetworkDown2:
				case SqlExcepetionNumbers.NetworkDown3:
				case SqlExcepetionNumbers.SemaphorError:
					customMessage = String.Format(!String.IsNullOrEmpty(repositoryName) ? Localized.SqlServerNetworkDown : Localized.SqlServerNetworkDown2, serverName, repositoryName);
					break;

				case SqlExcepetionNumbers.SqlServerServiceStopped:
					customMessage = String.Format(!String.IsNullOrEmpty(repositoryName) ? Localized.SqlSeverServiceDown : Localized.SqlSeverServiceDown2, serverName, repositoryName);
					break;

				case SqlExcepetionNumbers.SqlServerServicePaused:
					customMessage = String.Format(Localized.SqlServerServicePaused, serverName);
					break;

				case SqlExcepetionNumbers.LoginFailureDomainController:
					customMessage = String.Format(!String.IsNullOrEmpty(repositoryName) ? Localized.DomainControllerCannotBeReached : Localized.DomainControllerCannotBeReached2, serverName, repositoryName);
					break;

				case SqlExcepetionNumbers.ConnectionTimeout:
					customMessage = String.Format(!String.IsNullOrEmpty(repositoryName) ? Localized.ConnectionTimeOutExpired : Localized.ConnectionTimeOutExpired2, serverName, repositoryName);
					break;

				case SqlExcepetionNumbers.AccessDenied:
				case SqlExcepetionNumbers.LoginFailure:
					customMessage = String.Format(!String.IsNullOrEmpty(repositoryName) ? Localized.SqlServerAccessDenied : Localized.SqlServerAccessDenied2, serverName, repositoryName);
					break;

				case SqlExcepetionNumbers.TransportLevelError:
				case SqlExcepetionNumbers.TransportLevelError2:
					customMessage = String.Format(!String.IsNullOrEmpty(repositoryName) ? Localized.SqlServerTransportLevelError : Localized.SqlServerTransportLevelError2, serverName, repositoryName);
					break;

				case SqlExcepetionNumbers.MSDTCUnavailable:
					customMessage = String.Format(Localized.SqlServerMSDTCError, serverName);
					break;

				case SqlExcepetionNumbers.LinkedServerError:
					customMessage = String.Format(Localized.SqlServerLinkedServerError, serverName);
					break;
			}

			if (customMessage == String.Empty)
			{
				return sqlException;
			}

			return new CustomException(customMessage, sqlException.Message + Environment.NewLine + sqlException.StackTrace);
		}

Open in new window

0
 

Author Closing Comment

by:shanemay
ID: 33715957
Thank you for your help.  It is greatly appreciated.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
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…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

705 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