Hitchhikers Guide to Visual Studio and SQL Server: Best Practice Architectures and Examples, 7th Edition (Microsoft Windows Server System Series)

One of the most sophisticated parts of any serious application is the exception handlers. Since connecting to any external device is fraught with potential issues, your code must often set up a "state machine" to decide what to do. In any case, any production code should include a TRy/Catch exception handler to trap the exceptions that can occur.

One thing you shouldn't do is pass the responsibility of handling exceptions to your customersthe end-user. These folks are the least likely to be able to help the situation, so presenting them with an ADO.NET exception message just because your server is bogged down is just laziness and unprofessional.

The complexity of getting connected and having to deal with the issues yourself does not make your job any easier. This means you're going to have to:

• Determine the current application state. That is, what were you trying to do when the exception occurred? If you're in the process of opening your first connection, the list of potential problems is far longer than if this is the 99th time you've connected.

• Determine whether you have connectivity. Is the network card, the network, and all of the Ethernet (or whatever) hardware between you and the server working?

• Determine whether the server hosting SQL Server is up and visible. Has the server been taken down for maintenance or stolen in the night? Is it overloaded and unable to accept more work?

• Determine whether the SQL Server instance you're addressing is installed and running. Are you accessing the primary or failover server? Remember, you must include an instance name in the Data Source/Server keyword if you aren't addressing the default instance.

• Determine whether the SQL Server login accounts are set up to permit you to access the server and initial catalog database. While members of the Administrators Windows group have rights to access the server as SA, members of the Users group or other groups don't have rights by default. If you're using ASP applications, have you correctly configured the ASPNET account?

• Determine whether the Login credentials are currentyour password might have expired and you might need to use the ChangePassword method.

• Determine whether the SQL Server instance is configured for SSPI or "mixed" SQL Server security. In any case, you still need to set up login accounts for the credentials you're using, unless you're a member of the Administrators group.

• Determine whether this is first time the exception has occurredthat is, have you already retried the operation? If your connection fails because of a bad connection in the pool, you might need to flush the pool before retrying the connection.

• Determine whether the exception is caused by a full or overloaded connection pool. I've just spent a dozen pages discussing this possibility. You know what to do to fix the problem.

• Determine whether the file you're trying to attach is in an "attachable" state. Simply copying an .MDB file from one server to another does not mean the file can be attached. Make sure the .MDB file has been properly detached from the source server before trying to install it elsewhere.

• Determine whether the firewall is preventing access to the SQL Server ports TCP 1433 and UDP 1434, and port 49 for Named Pipes. Is SQL Server configured to use these ports and the needed protocols? Remember, setup disables these ports and the external protocols by default.

• Determine the load on the server. Have you exhausted the number of available resources on the server? Remember that SQL Server can be configured to limit the number of inbound connections. If the host server or SQL Server is too busy, it might take (far) longer to connect. Is the host server being used to host Reporting Services, Indexing Services, or a print server? These services can consume a great deal of processing resources and memoryyou might have consumed all that the server has available.

• Determine whether you're using MARS. If you've left MultipleActiveResultSets enabled, you'll discover a number of obscure exception conditionssome of which occur after only nine connections have been made.

Sourcing Exceptions

When connecting, you're generally going to experience at least two types of exceptions. While I assume you know how to write basic exception handlers, let's review a few points. First, I won't discuss "unmanaged" error handlers, which are provided in Visual Basic .NET (but not C#) for backward-compatibility with Visual Basic 6.0.

Exception handlers consist of a try statement followed by the code to monitor for exceptions, one or more Catch statements that mark the end of the monitored code block, and the beginning of the "exception handler" Catch block(s). The Catch statement specifies the Exception class to trap. Remember that the Exception class is the "base" class and should trap all exceptionswell, almost. After a while, you'll discover that if you use Windows controls to handle data, these controls can throw exceptions as well, and your exception handler won't be able to trap them. After the Catch blocks, you can add an optional Finally block that's executed whether or not there is an exception to catch. This is a great place to put the Close method for the SqlConnection to make sure it's closedwhether or not there is an exception during its use.

• SqlException: After the ConnectionString has been accepted and ADO.NET attempts to open the SqlConnection, you can expect a SqlException to fire if the server is not running, if the server is not configured correctly, or if some other server-related issue occurs. I examine this class more closely later in this section.

• (General) Exception: If you pass invalid settings to the SqlConnectionStringBuilder, or when declaring the SqlConnection object with a fleshed-out ConnectionString, ADO.NET throws a general Exception. Your Exception handler Catch routine needs to trap more specific exceptions first (as shown in Figure 9.35). Remember that (virtually) all exceptions are caught by the Catch Exception routine.

  Figure 9.35. Using multiple Catch statements to trap SqlExceptions.

Understanding the SqlException Class

The SqlClient .NET Data Provider is responsible for implementing its own "exception" class based on Exception. This implementation exposes SQL Server-specific information about went wrong. For example, a SqlException exposes the Errors collection of SqlError objects that provide even finer granularity to SQL Server exception reporting. Table 9.6 describes the SqlException properties.

Note that the Errors property returns a collection of SqlError objects. The SqlError class also contains a number of useful properties that are a subset of the SqlException properties, as indicated in Table 9.3. The example shown in Figure 9.36 illustrates a (very) simple SqlException handler to dump the returned error information to the debug window. When this code runs, the PRINT message is suppressed because of the RAISERROR executions. The SqlError messages are returned in "reverse" or "inside-out" order. That is, the "high-severity" message is returned first, followed by the "low-severity" message.

Dealing with Exceptions

Okay, something went wrong. What can/should you do about it? I can offer a few suggestions:

• Trap it. Each and every application you write needs to ensure that all exceptions are trapped and handled one way or another. You do not want your users to be frightened out of their wits by scary .NET exception dialogs.

• Report it. If the exception (even a minor exception) is logged, you'll be able to know how often it occurs and get a better handle on how to discover what's causing it. If it occurs each morning between 8 and 8:15, it might be the time the clerk plugs in the coffee maker into the outlet shared by the server. I often record exceptions to the Windows events log or a custom log file maintained for the application.

• Pass it up. You might find it necessary and expedient to simply pass the exception to the calling routine. You can set the InnerException of a new Exception that you fire.

• Retry it. If you're dealing with a transitory issue, waiting a bit and retrying might be your best choice. The problem is that unless you do some exploring, you won't know the source of the problem or whether it's worth waiting for it to resolve itself.

• Root it out. Connectivity issues are hard to diagnose, but there are a number of ways to isolate the problem. Try to connect on the host server with SQLCMD using the credentials your application is using. If you're using SSPI connectivity, the credentials used on the host server might not be the same as those being used by the client system. See if you can ping the server and use Telnet to probe the ports. See if other applications like SQLCMD can access the server. Can the SQL Server tools like SQL Server Management Studio or SQL Express Manager connect to the server?

• Repair it. If your connection string is incorrect or you haven't set up the accounts correctly, you'll need to correct these settings before retrying. However, if the accounts that were working before are no longer working, you need to have a talk with the DBA. If you're the DBA, it's still your fault unless you gave away the SA password (then it's still your fault). Do some hardware tracing to see if you have connectivity. Watch for changes in software or hardware firewalls.

• Ignore it. Yes, there are times when it makes perfect sense to ignore an exception and move on. As you progress through the chapters, you'll find many situations where stored procedures return low-severity messages that can be ignored or simply logged.

• Avoid it. It's an even better practice to code your application to prevent needless exceptions. Don't use Exception handlers as a back-channel communications methodthat's what the InfoMessage event is forit's simply too expensive.