ActiveReports 12
Custom Data Provider
ActiveReports 12 > ActiveReports User Guide > Samples and Walkthroughs > Walkthroughs > Page Report/RDL Report Walkthroughs > Advanced > Custom Data Provider

A custom data provider allows you to use non-traditional data sources in your page reports, both at run time and at design time. This walkthrough illustrates how to create a solution with projects that create a custom data provider and demonstrate how it pulls data from a comma separated values (CSV) file.

This walkthrough is split into the following activities:

When you complete this walkthrough, you will have a designer pre-loaded with a report that pulls data from a CSV file and looks like the following.

To create a Designer project to demonstrate the custom data provider

  1. In Visual Studio, create a Windows Forms project and name it CustomDataProviderDemo.
  2. From the Visual Studio toolbox ActiveReports 12 tab, drag a ReportExplorer and drop it onto the default Windows form, resizing the form to a comfortable working area.
  3. In the Properties window, set the Dock property of the ReportExplorer control to Left.
  4. From the toolbox Common Controls tab, drag a RichTextBox control onto the form and set the Dock property to Top.
  5. Add the following text to the Text property. (Drop down the box to ensure that all of the lines of text are added.)
    Text. Paste in the Text property of the RichTextBox.
    Copy Code
    1. In the Report Explorer, right-click the Data Sources node and select Add Data Source.
    2. In the Report Data Source dialog that appears, on the General tab, drop down the Type box, select CSV Data Provider, and click OK.
    3. In the Report Explorer, right-click DataSource1 and select Add Data Set.
    4. In the dialog that appears, select the Query page.
    5. Drop down the Query String box to display the custom query editor.
    6. Click the Select CSV File button and open the Categories.csv file in this project.
    7. Click OK to save the changes and close the dialog.
    8. Click Preview to see the report with data from the csv file.
    
  6. From the toolbox ActiveReports 12 tab, drag a Designer control and drop it on the empty part of the form.
  7. Set the Dock property to Fill, then right-click the Designer control on the form and select Bring to front.
  8. Select the ReportExplorer control and in the Properties window, drop down the ReportDesigner property and select Designer1.
  9. Double-click on the form's title bar to create a form Load event, and add code like the following above the class.
    Visual Basic code. Paste above the class.
    Copy Code
    Imports System.Xml
    Imports System.IO
    Imports GrapeCity.ActiveReports.Design
    
    C# code. Paste above the class.
    Copy Code
    using System.Xml;
    using System.IO;
    using GrapeCity.ActiveReports.Design;
    
  10. Add the following code to the form Load event.
    Visual Basic code. Paste inside the form Load event.
    Copy Code
    Using reportStream = File.OpenRead("DemoReport.rdlx")
            Using reader = XmlReader.Create(reportStream)
                    Designer1.LoadReport(reader, DesignerReportType.Page)
            End Using
    End Using
    
    C# code. Paste inside the form Load event.
    Copy Code
    using (var reportStream = File.OpenRead("DemoReport.rdlx"))
     {
         using (var reader = XmlReader.Create(reportStream))
          {
             designer1.LoadReport(reader, DesignerReportType.Page);
          }
     }
    

To configure the project to use a custom data provider

  1. In the Solution Explorer, right-click the project and select Add, then New Item.
  2. In the dialog that appears, select Text File, name it GrapeCity.ActiveReports.config, and click Add
  3. In the Solution Explorer, select the new file and in the Properties window, set its Copy to Output Directory property to Copy always.
  4. Paste the following text into the file and save it. (You can safely ignore the warning that the 'Configuration' element is not declared.)
    Paste into the config file.
    Copy Code
    <?xml version="1.0" encoding="utf-8" ?>
    <Configuration>
      <Extensions>
        <Data>
          <Extension Name="CSV" DisplayName="CSV Data Provider" 
            Type="CustomDataProvider.CsvDataProvider.CsvDataProviderFactory, 
              CustomDataProvider" 
              CommandTextEditorType="CustomDataProvider.CSVDataProvider.QueryEditor, 
            CustomDataProvider"/>
        </Data>
         </Extensions>
    </Configuration>
    
  5. In the Solution Explorer, right-click the project and select Add, then New Item.
  6. In the dialog that appears, select Text File, name it Categories.csv, and click Add
  7. In the Solution Explorer, click to select the file, and in the Properties window, change the Copy to Output Directory property to Copy always.
  8. Paste the following text into the file and save it.
    Paste into the text file.
    Copy Code
    EmployeeID(int32),LastName,FirstName,Role,City
    1,James,Yolanda,Owner,Columbus
    7,Reed,Marvin,Manager,Newton
    9,Figg,Murray,Cashier,Columbus
    12,Snead,Lance,Store Keeper,Columbus
    15,Halm,Jeffry,Store Keeper,Columbus
    17,Hames,Alma,Store Keeper,Oak Bay
    18,Nicki,Aubrey,Store Keeper,Columbus
    24,Cliett,Vikki,Store Keeper,Newton
    

To add a report to show the data from the custom data provider

  1. In the Solution Explorer, right-click the project and select Add, then New Item.
  2. In the dialog that appears, select ActiveReports 12 RDL Report, name it DemoReport, and click Add
  3. In the Solution Explorer, click to select the report, and in the Properties window, change the Copy to Output Directory property to Copy always.
  4. From the ActiveReports 12 RDL Report Toolbox, drag a Table report control onto the report.
    Note: In case you are still working on Page report layout, set the FixedSize property of the Table control to display all data on one page.
  5. Click inside the table to reveal the table adorners, then right-click the table adorner to the left of the footer row and select Delete Rows. The footer row is removed from the table.
  6. In the Report Explorer, select each of the textboxes in turn and set the properties as in the following table. (If you do not see the Report Explorer, from the View menu, select Other Windows, then Report Explorer 12.) 
    TextBox Name Value Property BackgroundColor Property
    TextBox1 Name MediumSlateBlue
    TextBox2 Role MediumSlateBlue
    TextBox3 City MediumSlateBlue
    TextBox4 =Fields!FirstName.Value & " " & Fields!LastName.Value
    TextBox5 =Fields!Role.Value
    TextBox6 =Fields!City.Value
  7. In the Report Explorer, select the Table1 node and in the Properties window, set the Location property to 0in, 1in and the Size property to 6in, 0.5in to make the table wide enough to see all of the data.
  8. With Table1 still selected in the Properties window, in the DataSetName property, enter the text DataSet1.

To add a class library project to the solution to contain the custom data provider

  1. From the File menu, select Add, then New Project.
  2. In the Add New Project dialog, select Class Library, and name the project CustomDataProvider.
  3. In the Solution Explorer, right-click the default class and select Delete. (We will add our classes to a folder below.)
  4. Right-click the CustomDataProvider project and select Add Reference, and in the Reference Manager dialog that appears, select the following references to add, holding down the Ctrl button to select multiple references. 
    • GrapeCity ActiveReports 12 (GrapeCity.ActiveReports.v12)
    • GrapeCity ActiveReports Extensibility Library (GrapeCity.ActiveReports.Extensibility.v12)
    • System.Drawing
    • System.Windows.Forms
  5. Right-click the CustomDataProvider project and select Add, then New Folder, and name the folder CSVDataProvider.
  6. Right-click the folder and select Add, then Class, then name the class CsvColumn and add code like the following to replace the default stub in the class.

    Visual Basic code

    Visual Basic code. Paste it to replace the default stub in the class.
    Copy Code
    Namespace CSVDataProvider
            ' Represents information about fields in the data source.
            Friend Structure CsvColumn
                    Private ReadOnly _fieldName As String
                    Private ReadOnly _dataType As Type
    
                    ' Creates a new instance of the CsvColumn class.
                    ' The fieldName parameter is the name of the field represented by this instance of the CsvColumn.
                    ' The dataType parameter is the Type of the field represented by this instance of the CsvColumn.
                    Public Sub New(fieldName As String, dataType As Type)
                            If fieldName Is Nothing Then
                                    Throw New ArgumentNullException("fieldName")
                            End If
                            If dataType Is Nothing Then
                                    Throw New ArgumentNullException("dataType")
                            End If
                            _fieldName = fieldName
                            _dataType = dataType
                    End Sub
    
    
                    ' Gets the name of the field represented by this instance of the CsvColumn.
                    Public ReadOnly Property FieldName() As String
                            Get
                                    Return _fieldName
                            End Get
                    End Property
    
    
                    ' Gets the the Type of the field represented by this instance of the CsvColumn.
                    Public ReadOnly Property DataType() As Type
                            Get
                                    Return _dataType
                            End Get
                    End Property
    
    
                    ' Returns a String that represents this instance of the CsvColumn.
                    Public Overrides Function ToString() As String
                            Return [String].Concat(New String() {FieldName, "(", DataType.ToString(), ")"})
                    End Function
    
    
                    ' Determines whether two CsvColumn instances are equal.
                    ' The obj represents the CsvColumn to compare with the current CsvColumn.
                    ' Returns True if the specified CsvColumn is equal to the current CsvColumn; otherwise, False.
                    Public Overrides Function Equals(obj As Object) As Boolean
                            Dim flag As Boolean
    
                            If TypeOf obj Is CsvColumn Then
                                    flag = Equals(CType(obj, CsvColumn))
                            Else
                                    flag = False
                            End If
                            Return flag
                    End Function
    
                    Private Overloads Function Equals(column As CsvColumn) As Boolean
                            Return column.FieldName = FieldName
                    End Function
    
    
                    ' Serves as a hash function for a CsvColumn, suitable for use in hashing algorithms and data structures like a hash table.
                    ' Returns a hash code for the current CsvColumn instance.
                    Public Overrides Function GetHashCode() As Integer
                            Return (FieldName.GetHashCode() + DataType.GetHashCode())
                    End Function
            End Structure
    End Namespace
    

    C# code

    C# code. Paste it to replace the default stub in the class.
    Copy Code
    using System;
    
    namespace CustomDataProvider.CSVDataProvider
    {
            // Represents information about fields in the data source.
            internal struct CsvColumn
            {
                    private readonly string _fieldName;
                    private readonly Type _dataType;
    
                    // Creates a new instance of the CsvColumn class.
                    // The fieldName parameter is the name of the field represented by this instance of the CsvColumn.
                    // The dataType parameter is the Type of the field represented by this instance of the CsvColumn.
                    public CsvColumn(string fieldName, Type dataType)
                    {
                            if (fieldName == null)
                                    throw new ArgumentNullException("fieldName");
                            if (dataType == null)
                                    throw new ArgumentNullException("dataType");
                            _fieldName = fieldName;
                            _dataType = dataType;
                    }
    
    
                    // Gets the name of the field represented by this instance of the CsvColumn.
                    public string FieldName
                    {
                            get { return _fieldName; }
                    }
    
    
                    // Gets the the Type of the field represented by this instance of the CsvColumn.
                    public Type DataType
                    {
                            get { return _dataType; }
                    }
    
    
                   // Returns a String that represents this instance of the CsvColumn.
                    public override string ToString()
                    {
                            return String.Concat(new string[] {FieldName, "(", DataType.ToString(), ")"});
                    }
    
    
                    // Determines whether two CsvColumn instances are equal.
                    // The obj represents the CsvColumn to compare with the current CsvColumn.
                    // Returns True if the specified CsvColumn is equal to the current CsvColumn; otherwise, False.
                    public override bool Equals(object obj)
                    {
                            bool flag;
    
                            if (obj is CsvColumn)
                            {
                                    flag = Equals((CsvColumn) obj);
                            }
                            else
                            {
                                    flag = false;
                            }
                            return flag;
                    }
    
                    private bool Equals(CsvColumn column)
                    {
                            return column.FieldName == FieldName;
                    }
    
    
                    // Serves as a hash function for a CsvColumn, suitable for use in hashing algorithms and data structures like a hash table.
                   // Returns a hash code for the current CsvColumn instance.
                    public override int GetHashCode()
                    {
                            return (FieldName.GetHashCode() + DataType.GetHashCode());
                    }
            }
    }
    
  7. Right-click the CSVDataProvider folder and select Add, then Class, then name the class CsvDataReader and add code like the following to replace the default stub in the class.

    Visual Basic code

    Visual Basic code. Paste it to replace the default stub in the class.
    Copy Code
    Imports System
    Imports System.Collections
    Imports System.Globalization
    Imports System.IO
    Imports System.Text.RegularExpressions
    Imports GrapeCity.ActiveReports.Extensibility.Data
    
    Namespace CSVDataProvider
    
        ' Provides an implementation of IDataReader for the .NET Framework CSV Data Provider.
        Friend Class CsvDataReader
            Implements IDataReader
            'NOTE: HashcodeProvider and Comparer need to be case-insensitive since TypeNames are capitalized differently in places. 
            'Otherwise data types end up as strings when using Int32 vs int32.
            Private _typeLookup As New Hashtable(StringComparer.Create(CultureInfo.InvariantCulture, False))
    
            Private _columnLookup As New Hashtable()
            Private _columns As Object()
            Private _textReader As TextReader
            Private _currentRow As Object()
    
    
            'The regular expressions are set to be pre-compiled to make it faster. Since we were concerned about
            'multi-threading, we made the properties read-only so no one can change any properties on these objects.
            Private Shared ReadOnly _rxDataRow As New Regex(",(?=(?:[^""]*""[^""]*"")*(?![^""]*""))", RegexOptions.Compiled)
            'Used to parse the data rows.
    
            Private Shared ReadOnly _rxHeaderRow As New Regex("(?<fieldName>(\w*\s*)*)\((?<fieldType>\w*)\)", RegexOptions.Compiled)
            'Used to parse the header rows.
    
    
            ' Creates a new instance of the CsvDataReader class.
            ' The textReader parameter represents the TextReader to use to read the data.
            Public Sub New(textReader As TextReader)
                _textReader = textReader
                ParseCommandText()
            End Sub
    
    
            ' Parses the passed-in command text.
            Private Sub ParseCommandText()
                If _textReader.Peek() = -1 Then
                    Return
                End If
                'Command text is empty or at the end already.
                FillTypeLookup()
    
                Dim header As String = _textReader.ReadLine()
                header = AddDefaultTypeToHeader(header)
    
                If Not ParseHeader(header) Then
                    Throw New InvalidOperationException( _
                    "Field names and types are not defined. " & _
                    "The first line in the CommandText must contain the field names and data types. e.g FirstName(string)")
                End If
            End Sub
    
            'A hashtable is used to return a type for the string value used in the header text.
            Private Sub FillTypeLookup()
                _typeLookup.Add("string", GetType([String]))
                _typeLookup.Add("byte", GetType([Byte]))
                _typeLookup.Add("boolean", GetType([Boolean]))
                _typeLookup.Add("datetime", GetType(DateTime))
                _typeLookup.Add("decimal", GetType([Decimal]))
                _typeLookup.Add("double", GetType([Double]))
                _typeLookup.Add("int16", GetType(Int16))
                _typeLookup.Add("int32", GetType(Int32))
                _typeLookup.Add("int", GetType(Int32))
                _typeLookup.Add("integer", GetType(Int32))
                _typeLookup.Add("int64", GetType(Int64))
                _typeLookup.Add("sbyte", GetType([SByte]))
                _typeLookup.Add("single", GetType([Single]))
                _typeLookup.Add("time", GetType(DateTime))
                _typeLookup.Add("date", GetType(DateTime))
                _typeLookup.Add("uint16", GetType(UInt16))
                _typeLookup.Add("uint32", GetType(UInt32))
                _typeLookup.Add("uint64", GetType(UInt64))
            End Sub
    
    
            ' Returns a type based on the string value passed in from the header text string. If no match is found,                 
            ' a string type is returned.
            ' The fieldType parameter represents the String value from the header command text string.
            Private Function GetFieldTypeFromString(fieldType As String) As Type
                If _typeLookup.Contains(fieldType) Then
                    Return TryCast(_typeLookup(fieldType), Type)
                End If
                Return GetType([String])
            End Function
    
    
            ' Parses the first line in the passed-in command text string to create the field names and field data types.                 
            ' The field information is stored in a CsvColumn struct, and these column info items are stored  
            ' in an ArrayList. The column name is also added to a hashtable for easy lookup later.
            ' The header parameter represents the header string that contains all the fields.
            ' Returns True if it can parse the header string; otherwise False.
            Private Function ParseHeader(header As String) As Boolean
                Dim fieldName As String
                Dim index As Integer = 0
                If header.IndexOf("(") = -1 Then
                    Return False
                End If
    
                Dim matches As MatchCollection = _rxHeaderRow.Matches(header)
                _columns = New Object(matches.Count - 1) {}
                For Each match As Match In matches
                    fieldName = match.Groups("fieldName").Value
                    Dim fieldType As Type = GetFieldTypeFromString(match.Groups("fieldType").Value)
                    _columns.SetValue(New CsvColumn(fieldName, fieldType), index)
                    _columnLookup.Add(fieldName, index)
                    index += 1
                Next
    
                Return True
            End Function
    
    
            ' Ensures that the header contains columns in the form of name(type)
            ' The line parameter represents the raw header line from the file to fix up.
            ' Returns a modified header with default types appended to column names.
            Private Shared Function AddDefaultTypeToHeader(line As String) As String
                Const ColumnWithDataTypeRegex As String = "[""]?\w+[\""]?\(.+\)"
                Dim columns As String() = line.Split(New String() {","}, StringSplitOptions.None)
                Dim ret As String = Nothing
                For Each column As String In columns
                    If Not String.IsNullOrEmpty(ret) Then
                        ret += ","
                    End If
                    If Not Regex.Match(column, ColumnWithDataTypeRegex).Success Then
                        ret += column + "(string)"
                    Else
                        ret += column
                    End If
                Next
                Return ret
            End Function
    
    
            ' Parses a row of data using a regular expression and stores the information inside an object                 
            ' array that is the current row of data.
            ' If the row does not have the correct number of fields, an exception is raised.
            ' The dataRow parameter represents the String value representing a comma delimited data row.
            ' Returns True if it can parse the data string; otherwise False.
            Private Function ParseDataRow(dataRow As String) As Boolean
                Dim index As Integer = 0
                Dim tempData As String() = _rxDataRow.Split(dataRow)
    
                _currentRow = New Object(tempData.Length - 1) {}
                If tempData.Length <> _columns.Length Then
                    Dim [error] As String = String.Format(CultureInfo.InvariantCulture, _
                                    "Invalid row ""{0}"". The row does not contain the same number of data columns as the table header definition.", dataRow)
                    Throw New InvalidOperationException([error])
                End If
                For i As Integer = 0 To tempData.Length - 1
                    Dim value As String = tempData(i)
    
                    If value.Length > 1 Then
                        If value.IndexOf(""""c, 0) = 0 AndAlso value.IndexOf(""""c, 1) = value.Length - 1 Then
                            value = value.Substring(1, value.Length - 2)
                        End If
                    End If
                    _currentRow.SetValue(ConvertValue(GetFieldType(index), value), index)
                    index += 1
                Next
                Return True
            End Function
    
    
            ' Coverts the string value coming from the command text to the appropriate data type, based on the field's type.
            ' This also checks a few string value rules to decide if a String.Empty of System.Data.DBNull needs to be returned.
            ' The type parameter represents the Type of the current column the data belongs to.
            ' The originalValue parameter represents the String value coming from the command text.
            ' Returns the object resulting from the converted string, based on the type.
            Private Function ConvertValue(type As Type, originalValue As String) As Object
                Dim fieldType As Type = type
                Dim invariantCulture As CultureInfo = CultureInfo.InvariantCulture
                Try
                    If originalValue = """""" OrElse originalValue = " " Then
                        Return String.Empty
                    End If
                    If originalValue = "" Then
                        Return DBNull.Value
                    End If
                    If originalValue = "DBNull" Then
                        Return DBNull.Value
                    End If
                    If fieldType.Equals(GetType([String])) Then
                        Return originalValue.Trim()
                    End If
                    If fieldType.Equals(GetType(Int32)) Then
                        Return Convert.ToInt32(originalValue, invariantCulture)
                    End If
                    If fieldType.Equals(GetType([Boolean])) Then
                        Return Convert.ToBoolean(originalValue, invariantCulture)
                    End If
                    If fieldType.Equals(GetType(DateTime)) Then
                        Return Convert.ToDateTime(originalValue, invariantCulture)
                    End If
                    If fieldType.Equals(GetType([Decimal])) Then
                        Return Convert.ToDecimal(originalValue, invariantCulture)
                    End If
                    If fieldType.Equals(GetType([Double])) Then
                        Return Convert.ToDouble(originalValue, invariantCulture)
                    End If
                    If fieldType.Equals(GetType(Int16)) Then
                        Return Convert.ToInt16(originalValue, invariantCulture)
                    End If
                    If fieldType.Equals(GetType(Int64)) Then
                        Return Convert.ToInt64(originalValue, invariantCulture)
                    End If
                    If fieldType.Equals(GetType([Single])) Then
                        Return Convert.ToSingle(originalValue, invariantCulture)
                    End If
                    If fieldType.Equals(GetType([Byte])) Then
                        Return Convert.ToByte(originalValue, invariantCulture)
                    End If
                    If fieldType.Equals(GetType([SByte])) Then
                        Return Convert.ToSByte(originalValue, invariantCulture)
                    End If
                    If fieldType.Equals(GetType(UInt16)) Then
                        Return Convert.ToUInt16(originalValue, invariantCulture)
                    End If
                    If fieldType.Equals(GetType(UInt32)) Then
                        Return Convert.ToUInt32(originalValue, invariantCulture)
                    End If
                    If fieldType.Equals(GetType(UInt64)) Then
                        Return Convert.ToUInt64(originalValue, invariantCulture)
                    End If
                Catch e As Exception
                    Throw New InvalidOperationException(String.Format("Input value '{0}' could not be converted to the type '{1}'.", originalValue, type), e)
                End Try
                'If no match is found return DBNull instead.
                Return DBNull.Value
            End Function
    
    #Region "IDataReader Members"
    
    
           ' Advances the CsvDataReader to the next record.
            ' Returns True if there are more rows; otherwise, False.
            Public Function Read() As Boolean Implements IDataReader.Read
                If _textReader.Peek() > -1 Then
                    ParseDataRow(_textReader.ReadLine())
                Else
                    Return False
                End If
    
                Return True
            End Function
    
    #End Region
    
    #Region "IDisposable Members"
    
    
            ' Releases the resources used by the CsvDataReader.
            Public Sub Dispose() Implements IDisposable.Dispose
                Dispose(True)
                GC.SuppressFinalize(Me)
            End Sub
    
            Private Sub Dispose(disposing As Boolean)
                If disposing Then
                    If _textReader IsNot Nothing Then
                        _textReader.Close()
                    End If
                End If
    
                _typeLookup = Nothing
                _columnLookup = Nothing
                _columns = Nothing
                _currentRow = Nothing
            End Sub
    
    
            ' Allows an Object to attempt to free resources and perform                 
            ' other cleanup operations before the Object is reclaimed by garbage collection.
            Protected Overrides Sub Finalize()
                Try
                    Dispose(False)
                Finally
                    MyBase.Finalize()
                End Try
            End Sub
    
    #End Region
    
    #Region "IDataRecord Members"
    
    
            ' Gets the number of columns in the current row.
            Public ReadOnly Property FieldCount() As Integer Implements IDataRecord.FieldCount
                Get
                    Return _columns.Length
                End Get
            End Property
    
    
            ' The i parameter represents the index of the field to find.
            ' Returns the Type information corresponding to the type of Object that would be returned from GetValue.
            Public Function GetFieldType(i As Integer) As Type Implements IDataReader.GetFieldType
                If i > _columns.Length - 1 Then
                    Return Nothing
                End If
    
                Return DirectCast(_columns.GetValue(i), CsvColumn).DataType
            End Function
    
    
            ' Gets the name for the field to find.
            ' The i parameter represents the index of the field to find. 
            ' Returns the name of the field or an empty string (""), if there is no value to return.
            Public Function GetName(i As Integer) As String Implements IDataRecord.GetName
                If i > _columns.Length - 1 Then
                    Return String.Empty
                End If
    
    
                Return DirectCast(_columns.GetValue(i), CsvColumn).FieldName
            End Function
    
    
            ' The name parameter represents the name of the field to find. 
            ' Returns the index of the named field.
            Public Function GetOrdinal(name As String) As Integer Implements IDataRecord.GetOrdinal
                Dim value As Object = _columnLookup(name)
                If value Is Nothing Then
                    Throw New IndexOutOfRangeException("name")
                End If
                Return CInt(value)
            End Function
    
    
            ' The i parameter represents the index of the field to find. 
            ' Returns the Object which contains the value of the specified field.
            Public Function GetValue(i As Integer) As Object Implements IDataRecord.GetValue
                If i > _columns.Length - 1 Then
                    Return Nothing
                End If
    
                Return _currentRow.GetValue(i)
            End Function
    
            Public Overridable Function GetData(fieldIndex As Integer) As IDataReader Implements IDataReader.GetData
                Throw New NotSupportedException()
            End Function
    
    #End Region
        End Class
    End Namespace
    

    C# code

    C# code. Paste it to replace the default stub in the class.
    Copy Code
    using System;
    using System.Collections;
    using System.Globalization;
    using System.IO;
    using System.Text.RegularExpressions;
    using GrapeCity.ActiveReports.Extensibility.Data;
    
    namespace CustomDataProvider.CSVDataProvider
    {
    
            // Provides an implementation of IDataReader for the .NET Framework CSV Data Provider.
            internal class CsvDataReader : IDataReader
            {
                    //NOTE: HashcodeProvider and Comparer need to be case-insensitive since TypeNames are capitalized differently in places. 
                                    //Otherwise data types end up as strings when using Int32 vs int32.
                    private Hashtable _typeLookup =
                            new Hashtable(StringComparer.Create(CultureInfo.InvariantCulture, false));
                    private Hashtable _columnLookup = new Hashtable();
                    private object[] _columns;
                    private TextReader _textReader;
                    private object[] _currentRow;
    
    
                    //The regular expressions are set to be pre-compiled to make it faster. Since we were concerned about
                    //multi-threading, we made the properties read-only so no one can change any properties on these objects.
                    private static readonly Regex _rxDataRow = new Regex(@",(?=(?:[^""]*""[^""]*"")*(?![^""]*""))", RegexOptions.Compiled);
                            //Used to parse the data rows.
    
                    private static readonly Regex _rxHeaderRow =
                            new Regex(@"(?<fieldName>(\w*\s*)*)\((?<fieldType>\w*)\)", RegexOptions.Compiled); 
                            //Used to parse the header rows.
    
            
                    // Creates a new instance of the CsvDataReader class.
                    // The textReader parameter represents the TextReader to use to read the data.
                    public CsvDataReader(TextReader textReader)
                    {
                            _textReader = textReader;
                            ParseCommandText();
                    }
    
            
                    // Parses the passed-in command text.
                    private void ParseCommandText()
                    {
                            if (_textReader.Peek() == -1)
                                    return; //Command text is empty or at the end already.
    
                            FillTypeLookup();
    
                            string header = _textReader.ReadLine();
                            header = AddDefaultTypeToHeader(header);
    
                            if (!ParseHeader(header))
                                    throw new InvalidOperationException(
                                            "Field names and types are not defined. The first line in the CommandText must contain the field names and data types. e.g FirstName(string)");
                    }
    
                    //A hashtable is used to return a type for the string value used in the header text.
                    private void FillTypeLookup()
                    {
                            _typeLookup.Add("string", typeof (String));
                            _typeLookup.Add("byte", typeof (Byte));
                            _typeLookup.Add("boolean", typeof (Boolean));
                            _typeLookup.Add("datetime", typeof (DateTime));
                            _typeLookup.Add("decimal", typeof (Decimal));
                            _typeLookup.Add("double", typeof (Double));
                            _typeLookup.Add("int16", typeof (Int16));
                            _typeLookup.Add("int32", typeof (Int32));
                            _typeLookup.Add("int", typeof (Int32));
                            _typeLookup.Add("integer", typeof (Int32));
                            _typeLookup.Add("int64", typeof (Int64));
                            _typeLookup.Add("sbyte", typeof (SByte));
                            _typeLookup.Add("single", typeof (Single));
                            _typeLookup.Add("time", typeof (DateTime));
                            _typeLookup.Add("date", typeof (DateTime));
                            _typeLookup.Add("uint16", typeof (UInt16));
                            _typeLookup.Add("uint32", typeof (UInt32));
                            _typeLookup.Add("uint64", typeof (UInt64));
                    }
    
            
                    // Returns a type based on the string value passed in from the header text string. If no match is found, a string type is returned.
                    // The fieldType parameter represents the String value from the header command text string.
                    private Type GetFieldTypeFromString(string fieldType)
                    {
                            if (_typeLookup.Contains(fieldType))
                                    return _typeLookup[fieldType] as Type;
                            return typeof (String);
                    }
    
            
                    // Parses the first line in the passed-in command text string to create the field names and field data types. The field information
                    // is stored in a CsvColumn struct, and these column info items are stored in an ArrayList. The column name is also added
                    // to a hashtable for easy lookup later.
            
                    // The header parameter represents the header string that contains all the fields.
                    // Returns True if it can parse the header string; otherwise False.
                    private bool ParseHeader(string header)
                    {
                            string fieldName;
                            int index = 0;
                            if (header.IndexOf("(") == -1)
                                    return false;
    
                            MatchCollection matches = _rxHeaderRow.Matches(header);
                            _columns = new object[matches.Count];
                            foreach (Match match in matches)
                            {
                                    fieldName = match.Groups["fieldName"].Value;
                                    Type fieldType = GetFieldTypeFromString(match.Groups["fieldType"].Value);
                                    _columns.SetValue(new CsvColumn(fieldName, fieldType), index);
                                    _columnLookup.Add(fieldName, index);
                                    index++;
                            }
    
                            return true;
                    }
    
            
                    // Ensures that the header contains columns in the form of name(type)
                    // The line parameter represents the raw header line from the file to fix up.
                    // Returns a modified header with default types appended to column names.
                    private static string AddDefaultTypeToHeader(string line)
                    {
                            const string ColumnWithDataTypeRegex = @"[""]?\w+[\""]?\(.+\)";
                            string[] columns = line.Split(new string[] { "," }, StringSplitOptions.None);
                            string ret = null;
                            foreach (string column in columns)
                            {
                                    if (!string.IsNullOrEmpty(ret))
                                            ret += ",";
                                    if (!Regex.Match(column, ColumnWithDataTypeRegex).Success)
                                    {
                                            ret += column + "(string)";
                                    }
                                    else
                                    {
                                            ret += column;
                                    }
                            }
                            return ret;
                    }
    
            
                    // Parses a row of data using a regular expression and stores the information inside an object array that is the current row of data.
                    // If the row does not have the correct number of fields, an exception is raised.
                    // The dataRow parameter represents the String value representing a comma delimited data row.
                    // Returns True if it can parse the data string; otherwise False.
                    private bool ParseDataRow(string dataRow)
                    {
                            int index = 0;
                            string[] tempData = _rxDataRow.Split(dataRow);
    
                            _currentRow = new object[tempData.Length];
                            if (tempData.Length != _columns.Length)
                            {
                                    string error =
                                            string.Format(CultureInfo.InvariantCulture,
                                                          "Invalid row \"{0}\". The row does not contain the same number of data columns as the table header definition.",
                                                          dataRow);
                                    throw new InvalidOperationException(error);
                            }
                            for (int i = 0; i < tempData.Length; i++)
                            {
                                    string value = tempData[i];
    
                                    if (value.Length > 1)
                                    {
                                            if (value.IndexOf('"', 0) == 0 && value.IndexOf('"', 1) == value.Length - 1)
                                                    value = value.Substring(1, value.Length - 2);
                                    }
                                    _currentRow.SetValue(ConvertValue(GetFieldType(index), value), index);
                                    index++;
                            }
                            return true;
                    }
    
            
                    // Coverts the string value coming from the command text to the appropriate data type, based on the field's type.
                    // This also checks a few string value rules to decide if a String.Empty of System.Data.DBNull needs to be returned.
                    // The type parameter represents the Type of the current column the data belongs to.
                    // The originalValue parameter represents the String value coming from the command text.
                    // Returns the object resulting from the converted string, based on the type.
                    private object ConvertValue(Type type, string originalValue)
                    {
                            Type fieldType = type;
                            CultureInfo invariantCulture = CultureInfo.InvariantCulture;
                            try
                            {
                                    if (originalValue == "\"\"" || originalValue == " ")
                                            return string.Empty;
                                    if (originalValue == "")
                                            return DBNull.Value;
                                    if (originalValue == "DBNull")
                                            return DBNull.Value;
                                    if (fieldType.Equals(typeof (String)))
                                            return originalValue.Trim();
                                    if (fieldType.Equals(typeof (Int32)))
                                            return Convert.ToInt32(originalValue, invariantCulture);
                                    if (fieldType.Equals(typeof (Boolean)))
                                            return Convert.ToBoolean(originalValue, invariantCulture);
                                    if (fieldType.Equals(typeof (DateTime)))
                                            return Convert.ToDateTime(originalValue, invariantCulture);
                                    if (fieldType.Equals(typeof (Decimal)))
                                            return Convert.ToDecimal(originalValue, invariantCulture);
                                    if (fieldType.Equals(typeof (Double)))
                                            return Convert.ToDouble(originalValue, invariantCulture);
                                    if (fieldType.Equals(typeof (Int16)))
                                            return Convert.ToInt16(originalValue, invariantCulture);
                                    if (fieldType.Equals(typeof (Int64)))
                                            return Convert.ToInt64(originalValue, invariantCulture);
                                    if (fieldType.Equals(typeof (Single)))
                                            return Convert.ToSingle(originalValue, invariantCulture);
                                    if (fieldType.Equals(typeof (Byte)))
                                            return Convert.ToByte(originalValue, invariantCulture);
                                    if (fieldType.Equals(typeof (SByte)))
                                            return Convert.ToSByte(originalValue, invariantCulture);
                                    if (fieldType.Equals(typeof (UInt16)))
                                            return Convert.ToUInt16(originalValue, invariantCulture);
                                    if (fieldType.Equals(typeof (UInt32)))
                                            return Convert.ToUInt32(originalValue, invariantCulture);
                                    if (fieldType.Equals(typeof (UInt64)))
                                            return Convert.ToUInt64(originalValue, invariantCulture);
                            }
                            catch (Exception e)
                            {
                                    throw new InvalidOperationException(
                                            string.Format("Input value '{0}' could not be converted to the type '{1}'.", originalValue, type), e);
                            }
                            //If no match is found return DBNull instead.
                            return DBNull.Value;
                    }
    
                    #region IDataReader Members
    
            
                    // Advances the CsvDataReader to the next record.
                    // Returns True if there are more rows; otherwise, False.
                    public bool Read()
                    {
                            if (_textReader.Peek() > -1)
                                    ParseDataRow(_textReader.ReadLine());
                            else
                                    return false;
    
                            return true;
                    }
    
                    #endregion
    
                    #region IDisposable Members
    
            
                    // Releases the resources used by the CsvDataReader.
                    public void Dispose()
                    {
                            Dispose(true);
                            GC.SuppressFinalize(this);
                    }
    
                    private void Dispose(bool disposing)
                    {
                            if (disposing)
                            {
                                    if (_textReader != null)
                                            _textReader.Close();
                            }
    
                            _typeLookup = null;
                            _columnLookup = null;
                            _columns = null;
                            _currentRow = null;
                    }
    
            
                    // Allows an Object to attempt to free resources and perform other cleanup operations before the Object is reclaimed by garbage collection.
                    ~CsvDataReader()
                    {
                            Dispose(false);
                    }
    
                    #endregion
    
                    #region IDataRecord Members
    
            
                    // Gets the number of columns in the current row.
                    public int FieldCount
                    {
                            get { return _columns.Length; }
                    }
    
            
                    // The i parameter represents the index of the field to find.
                    // Returns the Type information corresponding to the type of Object that would be returned from GetValue.
                    public Type GetFieldType(int i)
                    {
                            if (i > _columns.Length - 1)
                                    return null;
    
                            return ((CsvColumn) _columns.GetValue(i)).DataType;
                    }
    
            
                    // Gets the name for the field to find.
                    // The i parameter represents the index of the field to find. 
                    // Returns the name of the field or an empty string (""), if there is no value to return.
                    public string GetName(int i)
                    {
                            if (i > _columns.Length - 1)
                                    return string.Empty;
    
    
                            return ((CsvColumn) _columns.GetValue(i)).FieldName;
                    }
    
            
                    // The name parameter represents the name of the field to find. 
                    // Returns the index of the named field.
                    public int GetOrdinal(string name)
                    {
                            object value = _columnLookup[name];
                            if (value == null)
                                    throw new IndexOutOfRangeException("name");
                            return (int) value;
                    }
    
            
                    // The i parameter represents the index of the field to find. 
                    // Returns the Object which contains the value of the specified field.
                    public object GetValue(int i)
                    {
                            if (i > _columns.Length - 1)
                                    return null;
    
                            return _currentRow.GetValue(i);
                    }
    
                    public virtual IDataReader GetData(int fieldIndex)
                    {
                            throw new NotSupportedException();
                    }
    
                    #endregion
            }
    }
    
  8. Right-click the CSVDataProvider folder and select Add, then Class, then name the class CsvCommand and add code like the following to replace the default stub in the class.

    Visual Basic code

    Visual Basic code. Paste it to replace the default stub in the class.
    Copy Code
    Imports System
    Imports System.IO
    Imports GrapeCity.ActiveReports.Extensibility.Data
    
    Namespace CSVDataProvider
    
        ' Provides the IDbCommand implementation for the .NET Framework CSV Data Provider.
        Public NotInheritable Class CsvCommand
            Implements IDbCommand
            Private _commandText As String
            Private _connection As IDbConnection
            Private _commandTimeout As Integer
            Private _commandType As CommandType
    
    
            ' Creates a new instance of the CsvCommand class.
            Public Sub New()
                Me.New(String.Empty)
            End Sub
    
    
            ' Creates a new instance of the CsvCommand class with command text.
            ' The commandText parameter represents the command text.
            Public Sub New(commandText As String)
                Me.New(commandText, Nothing)
            End Sub
    
    
            ' Creates a new instance of the CsvCommand class with command text and a CsvConnection.
            ' The commandText parameter represents the command text.
            ' The connection parameter represents a CsvConnection to a data source.
            Public Sub New(commandText As String, connection As CsvConnection)
                _commandText = commandText
                _connection = connection
            End Sub
    
    
            ' Gets or sets the command to execute at the data source.
            Public Property CommandText() As String Implements IDbCommand.CommandText
                Get
                    Return _commandText
                End Get
                Set(value As String)
                    _commandText = value
                End Set
            End Property
    
    
            ' Gets or sets the wait time before terminating an attempt to execute the command and generating an error.
            Public Property CommandTimeout() As Integer Implements IDbCommand.CommandTimeout
                Get
                    Return _commandTimeout
                End Get
    
                Set(value As Integer)
                    _commandTimeout = value
                End Set
            End Property
    
    
            ' Gets or sets a value indicating how the CommandText property is interpreted.
            ' Remarks: We don't use this one for the Csv Data Provider.
            Public Property CommandType() As CommandType Implements IDbCommand.CommandType
                Get
                    Return _commandType
                End Get
    
                Set(value As CommandType)
                    _commandType = value
                End Set
            End Property
    
    
            ' Gets or sets the CsvConnection used by this instance of the CsvCommand.
            Public Property Connection() As IDbConnection
                Get
                    Return _connection
                End Get
    
                Set(value As IDbConnection)
                    _connection = value
                End Set
            End Property
    
    
           ' Sends the CommandText to the CsvConnection, and builds a CsvDataReader using one of the CommandBehavior values.
            ' The behavior parameter represents a CommandBehavior value.
            ' Returns a CsvDataReader object.
            Public Function ExecuteReader(behavior As CommandBehavior) As IDataReader Implements IDbCommand.ExecuteReader
                Return New CsvDataReader(New StringReader(_commandText))
            End Function
    
    
            ' Returns a string that represents the command text with the parameters expanded into constants.
            Public Function GenerateRewrittenCommandText() As String Implements IDbCommand.GenerateRewrittenCommandText
                Return _commandText
            End Function
    
    
            ' Sends the CommandText to the CsvConnection and builds a CsvDataReader.
            ' Returns a CsvDataReader object.
            Public Function ExecuteReader() As IDataReader Implements IDbCommand.ExecuteReader
                Return ExecuteReader(CommandBehavior.SchemaOnly)
            End Function
    
    #Region "Non implemented IDbCommand Members"
    
            Public ReadOnly Property Parameters() As IDataParameterCollection Implements IDbCommand.Parameters
                Get
                    Throw New NotImplementedException()
                End Get
            End Property
    
            Public Property Transaction() As IDbTransaction Implements IDbCommand.Transaction
                Get
                    Throw New NotImplementedException()
                End Get
    
                Set(value As IDbTransaction)
                    Throw New NotImplementedException()
                End Set
            End Property
    
            Public Sub Cancel() Implements IDbCommand.Cancel
    
            End Sub
    
            Public Function CreateParameter() As IDataParameter Implements IDbCommand.CreateParameter
                Throw New NotImplementedException()
            End Function
    
    #End Region
    
    #Region "IDisposable Members"
    
    
            ' Releases the resources used by the CsvCommand.
            Public Sub Dispose() Implements IDisposable.Dispose
                Dispose(True)
                GC.SuppressFinalize(Me)
            End Sub
    
    
            Private Sub Dispose(disposing As Boolean)
                If disposing Then
                    If _connection IsNot Nothing Then
                        _connection.Dispose()
                        _connection = Nothing
                    End If
                End If
            End Sub
    
    #End Region
        End Class
    End Namespace
    

    C# code

    C# code. Paste it to replace the default stub in the class.
    Copy Code
    using System;
    using System.IO;
    using GrapeCity.ActiveReports.Extensibility.Data;
    
    namespace CustomDataProvider.CSVDataProvider
    {
    
           // Provides the IDbCommand implementation for the .NET Framework CSV Data Provider.
            public sealed class CsvCommand : IDbCommand
            {
                    private string _commandText;
                    private IDbConnection _connection;
                    private int _commandTimeout;
                    private CommandType _commandType;
    
            
                    /// Creates a new instance of the CsvCommand class.
                    public CsvCommand()
                            : this(string.Empty)
                    {
                    }
    
            
                    // Creates a new instance of the CsvCommand class with command text.
                    // The commandText parameter represents the command text.
                    public CsvCommand(string commandText)
                            : this(commandText, null)
                    {
                    }
    
            
                    // Creates a new instance of the CsvCommand class with command text and a CsvConnection.
                    // The commandText parameter represents the command text.
                    // The connection parameter represents a CsvConnection to a data source.?
                    public CsvCommand(string commandText, CsvConnection connection)
                    {
                            _commandText = commandText;
                            _connection = connection;
                    }
    
            
                    // Gets or sets the command to execute at the data source.
                    public string CommandText
                    {
                            get { return _commandText; }
                            set { _commandText = value; }
                    }
    
            
                    // Gets or sets the wait time before terminating an attempt to execute the command and generating an error.
                    public int CommandTimeout
                    {
                            get { return _commandTimeout; }
    
                            set { _commandTimeout = value; }
                    }
    
            
                    // Gets or sets a value indicating how the CommandText property is interpreted.
                   // Remarks: We don't use this one for the Csv Data Provider.
                    public CommandType CommandType
                    {
                            get { return _commandType; }
    
                            set { _commandType = value; }
                    }
    
            
                   // Gets or sets the CsvConnection used by this instance of the CsvCommand.
                    public IDbConnection Connection
                    {
                            get { return _connection; }
    
                            set { _connection = value; }
                    }
    
            
                    // Sends the CommandText to the CsvConnection, and builds a CsvDataReader using one of the CommandBehavior values.
                    // The behavior parameter represents a CommandBehavior value.
                    // Returns a CsvDataReader object.
                    public IDataReader ExecuteReader(CommandBehavior behavior)
                    {
                            return new CsvDataReader(new StringReader(_commandText));
                    }
    
            
                   // Returns a string that represents the command text with the parameters expanded into constants.
                    public string GenerateRewrittenCommandText()
                    {
                            return _commandText;
                    }
    
            
                    // Sends the CommandText to the CsvConnection and builds a CsvDataReader.
                    // Returns a CsvDataReader object.
                    public IDataReader ExecuteReader()
                    {
                            return ExecuteReader(CommandBehavior.SchemaOnly);
                    }
    
                    #region Non implemented IDbCommand Members
    
                    public IDataParameterCollection Parameters
                    {
                            get { throw new NotImplementedException(); }
                    }
    
                    public IDbTransaction Transaction
                    {
                            get { throw new NotImplementedException(); }
    
                            set { throw new NotImplementedException(); }
                    }
    
                    public void Cancel()
                    {
                            
                    }
    
                    public IDataParameter CreateParameter()
                    {
                            throw new NotImplementedException();
                    }
    
                    #endregion
    
                    #region IDisposable Members
    
            
                    // Releases the resources used by the CsvCommand.
                    public void Dispose()
                    {
                            Dispose(true);
                            GC.SuppressFinalize(this);
                    }
    
    
                    private void Dispose(bool disposing)
                    {
                            if (disposing)
                            {
                                    if (_connection != null)
                                    {
                                            _connection.Dispose();
                                            _connection = null;
                                    }
                            }
                    }
    
                    #endregion
            }
    }
    
  9. Right-click the CSVDataProvider folder and select Add, then Class, then name the class CsvConnection and add code like the following to replace the default stub in the class. (You can safely ignore the errors, as they will go away when you add the CsvConnection class.)   

    Visual Basic code

    Visual Basic code. Paste it to replace the default stub in the class.
    Copy Code
    Imports System
    Imports System.Collections.Specialized
    Imports GrapeCity.ActiveReports.Extensibility.Data
    
    Namespace CSVDataProvider
    
        ' Provides an implementation of IDbConnection for the .NET Framework CSV Data Provider.
        Public NotInheritable Class CsvConnection
            Implements IDbConnection
            Private _localizedName As String
    
            ' Creates a new instance of the CsvConnection class.
            Public Sub New()
                _localizedName = "Csv"
            End Sub
    
           ' Creates a new instance of the CsvConnection class.
           ' The localizedName parameter represents the localized name for the CsvConnection instance.
            Public Sub New(localizeName As String)
                _localizedName = localizeName
            End Sub
    
    #Region "IDbConnection Members"
    
            ' Gets or sets the string used to open the connection to the data source.
            ' Remarks: We don't use this one for the Csv Data Provider.
            Public Property ConnectionString() As String Implements IDbConnection.ConnectionString
                Get
                    Return String.Empty
                End Get
    
                Set(value As String)
    
    
                End Set
            End Property
    
            ' Gets the amount of time to wait while trying to establish a connection before terminating 
            ' the attempt and generating an error.
            ' Remarks: We don't use this one for the Csv Data Provider.
            Public ReadOnly Property ConnectionTimeout() As Integer Implements IDbConnection.ConnectionTimeout
                Get
                    Throw New NotImplementedException()
                End Get
            End Property
    
            ' Begins a data source transaction.   
            ' Returns an object representing the new transaction.
            ' Remarks: We don't use this one for the Csv Data Provider.
            Public Function BeginTransaction() As IDbTransaction Implements IDbConnection.BeginTransaction
                Return Nothing
            End Function
    
            ' Opens a data source connection.     
            ' Remarks: We don't use this one for the Csv Data Provider.
            Public Sub Open() Implements IDbConnection.Open
    
    
            End Sub
    
            ' Closes the connection to the data source. This is the preferred method of closing any open connection.
            Public Sub Close() Implements IDbConnection.Close
                Dispose()
            End Sub
    
            ' Creates and returns a CsvCommand object associated with the CsvConnection.
            Public Function CreateCommand() As IDbCommand Implements IDbConnection.CreateCommand
                Return New CsvCommand(String.Empty)
            End Function
    
            Public Property DataProviderService() As IDataProviderService Implements IDbConnection.DataProviderService
                Get
                    Return Nothing
                End Get
                Set(value As IDataProviderService)
                End Set
            End Property
    
    #End Region
    
    #Region "IDisposable Members"
    
            ' Releases the resources used by the CsvConnection.
            Public Sub Dispose() Implements IDisposable.Dispose
                Dispose(True)
                GC.SuppressFinalize(Me)
            End Sub
    
            Private Sub Dispose(disposing As Boolean)
            End Sub
    
           ' Allows an Object to attempt to free resources and perform other cleanup operations 
            ' before the Object is reclaimed by garbage collection.
            Protected Overrides Sub Finalize()
                Try
                    Dispose(False)
                Finally
                    MyBase.Finalize()
                End Try
            End Sub
    
    #End Region
    
    #Region "IExtension Members"
    
           ' Gets the localized name of the CsvConnection.
            Public ReadOnly Property LocalizedName() As String Implements IDbConnection.LocalizedName
                Get
                    Return _localizedName
                End Get
            End Property
    
           ' Specifies any configuration information for this extension.
           ' The configurationSettings parameter represents a NameValueCollection of the settings.
            Public Sub SetConfiguration(configurationSettings As NameValueCollection) Implements IDbConnection.SetConfiguration
            End Sub
    
    #End Region
        End Class
    End Namespace
    

    C# code

    C# code. Paste it to replace the default stub in the class.
    Copy Code
    using System;
    using System.Collections.Specialized;
    using GrapeCity.ActiveReports.Extensibility.Data;
    
    namespace CustomDataProvider.CSVDataProvider
    {
    
            // Provides an implementation of IDbConnection for the .NET Framework CSV Data Provider.
            public sealed class CsvConnection : IDbConnection
            {
                    private string _localizedName;
    
            
                    // Creates a new instance of the CsvConnection class.
                    public CsvConnection()
                    {
                            _localizedName = "Csv";
                    }
    
            
                    // Creates a new instance of the CsvConnection class.
                    // The localizedName parameter represents the localized name for the CsvConnection instance.
                    public CsvConnection(string localizeName)
                    {
                            _localizedName = localizeName;
                    }
    
                    #region IDbConnection Members
    
            
                   // Gets or sets the string used to open the connection to the data source.
                   // Remarks: We don't use this one for the Csv Data Provider.
                    public string ConnectionString
                    {
                            get { return string.Empty; }
    
                            set { ; }
                    }
    
            
                    // Gets the amount of time to wait while trying to establish a connection before terminating the attempt and generating an error.
                    // Remarks: We don't use this one for the Csv Data Provider.
                    public int ConnectionTimeout
                    {
                            get { throw new NotImplementedException(); }
                    }
    
            
                   // Begins a data source transaction.   
                   // Returns an object representing the new transaction.
                   // Remarks: We don't use this one for the Csv Data Provider.
                    public IDbTransaction BeginTransaction()
                    {
                            return null;
                    }
    
            
                   // Opens a data source connection.     
                   // Remarks: We don't use this one for the Csv Data Provider.
                    public void Open()
                    {
                            ;
                    }
    
            
                    // Closes the connection to the data source. This is the preferred method of closing any open connection.
                    public void Close()
                    {
                            Dispose();
                    }
    
            
                   // Creates and returns a CsvCommand object associated with the CsvConnection.
                    public IDbCommand CreateCommand()
                    {
                            return new CsvCommand(string.Empty);
                    }
    
                    public IDataProviderService DataProviderService
                    {
                            get { return null; }
                            set { }
                    }
    
                    #endregion
    
                    #region IDisposable Members
    
            
                   // Releases the resources used by the CsvConnection.
                    public void Dispose()
                    {
                            Dispose(true);
                            GC.SuppressFinalize(this);
                    }
    
                    private void Dispose(bool disposing)
                    {
                    }
    
            
                    // Allows an Object to attempt to free resources and perform other cleanup operations before the Object is reclaimed by garbage collection.
                    ~CsvConnection()
                    {
                            Dispose(false);
                    }
    
                    #endregion
    
                    #region IExtension Members
    
            
                    // Gets the localized name of the CsvConnection.
                    public string LocalizedName
                    {
                            get { return _localizedName; }
                    }
    
            
                    // Specifies any configuration information for this extension.
                    // The configurationSettings parameter represents a NameValueCollection of the settings.
                    public void SetConfiguration(NameValueCollection configurationSettings)
                    {
                    }
    
                    #endregion
            }
    }
    
  10. Right-click the CSVDataProvider folder and select Add, then Class, then name the class CsvDataProviderFactory and add code like the following to replace the default stub in the class.

    Visual Basic code

    Visual Basic code. Paste it to replace the default stub in the class.
    Copy Code
    Imports GrapeCity.ActiveReports.Extensibility.Data
    Imports GrapeCity.BI.Data.DataProviders
    
    Namespace CSVDataProvider
    
           ' Implements the DataProviderFactory for .NET Framework CSV Data Provider.
            Public Class CsvDataProviderFactory
                    Inherits DataProviderFactory
    
                    ' Creates new instance of the CsvDataProviderFactory class.
                    Public Sub New()
                    End Sub
    
                   ' Returns a new instance of the the CsvCommand.
                    Public Overrides Function CreateCommand() As IDbCommand
                            Return New CsvCommand()
                    End Function
    
                    ' Returns a new instance of the the CsvConnection.
                    Public Overrides Function CreateConnection() As IDbConnection
                            Return New CsvConnection()
                    End Function
            End Class
    End Namespace
    

    C# code

    C# code. Paste it to replace the default stub in the class.
    Copy Code
    using GrapeCity.ActiveReports.Extensibility.Data;
    using GrapeCity.BI.Data.DataProviders;
    
    namespace CustomDataProvider.CSVDataProvider
    {
    
           // Implements the DataProviderFactory for .NET Framework CSV Data Provider.
            public class CsvDataProviderFactory : DataProviderFactory
            {
            
                    // Creates new instance of the CsvDataProviderFactory class.
                    public CsvDataProviderFactory()
                    {
                    }
            
                   // Returns a new instance of the the CsvCommand.
                    public override IDbCommand CreateCommand()
                    {
                            return new CsvCommand();
                    }
            
                   // Returns a new instance of the the CsvConnection.
                    public override IDbConnection CreateConnection()
                    {
                            return new CsvConnection();
                    }
            }
    }
    

To add a button to the query editor

  1. In the Solution Explorer, right-click the CSVDataProvider folder and select Add, then Class, then name the class QueryEditor and add code like the following to replace the default stub in the class.

    Visual Basic code

    Visual Basic code. Paste it to replace the default stub in the class.
    Copy Code

    Imports System.Collections.Generic
    Imports System.Drawing.Design
    Imports System.IO
    Imports System.Linq
    Imports System.Text
    Imports System.Text.RegularExpressions
    Imports System.Windows.Forms
    Imports System.Windows.Forms.Design


    Namespace CustomDataProvider.CSVDataProvider
        Public NotInheritable Class QueryEditor
            Inherits UITypeEditor
            Public Overrides Function GetEditStyle(context As System.ComponentModel.ITypeDescriptorContext) As UITypeEditorEditStyle
                Return UITypeEditorEditStyle.DropDown
            End Function
            Public Overrides Function EditValue(context As System.ComponentModel.ITypeDescriptorContext, provider As System.IServiceProvider, value As Object) As Object
                Dim edSvc As IWindowsFormsEditorService = DirectCast(provider.GetService(GetType(IWindowsFormsEditorService)), IWindowsFormsEditorService)
                Dim path = ""
                Dim btn = New Button()
                btn.Text = "Select CSV File..."
                Dim pdg = btn.Padding
                pdg.Bottom += 2
                btn.Padding = pdg
                btn.Click += Sub() Using openDlg = New OpenFileDialog()
                    openDlg.Filter = "CSV Files (*.csv)|*.csv|All Files (*.*)|*.*"
                    If openDlg.ShowDialog() <> DialogResult.OK Then
                        path = ""
                    Else
                        path = openDlg.FileName
                    End If
                End Using
                edSvc.DropDownControl(btn)
                If String.IsNullOrEmpty(path) Then
                    Return String.Empty
                End If
                If Not File.Exists(path) Then
                    Return String.Empty
                End If
                Return GetCSVQuery(path)
            End Function

            Private Function GetCSVQuery(path As String) As Object
                Dim sr As StreamReader = Nothing
                Try
                    sr = New StreamReader(path)
                    Dim ret As String = String.Empty
                    Dim currentLine As String
                    Dim line As Integer = 0
                    While (InlineAssignHelper(currentLine, sr.ReadLine())) IsNot Nothing
                        If line = 0 Then
                            ret += ProcessColumnsDefinition(currentLine) & Convert.ToString(vbCr & vbLf)
                        Else
                            ret += currentLine & Convert.ToString(vbCr & vbLf)
                        End If
                        line += 1
                    End While
                    Return ret
                Catch generatedExceptionName As IOException
                    Return String.Empty
                Finally
                    If sr IsNot Nothing Then
                        sr.Close()
                    End If
                End Try
            End Function
            Private Function ProcessColumnsDefinition(currentLine As String) As String
                Const  ColumnWithDataTypeRegex As String = "[""]?\w+[\""]?\(.+\)"
                Dim columns As String() = currentLine.Split(New String() {","}, StringSplitOptions.None)
                Dim ret As String = Nothing
                For Each column As String In columns
                    If Not String.IsNullOrEmpty(ret) Then
                        ret += ","
                    End If
                    If Not Regex.Match(column, ColumnWithDataTypeRegex).Success Then
                        ret += column & Convert.ToString("(string)")
                    Else
                        ret += column
                    End If
                Next
                Return ret
            End Function
            Private Shared Function InlineAssignHelper(Of T)(ByRef target As T, value As T) As T
                target = value
                Return value
            End Function
        End Class
    End Namespace

    C# code

    C# code. Paste it to replace the default stub in the class.
    Copy Code

    using System;
    using System.Collections.Generic;
    using System.Drawing.Design;
    using System.IO;
    using System.Linq;
    using System.Text;
    using System.Text.RegularExpressions;
    using System.Windows.Forms;
    using System.Windows.Forms.Design;
    namespace CustomDataProvider.CSVDataProvider
    {
        public sealed class QueryEditor : UITypeEditor
        {
            public override UITypeEditorEditStyle GetEditStyle(System.ComponentModel.ITypeDescriptorContext context)
            {
                return UITypeEditorEditStyle.DropDown;
            }

            public override object EditValue(System.ComponentModel.ITypeDescriptorContext context, System.IServiceProvider provider, object value)
            {
                IWindowsFormsEditorService edSvc = (IWindowsFormsEditorService)provider.GetService(typeof(IWindowsFormsEditorService));
                var path = "";
                var btn = new Button();
                btn.Text = "Select CSV File...";
                var pdg = btn.Padding;
                pdg.Bottom += 2;
                btn.Padding = pdg;
                btn.Click += delegate
                {
                    using (var openDlg = new OpenFileDialog())
                    {
                        openDlg.Filter = "CSV Files (*.csv)|*.csv|All Files (*.*)|*.*";
                        if (openDlg.ShowDialog() != DialogResult.OK)
                            path = "";
                        else
                            path = openDlg.FileName;
                    }
                };
                edSvc.DropDownControl(btn);
                if (string.IsNullOrEmpty(path)) return string.Empty;
                if (!File.Exists(path)) return string.Empty;
                return GetCSVQuery(path);
            }
            private object GetCSVQuery(string path)
            {
                StreamReader sr = null;
                try
                {
                    sr = new StreamReader(path);
                    string ret = string.Empty;
                    string currentLine;
                    int line = 0;
                    while ((currentLine = sr.ReadLine()) != null)
                    {
                        if (line == 0)
                            ret += ProcessColumnsDefinition(currentLine) + "\r\n";
                        else
                            ret += currentLine + "\r\n";
                        line++;
                    }
                    return ret;
                }
                catch (IOException)
                {
                    return string.Empty;
                }
                finally
                {
                    if (sr != null)
                        sr.Close();
                }
            }
            private string ProcessColumnsDefinition(string currentLine)
            {
                const string ColumnWithDataTypeRegex = @"[""]?\w+[\""]?\(.+\)";
                string[] columns = currentLine.Split(new string[] { "," }, StringSplitOptions.None);
                string ret = null;
                foreach (string column in columns)
                {
                    if (!string.IsNullOrEmpty(ret))
                        ret += ",";
                    if (!Regex.Match(column, ColumnWithDataTypeRegex).Success)
                    {
                        ret += column + "(string)";
                    }
                    else
                    {
                        ret += column;
                    }
                }
                return ret;
            }
        }
    }

  2. In the Solution Explorer, right-click the CustomDataProviderDemo project and select Add Reference. In the Reference Manager dialog that appears, on the Projects tab, select CustomDataProvider and click OK.
  3. Run the project, and follow the instructions in the RichTextBox to see the custom data provider in action.