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

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:

Setup a New WinForms App with the required packages
Create a Custom Data class to Map Fields with the JSON data
Bind FlexGrid to JSON Datasource

Ready to Try it Out? Download ComponentOne Today!

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.

Please see the following screenshots, which show the step-by-step representation of how to set up the application.

Steps

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:

Scroll