DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’»

DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’» is a community of 964,423 amazing developers

We're a place where coders share, stay up-to-date and grow their careers.

Create account Log in
Cover image for How to Load Millions of Rows from JSON Data Sources in Your .NET App
Chelsea Devereaux for GrapeCity, Inc.

Posted on • Originally published at grapecity.com

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:

  1. Setup a New WinForms App with the required packages
  2. Create a Custom Data class to Map Fields with the JSON data
  3. 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.

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

Step 1

Image description

Step 2

Image description

Step 3

Image description

Step 4

Image description

Step 5

Image description

Step 6

Image description

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 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;
            }
        }

Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

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();
    }
    }
Enter fullscreen mode Exit fullscreen mode

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

Scroll

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!

Top comments (0)

What image format should you use in your next project? πŸ€”