Getting always FALSE as a result of Table Exists method using C# SQLClient MSSQL

Question

I have the code like this for checking if database exists:

    private bool CheckDatabase(string databaseName)
    {
        var connString = "Server=localhost\\SQLEXPRESS;Integrated Security = SSPI; database = master";           
        string cmdText = "select count(*) from master.dbo.sysdatabases where name=\'" + databaseName + "\'";         
        using (var sqlConnection = new SqlConnection(connString))
        {
            using (var sqlCmd = new SqlCommand(cmdText, sqlConnection))
            {                    
                sqlCmd.Parameters.Add(@"database", System.Data.SqlDbType.NVarChar).Value = databaseName;                    
                sqlConnection.Open();                    
                return Convert.ToInt32(sqlCmd.ExecuteScalar()) == 1;
            }
        }            
    }

EDIT: I used the query from the comments but I am always getting false as a result:

public static bool CheckTableExists()
    {
        var connString = "Server=localhost\\SQLEXPRESS;Integrated Security = SSPI; database = master";
        string cmdText = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = \'" + "databaseName" + "\' AND TABLE_NAME = \'" + "testingTable" + "\'";
        using (var sqlConnection = new SqlConnection(connString))
        {
            using (var sqlCmd = new SqlCommand(cmdText, sqlConnection))
            {
                sqlCmd.Parameters.Add(@"database", System.Data.SqlDbType.NVarChar).Value = "databaseName";

                sqlConnection.Open();

                return Convert.ToInt32(sqlCmd.ExecuteScalar()) == 1;
            }
        }
    }

I cannot find the solution myself unless I will be able to put my hands on some slq learning resource. — positive perspective, May 17 '18 at 13:11
possible duplicate https://stackoverflow.com/questions/464474/check-if-a-sql-table-exists — Prasad Telkikar, May 17 '18 at 13:13
Possible duplicate of [Is there a C# API for sys.tables and sys.procedures?](https://stackoverflow.com/questions/25102044/is-there-a-c-sharp-api-for-sys-tables-and-sys-procedures) — mjwills, May 17 '18 at 13:13
the link you posted doesn't explain how to specify the database in which table exists(or not) — positive perspective, May 17 '18 at 13:14

score 1 · Answer 1 · answered May 17 '18 at 13:16

1

You can check for tables existing in SQL pretty easily

SELECT * FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'myDatabase' 
AND  TABLE_NAME = 'myTable'

answered May 17 '18 at 13:16

GiantJelly

298
2
15

excellent! Exactly what I was looking for. Can you direct me to the right place so I can learn building queries myself? – positive perspective May 17 '18 at 13:17
yes, I have no idea how to digest the thing. I'm totally new to it. – positive perspective May 17 '18 at 13:20
ah ok, w3schools is a great resource :D – GiantJelly May 17 '18 at 13:20
@GiantJelly I tried this query on my code but I get always false as a result and the code attempts to create a table of such name, giving me error that table exists. – positive perspective May 17 '18 at 14:57

score 0 · Answer 2 · answered May 17 '18 at 13:21

0

To get the list of databases (rather than tables): select name from sys.databases

answered May 17 '18 at 13:21

Avo Nappo

620
4
9

score 0 · Answer 3 · answered May 17 '18 at 13:34

Another way is using a try .. catch like below, make a query against the table and catch the SqlException like

    string cmdText = "select 1 from mytbl1";         
    using (var sqlConnection = new SqlConnection(connString))
    {
        using (var sqlCmd = new SqlCommand(cmdText, sqlConnection))
        {                    
            try
            {  
             sqlConnection.Open();                    
             return Convert.ToInt32(sqlCmd.ExecuteScalar()) == 1;
            }
            catch(SqlException ex)
            {
            }
        }
    }

Getting always FALSE as a result of Table Exists method using C# SQLClient MSSQL

3 Answers3