How to use ADO.NET to run a data set returned stored procedure ?

11 Jul 2006 3:34 AM

Cylix

I am going to use ADO.NET to call a sqlserver stored procedure with 1
parameter
The stored procedured will return a record set with one row data.

I have already get connection to the server and added the parameter,
but I cannot get the result by sqldatareader =
sqlcommand.executereader.
I ensure that there is one row return.

Besides, I would like to use the same sqlcommand to run a number of
query in difference function by declaire the variable in global, does
it good idea or I should declaire it in each function?

If it is good to do that, How can I close the sqlcommand and use the
same object in other function just like old ADODB rs.close?

Thank you.

11 Jul 2006 6:24 AM

Steven Nagy

Hi

First, try using a DataAdapter instead of a datareader.
Use it to fill a new dataset.
You can use the same command object to create a data adapter.

The data adapter will open and close the connection for you so you
don't need to do this.
If you need to reuse the command, you can probably use the "singleton"
pattern to define a class that can only be instantiated once and
reused. Google for singleton
In your little class, you can have a method that accepts the parameter
and returns a dataset. That method creates your data adapter and fills
the new dataset. As I said above, data adapters will open and close the
connection for you so you don't need to worry about it.

Cylix wrote:
Show quoteHide quote

> I am going to use ADO.NET to call a sqlserver stored procedure with 1
> parameter
> The stored procedured will return a record set with one row data.
>
> I have already get connection to the server and added the parameter,
> but I cannot get the result by sqldatareader =
> sqlcommand.executereader.
> I ensure that there is one row return.
>
> Besides, I would like to use the same sqlcommand to run a number of
> query in difference function by declaire the variable in global, does
> it good idea or I should declaire it in each function?
>
> If it is good to do that, How can I close the sqlcommand and use the
> same object in other function just like old ADODB rs.close?
>
> Thank you.