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

BDNull error...not expected!

I have a connection (SqlConnection1) established through the GUI. Here's the
code:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
'Create and initialise the command object
Dim cmd As New SqlCommand("GetAddress", SqlConnection1)
'State that the type of this command is a stored procedure
cmd.CommandType = CommandType.StoredProcedure
'Create the input paramater
cmd.Parameters.Add("@.BizName", SqlDbType.VarChar, 50)
cmd.Parameters("@.BizName").Direction = ParameterDirection.Input
cmd.Parameters("@.BizName").Value = TextBox1.Text
'Create the output paramater
cmd.Parameters.Add("@.BizAddress", SqlDbType.VarChar, 50)
cmd.Parameters("@.BizAddress").Direction = ParameterDirection.Output
'Enusre the connection is open
If (cmd.Connection.State <> ConnectionState.Open) Then
cmd.Connection.Open()
End If
'Execute the command object
cmd.ExecuteNonQuery()
'Assign the returned value of the output paramater
TextBox2.Text = cmd.Parameters("@.BizAddress").Value
'Close the connection
cmd.Connection.Close()
End Sub
This code allows a textbox (textbox1) to fill an input paramater and
displays the contents of the returned value from the output paramater in a
textbox (textbox2). It works fine with s imple select statement. But with a
stored procedure i get this error:
Cast from type 'DBNull' to type 'String' is not valid.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information abou
t
the error and where it originated in the code.
Exception Details: System.InvalidCastException: Cast from type 'DBNull' to
type 'String' is not valid.
Source Error:
Line 63:
Line 64: 'Assign the returned value of the output paramater
Line 65: TextBox2.Text = cmd.Parameters("@.BizAddress").Value
The code for the stored procedure:
CREATE PROCEDURE GetAddress
@.BizName varchar,
@.BizAddress varchar output
AS
SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
GO
Where did i go wrong?
Thanks for any insights.
NabYour Stored Procedure is returning a NULL value in the @.BizAddress output
parameter. You need to assign the value to an Object and check it for
DBNull.value before converting to string:
Dim o As Object
o = cmd.Parameters("@.BizAddress").Value
If (o Is Nothing OrElse o Is DBNull.Value) Then
TextBox2.Text = ""
Else
TextBox2.Text = Convert.ToString(o)
EndIf
"Nab" <Nab@.discussions.microsoft.com> wrote in message
news:96300218-F090-44B3-A5E9-F30FCE80D711@.microsoft.com...
>I have a connection (SqlConnection1) established through the GUI. Here's
>the
> code:
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
> 'Create and initialise the command object
> Dim cmd As New SqlCommand("GetAddress", SqlConnection1)
> 'State that the type of this command is a stored procedure
> cmd.CommandType = CommandType.StoredProcedure
> 'Create the input paramater
> cmd.Parameters.Add("@.BizName", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizName").Direction = ParameterDirection.Input
> cmd.Parameters("@.BizName").Value = TextBox1.Text
> 'Create the output paramater
> cmd.Parameters.Add("@.BizAddress", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizAddress").Direction = ParameterDirection.Output
> 'Enusre the connection is open
> If (cmd.Connection.State <> ConnectionState.Open) Then
> cmd.Connection.Open()
> End If
> 'Execute the command object
> cmd.ExecuteNonQuery()
> 'Assign the returned value of the output paramater
> TextBox2.Text = cmd.Parameters("@.BizAddress").Value
> 'Close the connection
> cmd.Connection.Close()
> End Sub
> This code allows a textbox (textbox1) to fill an input paramater and
> displays the contents of the returned value from the output paramater in a
> textbox (textbox2). It works fine with s imple select statement. But with
> a
> stored procedure i get this error:
> Cast from type 'DBNull' to type 'String' is not valid.
> Description: An unhandled exception occurred during the execution of the
> current web request. Please review the stack trace for more information
> about
> the error and where it originated in the code.
> Exception Details: System.InvalidCastException: Cast from type 'DBNull' to
> type 'String' is not valid.
> Source Error:
>
> Line 63:
> Line 64: 'Assign the returned value of the output paramater
> Line 65: TextBox2.Text = cmd.Parameters("@.BizAddress").Value
>
> The code for the stored procedure:
> CREATE PROCEDURE GetAddress
> @.BizName varchar,
> @.BizAddress varchar output
> AS
> SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
> GO
> Where did i go wrong?
> Thanks for any insights.
> Nab
>
>|||A NULL @.BizAddress value will be returned when no data is found and this
cannot be converted to a .Net string data type. You can check for NULL
using DbNull.Value:
If cmd.Parameters("@.BizAddress").Value Is DBNull.Value Then
MessageBox.Show("BizName not found")
Else
TextBox2.Text = cmd.Parameters("@.BizAddress").Value
End If
Also, you need specify varchar(50) in your stored procedure parameter
declaration. The default length is 1.
CREATE PROCEDURE GetAddress
@.BizName varchar(50),
@.BizAddress varchar (50) output
AS
SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Nab" <Nab@.discussions.microsoft.com> wrote in message
news:96300218-F090-44B3-A5E9-F30FCE80D711@.microsoft.com...
>I have a connection (SqlConnection1) established through the GUI. Here's
>the
> code:
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
> 'Create and initialise the command object
> Dim cmd As New SqlCommand("GetAddress", SqlConnection1)
> 'State that the type of this command is a stored procedure
> cmd.CommandType = CommandType.StoredProcedure
> 'Create the input paramater
> cmd.Parameters.Add("@.BizName", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizName").Direction = ParameterDirection.Input
> cmd.Parameters("@.BizName").Value = TextBox1.Text
> 'Create the output paramater
> cmd.Parameters.Add("@.BizAddress", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizAddress").Direction = ParameterDirection.Output
> 'Enusre the connection is open
> If (cmd.Connection.State <> ConnectionState.Open) Then
> cmd.Connection.Open()
> End If
> 'Execute the command object
> cmd.ExecuteNonQuery()
> 'Assign the returned value of the output paramater
> TextBox2.Text = cmd.Parameters("@.BizAddress").Value
> 'Close the connection
> cmd.Connection.Close()
> End Sub
> This code allows a textbox (textbox1) to fill an input paramater and
> displays the contents of the returned value from the output paramater in a
> textbox (textbox2). It works fine with s imple select statement. But with
> a
> stored procedure i get this error:
> Cast from type 'DBNull' to type 'String' is not valid.
> Description: An unhandled exception occurred during the execution of the
> current web request. Please review the stack trace for more information
> about
> the error and where it originated in the code.
> Exception Details: System.InvalidCastException: Cast from type 'DBNull' to
> type 'String' is not valid.
> Source Error:
>
> Line 63:
> Line 64: 'Assign the returned value of the output paramater
> Line 65: TextBox2.Text = cmd.Parameters("@.BizAddress").Value
>
> The code for the stored procedure:
> CREATE PROCEDURE GetAddress
> @.BizName varchar,
> @.BizAddress varchar output
> AS
> SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
> GO
> Where did i go wrong?
> Thanks for any insights.
> Nab
>
>|||Nab wrote:
> I have a connection (SqlConnection1) established through the GUI.
> Here's the code:
>
You really should post these client-side questions to a more appropriate
newsgroup. Here are some suggestions:
microsoft.public.dotnet.languages.vb.data
microsoft.public.dotnet.framework.adonet
More below:

> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
> 'Create and initialise the command object
> Dim cmd As New SqlCommand("GetAddress", SqlConnection1)
> 'State that the type of this command is a stored procedure
> cmd.CommandType = CommandType.StoredProcedure
> 'Create the input paramater
> cmd.Parameters.Add("@.BizName", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizName").Direction = ParameterDirection.Input
> cmd.Parameters("@.BizName").Value = TextBox1.Text
> 'Create the output paramater
> cmd.Parameters.Add("@.BizAddress", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizAddress").Direction =
> ParameterDirection.Output
>
<snip>
> 'Execute the command object
> cmd.ExecuteNonQuery()
> 'Assign the returned value of the output paramater
> TextBox2.Text = cmd.Parameters("@.BizAddress").Value
>
<snip>
> Exception Details: System.InvalidCastException: Cast from type
> 'DBNull' to type 'String' is not valid.
>
<snip>
> The code for the stored procedure:
> CREATE PROCEDURE GetAddress
> @.BizName varchar,
> @.BizAddress varchar output
Always, always, ALWAYS set the length of your parameters:
@.BizName varchar(50),
@.BizAddress varchar(50) output
Do not depend on the default values,

> AS
> SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
> GO
>
From online help:
If the ParameterDirection is output, and execution of the associated
SqlCommand does not return a value, the SqlParameter contains a null value.
So I would guess that the value of @.BizName is not getting set. Use SQL
Profiler to verify this.
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Thanks Dan. Stating the size in the stored procedure did the trick. Cheers.
Nab
"Dan Guzman" wrote:

> A NULL @.BizAddress value will be returned when no data is found and this
> cannot be converted to a .Net string data type. You can check for NULL
> using DbNull.Value:
> If cmd.Parameters("@.BizAddress").Value Is DBNull.Value Then
> MessageBox.Show("BizName not found")
> Else
> TextBox2.Text = cmd.Parameters("@.BizAddress").Value
> End If
> Also, you need specify varchar(50) in your stored procedure parameter
> declaration. The default length is 1.
> CREATE PROCEDURE GetAddress
> @.BizName varchar(50),
> @.BizAddress varchar (50) output
> AS
> SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Nab" <Nab@.discussions.microsoft.com> wrote in message
> news:96300218-F090-44B3-A5E9-F30FCE80D711@.microsoft.com...
>
>