CODEWITHSUNDEEP
c#sql-server
user3569147
user3569147

Reputation: 102

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

Upvotes: 0

Views: 5986

Answers (4)

user3569147
user3569147

Reputation: 102

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

Upvotes: 0

Steve
Steve

Reputation: 216243

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.

Upvotes: 10

Ashkan Mobayen Khiabani
Ashkan Mobayen Khiabani

Reputation: 34152

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

Upvotes: 1

Alexander Powolozki
Alexander Powolozki

Reputation: 665

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.

Upvotes: 0

Related Questions