# Virtualization in FlexGrid with OData

The topic covers a use-case for DataCollection for virtualization in FlexGrid using OData datasource.

## Content

In this walkthrough, you will learn how to integrate the DataCollection and DataConnector service components to use data virtualization with an OData service and bind the same to a data-aware control such as FlexGrid for WinForms. DataConnector is a cross-platform, data connectivity library for connecting to popular data sources such as OData and Dynamics 365.

![flowchart for odata and flexgrid](https://cdn.mescius.io/document-site-files/images/61bca276-a017-410c-b2f2-8d6dca57ae3b/images/adonet-flowchart.png)

Follow the steps below to apply Data Virtualization in FlexGrid for WinForms with OData:

### Set up Application

1. Create a WinForms project.
2. Drag and drop the FlexGrid control from the toolbox onto the form.
3. Install the following NuGet packages:
    * **C1.AdoNet.OData**
    * **C1.DataCollection.AdoNet**
    * **C1.DataCollection.BindingList**
4. To add a NuGet package, right-click the ‘References’ node in the ‘Solution Explorer’ window and select ‘Manage NuGet Packages’.

### Create Custom Class

1. Create a class 'Order' to provide data for binding to the FlexGrid control: 

    ```csharp
    public class Order : INotifyPropertyChanged, IEditableObject
    {
        // Fields
        private int _orderId, _employeeId, _shipVia;
        private string _customerId, _shipName, _shipAddress, _shipCity,
            _shipRegion, _shipPostalCode, _shipCountry;
        private DateTimeOffset _orderDate, _requiredDate, _shippedDate;
        private decimal _freight;
        // Properties
    
        public int OrderID
        {
            get => _orderId;
            set
            {
                if (_orderId != value)
                {
                    _orderId = value;
                    OnPropertyChanged();
                }
            }
        }
        public string CustomerID
        {
            get => _customerId;
            set
            {
                if (_customerId != value)
                {
                    _customerId = value;
                    OnPropertyChanged();
                }
            }
        }
        public int EmployeeID
        {
            get => _employeeId;
            set
            {
                if (_employeeId != value)
                {
                    _employeeId = value;
                    OnPropertyChanged();
                }
            }
        }
        public DateTimeOffset OrderDate
        {
            get => _orderDate;
            set
            {
                if (_orderDate != value)
                {
                    _orderDate = value;
                    OnPropertyChanged();
                }
            }
        }
        public DateTimeOffset RequiredDate
        {
            get => _requiredDate;
            set
            {
                if (_requiredDate != value)
                {
                    _requiredDate = value;
                    OnPropertyChanged();
                }
            }
        }
        public DateTimeOffset ShippedDate
        {
            get => _shippedDate;
            set
            {
                if (_shippedDate != value)
                {
                    _shippedDate = value;
                    OnPropertyChanged();
                }
            }
        }
        public int ShipVia
        {
            get => _shipVia;
            set
            {
                if (_shipVia != value)
                {
                    _shipVia = value;
                    OnPropertyChanged();
                }
            }
        }
        public decimal Freight
        {
            get => _freight;
            set
            {
                if (_freight != value)
                {
                    _freight = value;
                    OnPropertyChanged();
                }
            }
        }
        public string ShipName
        {
            get => _shipName;
            set
            {
                if (_shipName != value)
                {
                    _shipName = value;
                    OnPropertyChanged();
                }
            }
        }
        public string ShipAddress
        {
            get => _shipAddress;
            set
            {
                if (_shipAddress != value)
                {
                    _shipAddress = value;
                    OnPropertyChanged();
                }
            }
        }
        public string ShipCity
        {
            get => _shipCity;
            set
            {
                if (_shipCity != value)
                {
                    _shipCity = value;
                    OnPropertyChanged();
                }
            }
        }
        public string ShipRegion
        {
            get => _shipRegion;
            set
            {
                if (_shipRegion != value)
                {
                    _shipRegion = value;
                    OnPropertyChanged();
                }
            }
        }
        public string ShipPostalCode
        {
            get => _shipPostalCode;
            set
            {
                if (_shipPostalCode != value)
                {
                    _shipPostalCode = value;
                    OnPropertyChanged();
                }
            }
        }
        public string ShipCountry
        {
            get => _shipCountry;
            set
            {
                if (_shipCountry != value)
                {
                    _shipCountry = 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 Order _clone;
        public void BeginEdit()
        {
            _clone = (Order)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;
        }
    }
    ```

    ```vbnet
    Public Class Order
        Implements INotifyPropertyChanged, IEditableObject
    ' "Fields"
        Private _orderId, _employeeId, _shipVia As Integer
        Private _customerId, _shipName, _shipAddress, _shipCity, _shipRegion, _shipPostalCode, _shipCountry As String
        Private _orderDate, _requiredDate, _shippedDate As DateTimeOffset
        Private _freight As Decimal
    ' "Properties"
    
        Public Property OrderID As Integer
            Get
                Return _orderId
            End Get
            Set(ByVal value As Integer)
                If _orderId <> value Then
                    _orderId = value
                    OnPropertyChanged()
                End If
            End Set
        End Property
        Public Property CustomerID As String
            Get
                Return _customerId
            End Get
            Set(ByVal value As String)
                If Not Equals(_customerId, value) Then
                    _customerId = value
                    OnPropertyChanged()
                End If
            End Set
        End Property
        Public Property EmployeeID As Integer
            Get
                Return _employeeId
            End Get
            Set(ByVal value As Integer)
                If _employeeId <> value Then
                    _employeeId = value
                    OnPropertyChanged()
                End If
            End Set
        End Property
        Public Property OrderDate As DateTimeOffset
            Get
                Return _orderDate
            End Get
            Set(ByVal value As DateTimeOffset)
                If _orderDate <> value Then
                    _orderDate = value
                    OnPropertyChanged()
                End If
            End Set
        End Property
        Public Property RequiredDate As DateTimeOffset
            Get
                Return _requiredDate
            End Get
            Set(ByVal value As DateTimeOffset)
                If _requiredDate <> value Then
                    _requiredDate = value
                    OnPropertyChanged()
                End If
            End Set
        End Property
        Public Property ShippedDate As DateTimeOffset
            Get
                Return _shippedDate
            End Get
            Set(ByVal value As DateTimeOffset)
                If _shippedDate <> value Then
                    _shippedDate = value
                    OnPropertyChanged()
                End If
            End Set
        End Property
        Public Property ShipVia As Integer
            Get
                Return _shipVia
            End Get
            Set(ByVal value As Integer)
                If _shipVia <> value Then
                    _shipVia = value
                    OnPropertyChanged()
                End If
            End Set
        End Property
        Public Property Freight As Decimal
            Get
                Return _freight
            End Get
            Set(ByVal value As Decimal)
                If _freight <> value Then
                    _freight = value
                    OnPropertyChanged()
                End If
            End Set
        End Property
        Public Property ShipName As String
            Get
                Return _shipName
            End Get
            Set(ByVal value As String)
                If Not Equals(_shipName, value) Then
                    _shipName = value
                    OnPropertyChanged()
                End If
            End Set
        End Property
        Public Property ShipAddress As String
            Get
                Return _shipAddress
            End Get
            Set(ByVal value As String)
                If Not Equals(_shipAddress, value) Then
                    _shipAddress = value
                    OnPropertyChanged()
                End If
            End Set
        End Property
        Public Property ShipCity As String
            Get
                Return _shipCity
            End Get
            Set(ByVal value As String)
                If Not Equals(_shipCity, value) Then
                    _shipCity = value
                    OnPropertyChanged()
                End If
            End Set
        End Property
        Public Property ShipRegion As String
            Get
                Return _shipRegion
            End Get
            Set(ByVal value As String)
                If Not Equals(_shipRegion, value) Then
                    _shipRegion = value
                    OnPropertyChanged()
                End If
            End Set
        End Property
        Public Property ShipPostalCode As String
            Get
                Return _shipPostalCode
            End Get
            Set(ByVal value As String)
                If Not Equals(_shipPostalCode, value) Then
                    _shipPostalCode = value
                    OnPropertyChanged()
                End If
            End Set
        End Property
        Public Property ShipCountry As String
            Get
                Return _shipCountry
            End Get
            Set(ByVal value As String)
                If Not Equals(_shipCountry, value) Then
                    _shipCountry = value
                    OnPropertyChanged()
                End If
            End Set
        End Property
    ' "INotifyPropertyChanged Members"
    
        Public Event PropertyChanged As PropertyChangedEventHandler Implements INotifyPropertyChanged.PropertyChanged
        Private Sub OnPropertyChanged(
    <CallerMemberName> ByVal Optional propertyName As String = "")
            OnPropertyChanged(New PropertyChangedEventArgs(propertyName))
        End Sub
        Protected Sub OnPropertyChanged(ByVal e As PropertyChangedEventArgs)
            RaiseEvent PropertyChanged(Me, e)
        End Sub
    ' IEditableObject Members
    
        Private _clone As Order
        Public Sub BeginEdit() Implements IEditableObject.BeginEdit
            _clone = CType(MemberwiseClone(), Order)
        End Sub
        Public Sub CancelEdit() Implements IEditableObject.CancelEdit
            If _clone IsNot Nothing Then
                For Each p In [GetType](/componentone/docs/services/online-datacollection/walkthrough/virtualization-in-flexgrid-with-odata).GetRuntimeProperties()
                    If p.CanRead AndAlso p.CanWrite Then
                        p.SetValue(Me, p.GetValue(_clone, Nothing), Nothing)
                    End If
                Next
            End If
        End Sub
        Public Sub EndEdit() Implements IEditableObject.EndEdit
            _clone = Nothing
        End Sub
    End Class
    ```

### Bind FlexGrid to C1AdoNetVirtualDataCollection

1. In the **Form\_Load** event, use **Northwind OData Web APIs**, and fetch the records from the Orders table. We use the generic class **C1AdoNetVirtualDataCollection\<T>** to get strongly-typed records from the data source.You can use the non-generic class **C1AdoNetVirtualDataCollection**, which creates an appropriate type for the records at runtime. We wrap our virtual data collection object in **C1DataCollectionBindingList** to bind it to an instance of FlexGrid.Since the data collection is populated on a different thread, we use the BeginInvoke method of FlexGrid to avoid cross-thread exceptions for changing the DataSource. The **PageSize** property determines the number of rows requested in each fetch request. Note that in the FlexGrid control, the **DrawModeEnum** sets whether the control should fire after the OwnerDrawCell event. 

    ```csharp
    private void Form1_Load(object sender, EventArgs e)
    {
        string connectionString = @"Url=https://services.odata.org/Experimental/Northwind/Northwind.svc/";
        var odataConnection = new C1ODataConnection(connectionString);
        var collectionView = new C1AdoNetVirtualDataCollection<Order>(odataConnection, "Orders");
        collectionView.PageSize = 100;
        c1FlexGrid1.BeginInvoke(new MethodInvoker(() =>
        {
            c1FlexGrid1.DataSource = new C1DataCollectionBindingList(collectionView);
            c1FlexGrid1.AllowFiltering = true;
            c1FlexGrid1.AllowSorting = C1.Win.C1FlexGrid.AllowSortingEnum.MultiColumn;
        }));
        c1FlexGrid1.DrawMode = C1.Win.C1FlexGrid.DrawModeEnum.OwnerDraw;
        c1FlexGrid1.OwnerDrawCell += c1FlexGrid1_OwnerDrawCell;
    }
    ```

    ```vbnet
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load, c1FlexGrid1.Click
        Dim connectionString As String = "Url=https://services.odata.org/Experimental/Northwind/Northwind.svc/"
        Dim odataConnection = New C1ODataConnection(connectionString)
        Dim collectionView = New C1AdoNetVirtualDataCollection(Of ODataVirtualizationDemo_VB.Order)(odataConnection, "Orders")
        collectionView.PageSize = 100
        Me.c1FlexGrid1.BeginInvoke(New MethodInvoker(Sub()
                                                         Me.c1FlexGrid1.DataSource = New C1DataCollectionBindingList(collectionView)
                                                         Me.c1FlexGrid1.AllowFiltering = True
                                                         Me.c1FlexGrid1.AllowSorting = C1.Win.C1FlexGrid.AllowSortingEnum.MultiColumn
                                                     End Sub))
        Me.c1FlexGrid1.DrawMode = C1.Win.C1FlexGrid.DrawModeEnum.OwnerDraw
        AddHandler Me.c1FlexGrid1.OwnerDrawCell, AddressOf Me.c1FlexGrid1_OwnerDrawCell
    End Sub
    ```
2. Double click the **OwnerDrawCell** event, and add the following code: 

    ```csharp
    private void c1FlexGrid1_OwnerDrawCell(object sender, C1.Win.C1FlexGrid.OwnerDrawCellEventArgs e)
    {
        if (e.Row < c1FlexGrid1.Rows.Fixed)
            return;
        if (c1FlexGrid1.Cols[e.Col].DataType == typeof(DateTimeOffset))
        {
            object value = c1FlexGrid1[e.Row, e.Col];
            e.Text = (value != null) ? ((DateTimeOffset)value).DateTime.ToString("d") : string.Empty;
        }
    }
    ```

    ```vbnet
    Private Sub c1FlexGrid1_OwnerDrawCell(sender As Object, e As C1.Win.C1FlexGrid.OwnerDrawCellEventArgs) Handles c1FlexGrid1.OwnerDrawCell
        If e.Row < Me.c1FlexGrid1.Rows.Fixed Then Return
        If Me.c1FlexGrid1.Cols(e.Col).DataType Is GetType(DateTimeOffset) Then
            Dim value As Object = Me.c1FlexGrid1(e.Row, e.Col)
            e.Text = If(value IsNot Nothing, CType(value, DateTimeOffset).DateTime.ToString("d"), String.Empty)
        End If
    End Sub
    ```
3. Run the project. Now, you can see that the grid initially has 100 rows (configured using the PageSize property). And, as we scroll down, more data is fetched automatically.
<br>
    ![Virtual loading as the data is scrolled down](https://cdn.mescius.io/document-site-files/images/61bca276-a017-410c-b2f2-8d6dca57ae3b/images/odata-flexgridcontrol.gif)