ASP.NET CORE server side processing to acces to the files stored in FILE TABLE ?

Question

Hi,
first I want to create an ASP.NET core application linked to the FILE TABLE to open the files directly in my interface
After several attempts I was able to implement in my interface the links of my files to be able to consult them.
In the FILE TABLE I have 60000 files so the access to the file and directory of the FILE TABLE was not relevant.

I took the option to work server side processing to improve the performance.

I've created an class FileTable and the controller FileController

Questions :
_ Can I do a first migration to create a FILE TABLE?
_ If no, Should I create a class with the [NoMapped] attribute?
_ Can I use jquery Datatable with File Table?
_ How to use a controller to put the path of the files from the controller I put below?

I know these are not expert questions but I really need help on this.
thank you in advance

You will find below the class that I will need.

public class FileTable
     {
         public int Id { get; set; }
         public string Name { get; set; }
         public string Description { get; set; }
         public DateTime CreatedTimestamp { get; set; }
         public DateTime UpdatedTimestamp { get; set; }
         public string ContentType { get; set; }
     }


[Route("api/[controller]")]
     [ApiController]
     public class FileController : Controller
     {
         private readonly ApplicationDbContext context;
         public FileController(ApplicationDbContext context)
         {
             this.context = context;
         }

         [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;
             }
         }

Answer 1

If I understand your question you are asking how to add the actual file table as an entity so it can be exposed through the DbContext. First, create a model. My file table is named DocumentStore.

    public class DocumentStore  
    {  
  
        public Guid stream_id { get; set; }  
        public Byte[]? file_stream { get; set; }  
        public string? name { get; set; }  
        [NotMapped]  
        public HierarchyId? path_locator { get; set; }  
        [NotMapped]  
        public HierarchyId? parent_path_locator { get; set; }  
        public string? file_type { get; set; }  
        public Int64 cached_file_size { get; set; }  
        public DateTimeOffset creation_time { get; set; }  
        public DateTimeOffset last_write_time { get; set; }  
        public DateTimeOffset last_access_time { get; set; }  
        public bool is_directory { get; set; }  
        public bool is_offline { get; set; }  
        public bool is_hidden { get; set; }  
        public bool is_readonly { get; set; }  
        public bool is_archive { get; set; }  
        public bool is_system { get; set; }  
        public bool is_temporary { get; set; }  
    }  

You'll need to install a few NuGet packages. Visual Studio will help with this.

Add the entity to the DbContext.

public partial class ArchiveDbContext : DbContext  
{  
    public ArchiveDbContext(DbContextOptions<ArchiveDbContext> options)  
        : base(options)  
    {  
    }  
  
    public DbSet<FileDescription>? FileDescriptions { get; set; }  
    public DbSet<DocumentStore> Documents { get; set; }  
  
    protected override void OnModelCreating(ModelBuilder modelBuilder)  
    {  
  
        modelBuilder.Entity<FileTableRoot>().HasNoKey().ToTable("FileTableRoot", t => t.ExcludeFromMigrations());  
        modelBuilder.Entity<FileDescription>().HasKey(m => m.Id);  
        modelBuilder.Entity<DocumentStore>().HasNoKey().ToTable("DocumentStore", t => t.ExcludeFromMigrations());  
        base.OnModelCreating(modelBuilder);  
    }  
  
}  

Add the a method to the IFileTableService; GetFileTableRecordByFileName(string name)

    public interface IFileTableService  
    {  
        Task<string> GetFileTableDirectory(string fileStreamDirectory = "Archive");  
        Task<DocumentStore?> GetFileTableRecordByFileName(string name);  
    }  
  
    public class FileTableService : IFileTableService  
    {  
        private readonly ArchiveDbContext _context;  
  
        public FileTableService(ArchiveDbContext context)  
        {  
            _context = context;  
        }  
  
        public async Task<string> GetFileTableDirectory(string fileStreamDirectory = "Archive")  
        {  
            FileTableRoot? results = await _context.Set<FileTableRoot>()  
                .FromSqlRaw("SELECT FileTableRootPath() as Name").FirstOrDefaultAsync();  
            return $"{results?.Name}\\{fileStreamDirectory}";  
        }  
  
        public async Task<DocumentStore?> GetFileTableRecordByFileName(string name)  
        {  
            DocumentStore? doc = await _context.Documents.FirstOrDefaultAsync(d => d.name == name);  
            return doc;  
        }  
  
    }  

invoke the GetFileTableRecordByFileName(string name) method.

    [Route("api/[controller]")]  
    [ApiController]  
    public class FileTableController : ControllerBase  
    {  
        private readonly IFileTableService _fileTableService;  
        public FileTableController(IFileTableService fileTableService)  
        {  
            _fileTableService = fileTableService;  
        }  
  
        [HttpGet]  
        public async Task<IActionResult> GetAsync()  
        {  
            string directory = await _fileTableService.GetFileTableDirectory();  
            string[] files = Directory.GetFiles(directory).Select(f => Path.GetFileName(f)).ToArray();  
            return Ok(files);  
        }  
  
        [HttpGet("{filename}")]  
        public async Task<IActionResult> GetAsync(string filename)  
        {  
            string directory = await _fileTableService.GetFileTableDirectory("Archive");  
            string path = $"{directory}\\{filename}";  
  
            if(System.IO.File.Exists(path))  
            {  
                return File(System.IO.File.OpenRead(path), "application/octet-stream", filename);  
            }  
            return NotFound();  
        }  
  
        [HttpGet("Doc/{filename}")]  
        public async Task<IActionResult> GetDocAsync(string filename)  
        {  
            Models.DocumentStore? results = await _fileTableService.GetFileTableRecordByFileName(filename);  
            return Ok(results);  
        }  
    }  

Again, this type of information is cover in the openly published Raw SQL Queries documentation.

However, I would not reference the file table directly. I would use a View, as suggested by Bruce, or a stored procedure.

Answer 2

Thanks. I will try it.
one question my first target was to improve the acces to files.
So if I understand well after it will be possible to use datatable and use DocumentStore to define the datasource in getcustomers method. Is that right?

Answer 3

Sorry you didn't answered to my questions.

My question is
So if I understand well after it will be possible to use datatable and use DocumentStore to define the datasource in getcustomers method. Is that right? Y/N

Answer 4

Hi,
I tried to implement the file table as an entity. I received the error message

Microsoft.Data.SqlClient.SqlException (0x80131904): Nom d'objet 'DocumentStore' non valide.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__169_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.<>c.<.cctor>b__277_0(Object obj)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)

Answer 5

Hi,
I've copied your code with eagerness !

I've only acces to one file. Remember well, I want the all list return in server side proccessing.
This mean it's not necesary to use GetFileTableDirectory and GetFileTableRecordByFileName because I've defined the class model of the File Table (name in my case is wSerie)

So I've defined in ApplicationDbdContext.cs the acces to all data of File Table (wSerie)

public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)  
        { }  
  
        public DbSet<wSerie> wSeries { get; set; }  
        public DbSet<FileTableRoot> FileTableRoots { get; set; }  
         
        protected override void OnModelCreating(ModelBuilder modelBuilder)  
        {  
            modelBuilder.Entity<FileTableRoot>().HasNoKey().ToTable("FileTableRoot", t => t.ExcludeFromMigrations());  
            modelBuilder.Entity<wSerie>().HasNoKey().ToTable("wSerie", t => t.ExcludeFromMigrations());  
            base.OnModelCreating(modelBuilder);  
        }  

Right now, the acces to file table I can just define file table (wSerie) in datasource from Jquery datatable

$(document).ready(function () {  
    $('#customerDatatable').dataTable({  
        "processing": true,  
        "serverSide": true,  
        "filter": true,  
        "ajax": {  
            "url": "/api/wSerie",  
            "type": "POST",  
            "datatype": "json"  
        },  
        "columnDefs": [{  
            "targets": [0],  
            "visible": false,  
            "searchable": false  
        }],  
        "columns": [  
                   { "data": "name", "name": "Name", "autoWidth": true },  
                                    
        ]  
  
         });  
});  
  

I've defined the controller as follow :

[Route("api/[controller]")]  
    [ApiController]  
    public class wSerieController : ControllerBase  
    {  
          private readonly ApplicationDbContext context;  
          public wSerieController(ApplicationDbContext context)  
          {  
              this.context = context;  
          }  
  
            
         [HttpPost]  
            public IActionResult GetSerie()  
            {  
                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.wSeries 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));  
                                                    
                    }  
                    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;  
                }  
            }  
          
    }  

I received the message :
Have you an idea?

I would like to know what I have done in my code is correct.
Now that I have directly acces to wSerie (file table), how I can open the file directly in my UI? Have you an idea?

the code return error 404 :