How to add query builder page from our MVC sample project in your own app

You would like to add into your own project the same query builder page (or with some little modifications) as in our EQMvc3Demo sample project.

Below you will find step-by-step description how to achieve the desired goal. We use EQMvc3Demo project as a source but all instructions here can be applied to EQMvc4DemoEF project as well.

First of all you need the view file itself in your.

• Copy EQMvc3Demo\Views\Home\QueryBuilder.cshtml file to YourProject\Views\Home\
• Add it into your project (into Views/Home).
• Add a new action QueryBuilder (or whatever name your choose) into your HomeController:

public ActionResult QueryBuilder() {
    return View("QueryBuilder");
}

Run your project and check if this new page can be opened by /Home/QueryBuilder URL. New page will look quite ugly (since we haven't added any CSS files and scripts yet), but it must be opened without errors.

Now we need to brush up the style of our new page. We have created a special CSS files with default styles for EasyQuery widgets and QueryBuilder page. You need to copy those file into your project's folder, include them into the project and then link to your view page. Additionally you will need to include some JavaScript files. First of all we need JQuery and JQuery-UI scripts. Secondly - eq-all-min.js file which contains all EasyQuery widgets. So:

• Copy easyquery.css and eqview.css files from EQMvc3Demo\Content\ to YourProject\Content\
• Include these CSS files into your project.
• Copy eq-all-min.js file from EQMvc3Demo\Scripts to YourProject\Scripts and include this file into your project.
• Add jQuery, jQuery-UI and jQuery JSON scripts into your new QueryBuilder.cshtml view. You can skip this step if jQuery scripts are already included into your layout file (..\Views\Shared\_Layout.cshtml).
• Add EasyQuery script file (eq-all-min.js) into the view AFTER (it's important) all jQuery scripts.
• Link jQuery UI styles. Again this step is not necessary if those .css files are already linked in your layout file.
• Link our easyquery.css file.

Here are an example how your new view file may look like after all these changes:

@{
    ViewBag.Title = "Advanced Search";
}
 
    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
    <script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.10.1/jquery-ui.min.js"></script>
 
    <script src="@Url.Content("~/Scripts/eq-all-min.js")" type="text/javascript"></script>
 
    <link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.10.1/themes/base/minified/jquery-ui.min.css" rel="stylesheet" type="text/css" />
 
    <link href="@Url.Content("~/Content/easyquery.css")" rel="stylesheet" type="text/css" media="screen" />
    <link href="@Url.Content("~/Content/eqview.css")" rel="stylesheet" type="text/css" media="screen" />
 
 
     .  .  .  .  .  .  .  .  .  .  .  .  .

After this step try to open QueryBuilder page once again. Now it must look much better but still does not provide any functionality since we haven't added any server-side code yet.

EasyQuery JavaScript widgets make AJAX calls to your server-side code to perform the following tasks:

• To get data model.
• To load/save queries.
• To build SQL statement.
• To execute some SQL and get the result set.

To implement all these tasks our sample project has special controller: EasyQueryController. You need to copy this controller into your own project and then modify it according to your need. So, your next steps will be:

• Copy EQMvc3Demo\Controllers\EasyQueryController.cs file to YourProject\Controllers.
• Include this new controller into your project.
• Copy EQMvc3Demo\App_Data\NWind.xml file to YourProject\App_Data. Add App_Data folder if necessary.
• Add App_Data\NWind.xml into your project. Set it's “Build Action” property to “Content” and “Copy to Output Directory” to “Always”.
• Add references to EasyQuery assemblies:
  • Korzh.EasyQuery.NET40.dll
  • Korzh.EasyQuery.Db.NET40.dll
  • Korzh.EasyQuery.Mvc3.NET40.dll (or Korzh.EasyQuery.Mvc4.NET40.dll if your project uses MVC 4.)

Now try to run your project and open /Home/QueryBuilder page. Now it should look exactly like in our sample project. You can add columns and conditions and see the result SQL statement. [Execute] will not work since we don't have NWind database in your project. Now it's time to connect this page to your database and your model.

We specially used our demo model for NWind database on the previous step - just to make sure that everything works as expected and we don't have any problem in our server-side code, view markup or scripts. Now it's time to make the final modifications and connect this new query builder page to your project's data.

• First of all you will need your data model. To create it - run Data Model Editor (installed with EasyQuery library), connect it to your DB and follow the instructions. After performing all these steps save your model into some file. Let's call this new model file “MyModel.xml”.
• Copy MyModel.xml into App_Data folder, add it into the project and set its “Build Action” and “Copy to Output Directory” properties to the same values as for NWind.xml file on the previous step.
• Change the code in EasyQueryControl.cs to make it load your model on initialization instead of NWind.xml. Additionally you will need to change the connection string and the formats for generated SQL statements. Let's suppose you use MS SQL database. So the initialization part of EasyQueryController for your case will look similar to the following:

        static EasyQueryController() {
 
            dataPath = System.Web.HttpContext.Current.Server.MapPath("~/App_Data");
            dataModelPath = System.IO.Path.Combine(dataPath, "MyModel.xml");
 
            connectionString = "Data Source=localhost;Initial Catalog=MyDatabase;Integrated Security=True"; 
            queriesPath = System.IO.Path.Combine(dataPath, "Queries");
 
            if (!Directory.Exists(queriesPath)) 
                Directory.CreateDirectory(queriesPath);
 
            Model = new DbModel();
            Model.LoadFromFile(dataModelPath);
        }
 
 
        protected override DataModel GainModel() { 
            return Model;
        }
 
        protected override Query CreateQuery() {
            DbQuery dbQuery = new DbQuery();
            dbQuery.Formats.SetDefaultFormats(FormatType.MsSqlServer);
 
            return dbQuery;
        }

• Additionally you will possibly need to change GetDataSetBySql method of EasyQueryController class to make it use DbConnection and DataAdapter classes that correspond to your type of connection. In case of using MS SQL Server database instead of MS Access you will need to change there OleDbConnection to SqlConnection, OleDbCommand to SqlCommand and OleDbDataAdapter to SqlDataAdapter

That's all! Now your application has it's own form for advanced search!

For some projects it's not necessary to create model “manually” using Data Model Editor. EasyQuery has several other options to load and fill data model. Here are some of them:

• Load from .edmx file. If you use Entity Framework you can load your model directly from .edmx file. Just call: Model.LoadFromEdmx(“MyModel.edmx”)
• Entity framework code-first. In this case you can fill your model directly from DbContext or ObjectContext class. Here is an example:

    Model.LoadFromDBContext(myDbContext.GetType(), DbContextOptions.IncludeComplexTypesInParentEntity);

• You can fill your model by DB meta information.
• Finally, it's possible to fill data model “manually” using methods and properties of DataModel (or DbModel) class. Here is an example of such approach.