Designer - Adding stored procedure calls

Preface

As mentioned in Concepts - Entities, lists and views, you can re-use existing code, defined in stored procedures in the target catalog(s) / schema(s). To add these stored procedures to your project, you go through the following steps:

Adding a stored procedure call

If you double click 'Stored Procedure Calls' in your Project explorer, a tree will be shown containing 'Retrieval Stored Procedure Calls' and 'Action Stored Procedure Calls'.
Stored Procedure Calls that return one or more resultsets are Retrieval Stored Procedure Calls. All others are Action Stored Procedure Calls.

• To add a stored procedure call, right-click either category and select 'Add Stored Procedure Calls'. You can also select 'Add New Action Stored Procedure Calls' or 'Add New Retrieval Stored Procedure Calls' from the Project menu. If the catalog schemas do not contain any Action stored procedures or Retrieval stored procedures the menu option of that category is greyed out.
• A screen will pop up, showing all stored procedures in the catalog which are not yet present in your project and fall in the category of the type of stored procedure call you want to add: Action or Retrieval.
  By default, all possible stored procedure calls are checked to be added to the project.
  You can check / uncheck stored procedure calls by either using your mouse, or by stepping through them using your keyboard arrows. The spacebar will toggle the checkbox belonging to the selected stored procedure call on and off. You can also select one or more rows and click the 'Toggle checkboxes of selected rows' button to toggle the checkboxes of the selected rows.
• When you're done selecting, click 'Add to project'.

After you've clicked 'Add to project' the call definitions are added to the project.

Note:

There is no support for parameterized stored queries in MS Access, so no procedure call can be created when targeting MS Access.

Editing a stored procedure call

To edit a stored procedure call, you click with the right mouse button on a stored procedure call, and select 'Edit / Properties' or press Ctrl + E. A stored procedure call editor opens in the tabbed area, showing the complete stored procedure call and all editable elements on two sub tabs: Parameters and Custom properties. The sub tabs can be navigated at the bottom of the stored procedure call editor tab. Besides these sub tabs, the editor allows you to alter the name of the stored procedure call and view the target procedure in the catalog explorer.

Parameters sub tab

The first sub tab is Parameters. This is the sub tab which contains all input and output parameters of the stored procedure and their specifics: type, size, real name etc. A screenshot of this sub tab of the EmployeeSalesbyCountry stored procedure call is shown below.



Parameters sub tab of the stored procedure call editor
When you select a parameter in the list, you can change its name. This name, as with all names, can't contain spaces and will have its first character upper cased. Also, all field names have to be unique within a stored procedure call definition.

To mark a parameter as a nullable parameter, you can check its Is nullable checkbox. A stored procedure's parameters are generated as method parameters for the generated method representing the stored procedure call in code. When the .NET type of the parameter is a ValueType, it will be generated as a method parameter of type Nullable<T> / Nullable(Of T) when .NET 2.0 is the target platform. When the target platform is .NET 1.x, the .NET type of the parameter in the generated stored procedure call method's signature will be object.

Custom properties sub tab

The second sub tab contains the custom property editor for the stored procedure call. As with other custom properties (except the project and typed list custom properties), these are initially derived from the mapped view's Extended properties (SqlServer) or description fields (Oracle, other databases). You can add new ones, delete the initially created custom properties or edit the custom properties. Custom properties are name-value pairs (both strings) which are generated into the code in a static hashtable. See for more information about using the custom properties available in the generated code: LLBLGen Pro generated code - Custom properties.

A screenshot of the stored procedure call's custom properties sub tab is shown below



Custom properties sub tab of the stored procedure call editor

All changes you'll make in the editor will be reflected in the project immediately. Also, everything is kept in sync, so when you change a stored procedure call's name, the name is changed everywhere in the GUI directly. As you will notice, when you change something, the project's icon in the project explorer will change to the yellow/red dot, indicating there have been changes. In addition, in the status bar the remark that the project has unsaved changes will appear.