Upload
- Sergio Rodriguez
- Matias
Overview
On this lesson, we are going to review the “Upload” control that will help us to allocate files under an specific repository in Wayfast. File uploading is a feature for accepting and managing images, videos, PDFs, or other documents. A file uploader looks like a button on a website that opens a dialogue box where they can choose, attach, and submit files. Let’s understand how we can setup this control in simple steps!
Preconditions
1-Create a new table in SQL
Initially we need to create a new table in our database to connect the “Upload” action with the Wayfast repository where we are going to allocate the files.
CRM_Activities table will store the name and the upload details from the file loaded. “RepositoryId” is an important attribute that will be the identifier on Wayfast repository.
Let’s take a look on the SQL query:
CREATE TABLE dbo.CRM_Activities(
[RepositoryId] INT,
[FileName] VARCHAR(300),
UploadDate DATETIME
)
GOOnce the table is created, automatically the ID attribute will be generated by default including the procedures to manage the information stored in the Database for this table. There are 3 procedures that Wayfast generated automatically:
spWF_CRM_Activities_CreateOrUpdatespWF_CRM_Activities_DeletespWF_CRM_Activities_Get
2-Create a Repository table to storage the files
It’s important to create another object Repository_File_Repository as a generic table to storage all the files loaded from the Upload control in Wayfast.
CREATE TABLE dbo.Repository_File_Repository(
[RepositoryId] [int] IDENTITIY(1,1) NOT NULL primary key,
[FileName] [varchar](300) NULL,
[Size] [bigint] NULL,
[Data_File] [image] NULL,
[CreationDate] [datetime] NOT NULL DEFAULT (getdate()),
[ID] [uniqueidentifier] NULL
)
GOThis is specially needed when we are working on a testing environment and we want to run this script to have everything setup.
If the table already exist or we want to point to an existing table, this step is not required.
3-Create a new stored procedure
We need to create an stored procedure “sp_CRM_Activities_Insert” to establish the connection between the “CRM_Activities” table for upload and the Wayfast repository using the RepositoryId as key parameter to identify in which place this should be stored.
In the example we are using the generic table from Step 2 to insert the new files into the repository.
CREATE PROCEDURE dbo.sp_CRM_Activities_Insert
@RepositoryId int,
AS
BEGIN
INSERT INTO dbo.CRM_Activities(RepositoryId,FileName,UploadDate)
SELECT r.RepositoryId,r.[FileName], GETDATE()
FROM dbo.Repository_File_Repository r (nolock)
WHERE r.RepositoryId=@RepositoryId
END
GO
Now that we insert the “RepositoryID” into the Repository_File_Repository table, we can use the information to display all the files in grid table under any page in the application. Using this stored procedure, we will handle the file insertion on the table where we allocate all the files in the repository.
How to create an Upload control?
Now that we have the table and the stored procedure created, we can start creating the “Upload” control. Let’s synchronize first the objects created in database to make sure that they are now visible in Wayfast.
Click on “End Task” button related to Project’s requirement.
This step is only needed for synchronization of the new Table and Stored Procedure generated in DB.
A popup is displayed, click on “Synchronize” button. Now we are able to select all the objects from the Database in our project’s environment.
Navigate to “Project Pages” at the top menu and enter to the page where we are going to work in this lesson. Select the “Upload” control type.
Click on “New” button and popup is displayed on the screen with the control’s required fields to complete this functionality.
On “General” tab, it’s important to identify the following values as required:
Object Name: RepositoryId
Displayed Name: Attach files
Default Value: &RepositoryId
This “Upload” control allow us to create multiple actions depending on what we are going to do with the uploaded file. On next steps we will understand the importance to connect the “Upload” action with the objects previously created in database.
Let’s navigate to the next “Data Binding” tab. In this section we are not seeing fields to connect to dataset or stored procedures like we used to in control’s creation. We only have actions to connect this control. Our first action should be related to store the uploaded files in the Wayfast repository or the generic table that we created in Step 2 named Repository_File_Repository table
Wayfast allow us to create a new action at the same time that we create the control. Click on “New Action”
Before complete this step, let’s review all the fields and the different options that we have to create the action:
Fields | Description |
|---|---|
Control | Needs to point to our RepositoryId |
Order | Execution order related to the action under the Upload control |
Prototype | Choose if this is a prototype |
Action Groups | Select “Upload” as default |
Action | Select “Upload” as default |
Repository Type |
|
Database | Only needed when the “Repository Type” is Custom. Must indicate the Database where the repository is allocated. |
Schema | Only needed when the “Repository Type” is Custom. Must indicate the Schema that the repository is using. |
Table | Only needed when the “Repository Type” is Custom. Must indicate the Table from the repository. |
Field ID Repository | Only needed when the “Repository Type” is Custom. Must indicate the ID field that the repository is using to store the file uploaded. |
Field Name Field | Only needed when the “Repository Type” is Custom. Must indicate the Filename field that the repository is using to store the file uploaded. |
Field Size | Only needed when the “Repository Type” is Custom. Must indicate the Size field that the repository is using to store the file uploaded. |
Field Data Field | Only needed when the “Repository Type” is Custom. Must indicate the Data field that the repository is using to store the file uploaded. |
Upload Parameters | This field will indicate manually all the parameters to establish the Upload action. We will analyze further these items on the next steps. |
Instance | Select the instance where the Repository table is allocated. |
Return Value | Indicate the return value from the action created. |
Conditional | Set the validation rule to execute the action (if needed) |
In our example we are going to include the following information:
Let’s focus on the parameters:
Parameters | Description |
|---|---|
RepositoryID | This is pointing to the |
4 | Code to establish connection, by default we use this value on Wayfast |
Asignet2.dbo.Repository_File_Repository | This parameter indicates the |
FileName | Indicate the name of the file uploaded. |
Size | Indicate the size of the file uploaded. |
Data_File | Indicate the type of file uploaded. |
We can complete all the required fields for this action and click on “Submit” button to confirm the creation. The new action is now linked to the “Upload” control.
At this point, the action associated to the control will allow us to storage the files in the repository. Early in the “General” tab, we mentioned the importance to identify the default value in “Upload” control, now that we finished with the setup, we can say that the triggered action automatically will return a “RepositoryId” value for each uploaded file. This is something important if we want to display all the files uploaded in another page using a grid table.
Don’t forget to click on “Submit” button to create our new “Upload” control and finish the process.
How to preview all the uploaded files in the Application?
In our later example, we created the control and the action to upload files. We can use this first example to make all the files visible, for example, on a grid table. Let’s check how we can show this information from “Upload” control in our application and make these files available to be listed or downloadable.
Step 1-Create a new dataset to get the information from Upload Files table
Before start with the grid table, it’s necessary to create a new dataset in the page.
Click on “New Dataset” button
A popup is displayed on the screen. In this case, we are going to name this dataset as “DTSGetListActFile” to identify on the Upload control.
After we identified the dataset, go to “Data Binding” tab and connect to the stored procedure that we created in database named sp_WF_CRM_Activities_Get. Keep in mind that this stored procedure was created by default when the CRM_Activities table was generated in Database.
Parameters: '-1'
We can take note on the relationship between the “Upload” control and this dataset by clicking on “Add Relationship” button. Click on “Submit” button to generate the dataset. Now we are ready to make the files visible in the screen.
Step 2-Create a new action using the Stored Procedure to store the information from Upload Files table
Click on “Upload” link
Navigate to “Data Binding” tab
In previous example, we analyzed the “Upload” action into the repository. Every time that we upload a file, the action stored a unique identity under the “RepositoryId” variable in the “Upload” control.
This identity now will be used to create a new action that will connect the stored procedure sp_CRM_Activities_Insert and display the new files in grid table that we can retrieve from CRM_Activities table that we created initially on Preconditions.
In the parameters, we are going to look for the new identity as &RepositoryId. Click on “Submit” button and verify that the action is created.
Now that we have the dataset and the new stored procedure to retrieve the file’s information from the table that we stored the objects when uploading those files, let’s create the grid table to display the information in the application.
Step 3 - Create the Grid Controls to make visible the Uploaded files in the page
Initially we are going to set set 3 columns, one to display the “Filename”, another one to “Download” the file and finally one to “Delete” the file from the table.
Navigate to “Controls” section and select the “Grid_Column” control.
A popup is displayed on screen, let’s fill the information according to the example. On “General” tab, include the following details:
Object Name: FileName.
Displayed Name: File.
On “Data Binding” tab, include the following details:
Dataset: “DTSGetListActFile” generated in the previous step.
Click on “Submit” to create the first grid column.
Repeat the steps and create another “Grid Column” control to display the download icon related to the file.
On “General” tab, include the following details:
Object Name: RepositoryId.
Displayed Name: Download.
On “Data Binding” tab, include the following details:
Dataset: “DTSGetListActFile” generated in the previous step.
Attachment: RepositoryId,4,Asignet2.dbo.Repository_File_Repository,Filename,size,Data_File
All the parameters in “Attachment” should match with the ones setup in the first “Action” created on the “Upload” control.
Navigate to the “Layout” tab and search for the icon that will trigger the “Download” file action. Include the following details:
Place Holder: E1
Oder: 4
Pictures: flechi-verde.gif
Click on “Submit” button to save the changes.
Repeat the steps and create the last “Grid Column” control to display the Delete icon related to the file.
On “General” tab, include the following details:
Object Name: Delete.
Displayed Name: Delete.
On “Data Binding” tab, include the following details:
Dataset: “DTSGetListActFile” generated in the previous step.
Action: Execute Stored Procedure
Stored Procedure: sp.WR_CRM_Activities_Delete (This object was generated by default when creating the
CRM_Activitiesat the beginning of this lesson)
Navigate to the “Layout” tab and search for the icon that will trigger the “Delete” file action. Include the following details:
Place Holder: E1
Oder: 5
Pictures: delete.gif
Click on “Submit” button
Let’s review how the application works after all these new implementations that we learnt step by step. Click on “Preview” button
To start the process, click on “Select files…” button under the “Upload” control, automatically a popup will open our system directory to indicate which file needs to be uploaded to our repository.
After selecting all the files, click on “Open” button.
Once the files are uploaded into the Wayfast repository, we can observe the “File”, “Download” and “Delete” columns that shows the information from all the listed files that the we uploaded in the application.
How to display an image uploaded into a Wayfast Repository?
select Img='<img src=image.ashk?IdRepositorio='+Cast(@RepositoryId, Varchar)
Recap
In the first part, we revised the preconditions to create a new table in database to stored all the uploaded files in our repository. We understand the important connection between the “Upload” control and the action for this purpose. In the second part, we dove into all the uploaded files in the repository and display them on a grid table that every user can check to download the stored information.