patterncsharpModerate
Find if value exists using SQL True/False Return
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?
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 FunctionSolution
- This code is open to injection. Consider
what happens if I pass
sometext'; Drop Table TblTable; -- intothat 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 mightgarner you a negligible amount of performance at the cost of
readability. (
Exists wouldn't need to return any value at allexcept what you tell it.)
- You could simplify your return statement by using
IsNullOrEmpty()
Instead of:
If result = "" Then
Return False
Else
Return True
End IfYou could just use this:
Return Not String.IsNullOrEmpty(result)Code Snippets
If result = "" Then
Return False
Else
Return True
End IfReturn Not String.IsNullOrEmpty(result)Context
StackExchange Code Review Q#55679, answer score: 11
Revisions (0)
No revisions yet.