Solved

check if a value exists in a column

Posted on 2011-03-23
4
207 Views
Last Modified: 2012-06-22
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

0
Comment
Question by:llegar100
  • 2
4 Comments
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 35197066
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).
0
 
LVL 1

Expert Comment

by:Muralidharand
ID: 35197243

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());

0
 
LVL 13

Expert Comment

by:dwkor
ID: 35200409
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

0
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 500 total points
ID: 35210614
You probably meant this:

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

Open in new window


For some reason, I never think to use the EXISTS statement, but it's great - SQL will run until it finds a match and then quit - there's no need to get a count of rows here when you don't care about the count at all, just a yes/no.
0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now