如何向用户显示该名称在 sql 中不可用

Question

用户将创建一个角色并为该角色命名。我已修复，因此如果名称存在，它不会插入数据，但现在我想知道如何向用户显示该名称不可用。该代码将返回 1，因为它没有失败。以下是我的代码：

public int InsertAddventurer(Character Ad, int ClassId, out string errormsg)
{
    SqlConnection dbConnection = new SqlConnection();

    dbConnection.ConnectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Adventurer;Integrated Security=True";

    String sqlstring = "" +
        "BEGIN " +
        "DECLARE @Id int; " +
        "IF NOT EXISTS(SELECT Ad_Fornamn FROM [Tbl_Adventurer] WHERE Ad_Fornamn = @name) " +
        "BEGIN " +
        "INSERT INTO[Tbl_Adventurer] " +
        "(Ad_Fornamn, Ad_Efternamn, Ad_Age, Ad_Rank) " +
        "Values(@Ad_Fornamn, @Ad_Efternamn, @Ad_Age, @Ad_Rank) " +
        "SELECT @Id = IDENT_CURRENT('Tbl_Adventurer'); " +
        "INSERT INTO[Tbl_Stats] " +
        "(Stats_Id, Strength, Dexterity, Constitution, Intelligence, Wisdom, Charisma) " +
        "Values(@Id, @Strength, @Dexterity, @Constitution, @Intelligence, @Wisdom, @Charisma) " +
        "INSERT INTO[Tbl_AdventurerKlass] " +
        "(Ad_Id, Kl_Id) " +
        "Values(@Id, @ClassId) " +
        "END " +
        "END";
    SqlCommand dbCommand = new SqlCommand(sqlstring, dbConnection);

    dbCommand.Parameters.Add("name", SqlDbType.VarChar, 30).Value = Ad.Name;
    dbCommand.Parameters.Add("Ad_Fornamn", SqlDbType.VarChar, 30).Value = Ad.Name;
    dbCommand.Parameters.Add("Ad_Efternamn", SqlDbType.VarChar, 30).Value = Ad.Lastname;
    dbCommand.Parameters.Add("Ad_Age", SqlDbType.Int).Value = Ad.Age;
    dbCommand.Parameters.Add("Ad_Rank", SqlDbType.Int).Value = Ad.Rank;
    dbCommand.Parameters.Add("Strength", SqlDbType.Int).Value = Ad.Strength;
    dbCommand.Parameters.Add("Dexterity", SqlDbType.Int).Value = Ad.Dexterity;
    dbCommand.Parameters.Add("Constitution", SqlDbType.Int).Value = Ad.Constitution;
    dbCommand.Parameters.Add("Intelligence", SqlDbType.Int).Value = Ad.Intelligence;
    dbCommand.Parameters.Add("Wisdom", SqlDbType.Int).Value = Ad.Wisdom;
    dbCommand.Parameters.Add("Charisma", SqlDbType.Int).Value = Ad.Charisma;
    dbCommand.Parameters.Add("ClassId", SqlDbType.Int).Value = ClassId;

    try
    {
        dbConnection.Open();
        int i = 0;
        i = dbCommand.ExecuteNonQuery();
        if (i == 1)
        {
            errormsg = "";
        }
        else
        {
            errormsg = "No Adventurer was created";
        }
        return i;
    }
    catch (Exception ex)
    {
        errormsg = ex.Message;
        return 0;
    }
    finally
    {
        dbConnection.Close();
    }
}

Answer 1

您不需要更改大量当前代码，只需在结束查询之前选择内部变量 @Id 并使用 ExecuteScalar 执行查询

String sqlstring = @
"BEGIN 
     DECLARE @Id int = 0; 
     IF NOT EXISTS(SELECT Ad_Fornamn FROM [Tbl_Adventurer] WHERE Ad_Fornamn = @name) 
     BEGIN
        INSERT INTO[Tbl_Adventurer]
               (Ad_Fornamn, Ad_Efternamn, Ad_Age, Ad_Rank) 
                Values(@Ad_Fornamn, @Ad_Efternamn, @Ad_Age, @Ad_Rank)
        SELECT @Id = IDENT_CURRENT('Tbl_Adventurer')
        INSERT INTO[Tbl_Stats] 
               (Stats_Id, Strength, Dexterity, Constitution, Intelligence, Wisdom, Charisma) 
                Values(@Id, @Strength, @Dexterity, @Constitution, @Intelligence, @Wisdom, @Charisma) 
        INSERT INTO[Tbl_AdventurerKlass]
                    (Ad_Id, Kl_Id) 
                    Values(@Id, @ClassId)
    END 
SELECT @Id
END;";

注意最后的 SELECT 和（要清楚）@Id 的初始化为 0多变的。当您使用时，@Id将返回到您的调用代码中
执行标量。

int result = (int)dbCommand.ExecuteScalar();
if (i > 0)
{
    errormsg = "";
}
else
{
    errormsg = "No Adventurer was created";
}
return result;