Build a 500,000 Row Report in Seconds Using Our Powerful .NET Data Engine
Tags: webdev, tutorial, dotnet, devops
Published: 2022-08-05 21:25:00

---
canonical_url: https://www.grapecity.com/blogs/build-500000-row-report-in-seconds-using-net-data-engine
description: Learn how to build a 500,000 row report in seconds using our powerful .NET Data Engine. See more from ComponentOne today.
---

When working with [ComponentOne FlexReport](https://www.grapecity.com/componentone/docs/win/online-flexreport/overview.html) and large datasets, there can be a need to retrieve specific data based on certain groupings. For example, if you are working on some sales data, you might need to create a report to show the number of sales per country, or you might need to create a report to display the data based on unique combinations of region and country.

Suppose you need to render about one million records in the report. In that case, you will need to build the groups in your FlexReport layout via the [Groups option in the Home tab](https://www.grapecity.com/componentone/docs/win/online-flexreport/HomeTab.html) of FlexReportDesinger that are based on your requirements, and rendering this kind of report will take a significant amount of time which is around a minute.

But what if you can achieve the same functionality in a fraction of a second. You may be wondering: how is it even possible? The answer is yes; you can accomplish this using [**ComponentOne** DataEngine](https://www.grapecity.com/componentone/docs/services/online-dataengine/overview.html), the standalone .NET Standard library that provides high-performance analytics for large datasets. C1DataEngine is faster than any other column-oriented storage approach and processes millions of records in less than a second.

So, to accomplish this, you will first need to design a flat report according to your requirements and then obtain the aggregated data from your massive dataset in a flattened form using C1DataEngine API to be assigned as the datasource of your FlexReport.

In this blog, we will demonstrate how you can create a report to display the sales data per country using C1DataEngine in a very few seconds. This will be divided into the steps below:

1.  [Design a Flat Report](#design)
2.  [Get the Required Data](#required-data)
3.  [Render the Report in FlexViewer](#render)

### <a id="design"></a>Design a Flat Report

As previously stated, you must create a flat report displaying sales data by country. First, add [TextFields](https://www.grapecity.com/componentone/docs/win/online-flexreport/C1.Win.FlexReport.6~C1.Win.FlexReport.TextField.html) to the detail section for your database values, then design the layout that suits your needs. We have formatted the report in such a way that each record group will be shown in the form of a card, as presented in the following screenshot:

![Flat Report Design](https://files.grapecity.com/gc-website-media/b13lfsd4/01-flatreportdesign.png?width=500&height=292.67399267399264)

After this, create a Windows Forms Application, add the FlexReport component to load your report, and FlexViewer control into your form to render the report.

![Design](https://files.grapecity.com/gc-website-media/xplfofmh/02-design.png?width=500&height=368.3978873239437)

To load the report via code, use the following:

```
    c1FlexReport1.Load(@"..\..\SalesReport.flxr", "SalesReport");
```

### <a id="required-data"></a>Get the Required Data

To get the required data, you first need to add the following required packages for using the C1DataEngine API:

*   **C1.DataCollection.BindingList**
*   **C1.DataEngine**
*   **C1.DataEngine.Collection**

This blog will deal with the one million records via a CSV file containing the sales data. First, initialize the [Workspace ](https://www.grapecity.com/componentone/docs/services/online-dataengine/quickstart.html)and [IDataCollection ](https://www.grapecity.com/componentone/docs/services/online-dataengine/C1.DataEngine.Collection~C1.DataEngine.C1DataEngineCollection.html)objects and create a class containing properties that can hold the data corresponding to the fields in the **CSV** file.

```
    private Workspace _workspace;
    private IDataCollection<object> _dataCollection;
    class SalesData
            {
                public string Region { get; set; }
                public string Country { get; set; }
                public string ItemType { get; set; }
                public string SalesChannel { get; set; }
                public string OrderPriority { get; set; }
                public DateTime OrderDate { get; set; }
                public string OrderID { get; set; }
                public DateTime ShipDate { get; set; }
                public int UnitsSold { get; set; }
                public double UnitPrice { get; set; }
                public double UnitCost { get; set; }
                public double TotalRevenue { get; set; }
                public double TotalCost { get; set; }
                public double TotalProfit { get; set; }
    }
```

To get the complete one million records in your **Workspace** object use the [CsvReader](https://www.grapecity.com/componentone/docs/services/online-dataengine/importdatafromcsv.html) as given in the code snippet below:

```
    //Initialize a new workspace folder relative to the project root directory
    workspace = new Workspace();
    workspace.Init("workspace");

    //Read CSV data and convert it into a collection of custom .NET objects
    var reader = new StreamReader(@"..\..\Sales Records.csv");
    var csv = new CsvReader(reader, CultureInfo.InvariantCulture);
    var salesDataList = csv.GetRecords<SalesData>().AsEnumerable();

    //Import CSV data from custom collection to a DataEngine table
    ObjectConnector<SalesData> connector = new ObjectConnector<SalesData>(workspace, salesDataList);
    connector.GetData("SalesData");
    workspace.Save();
```

The specific data we will be extracting from the database is the **TotalCost, TotalRevenue, **and **TotalProfit** for each unique country, and this can be done via [query](https://www.grapecity.com/componentone/docs/services/online-dataengine/simpleoperations.html) using the [Op](https://www.grapecity.com/componentone/docs/services/online-dataengine/C1.DataEngine~C1.DataEngine.Op.html) class of the **C1.DataEngine** assembly is invoked while defining the query to perform a simple operation. 

The results of DataEngine queries are cached, making subsequent executions even more quickly. This query remains cached until the underlying data is refreshed. You can refresh data periodically, for example, hourly or daily, by checking a saved time stamp. 

```
    dynamic salesTable = workspace.table("SalesData");
    dynamic query = workspace.query("SalesByCountry", new
    {
    Country = salesTable.Country,
    TotalCost = Op.Sum(salesTable.TotalCost),
    TotalProfit = Op.Sum(salesTable.TotalProfit),
    TotalRevenue = Op.Sum(salesTable.TotalRevenue),
    });
```

Use the [C1DataEngineCollection](https://www.grapecity.com/componentone/docs/services/online-dataengine/C1.DataEngine.Collection~C1.DataEngine.C1DataEngineCollection.html) class to get the data from the workspace based on your specific query and [C1DataCollectionBindingList](https://www.grapecity.com/componentone/docs/services/online-datacollection/C1.DataCollection.BindingList~C1.DataCollection.BindingList.C1DataCollectionBindingList_members.html) class to be assigned as the data source of your FlexReport based on your [C1DataEngineCollection](https://www.grapecity.com/componentone/docs/services/online-dataengine/C1.DataEngine.Collection~C1.DataEngine.C1DataEngineCollection.html) object.

```
    C1DataEngineCollection _dataCollection = new C1DataEngineCollection(workspace, query);
    c1FlexReport1.DataSource.Recordset = new C1DataCollectionBindingList(_dataCollection);
```

### <a id="render"></a>Render the Report in FlexViewer

For the report to appear in the FlexViewer at runtime, you must bind the Flexreport to the FlexReport's [DocumentSource](https://www.grapecity.com/componentone/docs/win/online-flexreport/C1.Win.FlexViewer.6~C1.Win.FlexViewer.C1FlexViewer~DocumentSource.html) property.

```
    c1FlexViewer1.DocumentSource = c1FlexReport1;
```

The report will get rendered in a short amount of time after following all the above processes, and look as in the GIF below:

![Demo](https://files.grapecity.com/gc-website-media/4xffnsvj/03-demo1.gif?width=500&height=270.63740856844305)

If you are going to directly use FlexReport to work with this massive amount of data, you will need to create a grouped report like the one seen below:

![Sales Report](https://files.grapecity.com/gc-website-media/mrybggpp/04-salesreport.png?width=500&height=285.3087295954578)

The GIF attached shows how the report would render after assigning the one million records to FlexReport directly using the following code:

```
    DataTable dt = new DataTable();
    string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\\..\\Sales Records.mdb;";
    OleDbConnection conn = new OleDbConnection(connectionString);
    OleDbDataAdapter adapter = new OleDbDataAdapter("Select * from SalesRecords", conn);
    adapter.Fill(dt);
    c1FlexReport1.Load(@"..\..\SalesReportGrouped.flxr", "SalesReport");
    c1FlexReport1.DataSource.Recordset = dt;
    c1FlexViewer1.DocumentSource = c1FlexReport1;
```

![Demo](https://files.grapecity.com/gc-website-media/n3tclv3t/05-demo2.gif?width=500&height=272.34709879769997)

Hopefully, you will enjoy this demo of creating a large report using the C1DataEngine API. 

You can learn more about the C1DataEngine from [this](https://www.grapecity.com/componentone/docs/services/online-dataengine/overview.html) documentation link.

You can download the sample from [here](https://global-cdn.grapecity.com/blogs/componentone/20220725-build-500000-row-report-in-seconds-using-net-data-engine/LargeDatasetDemo.zip).

Happy coding!