Oracle Parameterized Queries for the .NET Developer

Introduction

As a developer, my database experience is almost exclusively with Microsoft SQL Server. Recently, I found it necessary to develop a small Windows app to automate data entry for an Oracle database. I won't bore you with the details but the data entry took hours of mind-numbing work executing a single script by hand, one record at a time.

Background

It took a good bit of trial end error to learn how to use Oracle.ManagedDataAccess for VS 2015, but I managed to muddle my way through. I want to share the Gotcha's that I stumbled on. There are some very subtle differences in how to write C# between the Microsoft and Oracle's data client implementations.

.NET T-SQL vs PL-SQL

For this example, I will use a small table of three columns and point out the differences between them and how to write code to access them.

Table Definitions

First point is that there are obvious differences on how equivalent data types are defined in the table definition. Using these two tables, I will expand on the pitfalls when writing code for database operations.

The first pitfalls are in the connections strings. For SQL Server, connecting to the database is a relatively simple affair. Oracle connections are very different. For one, Oracle connections can be done in two different ways, with the tnsnames.ora file or without. The tsanames.ora file contains the server IP address, the server port, and the Oracle service to connect to.

// SQL Server connection string
using System.Data.SqlClient;
// ...
string connStr = "Server=myServer;Database=myDatabase;Trusted_Connection= true;";
SqlConnection conn = new SqlConnection(connStr);
conn.Open();

// Oracle connection string -- without using tnsnames.ora
using Oracle.ManagedDataAccess.Client;

string connStr = "Data Source = (DESCRIPTION = " +
                "(ADDRESS_LIST = " +
                    "(ADDRESS = (PROTOCOL=TCP)(HOST=myHostId)(PORT=MyHostPort)))" +
                    "(CONNECT_DATA = (SERVICE_NAME =myDatabaseService))" +
                ");User Id=myUserName;Password=myPassword;";
OracleConnection conn = new OracleConnection(connStr);
conn.Open();

There are some pretty obvious differences here. For Oracle, you use the server dns/ip address plus the port to connect and then specify the SERVICE_NAME which points the correct database. I could not immediately find any way to use the built in Windows Authentication (Trusted_Connection) so I had to include name and password.

Now to define the DbCommands.

// SQL Server Command
using System.Data.SqlClient;
// ...
// On a side note, it is always preferable to fully qualify your SQL
string sqlStatement = String.Join(" ", new string[]
{
 "SELECT * FROM [schema].[table]",
 "WHERE [schema].[table].RecordId = @RecordId",
 "OR [schema].[table].Name = @Name;"
});

using (SqlCommand cmd = new SqlCommand(sqlStatement, sqlConn))
{
 cmd.Parameters.Add("@RecordId", SqlDbType.Int).Value = _recordId;
 cmd.Parameters.Add("@Name", SqlDbType.NvarChar).Value = _name;
 
 using (SqlDataReader reader = cmd.ExecuteReader())
 {
  // Do something with data...
 }
}

// Oracle Command
using Oracle.ManagedDataAccess.Client;
// ...
string sqlStatement = String.Join(" ", new string[]
{
 "SELECT * FROM [schema].[table]",
 "WHERE [schema].[table].RecordId = :RecordId",
 "OR [schema].[table].Name = :Name" // Gotcha #1 - you cannot 
 				// put a semicolon at the end of the statement
});

using (OracleCommand cmd = new OracleCommand())
{
 cmd.Connection = oraConn;
 cmd.BindByName = true;  // Gotcha #2 - the Oracle command 
 		// binds parameters by position by default unless you tell it otherwise
 cmd.CommandText = sqlStatement;
 
 cmd.Parameters.Add(new OracleParameter("RecordId", OracleDbType.Int32));
 // Gotcha #3 - see how the parameter name decoration (:) has been dropped 
 // Gotcha #4 - the table definition is NUMBER(10,0) 
 // but there is no Number in the OracleDbType enumeration.  
 // You have to figure out what native .Net data type will fit the data without loss
 cmd.Parameters.Add(new OracleParameter("Name", OracleDbType.NVarchar2));
 
 cmd.Parameters[0].Value = _recordId;
 cmd.Parameters[1].Value = _name;
 
 using (OracleDataReader reader = cmd.ExecuteReader())
 {
  // Do something with data...
 }
}

The differences between the two are very subtle and can cause headaches until how you figure it out. The fact that documentation from Oracle is spartan at best will only serve to frustrate any developer trying to write .NET for Oracle for the first time.

The errors produced by the gotchas are not very informative either.

• Gotcha #1 - Invalid Character error
• Gotcha's #2 & #3 - Empty Parameter error

Points of Interest

This is subjective, but it seems that Oracle is setting the bar to entry rather high for its database products. By introducing very subtle differences in how the code is written and not clearly pointing out the differences, developers might find it difficult to overcome the initial learning curve to writing .NET for Oracle.

History

• 2/5/2016 - Initial draft