C# .NET Embedded SQL Conversion - Oracle to SQL Server Migration

When you migrate an Oracle database to Microsoft SQL Server, you have to modify your applications to work with the new database.

In C# applications you have to convert the syntax of SQL statements as well as ADO.NET Data Provider classes, methods and types if you use an Oracle specific provider such as Oracle.DataAccess.Client or System.Data.OracleClient.

If you use Oracle Data Provider for .NET (ODP.NET, Oracle.DataAccess.Client namespace) or Microsoft Data Provider for Oracle (System.Data.OracleClient namespace), you have to modify your applications to use Microsoft Data Provider for SQL Server (System.Data.SqlClient namespace).

List of changes for data provider classes:

If you use parameters, you have to map types:

using directive allows the use of types in a namespace without having to specify the namespace:

C# for Oracle:

  // Oracle ODP.NET
  using Oracle.DataAccess.Client;
 
  // or .NET Framework Data Provider for Oracle
  using System.Data.OracleClient;

C# for SQL Server:

  // .NET Framework Data Provider for SQL Server
  using System.Data;
  using System.Data.SqlClient;

Note that System.Data is required to work with SQL Server types (SqlDbType Enumeration).

OracleConnection represents a connection to an Oracle database. For a SQL Server connection, you have to use SqlConnection class.

C# for Oracle:

  string connectionString = "Data Source=db;User Id=user;Password=password;"
 
  // Create a connection object and assign a connection string
  OracleConnection con =  new OracleConnection();
  con.ConnectionString = connectionString;
 
  // Open connection
  con.Open();

C# for SQL Server:

  string connectionString = "Data Source=db;User Id=user;Password=password;"
 
  // Create a connection object and assign a connection string
  SqlConnection con =  new SqlConnection();
  con.ConnectionString = connectionString;
 
  // Open connection
  con.Open();

Note that both Oracle and SQL Server data providers support Data Source, User Id and Password attributes in the connection string. In Oracle, Data Source specifies a TNS name, while in SQL Server it specifies a server name.

OracleTransaction represents a transaction in an Oracle database. For SQL Server, you have to use SqlTransaction class.

A transaction object is created by calling BeginTransaction method on the connection object.

C# for Oracle:

  // Open connection
  con.Open();
 
  // Start a transaction and create the transaction object
  OracleTransaction tran = con.BeginTransaction();

C# for SQL Server:

  // Open connection
  con.Open();
 
  // Start a transaction and create the transaction object
  SqlTransaction tran = con.BeginTransaction();

OracleCommand represents a SQL statement or a stored procedure. For SQL Server, you have to use SqlCommand class.

C# for Oracle:

  string query = "SELECT NVL(name, 'N/a') FROM cities";
  OracleCommand cmd = new OracleCommand(query);

C# for SQL Server:

  string query = "SELECT ISNULL(name, 'N/a') FROM cities";
  SqlCommand cmd = new SqlCommand(query);

Note that there are differences in the SQL languages of Oracle and SQL Server, so besides changing the class name, you may also need to modify the SQL statements (NVL function to ISNULL as in our example).

OracleParameter represents a parameter for a SQL statement. For SQL Server, you have to use SqlParameter class.

Besides changing the class name, you also have to map the data type of each parameter:

C# for Oracle:

  string stmt = "INSERT INTO cities VALUES (:name)";
 
  // Create a command for INSERT statement
  OracleCommand cmd = new OracleCommand(stmt);
 
  // Add a parameter and specify its value
  cmd.Parameters.Add(":name", OracleType.VarChar, 70).Value = "San Francisco";

C# for SQL Server:

  string stmt = "INSERT INTO cities VALUES (@name)";
 
  // Create a command for INSERT statement
  SqlCommand cmd = new SqlCommand(stmt);
 
  // Add a parameter and specify its value
  cmd.Parameters.Add("@name", SqlDbType.VarChar, 70).Value = "San Francisco";

Note that Oracle data providers use named parameters in the format :param or param, while in SQL Server, you have to use named parameter in the format @param.

More information on embedded SQL conversion in C# applications:

• Converting RETURNING Clause in INSERT Statements

SQLines offers tools and services to help you migrate C# applications from Oracle to Microsoft SQL Server. Please contact us at support@sqlines.com for more information.