ActiveReports 11
Bind Reports to a Data Source
ActiveReports 11 > ActiveReports User Guide > How To > Section Report How To > Work with Data in Section Reports > Bind Reports to a Data Source

At design time, you can connect a section report to a data source through the Report Data Source dialog. You can access the Report Data Source dialog by doing one of the following:

There are four tabs in the dialog for the four most commonly used data sources.

The following steps take you through the process of binding reports to each data source. These steps assume that you have already added an ActiveReports 11 Section Report template in a Visual Studio project. See Adding an ActiveReport to a Project further information on adding different report layouts.

To use the OLE DB data source

  1. In the Report Data Source dialog, on the OLE DB tab, click the Build button next to Connection String.
  2. In the Data Link Properties window that appears, select Microsoft Jet 4.0 OLE DB Provider and click the Next button to move to the Connection tab.
  3. Click the ellipsis (...) button to browse to your database, for example the NWind.mdb sample database. Click Open once you have selected the appropriate database path.
  4. Click the Test Connection button to see if you have successfully connected to the database.
  5. Click OK to close the Data Link Properties window and return to the Report Data Source dialog. Notice that the Connection String field gets filled automatically.
  6. In the Query field on the OLE DB tab, enter a SQL query to select the data that you want use from the connected database. For example, Select * From CUSTOMERS
    OR
    In the Report Data Source dialog, click on Query Designer button to access Visual Query Designer for creating SQL queries. See Visual Query Designer for further information on how to create a query using the interactive query designer.
  7. Click OK to save the data source and return to the report design surface.

To use the ODBC data source

Before you connect to a ODBC data source, you must install a ODBC driver and set up a ODBC data source. For more information, see How To: Setup an ODBC Data Source.
  1. In the Report Data Source dialog, click the ODBC tab.
  2. In the Connection String field on the ODBC tab, enter a connection string to connect to the database. For example, Provider=MSDASQL;Persist Security Info=False;DSN=MS Access Database
  3. In the Query field on the ODBC tab, enter a SQL query to select the data that you want use from the connected database. For example, Select * From CUSTOMERS
  4. Click OK to save the data source and return to the report design surface.

To use the SQL data source

  1. In the Report Data Source dialog, on the SQL tab, click the Build button next to Connection String.
  2. In the Data Link Properties window that appears, select Microsoft OLE DB Provider for SQL Server and click the Next button to move to the Connection tab.
  3. On the Connection tab of the Data Link Properties window:
    • In the Select or enter server name field, select your server from the drop down list.
    • Under Enter information to log on to the server, select the Windows NT security credentials or your specific user name and password.
    • Under Select the database on the server, select a database from the server or attach a database file.
    • Click the Test Connection button to see if you have successfully connected to the database.
  4. Click OK to close the Data Link Properties window and to return to the Report Data Source dialog. Notice that the Connection String field gets filled automatically.
  5. In the Query field on the SQL tab, enter a SQL query to select the data that you want use from the connected database. For example, Select * From CUSTOMERS
    OR
    In the Report Data Source dialog, click on Query Designer button to access Visual Query Designer for creating SQL queries. See Visual Query Designer for further information on how to create a query using the interactive query designer.
  6. Click OK to save the data source and return to the report design surface.

To use the XML data source

  1. In the Report Data Source dialog, on the XML tab, click the ellipsis (...) button next to File URL field.
  2. In the Open File window that appears, navigate to your XML data file to select it and click the Open button. You can use a sample XML data file located at C:\Users\YourUserName\Documents\GrapeCity Samples\ActiveReports 11\Data\customer.xml.
  3. In the Recordset Pattern field, enter a valid XPath expression like the following. //CUSTOMER
  4. Click OK to save the data source and return to the report design surface.

You also have the option to use an unbound or an IEnumerable data source. See the following procedures to implement these data source connections in code.

To use the CSV data source

  1. In the Report Data Source dialog, on the CSV tab, click the Build button next to Connection String.
  2. Specify the File Path by clicking the Open button and selecting the .csv file.
  3. Set the options in the wizard. See the Sample CSV Connection String drop-down under Report Data Source Dialog for further details.
  4. To edit the Name, Width (if applicable), and Data Type of columns shown in the Preview, click the Get from preview button. Note that Width is applicable only for Fixed data type.
  5. Click OK to save the changes and close the dialog. The Connection String tab displays the generated connection string. You can validate the connection string by clicking the Validate DataSource icon .
  6. Click OK on the lower right corner to close the dialog. You have successfully connected the report to a CSV data source. Note that the dataset for the CSV data source is added automatically.

To use an Unbound data source

To create a data connection

  1. Add an Imports (VisualBasic.NET) or using (C#) statement for System.Data and System.Data.Oledb namespaces.
  2. Right-click the gray area outside the design surface to select the report and select Properties.
  3. In the Properties window that appears, click the Events icon to view the available events for the report.
  4. In the events list, double-click the ReportStart event. This creates an event-handling method for the ReportStart event in code.
  5. Add the following code to the handler.

    To write code in VisualBasic.NET

    Visual Basic.NET code. Paste above the ReportStart event.
    Copy Code
    Dim m_cnnString As String
    Dim sqlString As String
    Dim m_reader As OleDbDataReader
    Dim m_cnn As OleDbConnection    
    
    Visual Basic.NET code. Paste inside the ReportStart event.
    Copy Code
    'Set data source connection string.
    m_cnnString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
            + "Data Source=C:\Users\YourUserName\Documents\GrapeCity Samples\ActiveReports 11\Data\Nwind.mdb;Persist Security Info=False"
    'Set data source SQL query.    
    sqlString = "SELECT * FROM categories INNER JOIN products ON categories.categoryid " _
            + "= products.categoryid ORDER BY products.categoryid, products.productid"
    'Open connection and create DataReader.     
    m_cnn = New OleDb.OleDbConnection(m_cnnString)
    Dim m_Cmd As New OleDb.OleDbCommand(sqlString, m_cnn)
    If m_cnn.State = ConnectionState.Closed Then
       m_cnn.Open()
    End If
    m_reader = m_Cmd.ExecuteReader()
    

    To write code in C#

    C# code. Paste above the ReportStart event.
    Copy Code
    private static OleDbConnection m_cnn;
    private static OleDbDataReader m_reader;  
    private string sqlString;
    private string m_cnnString;
    
    C# code. Paste inside the ReportStart event.
    Copy Code
    //Set data source connection string.    
    m_cnnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
            + @"C:\Users\YourUserName\Documents\GrapeCity Samples\ActiveReports 11\Data\Nwind.mdb;Persist Security Info=False";
    //Set data source SQL query.     
    sqlString = "SELECT * FROM categories INNER JOIN products"
            + " ON categories.categoryid = products.categoryid"
            + " ORDER BY products.categoryid, products.productid";
    //Open connection and create DataReader.               
    m_cnn = new OleDbConnection(m_cnnString);
    OleDbCommand m_Cmd = new OleDbCommand(sqlString,m_cnn);
    if(m_cnn.State == ConnectionState.Closed)
    {
      m_cnn.Open();
    }
    m_reader = m_Cmd.ExecuteReader();
    

To close the data connection

  1. Right-click the gray area outside the design surface to select the report and select Properties.
  2. In the Properties window that appears, click the Events icon to view the available events for the report.
  3. In the events list, double-click the ReportEnd event. This creates an event-handling method for the ReportEnd event.
  4. Add the following code to the handler.

    To write the code in Visual Basic

    Visual Basic.NET code. Paste inside the ReportEnd event.
    Copy Code
    m_reader.Close()
    m_cnn.Close()
    

    To write the code in C#

    C# code. Paste inside the ReportEnd event.
    Copy Code
    m_reader.Close();
    m_cnn.Close();
    

To create a fields collection

  1. Right-click the gray area around the design surface to select the report and select Properties.
  2. In the Properties window that appears, click the Events icon to view the available events for the report.
  3. In the events list, double-click DataInitialize event. This creates an event-handling method for the report's DataInitialize event.
  4. Add code to the handler to add fields to the report's fields collection.

    To write the code in Visual Basic.NET

    Visual Basic.NET code. Paste inside the DataInitialize event.
    Copy Code
    Fields.Add("CategoryName")
    Fields.Add("ProductName")
    Fields.Add("UnitsInStock")
    Fields.Add("Description")
    

    To write the code in C#

    C# code. Paste inside the DataInitialize event.
    Copy Code
    Fields.Add("CategoryName");
    Fields.Add("ProductName");
    Fields.Add("UnitsInStock");
    Fields.Add("Description");
    

To populate the fields

  1. Right-click the gray area around the design surface to select the report and select Properties.
  2. In the Properties window that appears, click the Events icon to view the available events for the report.
  3. In the events list, double-click the FetchData event. This creates an event-handling method for the report's FetchData event.
  4. Add the following code to the handler to retrieve information to populate the report fields.

    To write the code in Visual Basic.NET

    Visual Basic.NET code. Paste inside the FetchData event.
    Copy Code
    Try
        m_reader.Read()
        Me.Fields("CategoryName").Value = m_reader("CategoryName")
        Me.Fields("ProductName").Value = m_reader("ProductName")
        Me.Fields("UnitsInStock").Value = m_reader("UnitsInStock")
        Me.Fields("Description").Value = m_reader("Description")
        eArgs.EOF = False
    Catch ex As Exception
        eArgs.EOF = True
    End Try
    

    To write the code in C#

    C# code. Paste inside the FetchData event.
    Copy Code
    try
    {
        m_reader.Read();
        Fields["CategoryName"].Value = m_reader["CategoryName"].ToString();
        Fields["ProductName"].Value = m_reader["ProductName"].ToString();
        Fields["UnitsInStock"].Value = m_reader["UnitsInStock"].ToString();
        Fields["Description"].Value = m_reader["Description"].ToString();
        eArgs.EOF = false;
    }
    catch
    {
        eArgs.EOF = true;
    }
    
Tip: In order to view the added data at run time, add controls to your report and assign their DataField property to the name of the fields you added in code while creating a field collection.
Caution: Do not access the Fields collection outside the DataInitialize and FetchData events. Accessing the Fields collection outside of these events is not supported, and has unpredictable results.

To use the IEnumerable data source

  1. Right-click the design surface and select View Code.
  2. Add the following code inside the class declaration of the report:

    To create a data source in Visual Basic

    Visual Basic.NET code. Paste inside the class declaration of the report.
    Copy Code
    Private datasource1 As IEnumerator(Of String) = Nothing 
    Dim list As List(Of String)= Nothing
    
    Visual Basic.NET code. Paste inside the class declaration of the report.
    Copy Code
    Private Function GetIEnumerableData() As IEnumerable(Of String)    
        For i As Integer = 1 To 10
           list.Add(String.Format("TestData_{0}", i.ToString()))       
        Next       
        Return list
    End Function
    

    To create a data source in C#

    C# code. Paste inside the class declaration of the report.
    Copy Code
    private IEnumerator<string> datasource = null;
    
    C# code. Paste inside the class declaration of the report.
    Copy Code
    private IEnumerable<string> GetIEnumerableData()
    {
        for (int i = 1; i <= 10; i++)
        {
            yield return string.Format("TestData_{0}", i.ToString());
        }
    }
    
  3. On the design surface, right-click the gray area around the design surface to select the report and select Properties.
  4. In the Properties window that appears, click the Events icon to view the available events for the report.
  5. Double-click the DataInitialize event. This creates an event-handling method for the report's DataInitialize event.
  6. Add the following code to the handler to add fields to the report's Fields collection.

    To add fields in Visual Basic

    Visual Basic.NET code. Paste inside the DataInitialize event.
    Copy Code
    Me.Fields.Add("TestField")
    Me.list = New List(Of String)
    datasource1 = GetIEnumerableData().GetEnumerator()
    

    To add fields in C#

    C# code. Paste inside the DataInitialize event.
    Copy Code
    this.Fields.Add("TestField");
    datasource = GetIEnumerableData().GetEnumerator();
    
  7. Repeat steps 3 and 4 to open the events list in the property window.
  8. Double-click the FetchData event. This creates an event-handling method for the report's FetchData event.
  9. Add code to the handler to retrieve information to populate the report fields.

    To populate fields in Visual Basic

    Visual Basic.NET code. Paste inside the FetchData event.
    Copy Code
    If datasource1.MoveNext() Then
    Me.Fields("TestField").Value = datasource1.Current
    eArgs.EOF = False
    Else
    eArgs.EOF = True
    End If

    To populate fields in C#

    C# code. Paste inside the FetchData event.
    Copy Code
    if (datasource.MoveNext())
    {
        this.Fields["TestField"].Value = datasource.Current;
        eArgs.EOF = false;
    }
    else
        eArgs.EOF = true;
    
Tip: In order to view the added data at run time, add controls to your report and assign their DataField property to the name of the fields you added in code while creating a field collection.
See Also