4.7.4. 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");
    }
  }
}