C# Using ExecuteScalar() Method With Where Clause in SqlCommand

Question

try
{
    SqlCommand sqlCommand = new SqlCommand("SELECT COUNT(*) FROM Doctor WHERE Id = @id", sqlServerConnection);

    sqlCommand.Parameters.AddWithValue("@id", id.Text);
    
    int numberOfDoctors = (int) sqlCommand.ExecuteScalar();

    if(numberOfDoctors == 1)
    {
        Console.WriteLine("Doctor is already in database.");
    }

    else
    {
        Console.WriteLine("There is no doctor with this Id.");
    }
}
catch (Exception exc)
{
    Console.WriteLine(exc);
}

I have a code like this. I have an application that has a connection with SQL database. Firstly, sqlServerConnection object is defined in the code correctly. There is no problem with that object. I have a Doctor table in the database. id. Text comes from Text element that is the value user typed in. I want to be sure about whether this typed id is already in my database or not. Whichever value I type in I always see "Doctor is already in database." message in the console. When I remove WHERE clause from sqlCommand code works correctly. But when I add WHERE clause I can't track whether this user with the given id is in my database or not. Could you please help me? Thank you for your responses in advance. Have a great day :)

When there are no records matching the id value, the Executescalar will return null. You should check for the return value instead of always typecasting it to int. — Anand Sowmithiran, Jan 09 '22 at 14:13
Is there a unique/primary key on the `Id` column? What data type is it? — Charlieface, Jan 09 '22 at 14:19
The only issue I can see is that you are setting the id parameter with a string instead of an integer which is what I assume the type of `Id` is. I'm not sure if that has any detrimental effect other than [this](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/). I'm a little bit skeptical that the code you are showing is not actually exactly what you are executing. — Crowcoder, Jan 09 '22 at 14:19
`ExecuteScalar` will not return null in this case since `COUNT` is going to result in zero or greater. You can ignore that comment in this specific case. — Crowcoder, Jan 09 '22 at 14:21
You need to do some debugging. Run the SQL in a console and see what it returns for all the ids you are testing. Get the return value of ExecuteScalar() into a variable that is not cast to int and print it out. Or use the debugger and single-step through. — siride, Jan 09 '22 at 14:30
Try specifying the type of the parameter (type + length), instead of hoping that AddWithValue does it correctly — Hans Keﬆing, Jan 09 '22 at 14:35
_Does null conversion to int results in 1?_ Why don't you use the debugger and find out for yourself? — SMor, Jan 09 '22 at 17:50
I tried it doesn't. Maybe I did something wrong. You don't die if you share your knowledge. Horrible! — Roller Fischer, Jan 11 '22 at 20:39

score 1 · Answer 1 · answered Jan 09 '22 at 14:42

There are a number of issues with your code:

You should specify the type and length of the parameter explicitly
You need to dispose the connection and command objects
There is no need to use SELECT COUNT if there is only one row, you can just do SELECT 1

const string query = @"
SELECT 1
FROM Doctor
WHERE Id = @id;
";
try
{
    using (var connection = new SqlConnection(yourConnString))
    using (var sqlCommand = new SqlCommand(, sqlServerConnection);
    {
        sqlCommand.Parameters.Add("@id", SqlDbType.Char, 7).Value = id.Text;
        connection.Open();
        int numberOfDoctors = (sqlCommand.ExecuteScalar() as int) ?? 0;  // will be null if no rows
        connection.Close();
        if(numberOfDoctors == 1)
        {
            Console.WriteLine("Doctor is already in database.");
        }
        else
        {
            Console.WriteLine("There is no doctor with this Id.");
        }
    }
}
catch (Exception exc)
{
    Console.WriteLine(exc);
}

If there is no unique key on that column, you can instead do EXISTS:

  SELECT CASE WHEN EXISTS (SELECT 1
       FROM Doctor
       WHERE Id = @id)
     THEN 1 ELSE 0 END;

Using SELECT 1 will return no rows if the id isn't found. The C# code you have deals with this properly but it is important to note. — siride, Jan 09 '22 at 14:59
I did make a comment in the code re that, precisely for that reason — Charlieface, Jan 09 '22 at 15:33

Steve · Answer 2 · 2022-01-09T15:37:27.333

0

In practice you don't need to COUNT the whole table only to discover if your record exists or not.

try
{
    string cmdText = @"IF EXISTS(SELECT 1 FROM Doctor WHERE Id = @id) 
                                 SELECT 1 ELSE SELECT 0";
    SqlCommand sqlCommand = new SqlCommand(cmdText, sqlServerConnection);
    sqlCommand.Parameters.Add("@id", SqlDbType.Char, 7).Value = id.Text;
    
    int docExist = (int)sqlCommand.ExecuteScalar();
    if(docExist == 1)
    {
        Console.WriteLine("Doctor is already in database.");
    }
    else
    {
        Console.WriteLine("There is no doctor with this Id.");
    }
}
catch (Exception exc)
{
    Console.WriteLine(exc);
}

The IF EXIST will stop to search if the a record exists while COUNT will do what is supposed to do, count the record that satisfy the condition till the end of the table.

edited Jan 09 '22 at 15:37

answered Jan 09 '22 at 14:23

Steve

213,761
22
232
286

1

Then, this also must not be done by code, rather should be enforced using the UNIQUE constraint provided by the database engine. – Anand Sowmithiran Jan 09 '22 at 14:26
1

Yes, I agree with you. There is no way that you should have the same ID for different doctors, but I took the opportunity to talk about ExecuteScalar and IF EXIST that could be important also for other tasks. – Steve Jan 09 '22 at 14:28
It doesn't count the whole table. It only counts rows where the id matches the specified id. This is a needless "optimization". – siride Jan 09 '22 at 14:28
The you should tell your findings to https://www.oreilly.com/library/view/microsoft-sql-server/9780133408539/ch45lev2sec6.html#:~:text=IF%20EXISTS%20stops%20the%20processing%20of%20the%20select,FROM%20Sales.SalesOrderDetail%20WHERE%20ProductID%20%3D%20324%29%20%3E%200 By the way, I am curious to know where do you have read about this "needless" optimization – Steve Jan 09 '22 at 14:30
1

I should also note that count always returns a number. That does not create an issue for nulls – siride Jan 09 '22 at 14:31
1

That suggestion to use IF EXISTS instead of Count(*) is ok, however, it helps for checking presence of non-ID(primary key) columns, as in that example product ID looked up in SalesOrderDetail table. In this OP case, he must use the Unique constraint for the Doctor ID column. – Anand Sowmithiran Jan 09 '22 at 14:41
2

Well, you're both right, sort of. On the one hand, it's true that `COUNT` only *counts* the matching rows, but on the other hand, it's also true that `COUNT` does *scan* the entire table whereas `EXISTS` can quit after finding a single match. However, both of these performance can be affected by the presence of indexes/keys. In general, using EXISTS where possible is considered better than COUNT. – RBarryYoung Jan 09 '22 at 14:42
Can you verify that with an execution plan? I'd expect the count operator to apply after row filtering due to the where clause. My recollection of looking at execution plans back in the day was that this was the order. You aren't going to get a table scan just from having a count. That would be amazing and obviously inefficient. – siride Jan 09 '22 at 14:55
@Steve if there is only one result, then count(*) will also stop executing after the first result (the only result). The execution work should be the same either way. – siride Jan 09 '22 at 14:57

C# Using ExecuteScalar() Method With Where Clause in SqlCommand

2 Answers2