Raw SQL Query - .NET Framework

iqvct

My use case involves:

.NET Framework.
SQL Server 2019

I'm trying to discern the most efficient way to utilize the currently existing Models/Controllers to output to a View the result of raw SQL queries. The result of this query needs to be read as a single value on the front end. There would be no user interaction with this value.

My searches have come up mostly empty to my specific question.

One thing I found in the documentation which was semi-related was here:
https://editor.datatables.net/manual/net/getting-started#SQL-functions

This is related in the sense that it allows the usage of a raw sql query, but unrelated as I have no need for this to be an editable field in a table.

For example, let's say I have a table with 6 total records. I want to display the count of these records as a single value on the front end as 6.

One thing I have been able to do is leverage new Field in the controller to execute the select count() function as shown below:

    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")
                   
                
                    .Field(new Field("(select Count(LoginCountryAccessId) from LoginCountryAccess)", "Count").Set(false))

                    .Debug(true)
                    .Process(request)
                    .Data();

                return Json(response);
            }
        }
    }

This works and retrieves the value of 6. But as expected, this is appending a new field to the table, applying the value 6 as a new field to all 6 records.

What I'm looking to do is simply execute this SQL query, retrieve the value 6, and leverage this # independently in other ways on the front end rather than being part of a table.

I understand that this may not be specifically related to datatables, but any nudge in the right direction would certainly be appreciated.

kthorngren

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

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

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

@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

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

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

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