In this post, I'd like to explore a solution for updating data in a table before running a query on it in Sharperlight. While there may be several methods to achieve this, this article will focus on using the Sharperlight data model.
As a simple example, we will use a table with a date field and a stored procedure to update that date field. In essence, the goal is to execute this stored procedure to update the data in the table before it is queried.
To begin, let's create an empty database using Microsoft SQL Manager. Within this database, we will prepare a table and a stored procedure.
CREATE TABLE [dbo].[Qiita_OnPreProcess_Refresh](
[RefreshedDateTime] [datetime] NOT NULL
)
CREATE PROCEDURE Qiita_Refresh
AS
IF NOT EXISTS (SELECT 1 FROM [dbo].[Qiita_OnPreProcess_Refresh])
BEGIN
INSERT INTO [dbo].[Qiita_OnPreProcess_Refresh] ([RefreshedDateTime]) VALUES(getdate());
END
ELSE
BEGIN
UPDATE [dbo].[Qiita_OnPreProcess_Refresh] SET [RefreshedDateTime] = getdate();
END
GO
Let's check it.
EXEC Qiita_Refresh
SELECT [RefreshedDateTime]
FROM [Miscellaneous].[dbo].[Qiita_OnPreProcess_Refresh]
If the result is like this, it's OK.
Preparing the datamodel
Now, let's prepare the datamodel so that Sharperlight can access the table. If you are using an existing datamodel, you can skip this step.
Select Studio from the Sharperlight application menu.
Click on the New icon to begin creating a new data model.
The New Product dialog will appear. Set the Description,Unique Code, and Target Platform as follows.
Click the OK button to proceed. You will be prompted to enter the connection information for the database. Configure it as shown and then check if the connection is established successfully by clicking the Connect button.
Click the OK button to proceed.
When prompted with the question "Would you like to import Table and View schema information into the Datamodel now?" choose Yes to proceed.
The Import Objects dialog will appear.
"Use the Get Tables button to retrieve and display the table information.
Click the OK button to proceed. The table information has been loaded into the data model.
Click on Tables at the bottom left of the dialog to display a list of tables in the left-hand area.
In the top left section of the table tree view, double-click to select the table.
In the central area, you will see the field information for the selected table, and on the right-hand side, you will see the table/field property information.
Once you've completed this, save the datamodel. When prompted for the save location, save it to the default location, which is C:\ProgramData\SharperLight\MetaData\.
Enter the database connection information for the Sharperlight application (as the previous one was for the datamodel).
Click Yes to proceed.
The client setup will be displayed. Enter the connection information, perform a test connection, and if it works successfully, click the 'OK' button to proceed.
Close the Sharperlight Studio as well. With these steps, you have gained access to the database and tables using the Sharperlight applications.
Open the Sharperlight Query Builder from the application menu and proceed with the functionality check. Select the product (datamodel) and the table, then set the displayed fields to the output area.
Execute the query by clicking the 'Preview' button. It worked successfully.
Now, let's get to the main topic. As it stands, the initial date and time I added will be queried as is. We need to make some edits to the datamodel to execute a stored procedure before the query is run to refresh the date and time value.
Setting up the On Pre-process
Open the datamodel as mentioned earlier, and display the table information. In the right-hand properties panel, locate the 'On Pre-process' attribute. You can enter the process name defined within the datamodel here. This process will be executed before this table is queried (Pre-process). In this process, you can call the stored procedure you created earlier. Let's get to work right away.
Defining the process
Launch Sharperlight Studio and open the data model you created earlier. Select Processes on the lower left-hand side, and choose New Process from the right-click menu of Custom Events.
An initial code SP0001 is created. Change the code from SP0001 to P1.
The display area in the center might still show SP0001. If that's the case, you can remove it by clicking the X button in the upper right corner of the central display area. Then, select it again, and the new code P1 should appear.
Next, select New SQL Command from the right-click menu of P1.
Change the code to ExecSP.
Select ExecSP and display the text field in the center-bottom section.
In the text field, enter the command to execute the stored procedure.
Specify the process
Next, go to the table definition. Select the On Pre-process attribute, and display the search button.
Display the search dialog, and select the process P1 that you created earlier.
It should look something like this.
That's all. Save the datamodel.
Test the functionality
Launch the Sharperlight Query Builder from the Sharperlight application menu. Select the product (datamodel), choose the table, and set the date and time field as an output item. Execute the query by clicking the Preview button.
The execution results are displayed. Initially, it might be unclear whether the stored procedure was executed. Record the current results and then execute the query again using the Refresh button. How does the result look? The time should have changed. Try it a few times, and you should confirm that the stored procedure is indeed working.
In conclusion
Today, I introduced a very simple example of updating time, but in day-to-day business operations, there are often situations where you want to update the target table with the latest information before refreshing reports. In such cases, options like this can be quite useful.