21.2 Properties Reference

int32 depth = DataReader.Depth;

Indicates the depth of nesting for the current row. This is 0 by default. Many providers, including the SQL Server provider, always return 0 because they don't support nesting.

One case in which you might use nesting is when using the MSDataShape OLE DB provider to create a hierarchical result set, which use chapters (OLE DB type DBTYPE_HCHAPTER, ADO type adChapter). When returning a result set that includes chapters, the nested recordset is exposed as a column inside the first recordset.

Example

The following code shows a complete example that demonstrates nesting with a shaped record set. The custReader.Depth property returns 0; the ordReader.Depth property returns 1.

string connectionString = "Provider=MSDataShape;Data Provider=SQLOLEDB;" +
  "Data Source=localhost;Integrated Security=SSPI;" +
  "Initial Catalog=northwind";

string SQL = "SHAPE {SELECT CustomerID, CompanyName FROM Customers} " +
  "APPEND ({SELECT CustomerID, OrderID FROM Orders} AS CustomerOrders " +
  "RELATE CustomerID TO CustomerID)";

OleDbConnection con = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand(SQL, con);

con.Open();

OleDbDataReader custReader = cmd.ExecuteReader();
OleDbDataReader ordReader;

while (custReader.Read())
{
    Console.WriteLine("Orders for " + custReader.GetString(1)); 
    Console.WriteLine("Depth custReader: " + custReader.Depth.ToString());
    ordReader = (OleDbDataReader)custReader.GetValue(2);      
    Console.WriteLine("Depth ordReader: " + ordReader.Depth.ToString());

    while (ordReader.Read())
    {
        Console.WriteLine(ordReader.Depth);
    }   
    ordReader.Close();
}

custReader.Close();
con.Close();

Note

For more information on shaped recordsets and ADO.NET, you may want to refer to Microsoft Knowledge Base article Q308045 (see http://support.microsoft.com).

Int32 fieldCount = DataReader.FieldCount;

Returns the number of columns in the current row. If you aren't currently positioned on a valid row, the FieldCount returns 0.

Example

The following code displays the number of rows, which is 2, because the query includes only two columns:

string SQL = "SELECT CustomerID, ContactTitle FROM Customers";

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

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

Console.WriteLine(r.FieldCount.ToString() + " rows were returned.");

con.Close();

bool isClosed = DataReader.IsClosed;

Returns true if the DataReader has been closed, either by calling the DataReader.Close( ) method or calling Close( ) on the underlying connection.

Note

RecordsAffected and IsClosed are the only two properties that can safely be called if the DataReader is closed.

bool hasRows = DataReader.HasRows

Returns true if the DataReader has at least one row. This convenience feature (which was added in the .NET Framework 1.1) is provided so you can quickly tell if there is a result set without advancing to the first row. This is primarily useful when you need to decide whether or not to pass the DataReader to another function or component that will process it.

object value = DataReader[int columnOrdinal];
object value = DataReader[string columnName];

The Item property is also the default indexer for the DataReader. It allows you to retrieve a value from any field by specifying the column name or column ordinal. This value is returned as an object and is stored in the native provider-specific format. You can cast this object to the desired type.

Example

Here is an example of how to retrieve a value from the OrderPrice field and convert it to the .NET decimal type:

decimal price = (decimal)r["OrderPrice"];

You can also retrieve the same information using a column ordinal. This is a zero-based number that corresponds to the order in which fields were retrieved. Assuming the OrderPrice field is the second field in the result set, use the following equivalent syntax:

decimal price = (decimal)r[1];

Int32 numRecords = DataReader.RecordsAffected;

Returns the number of rows that are changed, inserted, or deleted. It returns 0 if no rows are affected or -1 if the command simply corresponds to a SQL SELECT statement. This property is available only after all rows are read, and the DataReader is closed.

Note

RecordsAffected and IsClosed are the only two properties that can be retrieved safely if the DataReader is closed.