Use an Imported Stored Procedure

Using LoadDatasource within an Event allows you to assign execution of the Stored Procedure to an Element on your form to be performed on demand. For example, you could assign it to a Button's Clicked Event, so that when the user clicks or taps the Button the Stored Procedure will be run. Assigning an action to an Event is done within the Events properties for the Element you want to invoke the Stored Procedure.

If the Stored Procedure requires parameters to be passed into it, each parameter will take its value at runtime from a specified Element on the form. When you configure the Event to run the LoadDatasource action, you will be asked to specify which Element will be used to provide that value for each of the parameters. You must specify an Element for each of the required parameters. You specify these Element mappings within the Event configuration and not as output mappings. For Imported Stored Procedures which return a Result Set you can use input mappings to display downloaded data in your Elements, but you don't specify any output mappings at all.

To assign the running of an Imported Stored Procedure to an Event, select the Element you want to invoke the Stored Procedure to display its properties in the Properties Pane and then click on the Event under the Events tab to load the Event Builder pop-up.

Within the Event Builder pop-up, select LoadDatasource under Database Operation and then select the appropriate Datasource and Dataset corresponding to the Stored Procedure Result Set that you want to be returned. You can then select the SQL query to run from the Query option drop-down list - there should only be the one which was generated automatically when the Dataset was created. After selecting the Query, if there are any parameters required by the Stored Procedure, additional fields will be displayed; one for each parameter:

 

In the example above, our chosen Stored Procedure requires two parameters - @Beginning_Date and @Ending_Date. For each parameter, you will need to specify an Element on your form which will provide the value for that parameter at runtime. Click on the button beside one of the parameters to display the following additional fields:

These fields allow you to specify the Element and its property from which the value for the parameter will be extracted at runtime. Select the page in the form which contains the required Element from the Page drop-down followed by the name of the Element from the Element drop-down. Select the property of the Element which will contain the value you want to use from the Property drop-down; this will usually be the Value property but may not always be. Click Save to map your chosen Element to this parameter or Cancel to abandon the Element selection.

Repeat this process for each of the parameters listed.

When you have finished, click Save to assign the defined action to the Event or Cancel to close the pop-up without assigning any action to the Event. If you clicked Save, the action name will be displayed in the Event property within the Properties Pane.

Now when the Event is triggered at runtime, any required parameter values will be extracted from the named Elements and entered into the SQL query before the query is forwarded to the SQL Server instance specified in the connection string, where the Stored Procedure will be executed.

 

If a Stored Procedure returns multiple Result Sets and you have created Datasets corresponding to more than one Result Set, you will need to configure separate Events for each Dataset. For example, if you want to execute a Stored Procedure on the click of a Button and obtain the data from two returned Result Sets, you would actually need to add two buttons to your form, one for each Result Set as you can only assign one Dataset, and hence Result Set, to an Event. If you want to download multiple Result Sets from a single button, you could use a custom JavaScript function assigned to the Event to do this instead of using the Event actions (see below).

 

When you configure an Event to run a LoadDatasource action, you specify a Dataset to be associated with that action. At runtime when the Stored Procedure executes and returns its Result Sets to your form, the form will check the Result Sets for one which matches the structure of the specified Dataset and load the data from that Result Set into the Dataset, making the data available to your form. The first record in the data will be made the current record and any Elements input mapped to this Dataset will automatically be updated with, and display, the values from that record.

 

If you want to invoke a Stored Procedure within custom JavaScript, you will need to do so by calling the loadDatasource scripting function. You will need to pass any parameters required by the Stored Procedure into the function as parameters of the function and specify the Datasource and Dataset which correspond to the Stored Procedure and the appropriate Result Set within the function call. For more details, including sample code, refer to the loadDatasource function description.

If a Stored Procedure returns multiple Result Sets and you have created Datasets corresponding to more than one of these Result Sets, you will need to call loadDatasource independently for each Dataset; you cannot read in multiple Result Sets from a single loadDatasource call. Therefore, for example, if you want to obtain the data from two Result Sets returned by the same Stored Procedure, you would need to call loadDatasource twice, with one call specifying one of the two corresponding Datasets and the second call specifying the other Dataset. You can, however, make both calls from within a single JavaScript function, if you want to, though you might need to use the onResponseReceived parameter if you need the Result Sets to be downloaded in a specific order.

 

When you configure an Event to run a LoadDatasource action, you specify a Dataset to be associated with that action. At runtime when the Stored Procedure executes and returns its Result Sets to your form, the form will check the Result Sets for one which matches the structure of the specified Dataset and load the data from that Result Set into the Dataset, making the data available to your form. The first record in the data will be made the current record and any Elements input mapped to this Dataset will automatically be updated with, and display, the values from that record.