What's the Best Way to Test SQL Server Connection Programmatically

What's the best way to test SQL Server connection programmatically?

Execute SELECT 1 and check if ExecuteScalar returns 1.

Most efficient way to test SQL connection string availibility

You're using connection.Open = true as if it were a property.

It's a method: connection.Open()

Use the ConnectionState enum to determine if the connection is open or not, eg:

connection.State == ConnectionState.Open

Efficient SQL test query or validation query that will work across all (or most) databases

The jOOQ manual's section about the DUAL table lists the following for jOOQ's select(inline(1)) query:

-- Access
SELECT 1 FROM (SELECT count(*) dual FROM MSysResources) AS dual

-- BigQuery, CockroachDB, Exasol, H2, Ignite, MariaDB, MySQL, PostgreSQL, 
-- Redshift, Snowflake, SQLite, SQL Server, Sybase ASE, Vertica
SELECT 1

-- MemSQL, Oracle
SELECT 1 FROM DUAL

-- CUBRID
SELECT 1 FROM db_root

-- Db2
SELECT 1 FROM SYSIBM.DUAL

-- Derby
SELECT 1 FROM SYSIBM.SYSDUMMY1

-- Firebird
SELECT 1 FROM RDB$DATABASE

-- HANA, Sybase SQL Anywhere
SELECT 1 FROM SYS.DUMMY

-- HSQLDB
SELECT 1 FROM (VALUES(1)) AS dual(dual)

-- Informix
SELECT 1 FROM (SELECT 1 AS dual FROM systables WHERE (tabid = 1)) AS dual

-- Ingres, Teradata
SELECT 1 FROM (SELECT 1 AS "dual") AS "dual"

Checked If there is Connection to SQL Server or not in WPF

You can simply check perform a check whether the client can open a connection.

Code lifted from this answer.

/// <summary>
/// Test that the server is connected
/// </summary>
/// <param name="connectionString">The connection string</param>
/// <returns>true if the connection is opened</returns>
private static bool IsServerConnected(string connectionString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        try
        {
            connection.Open();
            return true;
        }
        catch (SqlException)
        {
            return false;
        }
        finally
        {
            // not really necessary
            connection.Close();
        }
    }
}

Is it possible to connect to SQL Server without specifying a database?

In brief: no, you cannot do that. You might be able to leave out the database from the connection string, but in that case, that connection will be made to the configured default database of the login that's connecting to SQL Server (and that default database must exist at the time the connection is made)

If you want to have this scenario, you need to

1. first connect to your instance and database master and create your new testdb (or whatever it's called)

2. disconnect

3. in your tests, connect to the instance and the testdb database

Better yet: use a mocking framework of some sort so you don't even need an actual database in your testing scenario!

How can I programmatically test if SQL Server supports 'AT TIME ZONE'?

AT TIME ZONE is a new feature available in SQL Server 2016 (starting with CTP 3.1) and in the latest version of Azure SQL Database (V12).
It is also available in all on-prem editions of SQL2016 and in all service tiers of Azure SQL database.
Having said that, the best way to determine if AT TIME ZONE is supported is to check server version by running the following query

SELECT SERVERPROPERTY ('Edition') as Edition
       SERVERPROPERTY('ProductVersion')as VersionNumber

If Edition = 'SQL Azure' than VersionNumber has to be >= '12'

If Edition != 'SQL Azure' than Version Number has to be >= '13.0.800.000' (CTP 3.1)

For more details on SERVER property check out MSDN: https://msdn.microsoft.com/en-in/library/ms174396.aspx

Thanks,
Borko Novakovic (MSFT)

vb.net: Checking SQL Server status

You can instantiate a timer in your splash form that checks if it can log into the database every one second (or whatever interval you like). I'd invoke the splash form as modal so the calling app can't continue until the splash form has detected the connection and closed itself.

At the very least you need the server name to check the connection for. If it is using a named instance then the server name should also include the instance name in the format "myserver\myinstance".

I've encapsulated the connection checking logic in the 3 overloaded functions IsConnected. You can use these functions in your splash form to check connection from the timer tick. (Each depends on the next). You can use whichever function overload is suitable based on the input items you have available.

For the first overload, if the app is running under a Windows security context that can connect to the db server then you don't need to provide the username and password (pass as empty string), otherwise you need to provide those credentials needed to login to the db server. Or you can provide your own connection string or connection object for the other overloads.

(code within the splash form)...

Private Sub Timer1_Tick(sender As Object, e As System.EventArgs) Handles Timer1.Tick
    If Me.IsConnected("(local)\SQL2008R2", "", "") Then Me.Close()
End Sub

Public Function IsConnected(ServerName As String, UserID As String, Password As String) As Boolean
    Dim connStr As String = String.Format("Data Source={0}", ServerName)
    If Not String.IsNullOrEmpty(UserID) Then
        connStr &= String.Format(";User ID={0};Password={1}", UserID, Password)
    Else
        connStr &= ";Integrated Security=True"
    End If
    Return IsConnected(connStr)
End Function

Public Function IsConnected(Connection As String) As Boolean
    Static conn As SqlConnection
    If conn Is Nothing Then
        conn = New SqlConnection(Connection)
        conn.Open()
    End If
    Return IsConnected(conn)
End Function

Public Function IsConnected(ByRef Conn As SqlConnection) As Boolean
    If Conn IsNot Nothing Then Return (Conn.State = ConnectionState.Open)
    Return False
End Function

I'd invoke the splash form from the main app as a modal dialog, as such, so the app is blocked until the connection is detected.

(from the calling app form...)

        frm_Splash.ShowDialog()

SQL Server for testing on the web

You could use SQL Fiddle and run your queries in SQL Server 2008 R2 or 2012 mode:

http://sqlfiddle.com/

---

Related Topics