Best practice advice for efficient SQL connection code

Hi,

I have an application which is similar to the following example

Private Sub Start()
For a as int16 = 1 to 300
lstResults.items.add(GetPriceFromItem(a))
Next
End Sub

Private Function GetPriceFromItem(byval item as int16) as String
'Connect to SQL
'Execute "SELECT Price FROM Table WHERE Item='" & item.tostring & "'"
'Close Database connection
'Return Price
End Function

I want to know if there is a more efficeint way of doing this, i.e. i'm concerned that the routine creates 300 SqlConnection instances, 300 open/closes and 300 queries

Would a better way be to connect to SQL once, get the entire table then do the 300 "lookups" locally somehow, perhaps put it all into a DataTable, but can you query a datatable in this way, or could you suggest another control.

Best Regards

Ben

You might try one SQL statement which returns all of your needed records in one resultset, with a query like this:

SELECT Price, Item FROM Table WHERE Item BETWEEN 1 AND 300

(I suggest changing the data type of your Item column to integer.)|||

If lstResults is a listbox, then I would use tmorton's SELECT statement with a SqlDataSource control to fill the listbox instead of coding it. Unless of course, you want all of the items, then just don't put anything in the WHERE clause at all.

if lstResults is just a list, then use tmorton's SELECT statement with a datareader to fill the list all at once.

|||

Hi,

I just used lstResults to simplyfy my example, in the actual application these queries form part actually a DataTable which is built on the fly.

Most of the columns are populated with values coming from the Ebay API, then for the last column I take the value of column 0 which is ItemID and lookup to a SQL DB (approx 300 records)

Then datatable is bounded to a datagridview

|||

In that case, use the datareader and tmorton's SELECT statement, but you will need to reverse your logic. Read each record from the SQL Database, then find the row in the datatable that it corresponds to (if any).

Or, you can use a datareader, and stuff the result into a collection/dictionary, then iterate through the datatable, and use the itemID to retrieve the value from the collection/dictionary.

|||

Hi, yes thats the idea that I had. But what is a collection/dictionary?

|||

dim x as new collection

x.add("value1","key1")

x.add("value2","key2")

x.add("MyValue","Mykey")

debug.print x("key1") -- prints value1
debug.print x("Mykey") -- prints MyValue
debug.print x("key2") -- prints value2

A collection/dictionary is basically a key/value pair that allows you to store the value into an object and then quickly retrieve the value based on the key. Most implementations use a hashed key AND/OR binary tree structure so that retrieving the value is pretty fast, much faster than say iterating through an array looking for a key. Just be careful when you retrieve values from the collection as the default collection requires a string key. If you ask for a numeric key, then it'll act more like an array and give you back the nth entry in the collection rather than the value of that key.
So...
debug.print x(1) -- will retrieve the first value
debug.print x(cstr(1)) -- will retrieve the value that has a key of "1"

A dictionary is very similiar, as it stores and retrieves keys and values. In .NET dictionaries are a generic form of collection as far as I know, but with a few different methods, so the following will still work:
dim x as new generic.dictionary(Of string,string)
x.add("value","key")
debug.print x("key")

But you can't retrieve things by index like you can in a collection, so the following will NOT work:
debug.print x(1)

I think dictionaries are a bit faster than collections too.

|||

Hi

You could process just one sql statement (much more efficient use of the query engine) by using the IN statement eg.

select * from table

where tablefield IN ("a", "b", "c")

Hope this helps

Chris Seary