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;
    }
}

score 4 · Accepted Answer · answered Jan 12 '12 at 13:55

4

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;

answered Jan 12 '12 at 13:55

Jason Meckley

7,589
1
24
45

that worked like a charm but I wonder why the SqlDataReader was not returning any row (I tried it with ExecuteReader) – Uzair Farooq Jan 12 '12 at 14:58

score 1 · Answer 2 · edited Jan 12 '12 at 14:37

1

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.

edited Jan 12 '12 at 14:37

marc_s

732,580
175
1,330
1,459

answered Jan 12 '12 at 13:54

Paul

3,072
6
37
58

1

+1 `ExecuteScalar()` is the way to go here. I completely forget about it. – Yuck Jan 12 '12 at 13:58

score 0 · Answer 3 · answered Jan 12 '12 at 13:51

0

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

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

answered Jan 12 '12 at 13:51

Ɖiamond ǤeezeƦ

3,223
3
28
40

but is's still returning me 0 rows, it should return 1 row – Uzair Farooq Jan 12 '12 at 13:56
1

@UzairFarooq remove the backslashes from "SELECT * FROM master.sys.databases where name = \'aspnetdb\'" so it reads "SELECT * FROM master.sys.databases where name = 'aspnetdb'" – Ɖiamond ǤeezeƦ Jan 12 '12 at 14:01

score 0 · Answer 4 · answered Jan 12 '12 at 13:54

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.

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

4 Answers4