Xile File Upload and Database Synchronization

Xile is a powerful single/multiple file upload plugin for Xmod Pro. In this article we explore best practices for normalizing your data. How do you store the multiple images in the database? We'll use a product that has a primary image and additional images as an example.

By: Patrick Ryan On: 09/03/2014

Link to this Article
https://dnndev.com/Learn/Guide/Article/xile-file-upload-and-database-synchronization

Right out of the box Xile comes with an example add/edit form.

The ADD form looks like this, with unimportant factors stripped out:

<register tagprefix="rmg" namespace="reflect.xile" assembly="reflect.xile" />
<AddForm AddRoles="All Users">

  <SubmitCommand CommandText="INSERT INTO [RMG_Temp_FileUpload] ([Name], [PrimaryImage], [AdditionalImages]) VALUES(@Name, @PrimaryImage, @AdditionalImages) " />
  
  <div class="RMG_UploadDemo">
    <div class="xmp-form-row">
      <Label For="Name">Product Name</Label>
      <TextBox Id="Name" Nullable="False" MaxLength="50" DataField="Name" DataType="string"></TextBox>
      <Validate Type="required" Target="Name" Text="Required" Message="Required" />
    </div>
    
    <div class="xmp-form-row">
      <Label For="PrimaryImage">Primary Image</Label>
      <rmg:Xile 
        Id="PrimaryImage" 
        Nullable="False" 
        DataField="PrimaryImage" 
        Dropzone="False"
        AcceptFileTypes="jpg,jpeg,png"
        MaxNumberOfFiles="1"
        AutoUpload="True"
        AutoCreateFolder="True"
        FileUploadPath='[[Join("~/Portals/{0}/RMG_FileUploadTest/{1}", [[Portal:ID]], [[User:ID]])]]'
        ResizeVersions="width=800;height=600;format=jpg;mode=max, sm_:width=400;height=400;format=jpg;mode=max, thm_:width=80;height=80;format=jpg;mode=max"
        UniqueFileName="True"
        UploadMode="Single"
        AddFilesButtonText="Add Image"
        WrapperClass="rmg-singleupload"
        ShowTopCancelButton="False"
        ShowTopCheckBox="False"
        ShowTopProgressBar="False"
        ShowTopDeleteButton="False">
      </rmg:Xile>
    </div>
    
    <div class="xmp-form-row">
      <Label For="AdditionalImages">Additional Images</Label>
      <rmg:Xile 
         Id="AdditionalImages" 
         Nullable="True" 
         DataField="AdditionalImages" 
         Dropzone="True"
         AcceptFileTypes="jpg,jpeg,png"
         MaxNumberOfFiles="20"
         AutoUpload="True"
         AutoCreateFolder="True"
         ResizeVersions="width=800;height=600;format=jpg;mode=max, sm_:width=400;height=400;format=jpg;mode=max, thm_:width=80;height=80;format=jpg;mode=max"
         FileUploadPath='[[Join("~/Portals/{0}/RMG_FileUploadTest/{1}/Additional/", [[Portal:ID]], [[User:ID]])]]'
         UniqueFileName="True"
         UploadMode="Multiple"
         AddFilesButtonText="Add Files...">
       </rmg:Xile>
    </div>
    
    <div class="xmp-form-row">
      <AddLink Class="dnnPrimaryAction btnPrimary primaryButton" Text="Create Product"></AddLink>
    </div>
  </div>
</AddForm>

The EDIT form looks like this:

<EditForm>

  <SelectCommand CommandText="SELECT ProductID, Name, PrimaryImage, AdditionalImages FROM RMG_Temp_FileUpload WHERE ProductID = @ProductID" />
  <SubmitCommand CommandText="UPDATE RMG_Temp_FileUpload SET Name=@Name, PrimaryImage=@PrimaryImage, AdditionalImages=@AdditionalImages WHERE ProductID = @ProductID" />
  
  <div class="RMG_UploadDemo">

    <div class="xmp-form-row">
      <Label For="Name">Product Name</Label>
      <TextBox Id="Name" Nullable="False" MaxLength="50" DataField="Name" DataType="string"></TextBox>
      <Validate Type="required" Target="Name" Text="Required" Message="Required" />
    </div>
    
    <div class="xmp-form-row">
      <Label For="PrimaryImage">Primary Image</Label>
      <rmg:Xile 
        Id="PrimaryImage" 
        Nullable="False" 
        DataField="PrimaryImage" 
        Dropzone="False"
        AcceptFileTypes="jpg,jpeg,png"
        MaxNumberOfFiles="1"
        AutoUpload="True"
        AutoCreateFolder="True"
        FileUploadPath='[[Join("~/Portals/{0}/RMG_FileUploadTest/{1}", [[Portal:ID]], [[User:ID]])]]'
        ResizeVersions="width=800;height=600;format=jpg;mode=max, sm_:width=400;height=400;format=jpg;mode=max, thm_:width=80;height=80;format=jpg;mode=max"
        UniqueFileName="True"
        UploadMode="Single"
        AddFilesButtonText="Add Image"
        WrapperClass="rmg-singleupload"
        ShowTopCancelButton="False"
        ShowTopCheckBox="False"
        ShowTopProgressBar="False"
        ShowTopDeleteButton="False">
      </rmg:Xile>
    </div>
    
    <div class="xmp-form-row">
      <Label For="AdditionalImages">Additional Images</Label>
      <rmg:Xile 
         Id="AdditionalImages" 
         Nullable="True" 
         DataField="AdditionalImages" 
         Dropzone="True"
         AcceptFileTypes="jpg,jpeg,png"
         MaxNumberOfFiles="20"
         AutoUpload="True"
         AutoCreateFolder="True"
         ResizeVersions="width=800;height=600;format=jpg;mode=max, sm_:width=400;height=400;format=jpg;mode=max, thm_:width=80;height=80;format=jpg;mode=max"
         FileUploadPath='[[Join("~/Portals/{0}/RMG_FileUploadTest/{1}/Additional/", [[Portal:ID]], [[User:ID]])]]'
         UniqueFileName="True"
         UploadMode="Multiple"
         AddFilesButtonText="Add Files...">
       </rmg:Xile>
  	</div>
    
    <div class="xmp-form-row">
      <UpdateButton CssClass="rmg-btn rmg-startupload-btn" Text="Update Product"></UpdateButton>
    </div>
  </div>

  <TextBox Id="ProductID" DataField="ProductID" DataType="Int32" Visible="False"></TextBox>
	
</EditForm>

Lets isolate the SQL commands on both the add and the edit form. When a record is saved, the following insert statement is triggered:

<SubmitCommand CommandText="INSERT INTO [RMG_Temp_FileUpload] ([Name], [PrimaryImage], [AdditionalImages]) VALUES(@Name, @PrimaryImage, @AdditionalImages) " />

Then, when editing a record, the following query is used to retrieve the data:

<SelectCommand CommandText="SELECT ProductID, Name, PrimaryImage, AdditionalImages FROM RMG_Temp_FileUpload WHERE ProductID = @ProductID" />

And finally when a record is updated, the update command is triggered:

<SubmitCommand CommandText="UPDATE RMG_Temp_FileUpload SET Name=@Name, PrimaryImage=@PrimaryImage, AdditionalImages=@AdditionalImages WHERE ProductID = @ProductID" />

As you can see from the example add/edit form, the demo is based on managing products. A product has a primary image as well as additional images. There are two instances of Xile in the form to handle this. One is set as a single uploader whereas the other is set as a multiple file uploader. But how is this data stored? Prior to adding/update a record, Xile stores the filenames in a PIPE delimited string that looks basically like this:

filename1.jpg|filename2.jpg|filename3.jpg|filename4.jpg

If you look at the submit command, it passes this pipe delimited string in a single parameter called "@AdditionalImages". The select command in the edit form retrieves this pipe delimited string, and the update command sends this pipe delimited string back to the database when updating a record. At this point you have several options... You can store the pipe delimeted string in a single column within your table, or you can loop through each filename and create a single record for each file in a separate table. The second option mentioned is highly recommended and follows database normalization rules. One of the basic rules is that if data in a particular column should not hold grouped values... Basically storing our pipe delimited string in a column called Additional_Images is a bad idea and makes managing your data a potential nightmare. Also, it's better practice to store our images for a product in a completely separate table based on the primary key of the product. There's nothing else in our main product table that needs the images so they should be pushed to their own table. To fully understand database normalization, take a look at this link.

To give you a visual understanding of this, our current table looks like this:

I'll go a head and rename it from RMG_Temp_FileUpload to RMG_Product. We need to remove both the PrimaryImage and AdditionalImages column. They're not needed.

Next, we'll create another table called RMG_Product_Image that will store the file names in individual rows. Our two tables now look like this:

Keep in mind we've just broken the demo template that came with Xile. For demonstration purposes, lets strip a bunch of stuff out and make it look like this:

<xmod:Template UsePaging="True" Ajax="False" AddRoles="" EditRoles="" DeleteRoles="" DetailRoles="">
  <ListDataSource CommandText="SELECT [ProductID], [Name] FROM RMG_Product"/>
  <DeleteCommand CommandText="DELETE FROM RMG_Product WHERE [ProductID] = @ProductID">
    <Parameter Name="ProductID" />
  </DeleteCommand>
  <HeaderTemplate>
    <table>
      <thead>
        <tr>
          <th>Product ID</th>
          <th>Name</th>
          <th>&nbsp;</th>
        </tr>
      </thead>
      <tbody>
  </HeaderTemplate>
  <ItemTemplate>
        <tr>
          <td>[[ProductID]]</td>
          <td>[[Name]]</td>
          <td>
            <xmod:EditLink Text="Edit">
              <Parameter Name="ProductID" Value='[[ProductID]]' />
            </xmod:EditLink>
            <xmod:DeleteLink Text="Delete">
              <Parameter Name="ProductID" Value='[[ProductID]]' />
            </xmod:DeleteLink>
          </td>
        </tr>
  </ItemTemplate>
  <FooterTemplate>
      </tbody>
    </table>
  </FooterTemplate>
</xmod:Template>
<div style="margin-bottom: 20px;">
  <xmod:AddLink CssClass="dnnPrimaryAction btnPrimary primaryButton" Text="Create New Product" />
</div>

I've also setup a foreign key relationship for extra data integrity which basically says... We cannot have an image in the image table that doesn't have an associated Product in the product table. So we've linked the ProductID in the RMG_Product_Image table with the ProductID in the RMG_Product table. We create this foreign key by right clicking over the blank area on our table designer in SQL Server Management Studio, selecting Relationships, and then adding a new relationship. On the right hand side, expand the "Tables and Columns Specifications" section. Then click on the ellipsis.

We select our RMG_Product table on the left, and choose ProductID. We then select ProductID for our Foreign key table on the right.

Just one more step to make life easier down the road. What happens when a record is deleted from the RMG_Product table? This foreign key relationship is going to throw an error because a ProductID will exist in the RMG_Product_Image table that does not exist in the RMG_Product table, hence our purpose of data integrity. Just a quick note... You always want to protect your data integrity from the database / server first, and then your app. Do not fall into the trap thinking that you can build your app perfectly and handle the rules there... It usually ends in regret. An example would be making a field required in your application, but not making it required in the database. What if something goes wrong in your application and you now have corrupt data in your database? Again, protect your data from the server first, and then make your application obey the same rules.

There are a couple ways to handle this. You can write a stored procedure so that when a product is deleted it first deletes any images in the RMG_Product_Image table that are associated with that ProductID first, and then delete the Product from the RMG_Product table. Another option is to use what's called Cascade Delete which is cleaner and easier, and keeps your code easier to manage.

Before saving your foreign key relationship, scroll down and look for INSERT And UPDATE Specifications. Expand this section. For the Delete Rule, set it to cascade. This basically takes care of the previous example of deletion for you.

Go ahead and close when you're done. Your tables are now ready.

Next, lets handle the insertion/creation of a record first. In your SSMS (SQL Server Management Studio) Object Explorer, look for Programmability > Stored Procedures. Right click over Stored Procedures and select "New Stored Procedure". We'll call our stored procedure RMG_Product_AddProduct and the script to create it should look like this:

CREATE PROCEDURE [dbo].[RMG_Product_AddProduct]
	 @Name NVARCHAR(50)
	,@PrimaryImage NVARCHAR(150)
	,@AdditionalImages NVARCHAR(MAX)
	
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @NewProductID INT
	
	INSERT INTO [RMG_Product] ([Name]) VALUES (@Name)
	SET @NewProductID = SCOPE_IDENTITY()

	IF @PrimaryImage IS NOT NULL
		BEGIN
		  INSERT INTO RMG_Product_Image ( [ProductID], [Filename], [IsPrimary] ) VALUES ( @NewProductID, @PrimaryImage, 1 )		
		END
		
	-- Additional Images
	WHILE LEN(@AdditionalImages) > 0
		BEGIN
		    INSERT INTO RMG_Product_Image ( 
			 [ProductID]
			,[Filename] 
		    ) VALUES ( 
			 @NewProductID
			,LEFT(@AdditionalImages, CHARINDEX('|', @AdditionalImages+'|') -1) 
		    )
		    SET @AdditionalImages = STUFF(@AdditionalImages, 1, CHARINDEX('|', @AdditionalImages+'|'), '')
		END
END

You'll see that the first thing we do is insert the product name into the product table. Directly after this insert you can see that we've assigned the newly created primary key of the record to a parameter called @NewProductID. We then use a conditional IF statement to determine if the product had a primary image or not. If it does, we insert it into the image table and assigned the bit/boolean value of TRUE for IsPrimary.

Next, we use a WHILE loop that basically says... if the @AdditionalImages has a length greater than 0, we're going to do something until it reaches 0. The "SET" toward the bottom of it basically changes the string so that it's one less filename. Here's a logical example:

My pipe starts with:

filename1.jpg|filename2.jpg|filename3.jpg|filename4.jpg

And then as it loops...

filename2.jpg|filename3.jpg|filename4.jpg

filename3.jpg|filename4.jpg

filename4.jpg

Done...

When created the RMG_Product_Image table I set a default value for IsPrimary to False. You can do this in the table designer. If I didn't, I'd have to specify this here to be false. Here's a screenshot.

Now we can change our Add form so that our submit command looks like this:

<SubmitCommand CommandText="RMG_Product_AddProduct" CommandType="StoredProcedure">
    <Parameter Name="Name" DataType="String" />
    <Parameter Name="PrimaryImage" DataType="String" />
    <Parameter Name="AdditionalImages" DataType="String" />
</SubmitCommand>

Go ahead and create a product... You should see a record in the product table, and the associated images now in the image table.

We've run into a slight problem... When editing a record, how in the world is Xile going to know what file names to look for? Xile requires that PIPE delimited string when editing a record... The primary image is easy to deal with, but the additional images will take some additional effort. We need to basically reverse the previous process and turn all those file names into a PIPE delimited string. Lets start by creating a scalar valued function. Below stored procedures you'll see Functions. Expand Functions and right click over the Scalar Valued Functions and create a new one. It should look like this:

CREATE FUNCTION [dbo].[udf_RMG_Product_GetImages]
(
    @ProductID INT
)
RETURNS nvarchar(MAX)
AS
BEGIN

DECLARE @Images nvarchar(MAX)

SELECT @Images = COALESCE(@Images + '|', '') + [Filename]
			  FROM RMG_Product_Image 
			  WHERE ProductID = @ProductID 
			  AND IsPrimary = 0 
	
RETURN @Images

END

This function might look a little crazy at first glance, and we don't need to dive into the specifics. Basically it turns those filenames into our pipe delimeted string.

Next we need to create a stored procedure that we'll use when editing a record. We'll call it RMG_Product_GetProduct. So scroll back up and right click over stored procedures to create a new one. It should look like this:

CREATE PROCEDURE [dbo].[RMG_Product_EditProduct]
	 @ProductID INT
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT	
		 [Name]
		,(SELECT [Filename] FROM RMG_Product_Image WHERE ProductID = @ProductID AND IsPrimary = 1) AS PrimaryImage
		,(SELECT dbo.udf_RMG_Product_GetImages(@ProductID)) AS AdditionalImages
		,[ProductID]
		
	FROM RMG_Product WHERE ProductID = @ProductID
		
END

And finally, we can change our Select Command in our edit form to look like this:

<SelectCommand CommandText="EXEC RMG_Product_EditProduct @ProductID" />

The last step is to handle updating a record. Lets create another stored procedure and call it RMG_Product_UpdateProduct. It should look like this:

CREATE PROCEDURE [dbo].[RMG_Product_UpdateProduct]
	 @Name NVARCHAR(150)
	,@PrimaryImage NVARCHAR(150)
	,@AdditionalImages NVARCHAR(MAX)
	,@ProductID INT
	
AS
BEGIN

	SET NOCOUNT ON
	
	UPDATE [RMG_Product] SET [Name]=@Name	
	WHERE ProductID = @ProductID			
	
	DELETE FROM RMG_Product_Image WHERE ProductID = @ProductID AND IsPrimary = 1
	IF @PrimaryImage IS NOT NULL
		BEGIN
			INSERT INTO RMG_Product_Image ( [ProductID], [Filename], [IsPrimary] ) VALUES ( @ProductID, @PrimaryImage, 1 )		
		END
		
		-- Additional Images
	DELETE FROM RMG_Product_Image WHERE ProductID = @ProductID AND IsPrimary = 0
	WHILE LEN(@AdditionalImages) > 0
		BEGIN
			INSERT INTO RMG_Product_Image ( 
				 [ProductID]
				,[Filename] 
			) VALUES ( 
				 @ProductID
				,LEFT(@AdditionalImages, CHARINDEX('|', @AdditionalImages+'|') -1) 
			)
			
			SET @AdditionalImages = STUFF(@AdditionalImages, 1, CHARINDEX('|', @AdditionalImages+'|'), '')
		END
			
END

What this procedure basically does is updates the RMG_Product table first, and then deletes the primary image if it exists from the RMG_Product_Image table, and then inserts it again. It does the same for the additional images.

We can now change our Submit Command in our Edit form to look like this:

<SubmitCommand CommandText="RMG_Product_UpdateProduct" CommandType="StoredProcedure">
    <Parameter Name="Name" DataType="String" />
    <Parameter Name="PrimaryImage" DataType="String" />
    <Parameter Name="AdditionalImages" DataType="String" />
    <Parameter Name="ProductID" DataType="Int32" />
</SubmitCommand>

At this point you're all finished. Your data is looking great and easily queried depending on your needs.

Welcome to the Learning Center

The Guide | Knowledge Base | FAQ

Xile File Upload and Database Synchronization

Xile is a powerful single/multiple file upload plugin for Xmod Pro. In this article we explore best practices for normalizing your data. How do you store the multiple images in the database? We'll use a product that has a primary image and additional images as an example.