What is the difference between ExecuteScalar, ExecuteReader and ExecuteNonQuery?

• ExecuteScalar is typically used when your query returns a single value. If it returns more, then the result is the first column of the first row. An example might be SELECT @@IDENTITY AS 'Identity'.
• ExecuteReader is used for any result set with multiple rows/columns (e.g., SELECT col1, col2 from sometable).
• ExecuteNonQuery is typically used for SQL statements without results (e.g., UPDATE, INSERT, etc.).

ExecuteNonQuery():

1. will work with Action Queries only (Create,Alter,Drop,Insert,Update,Delete).
2. Returns the count of rows effected by the Query.
3. Return type is int
4. Return value is optional and can be assigned to an integer variable.

ExecuteReader():

1. will work with Action and Non-Action Queries (Select)
2. Returns the collection of rows selected by the Query.
3. Return type is DataReader.
4. Return value is compulsory and should be assigned to an another object DataReader.

ExecuteScalar():

1. will work with Non-Action Queries that contain aggregate functions.
2. Return the first row and first column value of the query result.
3. Return type is object.
4. Return value is compulsory and should be assigned to a variable of required type.

Reference URL:

http://nareshkamuni.blogspot.in/2012/05/what-is-difference-between.html

Each one is a different type execution.

• ExecuteScalar is going to be the typeof query which will be returning asingle value.

  An example would be returning a generated id after inserting.

  INSERT INTO my_profile (Address) VALUES ('123 Fake St.'); SELECT CAST(scope_identity() AS int)

• ExecuteReader gives you a data readerback which will allow you to read allof the columns of the results a rowat a time.

  An example would be pulling profile information for one or more users.

  SELECT * FROM my_profile WHERE id = '123456'

• ExecuteNonQuery is any SQL which isn't returning values, but isactually performing some form of worklike inserting deleting or modifyingsomething.

  An example would be updating a user's profile in the database.

  UPDATE my_profile SET Address = '123 Fake St.' WHERE id = '123456'