Name

Depth

Synopsis

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).