CODEWITHSUNDEEP
c#sql-serverexecutescalar
Roller Fischer
Roller Fischer

Reputation: 19

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

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 :)

Upvotes: 0

Views: 1598

Answers (2)

Steve
Steve

Reputation: 216291

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.

Upvotes: 0

Charlieface
Charlieface

Reputation: 71544

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;

Upvotes: 1

Related Questions