File

File control for uploading a single client-side file. See File Upload Area for multiple files.

When used in conjunction with NewEdit, additional control names are also returned. If the file control ID is Xyz then the parameters @XyzFileName, @XyzContentType, and @XyzContentSize are also posted. (ContentSize is only returned for legacy reasons and you can safely ignore its value. If you need to determine the length of your data then use the DATALENGTH function.)

CREATE PROCEDURE xxx_InsertUpdate
  ...
  @Xyz varbinary(max),
  @XyzFileId varchar(300),
  @XyzFileName nvarchar(300),
  @XyzContentType varchar(300),
  @XyzContentSize int
AS
...

To display an existing file, use SoftadminApi.FileControl_CreateExistingFileToken. If the user has not changed the file when saving, an additional parameter @XyzFileId will be passed to the procedure containing the given file id.

It is possible to specify which file extensions the user can select from and the maximum file size allowed by creating an input validation using the File category.

Start value: Binary data generated by SoftadminApi.FileControl_CreateExistingFileToken.
Return value: Selected file from local/network hard drive.
Supported in: NewEdit

Appearance

alt text
The "Choose File" button opens a standard file dialog where the user can select a file to upload.

Examples

Dealing with existing files

When dealing with existing files, there are three cases:

  1. The user chooses to replace the existing file.
    @UserPhoto is NOT NULL and @UserPhotoFileId is NULL.
  2. The user chooses to keep the existing file.
    @UserPhoto is NULL and @UserPhotoFileId is NOT NULL.
  3. The user chooses to delete the existing file.
    Both @UserPhoto and @UserPhotoFileId are NULL.
CREATE OR ALTER PROCEDURE Example.User_EditProfile_InsertUpdate
	@UserId               int,
	@PhoneNumber          varchar(100),
	@HomeAddress          varchar(300),
	@Bio                  varchar(max),
	@UserPhoto            varbinary(max),
	@UserPhotoFileId      varchar(300) = NULL,
	@UserPhotoFileName    nvarchar(300) = NULL,
	@UserPhotoContentType varchar(300) = NULL,
	@UserPhotoContentSize int = NULL
AS
BEGIN
	SET XACT_ABORT ON;
	BEGIN TRANSACTION;

	UPDATE Example.UserExtraInfo SET
		PhoneNumber = @PhoneNumber,
		HomeAddress = @HomeAddress,
		Bio         = @Bio
	WHERE
		UserId = @UserId;

	IF @UserPhotoFileId IS NULL
	BEGIN
		-- Delete existing photo.
		DELETE SoftadminApi.UserPhoto
		WHERE
			UserId = @UserId;
	END;

	IF @UserPhoto IS NOT NULL
	BEGIN
		-- Upload new photo.
		INSERT SoftadminApi.UserPhoto
		(
			UserId,
			UserPhoto,
			UserPhotoContentType,
			UpdateDatetime
		)
		VALUES
		(
			@UserId,
			@UserPhoto,
			@UserPhotoContentType,
			SYSDATETIMEOFFSET()
		);
	END;
	
	COMMIT TRANSACTION;
END;

Best practice

GetEditFields

Use SoftadminApi.FileControl_CreateExistingFileToken in GetEditFields if you want to display an existing file. Otherwise use CONVERT(varbinary(MAX), NULL) in instead of returning file data which will be discarded anyway.