【DataModel】Let's try using On Pre-Process for a table

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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f3466c60-cd9a-12d5-e7b3-7350109c1735.png)

# 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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/56cbb820-4303-05a1-9548-22692c1855b0.png)
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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3f1114b5-b815-4755-cba9-3d69f354c184.png)
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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ccdc8d06-3ace-c3df-ca15-a5f8a158a5b7.png)
The `Import Objects` dialog will appear.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/396c20fe-81ef-1585-3393-a2fc66ea8f01.png)
"Use the `Get Tables` button to retrieve and display the table information.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7e25891c-610a-041e-17a9-9cb8c1ec9c6c.png)
Click the `OK` button to proceed. The table information has been loaded into the data model.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/19e1fd62-69ee-a253-bc9e-aa2436453ecb.png)
Click on `Tables` at the bottom left of the dialog to display a list of tables in the left-hand area.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2a2bbfdf-9c66-f370-04e0-349189945870.png)
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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/72acabd9-b954-97ad-345b-13bbecc82f8a.png)
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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b078201e-6db0-f74a-296b-13a8185839b9.png)
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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/cbb12780-ce01-08b2-d934-a475a50e7cc0.png)
Execute the query by clicking the 'Preview' button. It worked successfully.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/cec5c44c-e03e-4c96-d19a-a635aecc4e30.png)

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`.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/11b6e83d-09fc-4a1e-10f3-635e537503a1.png)
An initial code **SP0001** is created. Change the code from **SP0001** to **P1**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d851bef2-6e04-9816-7451-89b3d5440e65.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/589aa6b1-d28f-8c7d-a391-4f223b8641f0.png)
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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5a82eec0-5121-4d46-6449-6954da6b6432.png)
Next, select **New SQL Command** from the right-click menu of **P1**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/9c8502a7-0bb6-4c77-21a8-39c37a7ddd8a.png)
Change the code to **ExecSP**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/453b18d1-72c0-ab24-415b-773c7a9791cf.png)
Select **ExecSP** and display the text field in the center-bottom section.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8218474a-a374-6044-a203-da15f6e1e40e.png)
In the text field, enter the command to execute the stored procedure.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/9faa6f18-0722-04d5-4ccd-9503a93704fb.png)
## Specify the process ##
Next, go to the table definition. Select the `On Pre-process` attribute, and display the search button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/517800ef-5f98-636d-ccdb-57cd75088e5c.png)
Display the search dialog, and select the process **P1** that you created earlier.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1698993e-e857-83cd-849b-f26d0d2bc59c.png)
It should look something like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/eca8232b-a4bd-ef04-a57f-d16a614ce6ab.png)

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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/273f7491-6716-76d1-1b19-1e04266caa07.png)
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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d45bb6b6-e2ba-315d-618a-085890ab3086.png)
# 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.