As Published In
Oracle Magazine
September/October 2005

DEVELOPER: ODP.NET


The Values That Bind

By Mark A. Williams Oracle ACE

Improve performance in .NET applications with bind variables and ODP.NET.

There are many ways to access data from Oracle databases in .NET applications. In terms of features and performance, however, the Oracle Data Provider for .NET (ODP.NET) is your best choice for connecting .NET applications with Oracle Database.

This new column will focus on how to make your .NET applications benefit from key features in ODP.NET 10g. My first topic: how to use bind variables with ODP.NET 10g.

Bind Variables: Use Them

One of the easiest and best ways to make sure your ODP.NET 10g applications perform and scale well is to use bind variables appropriately. Although they'll improve performance in many types of applications, I'll focus on using them with ODP.NET 10g.

Bind variables are placeholders in a SQL statement. For example, using the HR sample schema that ships with Oracle Database, a typical SELECT statement that doesn't use bind variables looks like this: 

select country_name
from hr.countries
where country_id = 'UK'


As you can see, I've specified the literal text ' UK' in the WHERE clause of the statement. By making a single, simple change, you can convert this statement to use a bind variable: 

select country_name
from hr.countries
where country_id = :country_id


In the bind variable version of the statement, I've replaced the literal text 'UK' with a placeholder identified as : country_id . The bind variable identifier begins with a single colon (":"); this is how a bind variable is represented in a SQL statement.

While I've used a SELECT statement to illustrate bind variable usage, bind variables can (and should) be used with UPDATE , INSERT , and DELETE statements as well. Here's an example of using bind variables in an UPDATE statement with the same countries table: 

update hr.countries
set country_name  = :country_name
where country_id   = :country_id


Why Are Bind Variables Important?

When Oracle Database 10g is presented with a SQL statement, it checks the shared pool—a memory area—to see if the statement already exists and is stored in memory. If the statement does exist in memory and Oracle Database 10g can reuse it, then the database can skip the task of parsing and optimizing the statement. When you use bind variables, you greatly increase the likelihood that SQL statements will be stored in memory, making them available—quickly—to the next operation that needs them.

If the statement doesn't exist in the shared pool, then the database must parse and optimize the statement—and that's where performance can bog down. Parsing and optimizing a statement consumes CPU cycles; the more CPU cycles consumed, the slower the operation will be. Parsing and optimizing also places locks on various portions of the shared pool through a mechanism known as a latch. Only one process at a time may hold a latch; therefore, any increased latching on the shared pool becomes a point of contention in the database.

In a single-user system, the amount of latching that takes place and the amount of time spent parsing and optimizing a statement may seem insignificant. However, as more users are added to the system or as additional copies of an application are launched, these events can quickly multiply—and can even render a system unusable.

Implementing Bind Variables in ODP.NET 10g

In order to use bind variables from your ODP.NET 10g programs, use the OracleParameter class to represent each bind variable in your .NET code. The OracleParameterCollection class is, as its name implies, a collection class that contains the OracleParameter objects associated with the OracleCommand object for each statement. The OracleCommand class passes your SQL statement to the database and returns the results to your application.

ODP.NET 10g lets you use two modes with bind variables: bind by position (the default) or bind by name. The OracleCommand Boolean property BindByName (which defaults to false) sets the mode. When using bind by position mode, you must add the parameters to the OracleParameterCollection collection in the same order as they appear in the SQL statement. You use the Add method with the Parameters property to add a parameter to the collection for the command object. If you want to use bind by name mode, you may add the parameters to the collection in any order; however, you must set the ParameterName property for the parameter object to the same value as the bind variable identifier in the SQL statement.

In addition to the binding mode (by position or by name), a few other properties are typically set for each parameter object:

  • Direction
  • OracleDbType
  • Size
  • Value


Bind variables may be used as output, input, or input/output parameters. Use the Direction property to indicate the appropriate direction for each parameter. The default value of the Direction property is Input. Use the OracleDbType property to indicate whether the parameter is a number, a date, a VARCHAR2 , and so on. When you use a variable length data type such as the VARCHAR2 data type, use the Size property to indicate the maximum size of the data that the parameter will hold. The Value property contains the parameter value either before statement execution (in the case of an input parameter), after execution (in the case of an output parameter), or both before and after (in the case of an input/output parameter).

The Main method in Listing 1 ties together these concepts and uses a bind variable in a SELECT statement. The following are key parts of the Main method: 

OracleCommand cmd = 
new OracleCommand(); 


—creates the command object ( OracleCommand ). 

OracleParameter p_country_id = 
new OracleParameter();


—creates the parameter object ( OracleParameter ). 

p_country_id.OracleDbType = OracleDbType.Varchar2;
p_country_id.Value = "UK";


—sets the OracleDbType and Value properties for the parameter object.

Next Steps


DOWNLOAD
sample application for this column
ODP.NET 10g
Oracle Developer Tools for Visual Studio .NET

Note that the Direction property uses the default value Input , and the Size property is not set. Since the object is an input parameter, you don't need to set the Size property—the data provider can determine the size from the value. 

cmd.Parameters.Add(p_country_id);


—adds the parameter to the collection.

To run the sample application in Listing 1, create a .NET console application and create a Main method using the Listing 1 code as the body of the method. You'll need to add a reference to the ODP.NET 10g assembly to your project and include the Oracle .DataAccess.Client namespace at the top of your code module. To add the reference, from the Microsoft Visual Studio .NET 2003 menu bar select Project -> Add Reference ..., and then select Oracle .DataAccess.dll from the Add Reference dialog box. To include the namespace, add using Oracle.DataAccess.Client; to the top of your code module. Your output should look like this: 

C:\VTB\bin\Release>ValuesThatBind.exe

Country Name: United Kingdom


Code Listing 1: Using a Bind Variable 

static void Main(string[] args)
{
  string constr = "User Id=hr; Password=hr; Data Source=oramag";

  OracleConnection con = new OracleConnection(constr);
  con.Open();

  StringBuilder sbSQL = new StringBuilder();
  sbSQL.Append("select    country_name ");
  sbSQL.Append("from      countries ");
  sbSQL.Append("where     country_id = :country_id");

  OracleCommand cmd = new OracleCommand();
  cmd.Connection = con;
  cmd.CommandText = sbSQL.ToString();

  OracleParameter p_country_id = new OracleParameter();
  p_country_id.OracleDbType = OracleDbType.Varchar2;
  p_country_id.Value = "UK";

  cmd.Parameters.Add(p_country_id);

  OracleDataReader dr = cmd.ExecuteReader();

  if (dr.Read())
  {
    Console.WriteLine("Country Name: {0}", dr.GetOracleString(0));
  }

  dr.Dispose();
  p_country_id.Dispose();
  cmd.Dispose();
  con.Dispose();
}


Bind Variables: Keep Using Them

This column described how to use a simple bind variable in a .NET application with ODP.NET 10g. I encourage you to test your own applications with and without bind variables to see the bind variable advantage.


Mark A. Williams (mawilliams@cheshamdbs.com) is an Oracle ACE, Oracle Certified Professional DBA, and the author of Pro .NET Oracle Programming (Apress, 2004). Williams focuses on solutions for Oracle on Windows and contributes to the Oracle Data Provider for .NET forums on the Oracle Technology Network.


Send us your comments