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

What exactly is the purpose of filestream?

I agree with you I added the filestream but it is useless.

getserie is working as expected

Yes, according to the link that I've put in my first post.

What is customerDAtaTable.js?

$(document).ready(function () {  
    $('#customerDatatable').dataTable({  
        "processing": true,  
        "serverSide": true,  
        "filter": true,  
        "ajax": {  
            "type": "POST",  
            "url": "/api/wSerie",  
            "datatype": "data.json"  
        },  
        "columnDefs": [{  
            "targets": [1],  
            "visible": true,  
            "searchable": true  
        }],  
        "columns": [         
           // { "data": "stream_id", "name": "Id", "autoWidth": true },  
            { "data": "name", "name": "Name", "autoWidth": true },  
            { "data": "file_type", "name": "Type", "autoWidth": true },  
            { "data": "cached_file_size", "name": "Size", "autoWidth": true },  
            { "data": "creation_time", "name": "Creation Date", "autoWidth": true },  
            { "data": "last_write_time", "name": "Last Write", "autoWidth": true },  
            { "data": "last_access_time", "name": "Last Acces", "autoWidth": true },  
            ]  
    });  
});  

Is this more code that you copied from the Internet???

Yes, according to the link that I've put in my first post.

I just want to display the file (open it) by adding a link in colum Name.
The best way to do that is to put to LINQ query directtly in getserie method or in customerDatatable.

I hope this is more clear. I wanted help to do this!

Answer 3

I know to put href in razor page.
I sorry I don't remember that you have already put the Linq query in this api context.
I suppose it's not easy.

In my side I'm here to have some help!

Let's move on.

Answer 4

Hi,

@AgaveJoe I tried to apply your code and I received it ; have yo an idea how I can solve this?

Answer 5

Sir, you have been a great help to me! I would like to thank you.

I repeat again your method is not adapted.