Now let’s take a look at the three Execute methods of the SqlCommand class: ExecuteNonQuery, ExecuteScalar, and ExecuteReader.

ExecuteNonQuery is used to execute a SQL statement or stored procedure that doesn’t

return any records. You’ll use this method when executing operations that update, insert, or

delete information in the database. ExecuteNonQuery returns an integer value that specifies how

many rows were affected by the query—this proves useful if you want to know, for example,

how many rows were deleted by the last delete operation. Of course, in case you don’t need to

know that number, you can simply ignore the return value. Here’s a simple piece of code that

shows how to open the connection, execute the command using ExecuteNonQuery, and imme-

diately close the connection afterward:

    connection.Open();

    command.ExecuteNonQuery();

    command.Close();

ExecuteScalar is like ExecuteNonQuery in that it returns a single value, although it returns a

value that has been read from the database instead of the number of affected rows. It is used in

conjunction with SELECT statements that select a single value. If SELECT returns more rows and/or

more columns, only the first column in the first row is returned. A typical SQL query that should be

executed using ExecuteScalar is SELECT COUNT(*) FROM Department—which returns the

number of rows in the Department table.

ExecuteReader is used with SELECT statements that return multiple records (with any

number of fields). ExecuteReader returns a SqlDataReader object, which contains the results of

the query. A SqlDataReader object reads and returns the results one by one, in a forward-only

and read-only manner. The good news about the SqlDataReader is that it represents the fastest

way to read data from the database, and the bad news is that it needs an open connection to

operate—no other database operations can be performed on that connection until the reader is closed. In our solution, you’ll load all the data returned by the SqlDataReader into a DataTable

object (which is capable of storing the data offline without needing an open connection),

which will allow you to close the database connection very quickly.

The DataTable class can store a result set locally without needing an open connection to

SQL Server, and it isn’t data provider–specific, like the other ADO.NET objects mentioned so

far (whose names begin with Sql because they’re SQL Server–specific).

 A “parent” of the DataTable object is the DataSet, which is a very smart object that represents

something like an in-memory database. DataSet is capable of storing data tables, their data types, relation-

ships between tables, and so on. Because of their complexity, DataSets consume a lot of memory, so it’s

good to avoid them when possible.

 

Not original,copied from, :Beginning ASP.NET E-Commerce in C# From Novice to Professional