kohera-logo-regular.svg

Using SQL filetables with entity framework

Vector_BG.png

Using SQL filetables with entity framework

Vector_BG.png

Recently I had a problem on a project where we needed to securely store documents. It was the first time for the customer to do such a thing. Telling them this was not so hard, I convinced them to use SQL file tables to store these documents securely in an SQL server database. After validating the SQL server database version used, I let the DBA’s create the file stream storage group and file, changing the database and server properties and created the file table. So far, no issues arose and we were ready for development.

At that point we encountered a serious problem. The application development standard prevented us from using direct SQL statements, and all database access had to be done using .NET Entity Framework (EF). The result was not as expected, it looked as if EF did not import the file table definition correctly. Looking at it in more detail we found that EF didn’t recognise the HIERARCHYID column type. Looking for hours on the msdn blogs, we found several tweaks to force EF to handle the file table meta correctly into the model schema. However, we still couldn’t insert data into the table without writing SQL statement ourselves.

What did I do to solve this? In fact the statement to use only ANSI standard SQL was not that bad, so we left that part as the standard and decided to create an SQL layer between the .NET and the table that managed all the non-standard ANSI code. In other words, preventing the technical specification (non ANSI sql part) from showing to the ‘outside’ world. That solved the whole problem. For EF all that was left was to follow the standard implementation of EF. Finally, everybody was happy and in no time thousands of documents where written.

Please find the complete insight of my solution below.

 

Solution

  1. Create the file table
    
    CREATE TABLE [dbo].[Document] AS FILETABLE ON [PRIMARY] FILESTREAM_ON [db_FS]
    WITH
    (
    FILETABLE_DIRECTORY = N'dboDocumentTable', FILETABLE_COLLATE_FILENAME = Latin1_General_CI_AS
    )
    

     

  2. Create a view on top of it hiding the hierachyid
    
    CREATE VIEW [dbo].[vDocument]
    AS
    SELECT [stream_id]
          ,[file_stream]
          ,[name]
          ,CONVERT(VARCHAR(4000),[path_locator]) AS [path_locator]
          ,CONVERT(VARCHAR(4000),[parent_path_locator]) AS [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]
      FROM [dbo].[Document]
      ;
    

    Depending on the directory structure you use, the varchar(4000) can be to small, but change it when needed to a longer field.

  3. Create another join table to link the document to
    
    CREATE TABLE [dbo].[LinkedDocuments](
    [ID] [INT] IDENTITY(1,1) NOT NULL,
    [OtherEntityID] [INT] NOT NULL,
    [DocumentID] [VARCHAR](4000) NULL,
    [InsertDT] [DATETIME] NOT NULL DEFAULT (GETDATE()),
    [UpdateDT] [DATETIME] NULL,
    [InsertedBy] [VARCHAR](255) NOT NULL DEFAULT (SUSER_SNAME()),
    [UpdatedBy] [VARCHAR](255) NULL,
    PRIMARY KEY CLUSTERED
    (
    [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]
  4. Now create the 3 stored procedure for adding, update and removing documents on the file table. First addDocument stored procedure. This needs some parameters:
      • relativePath: path starting from the tables root path where you want to store the new file
      • Name:name of the file with extension
      • Stream:the real data stream of the document to store
      • Path_locator: this is optional, when you know it, you can pass it here. This must be a variable representation of the path_locator (hierachryid). For the outside world remember? Hint:convert(varchar(4000), path_locator)

    The procedure will create the complete directory structure for you when it’s not present yet. Then adding the document stream to the file location, and returns the character representation of the path_locator. (this I the unique identifier of the stream)

    Ex. /45134823255421.163030830980382.1528000315/

    what is the same as hierarchyid
    0xFCA4278A369913CFE5113258908A3EF96C4D3566E0

    You write this value to the LinkedDocuments table in the documentID field.

    
    CREATE PROC [dbo].[AddDocument]
    (@relativePath varchar(max)=null
    ,@name varchar(512)=null
    ,@stream VARBINARY(MAX)=NULL
    ,@path_locator VARCHAR(4000)=null OUTPUT
    )
    AS 
    BEGIN
    SET NOCOUNT ON 
    
    DECLARE @path_Locator_for_file HIERARCHYID; SET @path_Locator_for_file=NULL;
    
    BEGIN TRY
    BEGIN TRAN adddocument
           DECLARE @directories TABLE(ID int IDENTITY(1,1), Name varchar(MAX),Parent varchar(MAX),Path_Locator HIERARCHYID, seq int);
                  
           -- test input
           IF COALESCE(@name,'') ='' 
                  THROW 50001, 'no input name given', 1; 
           
           -- test/create directory
           IF @relativePath IS NOT NULL
           BEGIN         
    
                  DECLARE @rows INT;
                  ;WITH directories(Name, Pname, seq) AS 
                  (SELECT @relativePath AS Name
                        , CASE WHEN LEFT(@relativePath,1)='\' THEN SUBSTRING(@relativePath,2,LEN(@relativePath)-1) ELSE @relativePath END AS PName
                           , 0 AS seq 
             UNION ALL 
                   SELECT CASE WHEN CHARINDEX('\',REVERSE(PName)) > 0 THEN RIGHT(PName,CHARINDEX('\',REVERSE(PName))-1) ELSE PName END AS Name
                        , CASE WHEN CHARINDEX('\',REVERSE(PName)) > 0 THEN LEFT(PName,LEN(PName)-CHARINDEX('\',REVERSE(PName))) ELSE '' END AS PName
                           , seq+1 AS seq 
             FROM directories recurs
                  WHERE PName>''
                  )
                  ,dirs AS
                  (
                  SELECT Name, PName, LAG(Name) OVER(ORDER BY seq DESC) AS Parent, ROW_NUMBER() OVER(ORDER BY seq desc) AS Seq
                  FROM directories 
                  WHERE seq > 0 AND name > ''
                  )
                  INSERT INTO @directories(Name, Parent, Path_Locator, seq)
                  SELECT Name
                        ,Parent
                           ,null
                        ,seq
                  FROM dirs
                  ;
                  SET @rows=@@ROWCOUNT;
                  
                  DECLARE @loop INT; SET @loop=1;
                  DECLARE @lname VARCHAR(MAX);
                  DECLARE @lparent VARCHAR(MAX);
                  DECLARE @lPath_Locator HIERARCHYID;
                  DECLARE @lParent_Path_Locator HIERARCHYID;
                  DECLARE @lnew_Path_Locator HIERARCHYID;
    
                  WHILE @loop <= @rows BEGIN SELECT @lPath_Locator=Path_Locator, @lname=Name, @lparent=Parent, @lParent_Path_Locator=null FROM @directories WHERE ID=@loop; IF (@lPath_Locator) IS NULL BEGIN IF @lparent IS NOT NULL BEGIN SET @lParent_Path_Locator = (SELECT path_locator FROM @directories WHERE id=@loop-1); SET @lPath_Locator=(SELECT path_locator FROM dbo.Document WHERE name=@lName AND parent_path_locator=@lParent_Path_Locator); IF @lPath_Locator IS NULL BEGIN SELECT @lPath_Locator = @lParent_Path_Locator.ToString() + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 1, 6))) + '.' + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 7, 6))) + '.' + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 13, 4))) + '/'; INSERT INTO dbo.Document(name, is_directory, path_locator) VALUES( @lName, 1, @lPath_Locator ); End END ELSE BEGIN SET @lPath_Locator=(SELECT path_locator FROM dbo.Document WHERE name=@lName); IF @lPath_Locator IS NULL BEGIN INSERT INTO dbo.Document(name, is_directory) VALUES( @lName, 1 ); SET @lPath_Locator = (SELECT path_locator FROM dbo.Document WHERE name=@lName); End END UPDATE @directories SET Path_Locator=@lPath_Locator WHERE ID=@loop; END SET @loop=@loop+1; end SET @path_Locator_for_file=@lPath_Locator; SELECT @path_Locator_for_file = @lPath_Locator.ToString() + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 1, 6))) + '.' + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 7, 6))) + '.' + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 13, 4))) + '/'; -- create file in subdir INSERT INTO dbo.Document (name, file_stream, path_locator) SELECT @name, @stream, @path_Locator_for_file; END ELSE BEGIN -- create file IN ROOT INSERT INTO dbo.Document (name, file_stream) SELECT @name, @stream; SELECT @path_Locator_for_file=path_locator FROM dbo.document WHERE name=@name; END SET @path_locator=CONVERT(VARCHAR(max),@path_Locator_for_file); COMMIT TRAN adddocument; END TRY BEGIN CATCH IF @@TRANCOUNT>0
                  ROLLBACK TRAN adddocument;
        THROW;
    
    END CATCH
    
    END
    
  5. Create the UpdDocument procedure:
    
    CREATE PROC [dbo].[UpdDocument]
    (@relativePath varchar(max)=null
    ,@name varchar(4000)=null
    ,@stream VARBINARY(MAX)=NULL
    ,@path_locator VARCHAR(512)=null
    )
    AS 
    BEGIN
    DECLARE @path_locator_hid HIERARCHYID;
    
    BEGIN TRY
    BEGIN TRAN upddocument
                  
           -- test input
           IF @path_locator IS null AND (COALESCE(@relativePath,'')='' OR COALESCE(@name,'')='')
                  THROW 50001, 'no input given', 1; 
       
           IF coalesce(@relativePath,'') > ''
           BEGIN
            SET @relativePath=LTRIM(RTRIM(@relativePath));
                  IF LEFT(@relativePath,1)='\'
                         SET @relativePath=SUBSTRING(@relativePath, 2, LEN(@relativePath)-1);
                  IF RIGHT(@relativePath,1)='\'
                         SET @relativePath=LEFT(@relativePath, LEN(@relativePath)-1);
        END 
                  
           -- find pathlocator for doc
           IF @path_Locator IS NULL
        begin
                  SELECT @path_locator_hid=getPathLocator(FileTableRootPath('dbo.document')+'\'+@relativePath+'\'+@name);
        END  
           ELSE 
           BEGIN
                  SET @path_locator_hid=CONVERT(HIERARCHYID,@path_locator);
        end
           
           -- update doc
           UPDATE dbo.Document
              SET file_stream=@stream
        WHERE path_locator=@path_locator_hid;
    
           IF @@ROWCOUNT=0 
                  THROW 50002, 'document not found', 1;
    
           COMMIT TRAN updocument;
    END TRY
    BEGIN CATCH
    
           IF @@TRANCOUNT>0
                  ROLLBACK TRAN upddocument;
        THROW;
    
    END CATCH
    
    END
    

    Input parameters are the same as in the addDocument procedure. In this case the document must exists otherwise it returns an error.
    Only the data stream is replaced the meta data left the same.

  6. The last procedure is the deletion of a document in delDocument
    Do not forget to remove our link in the linkedDocument table when you remove a document. (This must be done in the application.)

    
    CREATE PROC [dbo].[DelDocument]
    (@relativePath varchar(MAX)=NULL
    ,@name varchar(512)=NULL
    ,@path_locator VARCHAR(4000)=NULL 
    )
    AS 
    BEGIN
    
    SET NOCOUNT ON
    
    DECLARE @path_locator_hid HIERARCHYID; 
    
    BEGIN TRY
    BEGIN TRAN deldocument
    
           -- test input
           IF @path_locator IS null AND (COALESCE(@relativePath,'')='' OR COALESCE(@name,'')='')
                  THROW 50001, 'no input given', 1; 
       
           IF coalesce(@relativePath,'') > ''
           BEGIN
            SET @relativePath=LTRIM(RTRIM(@relativePath));
                  IF LEFT(@relativePath,1)='\'
                         SET @relativePath=SUBSTRING(@relativePath, 2, LEN(@relativePath)-1);
                  IF RIGHT(@relativePath,1)='\'
                         SET @relativePath=LEFT(@relativePath, LEN(@relativePath)-1);
        END 
                  
           -- delete doc
           IF @path_Locator IS NULL
        begin
                  SELECT @path_locator_hid=getPathLocator(FileTableRootPath('dbo.document')+'\'+@relativePath+'\'+@name);
        END    
           ELSE 
           BEGIN
                  SET @path_locator_hid=CONVERT(HIERARCHYID,@path_locator);
        end
    
           DELETE FROM [dbo].[Document] 
           WHERE path_locator=@path_locator_hid;
    
           IF @@ROWCOUNT=0 
                  THROW 50002, 'document not found', 1;
    
           COMMIT TRAN deldocument;
    END TRY
    BEGIN CATCH
    
           IF @@TRANCOUNT>0
                  ROLLBACK TRAN deldocument;
        THROW;
    
    END CATCH
    
    END
    

    That’s all you have to do. Now you can just calling the procedures AddDocument,DelDocument and UpdDocument to manage your files. If you need to search for a file and/or link the file table, just using the vDocument view (joined or not).

 

Conclusion

this is just a little work around to use file tables combined with EF. It’s not perfect, and can be optimised at some point, but for the moment it works for me.

2319-blog-database-specific-security-featured-image
Database specific security in SQL Server
There are many different ways to secure your database. In this blog post we will give most of them a...
kohera-2312-blog-sql-server-level-security-featured-image
SQL Server security on server level
In this blog, we’re going to look at the options we have for server level security. In SQL Server we...
blog-security_1
Microsoft SQL Server history
Since its inception in 1989, Microsoft SQL Server is a critical component of many organizations' data infrastructure. As data has...
DSC_1388
Aggregaties
Power BI Desktop is een prachtige tool om snel data-analyses te kunnen uitvoeren. Je connecteert op een databron, importeert en...
dba image
DBA is not that scary
Often when talking to people who are looking for a career path in the data world I feel like there...
blog-2303
How do you link an SCD Type 2 table in Power Query?
This article uses a simple example to demonstrate how to link an SCD Type 2 table in Power Query to...