The purpose of this sample application is to demonstrate
how one can control FetchSize property of OracleDataReader. It also shows how
one can populate DataGrid control using OracleDataReader class of ODP.NET.
To understand this sample, you need to have working knowledge
of .NET, ODP.NET and Oracle Database.
Technical Overview
Oracle Data Provider for .NET (ODP.NET) is an implementation
of a data provider for the Oracle database. ODP.NET uses Oracle native APIs
to offer fast and reliable access to Oracle data and features from any .NET
application. ODP.NET is provided in Oracle.DataAccesss.dll assembly. An OracleDataReader
object represents a forward-only, read-only, in-memory result set. Unlike the
DataSet, the OracleDataReader stays connected and fetches data. OracleDataReader
class has a property called as FetchSize. This property specifies the memory
size in bytes that is used by OracleDataReader object to fetch data. So, depending
on the number of rows users want to fetch in one database trip, they can set
this property to an appropriate value. If FetchSize is set to zero then no rows
are fetched. For any meaningful operation this size must be equal to RowSize
or an integral positive multiple thereof. RowSize is the property of OracleCommand
object. This property is set to the number of bytes required to fetch a single
row from the database after the ExecuteReader() method of OracleCommand object
is invoked. Once users get this value they can calculate the required FetchSize
as FetchSize = RowSize * Number of Rows to be fetched per database trip.
Users must set FetchSize property to this value before they read any rows from
the OracleDataReader object.
This application also shows how one can create a DataTable dynamically from
OracleDataReader and then use it to populate a DataGrid control.
Application Overview
When this sample application is run, users should supply a
username, password and connect string to connect to a database. To connect,
click "Connect" button. The application creates the required table to set up
this application. Once the table is populated "Number of Rows..." text box will
be enabled. You may edit this field and enter any number of your choice. This
number must be positive and greater than 1 and less than 1000. This restriction
is enforced by the application since there are 1000 rows in the table. You may
press the "Populate Grid" button to fill the DataGrid. You may change the number
of rows you want to fetch and repopulate the DataGrid. You can notice that the
time to populate the DataGrid increases as the FetchSize ( Number of rows per
database trip) is reduced.
Figure 1.1: Screen shot of the Main Screen
Software Requirements
List the softwares required for configuring and running this
sample application.
VB.NET installed with MS Development Environment 7.0 version
(MS Visual Studio .NET) including MS .NET Framework 1.0 version
1. Unzip FetchSize.zip using Winzip or command utility to
your convenient directory referred to as <SAMPLE_HOME>. Extraction of
the zip file results in the creation of 'FetchSize' directory. Refer description
of Sample Application Files section for
more details.
2. No special database setup is required. When this application starts it creates
the required table 'PRODUCTTAB' and inserts necessary data. Ensure that the
database user
has necessary privileges to drop, create and update tables.
Deploying and Running
the Application
The application can be deployed and run in the following way:
1. Open FetchSize.sln using Visual Studio .NET from <SAMPLE_HOME>\FetchSize
folder.
2. Ensure that the following .NET Component References have been added to FetchSize
project
Oracle.DataAccess
System
System.Data
System.Drawing
System.Windows.Forms
3. To add the reference to above .NET Components
a)Go to Menu ->View-> Solution Explorer.
b)Right click on FetchSize project, choose 'Add Reference'.
c)Choose the above .NET Components from the list displayed.
d)Click 'Select', then OK.
e) The chosen .NET component gets added to the project.
4. Build and run the sample.
Steps in the Sample
1. When the Main form of the application appears as shown in figure 1.1 enter
the username , password and connect string as per your database setting and
press "Connect" button. Wait until the table creation and data insertion
is complete. Once this is done the "Number of Rows.." text control
will be enabled.
2. You may change the number of rows that are fetched and then press "Populate
Grid" button to fetch the data.Time required to populate the DataGrid is
displayed at the bottom.
3. You may change the number of rows fetched per trip between 1 to 1000 and
repopulate the DataGrid to see that the time to populate the DataGrid increases
as the
FetchSize ( Number of rows per database trip) is reduced. (Maximum number of
rows is restricted to 1000 as there are 1000 rows in the table)
Note: The database user
used to execute this application must have necessary privileges to create, delete
and modify tables.
Sample Application
Files
Following is the directory structure of the FetchSize.zip
:
Directory
File
Description
FetchSize\doc
Readme.html
This file
otn_new.css
Stylesheet used in Readme.html
FetchSize\doc\images
*.jpg
This folder contains images used in the Readme.html file
FetchSize
FetchSize.sln
Visual Studio Solution
FetchSize.suo
Solution User option (this file gets automatically
generated when a Visual Studio Solution is created)
FetchSize\src\FetchSize
AssemblyInfo.vb
This file stores information about generated
assembly
DBAccess.vb
This file has the code that accesses the
database
FetchSizeForm.vb
Main window of this application
FetchSizeForm.vbproj.*
Visual Studio Project Files (these files
are automatically generated when a Visual Studio Project is created)
*.resx
.NET XML Resource Template (this file gets
automatically generated when a Visual Studio Project is created)
Please enter your comments about this sample application
here.
Bookmark
