Data Reader: Forward-only data read
Equivalent to: Recordset's CursorType = adOpenForwardOnly, LockType = adLockReadOnly (often referred to as a "fire-hose" cursor)
DataReader Four Execute Methods through Command object:
ExecuteReader - Simply executes the SQL query against the database, using the Read() method to traverse through data, as illustrated below
ExecuteNonQuery - Used whenever you work with SQL stored procedures with parameters, as illustrated in the Stored Procedures section below
ExecuteScalar - Returns a lightning fast single value as an object from your database Ex. object val = Command.ExecuteScalar(); Then check if != null.
ExecuteXmlReader - Executes the SQL query against SQL Server only, while returning an XmlReader object. See .NET documentation for more information
Other Important methods of Command:
Prepare - Equivalent to ADO's Command.Prepared = True property. Useful in caching the SQL command so it runs faster when called more than once. Ex. Command.Prepare();
Dispose - Releases the resources on the Command object. Used to force garbage collecting, ensuring no resources are being held after our connection is used. Incidentally, by using the Dispose method you automatically call the Connection object's Close method as well.
Usage:
SqlCommand objCommand = new SqlCommand(Sql String, objConnect);
SqlDataReader objDataReader = objCommand.ExecuteReader();
while (objDataReader.Read() == true) {
Response.Write (objDataReader[0].ToString() + "
");
}
DataReader Methods: Read, HasRows,IsClosed, NextResult, Closed
Good way of closing Reader object:
objDataReader.Close();
objDataReader = null;
objCommand.Dispose();
objCommand = null;
objConnect.Close();
objConnect= null;