Active Query Builder Web API

Query Transformer object

Create a new instance of the QueryTransformer object and pass the URL that will be used to for accessing the web server, obtaining the list of query columns and passing the user's commands.

var QT = new ActiveQueryBuilder.QueryTransformer(url);

Two handlers must be defined on the server: GetQueryColumns and TransformSql (see the "Server configuration" section below).

For example:

var QT = new ActiveQueryBuilder.QueryTransformer("http://localhost:54459");

The list of query columns will be requested from "http://localhost:54459/GetQueryColumns", and user commands will be passed to "http://localhost:54459/TransformSql".

Criteria Builder control

To display the Criteria Builder control, call the criteriaBuilder.Init method and pass the id of the container HTML element as a parameter:

QT.criteriaBuilder.init(id);

Server configuration

To send a request to Active Query Builder Web API, you must pass the identifier of your database schema snapshot via the "_guid" parameter, and the SQL query text to modify via the "_sql" parameter.

Request for query columns

Define a server-side method to handle the GetQueryColumns request as follows.

public ActionResult GetQueryColumns()
{
    ActiveQueryBuilderApi api = new ActiveQueryBuilderApi();
    SqlQuery query = new SqlQuery(_guid, _sql);
    List<QueryColumn> columns = api.GetColumnsPost(query);
    return Json(columns);
}

Request for data

Define a server-side method to handle the TransformSql request as follows.

public ActionResult Transform(Transform transform)
{
    transform.Guid = _guid;
    transform.Sql = _sql;
    ActiveQueryBuilderApi api = new ActiveQueryBuilderApi();
    TransformResult result = api.TransformSqlPost(transform);
    if(!string.IsNullOrEmpty(result.Error))
        return new HttpBadRequestResult(result.Error);
    return GetDataMsSql(result.Sql); // send data to the client
}

Working with the client library

Getting list of query columns and initial data request

const QT = new ActiveQueryBuilder.QueryTransformer("http://localhost:54459"); 
QT.pagination.skip( skipCount );
QT.pagination.take( takeCount );
 
var columns = [];
QT.on('columnsLoaded', function(_columns) {
    columns = _columns;
    QT.criteriaBuilder.init('criteriaBuilder'); 
    QT.transform(); // send request to server
}
 
QT.on('sendTransform', function() {
    //show loader
});
 
QT.on('dataReceived', function(rows) {
    renderTable(columns, rows); //display data table
    // hide loader
});
QT.loadColumns(); // get query columns

Sorting

$(document).on('click', '.order-asc', function() {
    var column = $(this).text(); //get clicked column name   
    QT.sortings.orderBy(column, 'asc');
    QT.transform(); 
    //the dataReceived event handler will render data table
});

Pagination

function paginate(skipCount, takeCount) {
    QT.pagination.skip( skipCount );
    QT.pagination.take( takeCount );
    QT.transform(); 
}

Filtration

function nameIsNotNull() {
    QT.filters.isNotNull( 'name' ); // WHERE 'name' is not null
    QT.transform(); 
}
function idBetween( from, to ) {
    QT.filters.Between( 'id', from, to ); // WHERE 'id' BETWEEN "from" AND "to"
    QT.transform(); 
}

Alternate way using the Column object:

function idBetween( from, to ) {
    var column = QT.getColumnByName( 'id' );
    QT.filters.add( column.between( from, to ) );
    QT.transform(); 
}

Totals

function maxId() {
    QT.totals.max( 'id' );
    QT.transform(); 
}

Alternate way using the Column object:

function maxId() {
    var column = QT.getColumnByName( 'id' );
    QT.totals.add( column.max() );
    QT.transform(); 
}

Note: The 'demo.js' library contains full JavaScript code of this Online SQL Query Builder web page.

Have questions or comments?

Leave your feedback below.