How to drop a table

Question

I am using following C# method to execute SQL queries:

public bool ExecuteQuery(String pQuery)
{
    SqlConnection con = new SqlConnection("MyConnectionString");
    con.Open();

    SqlTransaction trans = con.BeginTransaction(IsolationLevel.ReadCommitted);

    try
    {
        SqlCommand cmd = new SqlCommand(pQuery, con, trans);
        cmd.ExecuteNonQuery();
        trans.Commit();
        con.Close();
        trans.Dispose();
        return true;
    }
    catch (Exception exp)
    {
        trans.Rollback();
        con.Close();
        MessageBox.Show(exp.Message, "Error!!", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }

    return false;
}

When I pass this statement:

ExecuteQuery("DROP TABLE MyTable");

then the method returns true, which means it worked fine, but when I check SQL Server, myTable was not dropped. If I run the same statement in SQL Server Management Studio, MyTable is dropped...

Where am I wrong?

Answer 1

Before answering your question, some comments:

• Avoid coding such operations using query text, this is a high chance you can get problems with security. Better create stored procedure that executes table drop:

  create procedure sp_DropTable
  @tablename varchar(200)
  as
  BEGIN
      DECLARE @SQL VARCHAR(MAX);
      SET @SQL = 'IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N''' + @tableName + ''') AND type = (N''U'')) DROP TABLE [' + @tableName + ']'
  
      EXEC (@SQL);
     END
  GO
  

Then pass the stored procedure's name as parameter to your function. Now back to your error.

Table drop is not a transaction, but you try to execute it in transactional schema. This makes it fail. Try:

public bool ExecuteQuery(String pQuery)
{
    SqlConnection con = new SqlConnection("MyConnectionString");
    con.Open();

    try
    {
        SqlCommand cmd = new SqlCommand(pQuery, con);

        // if you pass just query text
        cmd.CommandType = CommandType.Text;

        // if you pass stored procedure name
        // cmd.CommandType = CommandType.StoredProcedure;   

        cmd.ExecuteNonQuery();

        con.Close();

        return true;
    }
    catch (Exception exp)
    {
        con.Close();
        MessageBox.Show(exp.Message, "Error!!", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }

    return false;
}