Check If Table Exists Using Command

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() { conn

Solution 1:

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;
intexists= (int)cmd.ExecuteScalar();
if(exists==1)
    //Tableexists

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.

Solution 2:

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

Solution 3:

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.

Solution 4:

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

publicvoidcheckTableExists()
    {
        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");
        }
    }