Provider Functionality

for RuBoard

In this section, we'll explore each of the provider components shown in Table 8.1 and look at their functionality at a high level by detailing the methods , properties, and events that are typically exposed by a provider.

Data Adapter

The data adapter is the bridge between the DataSet and the data store and, as such, is the most important component for providers that want to implement the disconnected programming model for which ADO.NET was primarily built. In fact, vendors who want to create the simplest provider possible can simply implement (inherit from) the IDataAdapter interface or derive from the DataAdapter class.

You'll notice that several different base classes and interfaces are shown in Figure 8.1. DataAdapter is an abstract class that implements the IDataAdapter interface and provides the basic functionality that all providers inherit from. The DbDataAdapter class is also abstract and is derived from DataAdapter to provide the basis for data adapters that work with relational databases. The IDbDataAdapter interface implements IDataAdapter with the addition of properties to refer to select, insert, update, and delete the commands. The result is that providers such as SqlClient and OleDb create their data adapters by inheriting from DbDataAdapter , implementing the IDbDataAdapter interface, and adding custom and overloaded members to implement their own functionality and provide strongly typed access. For example, the SqlDataAdapter class adds its own constructors that accept SqlConnection objects and a strongly typed implementation of the select, insert, update, and delete command properties. Table 8.2 lists the primary members for a data adapter.

Table 8.2. Data adapter members. These are primary members implemented by providers.

Member	Description
	Properties
AcceptChangesDuringFill	Property inherited from DataAdapter that specifies whether AcceptChanges is called on a row after it is added to a table.
ContinueUpdateOnError	Property inherited from DataAdapter that specifies whether to generate an exception when an error occurs during the Update method or whether to add error information to the row and continue.
SelectCommand , DeleteCommand , InsertCommand , UpdateCommand	Properties that reference strongly typed command objects used by the data adapter to manipulate data in the data store.
MissingMappingAction	Property inherited from DataAdapter that specifies the action to take when incoming data does not have a matching table or column in the DataSet during the Fill method.
MissingSchemaAction	Property inherited from DataAdapter that specifies the action to take when the schema of the DataSet used in the Fill method does not match the incoming data.
TableMappings	Property inherited from DataAdapter that references a DataTableMappingCollection object that stores the mappings between the incoming data and a DataSet .
	Methods
Fill	Overloaded and overridden method from DataAdapter and DbDataAdapter that populates a DataSet with data from the data store based on SelectCommand .
FillSchema	Overloaded method inherited from DbDataAdapter that adds a table to a DataSet and configures the schema to match the data store.
GetFillParameters	Overridden method inherited from the DbDataAdapter class that returns an array of parameters set by the user in the SelectCommand object.
Update	Overloaded method inherited from DbDataAdapter that calls the insert, update, and delete commands appropriately to modify the data store based on row states in the DataSet , DataTable , or DataRow passed to the method.
	Events
FillError	Event implemented by DbDataAdapter that is raised when an error occurs in the Fill method.
RowUpdated	Event raised during the Update method after an update command has been executed.
RowUpdating	Event raised during the Update method before an update command is executed.

Connection

Providers implement connection classes by implementing the IDbConnection interface and then adding their own custom members that provide provider-specific functionality, such as specifying additional attributes that affect the way the connection operates or implementing events that are fired in order to communicate back from the data store. Table 8.3 lists the members of the IDbConnection interface.

Table 8.3. Connection members. These are primary members implemented by providers from the IDbConnection interface.

Member	Description
	Properties
ConnectionString	Property that gets or sets the string used to open a connection.
ConnectionTimeout	Property that specifies how long to wait while trying to open a connection before raising an exception.
Database	Property that specifies the name of the current database that the connection will use.
State	Property that gets the current state of the database using the ConnectionState enumeration.
	Methods
BeginTransaction	Overloaded method that begins a new transaction on the connection for the data source. Providers return a strongly typed transaction object.
ChangeDatabase	Method that changes the current database for an open connection.
Close	Method that closes the connection to the database.
CreateCommand	Method that creates and returns a command object associated with the connection.
Open	Method that opens a connection using the attributes of the ConnectionString property.

As mentioned, in addition to the fundamental members shown in Table 8.3, providers implement specific members as well. For example, the OleDbConnection class includes additional Provider , DataSource , and ServerVersion properties that specify the OLE DB provider in use, the location and filename of the data source, and the version of the database server that is being used, respectively. In addition to those, the SqlConnection class adds PacketSize and WorkstationId to get the size of the network packets being exchanged between the client and SQL Server and the name of the client machine. Both providers also implement the InfoMessage and StateChange events in order to receive messages from the database and be alerted when the state of the connection changes.

Transaction

The transaction class implements the transaction semantics for a particular instance of a connection object. In other words, if the connection object's BeginTransaction method is called, a new transaction object is created that controls how the transaction behaves and when it is committed or rolled back. Providers implement the transaction class by inheriting from IDbTransaction , whose members are shown in Table 8.4.

Table 8.4. Transaction members. These are primary members implemented by providers from the IDbTransaction interface.

Member	Description
	Properties
Connection	Property that references the connection object that created the transaction. Providers implement a strongly typed version.
IsolationLevel	Property that specifies the locking behavior for the transaction using the IsolationLevel enumerated type.
	Methods
Commit	Method that commits the database transaction. Should throw an exception if the transaction has already been committed or rolled back.
Rollback	Method that rolls back or un-does a transaction after BeginTransaction has been called. Should throw an exception if the transaction has already been committed or rolled back.

You should notice from Table 8.4 that the programming model for transactions in ADO.NET implies an unchained model where, unless the BeginTransaction method is called, each individual command that is executed against a database is an implicit transaction. Commands can then be aggregated into their own logical unit of work explicitly using the BeginTransaction , Commit , and Rollback methods.

Permissions

Providers have the option of creating specific permission classes that work with .NET's Code Access Security (CAS) to enable administrators and developers to control access to the data store. Although a complete discussion of CAS is beyond the scope of this book, the basic idea is that CAS controls access to protected resources such as databases, the file system, the system registry, printing, and others by providing the following:

• Permissions and sets of predefined permissions ( FullTrust , Internet , and Nothing , among others) that represent high-level and granular access to various system resources

• The capability of users and administrators to set policies in one of three levels (Enterprise, Machine, User) that define the code groups ( code groups are logical groupings that contain permission sets and into which code is placed at runtime based on a membership condition) and permission sets

• The capability for code (developers) to request permissions (both declaratively and imperatively) it requires, would be nice to have, or does not require to run

• The capability of the common language runtime to determine, based on evidence, whether the code has the required permissions by their inclusion in a code group

A Quick CAS Overview

.NET's Code Access Security model relies on the concept of evidence to determine whether code has the required permissions for execution. Evidence includes the Authenticode signer of the code, the URL from where the code was downloaded, the strong name, the zone (such as the Internet Zone), and the application directory, in addition to optional custom attributes.

At load time, the common language runtime uses the evidence to determine which code groups at each policy level the assembly belongs to. After the common language runtime determines which code groups the code belongs to, it combines the permissions defined for each policy level. Finally, the common language runtime intersects the permissions from each policy level so that the resulting permission set contains only the permissions that all policy levels have in common. The set of permissions that has been calculated is compared to attributes defined in the code when it is loaded or executed, depending on where the permission is used, to determine which permissions are actually granted. If permissions that the code requires have not been granted, a SecurityException is thrown.

As an example, CAS allows administrators to edit the machine policy for a Web server to make sure that only code with specific evidence (for example, an assembly with a particular strong name or code signed with a digital signature) can access SQL Server by adding the SQL Server permission to the permission set used by the appropriate code group. This provides a much higher level of security than simply securing resources based on the identity of the account running the code.

Administrators modify policies, code groups, and permission sets through the Microsoft .NET Framework Configuration MMC snap-in found in the Administrative Tools group. Figure 8.2 shows a screenshot of the utility with a dialog showing the editing of the SqlClient permission for the permission set.

Figure 8.2. Editing a permission. This dialog can be used to edit the SqlClient permission for a particular permission set.

Note

By default, all code on the local machine runs under the FullTrust permission set, which allows unrestricted access to SQL Server. In other words, under the FullTrust permission set, all managed code can attempt to connect to a SQL Server by using the SqlConnection object. Of course, in order to actually gain access to the database server, the code must also supply the proper credentials.

Vendors implementing providers can create both declarative and imperative classes that can be used at the assembly, class, and method levels to make sure that the code has the appropriate permissions before accessing the data store. The abstract DbDataPermission class is used as the base class for imperative permission, whereas the DbDataPermissionAttribute class is the abstract base class for imperative permissions. The classes themselves are derived from CodeAccessPermission and CodeAccessSecurityAttribute of the System.Security namespace, respectively. The only member that DbDataPermission and DbDataPermissionAttribute add to the base classes is the AllowBlankPassword property. Setting this property to True allows the connection string to omit the password, whereas setting it to False makes sure that a password is used to connect to the data store.

To use CAS security declaratively, you can use attributes at the assembly, class, and method levels. For example, the declaration of a data access class that works against SQL Server might look like the following code snippet:

 <SqlClientPermissionAttribute(Security.Permissions.SecurityAction.Demand, _ AllowBlankPassword:=False)> _ Public Class ComputeBooksData : Inherits ComputeBooksDABase   ' members End Class 

In this snippet, the SqlClientPermissionAttribute is placed at the class level with its Action property set to Demand and AllowBlankPassword set to False . Setting Action to Demand simply means that code in this class (and any code higher in the call stack) needs to have permission to use SqlClient. Alternatively, the declaration could have used the SecurityAction.Assert value to indicate that only the Compu te BooksData class needs to be granted the permission. This is convenient because calling code won't necessarily be granted permission to access SQL Server. The SecurityAction enumeration also includes additional values to request permissions at the assembly level, deny a permission, and deal with permissions for derived classes.

Tip

Although using Assert sounds great (and unsecure), there is a catch. To assert permissions, the code (in this case, the assembly that Compu te BooksData is in) must have the assertion security permission (defined using the SecurityPermissionAttribute ). Assertion is included by default in the FullTrust , LocalIntranet , and Everything permission sets.

When a method contains a declarative demand for the SqlClientPermission and its assembly has not been granted the permission, the SecurityException is raised only if the method contains one of the objects from the SqlClient namespace, such as SqlDataAdapter or SqlConnection .

Permissions can also be used imperatively within a method like as shown in Listing 8.1.

Listing 8.1 Using imperative security. This method uses imperative security to demand the SqlClientPermission .

 Public Function GetTitles() as DataSet     Dim perm As New SqlClientPermission( _      PermissionState.Unrestricted, False)     Try       perm.Demand()       ' Go on to execute queries     Catch e As SecurityException       ' Handle security errors     Catch e As Exception       ' Handle other errors     End Try End Function 

In Listing 8.1, the permission object is explicitly created and its Demand method is called at the appropriate time. The constructor of the SqlClientPermission class accepts either the None or Unrestricted values from the PermissionState enumeration. Using None specifies that no access to the resource is required, whereas using Unrestricted means that the method requires full access to the resource. The second argument to the constructor populates the AllowBlankPassword property, so, in this example, blank passwords aren't allowed.

It's a good practice to at least declaratively mark your assemblies that contain data access code with the appropriate permission class so that any security exceptions can be found at load time rather than waiting until the method is called and finding that the assembly doesn't have the appropriate permissions. To do so, you can place the following line of code in the AssemblyInfo.vb (or .cs) file:

 <Assembly: SqlClientPermission(SecurityAction.RequestMinimum)> 

Note

When declaring attributes, the Attribute suffix is not required.

Command

The command object encapsulates a query or data modification to execute against a data store through a connection object. Command objects are also referenced in the SelectCommand , InsertCommand , UpdateCommand , and DeleteCommand properties of the data adapter used to fill a DataSet and synchronize it with the data store. If you've worked with ADO 2.x, the command objects exposed in ADO.NET will no doubt seem familiar because the Command object in ADO exposed many of the same properties.

Vendors implement command classes in a provider by inheriting from the IDbCommand interface and then adding custom members to expose specific functionality of the provider. The most common members of a command class are shown in Table 8.5.

Table 8.5. Command members. These are primary members implemented by providers from the IDbCommand interface.

Member	Description
	Properties
CommandText	Property that specifies the text of the command to run against the data store.
CommandTimeout	Property that specifies how long to wait before terminating the execution of a command.
CommandType	Property that specifies how the CommandText property is interpreted using a value from the CommandType enumeration.
Connection	Property that references the strongly typed connection object through which this command will be executed.
Parameters	Property that references a collection of strongly typed parameter objects associated with the command.
Transaction	Property that references the strongly typed transaction object the statement executed by this command will participate in.
UpdatedRowSource	Property that specifies how results from a command are applied to a row being updated using a value from the UpdateRowSource enumeration.
	Methods
Cancel	Method that attempts to cancel the execution of the command.
CreateParameter	Method that creates a new instance of a strongly typed parameter object. Does not automatically add the new parameter to the collection for the command.
ExecuteNonQuery	Method that executes the command against the data store and returns the number of rows affected, but not the rows themselves.
ExecuteReader	Method that executes the command against the data store and returns a strongly typed data reader that can be traversed.
ExecuteScalar	Method that executes the command against the data store and returns only the first column of the first row of the result set, regardless of how many rows and columns are specified by the CommandText .
	Methods
Prepare	Method that creates a compiled version of the query on the data store. This method is not a member of IDbCommand .
ResetCommandTimeout	Method that resets the CommandTimeout property to its default value.

As noted in Table 8.5, of the members listed, only the Prepare method isn't exposed by the IDbCommand interface, although it's implemented by both the SqlClient and OleDb providers. In addition, the SqlClient provider implements an ExecuteXmlReader method used to process results from queries that contain the FOR XML Transact -SQL keyword introduced in SQL Server 2000.

As with other provider-specific classes, members of the command object, including Connection , Transaction , Parameters , CreateParameter , and ExecuteReader , all return strongly typed objects rather than simply the interfaces in the System.Data namespace. For example, the declaration of the CreateParameter method of the OleDbCommand class looks like

 Public Function CreateParameter() As OleDbParameter 

rather than like this:

 Public Function CreateParameter() As IDbParameter 

In this way, the members are said to be strongly typed and so clients using the classes can always work with the types directly rather than only through the interfaces.

Note

Command builders are closely related to commands. We'll discuss them on Day 10, "Using Commands."

One of the other interesting members in Table 8.5 is the UpdatedRowSource property. This property can be set to one of the values of the UpdateRowSource enumeration ( Both , FirstReturnedRow , None , or OutputParameters ) to control how results from a command are mapped to a row in a DataSet . Both is the default and specifies that both output parameters and the first returned row are mapped to the changed row. In other words, when a data adapter executes the command referenced in its UpdateCommand property in order to update a row in a database, it will look for the values of output parameters (with the appropriate SourceColumn property set) and columns in a result set, if one is returned that can be placed back into the changed row. This allows the data store to resynchronize data generated on the server with data in the DataSet . The result is that the original row in the DataTable will have its RowState set to Unchanged if the update succeeds.

Data Reader

Data readers are used to expose the second major programming model in ADO.NET: streamed access to one or more result sets returned from a command object. Clients use a data reader by iterating over it in a loop using its Read method. In sharp contrast to the in-memory cache of a DataSet , data readers provide a forward-only read-only model where a row can be accessed only once and no navigation within the result set is possible. After the data reader has been exhausted, it can't be replayed using a second loop.

Note

Developers familiar with ADO 2.x will note that the data reader and the DataSet together provide most of the functionality of the Recordset object in ADO 2.x. In ADO 2.x, the Recordset could be used both to stream through data by using a forward-only, read-only cursor (cursor type of adOpenForwardOnly , lock type of adLockReadOnly ) and cache data using a disconnected Recordset (cursor location of adUseClient , cursor type of adOpenStatic , lock type of adLockReadOnly ).

A provider implements a data reader by creating a class that inherits from both the IDataReader and IDataRecord interfaces. The IDataReader interface provides the members used to provide information about the result set and iterate its rows. The IDataRecord interface provides the members used to read data from the current row in the result set.

Client code never actually creates a data reader (in fact, it can't because the constructor of the data reader is marked as private), but uses instances returned from the ExecuteReader method of the command object. The behavior of a data reader in terms of how rows are streamed to the client is totally dependent on the data store and provider. However, in most cases, as with SqlClient, the data is static after the command that creates the reader has executed, so changes made by other users are not visible.

The members typically implemented in a data reader are shown in Table 8.6 and are drawn from both the IDataReader and IDataRecord interfaces.

Table 8.6. Data reader members. These are primary members implemented by providers from the IDataReader and IDataRecord interfaces.

Member	Description
	Properties
Depth	Property from IDataReader that returns the nesting level of the current result set. Always zero for SqlClient but depends on the capability of the provider.
FieldCount	Property from IDataRecord that gets the number of columns in the current row.
IsClosed	Property from IDataReader that indicates whether the data reader is closed.
Item	Overloaded property from IDataRecord that gets the value of a column based on name or ordinal value in its native data type. In C#, this is the indexer to the data reader.
RecordsAffected	Property from IDataReader that returns the number of rows modified, inserted, or deleted by the command execution.
	Methods
Close	Method from IDataReader that closes the data reader.
GetByte , GetBytes	Methods from IDataRecord that get the value of the column as a Byte or a stream of bytes to be placed in an array based on the ordinal.
GetChar , GetChars	Methods from IDataRecord that get the value of the column based on the ordinal as a character or as stream characters to be placed in an array based on an offset.
GetDataTypeName	Method from IDataRecord that returns the name of the data type for the column specified by the ordinal.
GetBoolean , GetDateTime , GetDecimal , GetDouble , GetFloat , GetGuid , GetInt16 , GetInt32 , GetInt64 , GetString , GetTimeSpan	Methods from IDataRecord that return the value of the column as the appropriate .NET data type based on the ordinal.
GetFieldType	Method from IDataRecord that returns the Type of the field based on the ordinal value.
GetName	Method from IDataRecord that returns the name of the column based on the ordinal.
GetOrdinal	Method from IDataRecord that returns the ordinal of the column based on the name.
	Methods
GetSchemaTable	Method from IDataReader that returns a DataTable that mimics the structure of the current result set.
GetValue	Method from IDataRecord that returns the value of the column at the given ordinal.
GetValues	Method from IDataRecord that returns an array of objects for the columns in the current row in their native format.
IsDbNull	Method from IDataRecord that determines whether the column specified by the ordinal is equivalent to DBNull .
NextResult	Method from IDataReader that moves the data reader to the next result set returned from the execution of the command object.
Read	Method from IDataReader that advances the data reader to the next record in the result set.

As you can see from Table 8.6, the data reader provides strongly typed access to a result set by exposing the Get methods of the IDataRecord interface. These methods can be used both to retrieve the value of a single field (that is, GetString , GetInt32 , GetValue , and so on) or the values of the entire record using the GetValues method. Of course, specific providers such as SqlClient can augment this ability to return data in types that are particular to the data store. In fact, as mentioned on Day 1, ADO.NET contains the System.Data.SqlTypes namespace that includes classes that represent each of the native types in SQL Server. The SqlDataReader class then uses those types by exposing equivalent Get methods that return the types. For example, the SqlTypes namespace includes the types SqlBinary , SqlBoolean , SqlGuid , and SqlMoney , among others. The SqlDataReader class exposes GetSqlBinary , GetSqlBoolean , GetSqlGuid , and GetSqlMoney methods accordingly .

What Happened to GetRows ?

If you have developed in ADO 2.x in the past, you'll note that the data reader does not expose a method analogous to the GetRows method of the Recordset object. The GetRows method of the Recordset object reads the entire result set (or a specific number of rows) and creates a multi-dimensional Variant array before closing the Recordset . Developers used GetRows as an alternative to disconnected Recordset s to improve performance and eliminate COM marshalling and registration issues when passing data between tiers in a distributed application. However, because the DataSet provides the same functionality without the performance hit, the GetRows method is no longer needed. The bottom line is this: If you need to cache the data, use a DataSet .

The ability of data readers to work with multiple result sets is, of course, entirely dependent on the provider and the data store. In most cases, multiple result sets can be returned simply by concatenating SELECT statements in the CommandText property of the command object or in the stored procedure referenced by the CommandText property. You'll learn some specific techniques on Day 11, "Using Data Readers."

Parameter

Parameters are used to represent arguments passed to a command object and ultimately to a data store. Parameters are also used to map columns from a result set, return values, and output parameters back to columns within a DataSet . Providers typically implement strongly typed parameters by creating a class that inherits from both the IDataParameter and IDbDataParameter interfaces.

Note

Although most of the properties come from IDataParameter , the IDbDataParameter interface is used by the VB .NET Data Designers to indicate their precision, scale, and size.

To expose a strongly typed collection of parameters, the command object will then expose a collection object that inherits from the IDataParameterCollection interface. For example, the OleDb provider implements the OleDbParameter class that implements the IDataParameter and IDbDataParameter interfaces. A collection of OleDbParameter objects are then contained in an OleDbParameterCollection object exposed through the Parameters property of the OleDbCommand object.

The parameter objects themselves typically support the members shown in Table 8.7.

Table 8.7. Parameter members. These are primary members implemented by providers from the IDataParameter and IDbDataParameter interfaces.

Member	Description
DbType	Property from IDataParameter that specifies the data type of the parameter using the DbType enumeration.
Direction	Property from IDataParameter that specifies whether the parameter is input-only, output-only, bi-directional , or a return value using the ParameterDirection enumerated type.
IsNullable	Property from IDataParameter that specifies whether the parameter accepts null values.
ParameterName	Property from IDataParameter that specifies the name of the parameter.
Precision	Property from IDbDataParameter that specifies the precision (the maximum number of digits used to represent the value) of numeric parameters.
Scale	Property from IDbDataParameter that specifies the scale (the number of decimal places the value is resolved to) of numeric parameters.
Size	Property from IDbDataParameter that specifies the size in bytes of numeric parameters.
SourceColumn	Property from IDataParameter that specifies the name of the column in the DataSet the value of the parameter will be mapped to.
SourceVersion	Property from IDataParameter that specifies the DataRowVersion to use when populating the value of the parameter.
Value	Property from IDataParameter that specifies the value of the parameter.

In addition to the members shown in Table 8.7, the SqlClient and OleDb providers expose properties that return a value from the System.Data.SqlDbType and System.Data.OleDb.OleDbType enumerations, respectively. In both cases, the provider-specific types are linked to the DbType enumeration, so changing one of the properties for a particular parameter changes them both.

Note

A table of this linkage and how DbType maps to the .NET Framework types can be found in the online documentation by searching for the keywords "parameters, DataAdapter" in the index.

Parameters can be created using the CreateParameter method of the command object or directly using the New ( new in C#) keyword with its overloaded constructor. In both cases, the parameters must be added to the parameter collection using the Add method of the derived IDataParameterCollection object. The order in which parameters are added to the collection might also be important depending on the provider. Provider-specific characteristics also determine which values from the ParameterDirection enumeration ( Input , InputOutput , Output , or ReturnValue ) are valid to use with the Direction property.

The SourceColumn and SourceVersion properties are particularly interesting because they determine how the parameter maps to a DataSet and which version of the data is updated. For example, the SourceColumn property can be used to map a parameter that returns a new primary key value from a database server to the primary key column of a DataSet . This technique is often used with IDENTITY columns in SQL Server, as will be shown on Day 13, "Working with SQL Server." The SourceVersion property allows the UpdateCommand of a DataSet to use a value other than the Current value when performing an update. This can come in handy when you want to make sure that the Original value for a particular column is passed back to the data store.

Error and Exception

As noted in Table 8.1, ADO.NET does not provide templates for exposing errors and exceptions, and so each provider is free to implement them as it sees fit. However, the Microsoft providers handle errors in a similar fashion. When an error occurs in SQL Server or the data source that an OLE DB provider is communicating with, both providers create a strongly typed object that exposes properties that identify the error. In the case of SQL Server, the SqlError class exposes the properties shown in Table 8.8, whereas for OleDb, the properties of its OleDbError class are shown in Table 8.9.

Table 8.8. SqlError properties. Properties of the SqlError object that provide error information specific to SQL Server.

Property	Description
Class	Returns the severity level from SQL Server (1 to 25) with a default of 0. Levels above 20 are severe and usually close the SqlConnection object automatically.
LineNumber	Returns the line number in the Transact-SQL stored procedure or command batch that caused the error.
Message	Returns the text describing the error.
Number	Returns the SQL Server error number.
Procedure	Returns the name of the stored procedure or remote procedure call (RPC) that generated the error.
Server	Returns the name of the instance of SQL Server from where the error was generated.
Source	Returns the name of the provider that generated the error.
State	Returns a Byte value that corresponds to an error, warning, or no data found.

Table 8.9. OleDbError properties. Properties of the OleDbError class that provide information about the error that was found.

Property	Description
Message	Returns a text description of the error.
NativeError	Returns a database-specific error code.
Source	Returns the name of the provider that generated the error.
SQLState	Returns the five-character ANSI standard code representing the state of the database.

As you can see from Tables 8.8 and 8.9, there is virtually no overlap between the properties exposed by SqlError and OleDbError . This is the case because SqlError can expose very specific information from SQL Server, such as the LineNumber in the Transact-SQL command batch or stored procedure that caused the error or the name ( Procedure ) of the remote procedure call (RPC) or stored procedure that produced the error. The OleDbError object, on the other hand, exposes generic information such as the five-character ANSI code that represents the state of the database ( SQLState ) and data store “specific information in the NativeError property.

Both providers are capable of generating more than one error, so both implement collection classes to hold a collection of errors. The SqlErrorCollection and OleDbErrorCollection both inherit from the ICollection and IEnumerable interfaces and hold error objects of the appropriate type.

So, when an error occurs, the providers create an error object for each error returned from the data store and place it in the error collection object. The collection is then referenced through the Errors property of the SqlException or OleDbException object, which is then thrown by the provider. As a result, the errors collection will always be populated with at least one error object. As you might expect, the exception objects are derived from SystemException . In addition, the exception objects expose some of the same properties as the error objects, most of which are then populated with data from the first error in the errors collection. For example, the SqlException object exposes the Class , LineNumber , Number , Procedure , Server , Source , and State properties, all of which simply wrap the same property in the first SqlError object in the SqlErrorCollection . However, the Message property of both the SqlException and OleDbException classes automatically concatenates the Message properties from all the errors separated by carriage -return line-feeds.

To fully report the errors generated by the provider, you might use a method like that shown in Listing 8.2.

Listing 8.2 Logging error information. This method logs the errors reported through a SqlException to the Trace object.

 Public Sub LogSqlErrors(ByVal myException As SqlException)   Dim sqlE As SqlError   Dim strMsg As String   ' Write the header and stack dump   Trace.WriteLine("SqlException occurred at " & _    Now.ToLongTimeString & " in " & myException.TargetSite.Name)   Trace.WriteLine(myException.StackTrace)   ' Walk through all of the errors   For Each sqlE In myException.Errors     strMsg = "Source: " & sqlE.Source & ControlChars.Cr & _         "Number: " & sqlE.Number.ToString() & ControlChars.Cr & _         "State: " & sqlE.State.ToString() & ControlChars.Cr & _         "Class: " & sqlE.Class.ToString() & ControlChars.Cr & _         "Server: " & sqlE.Server & ControlChars.Cr & _         "Message: " & sqlE.Message & ControlChars.Cr & _         "Procedure: " & sqlE.Procedure & ControlChars.Cr & _         "LineNumber: " & sqlE.LineNumber.ToString()     Trace.WriteLine(strMsg)   Next   Trace.WriteLine("End of SqlException") End Sub 

You'll notice from Listing 8.2 that the LogSqlErrors method accepts a SqlException object as a parameter and so would be called from the Catch block of a Try Catch statement. The method then writes information using the Trace class of the System.Diagnostics namespace. The interesting aspect of the Trace class is that your application can create listeners that can capture the trace output to a file, the console, or even a Windows event log. The SqlErrorCollection is then iterated using a For Each loop and the error properties written to the trace output.

for RuBoard