【Publisher】Writeback using Restful service

This time, we will use the Restful service of [Sharperlight](https://www.sharperlight.com/) to create a data write process in JavaScript.
Let's start from scratch.
## Preparing the database ##
Create a new database in Microsoft SQL Server.
### New database ###
Create a new database with Microsoft SQL Management Studio as follows: Shall we call it **QiitaTopic**
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4e86b9fb-5f4d-393f-4965-71bdb9f5dbe8.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b7728c4e-6544-6569-c7bf-a514c7911179.png)
### Creating table ###
Prepare a table for storing data. Create by script.
```
CREATE TABLE [dbo].[Sales_Items](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[code] [varchar](512) NOT NULL,
	[name] [varchar](1024) NOT NULL,
	[qty] [int] NOT NULL,
	[amount] [decimal](18,0) NOT NULL,
	[date] [datetime] NOT NULL
 CONSTRAINT [PK_Sales_Items_ID] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/18ffde4c-4ea6-01f9-ca54-34e5eba15b8e.png)
## Creating a data model ##
Launch **Studio** from the Sharperlight application menu.
Click the new icon to get ready to connect to the database you just created.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/73c6b79a-f2af-88df-18ca-323c6ceef449.png)
Set **Description**, **Unique Code**, **Target Platforms** and press the `OK` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f25a42f3-0f41-b4d1-ca38-845de2c23c90.png)
A dialog for entering the connection details to the database will be displayed, so enter the correct connection information. You can check the connection with the `Test` button. I used Windows authentication this time, but you can also use SQL server authentication.
If you can confirm that the connection is done correctly with the `Test` button, save it with the `OK` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e1d9b1c9-6818-4e50-c54c-b0603b9aade3.png)
A message appears prompting you to load the database schema information. Click the `Yes` button to proceed.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f3d536cd-2418-10e0-5aa7-05fdbb6aaaea.png)
The Import Objects dialog will appear. Keep the default settings and press the `Get Tables` button (green button).
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ee182889-065b-4818-f0c7-25adff3d24ea.png)
The schema information of the database is displayed in a tree format, so confirm that all are checked and proceed with the `OK` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0e6e5749-cac9-c16d-4438-a8bb4c90e3f3.png)
A message **Created 1 new Tables** will be displayed, so click the `OK` button to proceed.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/9d1d831f-ac2c-680c-5707-b7869ce41df9.png)
The table tree will be displayed on the left, so open the **Other** folder and double-click the displayed table. A list of fields appears in the middle area. This is the initial state with the schema information loaded.
The table is also in the **Other** folder. This state is reflected in the display of the table search dialog of the **Query Builder**. The meaning of the **Other** folder is unknown, so consider renaming it or moving the table to the root folder.
Move it to the root folder, i.e. the **Table Display** folder, then delete the **Other** folder.
Select the **Table Display** folder and select **Move Tables** from the right-click menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d6133794-9fd5-18df-9b7c-5d3e42e37bf5.png)
Since the table list is displayed, put a check mark and press the OK button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/945ea5e6-67d5-47a9-7b32-d58ce2cfc0f9.png)
After confirming that the table has been moved, select the **Other** folder and select **Delete Folder** from the right-click menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/236c51bc-d9a5-5a8d-44ab-362b447ff441.png)
A confirmation message will be displayed, so select the `Yes` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1a6ecf84-4cef-ad41-9144-c29039360cd6.png)
The **Other** folder will be deleted and you will have a clean state like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/70ab85f2-354a-7542-38bb-9c0386af3372.png)
Then set the table to data writable. <INS>**Sharperlight basically prohibits writing data.**</INS>
This time we want to write data to this table, so we give this table `Writeback` permission.
When you select the table, the table properties list will appear in the right pane. Locate the **Allow Table Writeback** property and set it to **True**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5ad8f818-bee5-8b21-9431-5f6d4d968798.png)
This completes the creation of the data model. Select **Exit** from the **File** menu. A message prompting you to save will be displayed, so save with the `Yes` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d313acf3-c9d4-c6d6-dfa1-bbb340921046.png)
A note about where to save the data model will be displayed, so click the `OK` button to proceed.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/961e4363-c681-2518-c541-f99ecb28968a.png)
The file explorer will be displayed, so leave the destination as it is and click the `Save` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/291e683d-7dd9-f7df-be5e-2860eba1e56d.png)
The data model is now ready.
## Connecting database and data model ##
Launch **Client Setup** from the Sharperlight application menu.
Set the database connection information for the target data model **QiitaTopic** and check the connection with the `test connection` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/69b6f211-668a-3e63-2f01-bf3e7cde6473.png)
Sharperlight can now talk to the database through the data model.
Then enable the service. The settings are as shown below. The service code is used as part of the service's URL.
Save with the `OK` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/31edd2b3-b78c-477d-c32d-f38cb18b43f2.png)
## Preparing data writing UI and backend code ##
In this article, the data written is a fixed value, and only the `Save` button etc. is placed on the web page. Then we will see how to create writeback using RESTful services.
### Starting RESTful service ###
Now let's start the service. Services can be started from the Sharperlight application menu or from Windows Services.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1ba03dbe-671a-18c5-14ee-e2cc4e815593.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/becaca5c-1bc7-1bd4-6c01-b7f3e664c324.png)
### Viewing the Service Index Page ###
After the service starts, display the index page. Use the URL below.
```
http://{your server name is here}/mdService1Rest/Index
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/269e71c1-79fc-7163-09c4-874236fe6dc5.png)
### Getting JavaScript template for writeback ####
Templates are accessed from the index page under Writeback -> Metadata. Enter the code **QTX** of the data model created earlier in the **Product Code** filter and press the send button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c5084dd6-3058-ab95-49d3-4b735c6f8c26.png)
A list of tables will be displayed immediately below, so select the target table name **Sales Items**. Various URLs and JavaScript templates are displayed on the right.
Copy **JavaScript POST Simple** or **JavaScript POST Full** to your clipboard.
Create a new report in Publisher and check **Header** on the `Options` tab.
Launch the editor with the `Edit` button and paste the template previously copied to the clipboard.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f64ea286-261c-a067-3d84-6abbce5e23d4.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/be062abb-bd39-b40a-ceda-f6c471d4d6ea.png)
And since the writeback data is a fixed value, set the test data here in the template code.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/062a7336-7cd0-3f48-6bda-49a42e199214.png)
Save the template code with the `OK` button.
Create a query for the **Sales Items** table with the `Edit Query` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d0f710af-eb4e-6b9f-1aaf-a6c503605016.png)
After creating the query, enter the report `Group` name and `Code` and save it.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6ffce045-9433-fc38-1204-11bc08322105.png)
Select `View Report` from the right-click menu to view the report in a browser.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f282928f-4f02-ef99-b4af-4d1770a99e5e.png)
A page like this was displayed.
When you press the `SAVE` button, the test data set earlier will be written to the database.
If the writeback is finished successfully, the `SAVE` button will turn green like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f9f768ce-a719-9b98-0e67-a70395b114ed.png)
Try refreshing the report or querying the **Sales_Items** table in Microsoft SQL Management Studio to see if the data is actually being written.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/768835f7-b23d-6455-d9df-4d6b230a63ff.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4890b1d5-753b-81e8-07ce-bd1252b330cc.png)

In this way, Sharperlight is not just a reporting tool, but by using it in common with JavaScript, HTML, etc., various solutions can be created without much effort, which is very interesting.

Thank you for reading this article.