Set a limit of records returned through API request?

Snackles

Hello,

I have an ASP.NET MVC web app that uses API requests to send and receive records from an SQL server. On one particular table, there are over 80,000 records; however, I don't need all 80,000 records. Is there a way to only grab the top 10,000 records or set some kind of limit on the number of records returned?

Thank you!

colin

Hi @Snackles ,

Given your question, I suspect you haven't tried serverSide - for that number of records, it is recommend. There, only the required records needed for that display are returned,

Cheers,

Colin

Snackles

Hey Colin,

Yeah, that's kind of what I figured I should do. I'm just struggling to under the documentation for serverSide processing. I'm pretty new to this stuff and don't have as much time as I'd like to spend on it. Most examples seem to reference using PHP? Is that necessary? Can I just use my current API and ajax?

Thanks for the response!

Snackles

I found a temporary solution using the Where Conditions in my controller.

.Where(q => q.Where("Writeoffs.writeoffdatetime", "DATEADD(year, -1, GETDATE())", ">=", false))

In regard to implementing server-side processing in an ASP.NET MVC web app. I set "serverSide": true and I did get a response from the server after a few minutes of waiting. I'm just struggling to figure out how to set the "Sent parameters" like Draw, Length, Searchable mentioned on the Server-side processing manual webpage: https://datatables.net/manual/server-side.

allan

If you enable serverSide then those parameters will be sent to the server automatically.

One option would be to use the Editor .NET libraries which support server-side processing natively. You don't need an Editor license to use them, the .NET part is open source although you would need to use the Editor documentation to make use of them.

If you search the web you'll find a number of others have also written on the topic of using DataTables with server-side processing - e.g. this YouTube clip.

Allan

Snackles

Hey Allan,

I've watched that YouTube video before, but it's a bit confusing because my controller looks nothing like that - I used the generator on this site.

I noticed at the top of the controller in that video, that they declared variables like displayLength, displayStart. I don't have varaibles declared like that and in my API response draw is null and recordsTotal and recordsFiltered aren't being returned.

Should I declare the variables like what I did for int recordsTotal in my controller below? It's commented out at the moment.

public class ProductUpdatesController : ApiController
    {
        [Route("api/ProductUpdates")]
        [HttpGet]
        [HttpPost]
        public IHttpActionResult ProductUpdates()
        {
            var request = HttpContext.Current.Request;
            var settings = Properties.Settings.Default;
            //int recordsTotal = int.Parse(HttpContext.Current.Request["recordsTotal"]);

            using (var db1 = new Database(settings.DbType, settings.DbConnection1))
            {
                
                var response = new Editor(db1, "ProductUpdates", "productupdateID")
                    .Model<ProductUpdatesModel>()
                    .Field(new Field("ProductUpdates.productupdateID")
                        .Validator(Validation.NotEmpty())
                    )
                    .Field(new Field("ProductUpdates.requesttypeID")
                        .Options(new Options()
                            .Table("RequestTypes")
                            .Value("requesttypeID")
                            .Label("requestname"))
                        .Validator(Validation.NotEmpty())
                    )
                    .Field(new Field("RequestTypes.requestname")
                        .Validator(Validation.NotEmpty())
                    )
                    .Field(new Field("ProductUpdates.productstatusID")
                        .Options(new Options()
                            .Table("ProductUpdateStatuses")
                            .Value("productstatusID")
                            .Label("productupdatestatus"))
                        .Validator(Validation.NotEmpty())
                    )
                    .Field(new Field("Status.productupdatestatus")
                        .Validator(Validation.NotEmpty())
                    )
                    .LeftJoin("RequestTypes", "RequestTypes.requesttypeID", "=", "ProductUpdates.requesttypeID"
                    )
                    .LeftJoin("ProductUpdateStatuses as Status", "Status.productstatusID", "=", "ProductUpdates.productstatusID"
                    )
                     .Process(request)
                    .Data();
                 
                return Json(response);
            }
        }
    }

colin

Hi @Snackles ,

For server side processing, the protocol is discussed here. It would also be worth looking at the examples to see what's sent and what's expected in the response.

Cheers,

Colin