Doubt integration test

Question

I think it’s a somewhat conceptual question about the subject, but come on:

I have an example method that validates a user checking if it exists in the database, thus:

public bool ValidaCampos(string Nome)
{
    string str = "select count(*) FROM USER where racf='" + Nome "'";

    var cont = cD.ExecutaScalar(str);

    if(Cont>0)
    { return true;}

    else
    { return false;}
}

Although it is a simple method, I would like to understand what would be the most lean and correct way to test the operation with the database.

Answer 1

In a very simplified way and doing it a little bit more correctly, but not ensuring that it is 100% correct for the lack of context of the question would be this:

public bool ValidaCampos(string Nome) {
    cD.CommandText= "select count(*) FROM USER where racf = @user";
    cD.Parameters.AddWithValue("@user", Nome);
    return cD.ExecutaScalar(str) > 0;
}

I put in the Github for future reference.

Note that really the code can be simplified because it has completely redundant and unnecessary things, but to be safe you need to add other things. There are other ways to do and more safely (this case may give some problems), but it would have to change the whole architecture and not only this code, and we would have to know more about the scenario as a whole.

Something tells me you have other problems with the unexposed code.

Answer 2

(...) would like to understand what would be the most lean and correct way to test the operation with the database.

It is not necessary to count how many lines meet the clause WHERE; just find a line that meets that is enough to know that the user is already registered.

If there is no index in the column racf, the situation worsens even more with the use of count(*), because the whole table will be read; imagine if it is 1 million lines?!

Here are two options for T-SQL code (without considering the rest of the code):

string str = "SELECT case when exists (SELECT * from [USER] where racf='" + Nome + "') then 1 else 0 end;";

or

string str = "SELECT count(*) from (SELECT top (1) * from [USER] where racf='" + Nome + "');";