Introduction

• We covered DbConnection in some detail previously.
• Here we cover details on some of the other classes used by connected mode.
• There is more than we can possibly cover in detail. See the docs.
• We'll see classes related to transactions and parameters later.

Commands

How to create a command:

Use CreateCommand on a connection:

 IDbConnection conn = new SqlConnection("connection string");
 IDbCommand cmd = conn.CreateCommand();
 cmd.CommandText = "Select Id, FirstName, LastName from Student";
 cmd.CommandType = CommandType.Text; // default

or use a specific c-tor:

SqlConnection cnn = new SqlConnection("connection string");
IDbCommand cmd = new SqlCommand("Select Id, FirstName, LastName from Student",conn);

or:

IDbCommand cmd = new SqlCommand(); 
cmd.CommandText = "Select Id, FirstName, LastName from Student";
cmd.CommandType = CommandType.Text; // default
cmd.Connection = conn;

or use a factory:

DbProviderFactory factory = DbProviderFactories.GetFactory(provider);
DbConnection conn = factory.CreateConnection();
conn.ConnectionString = connStr;

DbCommand cmd = factory.CreateCommand();
cmd.Connection = conn;
cmd.CommandText = "select * from Student";

There are plenty of methods and properties to choose from. A sampling:

Method/property	Description
CommandText	Gets or sets the text command to run against the data source.
CommandTimeout	Gets or sets the wait time before terminating the attempt to execute a command and generating an error.
CommandType	Indicates or specifies how the CommandText property is interpreted. Values are from the CommandType enumeration: Text (default), TableDirect, StoredProcedure.
Connection	Gets or sets the IDbConnection used by this instance of the IDbCommand.
Parameters	Gets the IDataParameterCollection.
Transaction	Gets or sets the transaction within which the Command object of a .NET Framework data provider executes.
UpdatedRowSource	Gets or sets how command results are applied to the DataRow when used by the Update method of a DbDataAdapter. (Wait until we do DataSets.)
Cancel()	Attempts to cancels the execution of an IDbCommand.
CreateParameter()	Creates a new instance of an IDbDataParameter object.
Prepare()	Creates a prepared (or compiled) version of the command on the data source.

Each IDbCommand exposes several Execute... methods to perform the action in the CommandText.

Method	Description
ExecuteReader	Executes the CommandText against the Connection and builds an IDataReader. An IDataReader is used for forward-only, read-only access to the result set. For SELECT queries and stored procedures that return rows.
ExecuteNonQuery	Executes an SQL statement against the Connection object of a .NET Framework data provider, and returns the number of rows affected. For commands that do not return rows.
ExecuteScalar	Executes the query, and returns the first column of the first row in the resultset returned by the query. Extra columns or rows are ignored. Usually used to execute commands that return a singleton value.

Data readers

IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); //1


while ( reader.Read() ) //2
{
	Console.WriteLine("{0}\t{1}",reader.GetInt32(0),reader.GetString(1)); //3
	Console.WriteLine("{0}\t{1}",reader["CategoryID"],reader["CategoryName"]); //4
}

reader.Close(); //5

1. ExecuteReader take several arguments that affect the operation of the reader. Values are from the CommandBehavior enumeration (see below). Values may be or'd.
2. Read will return true as long as rows remain.
3. There are two primary ways to get the values of data in a row. Ordinal access is most efficient. There are methods GetInt32, GetString, etc. for all the primitive types. All take the column index as an argument.
4. The reader has an indexer that allows the column name to be used. Less efficient.
5. Always close. In this case, because CommandBehavior.CloseConnection was specified, the connection will also be closed.

Values in the CommandBehavior enumeration:

Value	Description
CloseConnection	When the command is executed, the associated Connection object is closed when the associated DataReader object is closed.
Default	The query may return multiple result sets. Execution of the query may effect the database state. Default sets no CommandBehavior flags, so calling ExecuteReader(CommandBehavior.Default) is functionally equivalent to calling ExecuteReader().
KeyInfo	The query returns column and primary key information. The query is executed without any locking on the selected rows. Note When using KeyInfo, the .NET Framework Data Provider for SQL Server appends a FOR BROWSE clause to the statement being executed. The user should be aware of potential side effects, such as interference with the use of SET FMTONLY ON statements. See SQL Server Books Online for more information.
SchemaOnly	The query returns column information only and does not effect the database state.
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.
SingleResult	The query returns a single result set.
SingleRow	The query is expected to return a single row. Execution of the query may effect 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 return multiple result sets. In that case, multiple result sets are still returned, but each result set has a single row.

Methods/properties in IDataReader/IDataRecord:

Method/property	Description
Depth	Gets a value indicating the depth of nesting for the current row.This is for OLE DB that allows navigating hierarchical relationships.
bool IsClosed	Gets a value indicating whether the data reader is closed.
int RecordsAffected	Gets the number of rows changed, inserted, or deleted by execution of the SQL statement.
void Close()	Closes the IDataReader 0bject.
DataTable GetSchemaTable()	Returns a DataTable that describes the column metadata of the IDataReader. See below.
bool NextResult()	Advances the data reader to the next result, when reading the results of batch SQL statements. See below.
bool Read()	Advances the IDataReader to the next record.
int FieldCount	Gets the number of columns in the current row.
object Item(string name)	Overloaded. Gets the specified column. In C#, this property is the indexer for the IDataRecord class.
bool GetBoolean(int i) DateTime GetDateTime(int i) int GetInt32(int i) string GetString(int i) etc.	Take an index, retrieves the value. Beware of nulls and type mismatches.
string GetDataTypeName(int i)	Gets the data type information for the specified field.
Type GetFieldType(int i)	Gets the Type information corresponding to the type of Object that would be returned from GetValue.
string GetName(int i)	Gets the name for the field to find.
int GetOrdinal(string name)	Return the index of the named field.
object GetValue(int i)	Return the value of the specified field.
int GetValues(object[] values)	Gets all the attribute fields in the collection for the current record.
bool IsDBNull(int i)	Return whether the specified field is set to null.

Output parameters, if any , won't be available until the DataReader is closed.

While a DataReader is open, the Connection is in use exclusively. You won't be able to issue any other commands for the Connection, including creating another DataReader, until the original DataReader is closed.

Specific implementations of IDataReader/IDataRecord, such as SqlDataReader, provide additional methods. For example, to have more efficient access to SQL Server data types, you can use GetSqlInt32, etc.

Null values

Note the DbDataReader.IsDBNull(int i) method. This is one way to check if a null value is returned for a field in the current row.

If you use GetInt32 or company to retrieve a field with a null value, you will get an error.

If you use GetValue or GetValues, you will get a null value. That null value is the singleton instance of the DbNull class. You can access that singleton via the static property DbNull.Value.

Closing connections

• You should close connections when you are done with them. This returns them to the pool.
• You can close the connection yourself.
• You can set the CommandBehavior.CloseConnection flag when you create a reader.
• You can use a with:

  using ( DbConnection conn = GetDbConnection() )
  {
      conn.Open();  
       // do things with the connection
       // don't worry about closing it
  } // exiting the scope of the with will close the connection