Firebird Documentation IndexFirebird 3.0 Developer's GuideDeveloping Firebird Applications with Microsoft Entity Framework → Secondary Modules
Firebird Home Firebird Home Prev: Working with DataFirebird Documentation IndexUp: Developing Firebird Applications with Microsoft Entity FrameworkNext: Working with Transactions

Secondary Modules

Table of Contents

Filtering Data
Loading the Invoice Data
Showing the Invoice Lines
Working with Stored Procedures
Showing Products for Selection

Our application will have only one secondary module, named “Invoices”. Secondary modules typically contain larger numbers of records than primary ones and new records are added to them frequently.

An invoice consists of a title where some general attributes are described (number, date, customer …) and invoice lines with the list of products, their quantities, prices, etc. It is convenient to have two grids for such documents: the main one showing the invoice header data and the detail one for the list of products sold. We will need one DataGridView component for each entity on the document form, binding the appropriate BindingSource to each.

Figure 4.16. Invoice form

Invoice form


Filtering Data

Most secondary entities contain a field with the document creation date. To reduce the amount of retrieved data, the concept of a work period is usually introduced to filter the data sent to the client. A work period is a range of dates for which the records are required. Since the application can have more than one secondary entity, it makes sense to add variables containing the start and end dates of a work period to the global AppVariables data module (see Getting a Context that is used by all modules working with the database in one way or another. Once the application is started, the work period is usually defined by the dates when the current quarter starts and ends, although of course, other options are possible. While working with the application, the user can change the work period.

Since the most recent records are the most requested, it makes sense to sort them by date in reverse order. As with the primary modules, we will use LINQ to retrieve data.

Loading the Invoice Data

Table of Contents

Paying an Invoice

The following method loads the invoice headers:

public void LoadInvoicesData() {
    var dbContext = AppVariables.getDbContext();

    var invoices =
        from invoice in dbContext.INVOICES
        where (invoice.INVOICE_DATE >= AppVariables.StartDate) &&
              (invoice.INVOICE_DATE <= AppVariables.FinishDate)
        orderby invoice.INVOICE_DATE descending
        select new InvoiceView
        {
            Id = invoice.INVOICE_ID,
            Cusomer_Id = invoice.CUSTOMER_ID,
            Customer = invoice.CUSTOMER.NAME,
            Date = invoice.INVOICE_DATE,
            Amount = invoice.TOTAL_SALE,
            Payed = (invoice.PAYED == 1) ? "Yes" : "No"
        };
    masterBinding.DataSource = invoices.ToBindingList();
}
        

To simplify type casting, we define an InvoiceView class, rather than use some anonymous type. The definition is as follows:

public class InvoiceView {
    public int Id { get; set; }

    public int Cusomer_Id { get; set; }

    public string Customer { get; set; }

    public DateTime? Date { get; set; }

    public decimal? Amount { get; set; }

    public string Payed { get; set; }

    public void Load(int Id) {
        var dbContext = AppVariables.getDbContext();
        var invoices =
            from invoice in dbContext.INVOICES
            where invoice.INVOICE_ID == Id
            select new InvoiceView
            {
                Id = invoice.INVOICE_ID,
                Cusomer_Id = invoice.CUSTOMER_ID,
                Customer = invoice.CUSTOMER.NAME,
                Date = invoice.INVOICE_DATE,
                Amount = invoice.TOTAL_SALE,
                Payed = (invoice.PAYED == 1) ? "Yes" : "No"
            };

        InvoiceView invoiceView = invoices.ToList().First();
        this.Id = invoiceView.Id;
        this.Cusomer_Id = invoiceView.Cusomer_Id;
        this.Customer = invoiceView.Customer;
        this.Date = invoiceView.Date;
        this.Amount = invoiceView.Amount;
        this.Payed = invoiceView.Payed;
    }
}
        

The Load method allows us to update one added or updated record in the grid quickly, instead of completely reloading all records. Here is the code of the event handler for clicking the Add button:

private void btnAddInvoice_Click(object sender, EventArgs e) {
  var dbContext = AppVariables.getDbContext();
  var invoice = dbContext.INVOICES.Create();
  using (InvoiceEditorForm editor = new InvoiceEditorForm()) {
    editor.Text = "Add invoice";
    editor.Invoice = invoice;
    // Form Close Handler
    editor.FormClosing += delegate (object fSender, FormClosingEventArgs fe) {
      if (editor.DialogResult == DialogResult.OK) {
        try {
          // get next sequence value
          invoice.INVOICE_ID = dbContext.NextValueFor("GEN_INVOICE_ID");
          // add a record
          dbContext.INVOICES.Add(invoice);
          // trying to save the changes
          dbContext.SaveChanges();
          // add the projection to the grid list
          ((InvoiceView)masterBinding.AddNew()).Load(invoice.INVOICE_ID);
        }
        catch (Exception ex) {
          // display error
          MessageBox.Show(ex.Message, "Error");
          // Do not close the form to correct the error
          fe.Cancel = true;
        }
      }
    };
    // show the modal form
    editor.ShowDialog(this);
  }
}
        

In our primary modules, the similarly-named method called dbContext.Refresh but, here, a record is updated by by calling the Load method of the InvoiceView class. The reason for the difference is that dbContext.Refresh is used to update entity objects, not the objects that can be produced by complex LINQ queries.

The code of the event handler for clicking the Edit button:

private void btnEditInvoice_Click(object sender, EventArgs e) {
  var dbContext = AppVariables.getDbContext();
  // find entity by id
  var invoice = dbContext.INVOICES.Find(this.CurrentInvoice.Id);
  if (invoice.PAYED == 1) {
    MessageBox.Show("The change is not possible, the invoice has already been paid.",
                    "Error");
    return;
  }
  using (InvoiceEditorForm editor = new InvoiceEditorForm()) {
    editor.Text = "Edit invoice";
    editor.Invoice = invoice;
    // Form Close Handler
    editor.FormClosing += delegate (object fSender, FormClosingEventArgs fe) {
      if (editor.DialogResult == DialogResult.OK) {
        try {
          // trying to save the changes
          dbContext.SaveChanges();
          // refresh
          CurrentInvoice.Load(invoice.INVOICE_ID);
          masterBinding.ResetCurrentItem();
        }
        catch (Exception ex) {
          // display error
          MessageBox.Show(ex.Message, "Error");
          // Do not close the form to correct the error
          fe.Cancel = true;
        }
      }
    };
    editor.ShowDialog(this);
  }
}
        

Here we needed to find an entity by the identifier provided in the current record. The CurrentInvoice is used to retrieve the invoice selected in the grid. This is how we code it:

public InvoiceView CurrentInvoice {
    get {
        return (InvoiceView)masterBinding.Current;
    }
}
        

Using the same approach, you can implement deleting the invoice header yourself.

Paying an Invoice

Besides adding, editing and deleting, we want one more operation for invoices: payment. Here is code for a method implementing this operation:

private void btnInvoicePay_Click(object sender, EventArgs e) {
  var dbContext = AppVariables.getDbContext();
  var invoice = dbContext.INVOICES.Find(this.CurrentInvoice.Id);
  try {
    if (invoice.PAYED == 1)
      throw new Exception("The change is not possible, the invoice has already been paid.");
    invoice.PAYED = 1;
    // trying to save the changes
    dbContext.SaveChanges();
    // refresh record
    CurrentInvoice.Load(invoice.INVOICE_ID);
    masterBinding.ResetCurrentItem();
  }
  catch (Exception ex) {
    // display error
    MessageBox.Show(ex.Message, "Error");
  }
}
          

Showing the Invoice Lines

We have two choices for displaying the invoice lines:

  1. Getting data for each invoice from the INVOICE_LINE navigation property and displaying the contents of this complex property in the detail grid, probably with LINQ transformations
  2. Getting the data for each invoice with a separate LINQ query that will be re-executed when the cursor moves to another record in the master grid

Either way has its advantages and drawbacks.

The first one assumes that we want to retrieve all invoices at once for the specified period together with the bound data from the invoice lines when the invoice form is opened. Although it is done with one SQL query, it may take quite a while and requires a large amount of random-access memory. It is better suited to web applications where records are usually displayed page by page.

The second one is a bit more difficult to implement, but it allows the invoice form to be opened quickly and requires less resource. However, each time the cursor in the master grid moves, an SQL query will be executed, generating network traffic, albeit with only a small volume of data.

For our application we will use the second approach. We need an event handler for the BindingSource component for editing the current record:

private void masterBinding_CurrentChanged(object sender, EventArgs e) {
    LoadInvoiceLineData(this.CurrentInvoice.Id);
    detailGridView.DataSource = detailBinding;
}
        

Now, the method for loading the invoice data:

private void LoadInvoiceLineData(int? id) {
  var dbContext = AppVariables.getDbContext();
  var lines =
      from line in dbContext.INVOICE_LINES
      where line.INVOICE_ID == id
      select new InvoiceLineView
      {
          Id = line.INVOICE_LINE_ID,
          Invoice_Id = line.INVOICE_ID,
          Product_Id = line.PRODUCT_ID,
          Product = line.PRODUCT.NAME,
          Quantity = line.QUANTITY,
          Price = line.SALE_PRICE,
          Total = Math.Round(line.QUANTITY * line.SALE_PRICE, 2)
      };
  detailBinding.DataSource = lines.ToBindingList();
}
        

We use the InvoiceLineView class as an extension:

public class InvoiceLineView {
    public int Id { get; set; }
    public int Invoice_Id { get; set; }
    public int Product_Id { get; set; }
    public string Product { get; set; }
    public decimal Quantity { get; set; }
    public decimal Price { get; set; }
    public decimal Total { get; set; }
}
        

Note

Unlike the InvoiceView class, this one has no method for loading one current record. In our example, the speed of reloading the detail grid it is not crucial, because one document does not contain thousands of items. Implementing this method is optional.

Now we will add a special property for retrieving the current line of the document selected in the detail grid.

public InvoiceLineView CurrentInvoiceLine {
    get {
        return (InvoiceLineView)detailBinding.Current;
    }
}
        

Working with Stored Procedures

The methods we will use for adding, editing and deleting illustrate how to work with stored procedures in Entity Framework. As an example, this is the method for adding a new record:

private void btnAddInvoiceLine_Click(object sender, EventArgs e) {
  var dbContext = AppVariables.getDbContext();
  // get current invoice
  var invoice = dbContext.INVOICES.Find(this.CurrentInvoice.Id);
  if (invoice.PAYED == 1) {
    MessageBox.Show("The change is not possible, the invoice has already been paid.", "Error");
    return;
  }
  // create invoice position
  var invoiceLine = dbContext.INVOICE_LINES.Create();
  invoiceLine.INVOICE_ID = invoice.INVOICE_ID;
  // create the position editor of the invoice
  using (InvoiceLineEditorForm editor = new InvoiceLineEditorForm()) {
    editor.Text = "Add invoice line";
    editor.InvoiceLine = invoiceLine;
    // Form Close Handler
    editor.FormClosing += delegate (object fSender, FormClosingEventArgs fe) {
      if (editor.DialogResult == DialogResult.OK) {
        try {
          // create SP parameters
          var invoiceIdParam = new FbParameter("INVOICE_ID",
                                               FbDbType.Integer);
          var productIdParam = new FbParameter("PRODUCT_ID",
                                               FbDbType.Integer);
          var quantityParam = new FbParameter("QUANTITY", FbDbType.Integer);
          // initial parameters values
          invoiceIdParam.Value = invoiceLine.INVOICE_ID;
          productIdParam.Value = invoiceLine.PRODUCT_ID;
          quantityParam.Value = invoiceLine.QUANTITY;
          // execute stored procedure
          dbContext.Database.ExecuteSqlCommand(
            "EXECUTE PROCEDURE SP_ADD_INVOICE_LINE("
          + "@INVOICE_ID, @PRODUCT_ID, @QUANTITY)",
             invoiceIdParam,
             productIdParam,
             quantityParam);
          // refresh grids
          // reload current invoice record
          CurrentInvoice.Load(invoice.INVOICE_ID);
          // reload all record in detail grid
          LoadInvoiceLineData(invoice.INVOICE_ID);
          // refresh all related data
          masterBinding.ResetCurrentItem();
        }
        catch (Exception ex) {
          // display error
          MessageBox.Show(ex.Message, "Error");
          // Do not close the form to correct the error
          fe.Cancel = true;
        }
      }
    };
    editor.ShowDialog(this);
  }
}
        

With our example, an update of the master grid record will be needed because one of its fields (TotalSale) contains aggregated information derived from the detail lines of the document. This is how we do that:

private void btnEditInvoiceLine_Click(object sender, EventArgs e) {
  var dbContext = AppVariables.getDbContext();
  // get current invoice
  var invoice = dbContext.INVOICES.Find(this.CurrentInvoice.Id);
  if (invoice.PAYED == 1) {
    MessageBox.Show("The change is not possible, the invoice has already been paid.",
                    "Error");
    return;
  }
  // get current invoice position
  var invoiceLine = invoice.INVOICE_LINES
         .Where(p => p.INVOICE_LINE_ID == this.CurrentInvoiceLine.Id)
         .First();
  // create invoice position editor
  using (InvoiceLineEditorForm editor = new InvoiceLineEditorForm()) {
    editor.Text = "Edit invoice line";
    editor.InvoiceLine = invoiceLine;
    // form close handler
    editor.FormClosing += delegate (object fSender, FormClosingEventArgs fe) {
      if (editor.DialogResult == DialogResult.OK) {
        try {
          // create parameters
          var idParam = new FbParameter("INVOICE_LINE_ID", FbDbType.Integer);
          var quantityParam = new FbParameter("QUANTITY", FbDbType.Integer);
          // initial parameters values
          idParam.Value = invoiceLine.INVOICE_LINE_ID;
          quantityParam.Value = invoiceLine.QUANTITY;
          // execute stored procedure
          dbContext.Database.ExecuteSqlCommand(
              "EXECUTE PROCEDURE SP_EDIT_INVOICE_LINE("
            + "@INVOICE_LINE_ID, @QUANTITY)",
              idParam,
              quantityParam);
          // refresh grids
          // reload current invoice record
          CurrentInvoice.Load(invoice.INVOICE_ID);
          // reload all records in detail grid
          LoadInvoiceLineData(invoice.INVOICE_ID);
          // refresh all related controls
          masterBinding.ResetCurrentItem();
        }
        catch (Exception ex) {
          // display error
          MessageBox.Show(ex.Message, "Error");
          // Do not close the form to correct the error
          fe.Cancel = true;
        }
      }
    };
    editor.ShowDialog(this);
  }
}
        

Deleting an Invoice Detail Line

The method for deleting a detail record is implemented as follows:

private void btnDeleteInvoiceLine_Click(object sender, EventArgs e) {
  var result = MessageBox.Show(
    " Are you sure you want to delete the invoice item?",
    "Confirmation",
    MessageBoxButtons.YesNo,
    MessageBoxIcon.Question);
  if (result == DialogResult.Yes) {
    var dbContext = AppVariables.getDbContext();
    // get current invoice
    var invoice = dbContext.INVOICES.Find(this.CurrentInvoice.Id);
    try {
      if (invoice.PAYED == 1)
        throw new Exception("It is not possible to delete the entry, the invoice is paid.");
      // create parameters
      var idParam = new FbParameter("INVOICE_LINE_ID", FbDbType.Integer);
      // initialize parameters values
      idParam.Value = this.CurrentInvoiceLine.Id;
      // execute stored procedure
      dbContext.Database.ExecuteSqlCommand(
        "EXECUTE PROCEDURE SP_DELETE_INVOICE_LINE(@INVOICE_LINE_ID)",
        idParam);
      // update grids
      // reload current invoice
      CurrentInvoice.Load(invoice.INVOICE_ID);
      // reload all records in detail grids
      LoadInvoiceLineData(invoice.INVOICE_ID);
      // refresh related controls
      masterBinding.ResetCurrentItem();
    }
    catch (Exception ex) {
      // display error
      MessageBox.Show(ex.Message, "Error");
    }
  }
}
          

Showing Products for Selection

In the methods for adding and editing invoice lines we used the form. For displaying products, we will use a TextBox control.

Figure 4.17. Product form

Product form


A click on the button next to the TextBox will open a modal form with a grid for selecting products. The same modal form created for displaying the products is used for selecting them. The click handler code for the embedded button that initiates the form is:

public partial class InvoiceLineEditorForm : Form {
    public InvoiceLineEditorForm() {
        InitializeComponent();
    }

    public INVOICE_LINE InvoiceLine { get; set; }

    private void InvoiceLineEditorForm_Load(object sender, EventArgs e) {
        if (this.InvoiceLine.PRODUCT != null) {
            edtProduct.Text = this.InvoiceLine.PRODUCT.NAME;
            edtPrice.Text = this.InvoiceLine.PRODUCT.PRICE.ToString("F2");
            btnChooseProduct.Click -= this.btnChooseProduct_Click;
        }
        if (this.InvoiceLine.QUANTITY == 0)
            this.InvoiceLine.QUANTITY = 1;
        edtQuantity.DataBindings.Add("Value", this.InvoiceLine, "QUANTITY");
    }

    private void btnChooseProduct_Click(object sender, EventArgs e) {
        GoodsForm goodsForm = new GoodsForm();
        if (goodsForm.ShowDialog() == DialogResult.OK) {
            InvoiceLine.PRODUCT_ID = goodsForm.CurrentProduct.Id;
            edtProduct.Text = goodsForm.CurrentProduct.Name;
            edtPrice.Text = goodsForm.CurrentProduct.Price.ToString("F2");
        }
    }
}
        

Prev: Working with DataFirebird Documentation IndexUp: Developing Firebird Applications with Microsoft Entity FrameworkNext: Working with Transactions
Firebird Documentation IndexFirebird 3.0 Developer's GuideDeveloping Firebird Applications with Microsoft Entity Framework → Secondary Modules