CODEWITHSUNDEEP
c#mysql
natli
natli

Reputation: 3822

c# reports mysql command failed even though it didn't

I split the command over several lines for ease of editing:

string docommand = "";
docommand += "DROP TABLE IF EXISTS `config`;";
//docommand += "/*!40101 SET @saved_cs_client     = @@character_set_client */;";
//docommand += "/*!40101 SET character_set_client = utf8 */;";
docommand += "CREATE TABLE `config` (";
docommand += "  `name` varchar(200) NOT NULL,";
docommand += "  `value` varchar(200) NOT NULL,";
docommand += "  `timestamp` int(11) NOT NULL,";
docommand += "  UNIQUE KEY `name` (`name`)";
docommand += ")";
//docommand += " ENGINE=MyISAM DEFAULT CHARSET=latin1";
docommand += ";";
//docommand += "/*!40101 SET character_set_client = @saved_cs_client */;";

mysql_connect connect = new mysql_connect();
MySqlConnection conn = connect.connect(core_config.server, core_config.database, core_config.username, core_config.pass);
bool success = mysql_command.command(docommand, conn);
connect.disconnect();

if (!success)
    Console.WriteLine("Command failed : " + docommand);

As you can see I commented out a few lines to see if they were causing trouble. Didn't make a difference.

Output:

Returning false because resultSet is 0
Command failed : DROP TABLE IF EXISTS `config`;CREATE TABLE `config` (  `name` varchar(200) NOT NULL,  `value` varchar(200) NOT NULL,  `timestamp` int(11) NOT NULL,  UNIQUE KEY `name` (`name`));

But the table is correctly created... and when I paste the exact same command in phpmyadmin and run it from there, it reports no problems whatsoever.

A command like "SELECT * FROM config" does not report a failure. So why does the CREATE TABLE command do?

mysql_command.command() function:

public static bool command(string input, MySqlConnection con)
{
    try
    {
        MySqlCommand command = new MySqlCommand(input, con);
        var resultSet = command.ExecuteNonQuery();

        if (!resultSet.Equals(0))
        {
            return true;
        }
        Console.WriteLine("Returning false because resultSet is 0");
        return false;
    }
    catch (Exception ex) { Console.WriteLine("MySQL command error : "+ex.Message); }
    Console.WriteLine("Returning false after exception");
    return false;
}

Upvotes: 0

Views: 251

Answers (1)

Arran
Arran

Reputation: 25056

Both DROP TABLE and CREATE TABLE do not send back anything other than 0. Therefore your "command" method will always believe the command fails. The ExecuteNonQuery() will returns the rows affected by your statements, which will be 0.

What you could do, in pass in the amount of rows you expect it to change, so something like:

public static bool command(string input, MySqlConnection con, int expectedRowsAffected = 0)
{
try
{
    MySqlCommand command = new MySqlCommand(input, con);
    var resultSet = command.ExecuteNonQuery();

    if (resultSet.Equals(expectedRowsAffected))
    {
        return true;
    }
    Console.WriteLine(string.Format("Returning false because resultSet isn't {0}", expectedRowsAffected));
    return false;
}
catch (Exception ex) 
{ 
    Console.WriteLine("MySQL command error : "+ex.Message); 
}
Console.WriteLine("Returning false after exception");
return false;

}

This is not a great way of doing it - as I said, whether or not a command is "successful" can mean different things - but at a low level, a command is successful if it runs without any errors/exceptions, which you are already checking.

Upvotes: 1

Related Questions