Running stored procedure multiple times

I'm binding the distinct values from each of 9 columns to 9 drop-down-lists using a stored procedure. The SP accepts two parameters, one of which is the column name. I'm using the code below, which is opening and closing the database connection 9 times. Is there a more efficient way of doing this?

newSqlCommand = New SqlCommand("getDistinctValues", newConn)
newSqlCommand.CommandType = CommandType.StoredProcedure

Dim ownrParam As New SqlParameter("@.owner_id", SqlDbType.Int)
Dim colParam As New SqlParameter("@.column_name", SqlDbType.VarChar)
newSqlCommand.Parameters.Add(ownrParam)
newSqlCommand.Parameters.Add(colParam)

ownrParam.Value = OwnerID

colParam.Value = "Make"
newConn.Open()
ddlMake.DataSource = newSqlCommand.ExecuteReader()
ddlMake.DataTextField = "distinct_result"
ddlMake.DataBind()
newConn.Close()

colParam.Value = "Model"
newConn.Open()
ddlModel.DataSource = newSqlCommand.ExecuteReader()
ddlModel.DataTextField = "distinct_result"
ddlModel.DataBind()
newConn.Close()

and so on for 9 columns…

First of all, reusing the connection will increase your performance significantly.

|||

Hi

if you create a stored procedure returning multiple result sets and load these result sets into the DataSet, you will avoid multiple roundtrips to SQL server.

-yuriy

|||

Under ADO.NET, only the first Open opens the database, the rest pick up a connection from a pool as closing the database connection does not actually close the connection but returns it to a pool. You still open and close in your code, but what happens behind the scenes is slightly different from previous access methods such as ADO.

|||

Thanks guys.

Tatworth, I believe you are saying that I'm reusing the same connection by doing it this way. In that case, is it an acceptable and reasonably efficient way to do things?

I need the DISTINCT values for these columns, so I can't simply select all the values for all 9 columns at once, unless I'm prepared to eliminate repeated values in code. Is it possible for a single stored procedure to return 9 sets of distinct values?

|||

>Tatworth, I believe you are saying that I'm reusing the sameconnection by doing it this way. In that case, is it an acceptable andreasonably efficient way to do things? yes

> need the DISTINCT values for these columns, so I can't simplyselect all the values for all 9 columns at once, unless I'm prepared toeliminate repeated values in code. Is it possible for a single storedprocedure to return 9 sets of distinct values?
It is possible as a stored procedure can return multiple recordsets but not practical as ASP.NET can only read the first recordset without extra code - so keep doing it the way you are doing it now.

I will be unable to access this site for some days, so I suggest you mark this thread as closed and open a new thread if you have further queries.

|||

There are multiple approaches to handling the above depending on your situation, and the data involved.

Normally, I would use a sqldatasource (9 of them), and bind the dropdowns to the datasources, and evaluate from there if necessary. The SqlDatasource control is nice because you can quickly and easily add caching if necessary (If you think that the ownerid will be called multiple times in a short period of time and the results should always remain the same for each call, then it'd be very helpful).

However, since you've gone down the approach of handling it manually, the only thing that is going to really buy you anything (other than coding any caching yourself), is to return the results in a single trip from the database. One such method would be to do something like return a single resultset that has 2 values, one being the column name, the other the value (one of the distinct values) likes:
col1 val1

col1 val2

col1 val3

col2 val1

col2 val2

Then you can retrieve this resultset from the database into a dataset. With the dataset, you can then derive a view that has a filter clause (Like a WHERE clause), and bind to the dataview.

Another approach, although I have not personally tested this is to use the datareaders nextresult property. Like:

conn.open

dim dr as sqldatareader=cmd.executereader

{Your code to prep control 1 here}

control1.datasource=dr

control1.databind

{Ending stuff for control 1 here}

dr.nextresult

{Your code to prep control 2 here}

control2.datasource=dr

control2.databind

{Ending stuff for contorl 2 here}

dr.nextresult

...

This approach would be extremely easy to modify your current code into, as you would just make need to make a master stored procedure like:

CREATE PROCEDURE dbo.sp_TheMaster ( @.ownerid int) AS

EXEC sp_OriginalProcedure @.ownerid,'column1'

EXEC sp_OriginalProcedure @.ownerid,'column2'

...

That would return the 9 resultsets that you need, although I'm not sure of it's performance, but I suspect it would be better.

|||

Thanks for your detailed response Motley. I considered the sqldatasource – I think it would be possible to use a single datasource for all 9 queries – but I chose the RP option only because it seems tidier to me (less 'markup'). I'm assuming there would be no performance difference?

As time is an issue, and as it seems that what I'm doing is not disastrous in performance terms, I'll leave it and monitor it when the site is uploaded.

Thanks all for your input.