Upload to FileStream table with UserId foreign key mapping

• lewisdow123 90045

  SSC-Addicted

  Points: 404

  More actions

  January 28, 2017 at 4:58 pm

  #339944

  Hi,

  I have a question regarding the flexibility of a filestream table with respect to adding in a custom foreign key.

  I have a standard filestream defined as below...

  
CREATE TABLE [dbo].[Documents] AS FILETABLE ON [PRIMARY] FILESTREAM_ON [FileStreamFG]
WITH
(
FILETABLE_DIRECTORY = N'UserCVFilesDocumentStore', FILETABLE_COLLATE_FILENAME = Latin1_General_CI_AS, 
FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME=[UQ_stream_id]
)


  This produces a table with the following system generated columns:

  stream_id,
  file_stream,
  name,
  path_locator,
  parent_path_locator,
  file_type,
  cached_file_size,
  creation_time,
  last_write_time,
  last_access_time,
  is_directory,
  is_offline,
  is_hidden,
  is_readonly,
  is_archive,
  is_system,
  is_temporary

  I can then place the files in to the windows directory (via Explore FileTable Directory) and they appear in the filestream table as expected... 

  In my scenario the file would be uploaded to the windows directory via a web site, however when doing so I would like to log the User's ID and store it either in an extra column called UserId or a table that would allow me to map the UserId to the Stream_Id, so that I can record which user uploaded the file.  It seems that I can not do so.

  Please could someone be kind enough to let me know how I can add the file to the windows directory and subsequently the filestream table but also store the UserId of who did so??

  Many thanks,

  Loo