Querying master.sys.databases view in SQL Server 2008 R2 from c# is not working

Question

I'm using the code below to find if a database exists but the ExecuteNonQuery always returns -1.

I've seen the master.sys.databases view and it has the database POS

SqlConnection tmpConn = new SqlConnection("Data Source=localhost\\SQLEXPRESS;Initial Catalog=master;Integrated Security=True");

sqlCheckDBQuery = "SELECT * FROM master.sys.databases where name = \'aspnetdb\'";

using (tmpConn)
{
    try
    {
        tmpConn.Open();
        tmpConn.ChangeDatabase("master");
    }
    catch (Exception)
    {
        MessageBox.Show("SQLServer Express Database is either not installed or not running!", "Database Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
        Application.Exit();
    }

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

        if (exists <= 0)
            databaseExists = false;
        else
            databaseExists = true;
    }
}

Answer 1

in this particular instance you can use a scalar query instead of a data reader.

sqlCheckDBQuery = "SELECT count(1) FROM master.sys.databases where name = 'aspnetdb'";
var count = (int)sqlCmd.ExecuteScalar();
databaseExists = count > 1;

Answer 2

ExecuteNonQuery() is the wrong method to use. Use ExecuteReader() instead: e.g.:

var reader = command.ExecuteReader();
if (reader.Read())
    ....

Answer 3

You should use ExecuteScalar() and change the query to run a COUNT(*).

ExecuteScalar will bring back the rows affected.

ExecuteNonQuery() will bring back -1 for SELECT's.

Answer 4

sqlCheckDBQuery = "SELECT * FROM master.sys.databases where name = \'aspnetdb\'";

Why are you using backslashes here? The name of the database is aspnetdb so this should be:

sqlCheckDBQuery = "SELECT * FROM master.sys.databases where name = 'aspnetdb'";

Also as has been mentioned in other answers you cannot use ExecuteNonQuery() here since that will return always -1 for select statements.