Difference between Execute Reader & XMLReader & NonQuery & Scaler & Query

Execute Reader..
1.Returns a datareader with data.
2.It is done by command object.
3.It is readonly.
4.It executes only select command.
5. This is known as a forward-only retrieval of records.It uses your sql statement to read through the table from the first to the last. 

Execute XMLReader..
1.Returns the recordset as a XML document.

2. Just Like Reader other than this

Execute NonQuery..
1.It will not return any data.
2.It is used with insert ,update and delete.
3.It returns only the number of rows affected.

Execute Scaler..
1.It returns only one value.
2.That value will the first column first row value.
3.This is very light-weight and is perfect when all your query asks for is one item.
4.  Select Count(*) or with in group by function

Execute Query..
1.Its for command objects.
2.It returns the value given by database through select statement.

Useful Links:
www.nigelwhitworth.com

Understanding Data Reader and Data Adapter

Data Reader: It take the data sequentially from the command object and requies the connection should be open while it is getting data.
Data Adapter: It fetch all the required data from database and pass it to DataSet.
DataSet: It get all the required data from Data Adapter. It contains data in the form of Data Table.




DataSet : it is a disconnected Architecture.
DataReader : it is a Connected Architecture. 

Connected Architecture : Means that while SqlDataReader reads the data from database and also reading data from SqlDataReader to any variable the connection to database should be open. Also once you read some data from SqlDataReader you should save them becouse it is not possible to go back and read it again.    

Disconnected Architecture : It is followed by Dataset. In this once the data has been read by Dataset from SqlDataAdapter we can close the connection to database and retirve the data from Dataset where ever we want. Dataset hold the data in the form of table and do not interact with Datasouse. 

For Details click