Back to VSLive! San Francisco Show Daily Home

	email article
	printer friendly
	more resources

Optimize Oracle-.NET Data Access
Make the most out of Oracle Data Provider for .NET.
by Alex Keh

VSLive! San Francisco, March 23, 2004

Note: Alex Keh is presenting "Optimizing the Oracle Database for .NET Applications" at VSLive! San Francisco on Thursday, March 25. These tips focus on using the Oracle Data Provider for .NET.

Oracle Data Provider for .NET (ODP.NET) offers optimized data access between .NET and the Oracle database. Here are some guidelines for maximizing .NET programming with Oracle:

• Use ODP.NET Dynamic Help in Visual Studio .NET for quick documentation access. ODP.NET Dynamic Help is integrated with Visual Studio .NET. Just hit the F1 key when your cursor is on any ODP.NET API, and the help page for that specific API will appear right in Visual Studio .NET. No more searching in manuals to track down an API definition!
• Start using Multiple Active Result Sets (MARS) today with ODP.NET. MARS, one of Whidbey's major features, allows .NET to have more than one DataReader open at a time, providing greater programmer flexibility when manipulating multiple result sets. Oracle developers can use MARS with ODP.NET now. This feature has been available in ODP.NET since it was first introduced two years ago.
• Distribute ODP.NET applications without using tnsnames.ora. The tnsnames.ora file lets you define alias names with connection information to your database servers. If you don't want to deploy the tnsnames.ora file with your application, you can define all the database connection information within the .NET connection string. For example, you can use this connection string from C#:

  string constr = "User Id=scott; Password=tiger;
  Data Source=(DESCRIPTION = (ADDRESS_LIST = 
  (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx)
  (PORT = 1521)))(CONNECT_DATA = (SERVER = 
  DEDICATED) (SERVICE_NAME = xxxx)));";

  Just put your HOST and SERVICE_NAME information in the data source and create a connection object.
• Use FetchSize and RowSize properties to control data retrieval performance. You can tune data access performance by modifying the amount of data fetched for each database round trip using the FetchSize (OracleDataReader) property. You can generally use FetchSize in conjunction with RowSize (OracleCommand), which determines the amount of data each row in the query will have. For example, if your application needs to return 10 rows per round trip, you would set FetchSize equal to RowSize multiplied by 10.
• Use anonymous PL/SQL to batch commands and reduce round trips. Use anonymous PL/SQL to batch SQL commands within your .NET code. The PL/SQL will execute on the server side all in a single database round trip, thereby improving data access performance by avoiding unnecessary round trips.
• Use array parameters to pass array variables between Oracle and .NET. You can use PL/SQL associative arrays (site requires registration) to pass array variables between the Oracle database and .NET middle tier. A large set of data stored in an array can be passed in one database round trip.
• Use array binding to execute the same statement multiple times in a single round trip. Array binding (site requires registration) allows the same SQL or PL/SQL statement to execute repeatedly in a single database round trip. The statement itself can use parameters so that its value changes with each execution. This feature improves data access performance and reduces the amount of code you need to write.

ODP.NET provides many Oracle-.NET database benefits, including cool XML functionality. Be sure to check out the ODP.NET sample code online.

About the Author
Alex Keh is a Windows data access senior product manager at Oracle. He focuses on all aspects of .NET development with the Oracle database, including the Oracle Data Provider for .NET and Visual Studio integration.