HiveBrain v1.2.0
Get Started
← Back to all entries
patterncsharpModerate

Find if value exists using SQL True/False Return

Submitted by: @import:stackexchange-codereview··
0
Viewed 0 times
findsqlreturntruevaluefalseusingexists

Problem

Aim: To Assess if one, or more, examples of a value exists in a Database in the quickest time as I only needs a True/False result.

The variable is Alphanumeric.

Question: Is this the quickest and best way to do this?

Public Function PrcCheckIfValueExists(vVariable As String) As String
        'Here we check if the Value Exists in the database 
        Try
            Dim ConnectionString As String = System.Configuration.ConfigurationManager.ConnectionStrings("SQLLocal").ToString()
            Using connection = New SqlConnection(ConnectionString)
                Using command As New SqlCommand("SELECT TOP 1 ID FROM TblTable WHERE ID= '" & vVariable & "'", connection)
                    connection.Open()
                    Dim result = command.ExecuteScalar()
                    connection.Close()
                    If result = "" Then
                        Return False
                    Else
                        Return True
                    End If
                End Using
            End Using

        Catch ex As Exception
            Return False
        End Try

    End Function

Solution


  • This code is open to injection. Consider


what happens if I pass sometext'; Drop Table TblTable; -- into
that variable. Use a proper parameterized query instead.

  • The SQL statement itself is about as efficient as it can be to my


knowledge. A count would add extra overhead. Using an Exists might
garner you a negligible amount of performance at the cost of
readability. (Exists wouldn't need to return any value at all
except what you tell it.)

  • You could simplify your return statement by using IsNullOrEmpty()



Instead of:

If result = "" Then
                    Return False
                Else
                    Return True
                End If


You could just use this:

Return Not String.IsNullOrEmpty(result)

Code Snippets

If result = "" Then
                    Return False
                Else
                    Return True
                End If
Return Not String.IsNullOrEmpty(result)

Context

StackExchange Code Review Q#55679, answer score: 11

Revisions (0)

No revisions yet.