Asp.net Stored Procedure instance application

create procedure datretrieveproductsxml
as
select * from products 
for xml auto
go

Use xmlreader to retrieve xml data
1. Create a sqlcommand object to call the stored procedure that can generate an xml result set (for example, use the for xml clause in a select statement ). Associate the sqlcommand object with a connection.
 
2. Call the executexmlreader method of the sqlcommand object and assign the result to the xmltextreader object only. When you do not need to perform any xml-based verification on the returned data, this is the fastest type of xmlreader object to be used.
 
3. Use the read method of the xmltextreader object to read data.
How to use stored procedure output parameters to retrieve a single row
With the help of named output parameters, you can call the stored procedure to return the retrieved data items in a single row. The following code snippet uses a stored procedure to retrieve the product name and unit price of a specific product contained in the products table of the northwind database tutorial.

 1 void getproductdetails( int productid, 
 2                         out string productname, out decimal unitprice )
 3 {
 4   using( sqlconnection conn = new sqlconnection(
 5         "server=(local);integrated security=sspi;database=northwind") )
 6   {
 7     // set up the command object used to execute the stored proc
 8     sqlcommand cmd = new sqlcommand( "datgetproductdetailsspoutput", conn )
 9     cmd.commandtype = commandtype.storedprocedure;
10     // establish stored proc parameters.
11     //  @productid int input
12     //  @productname nvarchar(40) output
13     //  @unitprice money output
14 
15     // must explicitly set the direction of output parameters
16     sqlparameter paramprodid = 
17              cmd.parameters.add( "@productid", productid );
18     paramprodid.direction = parameterdirection.input;
19     sqlparameter paramprodname = 
20              cmd.parameters.add( "@productname", sqldbtype.varchar, 40 );
21     paramprodname.direction = parameterdirection.output;
22     sqlparameter paramunitprice = 
23              cmd.parameters.add( "@unitprice", sqldbtype.money );
24     paramunitprice.direction = parameterdirection.output;
25 
26     conn.open();
27     // use executenonquery to run the command. 
28     // although no rows are returned any mapped output parameters 
29     // (and potentially return values) are populated 
30     cmd.executenonquery( );
31     // return output parameters from stored proc
32     productname = paramprodname.value.tostring();
33     unitprice = (decimal)paramunitprice.value; 
34   }
35 }

Use Stored Procedure output parameters to retrieve a single row
1. Create a sqlcommand object and associate it with a sqlconnection object.
 
2. You can call the add method of the parameters set of sqlcommand to set the stored procedure parameters. By default, all parameters are assumed as input parameters. Therefore, you must explicitly set the direction of any output parameter. Note that a good practice is to explicitly set the direction of all parameters (including input parameters.
 
3. Open the connection.
 
4. Call the executenonquery method of the sqlcommand object. This will fill in the output parameters (and may fill in the returned values ).
 
5. Use the value attribute to retrieve output parameters from an appropriate sqlparameter object.
 
6. Close the connection.
The preceding code snippet calls the following stored procedure.

 1 create procedure datgetproductdetailsspoutput
 2 @productid int,
 3 @productname nvarchar(40) output,
 4 @unitprice money output
 5 as
 6 select @productname = productname, 
 7        @unitprice = unitprice 
 8 from products 
 9 where productid = @productid
10 go