How to show the user that the name is not available in sql-CodePudding

The user is going to make a character and give a name on the caracter. I have fixed so it will not insert data if the name exists but now I want to know how to show the user that the name is not available. The code will return 1 because it's not failing. The following is my code:

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

CodePudding user response：

You don't need to change a lot of your current code, just, select the internal variable @Id before ending the query and execute the query with 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;";

Note the final SELECT and (to be clear) the initialization to 0 for the @Id variable. The @Id will be returned to your calling code when you use ExecuteScalar.

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