c# - 检查建表是否成功

Question

我有一个 Winforms 应用程序，必须将数据从 SQL 迁移到 MySQL。部分功能需要通过添加所需的表(并在必要时删除现有表)来准备目标数据库。

我正在寻找最有效的方法来确定表是否已成功创建。 This question's answer建议如果成功执行查询将返回大于 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.executenonquery%28v=vs.110%29.aspx

Although the ExecuteNonQuery returns no rows, any output parameters or return values mapped to parameters are populated with data. For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -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();
}