how display directories and files to open the files in server side in datatable?

Question

Hi,
I've an application aspNet core 5 razor page to display several datatable associated to the view in sql.
the view is :

The data is located in File Table where is the files and directories.

Right now my application returns all files of the directories and subdirectories.

To display the files I used relative path. How I can modify the method of the relative path to display the directories and subdirectories to have acces to the files? also in my datatable I would like to add a column to identify the directories with logo directories et files with logo.

Actually I used the webapi action

 [HttpGet]  
        public async Task<IActionResult> GetFileByFilePath(string relativePath)  
        {  
             string directory = await _filerepository.GetFileTableDirectory();  
            string path = $"{directory}\\{relativePath}";  
  
           if (System.IO.File.Exists(path))  
          {  
               return File(System.IO.File.OpenRead(path), "application/octet-stream", Path.GetFileName(path));  
          }  
             return NotFound();  
        }  

Service method

 public async Task<string> GetFileTableDirectory()  
        {  
            FileTableRoot results = await _context.Set<FileTableRoot>()  
                .FromSqlRaw("SELECT FileTableRootPath() as Name").FirstOrDefaultAsync();  
            return $"{results?.Name}";  
        }  

I tried to modified webapi action and associate services

 [HttpGet("{filename}")]  
 public async Task<IActionResult> GetAsync(string filename)  
 {  
     string directory = await _fileTableService.GetFileTableDirectory();  
     string path = $"{directory}\\{filename}";  
      
     if(System.IO.File.Exists(path))  
     {  
         return File(System.IO.File.OpenRead(path), "application/octet-stream");  
     }  
     return NotFound();  
 }  

This action seems not adapted to my application.

Have you any suggestions?

Answer 1

Is it necessary to go through the search value?

Of course not. The Search input the easiest approach because the Search input is designed to filter records. Plus the server side processing is setup.

You selected the jQuery DataTable as the presentation design. In my opinion, the jQuery DataTable is a poor HTML design for displaying a directory structure. An unordered list is a much better representation and the approach I recommended around 5 months ago.

With that being said, it is up to you to come up with a design

As you suggested, the key to the code is to have created the two columns in the view; I think we can do something with that.

I have no idea what you are thinking... IsDirectory is currently being used in the jQuery DataTable to render HTML for a file or a directory.

The SQL View returns the relative path. It seems to me the relative path is the key. Write a service method that returns a distinct list of relative paths. Use this list in your HTML design. For example, a standard HTML select (dropdown). The user selects an option from the select which sets a column filter.

The jQuery DataTable has the ability to add column filters. Place the select in the path column header. See the openly published jQuery DataTable documentation.

https://datatables.net/extensions/fixedheader/examples/options/columnFiltering.html

Answer 2

How I can modify the method of the relative path to display the directories and subdirectories to have acces to the files?

If I understand correctly the new use case is to display the directory structure? The view I wrote for you only returns file path. Simply update the SQL View to include the folders. The GetAsync or GetFileByFilePath methods return a file have nothing to do with your new use case.

ALTER VIEW [dbo].[FilePath]  
AS  
SELECT [stream_id] AS Id  
    ,[file_stream].GetFileNamespacePath() As [Path]  
    ,[name] As [Name]  
    ,ISNULL([cached_file_size],0) AS [Size]  
 ,[creation_time] as [Created]  
FROM [dbo].[DocumentStore]  

also in my datatable I would like to add a column to identify the directories with logo directories et files with logo.

The use case is to show folder and file images? The file table has several columns that identifies the type. For example is_archive = 1 is a file and is_directory = 1 is a folder. Update the SQL View to include is_archive and is_directory. Then it is a simply "if" in the jQuery Datatable to render a file or folder image using an HTML img element.

This action seems not adapted to my application.

I disagree, the update is very very simple. The problem is, like you other post, you've made no effort to understand the code or technology. It's clear that after several months you still have no idea how the code works.

Answer 3

The GetAsync or GetFileByFilePath methods return a file have nothing to do with your new use case.

I agree with you but how i can add the directory and sub directory in sql view to have access to the file and still be able to open them?

After putting in the databtable the directory and subdirectory structure I would like to add an image to identify a directory with an image and the pictures for the files

Answer 4

 ALTER VIEW [dbo].[FilePath]  
  AS  
 SELECT [stream_id] AS Id  
     ,[file_stream].GetFileNamespacePath() As [Path]  
     ,[name] As [Name]  
  ,[file_type] As [Type]  
  ,ISNULL([cached_file_size],0) AS [Size]  
     ,CAST([creation_time] AS DATE) [Created]  
 FROM [dbo].[wPreliminaire]  
 WHERE [is_directory] = 1   
 GO  

This will give the path to all directories.
So, my first request is the directory view AND the files.
I need to add is_directory and is_archive=1 with WHERE [is_archive]=1 AND [is_directory] = 1.
When I add both as above, in my view I only have two entries, whereas I should see all directories and files.
If I only do with [is_directory] = 1 I have one all directories in my table. So I think I need to write some js code but clearly I don't know where to start.

Answer 5

Right !
I modified the sql view as you told me.
I see in the Path column : the full path with the name of the file et the path with only the directory

Next step will be to modify the jquery, Is that it?

 "columns": [  
            { "data": "path", "name": "path", "autoWidth": true },  
            {  
                "data": "path", "name": "path",'render': function (data, type, row, meta) {  
                    return '<a href="/api/wPreliminaire/?relativePath=' + row.path + '" >' + row.name + '</a>';  
                }  
           },             
           { "data": "type", "name": "type", "autoWidth": true },  
           {  
                "data": "size", "name": "size", "autoWidth": true, 'render': function (data, type, row, meta) {  
                    var size = BigInt(data);  
                    if (size < 1024) { return size + ' B' }  
                    return size / BigInt(1024) + ' KB';  
                }   
            },       
            { "data": "created", "name": "created", "autoWidth": true}              
        ]