EasyQuery ASP.NET - using jQuery widgets on WebForms project

While we have native WebForms controls which can be used in your project, sometimes it's more convenient to use our jQuery widgets even in WebForms project and setup a kind of “controller” for them on server-side using web-service.

Run Data Model Editor application in wizard mode (“Create Data Model” program icon) and follow the instructions to create a new data model for your database. You will define database tables you would like to use in your the queries, links between them and entities with their attributes (mostly fields in added tables). When the model is created - save it to some file on your hard-drive (let it be model1.xml for example);

Then open your project and add the following assemblies to its References list:

• Korzh.EasyQuery.NET40
• Korzh.EasyQuery.Db.NET40
• Korzh.EasyQuery.WebServices.NET40

We listed here the names of .NET 4.0 assemblies. Use the similar assemblies but with another “suffix” if your have another target platform. For example the assemblies ending on ”.NET45” should be used for .NET 4.5 platform and so on.

To handle AJAX requests from client-side controls (widgets) you need some web-service or controller on your server side. EasyQuery library contains several special classes which allow you to define such service very easily and in the shortest time.

First of all add a new WebService class into your project. Right-click on your project node in Solution Explorer, select “Add | New Item” and then choose “Web Service” from the item templates. Change the default name of new web-service to “EasyQuery.asmx” and click Add button. Open EasyQuery.asmx.cs file and add the following namespaces into its “using” section:

using Korzh.EasyQuery.Db;
using Korzh.EasyQuery.WebServices;

Then change the base class of EasyQuery to inherit it from to EQDbWebService.

So now it should look this way:

    [System.Web.Script.Services.ScriptService]
    public class EasyQuery : Korzh.EasyQuery.WebServices.EQDbWebService

Then override GainModel() and CreateQuery() methods. Put there the code which creates and initializes your DataModel and Query objects respectively. Here is an example:

protected override DataModel GainModel() { 
    string dataPath = System.Web.HttpContext.Current.Server.MapPath("~/App_Data");
 
    DbModel model = new DbModel();
    model.LoadFromFile(System.IO.Path.Combine(dataPath, "model1.xml"););
 
    return model;
}
 
protected override Query CreateQuery() {
    DbQuery dbQuery = new DbQuery();
    dbQuery.Formats.SetDefaultFormats(FormatType.MsSqlServer);
    return dbQuery;
}

Finally, you need to define three additional methods: GenerateQueryStatement(), LoadQueryByName() and GetDataSetBySql() Here is an example of their implementation:

protected override string GenerateQueryStatement(Query query) {
    SqlQueryBuilder builder = new SqlQueryBuilder((DbQuery)query);
    if (builder.CanBuild) {
        builder.BuildSQL();
        return builder.Result.SQL;
    }
    else
        return string.Empty;
}
 
protected override void LoadQueryByName(Query query, string queryName) {
    string queriesFolderPath = queriesPath;
    query.LoadFromFile(queryName + ".xml");
}
 
protected override DataSet GetDataSetBySql(string sql) {
    SqlConnection conn = new SqlConnection();
    conn.ConnectionString = "<your connection string goes here>";
    DataSet dataSet = new DataSet();
 
    conn.Open();
    SqlCommand command = new SqlCommand(sql, conn);
    SqlDataAdapter dbAdapter = new SqlDataAdapter(command);
    dbAdapter.Fill(dataSet);
 
    return dataSet;
}

You can find an example of EasyQuery web-service implementation in our EqMvc3Demo project (it's distributed with demo version of EasyQuery). Look for EQService.asmx.cs file. Actually you can simply copy the content of that file to your own class and just modify it according to your settings (model name, connection type, etc).

Now we move from server-side code to client-side. You should include the necessary scripts and CSS files to your page(s). First of all we need jQuery and jQuery UI scripts and CSS files since EasyQuery controls are implemented as jQuery widgets. Secondly, the EasyQuery script itself and corresponding CSS file. We suggest you to use the scripts and CSS files from our CDN since it always contains the latest version of them. You can find local copise of those files in \JS sub-folder of EasyQuery's installation folder.

Here is an example:

    <script src="http://code.jquery.com/jquery-1.10.2.js"></script>
    <script src="http://code.jquery.com/ui/1.10.3/jquery-ui.js"></script>
    <link href="http://code.jquery.com/ui/1.10.3/themes/smoothness/jquery-ui.css" rel="stylesheet" type="text/css" />
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
 
    <script src="http://cdn.korzh.com/eq/eq.all.min.js" type="text/javascript"></script>
    <script src="http://cdn.korzh.com/eq/eq.view.js" type="text/javascript"></script>
 
    <link href="http://cdn.korzh.com/eq/themes/default/easyquery.css" rel="stylesheet" type="text/css" media="screen" />
    <link href="http://cdn.korzh.com/eq/themes/default/eqview.css" rel="stylesheet" type="text/css" />

Finally we need to define our visual widgets on the web-page and connect them with our server-side service. First of all we need to define the placeholders for our widgets into your .aspx file:

<div id="EntitiesPanel"></div>
.  .  .  .  .  .  .  .  .  .    
<div id="ColumnsPanel"></div>
.  .  .  .  .  .  .  .  .  .    
<div id="QueryPanel"></div>

It's better to give them the same IDs as in example above since in this case they will be automatically accessible by EasyQuery's client-side scripts without additional configuration.

Usually you need some default functionality to clear your current query, load/save and execute it. Our scripts have a default implementation for all these operations and to make them work - you just need to define the placeholders for corresponding buttons.

<div id="ClearQueryButton" class="eqv-button clear-button">Clear query</div>
<div id="LoadQueryButton" class="eqv-button load-query-button">Load query</div>
<div id="SaveQueryButton" class="eqv-button save-query-button">Save query</div>
<div id="ExecuteQueryButton" class="eqv-button execute-button">Execute</div>

As in case of widget placeholders, you should define them with exactly the same IDs. The names of style classes can be different - the ones which are listed here are from our default style theme defined in eqview.css file.

Finally you need to define a <script> section on our page and initialize EasyQuery widgets:

<script type="text/javascript">
    $(document).ready(function () {
        EQ.client.init({
            serviceUrl: "/EasyQuery.asmx",
            modelName: "Modle1",
            queryPanel: {},
            columnsPanel: { allowAggrColumns: false}
        });
 
        EQ.view.init();
    });
</script>

EQ.client.init call above initializes the widgets. You can set different parameters of those widgets using queryPanel, columnsPanel or entitiesPanel properties of the object passed in the parameter to init() method. Then you see another call: EQ.view.init(). This function adds default behavior for our widgets and correspoding buttons. It will works out-of-box if you define the widget placeholders and buttons as it's described above. All the functions which controls this default behavior are defined in eq.view.js file which is distributed with full, unminified source code - so you can see how it works and change the way it works if necessary.