Performing SQL Queries

Last Updated: February 2021

This tutorial teaches you how to use the SQL Query feature in the SnapDevelop IDE. The SQL Query tool in SnapDevelop provides you a straightforward way to interact with databases, such as viewing data from the database, drafting and executing SQL statements. For which types of database that SnapDevelop supports, refer to the Installation Guide.

Creating a Database Connection

To perform the various SQL Query operations, you need to establish a database connection first.

To create a database connection:

Select View on the top menu bar, and then select DB Server Explorer.
In the DB Server Explorer, click on the Connect to the database icon to open the Database Connection dialog.
In the the Database Connection dialog, click New to configure the database connection. If you have already created a database connection and selected the option Remember this connection for the connection, you can directly use the existing connection by selecting it from the list of Connection name, or click Edit to modify the settings. To rename an existing connection, type the new name in the Modify the connection name to field.
Select a data source from the Database provider list.
Configure the basic and advanced connection settings for the selected data source.

Refer to Appendix: Database Connection Settings for the settings to configure for each data source.
Click Test Connection to check the connection state.

If the test succeeds, you are ready to create a data model based on the underlying database or service.
Check the connection string in the Database Connection dialog to make sure the properties are configured as expected.

If you have successfully established a connection to the database, you are now ready to access the tables, views, stored procedures, functions, and triggers from the database in DB Server Explorer.

Note: If the database is connected via ODBC, SQL Query cannot get access to the stored procedures, functions, or triggers in the database.

Saving and Editing Connection String

The connection string is stored in the application settings file. If you want to modify the connection string, you can find and edit it in the application settings file.

To save a connection string directly into application settings:

In Solution Explorer, right-click on the project node and then select Add > New Item to bring up the New Item dialog box.
Select DataContext in the middle pane.
Specify a name for the DataContext.
In the Database Connection dialog box that appears, verify that the connection string is valid.
Click OK to complete the process.

After the process is complete, you can check the appsettings.json file in your project in Solution Explorer.

To change the connection string stored in application settings:

Open the appsettings.json file in Solution Explorer.
Locate the connection you want to change and modify the corresponding values in the connection.
Save the changes.

Managing Database Connections

After you have successfully connected to a database, you are able to manage the database connection by the right click menu. For example, you can refresh, delete, modify, or close the database connection.

Refresh

After you have successfully created a connection to a database, some modifications might be made to the database. In this case, you can select Refresh to update the database.

Delete

If you want to remove a database connection, you can right-click on the connected database and then select Delete.

Modify Connection

If you want to modify certain database connection properties, you can right-click on the connected database and then select Modify Connection, which leads you back to the Database Connection page.

Close Connection

Connections are a limited and relatively expensive resource. Therefore, you should always close the connection when you don't have to use it anymore so that it is returned to the connection pool.

Managing Database Objects

Database objects represent the logical structures of the database. After you have successfully connected to a database, you can manage the objects in the database. You can do this by right-clicking on the object and selecting one of the pop-up menu options:

Filter

If you want to search for particular items from a database object that contains too many items, you can use the search box at the top of DB Server Explorer to filter the items based on the supplied criteria.

Copy Schema/Item Name

Copies the name of the selected item in the folders so that you don't have to type every segment manually when you are writing your SQL statements.

Refresh

If some modifications have been made to the connected database, you can select Refresh for a particular folder so as to update the entire folder. In addition, you can select Refresh for a particular item in any folder so as to update the selected item.

Show Table Schema

Shows the collection of a table's relation schemas, which indicate the organization of data as a blueprint of how the table is constructed.

Note: If your database is connected via ODBC, SQL Query cannot show which column is the primary key for a table.

Show Table Data

Shows all data about the table you select.

Convert to C# Model

Converts one or multiple database tables to C# classes of SqlModelMapper type or standard C# model. Please note that, currently the table relationships (for example, master-slave) cannot be converted.

Querying

This section offers a walkthrough of how you can create and execute SQL queries.

Accessing the SQL Query editor

There are three possible ways to access the query editor:

Select Tools > SQL Query > New Query.
Select a table, view, stored procedure or function in the database treeview, and then select New Query from the right-click context menu.
Select a table, or view in the database treeview, and then select one of the three common SQL commands (New SQL - SELECT TOP 100, New SQL - SELECT ALL, and New SQL - SELECT BY Key) to automatically generate SQL statements.

New SQL - SELECT TOP 100

Automatically generates a SQL statement that selects the first 100 rows of data from the selected table. For example,

SELECT TOP 100 * FROM [Person].[ContactType];


Note: This feature is not available if the database is connected via ODBC.

**New SQL - SELECT ALL**

Automatically generates a SQL statement that selects all data from the selected item in the Table or View folder. For example, 

```sql
SELECT * FROM [Person].[ContactType];

New SQL - SELECT BY Key

Automatically generates a SQL statement that selects from the selected table a row uniquely identified by the primary key. For example,

SELECT * FROM [Person].[ContactType] WHERE ContactTypeID = 1;

Note: This feature is not available if the database is connected via ODBC.

Executing SQL queries

When you finish writing a SQL query, you can click the Execute icon to execute it.

Viewing query results

This section describes how you can select the format to view the SQL query execution results, split results panels, check the query message, etc.

Selecting the result display mode

You can choose to view the query results in one of the three different modes, grid, text, and file.

View Results as Grid

Displays the query results in grid format, which is the default format.
View Results as Text

Displays the query results in text format.
View Results as File

Displays query results in file format. If you choose this way to display your query results, you need to export the results to an external file.

Splitting the Result panel into two

If the SQL query contains multiple SQL statements, the option Split the Result panel into two (left and right) determines whether the returned results display horizontally, or vertically, side by side. As the screenshot below shows, if the option is enabled, the results display horizontally side by side.

Viewing Query Message

After the SQL statements are executed, a message displays in the Message tab, indicating the state of the query execution.

Showing Schema

You can enable this option to view the collection of the relation schemas for the table(s) involved in your SQL statements.

Note: This feature is supposed to provide the same function as Show Table Schema, however, it cannot work as well as in Show Table Schema yet. For example, the data type information of the columns may be missing when you show schema here.

Showing Execution Plan

An execution plan is generated as a consequence of the query optimizer's effort to calculate the most efficient way to implement a particular SQL query. If you enable this option, you will see how a query was executed or how a query will be executed.

Note: This feature is not available if the database is connected via ODBC.

Dealing with Query Results

If you have chosen to display your query results in grid format, you can select a table item and then select an action you want to perform from the right-click context menu.

Copy

Copies a table item so that you can paste it to the SQL Query editor or to an external file.
Copy Column Name(s)

Copies the column name of the table so that you can paste it to the SQL Query editor or to an external file.
Select All

Selects all table items.
Save Results As

Exports the query results to an external CSV file.

Appendix: Database Connection Settings

This appendix explains the various database connection settings required for different database providers.

SQL Server

Basic Properties

The following table lists the basic connection properties you need to configure for SQL Server.

Property	Description
Server name	Indicates the IP address of the server where the database you want to connect to is located.
Port	Indicates the port on a host bus adapter that offers the physical connection to a controller and is used for I/O operations.
Authentication	Indicates the method of authentication. There are four methods of authentication, which are: Windows authentication, SQL Server authentication, Active Directory Password authentication, and Active Directory Integrated authentication.
User name	Indicates the server login name.
Password	Indicates the server login password.
Connect to a database	Specifies the name of the database you want to connect to.

Advanced Properties

The following table lists the advanced properties you can configure for SQL Server.

Property	Sub-Property	Description
Advanced	MultipleActiveResultSets	When true, multiple result sets can be returned and read from one connection.
Connection	Port	The TCP port of the SQLServer server.
Initialization	Application Intent	Declares application workload type when connecting to the server.
Misc	Connection Name	Specifies the connection name.
	Database Provider	Specifies the database provider.
	Server Host	Specifies the server host.
Other	DelimitIdentifier	Encloses table and column names in quotes.
	Driver-Specific Parameters	You can customize the connection string. Each filed segment is separated by “;”.
	TrimSpaces	Trims trailing spaces in char columns.
Pool	Enlist	Sessions in the Environment of Component Service (MTS if Microsoft Windows NT is used) should be automatically enlisted in global transactions if necessary.
	Load Balance Timeout	The minimum amount of time (in seconds) that this connection survives in the pool until it is destroyed.
	Max Pool Size	The maximum number of connections allowed in the pool.
	Min Pool Size	The minimum number of connections allowed in the pool.
	Pool Blocking Period	Defines pause time behavior for connection pooling.
	Pooling	When true, the connection object is drawn from the appropriate pool, or if necessary, it is created and added to the appropriate pool.
Safety	Column Encryption Setting	Default column encryption settings for all commands on the connection.
	Encrypt	If true, and the server has a certificate installed, SQL Server will use SSL encryption for all data sent between the client and the server.
	Integrated Security	Whether the connection is a secure connection.
	Password	Indicates the password to use when connecting to the data source.
	TrustServerCertificate	When true (and encrypt=true), SQL Server uses SSL encryption for all data sent between the client and server without validating the server certificate.
	User ID	Indicates the user ID to use when connecting to the data source.
Source	Attach DB Filename	The name of the primary file, including the full path name, of an attachable database.
	Data Source	Indicates the data source address or name to connect to.
	Initial Catalog	The name of the initial directory or database in the data source.

Oracle

Basic Properties

The following table lists the basic connection properties you need to configure for Oracle.

Property	Description
Host	Indicates the server machine on which an Oracle database resides.
Port	Indicates the port on a host bus adapter that offers the physical connection to a controller and is used for I/O operations.
Protocol	Select a connection protocol, TCP or TCPS. Select TCPS if TLS is enabled for your Oracle database, otherwise select TCP.
Wallet Location	Browse to the folder where your wallet file is stored. This setting is visible only when you select the TCPS protocol.
Service name	Specifies that a database can register itself with the listener.
User name	Indicates the server login name.
Password	Indicates the server login password.

Advanced Properties

The following table lists the advanced connection properties you can configure for Oracle.

Property	Sub-property	Description
DataSource	Host	The host name or IP address of the Oracle server to connect to.
	Port	The TCP port of the Oracle server.
	Protocol	N/A
	Server	N/A
	Service Name	N/A
	Users	When you need to access objects from other users, you can add users here. Note that this data will not be saved, so you must edit this content each time you use the database connection. The format is as follows: user1, user2
	Wallet Location	Specifies the location of the wallet.
Initialization	Metadata Pooling	Caches metadata information.
	Statement Cache Size	Maximum number of SQL statements that can be cached.
Misc	Connection Name	Specifies the connection name.
	Database Provider	Specifies the database provider.
	Server Host	The host name or IP address of the Oracle server to connect to.
Other	DelimitIdentifier	Encloses table and column names in quotes.
	Driver-Specific Parameters	You can customize the connection string. Each field segment is separated by “;”.
	TrimSpaces	Trims trailing spaces in char columns.
Pooling	Connection Lifetime	Maximum lifetime (in seconds) of the connection.
	Connection Timeout	Maximum time (in seconds) to wait for a free connection from the pool.
	Enlist	Whether to enlist in an ambient TransactionScope.
	Incr Pool Size	Number of new connections to be created when all connections in the pool are in use.
	Max Pool Size	The maximum number of connections allowed in the pool.
	Min Pool Size	The minimum number of connections allowed in the pool.
	Pooling	The time to wait before closing unused connections in the pool if the count of all connections exceeds MinPoolSize.
	Statement Cache Purge	Statement cache purged when the connection goes back to the pool.
	Validation Connection	Validation of connections coming from the pool.
RAC	HA Events	Proactively removes connections from the pool when an Oracle RAC service, service member, or node goes down.
	Load Balancing	Balances work requests across Oracle RAC instances based on the load balancing advisory and service goal.
Safety	Password	Indicates the user ID to use when connecting to the data source.
	User ID	Indicates the user ID to use when connecting to the data source.
Security	Persist Security Info	Retrieval of the password in the connection string.
	Proxy Password	Password for the proxy user specified by Proxy User ID.
	Proxy User ID	User name of the proxy user.
Source	Data Source	Indicates the data source address or name to connect to.
	DBA Privilege	Administrative privileges: SYSDBA or SYSOPER.
	Initial Catalog	The name of the initial directory or database in the data source.
	Promotable Transaction	Indicates whether or not a transaction is local or distributed throughout its lifetime.
	Self Tuning	Enables or disables self-tuning for a connection.

MySQL

Basic Properties

The following table lists the basic connection properties you need to configure for MySQL.

General	Description
Server name	Indicates the server machine on which an MySQL database resides.
Port	Indicates the port on a host bus adapter that offers the physical connection to a controller and is used for I/O operations.
Protocol	Specifies the connection protocol.
User name	Indicates the server login name.
Password	Indicates the server login password.
Connect to a database	Specifies the name of a database you want to connect to in the MySQL data source.
SSL	Description
SSL mode	SSL properties for connection. Available options: Preferred or Prefered: Use SSL if the server supports it. None: Do not use SSL. Required: Always use SSL. Deny connection if the server does not support SSL. Does not validate CA or hostname. VerifyCA: Always use SSL. Validates the CA but tolerates hostname mismatch. VerifyFull: Always use SSL. Validates CA and hostname.
SSL private key	Name of the SSL key file in PEM format to use for establishing an encrypted connection.
SSL CA certificate	Path to a local file that contains a list of trusted TLS/SSL CAs.
SSL certificate	Name of the SSL certificate file in PEM format to use for establishing an encrypted connection.

Advanced Properties

The following table lists the advanced connection properties you can configure for MySQL.

Property	Sub-property	Description
Advanced	Allow User Variables	Should the provider expect user variables to appear in the SQL.
	Allow Zero DateTime	Should zero datetime be supported.
	Auto Enlist	Should the connection automatically enlist in the active connection, if there are any.
	Character Set	Character set this connection should use.
Authentication	Integrated Security	Use Windows authentication when connecting to the server.
Connection	Allow Batch	Allows execution of multiple SQL commands in a single statement.
	Allow Load Data Local Infile	Allows reading data from a text file.
	Connect Timeout	The time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error.
	Database	Database to use initially.
	Default Command Timeout	The default timeout that MySqlCommand objects will use unless changed.
	Logging	Enables output of diagnostic messages.
	Port	Port to use for TCP/IP connections.
Misc	Keep Alive	For TCP connections, the idle connection time (in seconds) before the first keepalive packet is sent. A value of 0 indicates that keepalive is not used.
	Server Host	The host name or IP address of the MySQL server to connect to.
Other	DelimitIdentifier	Encloses table and column names in quotes.
	Driver-Specific Parameters	You can customize the connection string. Each field segment is separated by “;”.
	TrimSpaces	Trims trailing spaces in char columns.
Pooling	Connection Lifetime	Maximum lifetime (in seconds) of the connection.
	Connection Reset	When true, indicates the connection state is reset when removed from the pool.
	Max Pool Size	The maximum number of connections allowed in the pool.
	Min Pool Size	The minimum number of connections allowed in the pool.
	Pooling	When true, the connection object is drawn from the appropriate pool, or if necessary, is created and added to the appropriate pool.
Safety	Password	Indicates the password to use when connecting to the data source.
	Persist Security Info	When false, sensitive information (e.g., password) is not returned as part of the connection if the connection is open or has ever been in an open state.
	SslCa	Path to a local file that contains a list of trusted TLS/SSL CAs.
	SslCert	Name of the SSL certificate file in PEM format to use for establishing an encrypted connection.
	SslKey	Name of the SSL key file in PEM format to use for establishing an encrypted connection.
	SslMode	SSL properties for connection.
	User ID	Indicates the user ID to use when connecting to the data source.
Source	Data Source	Indicates the data source address or name to connect to.
	Initial Catalog	The name of the initial directory or database in the data source.

PostgreSQL

Basic Properties

The following table lists the basic connection properties you need to configure for PostgreSQL.

General	Description
Host	Indicates the server machine on which a PostgreSQL database resides.
Port	Indicates the port on a host bus adapter that offers the physical connection to a controller and is used for I/O operations.
Protocol	Indicates the connection protocol.
Connect to a database	Indicates the name for the database you want to connect to.
User name	Indicates the server login name.
Password	Indicates the server login password.
SSL	Description
SSL mode	Available options: Disable: Do not use SSL. Prefer: Use SSL if the server supports it. Require: Always use SSL.
Client certificate	Specifies the path to the client's SSL certificate file.

Advanced Properties

The following table lists the advanced properties you can configure for PostgreSQL.

Property	Sub-Property	Description
Advanced	Auto Prepare Min Usages	The minimum number of usages a SQL statement is used before it is automatically prepared. This number defaults to 5.
	Keepalive	The number of seconds of connection inactivity before Npgsql sends a keepalive query.
	Load Table Composites	Load table composite type definitions, and not just free-standing composite types.
	Max Auto Prepare	The maximum number of SQL statements that can be automatically prepared at any given point. Beyond this number the least-recently-used statement will be recycled. Zero (the default) disables automatic preparation.
	No Reset On Close	If set to true, a pool connection's state won't be reset when it is closed (improves performance). Do not specify this unless you know what you are doing.
	Read Buffer Size	Determines the size of the internal buffer Npgsql uses when reading. Increasing may improve performance if transferring large values from the database.
	Socket Receive Buffer Size	Determines the size of socket receive buffer.
	Socket Send Buffer Size	Determines the size of socket send buffer.
	TCP Keepalive	Whether to use TCP keepalive with system defaults if overrides isn't specified.
	TCP Keepalive Interval	The interval, in milliseconds, between when successive keep-alive packets are sent if no acknowledgement is received.
	TCP Keepalive Time	The number of milliseconds of connection inactivity before a TCP keepalive query is sent.
	Use Perf Counters	Writes connection performance information to performance counters.
	Write Buffer Size	Determines the size of the internal buffer Npgsql uses when writing. Increasing may improve performance if transferring large values to the database.
Compatibility	Convert Infinity Date Time	Makes MaxValue and MinValue timestamps and dates readable as infinity and negative infinity.
Connection	Application Name	The optional application name parameter to be sent to the backend during connection initiation.
	Client Encoding	Gets or sets the client encoding parameter.
	Encoding	Gets or sets the .NET encoding that will be used to encode/decode PostgreSQL string data.
	Enlist	Whether to enlist in an ambient TransactionScope.
	Host	The host name or IP address of the PostgreSQL server to connect to.
	Passfile	Path to a PostgreSQL password file (PGPASSFILE), from which the password would be taken.
	Port	The TCP port of the PostgreSQL server.
	Search Path	Gets or sets the schema search path.
	Time Zone	Gets or sets the PostgreSQL session time zone, in Olson/IANA database format.
	User Name	The user name to connect with. Not required if using IntegratedSecurity.
Entity Configurations	Entity Admin Database	N/A
	Entity Template Database	The database template to specify when creating a database in Entity Configurations. If not specified, PostgreSQL defaults to \"template1\".
Misc	Connection Name	Specifies the connection name.
	Database Provider	Specifies the database provider.
	Server Host	The host name or IP address of the ODBC server to connect to.
Other	DelimitIdentifier	Encloses table and column names in quotes.
	Driver-Specific Parameters	You can customize the connection string. Each field segment is separated by “;”.
	TrimSpaces	Trims trailing spaces in char columns.
Pooling	Connection Idle Lifetime	The time to wait before closing unused connections in the pool if the count of all connections exceeds MinPoolSize.
	Connection Pruning Interval	How many seconds the pool waits before attempting to prune idle connections that are beyond idle lifetime.
	Max Pool Size	The maximum connection pool size.
	Min Pool Size	The minimum connection pool size.
	Pooling	Specifies whether connection pooling should be used.
Safety	Password	Indicates the password to use when connecting to the data source.
	User ID	Indicates the user ID to use when connecting to the data source.
Security	Certificate	Location of a client certificate to be sent to the server.
	Check Certificate Revocation	Specifies whether to check the certificate revocation list during authentication.
	Include Realm	Specifies whether to use the Kerberos for authentication.
	Integrated Security	Specifies whether to use Windows integrated security to log in.
	Persist Security Info	Gets or sets a Boolean value that indicates if security-sensitive information, such as the password, is not returned as part of the connection if the connection is open and has ever been in an open state.
	SSL Mode	Specifies whether SSL is required, disabled, or preferred, depending on server support.
	Trust Server Certificate	Specifies whether to trust the server certificate without validating it.
	Use SSL Stream	Npgsql uses its own internal implementation of TLS/SSL. Turn this on to use .NET SslStream instead.
Source	Data Source	Indicates the data source address or name to connect to.
	Initial Catalog	The name of the initial directory or database in the data source.
Timeouts	Command Timeout	The time to wait (in seconds) while trying to execute a command before terminating the attempt and generating an error. Set to zero for infinity.
	Internal Command Timeout	The time to wait (in seconds) while trying to execute a command before terminating the attempt and generating an error. -1 uses CommandTimeout while 0 means no timeout.
	Timeout	The time to wait (in seconds) while trying to establish a connection before terminating the attempt and generating an error.

ODBC

Basic Properties

The following table lists the basic connection properties you need to configure for ODBC.

Property	Description
Use user or system data source name	Whether to use the user or system data source name.
Use connection string	Whether to use the connection string.
User name	Indicates the server login name.
Password	Indicates the server login password.

Advanced Properties

The following table lists the advanced properties you can configure for ODBC.

Property	Sub-Property	Description
Misc	Connection Name	Specifies the connection name.
	Server Host	The host name or IP address of the ODBC server to connect to.
Other	Connection String	The connection string to use when connecting to the data source.
	DelimitIdentifier	Encloses table and column names in quotes.
	Driver-Specific Parameters	You can customize the connection string. Each field segment is separated by “;”.
	TrimSpaces	Trims trailing spaces in char columns.
Safety	Password	Indicates the password to use when connecting to the data source.
	User ID	Indicates the user ID to use when connecting to the data source.
Source	Data Source	Indicates the data source address or name to connect to.
	Driver	The name of the ODBC driver to use when connecting to the data source.
	DSN	The DSN to use when connecting to the data source.
	Initial Catalog	The name of the initial directory or database in the data source.

SQLite

Basic Property

If you connect to SQLite database, you only need to specify the data source.

Advanced Properties

The following table lists the advanced properties you can configure for SQLite.

Property	Sub-Property	Description
Misc	Connection Name	Specifies the connection name.
	Database Provider	Specifies the database provider.
	Server Host	The host name or IP address of the SQLite server to connect to.
Other	DelimitIdentifier	Encloses table and column names in quotes.
	Driver-Specific Parameters	You can customize the connection string. Each field segment is separated by “;”.
	TrimSpaces	Trims trailing spaces in char columns.
Safety	Password	Indicates the password to use when connecting to the data source.
	User ID	Indicates the user ID to use when connecting to the data source.
Source	Data Source	Indicates the data source address or name to connect to.
	Initial Catalog	The name of the initial directory or database in the data source.