Answers

• kthorngren Posts: 21,452Questions: 26Answers: 4,975

  December 2021

  I'm not familiar with .NET so can't give specifics but maybe you can create a separate SQL query and add that as a different object to the response variable. Maybe starting on line 22.

  Assuming you aren't using server side processing you can access the JSON response, using the 2nd parameter, of initComplete. Or if using server side processing you can access it in the xhr event.

  Kevin
• iqvct Posts: 45Questions: 7Answers: 0

  December 2021

  I'm not sure I completely follow.

  But as I mentioned previously, it seems this may extend beyond the scope of how I should be using Datatables as I'm not looking to return a table or any part of a table, but rather the result of a raw SQL query.
• kthorngren Posts: 21,452Questions: 26Answers: 4,975

  December 2021 edited December 2021 Answer ✓

  My suggestion is to add additional data to the response that is returned which is not a table or part of a table. I'm not familiar with .NET so here is some pseudo code:

  public class LoginCountryAccessController : ApiController
  {
      [Route("api/LoginCountryAccess")]
      [HttpGet]
      [HttpPost]
      public IHttpActionResult LoginCountryAccess()
      {
          var request = HttpContext.Current.Request;
          var settings = Properties.Settings.Default;
   
          using (var db = new Database(settings.DbType, settings.DbConnection))
          {
              var response = new Editor(db, "LoginCountryAccess", "LoginCountryAccessId")
                  .Model<LoginCountryAccessModel>("LoginCountryAccess")
                  
  
                  .Debug(true)
                  .Process(request)
                  .Data();
   
              response.count =  "select Count(LoginCountryAccessId) from LoginCountryAccess)", "Count")" 
              return Json(response);
          }
      }
  }
  

  Replace

  .Field(new Field("(select Count(LoginCountryAccessId) from LoginCountryAccess)", "Count").Set(false))

  With a select that adds an additional property to the JSON response to look something like this:

  {
    "data": [
       array of row data
    ],
    "count": "23"
  }
  

  In initComplete you can access the count property using the json parameter then do with it what you like. It won't be apart of the table data.

  Kevin
• iqvct Posts: 45Questions: 7Answers: 0

  January 2022

  @kthorngren I've been looking into this for a few days now.

  I follow your psuedo code and understand that you're saying we should add a new "key" : "value" pair to the response which should be in this case the variable name and the result of the SQL query. Then we would parse this out in the JS file using
  initComplete

  But from the way it's constructed it seems like if we were to do something in the area of line 21 from your psuedo code above, we'd be working within the properties of Editor, and I'm not seeing how you can add your own custom variable with a SQL query inside of this section.

  @allan Is this a limitation to .NET & Editor or is it more of a limitation to my understanding of how to apply this type of situation?
• allan Posts: 63,691Questions: 1Answers: 10,500 Site admin

  January 2022 Answer ✓

  The Editor.Data() method returns a DtResponse object - it doesn't have a count property available to it, so while Kevin's suggestion is spot on for a loosely typed language, C# is going to show a compiler error there.

  The way we've addressed that in DtResponse is that there is a meta property which is a Dictionary<string, object>, so you can do:

  response.meta.Add("count", value);
  

  where value is the result from a database query. You can use our database class to perform that query, or just call it directly if you already have a db connection.

  I should also note that for this specific example, on the client-side you could just use page.info() to get information about the number of records in the table.

  Allan
• iqvct Posts: 45Questions: 7Answers: 0

  January 2022

  This makes sense!

  The only issue now is this doesn't seem to work in the same manner as the Field property where I can easily place a query inside of parenthesis and it will pass it directly through and execute.

  When I do this using meta I get the query syntax returned as the value rather than the query result being returned as with Field

  I'll keep looking into the documentation and see what I can find.
• allan Posts: 63,691Questions: 1Answers: 10,500 Site admin

  January 2022

  Correct - you need to actually execute the SQL statement. The Field() method is used to build up the query to the database, and thus you can use an SQL function in there (now that we support that ), but if you want to run arbitrary SQL commands, you need to actually execute them.

  You can use our database class to do that if you want. E.g. the .Sql() method will execute an SQL statement and return a result object.

  Allan