Few days back I went for an Interview in HCL(2yr Exp) and this was one of questions that was asked :
What is Command Behavior in ADO.NET?
Command Behavior Enumeration is associated with DataReader and provided by System.Data.
The most common use of Command Behavior is to close the connection of DataReader automatically.
SqlDataReader dr = mycommand.ExecuteReader(CommandBehavior.<member>)
As SQLDataReader comes under Connected Model so it must have a open Connection then accessing the data. There are times when you use DataReader number of times and you forgot to open and close, it would generate an error. Though you need to open the connection every time you use a DataReader but by using CommandBehavior you can skip the manual closing of connection you just have to close the DataReader.This will save the resources.
Do remember one important thing that you can't use more than one DataReader with same connection at the same time.
The Members of CommandBehaviour Enumeration are: CloseConnection, SchemaOnly, SingleRow, SingleResult, Default, Keyinfo, and SequentialAccess.
You can use SchemaOnly to get the Schema Information(Name,Type and Size) of a column and SingleRow to return only a row from query.
Here is an example for CloseConnection:
SqlCommand cmd = new SqlCommand(queryString, SqlConnection cn);
cn.Open();
SqlDataReader rd =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (rd.Read())
{
Console.WriteLine(rd.GetString(0));
} rd.close(); Console.WriteLine(cn.State); Result: The Final Line of the output would be
ClosedAs you can see by closing the DataReader we closed the Connection 'cn'.
You can find Other Questions under 'HCL Interview' label in this blog.
