CODEWITHSUNDEEP
c#sqlsql-serversqlclient
positive perspective
positive perspective

Reputation: 349

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

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

Upvotes: 0

Views: 100

Answers (3)

Rahul
Rahul

Reputation: 77846

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)
            {
            }
        }
    }

Upvotes: 0

Avo Nappo
Avo Nappo

Reputation: 630

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

Upvotes: 0

GiantJelly
GiantJelly

Reputation: 305

You can check for tables existing in SQL pretty easily

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

Upvotes: 1

Related Questions