Building a .NET Data Provider

for RuBoard

The .NET Data Providers you have learned about in this book were all built using the classes and interfaces in the System.Data and System.Data.Common namespaces, as discussed on Day 8, "Understanding .NET Data Providers." The existence of the classes and interfaces provides a pattern or template that developers can use when implementing providers. Not surprisingly, you can also build your own .NET Data Provider. This section will discuss why you might want to embark on this task, some alternatives, and the different forms the provider might take. The section ends with a discussion and a code sample of a simple provider that the ComputeBooks organization might implement to more easily expose and work with XML documents in a file system.

Deciding to Implement a Provider

Before deciding to implement a provider, you should be clear about why you would want to. There are several scenarios to consider in making this decision, including the following:

• Proprietary Data Store . If your organization has developed its own proprietary data store, you might consider implementing a provider to interact with that data store. This is analogous to a database vendor building its own provider and is therefore a specific or targeted provider. For example, assume that your organization builds packaged medical software, and that the data storage format and query language for your suite of applications is one that your organization developed in-house. Many organizations have done just this, and have in the past exposed their proprietary data store as functions in a set of DLLs or in COM-based wrappers. By implementing a provider, you can expose the functionality of your data store to managed code in a way that is consistent with what .NET developers will be familiar with. In this way, developers internal to your organization can easily use the data store when building Windows Forms, Web Forms, and XML Web Services applications. In addition, a provider would enable your customers to query and possibly even update the data store in conjunction with other data they are working with. For example, customers could use your provider to fill a table in a DataSet and then populate a second table with data from their SQL Server database for display in their Intranet portal.

• Data Aggregation . You might also implement a provider to centralize access to organizational data. By abstracting the location and formats for data within your organization and exposing them simply through a provider, you can make the process of creating managed applications simpler for your developers. In this way, developers within your organization can work with the data through a single provider rather than having to figure out how to access the various data stores your applications require. This type of provider is more analogous to the generic providers such as OleDb and Odbc, although it is implemented at the organizational rather than the data store level.

In both cases, the end result is that you allow managed access to data through standard interfaces, thereby making the programming model easier for developers. In addition, the use of standard interfaces such as IDataReader and IDbCommand enables your developers to take advantage of polymorphism by programming to the interfaces rather than the concrete classes that implement them. In this way, your developers can also write code in the presentation or business services tiers of their applications which works with any provider ”true reusability.

Note

In fact, creating a provider that uses the ADO.NET interfaces allows it to be plugged into generic factory classes that abstract all the provider specifics from developers. We'll look at an example of just such a class on Day 18, "Building a Data Factory."

At the same time, however, the fact that you're developing your own classes gives you the opportunity to expose functionality specific to your organization or data store through your provider. For example, if you created a specific provider for a proprietary data store, you could expose additional (overloaded) signatures for the Fill method of your data adapter class that uses the industry standard format that you want the data returned in.

Exploring Alternatives to Implementing a Provider

That having been said, there are also several scenarios in which you might want to consider alternatives to developing a provider.

First, if you're considering implementing a provider for a proprietary data store, you need to consider which clients need access to the data store. Implementing a provider will allow only managed (.NET) clients to access the data store; therefore, it can't be used from other environments such as Win32 applications implemented with MFC or ASP Web sites implemented with VBScript and COM components . If your requirements dictate that you support other types of clients, you should consider implementing an OLE DB provider (or, less likely, an ODBC driver) instead. Implementing an OLE DB provider makes the data store accessible to managed clients through the OleDb provider as well as other data access interfaces such as ADO.

Note

For more information to get you started in implementing an OLE DB provider, see the article "OLE DB Minimum Levels of Consumer and Provider Functionality" and the OmniProv 1.0 sample OLE DB provider, both of which can be found on the MSDN Web site. Probably the easiest way is to use the OLE DB provider ActiveX Template Library (ATL) templates in Visual C++.

Second, you need to consider whether you need the core concepts exposed by the provider. In other words, does your scenario require the use of connections, transactions, data readers, and data adapters, or do you simply need to expose data to clients? If you simply need to expose data and don't want or need to provide the ADO.NET programming model, you might consider creating classes that expose the data as XML using the System.Xml namespace classes.

Choosing an Approach to Implementing a Provider

When implementing a provider, there are basically two approaches you can take depending on the needs of your organization:

• Full Provider . This type of provider would implement all the provider objects (all the interfaces in System.Data and System.Data.Common ) we discussed on Day 8, and provide complete transaction, data reader, and connection support. A full provider could be easily plugged into a data factory (like the one we'll discuss on Day 18) because the factory will be ensured that it supports the full range of provider functionality.

• Lightweight Provider . This type of provider would probably implement a subset of the provider objects we discussed on Day 8. For example, a lightweight provider would implement a data adapter for filling and synchronizing a DataSet , but not necessarily parameters, data readers, transactions, or perhaps even connections. This type of provider would be useful for disconnected scenarios, for example, where you wanted to be able to use your provider to return data from an XML Web Service. The ComputeBooks provider discussed in the next section is just such a provider.

For both types of providers, the available interfaces and their uses are shown in Table 14.1. Obviously, a full provider would implement all the interfaces, whereas a lightweight provider would implement only some of them. Note that, in addition, a full provider might implement command builder, exception, error, and permissions classes as well.

Table 14.1. Provider interfaces. Providers implement some of (lightweight) or all (full) these interfaces.

Interface	Use
IDataAdapter	Populates and synchronizes DataSet objects with the data store.
IDataParameter	Represents a parameter passed to a command.
IDataParameterCollection	Represents the collection of parameters passed to a command.
IDataReader	Streams through a result set returned from a command.
IDbCommand	Represents a query or command executed against the data store.
IDbConnection	Represents a unique session with the data store usually corresponding to a network connection.
IDbDataAdapter	Represents a data adapter that works with relational databases to support various commands to insert, update, and delete data from the data store. Implements the IDataAdapter interface.
IDbDataParameter	Represents a database parameter with Precision , Scale and Size passed to a command. Implements the IDataParameter interface.
IDbTransaction	Represents a local transaction to group commands into logical units of work.

Implementing a Provider: The ComputeBooks Provider

To illustrate generally how you would implement a provider, this section walks through the implementation of a provider for ComputeBooks. This provider is quite simple in that it simply abstracts access to XML documents stored in a location on the file system.

Note

Obviously, there are other ways to access XML documents, including using the System.Xml classes directly or abstracting the access in a custom class. The code shown in this example is used only for simplicity and to illustrate the concepts and code required to implement a provider. Most providers will be quite complex and will therefore run into thousands of lines of code.

As discussed previously, the ComputeBooks provider is something of a lightweight provider because it doesn't implement all the interfaces shown in Table 14.1. In particular, there's no need for IDbTransaction or IDataReader because there's no concept of transacted access to the file system and data will always be returned in a DataSet , respectively. The provider also doesn't support command builders, code access permissions classes, or individual error objects. The architecture of the ComputeBooks provider is shown in Figure 14.3.

Figure 14.3. ComputeBooks provider architecture. This diagram contains the classes implemented by the ComputeBooks .NET Data Provider.

As you can see from Figure 14.3, each of the classes uses the standard naming convention of prefixing with a three- or four-letter abbreviation for the provider; in this case, Cbks to denote ComputeBooks. In addition, all the classes and enumerated types used by the provider should be placed in the same namespace. The convention, of course, is that the highest-level namespace is that of the organization and the lowest -level namespace is the name of the provider. So, in this case, all the classes are contained in the ComputeBooks.Data.Cbks namespace and compiled into a single assembly so that it can be versioned, secured, and deployed to other developers within ComputeBooks.

Note

Although the previous code you've seen today was written in C#, the ComputeBooks provider shown in the following sections is written in VB, whereas the client code to access it is written in C#. This is done to illustrate the fact that it doesn't matter what managed language the provider is implemented in.

The Connection Class

The first class that you need to implement when building a provider is the connection class; in this case, CbksConnection . Although they aren't strictly required, connection classes are typically relied on by the command class to provide it with the avenue through which to execute. Some of the responsibilities of the connection class are to capture and verify the connection string and open and close the connection. Because CbksConnection is the first class we'll discuss, its complete code is shown in Listing 14.2. In later sections, you'll see only portions of each class.

Listing 14.2 Implementing a connection. This listing shows the complete code for the CbksConnection class.

 Public NotInheritable Class CbksConnection : Implements IDbConnection   Private _state As ConnectionState   Private _connect As String   Private _locationPath As String   Private _database As String   ' The default constructor.   Public Sub New()     MyBase.New()     Me.InitClass()   End Sub   ' Constructor that takes a connection string.   Public Sub New(ByVal connect As String)     MyBase.New()     Me.InitClass()     Me.ConnectionString = connect   End Sub   Private Sub InitClass()     _state = ConnectionState.Closed   End Sub   Public Property ConnectionString() As String _     Implements IDbConnection.ConnectionString     Get       ' Always return exactly what the user set.       ' Security-sensitive information may be removed.       Return _connect     End Get     Set(ByVal Value As String)       ' Parse the connection string for syntax, not content       Dim h As Hashtable = _parseConnectionString(Value)       If Not h.ContainsKey("Location") Then         Throw New CbksException("Must include Location attribute")       Else         _locationPath = h.Item("Location").ToString()       End If       ' Can look for other specific attributes here       _connect = Value     End Set   End Property   Private Function _parseConnectionString(ByVal s As String) As Hashtable     Dim pairs(), a As String     Dim h As New Hashtable()     ' Split into an array of each name-value pair     pairs = s.Split(CType(";", Char))     For Each a In pairs       ' Look for the name and value       Dim i As Integer = a.IndexOf(CType("=", Char))       If i = 0 Then         Throw New CbksException("Connection string is improperly formatted")       Else          ' Place them in a hashtable         h.Add(a.Substring(0, i), a.Substring(i + 1))       End If     Next     Return h   End Function   Public ReadOnly Property ConnectionTimeout() As Integer _     Implements IDbConnection.ConnectionTimeout     Get       ' Returns the connection time-out value set in the connection       ' string. Zero indicates an indefinite time-out period.       Return 0     End Get   End Property   Public ReadOnly Property Database() As String _     Implements IDbConnection.Database     Get       ' Returns an initial database as set in the connection string.       ' An empty string indicates not set - do not return a null reference.       Return _database     End Get   End Property   Public ReadOnly Property State() As ConnectionState _     Implements IDbConnection.State     Get       Return _state       End Get   End Property   Overloads Sub Dispose() Implements IDisposable.Dispose     ' Make sure all managed and unmanaged resources are cleaned up     ' In this case there are no unmanaged resources   End Sub   Public Overloads Function BeginTransaction() As IDbTransaction _     Implements IDbConnection.BeginTransaction      Throw New NotSupportedException("Transactions not supported")   End Function   Public Overloads Function BeginTransaction(ByVal level As IsolationLevel) _     As IDbTransaction Implements IDbConnection.BeginTransaction      Throw New NotSupportedException("Transactions not supported")   End Function   Public Sub ChangeDatabase(ByVal name As String) _     Implements IDbConnection.ChangeDatabase     ' Change the database setting on the back-end. Note that it is a method     ' and not a property because the operation requires an expensive     ' round trip.     ' Change the path     Try       Dim dir As New DirectoryInfo(name)       _locationPath = name       _database = dir.FullName     Catch e As Exception       Throw New CbksException("Cannot change database", e)     End Try   End Sub   Public Sub Open() Implements IDbConnection.Open     ' If the underlying connection to the server is     ' expensive to obtain, the implementation should provide     ' implicit pooling of that connection.     ' If the provider also supports automatic enlistment in     ' distributed transactions, it should enlist during Open().     ' Make sure the path exists     Try       Dim dir As New DirectoryInfo(_locationPath)       _database = dir.FullName     Catch e As Exception       Throw New CbksException("Cannot open connection", e)     End Try     _state = ConnectionState.Open   End Sub   Public Sub Close() Implements IDbConnection.Close     ' If the underlying connection to the server is     ' being pooled, Close() will release it back to the pool.     _database = ""  ' Reset the read-only properties     _state = ConnectionState.Closed   End Sub   Public Function CreateCommand() As CbksCommand     ' Return a new instance of a command object.     Return New CbksCommand()   End Function   Private Function _createCommand() As IDbCommand _     Implements IDbConnection.CreateCommand     ' Return a new instance of a command object.     Return Me.CreateCommand()   End Function     ' Your custom properties / methods. End Class 

The first thing you should notice in Listing 14.2 is that the CbksConnection class implements the IDbConnection interface. As a result, all the members of IDbConnection must be implemented by the class. In VB, this is done with the Implements keyword. You'll also notice that although all the methods of the interface are implemented, not all must be supported. To indicate that you don't support a method, simply throw the System.NotSupportedException , as is done in the overloaded BeginTransaction methods. You would use this technique when the client expects some specific behavior to occur when the method is called. If the client wouldn't necessarily expect anything to happen, you can simply leave the body of the method empty, as is done with the Dispose method. This is referred to as a no-op, or no operation. For a property that isn't used, you can similarly return the default value in the Get block and throw a NotSupportedException in the Set block.

The primary functionality in the CbksConnection class is to validate the ConnectionString property when it is set, either through the constructor or directly, and then to open the connection.

As you can see from the Set block in the ConnectionString property, the string is passed to the private _parseConnectionString method, which parses a connection string into a Hashtable by first splitting the string into attributes by looking for semicolons (;) and then placing the individual name-value pairs into the Hashtable based on the presence of an equal sign (=). Note that if one of the attributes doesn't contain an equal sign, an exception is thrown. When the Hashtable is returned, the property looks for the Location attribute and raises an exception if it isn't found. If it is found, the location path is stored in a private variable along with the entire connection string. You can use a technique like this to parse your connection string and look for specific attributes.

The Open method then simply determines whether the location found in the connection string is valid by using a DirectoryInfo object. If the location exists, the full path is placed in the _database private variable returned through the read-only Database property. Of course, the Open method should then set the State property to the Open value of the ConnectionState enumeration. Conversely, the Close method simply sets the state to Closed and resets the read-only Database property to an empty string.

A client can use the CbksConnection object as follows :

 CbksConnection con = new CbksConnection("Location=."); con.Open(); Console.WriteLine(con.Database); //prints the full path of the current directory con.Close(); 

The Command Class

The command class is responsible for managing the text and type of a command along with its parameters, as well as actually using the connection object to execute the command. Listing 14.3 contains all the implemented code for the CbksCommand . It doesn't include the members that are no-ops and those that will throw a NotSupportedException .

Tip

When you're developing classes that contain several members that won't be implemented, you can easily segregate them in VS .NET in a #Region statement and then collapse the region so that you can concentrate on the code you're actually implementing.

Listing 14.3 Implementing a command. This listing shows the simple CbksCommand class. It implements only the ExecuteNonQuery method and the ability to retrieve data from XML documents.

 Public NotInheritable Class CbksCommand : Implements IDbCommand   Private _cmdText As String   Private _params As CbksParameterCollection   Private _con As CbksConnection   ' Default constructor   Public Sub New()     _InitClass()   End Sub   ' Overloaded constructor   Public Sub New(ByVal cmdText As String)     Me.CommandText = cmdText     _InitClass()   End Sub   ' Overloaded constructor   Public Sub New(ByVal cmdText As String, ByVal connection As CbksConnection)     Me.CommandText = cmdText     Me.Connection = connection     _InitClass()   End Sub   Private Sub _InitClass()     _params = New CbksParameterCollection()   End Sub   ' Strongly typed and interface implementations   Public Property Connection() As CbksConnection     Get       Return _con     End Get     Set(ByVal Value As CbksConnection)       _con = Value     End Set   End Property   Private Property _connection() As IDbConnection _     Implements IDbCommand.Connection     Get       Return Me.Connection     End Get     Set(ByVal Value As IDbConnection)       Me.Connection = CType(Value, CbksConnection)     End Set   End Property   ' Strongly typed and interface implementations   Public ReadOnly Property Parameters() As CbksParameterCollection     Get       Return _params     End Get   End Property   Private ReadOnly Property _parameters() As IDataParameterCollection _     Implements IDbCommand.Parameters     Get       Return Me.Parameters     End Get   End Property   Public Property CommandText() As String Implements IDbCommand.CommandText     Get       Return _cmdText     End Get     Set(ByVal Value As String)       ' Usually commands are not validated until executed so simply set it here       _cmdText = Value     End Set   End Property   ' Strongly typed and interface implementations   Public Function CreateParameter() As CbksParameter     ' Return a new parameter     Return New CbksParameter()   End Function   Private Function _createParameter() As IDbDataParameter _     Implements IDbCommand.CreateParameter     ' Return a new parameter     Return Me.CreateParameter()   End Function   Public Function ExecuteNonQuery() As Integer _     Implements IDbCommand.ExecuteNonQuery     ' Check for a connection     If _con.State = ConnectionState.Closed Then       Throw New CbksException("Connection must be open")     End If     ' Go get the files based on the parameters     Dim files() As String = _validateParms()     Dim s As String     Select Case Me.CommandText       Case "Delete"         ' Delete each file         For Each s In files           Try             File.Delete(s)           Catch e As Exception             Throw New CbksException("Error in command execution", e)           End Try         Next         ' Case other commands to execute here       Case Else         Throw New CbksException("Command is not a non query command")     End Select   End Function   Friend Function GetData() As XmlReader()     ' Go get the data based on the command and return XmlReaders     ' for each document     Dim readers As New ArrayList()     If Me.CommandText = "Get" Then       Dim files() As String = _validateParms()       Dim s As String       For Each s In files         Dim xlr As New XmlTextReader(s)         readers.Add(xlr)       Next       ' Return the array of readers       Return CType(readers.ToArray(GetType(XmlReader)), XmlReader())     Else       Throw New CbksException("Invalid command")     End If   End Function   Private Function _validateParms() As String()     ' Translate the parms into an array of files to work with     Dim filePath As String     ' Build the filter     If _params.Contains("Vendor") Then       filePath = CType(_params.Item("Vendor"), CbksParameter).Value.ToString()     Else       filePath = ""     End If     ' Build the filter     If _params.Contains("YearMonth") Then       filePath &= CType( _       _params.Item("YearMonth"), CbksParameter).Value.ToString & ".xml"     Else       filePath &= "*.xml"     End If     Return Directory.GetFiles(Me.Connection.Database, filePath)   End Function End Class 

You'll notice in Listing 14.3 that the CbksCommand object stores the CommandText , a parameter collection (discussed in the next section), and a reference to a CbksConnection object as private variables in the class. As with all classes in the provider, you're free to implement whatever constructors you see fit, although following the patterns found in the SqlClient and OleDb providers will make your provider more usable. For example, the CbksCommand object includes three constructors: an empty or default constructor, one that accepts just the CommandText , and one that accepts both the CommandText and the CbksConnection . The fourth constructor implemented by SqlCommand , however, is not implemented because it accepts a transaction and this provider doesn't use transactions. These constructors are used to populate the private variables.

One interesting aspect of Listing 14.3 is the dual implementations of the Connection and Parameters properties and the CreateParameter method. Because the IDbCommand interface dictates that these members be implemented, you must create methods to implement them. However, when you do this, the type of the property or the return type of the method must be the same as that found in the interface. In other words, the Connection property would simply accept and return variables of type IDbConnection rather than the strongly typed (and preferred) CbksConnection . To deal with this situation, you can expose your own strongly typed public member and then make the interface's implementation private. This way, you ensure that clients use only the strongly typed classes as you would expect, while also being able to cast to the interface if the clients want to use polymorphism. This is illustrated by the following code snippet:

 CbksCommand com = new CbksCommand("Get"); // Calls the publicly exposed property com.Connection = new CbksConnection("Location=."); // Calls the private interface implementation IDbCommand icom = com Console.WriteLine(icom.Connection.ConnectiongString); 

You'll notice that when using the property directly, the public implementation is called, whereas after casting to the interface, the private interface implementation is called. This technique works exactly the same way when used with methods as well. Of course, this design means that you need to write two methods, but you should write the code for the methods only once in the public implementation, and then have the private implementation call it, as is done in both the Connection property and the CreateParameter method.

You can create the same design in C# by implementing a public member that uses the strong type and a second whose name includes the interface name and returns the type from the interface. For example, in C#, the private implementation of the Connection property would look like so:

 IDbConnection IDbCommand.Connection {    get    {        return this.Connection;    }    set    {        this.Connection = (CbksConnection)value;    } } 

The other point to note about CbksCommand is that the commands supported include just Get and Delete. These commands simply retrieve and delete a file or files based on the parameters associated with the command. Determining the granularity of the commands you'll support is the biggest issue you'll face when building a provider. For example, in a provider that is used for data aggregation, the commands you support might be less granular and simply point to a type of data and an action such as "get sales" or "get products." However, in a provider used to access a proprietary data store, the commands would need to be granular enough to access individual tables or sets of data analogous to SQL. Generally speaking, the CommandText should point to the action you want to take, coupled with the data elements you want, whereas the parameters define the filter to use.

In the case of CbksCommand , the ExecuteNonQuery method is implemented to be able to delete files, whereas the GetData method is used to retrieve file data. Note that, in both cases, the list of files to operate on is determined by the private _validateParms function, which returns an array of files. The GetData method is marked as Friend ( internal in C#) because it will be used by the CbksDataAdapter when filling a DataSet but shouldn't be publicly available. This method illustrates a key design point: When implementing the command object, encapsulate all the behavior of the command within itself. This includes the parsing of the command text and parameters as well as the actual execution of the command. This seems straightforward, but it's tempting, for example, to write code in the Fill method of the data adapter that actually performs the query rather than allowing the command object to do it. By allowing each class to do its own work, the provider will be easier to maintain and extend.

The Parameters Classes

Although not required for lightweight providers, by implementing parameter and parameter collection classes, you can allow your commands to vary based on the parameters. By not implementing parameters, you're forced to allow the CommandText to contain more information. However, as with other providers, you have the option of allowing both parameterized commands and commands that hard code all the information. In the ComputeBooks provider, it's assumed that unless parameters are provided, the Get and Delete commands will retrieve and delete all the files at the location specified by the CbksConnection object.

For most implementations, the parameter and parameter collection classes will be the most generic because they simply support the ability to create parameters and put them in a collection. In fact, most of the code in the following listings is based on the sample provider implementation you'll find in the online documentation. The implementation of the CbksParameter and CbksParameterCollection classes (once again, without their not supported and no-op members) is shown in Listing 14.4.

Listing 14.4 Implementing parameters. This is the code for the CbksParameter and CbksParameterCollection classes used to associate parameters with commands in the ComputeBooks provider.

 Public NotInheritable Class CbksParameter : Implements IDbDataParameter   Private _dbType As DbType = DbType.Object   Private _nullable As Boolean = False   Private _paramName As String   Private _sourceVersion As DataRowVersion = DataRowVersion.Current   Private _value As Object   ' Default constructor   Public Sub New()   End Sub   ' Specify the type   Public Sub New(ByVal parameterName As String, ByVal type As DbType)     _paramName = parameterName     _dbType = type   End Sub   ' Specify the type and value   Public Sub New(ByVal parameterName As String, ByVal value As Object)     _paramName = parameterName     Me.Value = value     ' Setting the value also infers the type.   End Sub   Public Property DbType() As DbType Implements IDataParameter.DbType     Get       Return _dbType     End Get     Set(ByVal Value As DbType)       _dbType = Value     End Set   End Property   Public ReadOnly Property IsNullable() As _     Boolean Implements IDataParameter.IsNullable     Get       Return _nullable     End Get   End Property   Public Property ParameterName() As String _     Implements IDataParameter.ParameterName     Get       Return _paramName     End Get     Set(ByVal Value As String)       _paramName = Value     End Set   End Property   Public Property SourceVersion() As DataRowVersion _     Implements IDataParameter.SourceVersion     Get       Return _sourceVersion     End Get     Set(ByVal Value As DataRowVersion)       _sourceVersion = Value     End Set   End Property   Public Property Value() As Object Implements IDataParameter.Value     Get       Return _value     End Get     Set(ByVal Value As Object)       _value = Value       _dbType = _inferType(Value)     End Set   End Property   Private Function _inferType(ByVal value As Object) As DbType     Select Case (Type.GetTypeCode(value.GetType()))       Case TypeCode.Object         Return DbType.Object       Case TypeCode.Boolean         Return DbType.Boolean       Case TypeCode.Int16         Return DbType.Int16       Case TypeCode.Int32         Return DbType.Int32       Case TypeCode.Int64         Return DbType.Int64       Case TypeCode.Single         Return DbType.Single       Case TypeCode.Double         Return DbType.Double       Case TypeCode.Decimal         Return DbType.Decimal       Case TypeCode.DateTime         Return DbType.DateTime       Case TypeCode.String         Return DbType.String       Case Else         Throw New CbksException("Value is of unsupported data type")     End Select   End Function End Class Public NotInheritable Class CbksParameterCollection : Inherits ArrayList   Implements IDataParameterCollection   Friend Sub New()     ' So that it is not publicly creatable, must go through CbksCommand   End Sub   Default Public Overloads Property Item( _    ByVal parameterName As String) As Object _     Implements IDataParameterCollection.Item     Get       Return Me(IndexOf(parameterName))     End Get     Set(ByVal Value As Object)       Me(IndexOf(parameterName)) = Value     End Set   End Property  Public Overloads Function Contains(ByVal parameterName As String) As Boolean _     Implements IDataParameterCollection.Contains     Return (-1 <> IndexOf(parameterName))   End Function   Public Overloads Function IndexOf(ByVal parameterName As String) As Integer _     Implements IDataParameterCollection.IndexOf     Dim index As Integer = 0     Dim item As CbksParameter     For Each item In Me       If 0 = _cultureAwareCompare(item.ParameterName, parameterName) Then         Return index       End If       index = index + 1     Next     Return -1   End Function   Public Overloads Sub RemoveAt(ByVal parameterName As String) _     Implements IDataParameterCollection.RemoveAt     RemoveAt(IndexOf(parameterName))   End Sub   ' Overloaded Add methods   Public Shadows Function Add(ByVal value As CbksParameter) As Integer     Return MyBase.Add(value)   End Function   Public Shadows Function Add(ByVal parameterName As String, _     ByVal type As DbType) As Integer     Return Add(New CbksParameter(parameterName, type))   End Function   Public Shadows Function Add(ByVal parameterName As String, _     ByVal value As Object) As Integer     Return Add(New CbksParameter(parameterName, value))   End Function   Private Function _cultureAwareCompare(ByVal strA As String, _     ByVal strB As String) As Integer     Return CultureInfo.CurrentCulture.CompareInfo.Compare(strA, strB, _         CompareOptions.IgnoreKanaType Or CompareOptions.IgnoreWidth Or _         CompareOptions.IgnoreCase)     End Function End Class 

As you can see from Listing 14.4, the only really interesting aspect of the CbksParameter class is in determining the type of the value. This is necessary because the Value property is of type System.Object . The private _inferType method is called from the Set block of the Value property. The case statement in the method can be used to check for all the types that your provider supports, and will simply throw an exception if the type isn't supported. Although not implemented in the ComputeBooks provider, this is also where you might implement provider-specific types (as in SqlClient) and additionally map the value of the parameter to your specific types.

In the CbksParameterCollection class, you'll notice that it inherits from ArrayList (where it gains much of its functionality) and implements the IDataParameterCollection interface. This class is used to hold the collection of parameters and isn't publicly creatable, as evidenced by its constructor being marked as Friend . This means that an instance of the class is available only through the CbksCommand object. One of its interesting aspects is that the overloaded Item property is marked as Default . This allows the property to be accessed directly and by its parameter name, rather than only through the index as implemented in the ArrayList base class. Likewise, the properties Contains , IndexOf , and RemoveAt simply provide additional overloads to the methods in ArrayList in order to allow access by parameter name. Finally, the Add method is overloaded to allow a parameter to be added in various ways. The use of the Shadows keyword in VB hides the base class version of the Add method that accepts an argument of type Object.

A client would then use the CbksCommand and CbksParameter objects like so:

 CbksCommand com = new CbksCommand("Get",con); com.Parameters.Add("Vendor", "Sams"); com.Parameters.Add("YearMonth", 200203); Console.WriteLine(com.Parameters[0].DbType.ToString()); //String Console.WriteLine(com.Parameters[1].DbType.ToString()); //Integer 

The Data Adapter Class

When you implement a data adapter, you have several options. In fact, there are two abstract classes, DataAdapter and DbDataAdapter , that you can inherit from in addition to two interfaces, IDataAdapter and IdbDataAdapter , that you can implement. As you would expect, the DataAdapter class implements the IDataAdapter interface and the DbDataAdapter class inherits from DataAdapter . This arrangement can be seen in Figure 14.4.

Figure 14.4. Data adapter classes and interfaces. This diagram shows the relationships between the classes and interfaces for building a data adapter.

As a result, you basically have two options:

1. If you've implemented a full provider complete with a data reader (discussed in the next section), you can simply inherit from DbDataAdapter and override the OnRowUpdating , CreateRowUpdatingEvent , CreateRowUpdatedEvent , and OnRowUpdated protected methods to initialize and raise the appropriate events as the data adapter is updated.

2. You can implement the IDbDataAdapter interface, which defines the four command properties used by the data adapter to select, insert, update, and delete data.

After you've completed these steps, your work is done. The Fill , FillSchema , and Update methods, along with the properties, are all implemented by the base class for you!

The reason this works is that the overloaded Fill method DbDataAdapter class calls the ExecuteReader method of the command object (passing it a command behavior of Sequential ), and uses the returned data reader to populate the DataSet or DataTable objects passed to the method. Likewise, the FillSchema method calls the ExecuteReader method with the command behavior set to the combination of KeyInfo and SchemaOnly to build the schema only.

If you don't implement a full provider, you can alternatively implement either of the interfaces and code the methods yourself.

Note

Implementing IDbDataAdapter brings IDataAdapter along as well because the former implements the latter.

The CbksDataAdapter class shown in Listing 14.5 takes this approach because the provider doesn't implement a data reader.

Listing 14.5 Implementing a data adapter. This listing shows the CbksDataAdapter class. It implements only interfaces, so the methods must be coded.

 Public NotInheritable Class CbksDataAdapter   Implements IDbDataAdapter   Private _selCommand As CbksCommand   ' Default Constructor   Public Sub New()   End Sub   Public Sub New(ByVal selectCommand As CbksCommand)     ' Assign the command     _selCommand = selectCommand   End Sub   Public Sub New(ByVal selectCommand As String, ByVal connection As String)     ' Create the connection and command     Dim con As New CbksConnection(connection)     _selCommand = New CbksCommand(selectCommand, con)   End Sub   Public Sub New(ByVal selectCommand As String, _     ByVal connection As CbksConnection)     ' Create the new command     _selCommand = New CbksCommand(selectCommand, connection)   End Sub   ' Implements only a SelectCommand   Public Property SelectCommand() As CbksCommand     Get       Return _selCommand     End Get     Set(ByVal Value As CbksCommand)       _selCommand = Value     End Set   End Property   Private Property _selectCommand() As IDbCommand _     Implements IDbDataAdapter.SelectCommand     Get       Return Me.SelectCommand     End Get     Set(ByVal Value As IDbCommand)       _selCommand = CType(Value, CbksCommand)     End Set   End Property   Public Function Fill(ByVal dataSet As DataSet) As Integer _     Implements IDataAdapter.Fill     ' Adds to or loads data into the dataset based on the parameters     Dim xlr() As XmlReader     Dim opened As Boolean = False     ' Make sure the connection is open     If Me.SelectCommand.Connection.State = ConnectionState.Closed Then       Me.SelectCommand.Connection.Open()       opened = True     End If     Try       ' Execute the command and get the xml readers       xlr = Me.SelectCommand.GetData()       Dim r As XmlReader       For Each r In xlr         ' Add each file to the DataSet         dataSet.ReadXml(r, XmlReadMode.Auto)         r.Close()       Next       Return -1     Catch e As Exception       Throw New CbksException("Could not fill DataSet", e)     Finally       If opened Then Me.SelectCommand.Connection.Close()     End Try   End Function   Public Function FillSchema(ByVal dataSet As DataSet, _     ByVal schemaType As SchemaType) As DataTable() _     Implements IDataAdapter.FillSchema     ' Fill the schema of the DataSet and return the array of tables     ' Note we're ignoring the schemaType     Dim t As DataTable     Dim i As Integer     Dim xlr() As XmlReader     Dim opened As Boolean = False     ' Make sure the connection is open     If Me.SelectCommand.Connection.State = ConnectionState.Closed Then       Me.SelectCommand.Connection.Open()       opened = True     End If     ' Empty the DataSet     For Each t In dataSet.Tables       dataSet.Tables.Remove(t)     Next     Try       ' Execute the command and get the xml readers       xlr = Me.SelectCommand.GetData()       Dim r As XmlReader       For Each r In xlr         ' Add each file to the DataSet         dataSet.ReadXml(r, XmlReadMode.Auto)         r.Close()       Next       ' Clear out the data       dataSet.Clear()       Dim tables(dataSet.Tables.Count - 1) As DataTable       For i = 0 To dataSet.Tables.Count - 1         tables(i) = dataSet.Tables(i)       Next       Return tables     Catch e As Exception       Throw New CbksException("Could not fill the schema", e)     Finally       If opened Then Me.SelectCommand.Connection.Close()     End Try   End Function End Class 

As with the other classes, you should follow the conventions and implement four constructors for your data adapter class that accept different combinations of the command used for selecting data and the connection if only the CommandText is specified.

You'll notice that CbksDataAdapter implements the IDbDataAdapter interface, but provides a strongly typed implementation only for the SelectCommand because the data adapter can only be used in a read-only mode. The only methods that are supported are Fill and FillSchema , although you certainly have the option of implementing additional overloaded Fill methods to populate a DataTable , as is done in DbDataAdapter .

In this case, the Fill method first makes sure that the connection object associated with the SelectCommand is open and, if not, opens it. The Finally block is used to Close the connection if it was opened within the Fill method (that is, implicitly). Then the GetData method of the CbksCommand class exposed as Friend is called to retrieve the array of XmlReader objects that will be used to read the XML documents. Each XmlReader is then read in to the DataSet using the ReadXml method with the XmlReadMode set to Auto . This will have the effect of augmenting any existing schema in the DataSet using a schema either inferred from the XML data or provided inline in the XmlReader . However, if the schema is incompatible, an exception will be thrown. Note that if all the schemas for the XmlReader objects are identical, the end result will be to append the XML data to the same tables within the DataSet .

The FillSchema method is very similar to Fill , although it first deletes all the tables from the passed-in DataSet and additionally returns an array of DataTable objects. In both cases, of course, the method needs to delete all the data using the Clear method because only the schema should be returned.

Finally, a client can then use the entire provider as shown in the following code snippet:

 CbksConnection con = new CbksConnection("Location=."); CbksDataAdapter da = new CbksDataAdapter("Get", con); da.SelectCommand.Parameters.Add("Vendor", "Sams"); DataSet ds = new DataSet(); da.FillSchema(ds); 

The Exception Class

All the classes in the ComputeBooks provider throw a CbksException when they encounter exceptions they can't handle. Creating custom exception classes is quite simple because you need only inherit from System.ApplicationException . The ApplicationException class inherits from System.SystemException , and simply provides a means of determining whether an exception was raised by custom code or the common language runtime itself. Of course, you can also extend your exception by adding custom members to provide additional information, just as the SqlException and OleDbException objects do.

In this case, the CbksException class shown in Listing 14.6 simply implements the constructors that call the constructors of ApplicationException . The second constructor is used to embed an inner exception, and is useful when the provider classes catch an exception so that the client is able to inspect it. You'll notice that the class is marked with the Serializable attribute to enable the common language runtime to copy it between application domains in the event the exception is thrown from a remote domain. This might occur, for example, if you use .NET remoting to call your provider across the network hosted in IIS.

Listing 14.6 Implementing an exception. This class implements the CbksException object for the ComputeBooks provider.

 <Serializable()> _ Public NotInheritable Class CbksException : Inherits ApplicationException   Public Sub New(ByVal message As String)     MyBase.New(message)   End Sub   Public Sub New(ByVal message As String, ByVal originalException As Exception)     MyBase.New(message, originalException)   End Sub End Class 

for RuBoard