Check if table exists using command

Question

I'm trying to write a method to check if a table exists. I am trying to use the using statement to keep it consistent through my database.

public void checkTableExists()
{
    connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\keith_000\Documents\ZuriRubberDressDB.mdf;Integrated Security=True;Connect Timeout=30";

    string tblnm = "BasicHours";
    string str = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = " + tblnm + ");";

    SqlDataReader myReader = null;
    int count = 0;

    try
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(str, connection))
            {
                connection.Open();

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    MessageBox.Show("The count is " + count);

                    myReader = command.ExecuteReader();

                    while (myReader.Read())
                    {
                        count++;
                    }

                    myReader.Close();

                    MessageBox.Show("Table Exists!");
                    MessageBox.Show("The count is " + count);
                }

                connection.Close();
            }
        }
    }
    catch (SqlException ex)
    {
        MessageBox.Show("Sql issue");
    }
    catch (Exception ex)
    {
        MessageBox.Show("Major issue");
    }

    if (count > 0)
    {
        MessageBox.Show("Table exists");
    }
    else
    {
        MessageBox.Show("Table doesn't exists");
    }
}

It throws an exception when it hits the try block. It catches in the SqlException block.

This is the point where I am learning to interact with databases again. The solution would be good, but more importantly, a brief explanation of where I have need to learn how to improve my code.

Thanks

Keith

TABLE_NAME is a string field. You need single quotes around your variable, but better use a parameter — Steve, Mar 17 '17 at 13:56
Please don't concatenate SQL queries like that. (tblnm = "BasicHours; TRUNCATE TABLE [BasicHours];" Use Parameterised SqlCommand objects instead. — WraithNath, Mar 17 '17 at 13:58

score 10 · Accepted Answer · edited May 23 '17 at 11:53

Your code fails because when you write directly a query searching for a string value then this value should be enclosed in single quotes like 'BasicHours'.

However there are some improvements to apply to your actual code.
First, you can use a simplified sql command.
Second, you use parameters instead of string concatenations.

SqlCommand cmd = new SqlCommand(@"IF EXISTS(
  SELECT 1 FROM INFORMATION_SCHEMA.TABLES 
  WHERE TABLE_NAME = @table) 
  SELECT 1 ELSE SELECT 0", connection);

cmd.Parameters.Add("@table", SqlDbType.NVarChar).Value = tblName;
int exists = (int)cmd.ExecuteScalar();
if(exists == 1)
    // Table exists

This command text don't require you to use an SqlDataReader because the query returns just one row with one 'column' and the value of this single cell is either 1 or 0.
A lot less overhead.

A part from this, it is of uttermost importance, that you never build sql queries concatenating strings. This method is well know to cause problems. The worse is called SQL Injection and could potentially destroy your database or reveal confidential information to hackers. The minor ones are crashes when the string concatenated contains single quotes. Use always a parameterized query.

Ashkan Mobayen Khiabani · Answer 2 · 2017-03-17T14:03:52.387

1

I have used the following code in my project and worked for me:

 try
 {
     using (con = new SqlConnection(Constr);)
        {
            con.Open();
            string query = $"IF EXISTS (SELECT * FROM sys.tables WHERE name = '{tableName}') SELECT 1 ELSE Select 0;"
             Exists = int.Parse(sqlQuery.ExecuteScalar().ToString())==1;
             con.Close();
         }

  }
  catch{}

edited Mar 17 '17 at 14:03

answered Mar 17 '17 at 13:57

Ashkan Mobayen Khiabani

33,575
33
102
171

score 0 · Answer 3 · answered Mar 17 '17 at 14:01

0

The problem could be the line: string tblnm = "BasicHours";. You table name is a string and should be apostrophed, try this: string tblnm = "'BasicHours'";

Inside catch blocks you could also log exception messages and details.

answered Mar 17 '17 at 14:01

Alexander Powolozki

637
4
12

score 0 · Answer 4 · answered Mar 17 '17 at 19:21

Thanks for the help on this issue. This is the solution that I'm implemnenting.

    public void checkTableExists()
    {
        connectionString = @" 
        Data Source=(LocalDB)\MSSQLLocalDB;
        AttachDbFilename=C:\Users\keith_000\Documents\ZuriRubberDressDB.mdf;
        Integrated Security=True;
        Connect Timeout=30";

        string tblName = @"BasicHours";
        string str = @"IF EXISTS(
                SELECT 1 FROM INFORMATION_SCHEMA.TABLES 
                WHERE TABLE_NAME = @table) 
                SELECT 1 ELSE SELECT 0";

        try
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(str, connection))
                {
                    connection.Open();
                    SqlCommand cmd = new SqlCommand(str, connection);

                    cmd.Parameters.Add("@table", SqlDbType.NVarChar).Value = tblName;
                    int exists = (int)cmd.ExecuteScalar();
                    if (exists == 1)
                    {
                        MessageBox.Show("Table exists");
                    }
                    else
                    {
                        MessageBox.Show("Table doesn't exists");
                    }
                    connection.Close();
                }
            }
        }
        catch (SqlException ex)
        {
            MessageBox.Show("Sql issue");
        }
        catch (Exception ex)
        {
            MessageBox.Show("Major issue");
        }
    }

Check if table exists using command

4 Answers4

Linked