Upload Files from PowerApps to SQL Server

Leveraging Microsoft SQL Server, we have made it easier for app makers to enable their users to take pictures and upload files in their apps. App makers can now use the Microsoft SQL Server connector to enable these features when building or modifying their apps.

To use SQL Server as a file store do the following:

• Create a new column that is an image type in a new table or existing table using Management Studio or any other SQL Server tool (NOTE: The "Image" type column is the only one supported today for SQL)

• NOTE: Be sure you assign a primary key to one of the columns so PowerApps can create and update records against this new table

• Create a new PowerApps App
• Add a SQL Connection to your App (View, Data Sources)
• Select the table that contains the image column

You have two options to send your image to SQL. You can insert a form and let PowerApps do most of the work for you or you can write a patch statement.

PowerApps Form based:

• Add a new form to your canvas (Insert, Forms, Edit)
  • Change the Default mode to New

• Select your Table
  • Select Fields to add to the Form (File Name and Blob Column for Example)

• On the form you will see the media type and a text box
• Add a button to the canvas
  • Go to the OnSelect property of the button and enter in SubmitForm(Form1)
  • This will submit the form to SQL Server
  • To reset the form, add ;ResetForm(Form1) to the OnSelect of the button property

Writing a patch statement:

• Add a control to capture a file such as the Add Picture Control (Insert, Media, Add Picture)
  • A few other options include:
    • Pen Input
    • Camera
    • Add Picture
• Add a Text Input Control which will allow you to enter in the name of the file
• Add a button to the canvas, this will allow you to take the file / input the user has entered and save it into SQL Server.
• Add the following to the OnSelect property of the button

Patch('[dbo].[TestBlobUpload]',

     Defaults('[dbo].[TestBlobUpload]'),

     {

        FileName:TextInput1.Text,

       BlobColumn:UploadedImage1.Image

    }

)

• TestBlobUpload – is the name of my table
• Defaults() – this will create a new record in my table
• TextInput1.Text – is a text field I added to save the name of the file and I want to get the Text property from this
• UploadImage1.Image – is the “Add Picture” control that I added to my canvas, I use “ .Image” to get the file the user uploaded

• Last step is to add a Gallery so we can see the files in the table along with the name
  • Go to Insert, then select a Vertical Gallery with images

• Select your table and your information will show up from your SQL Server

As an app maker, this is a great way to quickly allow your users to save pictures, documents, PDFs or other types of files in your applications without much setup.  When your users click on the "Add Picture" control, they will be prompted with a popup box if they are using PowerApps on their computer.  They can change the drop down from "Image Files" to "All Files" or simply enter in "*.*" into the file name to get a list of all documents.  By default it will show only images.