Using a Data Reader for Quick One-Way Access

Using a Data Reader for Quick One-Way Access

If the user isn't going to update the information you retrieve from the database, you might get better performance by using a data reader instead. A data reader is not as flexible as a data setfor example it can't be passed between machines or returned from a Web Service as a data set canbut it can substitute nicely for the data set that was used in the previous section. It can't be bound to a data grid, but not all applications benefit from a data grid anyway. In this section, a console application uses a data reader to look up employee names .

SHOP TALK: TWO DATA GRID CONTROLS

You might have heard somewhere that you can bind a data reader to a data grid. This is partially true. There are two data grids in the libraries that come with the .NET Framework: the WinForms data grid and the ASP.NET data grid. They appear very similar, but the way they work internally has a number of differences. An ASP.NET data grid will bind to a data reader, but a WinForms data grid will not.

Should you feel slighted, as a WinForms developer, that the WinForms team didn't add this feature to their data grid whereas the ASP.NET team did? Not really. The ASP.NET data grid simply creates a temporary DataTable (usually contained in a data set), reads the entire contents of the data reader into the temporary table, and then binds the data grid to the table. This eliminates any performance savings you might have achieved by using a data reader instead of a data set.

Creating the Console Application

Add another project to the solution; select a .NET Console Application and call it EmployeeConsole . This project needs a reference to the data layer; add a reference as before and again use the Projects tab of the Add Reference dialog box, and then select EmployeeData .

You will add code to the console application to use a new method of the Employee class to read from the database with a data reader.

Writing the New LookupReader() Method

Open EmployeeData.h and add another method to the Employee class:

 IDataReader* LookupReader(String* name); 

There is only one DataSet class no matter where your data comes from; all the specialization is in the adapters. There are adapters for SQL, for OleDB, and so on. The Lookup() method used an OleDbDataAdapter , and if you changed your data source, the changes required to your code would be confined to the Lookup() method, because it returns a DataSet with no source-specific information in it.

In contrast, there are many data reader classes: OleDbDataReader , SqlDataReader , OracleDataReader , and so on. If you were to write to LookupReader() to return an OleDbDataReader , and then later you needed to change your database implementation, your changes would ripple through to the calling code and perhaps even further. Returning a database-specific data reader is not a good idea. Instead, take advantage of the fact that all the data readers implement the IDataReader interface and declare that LookupReader() returns an IDataReader . The OleDbDataReader is an IDataReader , as are all the others, so any future changes to LookupReader() will not change the return type of the method and the changes won't affect any other code.

The code for LookupReader() is very similar to the Lookup() method, but you must create the connection and command objects yourself instead of passing strings to an adapter constructor. Add this code for the method:

 IDataReader* Employee::LookupReader(String* name) {    StringBuilder* query = new StringBuilder();    query->Append("SELECT * FROM Employees WHERE EmployeeName Like '%");    query->Append(name);    query->Append("%'");    OleDbConnection* conn = new OleDbConnection(ConnStr);    OleDbCommand* SelectCommand = new OleDbCommand(query->ToString(),conn);    conn->Open();    return SelectCommand->ExecuteReader(Data::CommandBehavior::CloseConnection); } 

Because your code opens the connection, it should close it. However, a data reader is not disconnected like a data set. You can't close it until you have read all the data, and you can't close the connection without closing the data reader. That means that this code can't close either the data reader or the connection. The data reader is returned to the calling code, which can close it, but that calling code has no access to this connection object. Passing the CloseConnection constant as the second parameter of ExecuteReader() tells the data reader "when you close, close your own connection at the same time." This ensures the connection will not be left open when the calling code is finished with the data reader. Leaving connections open when you are finished with them can have a significant impact on the performance of your application, because it sabotages the built-in connection pooling in ADO.NET.

Getting and Using the Data Reader from the Console Application

With the new method added to Employee , add code to the main() function to prompt the user for a name, pass the name to LookupData() , and then read and echo the contents of the data reader. Edit EmployeeConsole.cpp so that it reads like this:

 // This is the main project file for VC++ application project // generated using an Application Wizard. #include "stdafx.h"  #using <mscorlib.dll> using namespace System; using namespace System::Data; int _tmain() {     Console::WriteLine(S" Enter a name or part name");     String* name = Console::ReadLine();     EmployeeData::Employee* emp = new EmployeeData::Employee();     IDataReader* dr = emp->LookupReader(name);     while (dr->Read())     {         Console::WriteLine("{0}\t {1}\t {2}\t {3}",             dr->get_Item(0),             dr->get_Item(1),             dr->get_Item(2),             dr->get_Item(3));     }     dr->Close();     return 0; } 

The data reader has a Read() method that returns false when there is no more to read, but otherwise points itself to the next record. You can access the fields using get_Item() , which takes a numerical index or a stringpass the field name if you use the string. When you're finished with the data reader, close it. As discussed earlier, this will also close the connection.

Configuration Files Revisited

The configuration file that was added to the Windows Forms application does not control the console application. You need to add an application file to the console application project, and a custom post-build event as before. However, the copy is a little different. Enter the property as

 copy app.config ..$(SolutionName)$(ConfigurationName)$(TargetFileName).config 

This ensures the config file ends up in the EmployeeUI folder where it belongs.

Finally, you need to change the startup project. The EmployeeUI project is bolded in Solution Explorer to show you that when you run the solution (for example, by pressing F5), you will actually run the EmployeeUI project. Right-click EmployeeConsole in Solution Explorer and choose Set As Startup Project to arrange for F5 to run the console application. Then build and run the project. Enter a name or part of a name. Here is a sample run:

 Enter a name or part name g 250      Greene, Nancy   6.55    SPORTING 235      White, Gail     6.22    COSMETICS 236      Anderson, Maggie        8.95    COSMETICS 256      Yaslow, Meg     9.25    WOMENSCLOTHING Press any key to continue 

This console application demonstrates one of the values of a separate data layer; it can support multiple applications. This console application could easily have used the data set from Lookup() by iterating through the rows of the data set, but the data reader code is easier to write and executes more quickly. Now the data layer has two useful functions, and application developers can use whichever is best for each project.