DataReader

Hi all,

I read somewhere that we have to close the data reader explicitly before getting an output parameter from a stored procedure. I experimented, and this retrieves the right value:

sqlConnection1.Open();
SqlDataReader myReader = sc.ExecuteReader();
lblCatName.Text = "CatName: " + sc.Parameters["@.CatName"].Value;
lblReturn.Text = "Return: " + sc.Parameters["@.RETURN_VALUE"].Value;
myReader.Close();
sqlConnection1.Close();

I found the following in MSDN: " You must explicitly call the Close method when you are through using the SqlDataReader (...) The Close method fills in the values for output parameters (...)"

Am I missing something here? And if both ways work, what is better practice?

Thanks,you don't need a reader for output parameters.

simply sc.ExecuteNonReader();
lblCatName.Text = "CatName: " + sc.Parameters["@.CatName"].value;
...|||Hi,

Of course, you are correct!!! How did I miss that??!!
I tried returning some values from my stored procedure so that it would make sense to use the ExecuteReader, and it is true that the DataReader has to be closed before getting the output and return values!! (ExecuteNonQuery does not raise the problem I described because doesn't have to be closed)

Thanks for your help!!

Bea|||well here's the truth behind it on SQL Server side at least...

If you execute some command, text or a stored procedure, it sort of looks like this (depending on the provider)

DECLARE @.p1 DATATYPE
DECLARE @.p2 DATATYPE
SET @.p1 = <VALUE FOR PARAMETER p1>
SET @.p2 = <VALUE FOR PARAMETER p2>
-- do the command here i.e.
EXEC StoredProcedure @.p1, @.p2 OUTPUT
SELECT @.p1, @.p2 -- this is how it gets its output parameters

tricky eh... well this means that you can't get the output parameters until you close the data reader that is executing from the EXEC StoredProcedure @.p1, @.p2 OUTPUT ...

does this make sense? yes... this is because it does it in order of operation, and you may get zero or more datareaders from the StoredProcedure, while in other providers (Oracle, Odbc) the implementation may be different and thus it may be able to return the output parameters before it streams the readers ...

imagine:

CREATE PROCEDURE StoredProcedure (@.p1 INT, @.p2 INT OUTPUT) AS
BEGIN
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND @.p1 = 1
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), N'IsView') = @.p1

SET @.p2 = 5
END

execute this in a stream reader or soemthing like it ... if you pause on the first datareader ... assuming it returns something ... it will not execute the rest of the procedure until you close the stream or ask for the next reader... then it will stream you the next data reader and then you ask for another or close ... once you close you go outside the procedure and it finishes executing ...

so essentially what it should/we would like it to do is

EXECUTE COMMAND:
- GET ALL READERS
- REMOVE THE LAST READER FROM THE LIST OF AVAILABLE READERS AND STORE THE OUTPUT PARAMETERS CHANGES
- ALLOW ACCESS TO THE REMAINDING AVAILABLE READERS

You can see proof of all this if you execute SQL Profiler and an application in debug mode... try stepping through the executing of the procedure and the retrieval of the data reader(s)... also add watches to each of the parameters... to see when their values change...|||Thank you so much for your very complete post! It was extremely helpful!!

Bea

Database Variable

Hi,

I have a sql script that references a database explicitly, but the name of that database changes. Rather then have to find anywhere I reference that database and change it, i would like to just set it in one spot.

For example.

SELECT * FROM [V1_Database].[a_table]

I then decide to upgrade from V1 to V2.

Instead of having to find the above code in all of my stored procs and manually change it, I would like to do the following.

SET dbName = 'V2_Database' (Database wide variable)

SELECT * FROM [dbName].[a_table]

But I don't want to have to make all of the places that reference a specific database into string executions. ( strSQL = 'SELECT * FROM [' + dbName + ']' )

I hope i havent made this too confusing, but any help is appreciated.

Thanks,

Brian

You could put the connectionstring in web.config file and use it across your application. That way you would only need to change at one place.