Epic method to check if database table contains any suitable rows

Ondrej Linhart

Public Function CHeckDataPrjVyd(ByVal data As String) As Boolean
Dim cn As System.Data.SqlServerCe.SqlCeConnection
Dim cmd As System.Data.SqlServerCe.SqlCeCommand
Dim dtr As System.Data.SqlServerCe.SqlCeDataReader
Dim i As Integer = 0

cn = New System.Data.SqlServerCe.SqlCeConnection("Data Source=" + values.DatabaseFile)
cn.Open()
cmd = New System.Data.SqlServerCe.SqlCeCommand("SELECT \* FROM data where oznacenie like '" + data + "'", cn)
dtr = cmd.ExecuteReader()

While dtr.Read
    i = i + 1
    If i = 1 Then
        CHeckDataPrjVyd = True
        Exit While
    End If
    CHeckDataPrjVyd = False
End While
cn.Close()

End Function

After cleaning that monstrosity:

Public Function IsDataPresent(ByVal data As String) As Boolean
Using connection As New SqlCeConnection(My.Settings.ConnectionString)
connection.Open()
Dim commandText = String.Format("SELECT * FROM data WHERE oznacenie LIKE '{0}'", data)
Using command As New SqlCeCommand(commandText, connection)
Using reader = command.ExecuteReader()
Return reader.HasRows
End Using
End Using
End Using
End Function

Wayne Gaylard

I'm sure the original coder was being paid by the letter ;P

Ondřej Linhart wrote:

While dtr.Read i = i + 1 If i = 1 Then CHeckDataPrjVyd = True Exit While End If

Not only that but his function always returns true. Epic fail! :omg:

When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman

Nagy Vilmos

Ondřej Linhart wrote:

Dim commandText = String.Format("SELECT * FROM data WHERE oznacenie LIKE '{0}'", data)

Little Bobby Tables[^] is going to love that one. You should never build a SQL string, use command parameters[^] to keep it safe. Also, I would advice against SELECT * ..., it is a lazy approach. If you only want to know if the row exists use SELECT oznacenie FROM data WHERE oznacenie LIKE @OZ

Panic, Chaos, Destruction. My work here is done. Drink. Get drunk. Fall over - P O'H OK, I will win to day or my name isn't Ethel Crudacre! - DD Ethel Crudacre I cannot live by bread alone. Bacon and ketchup are needed as well. - Trollslayer Have a bit more patience with newbies. Of course some of them act dumb - they're often *students*, for heaven's sake - Terry Pratchett

Lost User

And, please, tell me what exactly an oznacenie is. :) Guessing by Google images, it may be a sign :)

And from the clouds a mighty voice spoke:
"Smile and be happy, for it could come worse!"

And I smiled and was happy
And it came worse.

Ondrej Linhart

In this case it is something like "marking". Stupid naming in former developer's native language is next horrifying issue.

BobJanova

No it doesn't, if dtr.Read returns false first time out (i.e. there are no rows) it will return false. It works, at least from a quick read, just in a stupid fashion.

BobJanova

True. I would think that 'select count(*) from data where oznacenie like @data limit 1' (or count(oznacenie)) would be the most efficient way, though. There's no reason to make the database build up a full result set since you're just checking if anything is present and throwing away the data reader.

AspDotNetDev

Or better yet:

SELECT TOP 1 0 FROM data WHERE oznacenie LIKE @OZ

Somebody in an online forum wrote:

INTJs never really joke. They make a point. The joke is just a gift wrapper.

BobJanova

Is top better than limit? Or just a different way of writing the same thing?

RobCroll

But you still haven't addressed the shame! The potential of dragging thousands of records across the network. I'd be taking advantage of the ExecuteScalar method and the SQL COUNT function to address this issue.

Dim commandText = String.Format("SELECT COUNT(*) FROM data WHERE oznacenie LIKE '{0}'", data)
Int32 count = (Int32) cmd.ExecuteScalar() ;

Shame on you ;)

"You get that on the big jobs."

Ondrej Linhart

It is true that i did not optimized SQL command, only removed worst trash from code. It is hundreds of records at maximum and it is not over network - only locally using SQL Server Compact Edition.

AspDotNetDev

I think limit is more flexible, but limit isn't available in SQL Server.

Somebody in an online forum wrote:

INTJs never really joke. They make a point. The joke is just a gift wrapper.

Mel Padden

Love that... ;P :laugh:

Smokie, this is not 'Nam. This is bowling. There are rules. http://melpadden.wordpress.com LinkedIn

prasun r

var cn = New System.Data.SqlServerCe.SqlCeConnection("Data Source=" + values.DatabaseFile); cn.Open(); var cmd = New System.Data.SqlServerCe.SqlCeCommand("SELECT Count(ID_COL) FROM data where oznacenie like '" + data + "'", cn); var count = cmd.ExecuteScalar(); this seems to be an optimized solution to me, shouldn't use '*' if we can do without it cause you don't know what kind of nutter designed the database. I have seen tables with 45 columns that slows down the query. Always prefer to specify exact columns I need in the queries.

James H

Select Count(*) has to do the fuzzy like search on the whole table just to show rows that match might exist - so it will probably do a full table scan (if start of like is a %) better is to let SQL return as soon as it has found one row that matches - so use exists - e.g.

String.Format("SELECT 1 WHERE EXISTS (SELECT 1 FROM data WHERE oznacenie LIKE '{0}')", data)

That way SQL can stop looking after the first row it finds that matches. Depending on size of table this can make a huge difference in performance. This will return 0 or 1 records to the rowcount which you can get using ExecuteScalar return