Exception Handling in ADO.NET


It's now time to talk about exception handling in ADO.NET. Each data provider provides three classes: Exception, Error, and ErrorCollection. For example, the SQL data provider provides the SqlException, SqlError, and SqlErrorCollection classes. You'll look at the ADO.NET-related classes and then you'll learn how to implement a few of them in your code.

If you look at the System.Data class hierarchy, you'll find many exception classes. These exception classes are accessible to all data providers through the System.Data namespace and the classes defined in this namespace. Table 9-2 briefly describes these classes.

Table 9-2: The System.Data Exception Classes

CLASS

DESCRIPTION

ConstraintException

This class represents an exception thrown when attempting an operation that violates a constraint.

DataException

This class is a base class for many database-related exception classes such as ContraintException, NoNullAllowException, and MissingPrimaryKey-Exception. It's thrown when an error is generated using ADO.NET.

DBConcurrencyException

This exception is a result of the DataAdapter's Update method when the affected rows after an Insert, Update, or Delete operation are zero because of the concurrency violation.

DeleteRowInaccessibleException

This exception is thrown when a program tries to delete a DataRow that has already been deleted.

DuplicateNameException

This exception is thrown when a program tries to add a duplicate database object such as a table, relation, column, or a constraint to the database schema.

EvaluateException

This exception is thrown when a program isn't able to evaluate the Expression property of a DataColumn object.

InRowChangingEventException

This exception is thrown when calling the EndEdit method within the RowChanging event.

InvalidConstraintException

This exception is thrown when a program tries to create an incorrect relation.

InvalidExpressionException

This exception is thrown when attempting to add a DataColumn with an invalid Expression property value to a DataColumnCollection.

MissingPrimaryKeyException

This exception is thrown when trying to access a row in a table that has no primary key.

NoNullAllowedException

This exception is thrown when a program tries to insert a null value where the DataColumn's AllowDBNull property is False.

NoNullAllowedException

This exception is thrown when a program tries to change the value of a read-only column.

RowNotInTableException

This exception is thrown when a program tries to perform an operation on a row that doesn't belong to a table.

StrongTypingException

This exception is thrown by a strongly typed DataSet when the user accesses a DBNull value.

SyntaxErrorException

This exception is thrown when the Expression property of a DataColumn contains a syntax error.

TypedDataSetGeneratorException

This exception is thrown when a name conflict occurs while generating a strongly typed DataSet.

VersionNotFoundException

This exception is thrown when a program tries to return a version of a DataRow that has been deleted.

Recognizing SQL Server 2000 Errors

Whenever you run an invalid SQL query, SQL Server returns a formatted error message. Also, SQL Server writes these error messages to the error log and/or event log. For instance, if you select records from a database table that doesn't exist, SQL Server will return an error message including the message ID, severity level, error state, line number, and the error message. For example, if you execute the following SQL query:

 select * from SomeTable 

but SomeTable doesn't exist in the database, then SQL Server will return an error that looks like the following:

 Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'SomeTable'. 

The first part of the error is the error message number. Each error message has an associated error message number that uniquely identifies the error. Error messages number from 1 to 49,999 are reserved for SQL Server. You can also define your own custom error messages, but your message number must be fall between 50,000 and 2,147,483,647.

The second part of the message is the error severity level, which falls between 0 and 25. The error severity represents the nature of the error. Table 9-3 describes the severity levels.

Table 9-3: Severity Levels

RANGE

DESCRIPTION

0–10

These messages are not actual errors. These messages are used only for informational purposes.

11–16

Severity levels 11–16 are generated because of user problems and can be fixed by the user. For example, the error message returned in the invalid Update query, used earlier, had a severity level of 16.

17

SQL Server has run out of a configurable resource, such as locks.

18

Represents nonfatal internal software problems.

19

A nonconfigurable resource limit has been exceeded.

20

Represents a problem with a statement issued by the current process.

21

SQL Server has encountered a problem that affects all the processes in a database.

22

Represents a table or index that has been damaged.

23

Represents a suspect database.

24

Represents a hardware problem.

25

Represents some type of system error.

Note

Severity level errors that fall between 19 and 25 are fatal errors and can only be used via RAISERROR by members of the fixed database role sysadmin with the with log option required.

The next part of SQL Server error message is the state number, which varies from 1 to 127; it represents information about the source that issued the error.

The line portion of the error message represents the source line where the actual error occurred.

The last part of the error message is the error description. The SQL Server error messages are stored in the sysmessages system table. To read all the messages from this table, you can run the following SQL query in the Query Analyzer:

 select * from sysmessages 

Using SQL Server Error Logs

SQL Server maintains an error log (in text format) that stores the server information and error messages. Error log files can help you track down problems or alert you to potential or existing problems. The SQL Server log files are stored in the LOG folder of the SQL Server home folder. This folder has more than one log file (usually seven), and the first of them is ERRORLOG. By default there's no extension to these files. To see their contents, you need to add a .txt extension to the file. Note that you may not be able to change the name of the log file if SQL Server is running. So, stop SQL Server before modifying or reading the log file. The partial contents of ERRORLOG looks like the following:

 2002-10-27 13:32:00.57 server Microsoft SQL Server  2000 - 8.00.194 (Intel X86) Aug  6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Personal Edition on Windows NT 5.0 (Build 2195: Service Pack 3) 2002-10-27 13:32:00.79 server Copyright (C) 1988-2000 Microsoft Corporation. 2002-10-27 13:32:00.79 server All rights reserved. 2002-10-27 13:32:00.79 server Server Process ID is 596. 2002-10-27 13:32:00.79 server Logging SQL Server messages in file 'D:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'. 2002-10-27 13:32:00.97 server SQL Server is starting at priority class 'normal'(1 CPU detected). 2002-10-27 13:32:02.43 server  SQL Server configured for thread mode processing. 2002-10-27 13:32:02.57 server Using dynamic lock allocation. [500] Lock Blocks, [1000] Lock Owner Blocks. 2002-10-27 13:32:02.95 spid3 …… 

SQL Server also adds error messages to the Windows NT event log. You can use the Windows NT Event Viewer to view the error messages. The Windows NT Event Viewer is located in the Windows NT Administrative Tools group.

Using the SqlException Class

The SqlException class is thrown when SQL Server returns a warning or error. The SqlException class is inherited from the SystemException class, which in turn is inherited from the Exception class. Besides the Exception class members, the SqlException class defines the properties described in Table 9-4.

Table 9-4: The SqlException Class Properties

PROPERTY

DESCRIPTION

Class

Returns the severity level of the error.

Errors

Returns a collection of one or more SqlError objects. This property is used when you need to collect more information about the errors generated by the Sql data provider.

LineNumber

Returns the line number within the SQL command or stored procedure that generated the error.

Message

Represents human-readable text of the error.

Number

Returns a number that identifies the type of error.

Procedure

Represents the name of the stored procedure that generated the error.

Server

Represents the name of the computer running SQL Server.

Source

Returns the name of the provider.

State

Numeric error code from SQL Server that represents an error or warning.

OK, now you'll see the SqlException class in action. Listing 9-7 connects to a SQL Server called MCB, reads data from the Customers table, and then fills a DataSet.

Listing 9-7: Reading Data from a SQL Server

start example
 ' Create a Connection Object     Dim ConnectionString As String ="Integrated Security=SSPI;" & _          "Initial Catalog=Northwind;Data Source=MCB;"     Dim conn As SqlConnection = New SqlConnection(ConnectionString)     Dim SQL As String = "SELECT * FROM Customers"     Dim adapter As SqlDataAdapter = New SqlDataAdapter(SQL, conn)     Dim ds As DataSet = New DataSet()     Try       ' open a connection       conn.Open()       adapter.Fill(ds, "Customers")     Catch exp As SqlException       ' Generate error message       Console.WriteLine("Error:" + exp.Message & _       ", Number:" + exp.Number.ToString() & _       ", Line Number: " + exp.LineNumber.ToString() & _       ", Server:" + exp.Server.ToString() & _        ", Source:" + exp.Source.ToString())     Finally       ' Close the connection       If conn.State = ConnectionState.Open Then         conn.Close()       End If       ' Dispose the connection       If (Not conn Is Nothing) Then         conn.Dispose()       End If     End Try 
end example

Now change the SQL Server name to MCB1. The connection string looks like following:

 Dim ConnectionString As String = "Integrated Security=SSPI;" & _          "Initial Catalog=Northwind;Data Source=MCB1;" 

If you run the code after changing the SQL Server name, it generates an error that looks like Figure 9-4.

click to expand
Figure 9-4: SqlException messages for SQL Server not found

Now let's switch the SQL Server to MCB and change the SQL string to the following:

 Dim SQL As String = "SELECT * FROM Test" 

The output looks like Figure 9-5.

click to expand
Figure 9-5: SqlException messages when a database table not found

Using the SqlError and SqlErrorCollection Classes

The SqlException.Errors property returns an object of SqlErrorCollection class. This object is a collection of SqlError objects, each of which encapsulates an error or warning by SQL Server. The Count property of SqlErrorCollection returns the total number of errors in a collection, and the Item property returns an error at the specified index.

The Class property of SqlError represents the severity level of the error, which varies from 0 to 25. Even using the severity level, you can notify a user of the results of the error.

Messages with a severity level starting from 0 to 10 are informational and indicate a nonfatal error. Severity levels from 11 to 16 indicate a user error that can be corrected by the user. Severity levels from 17 to 25 indicate software or hardware errors, but the program can continue working. Severity level 20 or more indicates serious fatal errors and may cause damage.

Listing 9-8 uses the SqlError and SqlErrorCollection classes and handles the errors generated by the Sql data provider.

Listing 9-8: Using the SqlError and SqlErrorCollection Objects

start example
 Imports System.Data.SqlClient Module Module1   Sub Main()     ' Create a Connection Object     Dim ConnectionString As String = "Integrated Security=SSPI;" & _          "Initial Catalog=Northwind;Data Source=MCB;"     Dim conn As SqlConnection = New SqlConnection(ConnectionString)     Dim SQL As String = "SELECT * FROM Test"     Dim adapter As SqlDataAdapter = New SqlDataAdapter(SQL, conn)     Dim ds As DataSet = New DataSet()     Try       ' open a connection       conn.Open()       adapter.Fill(ds, "Customers")     Catch exp As SqlException       ' Generate error message     Dim errs As SqlErrorCollection = exp.Errors     Dim i As Integer     Dim err As SqlError     For Each err In errs       Console.WriteLine("Error #:" & i.ToString() & _               ", Class:" & err.Class.ToString() & _               ", Line Number :" & err.LineNumber & _               ", Message:" & err.Message & _               ", Source:" & err.Source & _               ", Server:" & err.Server)     Next    Finally       ' Close the connection       If conn.State = ConnectionState.Open Then         conn.Close()       End If       ' Dispose the connection       If (Not conn Is Nothing) Then         conn.Dispose()       End If     End Try   End Sub End Module 
end example




Applied ADO. NET(c) Building Data-Driven Solutions
Applied ADO.NET: Building Data-Driven Solutions
ISBN: 1590590732
EAN: 2147483647
Year: 2006
Pages: 214

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net