What is the best way to check if a value exists in a database in VB.NET?

Question

I am familiar with connecting to databases in VB.NET to get data, but I have a slightly different task this time. In the past I have hit the database to get values from a table to use in my application (names, addresses, prices, etc.), but now I need to simply access the database to check and see if a value exists. I don't know the best way to go about this. Is there a good, efficient way to do this without actually getting any data from the database?

To further clarify, I want to check and see if a certain code exists in the database when the user enters a value in a textbox (this is an ASP.NET web site project). If the code exists, I will inform the user via a popup box.

EDIT:

If I create a stored procedure in SQL Server that returns true if the value exists or false if it doesn't (or 0 or 1), how can I use the return value in VB.NET?

Answer 1

If you know the Primary Key value or some other unique value of your table the fastest way I know to check the existence or not of a particular record is the following

Dim cmdText = "IF EXISTS(SELECT 1 FROM yourTable WHERE idField = @value) " & _
              "SELECT 1 ELSE SELECT 0"

Using cnn = new SqlConnection(.....)
Using cmd = new SqlCommand(cmdText, cnn)
    cnn.Open()
    cmd.Parameters.Add("@value", SqlDbType.Int).Value = 9876
    Dim result = Convert.ToInt32(cmd.ExecuteScalar())
    Dim exists = IF result = 1, True, False
    ....
End Using
End Using

This approach is preferable to count the records that match your condition because the database engine is free to return immediately after checking if the condition has been matched instead of counting till the end of the table. But, of course, it is of the utmost importance that the field on which you execute the search is indexed.

Some info on the EXISTS operator on MSDN

Answer 2

SELECT COUNT(*) FROM YourTableName WHERE CODE = @CODE