Accessing Databases

The built-in .NET support for database access is fully represented in QuickSharp. The SQL Manager allows ADO.NET database connections to be defined and maintained while the SQL Editor allows SQL scripts to be developed and tested. These support any database for which an ADO.NET provider is available and provide full connection management and query support. For SQL Server, SQLite and MySQL databases Code Assist is also available allowing the editor to present database entities such as tables and columns for inclusion in SQL files.

Managing connections

QuickSharp automatically detects the available ADO.NET data providers and allows database connections to be defined for any supported database. To create or edit a connection open the QuickSharp options editor and select SQL Manager to display the Connections page. Right-click to access the context menu or double-click an existing entry to open the connection editor.

screenshot

A connection defines the data provider and the connection string required to access a database. Use the editor to define these and provide a text description for the connection. Use the Test Connection button to check the connection and click OK to save the details and close the form.

SQL Manager

The SQL Manager appears in the main QuickSharp toolbar and is used to manage the currently active connection; this will be disabled until a connection has been selected. Next to the button a drop-down list shows all the available connections; this will be unavailable until at least one connection has been defined. From the list select the connection you wish to use; the database button will be enabled and the connection will be available for activation.

screenshot

In QuickSharp using a connection is a two-part process; it must first be selected and then activated before it can be used. A connection is activated using the database button. When a connection is activated QuickSharp tests the connection to see it is valid and if successful the connection will become active. Click the button a second time to deactivate the connection.

SQL Editor

The SQL Editor allows SQL scripts to be edited and tested against the currently active connection. Queries are run using the Tools menu Run Query option, F5 or the toolbar button. A query can only be run once a connection has been activated and the menu and button will be disabled until a connection is available.

screenshot

Using SQL Server

SQL scripts can include any number of SQL statements separated by a semi-colon; when run the result of each will appear in the output window as the script progresses. Where a script contains multiple statements it will be run as a single transaction; if any individual statement fails the entire script will be rolled back and any database changes undone. Where select statements return data each result set will be displayed as a data table in the grid view presented in the lower part of the SQL Editor window. The grid view can contain multiple tables; to switch between them use the grid context menu to select the table required. For each table the data and schema can be exported to XML using the menu options.

screenshot

Selecting a SQL Server connection as the active database enables Code Assist support for SQL queries.

Metadata

For SQL Server connections, making the connection active allows QuickSharp to gather metadata from SQL Server and store this for use by the Code Assist system. In the SQL editor, pressing Ctrl+Space will activate the Code Assist pop-up and the appropriate database entities will be presented for insertion into the SQL script.

Code Assist support is available for schema, table and column lookups allowing queries to be developed easily and quickly without having to refer directly to the database schema. Tables and columns can be accessed via their parent schema or directly via the default schema 'dbo'. Where tables have already been specified in 'from' or 'join' clauses the table columns will appear directly in the lookup window. Table aliases may also be used to access table columns.

Building a query

The simplest way to build a query is to start with the 'from' clause as in this example:

screenshot

Select the table first and then backtrack to the 'select' clause and hit Ctrl+Space:

screenshot

The columns from the table will be displayed in the list but since a column can also be qualified with a table name, the tables in the default schema are also listed along with any additional schema present. SQL Server columns can be specified using a multi-part name and the Code Assist pop-up allows these parts to be selected to build the full name.

A simpler approach is to specify a table alias and use this to present the column names:

screenshot

As always the best way to understand this is to experiment with an existing database.

Notes and limitations

Code Assist is only available when a connection is active and is based on a snapshot of the database obtained when the connection is activated. If database changes occur after the connection is activated the metadata must be refreshed by reactivating the connection; click the database toolbar button to deactivate and again to reactivate.

SQL Server Code Assist is only available for version 2005 and later. The default schema is assumed to be 'dbo' and entities belonging to this are presented unqualified for ease of use where multiple schemata are not in use. Where a user account has a different default database entities will need to be fully qualified with the appropriate schema.

Entities containing spaces using square bracket delimiters have limited support and may cause odd behaviour.

Using MySQL

By installing and enabling the Connector/NET ADO.NET data provider QuickSharp can be used with MySQL databases. This includes full support for SQL scripts and Code Assist for MySQL database entities.

Installing Connector/NET

The MySQL data provider can be downloaded from the MySQL website and installed globally using the supplied installer or locally by copying the provider assembly ('mysql.data.dll') to the QuickSharp installation directory.

For a local install the provider must be made visible to QuickSharp: open the application configuration file ('QuickSharp.exe.config') located in the QuickSharp installation directory and uncomment the section for the MySQL data provider.

To access a MySQL database, open the QuickSharp options editor and select the SQL Manager page, right-click the connections list to create a new connection. Select the MySQL provider from the drop-down list and enter a connection string something like this:

Server=127.0.0.1;User=root;Pwd=secret;Database=world;

Code Assist

Code Assist is available for MySQL database connections and is mostly the same as the SQL Server version. However, unlike SQL Server, the MySQL Code Assist provides access to the entire server and databases entities can be accessed using a three part name; database.table.column. If the connection string for the active SQL connection includes a database reference this will appear as the default database in the lookups and its table names can be used unqualified.

Using SQLite

QuickSharp includes an ADO.NET data provider for SQLite databases and is able to provide Code Assist and application connectivity 'out of the box'.

Getting connected

The bundled System.Data.SQLite data provider is installed in the QuickSharp home directory and is enabled via the application configuration file. To create a database connection select the SQL Manager page of the QuickSharp options editor and right-click the connections list to open the connection editor. Select the SQLite provider from the drop-down list and create a connection string something like this:

Data Source=filename

The file name can contain path information and be either absolute or relative. Take care with relative paths as the actual path will depend on the current workspace; if the workspace changes so too will the path to the database.

By default SQLite will create a database automatically as soon as a connection is established; this allows a new database to be created by simply clicking the Test Connection button once the connection string has been entered. If it doesn't already exist the database will be created (it will be necessary to refresh the workspace view to see any databases created there).

Automatic database creation can be disabled using a connection string like this:

Data Source=filename; FailIfMissing=true

SQLite queries are run exactly the same way as for SQL Server; see the 'Accessing Databases' page for more information.

Code Assist

SQLite queries can make use of Code Assist once a SQLite connection has been created and activated. Being file rather than server based SQLite does not support multiple database schema, instead only the current database is presented in Code Assist lookups. Attached databases are not supported as the database metadata is gathered when the connection is activated; any databases attached during the session will not appear in the Code Assist.

Database management

QuickSharp provides a simple database management facility through the 'sqlite3.exe' command-line utility which is included in the QuickSharp home directory. A document handler is provided allowing SQLite databases to be opened within QuickSharp; opening a database file (for example using the workspace) will run the management utility with the database file open for access.