-1

I have found this question answered but not specifically for C#. The answer seems to be to execute the following statements tailored to my specific database. However, I can't figure out how to use these statements in C#.

I would add that in my program the prefix will be a variable, not a set prefix every time. My schema name is lifeskills1415.

Found this answer:

Here is a query to drop all tables that start with my_prefix in the current database:

SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(CONCAT(table_schema,'.',table_name)),';') FROM information_schema.tables WHERE table_schema=database() AND table_name like 'my_predix%';

Next thing to do is store the result of it in

SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(CONCAT(table_schema,'.',table_name)),';') INTO @dropcmd FROM information_schema.tables WHERE table_schema=database() AND table_name like 'my_prefix%';

Last thing is to execute the dynamic SQL using these three(3) commands:

PREPARE s1 FROM @dropcmd; EXECUTE s1; DEALLOCATE PREPARE s1;

  • So what's your question? If you intend to answer your own question (which is allowed, encouraged even!), you should submit it as a separate question and answer. – Colin 't Hart Aug 3 '14 at 7:58
  • My question is how would I go about making those statements work with c#? These statements are designed to be put directly into the mySQL console. But I need to execute them from my C# program and I am having trouble building and executing the commands correctly. I know how to connect to the mySQL database, and I done several CREATE TABLE, SELECT, INSERT and UPDATE commands successfully so I know the basics, but I don't know how to build this type of command in C#. – MichaelH1985 Aug 3 '14 at 8:06
0

Please note that using the mysql syntax for prepared statements to generate dynamic SQL is an ugly hack and should not be recommended.

Using the language/connector commands, as you probably want to do, is usually the best option for maintainability, unless you have an extremely large number of tables to be dropped.

On the manual you have an example of how to use prepared statements with the .net connector (the second one is the C# version). You can use those or MySQLCommands, in this case it doesn't matter much (more on this below):

  • Execute your select (MySqlCommand) without selecting "into a variable". You will get one column, one row because of the concat. You can also get rid of that and you will get on column, several rows, which is probably cleaner in case you get no results.
  • Get the first MySqlDataReader item you got, substitute it on a second query ("DROP " + mdr[0]). You have to loop for each result if you didn't use the concat.

Please note that this is dynamic SQL and prepared statements or filtering will not be effective against SQL injection. If there is a table in your database called "my_prefix ; DROP TABLE Students;--" it is your responsibility to sanitize that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.