4.6. Working with Data

The entities in the model definition contain no data. The easiest way to to load data is to call the Load method. For example,

private void LoadCustomersData()
{
    dbContext.CUSTOMERS.Load();
    var customers = dbContext.CUSTOMERS.Local;
    bindingSource.DataSource = customers.ToBindingList();
}
private void CustomerForm_Load(object sender, EventArgs e)
{
    LoadCustomersData();
    dataGridView.DataSource = bindingSource;
    dataGridView.Columns["CUSTOMER_ID"].Visible = false;
}

However, this approach has a few drawbacks:

1. The Load method loads all data from the CUSTOMER table to memory at once

2. Although lazy properties (INVOICES) are not loaded immediately, but only once they are accessed, they will be loaded anyway when the records are shown in the grid and it will happen each time a group of records is shown

3. Record ordering is not defined

To get around these drawbacks, we will use a feature of the LINQ (Language Integrated Query) technology, LINQ to Entities. LINQ to Entities offers a simple and intuitive approach to getting data using C# statements that are syntactically similar to SQL query statements. You can read about the LINQ syntax in LINQ to Entities.

4.6.1. LINQ Extension Methods

The LINQ extension methods can return two objects: IEnumerable and IQueryable. The IQueryable interface is inherited from IEnumerable so, theoretically, an IQueryable object is also an IEnumerable. In reality, they are distinctly different.

The IEnumerable interface is in the System.Collections namespace. An IEnumerable object is a collection of data in memory that can be addressed only in a forward direction. During the query execution, IEnumerable loads all data. Filtering, if required, is done on the client side.

The IQueryable interface is in the System.Linq namespace. It provides remote access to the database and movement through the data can be bi-directional. During the process of creating a query that returns an IQueryable object, the query is optimized to minimise memory usage and network bandwidth.

The Local property returns the IEnumerable interface, through which we can create LINQ queries.

private void LoadCustomersData()
{
    var dbContext = AppVariables.getDbContext();
    dbContext.CUSTOMERS.Load();
    var customers =
        from customer in dbContext.CUSTOMERS.Local
        orderby customer.NAME
        select new customer;
    bindingSource.DataSource = customers.ToBindingList();
}

However, as this query will be executed on the data in memory, it is really useful only for small tables that do not need to be filtered beforehand.

For a LINQ query to be converted into SQL and executed on the server, we need to access the dbContext.CUSTOMERS directly instead of accessing the dbContext.CUSTOMERS.Local property in the LINQ query. The prior call to dbContext.CUSTOMERS.Load(); to load the collection to memory is not required.

IQueryable and BindingList

IQueryable objects present a small problem: they cannot return BindingList. BindingList is a base class for creating a two-way data-binding mechanism. We can use the IQueryable interface to get a regular list by calling ToList but, this way, we lose handy features such as sorting in the grid and several more. The deficiency was fixed in .NET Framework 5 by creating a special extension. To do the same thing in FW4, we will create our own solution.

public static class DbExtensions
{
    // Internal class for map generator values to it
    private class IdResult
    {
        public int Id { get; set; }
    }
    // Cast IQueryable to BindingList
    public static BindingList<T> ToBindingList<T>
       (this IQueryable<T> source) where T : class
    {
        return (new ObservableCollection<T>(source)).ToBindingList();
    }
    // Get the next value of the sequence
    public static int NextValueFor(this DbModel dbContext, string genName)
    {
        string sql = String.Format(
          "SELECT NEXT VALUE FOR {0} AS Id FROM RDB$DATABASE", genName);
        return dbContext.Database.SqlQuery<IdResult>(sql).First().Id;
    }
    // Disconnect all objects from the DbSet collection from the context
    // Useful for updating the cache
    public static void DetachAll<T>(this DbModel dbContext, DbSet<T> dbSet)
       where T : class
    {
        foreach (var obj in dbSet.Local.ToList())
        {
            dbContext.Entry(obj).State = EntityState.Detached;
        }
    }
    // Update all changed objects in the collection
    public static void Refresh(this DbModel dbContext, RefreshMode mode,
        IEnumerable collection)
    {
        var objectContext = ((IObjectContextAdapter)dbContext).ObjectContext;
        objectContext.Refresh(mode, collection);
    }
    // Update the object
    public static void Refresh(this DbModel dbContext, RefreshMode mode,
        object entity)
    {
        var objectContext = ((IObjectContextAdapter)dbContext).ObjectContext;
        objectContext.Refresh(mode, entity);
    }
}

Other Extensions

There are several more extensions in the iQueryable interface:

NextValueFor

is used to get the next value from the generator.

dbContext.Database.SqlQuery

allows SQL queries to be executed directly and their results to be displayed on some entity (projection).

DetachAll

is used to detach all objects of the DBSet collection from the context. It is necessary to update the internal cache, because all retrieved data are cached and are not retrieved from the database again. However, that is not always useful because it makes it more difficult to get the latest version of records that were modified in another context.

Refresh

is used to update the properties of an entity object. It is useful for updating the properties of an object after it has been edited or added.

Code for Loading the Data

Our code for loading data will look like this:

private void LoadCustomersData()
{
    var dbContext = AppVariables.getDbContext();
    // disconnect all loaded objects
    // this is necessary to update the internal cache
    // for the second and subsequent calls of this method
    dbContext.DetachAll(dbContext.CUSTOMERS);
    var customers =
        from customer in dbContext.CUSTOMERS
        orderby customer.NAME
        select customer;
    bindingSource.DataSource = customers.ToBindingList();
}
private void CustomerForm_Load(object sender, EventArgs e)
{
    LoadCustomersData();
    dataGridView.DataSource = bindingSource;
    dataGridView.Columns["INVOICES"].Visible = false;
    dataGridView.Columns["CUSTOMER_ID"].Visible = false;
    dataGridView.Columns["NAME"].HeaderText = "Name";
    dataGridView.Columns["ADDRESS"].HeaderText = "Address";
    dataGridView.Columns["ZIPCODE"].HeaderText = "ZipCode";
    dataGridView.Columns["PHONE"].HeaderText = "Phone";
}

Adding a Customer

This is the code of the event handler for clicking the Add button:

private void btnAdd_Click(object sender, EventArgs e) {
  var dbContext = AppVariables.getDbContext();
  // creating a new entity instance
  var customer = (CUSTOMER)bindingSource.AddNew();
  // create an editing form
  using (CustomerEditorForm editor = new CustomerEditorForm()) {
    editor.Text = "Add customer";
    editor.Customer = customer;
    // Form Close Handler
    editor.FormClosing += delegate (object fSender,
      FormClosingEventArgs fe) {
      if (editor.DialogResult == DialogResult.OK) {
        try {
          // get next sequence value
          // and assign it
          customer.CUSTOMER_ID = dbContext.NextValueFor("GEN_CUSTOMER_ID");
          // add a new customer
          dbContext.CUSTOMERS.Add(customer);
          // trying to save the changes
          dbContext.SaveChanges();
          // and update the current record
          dbContext.Refresh(RefreshMode.StoreWins, customer);
        }
        catch (Exception ex) {
          // display error
          MessageBox.Show(ex.Message, "Error");
          // Do not close the form to correct the error
          fe.Cancel = true;
        }
      }
      else
        bindingSource.CancelEdit();
    };
    // show the modal form
    editor.ShowDialog(this);
  }
}

While adding the new record, we used the generator to get the value of the next identifier. We could have done it without applying the value of the identifier, leaving the BEFORE INSERT trigger to fetch the next value of the generator and apply it. However, that would leave us unable to update the added record.

Editing a Customer

The code of the event handler for clicking the Edit button is as follows:

private void btnEdit_Click(object sender, EventArgs e) {
  var dbContext = AppVariables.getDbContext();
  // get instance
  var customer = (CUSTOMER)bindingSource.Current;
  // create an editing form
  using (CustomerEditorForm editor = new CustomerEditorForm()) {
    editor.Text = "Edit customer";
    editor.Customer = customer;
    // Form Close Handler
    editor.FormClosing += delegate (object fSender, FormClosingEventArgs fe) {
      if (editor.DialogResult == DialogResult.OK) {
        try {
          // trying to save the changes
          dbContext.SaveChanges();
          dbContext.Refresh(RefreshMode.StoreWins, customer);
          // update all related controls
          bindingSource.ResetCurrentItem();
        }
        catch (Exception ex) {
          // display error
          MessageBox.Show(ex.Message, "Error");
          // Do not close the form to correct the error
          fe.Cancel = true;
        }
      }
      else
        bindingSource.CancelEdit();
    };
    // show the modal form
    editor.ShowDialog(this);
  }
}

The form for editing the customer looks like this:

Figure 25. Customer edit form

The code for binding to data is very simple.

public CUSTOMER Customer { get; set; }
private void CustomerEditorForm_Load(object sender, EventArgs e)
{
    edtName.DataBindings.Add("Text", this.Customer, "NAME");
    edtAddress.DataBindings.Add("Text", this.Customer, "ADDRESS");
    edtZipCode.DataBindings.Add("Text", this.Customer, "ZIPCODE");
    edtPhone.DataBindings.Add("Text", this.Customer, "PHONE");
}

Deleting a Customer

The code of the event handler for clicking the Delete button is as follows:

private void btnDelete_Click(object sender, EventArgs e) {
  var dbContext = AppVariables.getDbContext();
  var result = MessageBox.Show("Are you sure you want to delete the customer?",
    "Confirmation",
    MessageBoxButtons.YesNo,
    MessageBoxIcon.Question);
  if (result == DialogResult.Yes) {
    // get the entity
    var customer = (CUSTOMER)bindingSource.Current;
    try {
      dbContext.CUSTOMERS.Remove(customer);
      // trying to save the changes
      dbContext.SaveChanges();
      // remove from the linked list
      bindingSource.RemoveCurrent();
    }
    catch (Exception ex) {
      // display error
      MessageBox.Show(ex.Message, "Error");
    }
  }
}