GrapeCity
Published in

GrapeCity

How to Load Millions of Rows from JSON Data Sources in Your .NET App | ComponentOne

ComponentOne DataConnectors are ADO.NET-based service components that provide a unified interface for accessing different data sources for efficient data communication. It reduces the time required to develop data-driven apps. Nowadays, the use of the JSON data source is currently becoming increasingly common to bind applications, and GrapeCity provides a DataConnector for JSON that allows easy access to JSON data using ADO.NET or EntityFramework Core. It simplifies the process of working with JSON data and gives you the ability to query both flat and relational JSON data.

Generally, if you have a huge number of records in your JSON, for example in millions, then it would take so long to assign the data by iterating through it but with the JSON DataConnector, you can load as much as data in just a few seconds and currently, this functionality is available using Top level Document Model only.

In this blog, we will learn how to load millions of records into the FlexGrid very quickly using ADO.NET Provider for JSON, and the data we are taking is the Employees’ data with four million records. The implementation steps are categorized as below:

  1. Create a Custom Data class to Map Fields with the JSON data
  2. Bind FlexGrid to JSON Datasource

Setup a New WinForms App with the Required package

Create a new .NET 6 WinForms App using Visual Studio 2022 and add the following packages to the project:

Note: Here, we are using the C1Themes class of FlexGrid to give it an enhanced look by applying the MaterialDark theme.

Step 1

Step 2

Step 3

Step 4

Step 5

Step 6

Create a Custom Data class to Map Fields with the JSON data

Create a new custom data class named Data, implementing the INotifyPropertyChanged, IEditableObjectInterfaces as per the schema in the JSON, which will be used by the generic class C1AdoNetCursorDataCollection<class> to get strongly-typed records from the JSON data source.

internal class Data : INotifyPropertyChanged, IEditableObject
{
// Fields
private string index;
private string firstName;
private string lastName;
private bool isActive;
private string salary;
private string age;
private string gender;
private string company;
private string email;
private string phone;
private string address;
[DataMember(Name = "index")]
public string Index
{
get => index;
set
{
if (index != value)
{
index = value;
OnPropertyChanged();
}
}
}
[DataMember(Name = "firstName")]
public string FirstName
{
get => firstName;
set
{
if (firstName != value)
{
firstName = value;
OnPropertyChanged();
}
}
}
[DataMember(Name = "lastname")]
public string Lastname
{
get => lastName;
set
{
if (lastName != value)
{
lastName = value;
OnPropertyChanged();
}
}
}
[DataMember(Name = "salary")]
public string Salary
{
get => salary;
set
{
if (salary != value)
{
salary = value;
OnPropertyChanged();
}
}
}
[DataMember(Name = "age")]
public string Age
{
get => age;
set
{
if (age != value)
{
age = value;
OnPropertyChanged();
}
}
}
[DataMember(Name = "gender")]
public string Gender
{
get => gender;
set
{
if (gender != value)
{
gender = value;
OnPropertyChanged();
}
}
}
[DataMember(Name = "company")]
public string Company
{
get => company;
set
{
if (company != value)
{
company = value;
OnPropertyChanged();
}
}
}
[DataMember(Name = "email")]
public string Email
{
get => email;
set
{
if (email != value)
{
email = value;
OnPropertyChanged();
}
}
}
[DataMember(Name = "phone")]
public string Phone
{
get => phone;
set
{
if (phone != value)
{
phone = value;
OnPropertyChanged();
}
}
}
[DataMember(Name = "address")]
public string Address
{
get => address;
set
{
if (address != value)
{
address = value;
OnPropertyChanged();
}
}
}
[DataMember(Name = "isActive")]
public bool IsActive
{
get => isActive;
set
{
if (isActive != value)
{
isActive = value;
OnPropertyChanged();
}
}
}
// INotifyPropertyChanged Members
public event PropertyChangedEventHandler PropertyChanged;
private void OnPropertyChanged([CallerMemberName] string propertyName = "")
{
OnPropertyChanged(new PropertyChangedEventArgs(propertyName));
}
protected void OnPropertyChanged(PropertyChangedEventArgs e)
{
PropertyChanged?.Invoke(this, e);
}// IEditableObject Members
private Data _clone;
public void BeginEdit()
{
_clone = (Data)MemberwiseClone();
}
public void CancelEdit()
{
if (_clone != null)
{
foreach (var p in GetType().GetRuntimeProperties())
{
if (p.CanRead && p.CanWrite)
{
p.SetValue(this, p.GetValue(_clone, null), null);
}
}
}
}
public void EndEdit()
{
_clone = null;
}
}

Bind FlexGrid to JSON Datasource

In the final step, you need to build a connection with your JSON data, as suggested here, and wrap your CursorDataCollection object in C1DataCollectionBindingList to bind it to an instance of FlexGrid.

C1AdoNetCursorDataCollection<Data> dataCollection;
string documentConnectionString = $"Data Model=Document;Uri=" + @"..\..\..\4mData.json" + ";Json Path='$.employees';Max Page Size=1000";
var conn = new C1JsonConnection(documentConnectionString);
dataCollection = new C1AdoNetCursorDataCollection<Data>(conn, "employees");
await dataCollection.LoadMoreItemsAsync();

Here we are using the LoadOnDemand feature to load the data, so you need to handle the AfterScroll event of FlexGrid as given below to load the data asynchronously as soon as the user scrolls to the last visible row.

c1FlexGrid1.AfterScroll += C1FlexGrid1_AfterScroll;
private void C1FlexGrid1_AfterScroll(object sender, C1.Win.FlexGrid.RangeEventArgs e)
{
if (e.NewRange.BottomRow == c1FlexGrid1.Rows.Count - 1)
{
_ = dataCollection.LoadMoreItemsAsync();
}
}

After implementing all the above-given steps, the final application works as shown in the GIF below:

Hopefully, you will enjoy this demo of creating a large data application using the ADO.NET Provider for JSON. You can download the sample from here.

Download the sample for this blog.

Please feel free to try it out and leave your feedback or questions in the comments section. Happy Coding!

Originally published at https://www.grapecity.com on September 22, 2022.

--

--

Sharing stories, concepts, and code.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
GrapeCity Developer Solutions

We provide developers with the widest range of Microsoft Visual Studio components, IDE platform development tools, and applications.