如何将mysql查询转换为可接受的c#字符串
本文关键字:可接受 字符串 转换 mysql 查询 | 更新日期: 2023-09-27 18:07:57
如果列不存在,我将使用mysql动态创建列。我得到了在mysql控制台工作的代码,但当涉及到c#时,它给我"在命令执行期间遇到致命问题"
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);
}
}
部分
'"SELECT 1', '"A
与
处的原始查询不匹配"SELECT 1",
"A
你发现了吗?您将1
后面的"
替换为'
我刚刚找到了另一种检查列是否存在的方法。刚刚在下面的查询中使用,并检查列是否存在,遍历列
的循环string last_col = "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'attendance' AND TABLE_NAME ='" + subCodeText.Text + "'";
其中attendance
是数据库,subCodeText.Text
是我的表名