Now that you understand the data provider interfaces, you'll implement a custom data provider that has the ability to fill a DataSet from data in piped format. (This data provider will be read only for the sake of brevity.) Figure 13-3 shows the PipedDataProvider that you'll create in the following sections.
Figure 13-3: Data from pipeddata.txt in Notepad
As you can see from Figure 13-3, a piped format file is basically a series of columns separated by the pipe symbol (|). There's no data typing—or even the suggestion of data typing—other than what can be guessed from the strings between each pipe. For this reason, the object you'll implement will only support string data and not attempt to make a guess that the first column in the figure should be an integer.
Because the data provider you're creating needs to access a file system object, the connection implemented in Listing 13-2 manages the file handle for the file provided to the object.
Listing 13-2: The PipedDataConnection Object
Imports System Imports System.Data Imports System.IO Namespace AppliedADO.DataProvider Public Class PipedDataConnection Implements System.Data.IDbConnection Public FileName As String Friend file As System.IO.StreamReader Public Sub New(ByVal fn As String) Me.FileName = fn file = New StreamReader(New FileStream(Me.FileName, FileMode.Open)) _State = ConnectionState.Open 'The constructor for this connection opens a file handle End Sub Public Sub NotSupported() Throw New System.NotSupportedException() End Sub Public Function BeginTransaction(ByVal iso As IsolationLevel) _ As IDbTransaction Implements IDbConnection.BeginTransaction NotSupported() Return Nothing End Function Public Function BeginTransaction() _ As IDbTransaction Implements IDbConnection.BeginTransaction NotSupported()'We are not supporting transactions Return Nothing End Function Public Sub ChangeDatabase(ByVal newDB As String) _ Implements IDbConnection.ChangeDatabase NotSupported()'We are not supporting the ability to change databases End Sub Public Function CreateCommand() _ As IDbCommand Implements IDbConnection.CreateCommand Dim idbCmd As IDbCommand idbCmd = New PipedDataCommand() Return idbCmd 'Creates the command object End Function Public Sub Close() Implements IDbConnection.Close file.Close() _State = ConnectionState.Closed 'Closes the file handle and sets the state to closed End Sub Public Sub Open() Implements IDbConnection.Open _State = ConnectionState.Open 'Since we're opening the file handle in the constuctor, we're 'just setting the state here. End Sub Dim _State As ConnectionState Public ReadOnly Property State() As ConnectionState _ Implements IDbConnection.State Get Return _State End Get End Property Public Property ConnectionString() As String Implements _ IDbConnection.ConnectionString Get Return FileName End Get Set(ByVal Value As String) FileName = Value End Set End Property Private _ConnectionTimeout = 0 Public ReadOnly Property ConnectionTimeout() As Integer Implements _ IDbConnection.ConnectionTimeout Get Return _ConnectionTimeout End Get End Property Private _Database = "" Public ReadOnly Property Database() As String Implements _ IDbConnection.Database Get Return _Database End Get End Property End Class End Namespace
In the case of the PipedDataConnection object, the connection string is a complete path and filename of the file in piped format. As soon as you create a new FileStream object, the file handle opens; so, in the constructor for this object, you set the ConnectionState to Open. When the Close() method is called on the PipedDataConnection object, the FileStream closes and the handle to the file is released.
In the course of implementing this object, you'll find there are several methods that have no relevance to this particular type of data source. For example, ChangeDatabase() is meaningless because you're not working with a database or any other type of data source with named subsections. To avoid complexity, you can skip BeginTransaction(). With these methods you simply throw a NotSupportedException to let the data provider system know that this particular method or property isn't supported.
This may sound like it would cause this connection to create all kinds of problems, but in a practical application it doesn't. The portions of code in the PipedDataConnection object that throw the NotSupportedException will be understood and suppressed by the container they run in, assuming of course that you don't try to directly call them from your code.
The Command object for the piped data provider is the PipedDataCommand object. This object represents a single action taken against the data source; in this case, the PipedDataCommand object can only load the entire contents of the file named in the PipedDataConnection object, so it has no need to actually interpret the CommandText property.
When this object is created, it's passed a string for the CommandText of the command and the PipedDataConnection on which this command will take place.
The primary workhorse of this object is the ExecuteReader method. According to the interface it implements, the IDbCommand interface, this method should return something of the type IDataReader. In this case, a PipedDataReader is returned (PipedDataReader is defined in the next section of this chapter). Listing 13-3 shows the PipedDataCommand object.
Listing 13-3: The PipedDataCommand Object
Imports System Imports System.Data Namespace AppliedADO.DataProvider Public Class PipedDataCommand Implements System.Data.IDbCommand Public Sub New() End Sub Public Sub New(ByVal cmd As String, ByRef pdc As PipedDataConnection) _CommandText = cmd _Connection = pdc End Sub Public Sub NotSupported() Throw New NotSupportedException() End Sub Public Sub NotImpl() Throw New NotImplementedException() End Sub Public Sub Cancel() Implements IDbCommand.Cancel NotSupported() End Sub Public Sub Prepare() Implements IDbCommand.Prepare End Sub Public Function ExecuteNonQuery() _ As Integer Implements IDbCommand.ExecuteNonQuery Return 0 End Function Public Function CreateParameter()_ As IDataParameter Implements IDbCommand.CreateParameter NotSupported() Return Nothing End Function Public Function ExecuteReader()_ As IDataReader Implements IDbCommand.ExecuteReader Dim reader As PipedDataReader reader = New PipedDataReader("ALL", _Connection) Return reader End Function Public Function ExecuteReader(ByVal b As CommandBehavior) _ As IDataReader Implements IDbCommand.ExecuteReader Return ExecuteReader() End Function Public Function ExecuteScalar() As Object Implements _ IDbCommand.ExecuteScalar NotImpl() Return Nothing End Function Private _CommandText As String Public Property CommandText() As String Implements IDbCommand.CommandText Get Return _CommandText End Get Set(ByVal Value As String) _CommandText = Value End Set End Property Private _CommandTimeout = 0 Public Property CommandTimeout()_ As Integer Implements IDbCommand.CommandTimeout Get Return _CommandTimeout End Get Set(ByVal Value As Integer) _CommandTimeout = Value End Set End Property Private _CommandType As CommandType Public Property CommandType() As CommandType _ Implements IDbCommand.CommandType Get Return _CommandType End Get Set(ByVal Value As CommandType) If (Value <> CommandType.Text) Then NotSupported() End If _CommandType = Value End Set End Property Private _Connection As IDbConnection Public Property Connection() As IDbConnection _ Implements IDbCommand.Connection Get Return _Connection End Get Set(ByVal Value As IDbConnection) _Connection = Value End Set End Property Public ReadOnly Property Parameters() As _ IDataParameterCollection Implements IDbCommand.Parameters Get NotSupported() Return Nothing End Get End Property Public Property Transaction() As IDbTransaction _ Implements IDbCommand.Transaction Get NotSupported() Return Nothing End Get Set(ByVal Value As IDbTransaction) NotSupported() End Set End Property Public Property UpdatedRowSource() As UpdateRowSource _ Implements IDbCommand.UpdatedRowSource Get Return UpdatedRowSource.None End Get Set(ByVal Value As UpdateRowSource) End Set End Property End Class End Namespace
For the calling program to loop through and access the data in the piped data file, you have to implement a Reader object. The PipedDataReader object's responsibility is iterating through the data in the file and providing the data to the calling program in a strongly typed format.
Most of the work that this object does happens in the Read() method. This method reads the next line in the file and sets the internal object array _cols to the result. Once the _cols variable has been set, the different GetXXX methods can provide this information to the calling program.
In the constructor for this object, the Read() method is automatically called once and the connection is reset. The reason for this is so the object can "taste" the contents of the file and know how many columns to expect.
One of the traditional requirements of a file in piped format is that the column lengths be consistent. As an addition to the program in Listing 13-4, you could actually read through the whole file and verify that this is indeed the case and then throw an error if they aren't the same length.
Listing 13-4: The PipedDataReader Object
Imports System Imports System.Data Imports System.Data.Common Imports System.IO Imports PipedDataProvider Namespace AppliedADO.DataProvider Public Class PipedDataReader Implements IDataReader, IDataRecord, IEnumerable Private _Command As String Public Sub New(ByVal cmd As String, ByRef conn As PipedDataConnection) _Command = cmd _Connection = conn 'Taste The File To Fill In The Meta-Data Me.Read() 'File Tasted, close and reopen the connection _Connection.Close() _Connection = New PipedDataConnection(_Connection.ConnectionString) End Sub Private Sub NotSupported() Throw New NotSupportedException() End Sub Public Function GetSchemaTable()_ As DataTable Implements IDataReader.GetSchemaTable Me.NotSupported() End Function Public Sub Close() Implements IDataReader.Close _isClosed = True If (Not _Connection Is Nothing) Then _Connection.Close() End If End Sub Public Function NextResult() As Boolean Implements IDataReader.NextResult Return False End Function Private splitter() As Char = {"|"} Public Function Read() As Boolean Implements IDataReader.Read 'This is the main method where a single line is read from the file and sets 'the value of the _cols collection. Dim line As String line = _Connection.file.ReadLine() If (line = "") Then Return False End If Dim tCols() As String tCols = line.Split(splitter) _cols = tCols Return True End Function Private _depth = 3 Public ReadOnly Property Depth() As Integer Implements IDataReader.Depth Get Return _depth End Get End Property Private _isClosed = False Public ReadOnly Property IsClosed() As _ Boolean Implements IDataReader.IsClosed Get Return _isClosed End Get End Property Private _RecordsAffected As Integer Public ReadOnly Property RecordsAffected()_ As Integer Implements IDataReader.RecordsAffected Get Return _RecordsAffected End Get End Property Public Function GetBoolean(ByVal i As Integer) _ As Boolean Implements IDataReader.GetBoolean Return CType(_cols(i), Integer) End Function Public Function GetByte(ByVal i As Integer) _ As Byte Implements IDataReader.GetByte Return CType(_cols(i), Byte) End Function Public Function GetBytes(ByVal i As Integer, ByVal _ fieldoffset As Long, ByVal bytes()_ As Byte, ByVal length As Integer, ByVal bufferoffset As Integer) _ As Long Implements IDataReader.GetBytes NotSupported() Return Nothing End Function Public Function GetChar(ByVal i As Integer) _ As Char Implements IDataReader.GetChar Return CType(_cols(i), Char) End Function Public Function GetChars(ByVal i As Integer, ByVal fieldoffset As Long, _ ByVal buffer As Char(), ByVal length As Integer, ByVal bufferoffset As Integer) _ As Long Implements IDataReader.GetChars NotSupported() Return Nothing End Function Public Function GetData(ByVal i As Integer) As _ IDataReader Implements IDataReader.GetData NotSupported() Return Nothing End Function Public Function GetDataTypeName(ByVal i As Integer) _ As String Implements IDataReader.GetDataTypeName Return GetType(String).ToString() End Function Public Function GetDateTime(ByVal i As Integer) _ As DateTime Implements IDataReader.GetDateTime Return CType(_cols(i), DateTime) End Function Public Function GetDecimal(ByVal i As Integer) As _ Decimal Implements IDataReader.GetDecimal Return CType(_cols(i), Decimal) End Function Public Function GetDouble(ByVal i As Integer) As _ Double Implements IDataReader.GetDouble Return CType(_cols(i), Integer) End Function Public Function GetFieldType(ByVal i As Integer) As _ Type Implements IDataReader.GetFieldType Return GetType(String) End Function Public Function GetFloat(ByVal i As Integer) As _ Single Implements IDataReader.GetFloat Return CType(_cols(i), Single) End Function Public Function GetString(ByVal i As Integer) As _ String Implements IDataReader.GetString Return CType(_cols(i), String) End Function Public Function GetGuid(ByVal i As Integer) As _ Guid Implements IDataReader.GetGuid Return CType(_cols(i), Guid) End Function Public Function GetInt16(ByVal i As Integer) As _ Short Implements IDataReader.GetInt16 Return CType(_cols(i), Short) End Function Public Function GetInt32(ByVal i As Integer) As _ Int32 Implements IDataReader.GetInt32 Return CType(_cols(i), Int32) End Function Public Function GetInt64(ByVal i As Integer) As _ Int64 Implements IDataReader.GetInt64 Return CType(_cols(i), Int64) End Function Public Function GetName(ByVal i As Integer) As _ String Implements IDataReader.GetName Return "COLUMN" + i.ToString() End Function Public Function GetOrdinal(ByVal name As String) As _ Integer Implements IDataReader.GetOrdinal NotSupported() Return Nothing End Function Public Function GetValue(ByVal i As Integer) As _ Object Implements IDataReader.GetValue Return CType(_cols(i), Object) End Function Public Function GetValues(ByVal values As Object()) As _ Integer Implements IDataReader.GetValues Dim i As Integer If (FieldCount < 1) Then Return 0 End If For i = 0 To FieldCount - 1 values(i) = _cols(i) Next Return FieldCount End Function Public Function IsDBNull(ByVal i As Integer) As _ Boolean Implements IDataReader.IsDBNull Return False End Function Public ReadOnly Property FieldCount() As Integer _ Implements IDataReader.FieldCount Get Return _cols.Length - 1 End Get End Property Default Public ReadOnly Property Item(ByVal name As String) _ As Object Implements IDataReader.Item Get Me.NotSupported() Return Nothing 'Return _cols(Array.IndexOf(_names, name)) End Get End Property Default Public ReadOnly Property Item(ByVal i As Integer) _ As Object Implements IDataReader.Item Get Return _cols(i) End Get End Property Public Function GetEnumerator() As IEnumerator Implements _ IEnumerable.GetEnumerator Return New System.Data.Common.DbEnumerator(Me) End Function Private _Connection As PipedDataConnection Private _cols() As Object End Class End Namespace
There are portions of the object that are hard-coded such as the type of data supported. Because piped format contains no meta-data as to the data types of each column, you have to assume that they're all strings. Also, because there's no meta-data, the columns are not actually named, so the PipedDataReader has to invent a column name for each column based on its position.
The final item of interest in the PipedDataReader object is the IEnumerable interface and its related implementation. By implementing this interface, you allow your PipedDataReader to be bound directly to Web page and Windows Forms controls. In situations where performance is an absolute requirement down to the last processor cycle, Microsoft's tests show the DataReader to far outperform working with DataSets and XMLReaders.
The real joy of this is the simplicity of this implementation. All you have to do is implement the GetEnumerator() method and return a DbEnumerator object. Because you're implementing a standard .NET interface, there's a related object that knows how to work with it; therefore, there's no reason for you to implement a custom enumerator for your data provider.
Finally, the PipedDataAdapter object is where it all comes together. This object provides the layer between the data source and the DataSet. This object also defines what operation should be performed to fill the DataSet with data and how to go about updating the data source to reflect changes made to the DataSet in your data source.
This object inherits DbDataAdapter to get the utility of methods such as Fill(). It also implements the IDbDataAdapter interface to define what IDbCommands (the interface that defines a single operation on the data source) perform operations on the DataSet.
The four main points of interest in this implementation are the SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand properties. You'll probably recall the names of these commands from the first few chapters of this book. Each of these commands, once defined, allows any alteration made in the data source or DataSet to be reflected in the other. Listing 13-5 shows the PipedDataAdapter object.
Listing 13-5: The PipedDataAdapter Object
Imports System Imports System.Data Imports System.Data.Common Imports System.IO Imports PipedDataProvider Namespace AppliedADO.DataProvider Public Class PipedDataAdapter Inherits DbDataAdapter Implements IDbDataAdapter Public Sub New() End Sub Public Sub New(ByRef cmd As PipedDataCommand) _SelectCommand = cmd End Sub Private Sub NotSupported() Throw New NotSupportedException() End Sub Protected Overrides Function CreateRowUpdatingEvent(ByVal row As DataRow, _ ByVal cmd As IDbCommand, ByVal stmtType As StatementType, ByVal mapping _ As DataTableMapping) As RowUpdatingEventArgs NotSupported() End Function Protected Overrides Function CreateRowUpdatedEvent(ByVal row As DataRow, _ ByVal cmd As IDbCommand, ByVal stmtType As StatementType, ByVal mapping _ As DataTableMapping) As RowUpdatedEventArgs NotSupported() End Function Protected Overrides Sub OnRowUpdated(ByVal value As RowUpdatedEventArgs) End Sub Protected Overrides Sub OnRowUpdating(ByVal e As RowUpdatingEventArgs) End Sub Private _SelectCommand As IDbCommand Private _InsertCommand As IDbCommand Private _UpdateCommand As IDbCommand Private _DeleteCommand As IDbCommand Public Property SelectCommand() As IDbCommand Implements _ IDbDataAdapter.SelectCommand Get Return _SelectCommand End Get Set(ByVal Value As IDbCommand) _SelectCommand = Value End Set End Property Public Property InsertCommand() As IDbCommand Implements _ IDbDataAdapter.InsertCommand Get Return _InsertCommand End Get Set(ByVal Value As IDbCommand) _InsertCommand = Value End Set End Property Public Property UpdateCommand() As IDbCommand Implements _ IDbDataAdapter.UpdateCommand Get Return _UpdateCommand End Get Set(ByVal Value As IDbCommand) _UpdateCommand = Value End Set End Property Public Property DeleteCommand() As IDbCommand Implements _ IDbDataAdapter.DeleteCommand Get Return _DeleteCommand End Get Set(ByVal Value As IDbCommand) _DeleteCommand = Value End Set End Property End Class End Namespace
Creating a test case for the PipedDataProvider application is a simple one. It's simply a DataGrid and a single button that loads the pipeddata.txt file, fills a DataTable with its data, and then binds it to the DataGrid.
This example highlights how little is required in the front-end code that's forever simplified by the encapsulated code in your custom data provider. A piped data file is loaded, parsed, and displayed in just a few lines of code. Listing 13-6 shows the PipedDataProvider application.
Listing 13-6: The PipedDataProvider Test Case
Private Sub LoadButton_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles LoadButton.Click Dim adapter As New PipedDataAdapter() adapter.SelectCommand = New PipedDataCommand("ALL", New _ PipedDataConnection("../../pipeddata.txt")) Dim reader As System.Data.IDataReader Dim data As New DataTable() adapter.Fill(data) DisplayGrid.DataSource = data End Sub
Figure 13-4 shows the output of this program.
Figure 13-4: The output of the PipedDataProvider application