Data Access has been discontinued. Please refer to this page for more information.

Retrieving Cars from the Database

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.

  1. 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
    
  2. 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
    
  3. 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
    
  4. 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
    
  5. 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
    
  6. 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