asked on

check if a value exists in a column

I want to check if a value exist in a column:
I'm very new to SQL and c#.
I suspect that there is some easier way to do this.
Right now i do this

SqlCommand myCommand2 = new SqlCommand("SELECT count(*) FROM Customers WHERE CustNo =" + customerNumber, conn);
                int count = (int)myCommand2.ExecuteScalar();
                if (count == 1)
                {
                    existing = true;
                }
                else
                {
                    existing = false;
                }
return existing;

Open in new window

Ryan McCauley

I'd do it like this:

SqlCommand myCommand2 = new SqlCommand("SELECT count(*) FROM Customers WHERE CustNo = @CustomerNumber", conn);

myCommand2.Parameters.AddWithValue("@CustomerNumber", customerNumber) 

return (boolean)myCommand2.ExecuteScalar();

Open in new window

First, parameterize your customerNumber - it protects you from SQL Injection attacks, and it's quicker for the query engine to parse.

Second, the result of your query will either be zero or something else, so you can convert it directly to a boolean value without doing your if statements - if it's zero, the boolean is zero, and if it's anything else, the boolean returns true (non-zero).

Muralidharand

Please parameterize your query .

SqlCommand myCommand2 = new SqlCommand("SELECT count(*) FROM Customers WHERE CustNo = @CustomerNumber", conn);

myCommand2.Parameters.AddWithValue("@CustomerNumber", customerNumber)

return Convert.ToBoolead(myCommand2.ExecuteScalar());

dwkor

Use the following SQL instead. you don't need to count actual rows to check existence

select case when exists (SELECT count(*) FROM Customers WHERE CustNo = @CustomerNumber) then 1 else 0 end

Open in new window

ASKER CERTIFIED SOLUTION

Ryan McCauley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial