代码之家 › 专栏 › 技术社区 › Daniel

检查表创建是否成功

mysql c#

Daniel · 技术社区 · 9 年前

我正在寻找最有效的方法来确定表是否成功创建。 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();
}

1 回复 | 直到 9 年前

Daniel 9 年前

下面是对Microsoft site中ExecuteNonQuery返回值的描述

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery%28v=vs.110%29.aspx

映射到参数的返回值由数据填充。对于受命令影响的行数。当表上存在触发器时在插入或更新时,返回值包括受插入或更新操作和受一个或多个触发器影响的行。对于所有其他类型的

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();
}