检查表创建是否成功

Question

我有一个winforms应用程序，必须将数据从SQL迁移到MySQL。该功能的一部分需要通过添加所需的表来准备目标数据库(如有必要，并丢弃现有表(。

我正在寻找最有效的方法来确定表是否成功创建了表。这个问题的答案表明，执行查询将返回一个大于0的整数值，如果它成功。理想情况下，我想执行命令并使用返回值，而不是运行其他查询。这可能吗？

我当前的代码：

MySqlConnection myConnection = new MySqlConnection(ConnectionString);
string sql = @" DROP TABLE IF EXISTS    `sf_root_items`;
                CREATE TABLE            `sf_root_items` (
                                        `ID` varchar(255) NOT NULL,
                                        `LoweredName` varchar(255) DEFAULT NULL,
                                        `MenuName` varchar(255) DEFAULT NULL,
                                        `Title` varchar(255) DEFAULT NULL,
                                        `Description` varchar(255) DEFAULT NULL,
                                        `PageType` varchar(255) DEFAULT NULL,
                                        `ExternalUrl` varchar(255) DEFAULT NULL,
                                        PRIMARY KEY(`ID`)
                                        )";
MySqlCommand cmd;
try
{
    if (myConnection.State != ConnectionState.Open)
    {
        myConnection.Close();
        myConnection.Open();
    }
    cmd = myConnection.CreateCommand();
    cmd.CommandText = sql;
    int output = cmd.ExecuteNonQuery();
    // a value greater than 0 means execution was successful
    if (output > 0)
    {
        DBPrepDone = "Table created";
    }
    else
    {
        DBPrepDone = "There was an error";
    }
    myConnection.Close();
}
catch (Exception ex)
{
    DBPrepDone = ex.ToString();
}

Answer 1

以下是Microsoft站点的executeNonquery返回值的描述

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonqummand.executenonquery(v = vs.110).aspx

尽管执行量没有返回行，但任何输出参数或 映射到参数的返回值用数据填充。为了 更新，插入和删除语句，返回值是数字 受命令影响的行。当扳机存在于桌子上时 被插入或更新，返回值包括 受插入或更新操作影响的行以及 受触发器或触发器影响的行。对于所有其他类型的 语句，返回值为-1。如果发生回滚，返回 值也为-1。

要进行查询工作，您应该添加检查表格的现有表格，例如

string sql = @" DROP TABLE IF EXISTS    `sf_root_items`;
    CREATE TABLE            `sf_root_items` (
                            `ID` varchar(255) NOT NULL,
                            `LoweredName` varchar(255) DEFAULT NULL,
                            `MenuName` varchar(255) DEFAULT NULL,
                            `Title` varchar(255) DEFAULT NULL,
                            `Description` text,
                            `PageType` varchar(255) DEFAULT NULL,
                            `ExternalUrl` varchar(255) DEFAULT NULL,
                            PRIMARY KEY(`ID`)
                            )";
string sql_check = @" SELECT count(*)
                    FROM information_schema.TABLES
                    WHERE (TABLE_NAME = 'sf_root_items')
                    AND (TABLE_SCHEMA = '" + WP_db.Text +"')";
MySqlCommand cmd;
try
{
    if (myConnection.State != ConnectionState.Open)
    {
        myConnection.Close();
        myConnection.Open();
    }
    cmd = myConnection.CreateCommand();
    cmd.CommandText = sql;
    cmd.ExecuteNonQuery();
    //call check if table was created
    cmd = myConnection.CreateCommand();
    cmd.CommandText = sql_check;
    var test = cmd.ExecuteScalar();
    int output;
    int.TryParse(test.ToString(), out output);
    // a value greater than 0 means execution was successful
    if (output > 0)
    {
        DBPrepDone = "Table 'sf_root_items' has been created";
    }
    else
    {
        DBPrepDone = "There was an error";
    }
    myConnection.Close();
}
catch (Exception ex)
{
    DBPrepDone = ex.ToString();
}