HomeActive Query Builder - IntroductionQuick Start GuidesQuick Start Guide for ASP.NET edition: Razor view engine, MVC4 and MVC5

1.5. Quick Start Guide for ASP.NET edition: Razor view engine, MVC4 and MVC5

Important:

This guide is for MVC 4 and higher environment, Razor view engine only!

Please refer to the appropriate guides for other environments:

Follow the steps below to install Active Query Builder ASP.NET Edition to your project. If you already have the component installed in your project and need further information about getting or setting the SQL query text, go to the Mini F.A.Q. section of this guide.

Easy installation
by using the NuGet package:

  1. Add the NuGet Package Manager Visual Studio extension.
  2. Add the "Active Query Builder ASP.NET Edition" NuGet package to your project.
  3. Add typical HTML code and the component's initialization code to your web page.

Traditional installation
by following the steps of this guide:

  1. Add the necessary assemblies.
  2. Add the necessary modules according to your IIS version and routes.
  3. Add the necessary JavaScript and CSS files.
  4. Load JavaScript libraries manually or using the BundleConfig.
  5. Add typical HTML code and the component's initialization code to your web page.
  1. Create a new solution and add a new project - ASP.NET MVC 4 Web Application, select the "Razor" option for the "View engine" setting.

    The steps below can be skipped in case of using the "Active Query Builder ASP.NET Edition" NuGet package.

    Proceed to the final steps in case of using it.

  2. Add the following assembly references to your project:

    • \assemblies\.NET 2.0\ActiveDatabaseSoftware.ActiveQueryBuilder2.dll
    • \assemblies\.NET 2.0\ActiveDatabaseSoftware.ActiveQueryBuilder2.Web.dll
    • \assemblies\.NET 2.0\ActiveDatabaseSoftware.ActiveQueryBuilder2.Web.Server.dll
    • \assemblies\third-party\Net40\Newtonsoft.Json.dll
    • \assemblies\third-party\log4net.dll

    You may also need to add the assemblies for specific metadata providers to retrieve metadata from a database, e.g.:

    • \assemblies\.NET 2.0\ActiveDatabaseSoftware.OLEDBMetadataProvider.dll

    Is it necessary to set the CopyLocal property of "ActiveDatabaseSoftware.ActiveQueryBuilder2.*" assemblies to True. This isn't done automatically in case of instructing to install the component to the Toolbox because assemblies are installed to GAC in this case.

  3. Apply the necessary changes to the "web.config" file according to your web server version.

    • For IIS 6 or Visual Studio Development Server, add the following module to the "configuration/system.web/modules" section:

      <add name="HttpModule"
      	type="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server.HttpModule,
      	ActiveDatabaseSoftware.ActiveQueryBuilder2.Web.Server"/>
      

      and the following assembly to the "configuration/system.web/compilation" section:

      <assemblies>
      	<add assembly="System.Windows.Forms, Version=2.0.0.0, Culture=neutral,
      	PublicKeyToken=B77A5C561934E089" />
      </assemblies>
      
    • For IIS 7 and higher, add the following modules to the "configuration/system.webServer/modules" section:

      <remove name="Session" />
      <add name="Session" type="System.Web.SessionState.SessionStateModule"
      	preCondition="managedHandler" />
      <add name="HttpModule" preCondition="integratedMode"
      	type="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server.HttpModule,
      	ActiveDatabaseSoftware.ActiveQueryBuilder2.Web.Server" />
      

      and the following assembly to the "configuration/system.web/compilation" section:

      <assemblies>
      	<add assembly="System.Windows.Forms, Version=2.0.0.0, Culture=neutral,
      	PublicKeyToken=B77A5C561934E089" />
      </assemblies>
      
  4. Add the following line to the to the beginning of the RegisterRoutes method in the RouteConfig file:

    routes.Add(ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Mvc.Routing.GetRoute());
    

    If you want to change the default HTTP handler URL for this route ("/ActiveQueryBuilderHandler.axd"), you can specify it as a parameter for the GetRoute method.

  5. Get the content from the "web_parts" directory ("css", "img" and "js" sub-folders) and put it in the directory of the web page or the parent control.

  6. Add the following directives to the <head> tag of the layout file (for example, "_Layout.cshtml").

    @using ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Mvc.UI
    
    @Html.ActiveQueryBuilder().GetCSS(settings =>
    	{
    		// Instructs to load the jQueryUI CSS files
    		settings.LoadJQuery = true;
    		// Determines path to the component's CSS files
    		settings.Path = "~/css/";
    	})
    
    @Html.ActiveQueryBuilder().GetScripts(settings =>
    	{
    		// Instructs to load the jQuery and jQueryUI JavaScript libraries
    		settings.LoadJQuery = true;
    		// Determines path to the component's JavaScript libraries
    		settings.Path = "~/js/release/";
    	})
    
    • Active Query Builder ASP.NET Edition requires jQuery 1.11 or higher, and jQueryUI 1.10.4 or higher, latest versions of the 2.x branch are recommended.

    • The default CSS theme allows for flex layout of controls, but limits support of web browsers (details). To get Active Query Builder compatible with legacy web browsers (IE 8-9), use the old CSS files contained the "web_parts\legacy_css" folder.
  7. Add the following code to your CSHTML file. Also you can find typical HTML code in the "web_parts\!Razor" folder.

    @using System.Data.SqlClient
    @using System.Web.UI.WebControls
    @using ActiveDatabaseSoftware.ActiveQueryBuilder
    @using ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Mvc.UI
    @using ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server  
    
    @Html.ActiveQueryBuilder(settings =>
    	{
    		settings.HttpCompressionEnabled = false;
    		settings.PersistentConnection = false;
    		settings.Language = "auto";
    		settings.UseCustomLanguageFiles = false;
    		settings.CustomLanguagePath = "~/Language Files/";
    
    		settings.Init += (sender, e) =>
    		{
    			// Get instance of the QueryBuilder object
    			var sessionStoreItem = sender as SessionStoreItem;
    			var queryBuilder = sessionStoreItem.QueryBuilder;
    
    			// Create an instance of the proper syntax provider for your database server.
    			queryBuilder.SyntaxProvider = new MSSQLSyntaxProvider();
    
    			// Metadata source definition. Choose one of the following ways:
    			// ===========================================================
    			// a) you can load metadata from the live database connection
    
    			var connection = new SqlConnection { ConnectionString = @"<Your connection string here>"};
    
    			queryBuilder.MetadataProvider = new MSSQLMetadataProvider {Connection = connection};
    
    			// ===========================================================
    			// b) or you can load metadata from the pre-generated XML file
    
    			// Denies metadata loading requests from the metadata provider
    			queryBuilder.OfflineMode = true;
    
    			var pathToXml = Server.MapPath("~\Path\to\file.xml");
    			queryBuilder.MetadataContainer.ImportFromXML(pathToXml);
    
    			// end of b)
    			// ===========================================================
    
    			// Initialization of the Metadata Structure object that's
    			// responsible for representation of metadata in a tree-like form
    			try
    			{
    				// Clears and loads the first level of metadata structure tree  
    				queryBuilder.MetadataStructure.Refresh();
    			}
    			catch (Exception ex)
    			{
    				Logger.Error("Error loading metadata", ex);
    			}
    		};
    	}).GetHtml()
    
    
    <div id="all">
    	<div id="content-container">
    		<div id="qb-ui">
    			@Html.ActiveQueryBuilder().ObjectTreeView(settings =>
    			{
    				settings.ShowFields = false;
    				settings.ShowDescriptons = false;
    				settings.SortingType = ObjectsSortingType.None;
    			}).GetHtml()
    
    			<div id="center">
    				@Html.ActiveQueryBuilder().SubQueryNavigationBar(settings >
    				{
    					settings.UnionNavBarVisible = true;
    				}).GetHtml()
    
    				<div class="qb-ui-canvas-container block-flat">
    					@Html.ActiveQueryBuilder().Canvas(settings =>
    					{
    						settings.DefaultDatasourceWidth = "auto";
    						settings.DisableDatasourcePropertiesDialog = false;
    						settings.DisableLinkPropertiesDialog = false;
    						settings.DisableQueryPropertiesDialog = false;
    						settings.MaxDefaultDatasourceHeight = "144";
    						// settings.FieldListOptions
    					}).GetHtml()
    					@Html.ActiveQueryBuilder().StatusBar().GetHtml()
    					@Html.ActiveQueryBuilder().Grid(settings =>
    					{
    						settings.OrColumnCount = 2;
    					}).GetHtml()
    				</div>
    			</div>
    			<div class="clear"></div>
    		</div>
    	</div>
    	@Html.ActiveQueryBuilder().SqlEditor().GetHtml()
    </div>

    The initialization code should be placed in the ActiveQueryBuilder.settings.Init event handler. There you should create Metadata and Syntax provider components and link these providers to the QueryBuilder object by setting the MetadataProvider and SyntaxProvider properties. Define a proper database connection object as a source for the Metadata provider or load metadata from the XML file.

    The Init event handler can get access to two object references to get access to their methods and properties: (sender as SessionStoreItem).QueryBuilder and (sender as SessionStoreItem).PlainTextSQLBuilder.

    The rest of the properties provided in the code above are initialized with their default values, they are listed here for reference, so you can remove them in your code unless you want to initialize them with some other value. The complete property reference is available here. Note that this reference was generated for the ASPX view engine. The properties and events of the QueryBuilderControl ASPX control can be accessed via the ActiveQueryBuilder helper in Razor.

    Targeting the project for .NET Framework 4.5, you should avoid setting of the HttpCompressionEnabled property to True. You can turn on HTTP compression in IIS as described here

  8. That's all! Now you can run your application.

Mini F.A.Q.: Getting and setting the SQL query text

  • To retrieve the query text from the query builder use the following code:

    protected void btnGetQuery_Click(object sender, System.EventArgs e)
    {
    	QueryBuilder queryBuilder = SessionStore.Current.QueryBuilder;
    	PlainTextSQLBuilder sqlBuilder = SessionStore.Current.PlainTextSQLBuilder;
    
    	string plainSQL = queryBuilder.SQL; // unformatted text
    	string formattedSQL = sqlBuilder.SQL; // formatted text
    }
  • To retrieve the query text from the text editor, use the following code:

    protected void btnGetQuery_Click(object sender, System.EventArgs e)
    {
    	string textSQL2 = SessionStore.Current.ClientSQL;
    }

    If you want to parse the final modifications made to SQL text by the end-user before the query execution, additionaly use the following code:

    try
    {
    	SessionStore.Current.QueryBuilder.SQL = SessionStore.Current.ClientSQL;
    
    	// execute the query here
    }
    catch (Exception ex)
    {
    	MessageBox.Show(ex.Message, "Parsing error");
    }
    
  • To assign SQL query text to the control use the following code:

    protected void btnSetQuery_Click(object sender, System.EventArgs e)
    {
    	SessionStore.Current.QueryBuilder.SQL = "SELECT * FROM ...";
    }

    If you want to do this without reloading a web page, additionaly use the following JavaScript code:

    QB.Web.Application.fullUpdate();
  • To update the SQL text on the client use the following JavaScript code:

    QB.Web.Application.importSQL('Select * From ...');
    QB.Web.Application.refreshSql();

This page was: Helpful | Not Helpful