Check if Database Exists Before Creating

Question

As of SQL Server 2005, the old-style sysobjects and sysdatabases and those catalog views have been deprecated. Do this instead - use the sys. schema - views like sys.databases

private static bool CheckDatabaseExists(SqlConnection tmpConn, string databaseName)
{
    string sqlCreateDBQuery;
    bool result = false;

    try
    {
        tmpConn = new SqlConnection("server=(local)\\SQLEXPRESS;Trusted_Connection=yes");

        sqlCreateDBQuery = string.Format("SELECT database_id FROM sys.databases WHERE Name 
        = '{0}'", databaseName);

        using (tmpConn)
        {
            using (SqlCommand sqlCmd = new SqlCommand(sqlCreateDBQuery, tmpConn))
            {
                tmpConn.Open();

                object resultObj = sqlCmd.ExecuteScalar();

                int databaseID = 0;    

                if (resultObj != null)
                {
                    int.TryParse(resultObj.ToString(), out databaseID);
                }

                tmpConn.Close();

                result = (databaseID > 0);
            }
        }
    } 
    catch (Exception ex)
    { 
        result = false;
    }

    return result;
}

This will work with any database name you pass in as a parameter, and it will return a bool true = database exists, false = database does not exist (or error happened).

Reading this a few years on and there's a cleaner way of expressing this:

public static bool CheckDatabaseExists(string connectionString, string databaseName)
{
      using (var connection = new SqlConnection(connectionString))
      {
           using (var command = new SqlCommand($"SELECT db_id('{databaseName}')", connection))
           {
                connection.Open();
                return (command.ExecuteScalar() != DBNull.Value);
           }
      }
}

shouldn't this

"SELECT * FROM master.dbo.sysdatabases where name = \'INVENTORY\'"

be this?

"SELECT * FROM master.dbo.sysdatabases where name = 'INVENTORY'"

Also According to MSDN

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

You are doing a SELECT not an DML statement. Why don't you use a ExecuteReader method instead?

An alternative to querying the system views is to use the function db_id which returns the Id of the database if it exists, otherwise null. Example T-SQL below:

if (db_id('INVENTORY') is null)
begin
    return 0
end
else
begin
    return 1
end

You can't use ExecuteNonQuery because it will always return -1 for SELECT, as the MSDN link shows.

You'll have to use process a resultset eg SELECT DB_ID('INVENTORY') AS DatabaseID or use a variable/parameter: SELECT @DatabaseID = DB_ID('INVENTORY')

Took Stephen Lloyd's code and added some async and sql injection mitigation.

public static async Task<bool> TestDatabase(string connectionString, string databaseName)
{
   using (var connection = new SqlConnection(connectionString))
   using (var command = new SqlCommand("SELECT db_id(@databaseName)", connection))
   {
       command.Parameters.Add(new SqlParameter("databaseName", databaseName));

       connection.Open();

       return (await command.ExecuteScalarAsync() != DBNull.Value);
   }
}