Doubt integration test

Question

Doubt integration test

Navigation

#1 by (4 votes)
#2 by (0 votes)

0

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

I have an example method that validates a user by checking whether it exists in the database, like this:

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 leanest and most correct way to test the operation with the database.

sql-server c# visual-studio-2017

asked by anonymous 30.10.2018 / 20:02

2 answers

How to sum the main diagonal of a matrix in python? [closed] Receive result of an ajax as return

score 4 · Answer 1

In a very simplified way and making it a bit more correct, but not guaranteeing that it is 100% correct due to 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 placed GitHub for future reference a>.

Note that the code can actually be simplified because it has completely redundant and unnecessary things, but to be sure you need to add other things. It has other ways of doing it and more securely (this case may give some problems), but 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 that there are other problems in the code not exposed.

score 0 · Answer 2

I would like to understand what would be the simplest and most accurate way of testing the operation with the database.

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

If there is no index for the racf column, the situation becomes even worse with the use of count(*) because the entire table will be read; Can you imagine if there are 1 million lines ?!

Here are two options for the 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 + "');";