c# - 如何将 mysql 查询转换为可接受的 c# 字符串？

Question

如果列不存在，我将使用 mysql 动态创建列。我得到了在 mysql 控制台中工作的代码，但是当涉及到 c# 时，它给了我“在命令执行期间遇到 fatal error ”

SET @preparedStatement = (SELECT IF(
(SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE  table_name = 'tableName'
    AND table_schema = DATABASE()
    AND column_name = 'colName'
) > 0,
"SELECT 1",
"ALTER TABLE `tableName` ADD `colName` TINYINT(1) NULL DEFAULT '0';"
));

PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

上面的代码我转换成了c#字符串

string qry = "SET @preparedStatement = ( SELECT IF( (SELECT count(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'attendance' AND TABLE_NAME = '" + tname + "' AND COLUMN_NAME = '" + code + "_C' ) > 0, \"SELECT 1', \"ALTER TABLE " + tname + " ADD " + code + "_C int(3) NOT NULL default '0'; \" )); PREPARE alterIfNotExists FROM @preparedStatement; EXECUTE alterIfNotExists; DEALLOCATE PREPARE alterIfNotExists;";

出现了什么错误？

执行代码:

    private void columnCreate_Load(object sender, EventArgs e)
    {
        string tname = "bca_i"; //for temprory
        string code = "BCAXX";//for temprory
        string qry = @"SET @preparedStatement = ( SELECT IF( (SELECT count(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'attendance' AND TABLE_NAME = '" + tname + "' AND COLUMN_NAME = '" + code + "_C' ) > 0, \"SELECT 1', \"ALTER TABLE " + tname + " ADD " + code + "_C int(3) NOT NULL default '0'; \" )); PREPARE alterIfNotExists FROM @preparedStatement; EXECUTE alterIfNotExists; DEALLOCATE PREPARE alterIfNotExists;";
        try
        {
            using (MySqlConnection conn = new MySqlConnection(ConStr))
            {
                conn.Open();
                using (MySqlCommand cmd = new MySqlCommand(qry, conn))
                {
                    cmd.ExecuteNonQuery();
                }

            }

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }

    }

Answer 1

部分

\"SELECT 1', \"A

与您的原始查询不匹配

"SELECT 1",
"A

你发现了吗？您将 1 之后的 " 替换为 '。