ODP.NET Tip of the Month Archives

November 2003: Using Dynamic Help

ODP.NET Dynamic Help is integrated inside of Visual Studio .NET. Just hit the F1 key when your cursor is on an ODP.NET API and help pages for that API will appear right in Visual Studio .NET.

December 2003: Using ODP.NET without tnsnames.ora

From .NET, you can connect to Oracle without using a database alias. Instead, insert all your Oracle connection and configuration information in the "Data Source" value of your ODP.NET connection string. Click here for more details.

January 2004: Validate Connections

When set to true, the connection string attribute, "validate connection", will check whether connections from the connection pool remain valid. Note that this produces a database round trip for each connection pulled from the pool, which can lower performance.

February 2004: Array Binding for Multiple Execution

Array binding allows the same SQL or PL/SQL statements to execute multiple times all in a single database round trip. Thus, it improves data access performance and reduces the amount of code that needs to be written.

March 2004: Using Array Parameters

PL/SQL associative arrays can be used to pass array variables between the Oracle database and .NET application. Thus, a large set of values can be passed easily between the two in one round trip.

April 2004: Multiple Active Result Sets (MARS)

Multiple Active Result Sets (MARS), one of the major Whidbey 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 already use MARS with ODP.NET today! This feature has been available in ODP.NET since its first release two years ago.

May 2004: LOB Retrieval Tuning

LOB data retrieval can be tuned to return all the LOBs queried in one round trip, only a portion of a LOB (set by the chunk size) per round trip, or to defer LOB retrieval to .NET until later (uses LOB locators).

June 2004: Proxy Authentication

When using proxy authentication, ODP.NET creates one connection pool for each proxy user, no matter the number of client users mapped to the proxy user. As such, you can identify specific database client users while maintaining full control over connection resources.

July 2004: Statement Batching

Multiple statements can be batched and executed in one database round trip by using anonymous PL/SQL within your .NET code.

August 2004: Named and Positional Parameter Binding

Parameters can be bound to statements by name or by position, giving developers more flexible choices. To modify variable binding, developers should set the OracleCommand.BindByName property to true or false(default).

September 2004: Single sign-on

ODP.NET can use Windows user login credentials to authenticate users. To open a connection using Windows user login credentials, the User Id connection string attribute must be set to /. If the Password is provided, it is ignored.

October 2004: Command Cancellation

In ODP.NET 10.1.0.3 and higher, developers can cancel long running queries by using the OracleCommand.Cancel method.

November 2004: Statement caching

In ODP.NET 10.1.0.3 and higher, Oracle provides a configurable cache of SQL and PL/SQL statements for each session. This statement caching feature improves performance and scalability for subsequent executions of the same statement (possibly with different parameter values) by reusing the parsed information from the cursor.

December 2004: Oracle Developer Tools for Visual Studio .NET

Oracle Developer Tools (ODT) provide database designers and ODP.NET wizards from right within the Visual Studio .NET development environment. Developers can now edit PL/SQL, drag and drop code, and get Oracle documentation all without leaving Visual Studio.

January 2005: Controlling Database Round Trips in SELECT Statements

.NET developers can optimize performance by setting the exact amount of data to be retrieved per database round trip. The OracleCommand.RowSize property is automatically populated with the query row size after a SELECT statement execution. The OracleDataReader.FetchSize is a user-defined property that represents the total amount of data that ODP.NET retrieves in one server round trip. Using these two properties together, developers can set the exact amount of data they want fetched per server round trip.

February 2005: Using Oracle data types in .NET

Oracle Database data types, such as REF Cursors, LOBs, and XMLType, exist natively within the .NET middle-tier through ODP.NET. These data types have the same functionality in the database as in .NET, providing developers more flexibility in storing and manipulating their data.

March 2005: Using ODP.NET with a different version of the database server

The ODP.NET client is certified to work with database servers up to two versions older and two versions newer. For example, an Oracle9 i Release 2 ODP.NET client is supported with Oracle8 Database up to Oracle Database 10g and the next major release beyond it. This allows users to upgrade the server and client separately if necessary.

April 2005: Use DeriveParameters method sparingly

Use OracleCommandBuilder's DeriveParameters method sparingly when application performance is critical. DeriveParameters initiates a database server roundtrip in order to automatically populate the parameter metadata of a stored procedure or function.

May 2005: Use BINARY_FLOAT and BINARY_DOUBLE instead of NUMBER in ODP.NET

For intensive ODP.NET number retrieval, use Oracle's BINARY_FLOAT (4 bytes) and BINARY_DOUBLE (8 bytes) rather than the Oracle NUMBER (21 bytes) data type. BINARY_FLOAT's and BINARY_DOUBLE's smaller size provides better performance for data retrieval and manipulation.

June 2005: ODT includes filtering and query window

Oracle Developer Tools (ODT) now supports schema filtering and ad-hoc SQL. Schema filtering allows the developer to limit the schema objects that populate ODT. This provides a more manageable working set if there are numerous objects in the database server. The Query Window allows developers to test their SQL and PL/SQL statements or perform ad-hoc operations on the database.

July 2005: ODP.NET Policy Files

When upgrading ODP.NET (e.g. 9.2 to 10.1), you do not have to recompile existing ODP.NET applications to take advantage of the new data provider. Instead, use the ODP.NET policy DLL (Policy.9.2.Oracle.DataAccess.dll or Policy.10.1.Oracle.DataAccess.dll) to redirect your .NET application to use the new ODP.NET provider.

August 2005: Clearing the Connection Pool

In ODP.NET version 10.2, you can programmatically clear the connection pool by calling OracleConnection.ClearPool. This is useful for administration in case of events, such as a database server reboot or temporarily losing network connectivity to the database server.