{"id":718,"date":"2009-10-23T00:00:00","date_gmt":"2009-10-23T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/how-to-build-a-query-template-explorer\/"},"modified":"2021-04-29T15:28:28","modified_gmt":"2021-04-29T15:28:28","slug":"how-to-build-a-query-template-explorer","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/development\/dotnet-development\/how-to-build-a-query-template-explorer\/","title":{"rendered":"How to build a Query Template Explorer"},"content":{"rendered":"<div id=\"PRETTY\">\n<p class=\"START\">This is the second of a two-part series focusing on a WinForm user control called the <em>QueryPicker<\/em>. In <a href=\"http:\/\/www.simple-talk.com\/dotnet\/.net-tools\/a-unified-approach-to-multi-database-query-templates\/\">part 1<\/a>, I described <em>QueryPicker<\/em> from the user perspective: its greater versatility compared to the template explorer in SQL Server Management Studio (SSMS); the meta-query library available for SQL Server, Oracle, and MySQL; and how to easily add your own meta-queries to the library. In this article, I will demonstrate how to manipulate the <em>QueryPicker<\/em> programmatically with its straightforward API. Treating it (and a few other controls I&#8217;ve developed) as .NET Building blocks, I&#8217;ll describe (in detail) all the steps you need to wrap it in a subform and make it seamlessly blend with your own application &#8211; abstract concepts are useful, but when I discuss software design in my articles I like to give you everything you need for implementation, so you don&#8217;t have to waste your time figuring it out.<\/p>\n<p>For greater flexibility, I&#8217;ll also take you a step up the food chain to another user control in my open-source library, the <em>SqlEditor<\/em>. This control is a complete SQL editor akin to SSMS, but packaged as a user control so that it can easily be incorporated into a .NET application. <span class=\"START\">If you&#8217;re finding these articles interesting, then my next discussion <a href=\"http:\/\/www.simple-talk.com\/dotnet\/.net-framework\/using-three-flavors-of-linq-to-populate-a-treeview\/\">will dive into some of the inner workings<\/a> of my QueryPicker control.<\/span><\/p>\n<h1>Wrapping the QueryPicker in a Form<\/h1>\n<p>The <em>QueryPicker<\/em> is available as a user control to be embedded on your own form, but as I&#8217;ve mentioned, I have incorporated it into higher-level components also available as building blocks, which I&#8217;ll discuss later on.<br \/>\n If you choose to start with the <em>QueryPicker<\/em> itself, I recommend placing the control on a subform of your main application. You only need three controls on this subform: the <em>QueryPicker<\/em> plus two buttons to process or cancel the operation, as shown in<strong> Figure 1<\/strong>.<\/p>\n<div class=\"ILLUSTRATION\">\n<p class=\"ILLUSTRATION\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/852-forms.jpg\" alt=\"852-forms.jpg\" width=\"497\" height=\"357\" \/><\/p>\n<p class=\"CAPTION\">Figure 1. QuerySelectorForm &#8211; The QueryPicker is joined only by an Execute button and a Cancel button to provide a complete subform for your application.<\/p>\n<\/div>\n<p>The backing this form contains very few lines of code, as I&#8217;ll show you in a moment. On the startup side, there is a two-line <em>constructor<\/em> and a one-line <em>Setup<\/em> method. The <em>Setup<\/em> method needs to be called just before calling <em>ShowDialog<\/em> on the form; it identifies the type of the database being used (SQL Server, Oracle, or MySQL) so that the QueryPicker is populated with the correct set of queries. The <em>constructor<\/em> includes the standard <em>InitializeComponent<\/em> call plus a second line to hook up an event handler that watches which node is selected. If a leaf node is selected (i.e. an actionable query template) then the event handler enables the <strong>Execute<\/strong> button, otherwise it disables it. On the output side, there are handlers for the <strong>Execute<\/strong> and the <strong>Cancel<\/strong> buttons, along with the <em>Query<\/em>, <em>RevealQuery<\/em>, and <em>Description<\/em> properties. The three-line <strong>Execute<\/strong> button handler does the following:<\/p>\n<ul>\n<li>The first line of the handler implements the behavioral convention that if the <strong>Shift<\/strong> key is depressed, the query should be revealed (it is invisible by default). As shown below, it&#8217;s only setting the <em>RevealQuery<\/em> property, whose value should be checked by your main application, triggering an appropriate action.<\/li>\n<li>The second line does the work of generating the query from the template and your inputs. The single argument to the <em>ProcessInputs<\/em> method implements the behavioral convention that depressing the <strong>Alt<\/strong> key unmasks universal matches.<\/li>\n<li>The final line of the handler checks that there is, in fact, a query of non-zero length available after processing, and if so closes the dialog.<\/li>\n<\/ul>\n<pre class=\"lang:c# theme:vs2012\">public partial class QuerySelectorForm : Form\r\n{\r\n\u00a0\u00a0\u00a0\u00a0public QuerySelectorForm()\r\n\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0InitializeComponent();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0queryPicker.AfterSelect += queryPicker_AfterSelect;\r\n\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0public void Setup(ConnectionDetails connectionDetails)\r\n\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0queryPicker.DbType = connectionDetails.BaseDbType;\r\n\u00a0\u00a0\u00a0\u00a0}\r\n\r\n\u00a0\u00a0\u00a0\u00a0\/\/ Properties\r\n\u00a0\u00a0\u00a0\u00a0public string Query { get { return queryPicker.Query; } }\r\n\r\n\u00a0\u00a0\u00a0\u00a0public string Description { get { return queryPicker.Description; } }\r\n\r\n\u00a0\u00a0\u00a0\u00a0public bool RevealQuery { get; set; }\r\n\r\n\u00a0\u00a0\u00a0\u00a0\/\/ Event Handlers\r\n\u00a0\u00a0\u00a0\u00a0private void queryPicker_AfterSelect(object sender, TreeViewEventArgs e)\r\n\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0executeButton.Enabled = (e.Node.Nodes.Count == 0);\r\n\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0private void executeButton_Click(object sender, EventArgs e)\r\n\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0RevealQuery = (Control.ModifierKeys &amp;amp; Keys.Shift) &gt; 0;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0queryPicker.ProcessInputs((Control.ModifierKeys &amp;amp; Keys.Alt) &gt; 0);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0if (Query.Length &gt; 0) { Close(); }\r\n\u00a0\u00a0\u00a0\u00a0}\r\n\r\n\u00a0\u00a0\u00a0\u00a0private void cancelButton_Click(object sender, EventArgs e)\r\n\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Close();\r\n\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0}<\/pre>\n<p>Once the <em>QuerySelectorForm<\/em> closes, your application has the three updated properties (<em>Query<\/em>, <em>Description<\/em>, and <em>RevealQuery<\/em>), ready to use. This next code fragment shows the interaction from that perspective. As just described, first it calls the Setup method to identify the database type, and then invokes the dialog. If it comes back with a non-empty query (i.e. the operation was not cancelled by the user), it then honors the <em>RevealQuery<\/em> property by inserting the query&#8217;s description (<em>queryForm.Description<\/em>) as a comment along with the query text (<em>queryForm.Query<\/em>) into the <em>QueryTextBox<\/em> (a <em>RichTextBox<\/em> or similar control), automatically selecting the text of the query as well. Finally, it calls <em>ExecuteTheQuery<\/em>, a method in your application aligned with the function of the Execute button of the subform:<\/p>\n<pre class=\"lang:c# theme:vs2012\">public void PerformMetaQuery()\r\n{\r\n\u00a0\u00a0\u00a0\u00a0if (DbConnection != null)\r\n\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0queryForm.Setup(DbConnection);\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0queryForm.ShowDialog();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0if (queryForm.Query.Length &gt; 0)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0if (queryForm.RevealQuery)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0QueryTextBox.AppendText(\"\\n\\n\" +\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"--\" + queryForm.Description + \"\\n\");\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0int startPos = QueryTextBox.SelectionStart;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0QueryTextBox.AppendText(queryForm.Query);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0int endPos = QueryTextBox.SelectionStart;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0if (endPos &gt; startPos)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0QueryTextBox.Select(startPos, endPos - startPos + 1);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\/\/ Take appropriate action here...\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ExecuteTheQuery(queryForm.Query);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0else\r\n\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0MessageBox.Show(\"Please select a DB connection first.\", \"No connection\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0MessageBoxButtons.OK, MessageBoxIcon.Warning);\r\n\u00a0\u00a0\u00a0\u00a0}\r\n}\r\n<\/pre>\n<h2>Encapsulating the Multiple Database Support<\/h2>\n<p>The <em>QueryPicker<\/em> itself is completely database-neutral, as are the <em>QuerySelectorForm<\/em> and the <em>PerformMetaQuery<\/em> code to interface with it. Yet the secret to creating generic, multiple-database access components is actually in grasping the underlying parallelisms, illustrated in <strong>Figure 2<\/strong>. Typical .NET applications use the data access objects for SQL Server (e.g. <em>SqlConnectionStringBuilder<\/em>, <em>SqlDataAdapter<\/em>, <em>SqlConnection<\/em>, and so forth), and parallel components for other database types are also available, some of which are already incorporated in Microsoft&#8217;s .NET framework (e.g. the ODBC&lt;<em>DAO components<\/em>&gt;). Others are available from the database vendor (e.g. the MySQL&lt;<em>DAO components<\/em>&gt;). Oracle is right in the middle: Microsoft has supplied an Oracle client in the framework right up through to .NET framework 3.5, but this will be discontinued as of .NET 4.0 and you will need to obtain a third-party version.<\/p>\n<p>(If you&#8217;re interested, the announcement from the <em>OracleClient<\/em> program manager, Himanshu Vasishth, is <a href=\"http:\/\/blogs.msdn.com\/adonet\/archive\/2009\/06\/15\/system-data-oracleclient-update.aspx\">available here<\/a>.)<\/p>\n<div class=\"ILLUSTRATION\">\n<p class=\"ILLUSTRATION\"><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/852-dbSubClassing.gif\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/852-dbSubClassing_sml.jpg\" alt=\"852-dbSubClassing_sml.jpg\" width=\"630\" height=\"410\" \/><\/a><\/p>\n<p class=\"CAPTION\">Figure 2. Common Denominators for Multiple Database Access &#8211; (click on the image for a closer look) The typical .NET data access objects (e.g. SqlDataAdapter) are implementations of a broader-scoped component (e.g. the DbDataAdapter).<\/p>\n<\/div>\n<p>The <em>QueryPicker<\/em>, as well as every other relevant class in my open-source libraries, interacts only with the leftmost objects in Figure 2. There is just a single generic class, <em>DbDetails<\/em>, which references the database-specific classes and their underlying DLLs. The method signatures of the <em>DbDetails<\/em> class are:<\/p>\n<div class=\"ILLUSTRATION\">\n<p class=\"ILLUSTRATION\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/852-signatures.jpg\" alt=\"852-signatures.jpg\" width=\"607\" height=\"177\" \/><\/p>\n<p class=\"CAPTION\">Figure 3. Neatly Grouped Signatures of the DbDetails Class<\/p>\n<\/div>\n<p>The first parameter of each method (<em>dbType<\/em>) specifies the database type, which lets the class know which database-specific object to reference. I have grouped the methods in<strong> Figure 3<\/strong> to make this discussion a little easier to follow&#8230;<\/p>\n<p>The implementation of each method in the first group is just a switch statement, as shown here for <em>GetDbDataAdapter<\/em>, taking advantage of the relationships shown in Figure 2.<\/p>\n<pre class=\"lang:c# theme:vs2012\">public static DbDataAdapter GetDbDataAdapter(\r\n\u00a0\u00a0\u00a0\u00a0ConnectionStringManager.DBTypes dbType,\r\n\u00a0\u00a0\u00a0\u00a0DbConnection connection,\r\n\u00a0\u00a0\u00a0\u00a0string query)\r\n{\r\n\u00a0\u00a0\u00a0\u00a0switch (dbType)\r\n\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0case ConnectionStringManager.DBTypes.SqlServer:\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0return new SqlDataAdapter(query, (SqlConnection)connection);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0case ConnectionStringManager.DBTypes.Oracle:\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0return new OracleDataAdapter(query, (OracleConnection)connection);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0case ConnectionStringManager.DBTypes.MySql:\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0return new MySqlDataAdapter(query, (MySqlConnection)connection);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0case ConnectionStringManager.DBTypes.Odbc:\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0return new OdbcDataAdapter(query, (OdbcConnection)connection);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0default:\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0return new OdbcDataAdapter(query, (OdbcConnection)connection);\r\n\u00a0\u00a0\u00a0\u00a0}\r\n}\r\n<\/pre>\n<p>The second group provides database-neutral methods to access the database-type properties (known to the <em>DbDetails<\/em> class), such as:<\/p>\n<ul>\n<li>SQL Server allows a choice of windows authentication or database authentication, while neither Oracle nor MySQL does.<\/li>\n<li>SQL Server and MySQL have the concept of separate databases while Oracle does not. The <em>GetDatabaseEnumeratorQuery<\/em> method returns either a SQL expression to enumerate the database, or null to indicate it does not support it.<\/li>\n<li>Oracle is case-sensitive in queries while SQL Server is not. References to meta-objects in Oracle, therefore, must take this into account. Table names, column names, and so forth, are stored as uppercase in Oracle so must be referenced that way. The <em>GetRequiresUpperCase<\/em> method allows the <em>QueryPicker<\/em> to glean this fact and automatically convert user inputs to uppercase so your users will not have to worry about it.<\/li>\n<\/ul>\n<p>The final group contains a single method, <em>GetBaseDbType<\/em>, which is used for enhancing ODBC data sources. ODBC sources can represent standard databases, but they may also tie to Excel spreadsheets, Access databases, even plain text files. This method looks into the data source to determine if it is, in fact, a conduit for one of the supported database types (SQL Server, Oracle, or MySQL).<\/p>\n<p>The second and third groups provide support, but the first group of methods is the key. At the beginning of this section I mentioned that a typical .NET application uses SqlDataAdapter among other components. Despite that generic sounding name, <em>SqlDataAdapter<\/em> is specific to SQL Server. But now you have the <em>GetDbDataAdapter<\/em> method that returns a <em>DbDataAdapter<\/em>, the generic base class of the <em>SqlDataAdapter<\/em>. The same applies to <em>SqlConnection<\/em> <strong>vs<\/strong>. <em>DbConnection<\/em> and <em>SqlConnectionStringBuilder<\/em> <strong>vs<\/strong>. <em>DbConnectionStringBuilder<\/em>. Typical code for programmatically retrieving data and displaying it in a <em>DataGridView<\/em> from SQL Server (as for example, in Microsoft&#8217;s <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/fbk67b6z.aspx\">How to: Bind Data to the Windows Forms DataGridView Control<\/a>) looks like this:<\/p>\n<pre class=\"lang:c# theme:vs2012\">dataGridView.DataSource = bindingSource;\r\nSqlConnection connection = new SqlConnection(connectionString);\r\nSqlDataAdapter dataAdapter = new SqlDataAdapter(selectQuery, connectionString);\r\nDataTable dataTable = new DataTable(); dataAdapter.Fill(dataTable);\r\nbindingSource.DataSource = dataTable;<\/pre>\n<p>That is, you connect your <em>DataGridView<\/em> to your binding source, get a connection object from your connection string, then use that and the query to get a data adapter. The Fill method of the data adapter loads a data-table that you finally connect to the binding source, completing the necessary links. Converting the above code fragment to one that connects generically to any database type is a trivial substitution of methods:<\/p>\n<pre class=\"lang:c# theme:vs2012\">dataGridView.DataSource = bindingSource;\r\nDbConnection connection = DbDetails.GetDbConnection(dbType, connectionString);\r\nDbDataAdapter dataAdapter = \r\n\u00a0\u00a0\u00a0\u00a0\u00a0DbDetails.GetDbDataAdapter(dbType, connection,\u00a0selectQuery);\r\nDataTable dataTable = new DataTable(); dataAdapter.Fill(dataTable);\r\nbindingSource.DataSource = dataTable<\/pre>\n<p>Typical applications use a fixed connection string and a fixed query, which works fine unless your application is something like SSMS, where you want the user to be able to select a server, a database, and\/or an arbitrary query at runtime. Clearly, the <em>QueryPicker<\/em> is geared for just such an environment: it supplies arbitrary queries and can handle different database types. However, the QueryPicker requires additional infrastructure-a framework for pointing to a server and database as well as input and output venues.<\/p>\n<p><em>(You can find a comprehensive review of these elements of dynamic data sourcing in <a href=\"http:\/\/www.devx.com\/dotnet\/Article\/34845?pf=true\">an earlier article of mine<\/a>)<\/em><\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/852-ConnectionEditor_snapshot.jpg\" alt=\"852-ConnectionEditor_snapshot.jpg\" \/><br \/>\n <strong>Figure 4. Connection Editor &#8211; The <br \/>\n MultiConnectionStringManager user<br \/>\n control on this form manages a set<br \/>\n of database connections.<\/strong><\/p>\n<h2>A Control to Manage a Set of Connection Strings<\/h2>\n<p>The first component needed to supplement the QueryPicker is the <em>MultiConnectionStringManager<\/em>, a user control that lets you create, edit, store, import, and export a set of connection strings.<\/p>\n<p>The control (<strong>Figure 4<\/strong>) comprises the bulk of the Connection Editor form; only the OK and Cancel buttons on the form are supplemental. The drop-down selector at top lets you switch between connections and the DB Type selector conveniently supports the same set of database types we&#8217;ve been discussing: SQL Server, Oracle, MySQL, and ODBC.<\/p>\n<p>The <em>MultiConnectionStringManager<\/em> itself builds on the <em>ConnectionStringManager<\/em> user control, described in detail in a previous article &#8216;<a href=\"http:\/\/www.devx.com\/dotnet\/Article\/35374?pf=true\">.NET Building Blocks<\/a>&#8216; article of mine. Combining this connection editor with a <em>RichTextBox<\/em> for input and a <em>DataGridView<\/em> for output is enough to quickly build a working framework to use the QueryPicker. The <strong><em>SqlEditor<\/em><\/strong> provides just that.<\/p>\n<h1>A SQL Editor Control<\/h1>\n<p>The eponymous <em>SqlEditor<\/em> user control combines a <em>QueryPicker<\/em>, a <em>MultiConnectionStringManager<\/em>, a <em>DataGridView<\/em>, and a <em>RichTextBox<\/em> to provide an editor pane that supports multiple database-types right out of the box! You could literally drag this onto an otherwise empty form in the Visual Studio designer, then compile and go:<\/p>\n<div class=\"ILLUSTRATION\">\n<p class=\"ILLUSTRATION\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/852-sqlEditor_snapshot.gif\" alt=\"852-sqlEditor_snapshot.gif\" width=\"512\" height=\"512\" \/><\/p>\n<p class=\"CAPTION\">Figure 5. The SqlEditor User Control &#8211; This user control encapsulates a full-fledged single pane SQL editor complete with syntax highlighting, file loading\/saving, output grid formatting\/filtering, and more.<\/p>\n<\/div>\n<p>The <em>SqlEditor<\/em> control does not just use a plain vanilla <em>DataGridView<\/em> or <em>RichTextBox<\/em>, though. It includes fully customized versions: an <em>ExtendedDataGridView<\/em> and <em>ChameleonRichTextBox<\/em>, respectively. My <em>ExtendedDataGridView<\/em> is built upon a foundation provided by Chris McGrath&#8217;s <a href=\"http:\/\/www.codeproject.com\/KB\/grid\/ExtendedDataGridView.aspx\">Extending the DataGridView<\/a>. The <em>ChameleonRichTextBox<\/em> is built upon the work of two other open source components, the <em>SyntaxHighlightingTextBox<\/em> (from a developer known as &#8220;<a href=\"http:\/\/www.codeproject.com\/KB\/miscctrl\/FixingTheCode.aspx\">kabwla<\/a>&#8220;, who built his improvements upon the work of &#8220;<a href=\"http:\/\/www.codeproject.com\/KB\/edit\/SyntaxHighlighting.aspx\">uri guy<\/a>&#8220;) and Jim Blackler&#8217;s <a href=\"http:\/\/www.codeproject.com\/KB\/edit\/SearchableControls.aspx\"><em>SearchableRichTextBox<\/em><\/a>. Table 1 shows highlights of my controls&#8217; additional capabilities.<\/p>\n<table>\n<tbody>\n<tr>\n<th>\n<p>ExtendedDataGridView<\/p>\n<\/th>\n<th>\n<p>ChameleonRichTextBox<\/p>\n<\/th>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>\u2022 Arbitrary filtering<br \/>\n \u2022 Column width snap-to-data or snap-to-headers<br \/>\n \u2022 Row height adjustment<br \/>\n \u2022 Export to Excel<br \/>\n \u2022 Date format customization<br \/>\n \u2022 Display column datatype <br \/>\n \u2022 Hide\/show columns<br \/>\n \u2022 Searching<\/p>\n<\/td>\n<td valign=\"top\">\n<p>\u2022 Dialect-specific syntax highlighting<br \/>\n \u2022 SQL Server, Oracle, MySQL out-of-the-box; customizable to any<br \/>\n \u00a0 other<br \/>\n \u2022 Enable\/disable highlighting<br \/>\n \u2022 Automatic or on-demand highlighting<br \/>\n \u2022 Differentiates end-of-line and block comment highlighting<br \/>\n \u2022 Auto-convert keywords to uppercase if desired<br \/>\n \u2022 Differentiates groups of keyword highlighting<br \/>\n \u2022 Case-sensitive keyword recognition if desired<br \/>\n \u2022 Keyword completion<br \/>\n \u2022 Macro instantiation with placeholder highlighting and snap-to-<br \/>\n \u00a0\u00a0placeholders<br \/>\n \u2022 Comment\/uncomment region<br \/>\n \u2022 Search\/replace by text or regular expression<br \/>\n \u2022 Indent region left or right<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p class=\"CAPTION\">Table 1. ExtendedDataGridView and ChameleonRichTextBox Additional Features<\/p>\n<p>Here&#8217;s a visual overview of the principal controls from my open source library that I&#8217;ve used here:<\/p>\n<div class=\"ILLUSTRATION\">\n<p><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/852-sqlEditor.gif\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/852-sqlEditor_sml.jpg\" alt=\"852-sqlEditor_sml.jpg\" width=\"630\" height=\"359\" \/><\/a><\/p>\n<p class=\"CAPTION\">Figure 6. The SqlEditor Components &#8211; (click on the image for a closer look) The central portion shows the 4 key user controls used to provide a platform for the QueryPicker, while the side bars show the ample help and tip displays that come along for free.<\/p>\n<\/div>\n<p>Although the <em>SqlEditor<\/em> control provides its own rich features to expose the capabilities of the <em>ExtendedDataGridView<\/em> and the <em>ChameleonRichTextBox<\/em>, at its heart it is just the same six lines of code you saw earlier, loading a <em>DataGridView<\/em> from a database.<\/p>\n<h1>Exercising the Demo Applications<\/h1>\n<p>I have included two demo applications with this article to give you some hands-on practice with the <em>QueryPicker<\/em> control. I&#8217;ve included the Visual Studio solution with source code, as well as the compiled executables (in each project&#8217;s bin\/Debug directory) so you need only rename each name.executable file to name.exe, and you can run them right out of the box.<\/p>\n<p>The Basic QueryPicker Demo gives you the <em>QueryPicker<\/em> control distilled down to the simplest possible interface, with a couple on-screen reminders for what to do with it. The form (Figure 7) gives you a drop-down control to select the database type, a Launch button to open the <em>QueryPicker<\/em>, and a text box to contain the output when you return from the <em>QueryPicker<\/em> subform.<\/p>\n<div class=\"ILLUSTRATION\">\n<p class=\"ILLUSTRATION\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/852-basic_demo.gif\" alt=\"852-basic_demo.gif\" width=\"571\" height=\"450\" \/><\/p>\n<p class=\"CAPTION\">Figure 7. The Basic QueryPicker Demo<\/p>\n<\/div>\n<p>The Embedded QueryPicker Demo literally took seconds to create: I created a WinForm application and added the production-quality <em>SqlEditor<\/em> control to the toolbox by including my open-source DLL file, then dragged it onto the visual designer. The only other piece I added was a pop-up that gives a brief explanation on how to use it, in case you don&#8217;t have this article in front of you at the time.<\/p>\n<p>Here&#8217;s what to do:<strong> Alt-click<\/strong> the connection selector to open the connection editor (<strong>item 1 in Figure 8<\/strong>). To create a new connection, just type a descriptive name in the top box of the connection editor, enter the details of your connection, and test it with (unsurprisingly) the test button. Once you&#8217;re satisfied, close the connection editor to return to the main window and then select the connection you just defined. Access the QueryPicker via the button with the question mark (<strong>item 2 in the figure<\/strong>), drill down to select a query &#8211; the simplest is the Version query in the System category &#8211; and press <strong>Execute<\/strong> (or <strong>Shift-Execute<\/strong> if you want to see it) and the meta-query auto-executes.<\/p>\n<div class=\"ILLUSTRATION\">\n<p class=\"ILLUSTRATION\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/852-embedded_demo.gif\" alt=\"852-embedded_demo.gif\" width=\"562\" height=\"530\" \/><\/p>\n<p class=\"CAPTION\">Figure 8. The Embedded QueryPicker Demo<\/p>\n<\/div>\n<p>Finally, if you are one of those people who like to be able to understand the relationship of components you use, <strong>Figure 9<\/strong> shows the assemblies and their relationships brought together when you instantiate a <em>SqlEditor<\/em> control. The <em>QueryPicker<\/em> resides in the <em>CleanCode.DatabaseControls<\/em> assembly, highlighted in the figure.<\/p>\n<div class=\"ILLUSTRATION\">\n<p class=\"ILLUSTRATION\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/852-assemblies.gif\" alt=\"852-assemblies.gif\" width=\"622\" height=\"523\" \/><\/p>\n<p class=\"CAPTION\">Figure 9. Assembly relationships used by SqlEditor and QueryPicker<\/p>\n<\/div>\n<h1>Call to Action<\/h1>\n<p>Table 1 in<a href=\"http:\/\/www.simple-talk.com\/dotnet\/.net-tools\/a-unified-approach-to-multi-database-query-templates\/\"> part 1<\/a> of this article listed all the meta-queries I supplied with the QueryPicker at the time of this writing. The unfilled cells in the table are not indicative of unavailable, unfathomable, or otherwise undeterminable queries, but rather only that limited time has prevented me from tracking those down. If you can fill in any of those missing ones or, indeed, supply other new, interesting queries, please post a comment at the bottom of this article, so that others could benefit from your find. I will likely include your contribution (with your name attached to it!) in a future release of my code, so be sure to check my website for updates (this article coincides with release 0.9.29). If you missed out on the background to the QueryPicker Control, <a href=\"http:\/\/www.simple-talk.com\/dotnet\/.net-tools\/a-unified-approach-to-multi-database-query-templates\/\">you can read more about it here<\/a>. If you&#8217;ve keep up with my this far, then you might like to read more about <a href=\"http:\/\/www.simple-talk.com\/dotnet\/.net-framework\/using-three-flavors-of-linq-to-populate-a-treeview\/\">how I implemented some of the QueryPicker&#8217;s functionality<\/a>.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Having introduced his cross-platform Query Template solution, Michael now gives us the technical details on how to integrate his .NET controls into applications both simple and complex. With screenshots and code samples, this has everything you need to build your own powerful SQL editor or Query Template explorer.&hellip;<\/p>\n","protected":false},"author":221868,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143538],"tags":[4143,5053,4179,4150,5078,4316],"coauthors":[6802],"class_list":["post-718","post","type-post","status-publish","format-standard","hentry","category-dotnet-development","tag-net","tag-net-control","tag-source-control","tag-sql","tag-sql-editor","tag-windows-forms"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/718","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221868"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=718"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/718\/revisions"}],"predecessor-version":[{"id":74247,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/718\/revisions\/74247"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=718"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=718"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=718"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=718"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}