Check if Database Exists Before Creating

Question

This seems pretty trivial, but it is now frustrating me.

I am using C# with SQL Server 2005 Express.

I am using the following code. I want to check if a database exists before creating it. However, the integer returned is -1 and this is how MSDN defines what ExecuteNonQuery() will return as well. Right now, the database does exist but it still returns -1. Having said that, how can I make this work to get the desired result?

private static void checkInventoryDatabaseExists(ref SqlConnection tmpConn, ref bool databaseExists)
{
     string sqlCreateDBQuery;
      try
      {
                tmpConn = new SqlConnection("server=(local)\\SQLEXPRESS;Trusted_Connection=yes");

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

                using (tmpConn)
                {
                    tmpConn.Open();
                    tmpConn.ChangeDatabase("master");

                    using (SqlCommand sqlCmd = new SqlCommand(sqlCreateDBQuery, tmpConn))
                    {
                        int exists = sqlCmd.ExecuteNonQuery();

                        if (exists <= 0)
                            databaseExists = false;
                        else
                            databaseExists = true;
                    }
                }
            }
            catch (Exception ex) { }

}

Answer 1

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?

Answer 2

You can't use ExecuteNonQuery because it will always return -1 for SELECT, as the MSND 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')

Answer 3

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();
                int databaseID = (int)sqlCmd.ExecuteScalar();    
                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).