You use Microsoft Visual Studio 2010 and Microsoft .NET Framework 4 to create an application. The
application connects to a Microsoft SQL Server 2008 database. The database includes a table named
dbo. Documents that contains a column with large binary data. You are creating the Data Access
Layer (DAL). You add the following code segment to query the dbo.Documents table. (Line numbers
are included for reference only.)
01 public void LoadDocuments(DbConnection cnx)
02 {
03 var cmd = cnx.CreateCommand();
04 cmd.CommandText = “SELECT * FROM dbo.Documents”;
05 …
06 cnx.Open();
07 …
08 ReadDocument(reader);
09 }
You need to ensure that data can be read as a stream. Which code segment should you insert at line
07?
A.
var reader = cmd.ExecuteReader(CommandBehavior.Default);
B.
var reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
C.
var reader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
D.
var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
Explanation:
CommandBehavior:
Default The query may return multiple result sets. Execution of the query may affect the database
state. Default sets no CommandBehavior flags, so calling ExecuteReader(CommandBehavior.Default)
is functionally equivalent to
calling ExecuteReader().
SingleResult The query returns a single result set.
SchemaOnly The query returns column information only. When using SchemaOnly, the .NET
Framework
Data Provider for SQL Server precedes the statement being executed with SET FMTONLY ON.
KeyInfo The query returns column and primary key information. When KeyInfo is used for command
execution, the provider will append extra columns to the result set for existing primary key and
timestamp columns. When using KeyInfo, the .NET Framework Data Provider
for SQL Server precedes the statement being executed with SET FMTONLY OFF and SET
NO_BROWSETABLE ON.
The user should be aware of potential side effects, such as interference with the use of SET
FMTONLY ON statements.
SingleRow The query is expected to return a single row of the first result set. Execution of the query
may affect the database state.Some .NET Framework data providers may, but are not required to, use this information to
optimize the performance of the command.
When you specify SingleRow with the ExecuteReader method of the OleDbCommand object, the
.NET Framework Data Provider for
OLE DB performs binding using the OLE DB IRow interface if it is available. Otherwise, it uses the
IRowset interface.
If your SQL statement is expected to return only a single row, specifying SingleRow can also improve
application performance.
It is possible to specify SingleRow when executing queries that are expected to return multiple result
sets.
In that case, where both a multi-result set SQL query and single row are specified, the result
returned will contain only the first row of the first result set. The other result sets of the query will
not be returned.
SequentialAccess Provides a way for the DataReader to handle rows that contain columns with large
binary values. Rather than loading the entire row,
SequentialAccess enables the DataReader to load data as a stream. You can then use the
GetBytes or GetChars method to specify a byte location to start the read operation, and a limited
buffer size for the data being returned.
When you specify SequentialAccess, you are required to read from the columns in the order
they are returned, although you are not required to read each column. Once you have read past a
location in the returned stream of data, data at or before that location can no longer be read from
the DataReader.
When using the OleDbDataReader, you can reread the current column value until reading past
it. When using the SqlDataReader, you can read a column value can only once.
CloseConnection When the command is executed, the associated Connection object is closed when
the associated DataReader object is closed.
CommandBehavior Enumeration
(http://msdn.microsoft.com/en-us/library/system.data.commandbehavior.aspx)