Get Employee Project Assignments with a Stored Procedure (IBX General Tutorial)

From RAD Studio
Jump to: navigation, search

Go Up to Tutorial: Using InterBase Express to Access a Database

In this section you build a form that uses a stored procedure to find an employee's projects. The process of creating this form is very similar to the procedure you followed in Audit Salary Changes, especially since you will use the "DmCSDemo" TDataModule again. You will add some database components, unlike the form in Audit Salary Changes.

After components are added, the project assignments form looks like this:

EmpProjAssignDesignTime.png


The top TDBGrid shows the EMPLOYEE table. The bottom TDBGrid shows the selected employee's projects.

Create the Form

Begin by adding another VCL Form to the project. In the Project Manager, right-click the project and click the menu item Add New > VCL Form.

Adjust the new form's properties:

Save the form:

  • For Delphi, save the file as Frmqrysp.pas.

To make use of the TDataModule in this form:

  • For Delphi, add the following line at the beginning of the implementation section of Frmqrysp.pas:
uses DmCSDemo;

Add Database Components

  • Add a TDataSource component to get information from the EMPLOYEE table. Set DataSet to "DmEmployee.EmployeeTable" from the drop-down menu. Note that this dataset is furnished by the "DmCSDemo" TDataModule. Change Name to "EmployeeSource".
  • Add a TIBQuery to access a database table using SQL statements.
    • Change Name to "EmployeeProjectsQuery".
    • Set Database to "DmEmployee.EmployeeDatabase" from the drop-down menu.
    • If necessary, set Transaction to "DmEmployee.IBTransaction1" from the drop-down menu.
    • Set the SQL property by clicking on the ellipsis (...) button for this property, which displays the CommandText Editor dialog. Enter this line in the SQL: pane of the dialog:
Select * from Get_Emp_Proj( :EMP_NO )

CommandTextEdtProj Assign.png


This SQL code calls the stored procedure Get_Emp_Proj, which returns a dataset containing all the projects for the given employee number.

Add Visual Components

  • Add a TStatusBar to the bottom of the form. You use this to display information about the currently selected employee's projects. Check SimplePanel true.
  • Add three TPanel components, positioning them as shown in the figure below. These TPanels provide a framework for positioning the visual components. Resize them to fill the form's area, except for the TStatusBar.

EmpProjAssignFrmPanels.png


  • Place a TBitBtn on the right side of "Panel1". Set the following properties:
    • Set Caption to "E&xit", so that pressing ALT+x is the same as clicking the button.
    • Set Kind to "bkClose" from the drop-down menu. This setting makes the button execute a command to close the dialog when you click the TBitBtn.
  • Place a TDBGrid on "Panel2". Resize it to fill the TPanel. Set DataSource to "EmployeeSource" from the drop-down menu.
  • Place another TDBGrid on "Panel3". Resize it to fill the TPanel. Set DataSource to "EmployeeProjectsSource" from the drop-down menu. The SQL query embodied in the TIBQuery obtains the dataset containing all the projects for a given employee number. The "EmployeeProjectsSource" TDataSource makes TIBQuery's dataset available for the TDBGrid.

Add Event Handlers and Other Code

Complete the form by adding one routine and creating three event handlers. You don't need an event handler for the TBitBtn, because this button already has a command associated with clicking it.

Add a WriteMsg routine that displays a message in the TStatusBar.

Delphi

Add this procedure in the private part of the type section of Frmqrysp.pas: 

procedure WriteMsg( strWrite : String );

You can use class completion by pressing CTRL-SHIFT-C to create a stub for this function in the implementation section.

Add this code for the new procedure: 

procedure TFrmQueryProc.WriteMsg(StrWrite: String);
begin
   StatusBar1.SimpleText := StrWrite;
end;

Next, create an event handler for the "EmployeeSource" TDataSource component. Select the "EmployeeSource" TDataSource in the Design tab, then double-click the OnDataChange event in the Object Inspector. This event handler keeps track of the currently selected employee in the top TDBGrid and updates the TStatusBar and the lower TDBGrid. Add this code for the event handler:

Delphi 

procedure TFrmQueryProc.EmployeeSourceDataChange(Sender: TObject;
  Field: TField);
begin
  { Execute the ProjectsQuery, which uses a query procedure }
  EmployeeProjectsQuery.Close;
  EmployeeProjectsQuery.Params[0].AsInteger :=
    DmEmployee.EmployeeTable.FieldByName('EMP_NO').AsInteger;
  EmployeeProjectsQuery.Open;

  WriteMsg('Employee ' + DmEmployee.EmployeeTable.FieldByName('EMP_NO').AsInteger +
           ' is assigned to ' + IntToStr(EmployeeProjectsQuery.RecordCount) +
           ' project(s).');
end;

Finally, write two event handlers for the form itself. In the Design tab, select the form. In the Object Inspector, double-click the OnShow event to create the skeleton code for the event. Add the following code:

Delphi 

procedure TFrmQueryProc.FormShow(Sender: TObject);
begin
  DmEmployee.EmployeeTable.Open;
  { Allow data flow from the EmployeeTable to the local EmployeeSource.  This
    will allow DataChange events to execute the query procedure }
  EmployeeSource.Enabled := True;
  { Explicit query preparation is not required, but gives the best possible
    performance }
  with EmployeeProjectsQuery do if not Active then Prepare;
end;

While the form is still selected, in the Object Inspector, double-click the OnHide event to create this event's code stub. Modify the stub as follows:

Delphi 

procedure TFrmQueryProc.FormHide(Sender: TObject);
begin
  { Turn off the DataChange event for our form, since DmEmployee.EmployeeTable
    is used elsewhere }
  EmployeeSource.Enabled := False;
end;

This completes the form.

Display the Table Viewing Form

Modify the main form created in Create Main Form. Add a button on that form to display the employee's projects form you just created.

Delphi

Add an additional line to the uses clause at the beginning of the implementation section of Frmmain.pas, so the main form knows about the FrmQrySP unit you just added: 

uses
   FrmQrySP,   { The Query Stored Procedure demo }
   FrmTrigg,   { The Trigger Demo }
   FrmViews;   { The View Demo }

Add another TButton to the Frmmain form, similar to the TButton that was added in Create Main Form. Set TButton.Caption "&Query Stored Procedure Demo". Set TButton.Name to "BtnQrySP". Then add an event handler for the new button by double-clicking that TButton in the Design tab of Frmmain form. Use the following code for the event handler:

Delphi 

procedure TFrmLauncher.BtnQrySPClick(Sender: TObject);
begin
  FrmQueryProc.ShowModal;
end;

Run the Application

Build and run the application. The main form displays:

FrmMainEmplProjAssign.png


Click the new "Query Stored Procedure Demo" TButton to display the new form:

FrmEmpProjAssignRunTime.png


The top TDBGrid displays the EMPLOYEE table from the database. The TStatusBar shows that the currently selected employee has no projects. The bottom TDBGrid displays no data other than the field's name PROJ_ID, since the employee has no projects. If you select an employee with projects, you see this:

EmpProjAssignWithProjects.png


The bottom TDBGrid shows the dataset returned by the TIBQuery's SQL command: the dataset containing all the projects for a given employee number. The TStatusBar shows the number of projects for the employee.

This concludes this project. You could expand it along the lines given in the CsDemos sample, installed with RAD Studio.

Previous

Audit Salary Changes

Retrieved from "https://docwiki.embarcadero.com/RADStudio/Rio/e/index.php?title=Get_Employee_Project_Assignments_with_a_Stored_Procedure_(IBX_General_Tutorial)&oldid=219435"