CODEWITHSUNDEEP
c#mysql
JAN
JAN

Reputation: 21865

Drop a table if it exists in C#?

I want to drop a table only if it exists , in C# with MySql .

Consider the following code: 

namespace CSharpMySqlSample
{
   class Example2
   {
      static void Main()
      {
         String str = @"server=localhost; database=sakila; uid=root;                password=root;";
         MySqlConnection con = null;
         try
         {
            con = new MySqlConnection(str);
            con.Open(); //open the connection        
            String cmdText = @"drop table `sakila`.`testable` if exists"; // this one drops a table 
            MySqlCommand cmd = new MySqlCommand(cmdText, con);
            cmd.Prepare();
            cmd.ExecuteNonQuery(); //execute the mysql command
         }
         catch (MySqlException err)
         {
            String outp = err.ToString();
            Console.WriteLine("Error: " + err.ToString());
         }
         finally
         {
            if (con != null)
            {
               con.Close(); //close the connection
            }
         } //remember to close the connection after accessing the database
      }
   }
}

It produced :

"MySql.Data.MySqlClient.MySqlException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if exists' at line 1\r\n at MySql.Data.MySqlClient.MySqlStream.ReadPacket()\r\n at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)\r\n at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)\r\n at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)\r\n at MySql.Data.MySqlClient.MySqlDataReader.NextResult()\r\n at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)\r\n at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()\r\n at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()\r\n at CSharpMySqlSample.Example2.Main()

So what's wrong with the query ?

Upvotes: 3

Views: 12362

Answers (4)

gemGreg
gemGreg

Reputation: 194

just type:

String cmdText = @"drop table `sakila`.`testable`";

without "if exists"

and don't add anything in catch so you will have table deleted or not depends on if it exists or not without any errors :)

Upvotes: 1

Jeff B
Jeff B

Reputation: 8982

if exists needs to go before the table name. Read the docs....

String cmdText = @"drop table if exists 'sakila'.'testable'";

Upvotes: 2

Mad Dog Tannen
Mad Dog Tannen

Reputation: 7244

Try this:

DROP TABLE IF EXISTS sakila.testtable;

Upvotes: 6

HaukurHaf
HaukurHaf

Reputation: 13816

Try this instead:

String cmdText = @"IF OBJECT_ID('sakila'.'testable', 'U') IS NOT NULL DROP TABLE 'sakila'.'testable'";

Also make sure that the database user which your program runs under has the neccessary permission to drop tables, but you'll see that right away when you try to run this :-)

Upvotes: 2

Related Questions