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

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

I've explained how to do this several times in previous threads with source code and links to openly published documentation! Please read your previous threads.

SELECT FileTableRootPath() + d.[file_stream].GetFileNamespacePath()  
 FROM DocumentStore as d   
 WHERE [Name] = 'guitar_PNG3338.png'  

Answer 3

your question is not clear. what will open the file? links no longer support UNC paths. if you use an action to return the file contents, are you going to use the file data in filetable or the UNC path?

Answer 4

Yes I just want to display the file (open it) . The content type is pdf, tiff...

This information has been thoroughly covered in your many threads with working sample code!

What's very confusing is I showed you early on how to get the file table directory using Entity Framework and a raw query. Why are you unable to do the same with a file path? It's the exact same code pattern. All you have to do is add the SQL which I also provided.

The following gets the full file path. You can use the file path to return the file.

Model

    public class FileTableFilePath  
    {  
        public string FilePath { get; set; } = string.Empty;  
    }  

Service

public async Task<string?> GetFilePathFormTheFileTable(string filename)  
{  
    FileTableFilePath? results = await _context.Set<FileTableFilePath>()  
        .FromSqlRaw($@"SELECT FileTableRootPath() + d.[file_stream].GetFileNamespacePath() AS FilePath  
                        FROM DocumentStore as d    
                        WHERE [Name] = '{filename}'").FirstOrDefaultAsync();  
    return results?.FilePath;  
}  

Action

[HttpGet("path/{filename}")]  
public async Task<IActionResult> GetFilePath(string filename = "guitar_PNG3338.png")  
{  
    string? path = await _fileTableService.GetFilePathFormTheFileTable(filename);  
    return Ok(path);  
              
}  

DbContext

protected override void OnModelCreating(ModelBuilder modelBuilder)  
{      
    modelBuilder.Entity<FileTableRoot>().HasNoKey().ToTable("FileTableRoot", t => t.ExcludeFromMigrations());  
    modelBuilder.Entity<FileTableFilePath>().HasNoKey().ToTable("FileTableFilePath", t => t.ExcludeFromMigrations());  
    modelBuilder.Entity<DocumentStore>().HasNoKey().ToTable("DocumentStore", t => t.ExcludeFromMigrations());  
    base.OnModelCreating(modelBuilder);  
}  

The URL is simply...

/api/FileTable/path/yourFileName.tiff  

To return a file stream, as shown several times now, from an action is just...

return File(System.IO.File.OpenRead(path), "application/octet-stream", filename);  

Answer 5

I understood that you've wrote but as I said it does not work in my context (In ohter context that you propose is ok)

why?

I use jquery datatable which works on the server side processing.
The means that we send information via HttppResquet to the client so we are working server side.

Hence my initial question how to introduce in the getserie method or directly in customerDAtaTable.js in the ajax code the link of files in the table to display them.