Retrieving Cars from the Database

This article is relevant to entity models that utilize the deprecated Visual Studio integration of Telerik Data Access. The current documentation of the Data Access framework is available here.

When MainForm is loaded it will automatically display all available in the database cars. In this step you will write the code necessary to do just that. All code below is to be added to MainForm.

Open the code view of the MainForm and add the next few namespaces:

using SofiaCarRental.Model;
using Telerik.OpenAccess;
using Telerik.OpenAccess.Exceptions;
using Telerik.WinControls.UI;

Imports Telerik.OpenAccess
Imports Telerik.OpenAccess.Exceptions
Imports SofiaCarRental.Model
Imports Telerik.WinControls.UI

Next you need to define several private fields. carsBindingSource is the BindingSource for the MainForm`s RadGridView. context is the OpenAccessContext object through which you will execute CRUD operations against the SofiaCarRental database. carMakerFilter holds the car maker value by which the cars may be filtered. selectedIndex will hold the index of the element which is currently selected in carsBindingSource.
```
private BindingSource carsBindingSource;
private SofiaCarRentalContext context;
private string carMakerFilter;
private int selectedIndex = -1;
```
```
Private _carsBindingSource As BindingSource
Private _context As SofiaCarRentalContext
Private _carMakerFilter As String
Private _selectedIndex As Integer = -1
```

Generate the Load event handler for MainForm. In it you need to initialize the context, the binding source and the filter string. Also you will call two methods which will populate and setup the RadGridView on the MainForm. Also the Reset button is initially disabled.

private void MainForm_Load(object sender, EventArgs e)
{
    this.context = new SofiaCarRentalContext();
    this.carsBindingSource = new BindingSource();
    this.carMakerFilter = string.Empty;
    this.PopulateBindingSource();
    this.GridViewSetup();
    this.rbtnResetFilter.Enabled = false;
}

Private Sub MainForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Me._carsBindingSource = New BindingSource()
    Me._context = New SofiaCarRentalContext()
    Me._carMakerFilter = String.Empty
    Me.PopulateBindingSource()
    Me.GridViewSetup()
    Me.rbtnResetFilter.Enabled = False
End Sub

Now you need to define the PopulateBindingSource() method which by using the context object populates the carsBindingSource with the cars from the database. The method will also display the number of retrieved cars by setting it to the label lblResultCount.

private void PopulateBindingSource()
{
    this.ClearCache();
    this.selectedIndex = this.carsBindingSource.Position;
    List<Car> cars = null;
    if (string.IsNullOrEmpty(this.carMakerFilter))
    {
        cars = this.context.Cars.ToList();
    }
    else
    {
        cars = this.context.Cars.Where(car => car.Make == carMakerFilter).ToList();
    }
    this.carsBindingSource.DataSource = cars;
    this.lblResultCount.Text = string.Format("{0} entries retrieved.", cars.Count.ToString());
    this.carsBindingSource.Position = this.selectedIndex;
}

Private Sub PopulateBindingSource()
    Me.ClearCache()
    Me._selectedIndex = Me._carsBindingSource.Position
    Dim cars As IList(Of Car)
    If String.IsNullOrEmpty(Me._carMakerFilter) Then
        cars = Me._context.Cars.ToList()
    Else
        cars = Me._context.Cars.Where(Function(car) car.Make = Me._carMakerFilter).ToList()
    End If
    Me._carsBindingSource.DataSource = cars
    Me.lblResultCount.Text = String.Format("{0} entries retrieved.", cars.Count.ToString())
    Me._carsBindingSource.Position = Me._selectedIndex
End Sub

The ClearCache() method is called by PopulateBindingSource() in order to make sure that the data for cars that are already loaded will be updated.

private void ClearCache()
{
    var oldData = this.carsBindingSource.DataSource as IEnumerable<Car>;
    if (oldData != null)
    {
        this.context.ClearChanges();
    }
}

Private Sub ClearCache()
    Dim oldData As IEnumerable(Of Car) = Me._carsBindingSource.DataSource
    If IsNothing(oldData) = False Then
        Me._context.ClearChanges()
    End If
End Sub

Add the GridViewSetup() method which is called by the Load event handler. This method will set the DatSource property of the RadGridView and make some additional settings.

private void GridViewSetup()
{
    this.rgvCarsDisplay.DataSource = this.carsBindingSource;
    this.rgvCarsDisplay.AllowColumnChooser = false;
    this.rgvCarsDisplay.Columns["Latitude"].FormatString = "{0:F6}";
    this.rgvCarsDisplay.Columns["Longitude"].FormatString = "{0:F6}";
    this.rgvCarsDisplay.Columns["CarID"].IsVisible = false;
    this.rgvCarsDisplay.Columns["RentalOrders"].IsVisible = false;
    this.rgvCarsDisplay.Columns["Rating"].IsVisible = false;
    this.rgvCarsDisplay.Columns["NumberOfRatings"].IsVisible = false;
    this.rgvCarsDisplay.Columns["CategoryID"].IsVisible = false;
    this.rgvCarsDisplay.Columns["Category"].IsVisible = false;
    this.rgvCarsDisplay.AllowEditRow = false;
    this.rgvCarsDisplay.AllowDeleteRow = false;
    this.rgvCarsDisplay.AllowAddNewRow = false;
    this.rgvCarsDisplay.AutoSizeColumnsMode = GridViewAutoSizeColumnsMode.Fill;
}

Private Sub GridViewSetup()
    Me.rgvCarsDisplay.DataSource = Me._carsBindingSource
    Me.rgvCarsDisplay.AllowColumnChooser = False
    Me.rgvCarsDisplay.Columns("Latitude").FormatString = "{0:F6}"
    Me.rgvCarsDisplay.Columns("Longitude").FormatString = "{0:F6}"
    Me.rgvCarsDisplay.Columns("CarID").IsVisible = False
    Me.rgvCarsDisplay.Columns("RentalOrders").IsVisible = False
    Me.rgvCarsDisplay.Columns("Rating").IsVisible = False
    Me.rgvCarsDisplay.Columns("NumberOfRatings").IsVisible = False
    Me.rgvCarsDisplay.Columns("CategoryID").IsVisible = False
    Me.rgvCarsDisplay.Columns("Category").IsVisible = False
    Me.rgvCarsDisplay.AllowEditRow = False
    Me.rgvCarsDisplay.AllowDeleteRow = False
    Me.rgvCarsDisplay.AllowAddNewRow = False
    Me.rgvCarsDisplay.AutoSizeColumnsMode = GridViewAutoSizeColumnsMode.Fill
End Sub

Disposing the Context object

Whenever an OpenAccessContext object is initialized, a connection to the database is opened. This connection stays opened as long as the Context object lives. It is a good practice to dispose of the Context object when it is no longer needed. In the case of the current Quick Start application, the context should be disposed when the form is close - in the FormClosing event handler.

private void MainForm_FormClosing(object sender, FormClosingEventArgs e)
{
    if (this.context != null)
    {
        this.context.Dispose();
    }
}

Private Sub MainForm_FormClosing(sender As Object, e As FormClosingEventArgs) Handles MyBase.FormClosing
    If IsNothing(Me._context) = False Then
        Me._context.Dispose()
    End If
End Sub

Checkpoint

Now the application can load cars from the database and display them.

Next Step: Filtering and Refreshing the Displayed Data