You can use the IDataProviderFactory interface to connect to an ADO.NET data provider like SQLite or SQLCE.

To connect to the database

These steps assume that your project already contains a PivotView control. For details on how to add the Pivot View control, see Add Controls to Your Toolbox.

Create an ADO.NET data provider which implements the IDataProviderFactory interface and set the command, connection and command timeout in code.

Show To write the code in Visual Basic.NET

Visual Basic.NET code. Create a data provider class.	Copy Code
Private Class SQLiteDataProviderFactory Implements IDataProviderFactory Public Function CreateCommand() As System.Data.IDbCommand Implements IDataProviderFactory.CreateCommand Return New System.Data.SQLite.SQLiteCommand() End Function Public Function CreateConnection() As System.Data.IDbConnection Implements IDataProviderFactory.CreateConnection Return New System.Data.SQLite.SQLiteConnection() End Function Public ReadOnly Property CommandTimeout As Integer Implements IDataProviderFactory.CommandTimeout Get Return 30 End Get End Property End Class

Visual Basic.NET code. Create a data provider class.

Copy Code

Private Class SQLiteDataProviderFactory
Implements IDataProviderFactory

Public Function CreateCommand() As System.Data.IDbCommand Implements IDataProviderFactory.CreateCommand

Return New System.Data.SQLite.SQLiteCommand()
End Function

Public Function CreateConnection() As System.Data.IDbConnection Implements IDataProviderFactory.CreateConnection

Return New System.Data.SQLite.SQLiteConnection()
End Function

            Public ReadOnly Property CommandTimeout As Integer Implements IDataProviderFactory.CommandTimeout
                Get
                    Return 30
                End Get
            End Property

End Class

Show To write the code in C#

C# code. Create a data provider class.	Copy Code
private class SQLiteDataProviderFactory : IDataProviderFactory { public System.Data.IDbCommand CreateCommand() { return new System.Data.SQLite.SQLiteCommand(); } public System.Data.IDbConnection CreateConnection() { return new System.Data.SQLite.SQLiteConnection(); } public int CommandTimeout { get { return 30; } } }

C# code. Create a data provider class.

Copy Code

private class SQLiteDataProviderFactory : IDataProviderFactory
{

   public System.Data.IDbCommand CreateCommand()
   {
    return new System.Data.SQLite.SQLiteCommand();
   }

   public System.Data.IDbConnection CreateConnection()
   {
    return new System.Data.SQLite.SQLiteConnection();
   }

   public int CommandTimeout
   {
    get { return 30; }
   }

}

Create a function Connect() to register the data provider created above and then create an ADO connection to the relational data source.

Show To write the code in Visual Basic.NET

Visual Basic.NET code. Paste the code below in the Connect function.	Copy Code
Private Sub Connect() If _rdDataSource.IsConnected Then _rdDataSource.Disconnect() End If ' Register the data provider DataSourceFactory.RegisterADODataProviderFactory(New SQLiteDataProviderFactory()) ' Set up connection parameters _rdDataSource.ConnectionType = ConnectionType.ADO _rdDataSource.ConnectionString = "Data Source=" & MyDocumentsPath & "\GrapeCity\ActiveAnalysis\DataSources\NWind\NWIND.db" _rdDataSource.QueryString = "SELECT c.CategoryName, p.ProductName, o.OrderID, o.OrderDate, d.UnitPrice, d.Quantity, s.Country AS SupplierCountry, s.City AS SupplierCity, s.CompanyName AS SupplierCompany, m.Country AS CustomerCountry, m.City AS CustomerCity, m.CompanyName AS CustomerCompany, d.Discount, m.Region AS CustomerRegion, s.Region AS SupplierRegion, e.LastName, e.EmployeeID AS EmployeeID, e.Title AS EmployeeTitle, e.Region AS EmployeeRegion, e.Country AS EmployeeCountry, e.City AS EmployeeCity, o.Freight, o.ShipName, o.ShipVia, o.ShipCity, o.ShipRegion, o.ShipCountry FROM Suppliers AS s, Categories AS c,Products AS p,Employees AS e ,Customers AS m,Orders AS o,Order_Details AS d WHERE c.CategoryID = p.CategoryID And s.SupplierID = p.SupplierID And o.OrderID = d.OrderID And m.CustomerID = o.CustomerID AND e.EmployeeID = o.EmployeeID AND p.ProductID = d.ProductID" _rdDataSource.CustomSchemaFile = MyDocumentsPath & "\GrapeCity\ActiveAnalysis\DataSources\NWind\NwindMDB-SalesProductsSuppliers.schema" ' Connect to the datasource _rdDataSource.Connect() ' Install the data source _pivotView.DataSource = _rdDataSource _pivotView.Read(New FileInfo(MyDocumentsPath & "\GrapeCity\ActiveAnalysis\Layouts\NWind\Products' Orders per Year.analysis"), PersistSettings.Layout) End Sub

Visual Basic.NET code. Paste the code below in the Connect function.

Copy Code

Private Sub Connect()
    If _rdDataSource.IsConnected Then
         _rdDataSource.Disconnect()
    End If

   ' Register the data provider
   DataSourceFactory.RegisterADODataProviderFactory(New SQLiteDataProviderFactory())

    ' Set up connection parameters
    _rdDataSource.ConnectionType = ConnectionType.ADO
    _rdDataSource.ConnectionString = "Data Source=" & MyDocumentsPath & "\GrapeCity\ActiveAnalysis\DataSources\NWind\NWIND.db"
    _rdDataSource.QueryString = "SELECT c.CategoryName, p.ProductName, o.OrderID, o.OrderDate, d.UnitPrice, d.Quantity, s.Country AS SupplierCountry, s.City AS SupplierCity, s.CompanyName AS SupplierCompany, m.Country AS CustomerCountry, m.City AS CustomerCity, m.CompanyName AS CustomerCompany, d.Discount, m.Region AS CustomerRegion, s.Region AS SupplierRegion, e.LastName, e.EmployeeID AS EmployeeID, e.Title AS EmployeeTitle, e.Region AS EmployeeRegion, e.Country AS EmployeeCountry, e.City AS EmployeeCity, o.Freight, o.ShipName, o.ShipVia, o.ShipCity, o.ShipRegion, o.ShipCountry FROM Suppliers AS s, Categories AS c,Products AS p,Employees AS e ,Customers AS m,Orders AS o,Order_Details AS d WHERE c.CategoryID = p.CategoryID And s.SupplierID = p.SupplierID And o.OrderID = d.OrderID And m.CustomerID = o.CustomerID AND e.EmployeeID = o.EmployeeID AND p.ProductID = d.ProductID"
    _rdDataSource.CustomSchemaFile = MyDocumentsPath & "\GrapeCity\ActiveAnalysis\DataSources\NWind\NwindMDB-SalesProductsSuppliers.schema"

    ' Connect to the datasource 
     _rdDataSource.Connect()

     ' Install the data source 
      _pivotView.DataSource = _rdDataSource
      _pivotView.Read(New FileInfo(MyDocumentsPath & "\GrapeCity\ActiveAnalysis\Layouts\NWind\Products' Orders per Year.analysis"), PersistSettings.Layout)
End Sub

Show To write the code in C#

C# code. Paste the code below in the Connect function.	Copy Code
private void Connect() { // Register the data provider. DataSourceFactory.RegisterADODataProviderFactory(new SQLiteDataProviderFactory()); if (_rdDataSource.IsConnected) _rdDataSource.Disconnect(); // Set up connection parameters _rdDataSource.ConnectionType = ConnectionType.ADO; _rdDataSource.ConnectionString = "Data Source=" + MyDocumentsPath + @"\GrapeCity\ActiveAnalysis\DataSources\NWind\NWIND.db"; _rdDataSource.QueryString = @"SELECT c.CategoryName, p.ProductName, o.OrderID, o.OrderDate, d.UnitPrice, d.Quantity, s.Country AS SupplierCountry, s.City AS SupplierCity, s.CompanyName AS SupplierCompany, m.Country AS CustomerCountry, m.City AS CustomerCity, m.CompanyName AS CustomerCompany, d.Discount, m.Region AS CustomerRegion, s.Region AS SupplierRegion, e.LastName, e.EmployeeID AS EmployeeID, e.Title AS EmployeeTitle, e.Region AS EmployeeRegion, e.Country AS EmployeeCountry, e.City AS EmployeeCity, o.Freight, o.ShipName, o.ShipVia, o.ShipCity, o.ShipRegion, o.ShipCountry FROM Suppliers AS s, Categories AS c,Products AS p,Employees AS e ,Customers AS m,Orders AS o,Order_Details AS d WHERE c.CategoryID = p.CategoryID AND s.SupplierID = p.SupplierID AND o.OrderID = d.OrderID AND m.CustomerID = o.CustomerID AND e.EmployeeID = o.EmployeeID AND p.ProductID = d.ProductID"; _rdDataSource.CustomSchemaFile = MyDocumentsPath + @"\GrapeCity\ActiveAnalysis\DataSources\NWind\NwindMDB-SalesProductsSuppliers.schema"; // Connect to the datasource _rdDataSource.Connect(); // Install the data source _pivotView.DataSource = _rdDataSource; _pivotView.Read(new FileInfo(MyDocumentsPath + @"\GrapeCity\ActiveAnalysis\Layouts\NWind\Products' Orders per Year.analysis"), PersistSettings.Layout); }

C# code. Paste the code below in the Connect function.

Copy Code

private void Connect()
  {
   // Register the data provider.
   DataSourceFactory.RegisterADODataProviderFactory(new SQLiteDataProviderFactory());

   if (_rdDataSource.IsConnected)
       _rdDataSource.Disconnect();

   // Set up connection parameters
   _rdDataSource.ConnectionType = ConnectionType.ADO;
   _rdDataSource.ConnectionString = "Data Source=" + MyDocumentsPath + @"\GrapeCity\ActiveAnalysis\DataSources\NWind\NWIND.db";
   _rdDataSource.QueryString =
    @"SELECT c.CategoryName, p.ProductName, o.OrderID, o.OrderDate, d.UnitPrice, d.Quantity, s.Country AS SupplierCountry,
    s.City AS SupplierCity, s.CompanyName AS SupplierCompany, m.Country AS CustomerCountry, m.City AS CustomerCity,
    m.CompanyName AS CustomerCompany, d.Discount, m.Region AS CustomerRegion, s.Region AS SupplierRegion, e.LastName,
    e.EmployeeID AS EmployeeID, e.Title AS EmployeeTitle, e.Region AS EmployeeRegion, e.Country AS EmployeeCountry,
    e.City AS EmployeeCity, o.Freight, o.ShipName, o.ShipVia, o.ShipCity, o.ShipRegion, o.ShipCountry
    FROM Suppliers AS s, Categories AS c,Products AS p,Employees AS e ,Customers AS m,Orders AS o,Order_Details AS d
    WHERE c.CategoryID = p.CategoryID AND s.SupplierID = p.SupplierID AND o.OrderID = d.OrderID AND m.CustomerID = o.CustomerID
    AND e.EmployeeID = o.EmployeeID AND p.ProductID = d.ProductID";
   _rdDataSource.CustomSchemaFile = MyDocumentsPath + @"\GrapeCity\ActiveAnalysis\DataSources\NWind\NwindMDB-SalesProductsSuppliers.schema";

   // Connect to the datasource
   _rdDataSource.Connect();

   // Install the data source
   _pivotView.DataSource = _rdDataSource;
   _pivotView.Read(new FileInfo(MyDocumentsPath + @"\GrapeCity\ActiveAnalysis\Layouts\NWind\Products' Orders per Year.analysis"), PersistSettings.Layout);
  }

Call the Connect function to connect to the data source using the data provider.
To write the code in Visual Basic.NET

Visual Basic.NET code. Paste the code below in the Connect function. Copy Code

Public Sub New()
    InitializeComponent()
    Connect()

End Sub

To write the code in C#

C# code. Paste the code below in the Connect function. Copy Code

public MainForm()
{
   InitializeComponent();
   Connect();
}

Visual Basic.NET code. Paste the code below in the Connect function.	Copy Code
Public Sub New() InitializeComponent() Connect() End Sub

C# code. Paste the code below in the Connect function.	Copy Code
public MainForm() { InitializeComponent(); Connect(); }

Note: To see the implementation of the code above, please take a look at the ADOConnection sample.

GrapeCity ActiveAnalysis Online Help
Connect to database using ADO.NET data provider
Support Forum