How I can do raw query in jquery datatable to acces to the files from sqlserver file table?

Question

Hi,
I have an asp.net core 5 application that displays the entire sqlserver file table content.

My question is : How can open the file in colunm Name?

I would like to modify the code below (extract from jquery-datatable-in-aspnet-core ).

I think we should do something like : var path = context.Set<wSerie>().FromSqlRaw("SELECT FileTableRootPath() as Chemin").FirstOrDefaultAsync();
I don't know how to introduce the path to create a link with the file in the file table in the code below.

The controller is :

 [HttpPost]  
            public IActionResult GetSerie(string filestream)  
            {  
                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)  
                                                    ||  m.file_type.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

This post is in response your most recent comments. Your question has nothing to do with aps.net core. As I understand, you're asking how to configure the jQuery DataTable to display a link. Clicking the link downloads a file. Even though I've shown you how to do this several different ways with examples, you are struggling with how to make this work with the jQuery DataTable syntax.

The answer to your question is the pattern below. Use the row parameter to get to the items in the DataTable row.

{ "data": "name", 'render': function(data, type, row, meta) {  
        console.log(row);  
        return '<a href="https://localhost:7134/api/filetable/path/' + row.id + '" >' + row.name +'</a>';  
    }  
}  

However, I'll provide the rest of the code which is a little different than your code. I took the time to read the jQuery DataTable documentation related to server side processing and applied the concept to the code above.

First, below are request and response models. I grabbed the request model from the DataTable forum and added the nullable types so the models work with .NET 6 validation.

   public class FilePath  
    {  
        public Guid Id { get; set; }  
        public string Path { get; set; }  
        public string Name { get; set; }  
        public DateTimeOffset Created { get; set; }  
    }  
  
  
    public class DataTableResponse  
    {  
        public int draw { get; set; }  
        public int recordsTotal { get; set; }  
        public int recordsFiltered { get; set; }  
        public List<FilePath> data { get; set; }  
    }  
  
  
    public class DataTableAjaxPostModel  
    {  
        public int? draw { get; set; }  
        public int? start { get; set; }  
        public int? length { get; set; }  
        public List<Column>? columns { get; set; }  
        public Search? search { get; set; }  
        public List<Order>? order { get; set; }  
    }  
  
    public class Column  
    {  
        public string? data { get; set; }  
        public string? name { get; set; }  
        public bool? searchable { get; set; }  
        public bool? orderable { get; set; }  
        public Search? search { get; set; }  
    }  
  
    public class Search  
    {  
        public string? value { get; set; }  
        public string? regex { get; set; }  
    }  
  
    public class Order  
    {  
        public int? column { get; set; }  
        public string? dir { get; set; }  
    }  
  
}  

The IFileTableService does not change. The only change is to the api action that returns file results. Notice the response is based on the jQuery DataTable object model.

[HttpPost("path")]  
public async Task<DataTableResponse> GetFilePathsFromDocumentStore([FromForm] DataTableAjaxPostModel model)  
{  
    List<FilePath>? results = await _fileTableService.GetFilePathsFromDocumentStore();  
  
    DataTableResponse response = new DataTableResponse()  
    {  
        draw = 1,  
        recordsFiltered = 5,  
        recordsTotal = 5,  
        data = results  
    };  
  
    return response;  
}  

I did not implement sorting, filtering, paging, etc I'll leave that part up to you. I did convert your string indexed POST parameters to use standard MVC model binding that matches what the jQuery DataTable submits. Again please see the jQuery DataTable documentation for the jQuery DataTable object model details.

The client page simply calls the api to get the data.

@{  
    ViewData["Title"] = "Index";  
}  
  
<h1>Index</h1>  
  
<table id="customerDatatable" class="table">  
    <thead>  
        <tr>  
            <th>Id</th>  
            <th>Path</th>  
            <th>File</th>  
            <th>Created</th>  
        </tr>  
    </thead>  
    <tbody>  
    </tbody>  
</table>  

The script

$('#customerDatatable').dataTable({  
    "processing": true,  
    "serverSide": true,  
    "filter": true,  
    "ajax": {  
        "type": "POST",  
        "url": "https://localhost:7134/api/filetable/path",  
        "datatype": "data.json"  
    },  
    "columnDefs": [{  
        "targets": [1],  
        "visible": true,  
        "searchable": true  
    }],  
    "columns": [  
        { "data": "id" },  
        { "data": "path" },  
        { "data": "name", 'render': function(data, type, row, meta) {  
                console.log(row);  
                return '<a href="https://localhost:7134/api/filetable/path/' + row.id + '" >' + row.name +'</a>';  
            }  
        },  
        { "data": "created" }  
    ]  
});  

My Web API is a remote service which is why you see an absolute path. But if you take a look at the file download action you'll see it is the same pattern that I've shared many times over the last few months.

A few things to note. I'm using the file table stream_id to get the file contents from the actual SQL table; file_stream. I also included the relative file path which is not used in the example code. But, the relative path is needed if you are still using System.IO namespace and have sub directories. You can pass the relative path as a querystring parameter.

Answer 2

The file_Stream column in the file table contains the file contents. This is another way to get the file contents using an entity and LINQ rather than using the System.IO namespace. The API action returns a file stream regardless of the approach.

In other words, the sample uses LINQ to get the file which is exactly what you asked.

Now If I use your method I've to calll the method get by HttpClient and I could not fill the column nma via customer datatable but in my case I didn't do it that way.

I think you are making assumption again due to holes in your understanding. You said the Web API project is not a remote web site. Why is HttpClient required?

The example is a basic URL that downloads a file. You can use whatever method you like to call the URL but the easiest approach is a basic browser link.

I think the best way is to add the link of the file is to use ajax code that's the same context of my code. What do you think?

With AJAX, the HTTP response is returned to a call back function or promise. If the HTTP response contains the file contents, then JavaScript is required to open/download the file contents.

Answer 3

You said the Web API project is not a remote web site. Why is HttpClient required?

My understanding is the server side application is an ASP.NET Core Web project that's includes a Web API. The client side , which contains HttpClient to send HTTP requests. Is it correct?

In any case it doesn't work!

Answer 4

It missed nothing it's only code extract. To show you that if I used your method I didn't achieve that I want.
The performance will be bad. Please see our last post.

Let's move on!

Answer 5

Keep in mind the code below, like all the code presented so far, is NOT a production solution. You must take the concepts from the code sample and make them your own.

The Web API has two actions one returns a record set and the other returns a file stream. The record set consists of the stream_id, relative file path, file name, and create date. The relative path is not used in this example but I included the path to show you how to get the path if you want to use the relative path. The DbContext is not included but you should be able to register the FilePath type with the Dbcontext by this point.

The file steam comes from the file table NOT the System.IO namesapce. However, you could use the relative path to get the file from the file table directory and System.IO if that's what you want to do. Keep in mind every one of these concepts have been illustrates throughout your many posts.

Web API Service

    public interface IFileTableService  
    {  
        Task<List<FilePath>> GetFilePathsFromDocumentStore();  
        Task<DocumentStore?> GetFileByIdFromDocumentStore(Guid Id);  
    }  
  
    public class FileTableService : IFileTableService  
    {  
        private readonly ArchiveDbContext _context;  
  
        public FileTableService(ArchiveDbContext context)  
        {  
            _context = context;  
        }  
  
        public async Task<List<FilePath>> GetFilePathsFromDocumentStore()  
        {  
            List<FilePath>? results = await _context.Set<FilePath>()  
                .FromSqlRaw($@"SELECT [stream_id] AS Id  
                                  ,[file_stream].GetFileNamespacePath() As [Path]  
                                  ,[name] As [Name]  
                                  ,[creation_time] [Created]  
                              FROM [dbo].[DocumentStore]  
                              WHERE [is_archive] = 1").ToListAsync();  
            return results;  
        }  
  
        public async Task<DocumentStore?> GetFileByIdFromDocumentStore(Guid Id)  
        {  
            DocumentStore? doc = await _context.Documents.FirstOrDefaultAsync(d => d.stream_id == Id & d.is_archive == true);  
            return doc;  
        }  
  
    }  

Web API Model

    public class FilePath  
    {  
        public Guid Id { get; set; }  
        public string Path { get; set; }  
        public string Name { get; set; }  
        public DateTimeOffset Created { get; set; }  
    }  

Web API controller

    [Route("api/[controller]")]  
    [ApiController]  
    public class FileTableController : ControllerBase  
    {  
        private readonly IFileTableService _fileTableService;  
        public FileTableController(IFileTableService fileTableService)  
        {  
            _fileTableService = fileTableService;  
        }  
  
        [HttpGet("path")]  
        public async Task<List<FilePath>> GetFilePathsFromDocumentStore()  
        {  
            List<FilePath>? results = await _fileTableService.GetFilePathsFromDocumentStore();  
            return results;  
        }  
  
        [HttpGet("path/{uid:guid}")]  
        public async Task<IActionResult> GetFileByIdFromDocumentStore(Guid uid)  
        {  
            DocumentStore? doc = await _fileTableService.GetFileByIdFromDocumentStore(uid);  
  
            if(doc == null)  
            {  
                return NotFound();    
            }  
            return File(doc?.file_stream ?? new byte[0], "application/octet-stream", doc?.name);  
        }  
    }  

The MVC Controller also has two actions. The Index action populates the View from the FilePath records set. The DownloadFile action downloads a file. A few items to note. The example shows how to use HttpClient to get a file stream from Web API. The Razor view shows how to populate a link.

MVC ViewModel

    public class FilePathViewModel  
    {  
        public Guid id { get; set; }  
        public string path { get; set; }  
        public string name { get; set; }  
        public DateTimeOffset created { get; set; }  
    }  

MVC Controller

    public class FileController : Controller  
    {  
        private readonly IHttpClientFactory _httpClientFactory;  
        public FileController(IHttpClientFactory httpClientFactory)  
        {  
            _httpClientFactory = httpClientFactory;  
        }  
        // GET: FileController  
        [HttpGet]  
        public async Task<ActionResult> Index()  
        {  
            var httpClient = _httpClientFactory.CreateClient("FileTableClient");  
            var httpResponseMessage = await httpClient.GetAsync("api/FileTable/path");  
  
            if (httpResponseMessage.IsSuccessStatusCode)  
            {  
                using var contentStream = await httpResponseMessage.Content.ReadAsStreamAsync();  
                List<FilePathViewModel>? files = await JsonSerializer.DeserializeAsync<List<FilePathViewModel>>(contentStream);  
                return View(files);  
            }  
            return BadRequest();  
        }  
  
        [HttpGet]  
        public async Task<ActionResult> DownloadFile(Guid uid, string filename)  
        {  
            var httpClient = _httpClientFactory.CreateClient("FileTableClient");  
            var httpResponseMessage = await httpClient.GetAsync($"api/FileTable/path/{uid}");  
            if (httpResponseMessage.IsSuccessStatusCode)  
            {  
                byte[]? bytes = await httpResponseMessage.Content.ReadAsByteArrayAsync();  
                return File(bytes, "application/octet-stream", filename);  
                       
            }  
            return BadRequest();  
        }  
  
    }  

Razor View

@model List<FilePathViewModel>  
@{  
    ViewData["Title"] = "Index";  
}  
  
<h1>Index</h1>  
  
@if (Model != null && Model.Count() > 0)  
{  
    <table class="table">  
    <thead>  
        <tr>  
            <th>Id</th>  
            <th>Path</th>  
            <th>File</th>  
            <th>Created</th>  
        </tr>  
    </thead>  
  
    <tbody>  
        @foreach (FilePathViewModel item in Model)  
        {  
            <tr>  
                <td>@item.id</td>  
                <td>@item.path</td>  
                <td><a href="/file/DownloadFile?uid=@item.id&filename=@item.name">@item.name</a></td>  
                <td>@item.created</td>  
            </tr>  
        }  
    </tbody>  
    </table>  
}  
else  
{  
    <h1>No files found!</h1>  
}  

Lastly, it is up to you to configure the jQuery DataTable which should be trivial given the code sample. However, you must follow the openly published DataTable documentation to create the link. See previous posts for links to example code.