Use FileTable / .NET Core application class model

Question

Hi,

I would like some advise about the schema that I would to implement to my .Net App.
I want to use FileTable with my .NET Core application where I must create a relationship of this FileTable with another simple table (e.g Document.cs)

Schema :
1 - Documents.cs include the normal attributes and including an attribute to reference file in File Table.
The File Table has PK named Path_Locator with type of HIERARCHYID this means I have to create the varchar(max) field in the Document table and will store the
Path_Locator in this column.
2 - Make a migration with migrationBuilder.Sql() to do the link between file table and the class Document.cs
3 - To accelarete the acces to the file (quanties of the > 60k) in Document.cs, I think to use Server-Side Processing with JQuery DataTable

First of all I would like to know if the schema is realistic.

For the point 1 / the class Document.cs is :

  public class Document
        {
           [Key]
            public int Id { get; set; }
            public string FileName { get; set; }
            public string Description { get; set; }
            public DateTime CreatedTimestamp { get; set; }
            public DateTime UpdatedTimestamp { get; set; }
            public string ContentType { get; set; }
            // Field to link varchar(max) with hierarchyId in File Table
            public string Name{ get; set; }
       }

how I can do the relationship between Name (varchar(max)) in Document.cs with File Table (Path_Locator, HierarchyId)? After that, I think I have to convert varchar(max) in HierarchyId.

For the point 2 / I don't know where to start.

Fort the point 3/ normally I will able to do that.

Thanks in advance to your support.

Answer 1

Hi,
you can use the stream_id as a link

I've to add in FileDescription class stream_id as follow :

public string steam_id {get; set;}

or in trigger I will link the column Name in FileDescription with stream_id.

Do you have aan example of Trigger that insert row from File Table to FileDescription class?

thanks in advance

Answer 2

its not clear the point if your additional table because it has no additional information. but you can just use a trigger on the file table to insert rows in the document table. as I stated, you can use the stream_id as a link.

this shows how to get the unc paths:

https://learn.microsoft.com/en-us/sql/relational-databases/blob/work-with-directories-and-paths-in-filetables?view=sql-server-ver16

Answer 3

the stream_id is a guid (unique_identifier). here is the file table schema:

https://learn.microsoft.com/en-us/sql/relational-databases/blob/filetable-schema?view=sql-server-ver15

note. some of the rows are directories, and some are files. the file table is tree, built using the hierarchy id support:

https://learn.microsoft.com/en-us/sql/relational-databases/tables/tutorial-using-the-hierarchyid-data-type?view=sql-server-ver15

to insert a row in the file table, you need to know which directory row it belongs to.

https://learn.microsoft.com/en-us/sql/relational-databases/blob/work-with-directories-and-paths-in-filetables?view=sql-server-ver15

Answer 4

The stream_id (a guid) would be a more compact key to file table row. The hierarchyid can be converted to a string or byte array (varmax) but it can be large (directory path plus filename)

Answer 5

Hi,

I've implemented process Server-Side Processing with JQuery DataTable. It's really impressive the difference in performance between client side and server side processing.

1- I've synchronize the File Table and Simple Class FileDescription.cs (thanks to @AgaveJoe ) directly in SSMS with script below.

 INSERT INTO [dbo].[FileDescriptions] ([Name], [Description], [CreatedTimestamp], [UpdatedTimestamp], [ContentType])  
 SELECT w.name,  
   w.[file_type],  
   GETDATE(),  
   GETDATE(),  
  CASE   
  WHEN w.[file_type] IN ('jpg', 'png', 'gif', 'tif') THEN 'image/' +  w.[file_type]  
  WHEN w.[file_type] IN ('pdf') THEN 'applicaiton/' + w.[file_type]  
  ELSE 'application/octet-stream'  
  END  
 FROM   [dbo].[wSerie] as w  
  LEFT JOIN [dbo].[FileDescriptions] as f ON w.name = f.Name  
 WHERE f.Name IS NULL  
  AND [is_archive] = 1   

2- I've implemented FileController

[HttpPost]  
        public IActionResult GetCustomers()  
        {  
            try  
            {  
                var draw = Request.Form["draw"].FirstOrDefault();  
                var start = Request.Form["start"].FirstOrDefault();  
                var length = Request.Form["length"].FirstOrDefault();  
                var sortColumn = Request.Form["columns[" + Request.Form["order[0][column]"].FirstOrDefault() + "][name]"].FirstOrDefault();  
                var sortColumnDirection = Request.Form["order[0][dir]"].FirstOrDefault();  
                var searchValue = Request.Form["search[value]"].FirstOrDefault();  
                int pageSize = length != null ? Convert.ToInt32(length) : 0;  
                int skip = start != null ? Convert.ToInt32(start) : 0;  
                int recordsTotal = 0;  
                var customerData = (from tempcustomer in context.FileDescriptions select tempcustomer);  
                if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDirection)))  
                {  
                    customerData = customerData.OrderBy(sortColumn + " " + sortColumnDirection);  
                }  
                if (!string.IsNullOrEmpty(searchValue))  
                {  
                    customerData = customerData.Where(m => m.Name.Contains(searchValue)  
                                                || m.Description.Contains(searchValue)  
                                                || m.ContentType.Contains(searchValue));  
                                                
                }  
                recordsTotal = customerData.Count();  
                var data = customerData.Skip(skip).Take(pageSize).ToList();  
                var jsonData = new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data };  
                return Ok(jsonData);  
            }  
            catch (Exception ex)  
            {  
                throw;  
            }  
       }  

So Actually, I've no link in FileDescription class with FileTable to open the File directly in column Name?

Questions :

• I've tried to implemented HierarchyId in FileDescription class but I don't know How I can Synchronise with this kind of the type field?
• How can I make sure that the File Table and FileDescription class are automatically synchronized? Because files can later be added to the File Table

Thanks in advance to your support