Epic method to check if database table contains any suitable rows
-
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
-
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 useSELECT 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
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.
-
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 useSELECT 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
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.
-
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.
-
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 = 0cn = 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 FunctionBut 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."
-
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."
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.
-
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.
-
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 = 0cn = 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 FunctionLove that... ;P :laugh:
Smokie, this is not 'Nam. This is bowling. There are rules. http://melpadden.wordpress.com LinkedIn
-
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 = 0cn = 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 Functionvar 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.
-
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."
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