21.3 Methods Reference

DataReader.Close();

Closes the DataReader, but leaves the connection open. You must call Close( ) before you can use the connection for another command. However, if you want to close the DataReader and the connection, simply call the Connection.Close( ) method instead.

Example

The following code shows how to close a DataReader and reuse the connection for another task:

con.Open();

SqlDataReader r = cmdA.ExecuteReader();

// (Process rows here.)

r.Close();

// Reuse the open connection for another query.
r = cmdB.ExecuteReader();

con.Close();

Object value = DataReader.GetTypeName(Int32 columnOrdinal);

Every DataReader has a set of strongly typed accessors that allow you to retrieve column values without performing any sort of conversion, potentially improving performance. For example, you can use the GetInt32( ) method to retrieve a column value that holds a 32-bit integer. This call succeeds only if the column contains a 32-bit integer; otherwise, an exception occurs.

Many providers define additional accessor methods that are customized to use database-specific data types. You can recognize these because they will include the provider abbreviation. For example, the SQL Server uses the abbreviation Sql and provides strongly typed accessors such as GetSqlGuid( ), GetSqlMoney( ), and GetSqlDateTime( ). These methods return values using the native SQL data types defined in the System.Data.SqlTypes namespace. For a complete example on how to use these types with a DataReader, refer to Chapter 5.

When using the strongly typed accessors, you must specify the column index. You can't look up a value by column name. However, you can use the GetOrdinal( ) method to retrieve the column ordinal for a column with a specific name.

Example

The following code retrieves the second column as a string:

string value = r.GetString(1);

Notes

It isn't necessary to use strongly typed accessors. You can use the indexer, unless you have reason to be concerned that the conversion to a .NET type could introduce a rounding error.

Before calling a strongly typed accessor on a field that can contain null values, you should use the IsDBNull( ) method.

String typeName = DataReader.GetDataTypeName(Int32 columnOrdinal);

Retrieves the name of the native data type used for a specified column. In C#, you can also use the typeof( ) operator to retrieve type information.

Example

The following code displays the data type of the second column:

Console.WriteLine(r.GetDataTypeName(1));

String columnName = DataReader.GetName(Int32 columnOrdinal);

Returns the name of a column at a specified index. One reason to use this method is to display column headings when iterating through a result set by index number.

Example

The following code statement generically prints every column retrieved from a query and its column name:

con.Open();
r = cmd.ExecuteReader();

while (r.Read())
{
    for (int i = 1; i <= r.FieldCount - 1; i++)
    {
        Console.Write(r.GetName(i).ToString() + ": ");
        Console.WriteLine(r[i].ToString());
    }
    Console.WriteLine();
}

con.Close();

Int32 ordinal = DataReader.GetOrdinal(String columnName);

Retrieves the zero-based ordinal for the column with the specific name. This is useful for two reasons. First of all, many DataReader methods require the use of column ordinals, not field names. Second, access via a column ordinal is likely to perform faster. In fact, when you use a column name, ADO.NET performs a hashtable lookup behind the scenes to determine the correct column ordinal. Using GetOrdinal( ), you can perform this lookup once, rather than every time you need to access a field.

Example

The following code shows a simple example of how you might access a column using the column ordinal, even if you only know its column name:

// Perform the ordinal lookups.
int colID = r.GetOrdinal("CustomerID");
int colFirstName = r.GetOrdinal("FirstName");
int colSecondName = r.GetOrdinal("SecondName");

while (r.Read())
{
    // Use the ordinals far faster column value access.
    Console.WriteLine(r[colID].ToString());
    Console.WriteLine(r[colFirstName].ToString() + " " +
      r[colSecondName].ToString());
    Console.WriteLine();
}

Note

Columns are returned in the same order they appear in a SELECT statement.

DataTable dt = DataReader.GetSchemaTable();

Returns a DataTable that contains metadata for the current query. This table contains one row for each column in the result set and several fields that describe details such as column names and data types. Table 21-2 lists all columns returned in the schema DataTable, in order.

Table 21-2. Schema columns

Column	Description
ColumnName	The name of the column. If the query renamed the column using the AS keyword, this is the new name.
ColumnOrdinal	The ordinal number of the column.
ColumnSize	The maximum allowed length of values in the column or the size of the data type for fixed-length data type.
NumericPrecision	The maximum precision (number of digits) of the column for a numeric data type or null for all other data types.
NumericScale	The maximum scale (number of digits to the right of the decimal point) of the column for a numeric data type, or null for all other data types.
IsUnique	Indicates whether or not column values can be duplicated.
IsKey	Indicates whether or not this column is part of the primary key for the table.
BaseCatalogName	The name of the database that contains this table, or null if it can't be determined.
BaseColumnName	The name of the column in the data source. If the query renamed the column using the AS keyword, this is the original name.
BaseSchemaName	The name of the schema in the data source, or null if it can't be determined.
BaseTableName	The name of the table or view in the data source that contains this column, or null if it can't be determined.
DataType	The mapped .NET framework type.
AllowDBNull	Indicates whether null values are accepted for column values.
ProviderType	Indicates the provider-specific data type.
IsAliased	True if the column has been renamed using the AS keyword.
IsExpression	True if the column is calculated based on an expression.
IsIdentity	True if the column is an identity value generated by the data source.
IsAutoIncrement	True if column values are assigned by the data source in fixed increments.
IsRowVersion	True if the column contains a read-only row identifier.
IsHidden	True if the column is hidden.
IsLong	True if the column contains a binary long object (BLOB).
IsReadOnly	True if the column can't be modified.

Example

The following example retrieves a schema table and displays the returned information. The schema information describes the columns from the Customers table.

SqlCommand cmd = new SqlCommand("SELECT * FROM CUSTOMERS", con);

// Get the schema table.
con.Open();
SqlDataReader r = cmd.ExecuteReader();
DataTable schema = r.GetSchemaTable();
con.Close();

// Display schema table information.
foreach (DataRow row in schema.Rows)
{
    foreach (DataColumn col in schema.Columns)
    {
        Console.WriteLine(col.ColumnName + " = " + row[col].ToString());
    }
    Console.WriteLine();
}

Object value = DataReader.GetValue(Int32 columnOrdinal);

Retrieves a single value from a column as a .NET framework type. This method is rarely needed because the indexer provides more convenient access.

Note

Some providers also provide strongly typed versions of the GetValue( ) method, which you can recognize based on the provider prefix. For example, SQL Server provides a GetSqlValue( ) method that retrieves a column values as a SQL Server-specific type.

Int32 numberOfValues = DataReader.GetValues(Object[] values);

This method provides an efficient way to retrieve all the values in a row at once rather than access each column value separately. The values are retrieved into an array of objects, which you must supply as an argument. The GetValues( ) method returns the number of values used to fill the array.

Before you use the GetValues( ) method, you should make sure the array length is the correct size. If the array length is less than the number of required columns, all the values will not be retrieved. Instead, the available slots in the array are filled with the corresponding column values, and all additional column values are ignored. No exception is thrown. You can also pass an object array that has a length greater than the number of columns contained in the resulting row without generating an error.

Example

The following example retrieves all the column values for a row into an object array, and then adds this array to an ArrayList collection. The information for each row is added to the ArrayList in this fashion.

string SQL = "SELECT * FROM Customers";

SqlCommand cmd = new SqlCommand(SQL, con);
ArrayList rows = new ArrayList();
con.Open();
SqlDataReader r = cmd.ExecuteReader();

while (r.Read())
{
    object[] values = new object[r.FieldCount];
    r.GetValues(values);
    rows.Add(values);
}

con.Close();

Console.WriteLine("Data retrieved for " + rows.Count.ToString() + " rows");

Notes

Some providers also provide strongly typed versions of the GetValue( ) method, which you can recognize based on the provider prefix. For example, SQL Server provides a GetSqlValue( ) method.

There is no DataReader method that allows you to retrieve multiple rows into an array.

Boolean = DataReader.IsDBNull(Int32 columnOrdinal);

Returns a Boolean value that indicates whether the indicated column contains a null value. You can call this method to check for a null value before you call a typed accessor method such as GetByte( ) or GetChar( ) and thereby avoid raising an error.

Example

The following code tests for a null value before attempting to retrieve an integer value:

int rowVal;

if (r.IsDbNull(i))
{
    // Use default value. Row is null.
    rowVal = 0;
}
else
{
    // Use database value.
    rowVal = (int)r[i];
}

Boolean moreResultSets = DataReader.NextResult();

Moves the reader to the next result set. A DataReader returns multiple result sets only if you use a batch query or if you invoke a stored procedure that includes more than one SELECT query. By default, the DataReader begins on the first result set. NextResult( ) returns true if there are more result sets.

Example

Here's an example that retrieves multiple result sets using a batch query:

// Define a batch query.
string SQL = "SELECT * FROM Categories; SELECT * FROM Products";

SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(SQL, con);

con.Open();

// Execute the batch query.
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
    // (Process the category rows here.)
}

reader.NextResult();

while (reader.Read())
{
    // (Process the product rows here.)
}

Boolean moreRecords = DataReader.Read();

Moves to the next record. If no record can be found, it returns false. Otherwise, it returns true. When the DataReader is first created, it is positioned just before the first row. You must call Read( ) before you can retrieve information from the first row. (The first Read( ) call advances the DataReader to the first record, if any.)

Example

The following code shows the basic pattern of access for reading rows with the DataReader. The Read( ) method is invoked as part of a while loop, ensuring that the loop ends immediately when the Read( ) method returns false.

string SQL = "SELECT ContactName FROM Customers";

SqlCommand cmd = new SqlCommand(SQL, con);
SqlDataReader r;

try
{

    con.Open();
    r = cmd.ExecuteReader();

    // Iterate over the results.
    while (r.Read())
    {
        Console.WriteLine(r["ContactName"].ToString());
    }
}
finally
{
    con.Close();
}

Because the DataReader encapsulates a live connection, you should read all the information as quickly as possible and close the connection immediately after.

Note

The DataReader provides only a single record at a time. Once the DataReader has been moved forward, you can't retrieve a value from a previous row.