Integrating 3rd party SQL query builder in ActiveReports.

UPDATE: Since this post was written, we've added our own visual query builder. The data source dialog that comes with ActiveReports does not provide an in-built SQL editor to build queries. The query editor in the data source dialog is a plain text editor. Editing a SQL query can become strenuous at times, when there are a lot of tables and columns involved. This post demonstrates how to integrate a 3rd party SQL query builder in ActiveReports. I have used the trial version of Active Query Builder .NET edition, a visual SQL query builder component. However, if you have another component built in .NET that provides similar functionality, it can be very easily integrated using the ActiveReports Designer API. I will use this 3rd party tool in the EndUserDesigner sample application that comes with ActiveReports install. The sample attached to this post uses the Northwind database (nwind.mdb). The database connection is a standard OLeDB connection. Once the connection is setup and the EndUserDesigner sample is run, a custom query builder form is displayed when the report's database icon is clicked on the detail section's header. This form is populated with a list of tables and queries from the database, which can then be dragged onto the editor The SQL is generated based on the tables and columns that is selected. This SQL is then assigned to report's data source to populate the Report Explorer's bound fields node with the columns selected in the query. There are 2 VS 2010 projects in this sample:

• SQLQueryBuilder project: This project contains a custom form built using sql editor components from Active Query SQL query builder. The output of this project is an assembly.
• EndUserDesigner project: This project contains the end user designer control from ActiveReports. The custom form built using the SQLQueryBuilder project is consumed in this project. The output from SQLQueryBuilder project is added as reference to this project.

SQLQueryBuilder project: This project uses three components from the Active Query Builder and two windows buttons to design a custom query builder form. This form has to two public properties: ReportQuery and ConnectionString.

• QueryBuilder component, PlainTextSQLEditor component, SQL Editor component from Active Query Builder.
• QueryBuilder load event: Wires up the three components together.
• QueryBuilder SQLUpdated event: Assigns the sql being generated on the fly to the sql editor as plain text.
• SQL Editor Leave event: Assigns the query as text from sql editor to the query builder as a sql.
• OK button: On click, sets the ReportQuery property to the SQL string. This property is then used to assign the SQL to ActiveReport's data source in the EndUserDesigner project.
• Cancel button: On click, sets the the ReportQuery property to an empty string.

QueryBuilder_load event code: // OleDB connection that is set to the default connection string. OleDbConnection connection = new OleDbConnection(); connection.ConnectionString = this.ConnectionString; // meta data provider set to the above connection OLEDBMetadataProvider metadataProvider = new OLEDBMetadataProvider(); metadataProvider.Connection = connection; // A syntax provider for different syntax for different databases UniversalSyntaxProvider syntaxProvider = new UniversalSyntaxProvider(); // assign the metadata provider and syntax provider to query builder this.queryBuilder1.MetadataProvider = metadataProvider; this.queryBuilder1.SyntaxProvider = syntaxProvider; // PlainTextSQLBuilder builds the query as the tables and fields are added or removed this.plainTextSQLBuilder1.QueryBuilder = this.queryBuilder1; // refresh the meta data once the setup is done. queryBuilder1.RefreshMetadata(); QueryBuilder_SQLUpdated event code: //Query from PlainTextSQLBuilder is assigned to the sql editor when the sql is updated in the query builder this._sqlEditor.Text = this.plainTextSQLBuilder1.SQL; SQLEditor Leave event code: try { //sql editor text getting assigned to the query builder as sql. There is a possibility that a user might type the SQL directly in the SQL editor. queryBuilder1.SQL = _sqlEditor.Text; } catch (Exception ex) { MessageBox.Show(ex.Message, "Could not parse SQL. Please verify that the sql input is correct."); } ReportQuery property holds on to the SQL that is generated using the query builder form. ConnectionString property is used to pass the database connection information to this form. When this form loads, the tables and queries from database is automatically populated in the right pane. A table can now be selected and dragged onto this builder surface. See image below: EndUserDesigner project: ActiveReports Designer API provides a DataSourceIconClick event where the default data source dialog that comes with ActiveReports can be overridden with a custom 3rd party control implementation. We will use this event to display the custom query builder from instead. To attach to this event, navigate to the property of the designer control in the property grid. Scroll down the list of event and you will find the DataSourceIconClick event and double click . Designer_DataSourceIconeClick method signature: private void arDesigner_DataSourceIconClick(object sender, DataSourceIconClickEventArgs e) //connection string passed to the query builder form: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\..\\NWIND.MDB;Persist Security Info=False Hide the default data source dialog to show up: // Setting the Cancel property of DataSourceIconClickEventArgs will hide the default data source dialog to display when the data source icon is clicked. e.Cancel = true; Displaying the custom query builder: // Create an instance of the query builder form SQLQueryBuilder.SQLQueryBuilder sqlQueryBuilder = new SQLQueryBuilder.SQLQueryBuilder(); // assign the connection string sqlQueryBuilder.ConnectionString = this._defaultConString; // call ShowDialog and get the info based on dialog result if (sqlQueryBuilder.ShowDialog() == DialogResult.Cancel) return; Assigning this SQL query to the report in the designer. // ActiveReports 7 code: Create an ActiveReports OLEDB datasource instance; GrapeCity.ActiveReports.Data.OleDBDataSource oledbDS = new GrapeCity.ActiveReports.Data.OleDBDataSource(); // ActiveReports 6 code: Create an ActiveReports OLEDB datasource instance; DataDynamics.ActiveReports.DataSources.OleDBDataSource oledbDS = new DataSources.OleDBDataSource(); // assign the connection string and SQL to this data source instance. oledbDS.ConnectionString = this._defaultConString; oledbDS.SQL = sqlQueryBuilder.ReportQuery; // using the public ReportQuery property from the query builder from to assign the SQL. // ActiveReports 7 code: set this OLeDB data source as report's data source. (this.reportdesigner.Report as SectionReport).DataSource = oledbDS; // ActiveReports 6 code: set this OLeDB data source as report's data source. this.arDesigner.Report.DataSource = oledbDS; In the query builder form, the right pane shows the list of tables and views in the database. I can drag a table and and select columns. As the columns are selected in the middle pane of the form, the SQL text gets updated in the sql text editor in the bottom half of the form. When finished, click OK button. The form closes and the ReportQuery property of the form now contains the generated SQL as text. Left pane shows the selected columns in the "FIELDS" node and tables in the "FROM" node. How do we know if ActiveReport's report was assigned with that SQL: Once the sql builder form is closed, assuming you clicked OK, in the end user designer application, go to the Report Explorer window (on the right) and expand the Fields -> Bound Node. The field list is populated with the fields that you selected in the query. You can now drag one or more of these fields on to the report design surface and preview the report. In this screen-shot, I dragged the OrderID node from Report Explorer on to the detail section., which is added as a TextBox control on to the report. You will notice that in the property grid, that DataField property of this control is automatically assigned to the "OrderID" field. This sample demonstrates a very simple usage of OLeDB with Active Query Builder component. However, the same can be achieved with Microsoft SQL Server or Oracle Server as Active Query Builder has support for those. This implementation can be more ingrained if the developer desires to make it a user driven databases selection input at run-time that can then be programmed into dynamically loading this query builder form with user selected connection string. Topic links from ActiveReports 7 online help: Overview of DataSourceIconClick event How to Bind Reports to a Data Source ActveReports 7 links: ActiveReports 7 ActiveReports 7 Download ActiveReports 7 Online Help ActiveReports 7 Forum Active Query Builder links: Active Query Builder Active Query Builder .NET edition This can also be achieved in ActiveReports 6. Topic links from ActiveReports 6 online help: Overview of DataSourceIconClick event How to Bind Reports to a Data Source ActveReports 6 links: ActiveReports 6 ActiveReports 6 Download ActiveReports 6 Online Help ActiveReports 6 Forum Attachments: Attached are projects for ActiveReports 6 and ActiveReports 7. EndUserDesigner_SQLBuilder_AR7 EndUserDesigner_SQLBuilder_AR6