Microsoft.NET

……………………………………………….Expertise in .NET Technologies

ADO.NET Core Classes:DataReaders – Part 3

Posted by Ravi Varma Thumati on November 17, 2009

In the previous parts, you learned to execute simple nonquery commands to update the data source and retrieve calculated values. You can also use queries to fetch a set of rows from a data source in a single operation. In ADO.NET, there are two ways to use query commands: with the disconnected DataSet object, as discussed in later chapters, and with the DataReader, which is the focus of this chapter.

The DataReader is little more than a thin wrapper over a cursor that retrieves query results in a read-only, forward-only stream of information. The DataReader won’t let you perform updates, see the results of live updates, or move back and forth through a result set as a server-side cursor does in traditional ADO programming. However, what you sacrifice in flexibility, you gain in performance. Because this cursor consumes few server resources and requires relatively little locking, the DataReader is always a performance-optimal way to retrieve data.

In this part, you’ll learn how to use a DataReader to retrieve data and schema information, how to handle specialized data types such as binary large objects (BLOBs), and how to write code that can access any type of data source with the DataReader.

DataReader Object Overview

As with all connection-specific objects, there is a DataReader for every data provider. Here are two examples:

  • System.Data.SqlClient.SqlDataReader provides forward-only, read-only access to a SQL Server database (Version 7.0 or later).
  • System.Data.OleDb.OleDbDataReader provides forward-only, read-only access to a data source exposed through an OLE DB provider.

Every DataReader object implements the System.Data.IDataReader and the System.Data.IDataRecord interfaces. The IDataReader interface provides the core methods shown in Table 5-1, such as Read( ), which retrieves a single row from the stream. The IDataRecord interface provides the indexer for the DataReader and allows you to access the column values for the current row by column name or ordinal number.

Table 5-1. IDataReader methods
Member Description
Close(  ) Closes the DataReader but not the underlying Connection. This allows you to use the Connection for another task.
GetSchemaTable(  ) Retrieves a DataTable object with information about the schema for the current result set.
NextResult(  ) When executing a Command that returns multiple result sets, you must use NextResult( ) to move from one result set to another. This method returns true if there are more result sets.
Read(  ) Loads the next row into the DataReader. This method returns true if there are more rows left to be read.

The key to understanding the DataReader is to understand that it loads only a single row into memory at a time. This ensures that memory use is kept to a minimum. It’s also important to realize that the DataReader represents a live connection. Thus, you should read the values, close the connection as quickly as possible, and then perform any time-consuming data processing.

You can’t create a DataReader directly. Instead, a DataReader must be generated by the ExecuteReader( ) method of a Command object. You won’t need to manually open the DataReader; it will be initialized as soon as you execute the Command. You can begin using it immediately by calling the Read( ) method.

Typical DataReader access code follows five steps:

  1. Create a Command object with an appropriate SELECT query.
  2. Create a Connection, and open it.
  3. Use the Command.ExecuteReader( ) method, which returns a live DataReader object.
  4. Move through the returned rows from start to finish, one at a time, using the DataReader.Read( ) method. You can access a column in the current row by index number or field name.
  5. Close the DataReader( ) and Connection( ) when the Read( )false to indicate there are no more rows.

    method returns

The DataReader is limited in scope and thus extremely simple to use. For example, the DataReader also has no intrinsic support for table relations, so you will need to perform a JOIN query if you want to see combined information from more than one table.

Performing a Query with a DataReader

To retrieve records with a Command and DataReader, you need to use the SELECT statement, which identifies the table and rows you want to retrieve, the filter and ordering clauses, and any table joins:

SELECT columns FROM tables WHERE search_condition

ORDER BY order_expression ASC | DESC

When writing a SELECT statement with a large table, you may want to limit the number of returned results to prevent your application from slowing down dramatically as the database grows. Typically, you accomplish this by adding a WHERE clause that limits the results.

Example 5-1 shows a sample Windows application that fills a list box with the results of a query. The designer code is omitted.

Example 5-1. Using a fast-forward DataReader

// DataReaderFillForm.cs – Fills a ListBox

using System;

using System.Windows.Forms;

using System.Data.SqlClient;

public class DataReaderTest : Form

{

private ListBox lstNames;

private string connectionString = “Data Source=localhost;” +

“Initial Catalog=Northwind;Integrated Security=SSPI”;

public DataReaderTest()

{

lstNames = new ListBox();

lstNames.Dock = DockStyle.Fill;

Controls.Add(lstNames);

Load += new EventHandler(DataReaderTest_Load);

}

public static void Main()

{

DataReaderTest t = new DataReaderTest();

Application.Run(t);

}

private void DataReaderTest_Load(object sender, System.EventArgs e)

{

string SQL = “SELECT ContactName FROM Customers”;

// Create ADO.NET objects.

SqlConnection con = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand(SQL, con);

SqlDataReader r = null;

// Execute the command.

try

{

con.Open();

r = cmd.ExecuteReader();

// Iterate over the results.

while (r.Read())

{

lstNames.Items.Add(r[“ContactName”]);

}

}

catch (Exception err)

{

MessageBox.Show(err.ToString());

}

finally

{

if (r != null) r.Close();

con.Close();

}

}

}

Using Column Ordinals

The previous example retrieved field values using a column name. Internally, however, the DataReader stores field information using a zero-based index. When you supply a column name, the DataReader performs a lookup in a Hashtable collection behind the scenes and then determines the appropriate ordinal. This adds a slight overhead, which increases the time required to read a column by up to 30%.

You can avoid this overhead by using the column ordinal when selecting a column:

// Display the value from the first column.

Console.WriteLine(r[0]);

Of course, this adds a tighter level of coupling between the data source and your code. For example, imagine you are writing your code as part of an enterprise-level distributed application. You are probably retrieving your query through a stored procedure. The order of DataReader columns is determined by the order of column names in the SELECT statement that the stored procedure uses. If the stored procedure changes, your code could fail. (A similar problem occurs if you are using name-based lookup, and the column names are changed, but this problem is typically easier to spot.)

To manage the possible confusion, you can retrieve the column ordinals after executing the query. This way, you perform the name lookup once and can use the better-performing index numbers for the remainder of your code, without exposing your code to unnecessary risk if the database changes. The DataReader provides a GetOrdinal( ) method for this purpose:

// Read and store all the ordinals you need.

int ID = r.GetOrdinal(“CustomerID”);

int Name = r.GetOrdinal(“ContactName”);

while (r.Read())

{

// Use the ordinals when retrieving field values.

Console.Write(r[ID]);

Console.WriteLine(r[Name]);

}

This code realizes a fairly modest performance increase.

Using Typed Accessors

Databases use their own proprietary data types, which map closely, but not exactly, to .NET data types. Internally, the DataReader uses a type as close as possible to the database-specific data type. If needed, you can cast this type to a .NET framework type, or you can use the strongly typed accessor methods such as GetInt32( ) and GetString( ), which perform this step automatically.

In some cases, these conversions can conceivably introduce minute rounding errors, loss of precision, or a minor performance slowdown. To circumvent these problems, some DataReader implementations provide additional methods that let you retrieve data types in their native form. For example, the SQL Server provider includes the types in the System.Data.SqlTypes namespace. These types map directly to SQL Server database types (such as money, smalldatetime, and varchar).

The SqlDataReader also provides corresponding methods for each data type, such as GetSqlMoney( ) and GetSqlDataTime( ). To use this method, you must supply the column index (the column name isn’t supported).

Example 5-2 shows how you might retrieve information from the Orders table using native SQL Server data types.

Example 5-2. Using native SQL Server data types

// NativeSqlServer.cs – Retrieves data as native SQL Server types.

using System;

using System.Data.SqlClient;

using System.Data.SqlTypes;

public class NativeSqlServer

{

public static void Main()

{

// Query string to get some records from Orders table

string SQL = “SELECT OrderID, CustomerID, ” +

“OrderDate, Freight FROM Orders”;

// First column OrderID is int datatype in SQL Server.

SqlInt32 orderID;

// Second column CustomerID is nchar in SQL Server.

SqlString customerID;

// Third column OrderDate is datetime in SQL Server.

SqlDateTime orderDate;

// Fourth column Freight is money in SQL Server.

SqlMoney freight;

// Create the ADO.NET objects.

SqlConnection con = new SqlConnection(“Data Source=localhost;” +

“Initial Catalog=Northwind;Integrated Security=SSPI”);

SqlCommand cmd = new SqlCommand(SQL, con);

SqlDataReader r;

con.Open();

// Perform the query.

r = cmd.ExecuteReader();

// Read the rows from the query result.

while (r.Read())

{

// Get the columns as native SQL types.

orderID = r.GetSqlInt32(0);

customerID = r.GetSqlString(1);

orderDate = r.GetSqlDateTime(2);

freight = r.GetSqlMoney(3);

// You can now do something with the data.

// This example just prints out the row.

Console.Write(orderID.ToString() + “, “);

Console.Write(customerID + “, “);

Console.Write(orderDate.ToString() + “, “);

Console.Write(freight.ToString() + “, “);

Console.WriteLine();

}

}

}

In this example, the advantage of using the SQL Server-specific types is minimal. In fact, all the SQL Server types map quite closely to their .NET equivalents. However, if you use a data source that exposes data in a unique format, this approach may become very important.

The managed Microsoft Oracle provider includes some specialized structures (e.g., OracleDateTime) in the System.Data.OracleClient namespace. You can use these structures to retrieve data using the dedicated methods of the OracleDataReader class.

The OLE DB managed provider doesn’t include any specialized structures for OLE DB types.

Retrieving Null Values

A common database convention is to use the null value to represent missing data. Some fields may refuse nulls, while others may allow them, indicating that data doesn’t need to be entered for this column.

.NET value types can’t legally contain a null value. Thus, if you try to retrieve a null value through a DataReader and assign it to a value type, you will receive an InvalidCastException. However, the data types in the DataReader can contain null values. (Otherwise, simply trying to read a row that contains a null value generates an error, rendering the DataReader useless).

There are several ways to code around the null value problem:

  • If you use SQL Server, you can retrieve native SQL Server data types with the appropriate DataReader methods. Every SQL Server data type implements the System.Data.SqlTypes.INullable interface, allowing them to legally contain a null value. However, a problem will occur if you try to cast a null value to a base .NET type.
  • You can call the ToString( ) method on the value, as shown in Example 5-2. Types that contain the null value simply return an empty string. This approach works well when you only need to display the data.
  • You can explicitly check the value before attempting to assign it to another variable.

The final approach is useful if you need to store the value in another variable. However, you can’t just test the field for a null reference. The problem here is that the column value does in fact exist: it isn’t null. However, it represents a null value because it doesn’t contain any information. The .NET framework includes the System.DBNull class for this purpose, which allows you to distinguish between a null reference and a null database value. If the column value is equal to DBNull.Value, it represents a null database field. The syntax is shown here:

int rowVal;

if (r[i] != DBNull.Value)

{

// Use default value. Row is null.

rowVal = 0;

}

else

{

// Use database value.

rowVal = (int)r[i];

}

There’s another way to explicitly test for a null value: using the IsDbNull( ) method of the DataReader. This code is equivalent:

int rowVal;

if (r.IsDbNull(i))

{

// Use default value. Row is null.

rowVal = 0;

}

else

{

// Use database value.

rowVal = (int)r[i];

}

Returning Multiple Result Sets

It is possible to execute a query that returns multiple result sets. This technique can improve performance because you need to contact the database only once to initiate the query. All data is then retrieved in a read-only stream from start to finish.

There are two ways to return more than one result set. You might be executing stored procedures that contain more than one SELECT statement. Alternatively, you might set up a batch query to execute multiple SQL statements by separating them with a semicolon:

// 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 r = cmd.ExecuteReader();

You need only one DataReader to process multiple result sets. To move from the first result set to the second, use the DataReader.NextResult( ) method:

while (reader.Read())

{

// (Process the category rows here.)

}

reader.NextResult();

while (reader.Read())

{

// (Process the product rows here.)

}

Showing All Columns with the DataReader

The OLE DB, ODBC, Oracle, and SQL Server providers all add a FieldCount property to the DataReader. This property allows you to retrieve the number of fields in the current row. Using this information, you can write a generic code routine to display the results of a query by index number, rather than by hard-coding field names. The disadvantage of this approach is that you are forced to use the order in which the columns were retrieved, which may not make the most sense for display purposes. Generally, the more your application knows about the structure of your data, the better it can present it—and the more difficult your life becomes when the database changes.

Example 5-3 shows an example that fills a ListView details grid with the results of the current query, regardless of the number or type of columns.

Example 5-3. Filling a ListView with all columns

// ListViewFillForm.cs – Fills a ListView

public class DataReaderTest : System.Windows.Forms.Form

{

private System.Windows.Forms.ListView lvCustomers;

private string connectionString = “Data Source=localhost;” +

“Initial Catalog=Northwind;Integrated Security=SSPI”;

// (Windows designer code omitted.)

private void DataReaderTest_Load(object sender, System.EventArgs e)

{

string SQL = “SELECT * FROM Customers”;

lvCustomers.View = View.Details;

// Create ADO.NET objects.

SqlConnection con = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand(SQL, con);

SqlDataReader r = null;

// Execute the command.

try

{

con.Open();

r = cmd.ExecuteReader();

// Add the columns to the ListView.

for (int i = 0; i <= r.FieldCount – 1; i++)

{

lvCustomers.Columns.Add(“Column ” + (i + 1).ToString(),

100, HorizontalAlignment.Left);

}

// Add rows of data to the ListView.

while (r.Read())

{

// Create the ListViewItem row with the first column.

ListViewItem lvItem = new ListViewItem(r[0].ToString());

// Add the data for the other columns.

for (int i = 1; i <= r.FieldCount – 1; i++)

{

lvItem.SubItems.Add(r[i].ToString());

}

// Add the completed row.

lvCustomers.Items.Add(lvItem);

}

}

catch (Exception err)

{

MessageBox.Show(err.ToString());

}

finally

{

if (r != null) r.Close();

con.Close();

}

}

}

Reading Single Rows with a DataReader

Command objects also provide another variation of the ExecuteReader( ) method that accepts a combination of values from the CommandBehavior enumeration. These values provide additional information about how the command should be executed.

The CommandBehavior enumeration is useful if you need to read large binary data sequentially, as described in the next section. However, it can also offer some performance improvement in cases when you know a SELECT statement will return only a single record (for example, if you include a WHERE clause specifying a value from a unique column). In this case, you can use CommandBehavior.SingleRow to inform the provider:

r = cmd.ExecuteReader(CommandBehavior.SingleRow);

This extra step certainly won’t harm performance, but its potential benefit depends on the specific implementation in the data provider.

Retrieving BLOB Data

The CommandBehavior enumeration is useful if you need to retrieve a BLOB from the database. In this situation, the DataReader‘s default behavior, which is to load the entire row into memory before providing it to your code, is dangerously inefficient. By specifying CommandBehavior.SequentialAccess, you indicate that your code will read through the data in a row sequentially, from start to finish. Thus, only a single field of data is read into memory at a time, instead of the entire row, reducing the memory overhead of your code. This benefit is trivial if row sizes are small but important if they are large.

When using CommandBehavior.SequentialAccess, you must read the fields in the same order they are returned by your query. For example, if your query returns three columns, the third of which is a BLOB, you must return the values of the first and second fields before accessing the binary data in the third field. If you access the third field first, you can’t access the first two fields.

When dealing with binary data, you typically use the DataReader.GetBytes( ) method, which fills a byte array with a portion of the data, according to the buffer size and starting position you specify. The GetBytes( ) method returns an Int64 value that indicates the number of bytes that were retrieved. To determine the total number of bytes in the BLOB, pass a null reference to the GetBytes( ) method.

Example 5-4 demonstrates a console application that reads a list of records, and then reads a large binary field and writes it to disk as a bitmap file. This example uses the pubs database.

Example 5-4. Writing a BLOB to a file

// BLOBTest.cs – Writes binary data to a file

using System;

using System.Data.SqlClient;

using System.Data;

using System.IO;

public class ConnectionTest

{

private static string connectionString = “Data Source=localhost;” +

“Initial Catalog=pubs;Integrated Security=SSPI”;

private static string SQL = “SELECT pub_id, logo FROM pub_info”;

public static void Main()

{

int bufferSize = 100;                  // Size of the BLOB buffer.

byte[] bytes = new byte[bufferSize];   // The BLOB byte[] buffer.

long bytesRead;                        // The number of bytes read.

long readFrom;                         // The starting index.

SqlConnection con = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand(SQL, con);

// Open the connection and execute a sequential DataReader.

con.Open();

SqlDataReader r =

cmd.ExecuteReader(CommandBehavior.SequentialAccess);

while (r.Read())

{

string filename = “logo” + r.GetString(0) + “.bmp”;

Console.WriteLine(“Creating file ” + filename);

// Create a file stream and binary writer for the data.

FileStream fs = new FileStream(filename, FileMode.OpenOrCreate,

FileAccess.Write);

BinaryWriter bw = new BinaryWriter(fs);

// Reset the starting position for the new BLOB.

readFrom = 0;

// Read the field 100 bytes at a time.

do

{

bytesRead = r.GetBytes(1, readFrom, bytes, 0, bufferSize);

bw.Write(bytes);

bw.Flush();

readFrom += bufferSize;

} while (bytesRead == bufferSize);

// Close the output file.

bw.Flush();

bw.Close();

fs.Close();

}

r.Close();

con.Close();

}

}

Stored Procedures with the DataReader

Using a command to execute a stored procedure query isn’t much different from using one to execute a stored procedure that wraps a nonquery command such as INSERT, UPDATE, or DELETE.

The Northwind database includes a small set of stored procedure queries. One example is the CustOrderHist procedure, which returns the total number of products a given customer has ordered, grouped by product name.

Here’s the SQL code to create the CustOrderHist stored procedure. It defines one parameter (shown in the first line), called @CustomerID:

CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)

AS

SELECT ProductName, Total=SUM(Quantity)

FROM Products P, [Order Details] OD, Orders O, Customers C

WHERE C.CustomerID = @CustomerID AND

C.CustomerID = O.CustomerID AND

O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID

GROUP BY ProductName

GO

Example 5-5 executes this stored procedure for the customer “ALFKI” and displays the results in a console window.

Example 5-5. Using a stored procedure query

// TotalOrders.cs – Runs the CustOrderHist stored procedure

using System;

using System.Data;

using System.Data.SqlClient;

public class TotalOrders

{

public static void Main()

{

string connectionString = “Data Source=localhost;” +

“Initial Catalog=Northwind;Integrated Security=SSPI”;

string procedure = “CustOrderHist”;

// Create ADO.NET objects.

SqlConnection con = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand(procedure, con);

SqlDataReader r;

// Configure command and add parameters.

cmd.CommandType = CommandType.StoredProcedure;

SqlParameter param;

param = cmd.Parameters.Add(“@CustomerID”, SqlDbType.NChar, 5);

param.Value = “ALFKI”;

// Execute the command.

con.Open();

r = cmd.ExecuteReader();

while (r.Read())

{

Console.WriteLine(r[“Total”].ToString() + ” of ” +

r[“ProductName”].ToString());

}

con.Close();

}

}

Here’s the sample output for this code:

20 of Vegie-spread

15 of Raclette Courdavault

17 of Rössle Sauerkraut

15 of Lakkalikööri

16 of Grandma’s Boysenberry Spread

20 of Flotemysost

2 of Original Frankfurter grüne Soße

2 of Spegesild

21 of Chartreuse verte

6 of Aniseed Syrup

40 of Escargots de Bourgogne

DataReaders and Schema Information

Schema information is information about the structure of your data. It includes everything from column data types to table relations.

Schema information becomes extremely important when dealing with the ADO.NET DataSet, as you’ll learn in the following chapters. However, even if you aren’t using the DataSet, you may want to retrieve some sort of schema information from a data source. With ADO.NET, you have two choices: you can use the DataReader.GetSchemaTable( ) method to retrieve schema information about a specific query, or you can explicitly request a schema table from the data source.

Retrieving Schema Information for a Query

As long as a DataReader is open, you can invoke its GetSchemaTable( ) method to return a DataTable object with the schema information for the result set. This DataTable will contain one row for each column in the result set. Each row will contain a series of fields with column information, including the data type, column name, and so on.

Example 5-6 shows code to retrieve schema information for a simple query.

Example 5-6. Retrieving the schema information for a query

// GetSchema.cs – Retrieves a schema table for a query

using System;

using System.Data;

using System.Data.SqlClient;

public class GetSchema

{

public static void Main()

{

string connectionString = “Data Source=localhost;” +

“Initial Catalog=Northwind;Integrated Security=SSPI”;

string SQL = “SELECT * FROM CUSTOMERS”;

// Create ADO.NET objects.

SqlConnection con = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand(SQL, con);

SqlDataReader r;

DataTable schema;

// Execute the query.

try

{

con.Open();

r = cmd.ExecuteReader();

schema = r.GetSchemaTable();

}

finally

{

con.Close();

}

// Display the schema table.

foreach (DataRow row in schema.Rows)

{

foreach (DataColumn col in schema.Columns)

{

Console.WriteLine(col.ColumnName + ” = ” + row[col]);

}

Console.WriteLine();

}

}

}

If you run this test, you’ll find that it returns a significant amount of information. Here’s the output for just a single column in the query (omitting columns that don’t return any information):

ColumnName = CustomerID

ColumnOrdinal = 0

ColumnSize = 5

NumericPrecision = 255

NumericScale = 255

IsUnique = False

BaseColumnName = CustomerID

DataType = System.String

AllowDBNull = False

ProviderType = 10

IsIdentity = False

IsAutoIncrement = False

IsRowVersion = False

IsLong = False

IsReadOnly = False

Although you must retrieve the schema DataTable while the DataReader is open, you can store it in a variable and access it later, after the connection is closed. That’s because the DataTable is a disconnected data container.

Retrieving Schema Tables

The GetSchemaTable( ) method is ideal if you need schema information based on a query, but it won’t allow you to retrieve anything else. For example, you might want to retrieve a list of databases, tables and views, constraints, or stored procedures from a data source. The DataReader has no built-in support for this type of information. However, it is possible to retrieve schema information directly with a specialized command, depending on the data source and data provider you use.

Retrieving schema tables with SQL Server

SQL Server exposes schema information through dedicated stored procedures and informational schema views. Informational schema views allow you to retrieve metadata as a table, using a SQL SELECT statement. However, the information is generated internally by the data source, not stored in a table. For example, the code in Example 5-7 shows how you can use one of the information schema views (TABLES) to retrieve a list of all the tables and views in the Northwind database. Though it appears to be querying information from a table, there is no physical table named INFORMATION_SCHEMA.TABLES in the data source.

A full description of information schemas is beyond the scope of this book, but they are described in detail in the SQL Server Books Online (just search for INFORMATION_SCHEMA).

Example 5-7. Retrieving a list of tables using an information schema

// GetTableList.cs – Retrieves a list of tables in a database

using System;

using System.Data.SqlClient;

public class GetTableList

{

public static void Main()

{

string connectionString = “Data Source=localhost;” +

“Initial Catalog=Northwind;Integrated Security=SSPI”;

string SQL = “SELECT TABLE_TYPE, TABLE_NAME FROM ” +

“INFORMATION_SCHEMA.TABLES”;

// Create ADO.NET objects.

SqlConnection con = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand(SQL, con);

SqlDataReader r;

// Execute the query.

try

{

con.Open();

r = cmd.ExecuteReader();

while (r.Read())

{

Console.WriteLine(r[0] + “: ” + r[1]);

}

}

finally

{

con.Close();

}

}

}

Here’s a partial listing of the information this code returns:

VIEW: Alphabetical list of products

BASE TABLE: Categories

VIEW: Category Sales for 1997

VIEW: Current Product List

VIEW: Customer and Suppliers by City

BASE TABLE: CustomerCustomerDemo

BASE TABLE: CustomerDemographics

BASE TABLE: Customers

BASE TABLE: Employees

BASE TABLE: EmployeeTerritories

If you want to include only tables (not views), you can modify the query by adding an extra WHERE clause as follows:

SELECT TABLE_TYPE, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = ‘BASE TABLE’

Other views provide information about stored procedure parameters, columns, keys, constraints, user privileges, and more. You can also use specialized system stored procedures to perform tasks that the informational schema views can’t, such as retrieving a list of all databases (via sp_catalog). All system stored procedures start with “sp_” and are documented in the SQL Server Books Online.

Retrieving schema tables with the OLE DB provider

Information views are limited to SQL Server and won’t work with other data sources. However, if you use the OLE DB provider, you have another option. The OleDbConnection object provides a GetOleDbSchemaTable( ) method that can return various types of schema information, similar to what SQL Server accomplishes with its built-in informational views. Each data source handles this task differently, depending on the data source, but the ADO.NET code is generic.

GetOleDbSchemaTable( ) takes two parameters. The first is a value from the OleDbSchemaGuid class that specifies the type of schema information you want to return. The second is an array of objects that represent column restrictions. You apply these in the same order as the columns of the schema table.

GetOleDbSchemaTable( ) returns the schema information as a DataTable, similar to the GetSchemaTable( ) method of the DataReader. Example 5-8 uses GetOleDbSchemaTable( ) to retrieve a list of tables and views, similar to Example 5-7.

Example 5-8. Retrieving a list of tables using the OleDbSchemaGuid

// GetOleDbTableList.cs – Retrieves a list of tables in a database

using System;

using System.Data;

using System.Data.OleDb;

public class GetSchema

{

public static void Main()

{

string connectionString = “Data Source=localhost;” +

“Provider=SQLOLEDB;Initial Catalog=Northwind;” +

“Integrated Security=SSPI”;

// Create ADO.NET objects.

OleDbConnection con = new OleDbConnection(connectionString);

DataTable schema;

// Execute the query.

try

{

con.Open();

schema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,

new object[] {null, null, null, null});

}

finally

{

con.Close();

}

// Display the schema table.

foreach (DataRow row in schema.Rows)

{

Console.WriteLine(row[“TABLE_TYPE”] + “: ” +

row[“TABLE_NAME”]);

}

}

}

The resulting output is similar to the previous example. To create a list that includes only tables, you would need to realize that the TABLE_NAME column is the fourth column returned from the GetOleDbSchemaTable( ) method. You can then specify a restriction by supplying a filter string as the fourth element of the restriction array:

schema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,

new object[] {null, null, null, “TABLE”});

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: