Migrating SQL Server to Oracle – .NET Perspective

Some time back I had written a post on adding parameters to Oracle Stored Procedure. Extending that in this post I will try to outline the challenges to be met for converting a data access layer code (.NET side) for SQL Server to one for Oracle. Hope you find it useful.

1) Multiple Result Sets – Oracle expects a REF CURSOR parameter to be passed for every result set that a Stored Procedure is returning which isn’t required by MSSQL Server. So you would have to create API’s that take number of REF CURSOR’s required and in turn add those. You would even a need a REFCURSOR for retriving scalar values or executing datareaders.

2) Order Of Parameters – As I had posted earlier Oracle expects that parameters should be added in the same order as they are specified in Stored Procedure. To deal with this issue you need to set the property BindByName of OracleCommand class to ‘true’.

3) Parameter Prefix – Oracle doesn’t allow the parameter names specified in Stored Procedure to start with ‘@’. So when you try migrating the Stored Procs of SQL Server to Oracle with help of tools like SwisSQL, Oracle Migration Workbench, etc. they convert that ‘@’ prefix to something else like ‘v_’. Now this would affect your calling code to stored procedures. You would have to replace that ‘@’ with ‘v_’.

4) Handling Error Numbers – Many a times we write database code specific to error numbers of underlying DB. E.g. You may want to re-execute the query in case of a deadlock. Now the issue you would run into is the Deadlock no. of SQL Server is 1205 while for Oracle it’s 60 (N.B. you can catch both of them via SQLException & OracleException – Number property). A way to tackle this is to introduce generic errors in form of Enum inside your Data Access Layer.

5) Date Formats – While this may vary, Oracle expects dd-MMM-yyyy and SQL Server is fine with dd-mm-yyyy. Most of us, pick the date value from a textbox and try to format it. But what is easy for us is to just convert the textbox’s text to a DateTime object and add it to the parameter collection of the underlying command (SqlCommand / OracleCommand). Underlying provider then takes care of format conversion for us.

6) Return Parameters – Although this is not completely from .NET perspective, in SQL Server you don’t have to specify the return parameter at Stored Procedure level. You can add parameter (ParameterDirection.ReturnValue) in your .NET code & retrieve the value on completion of stored procedure. But in Oracle the every parameter you add at .NET level there has to be an equivalent at Stored Procedure level.

I am not sure if this is a comprehensive list but hopefully it covers the most basic ones. Do drop a comment below helping me make this list complete. Just remember this are the changes from .NET side. I will outline changes at database level in another post.

Passing Optional & Unordered Parameters via ODP.NET – PLS-00306

Times change. Recently I was interacting with a team that was using ODP.NET. And they had this stored procedure written in SQL server which they were moving to Oracle DB. They had some challenges in porting their data access code to support Oracle. One of them was – ODP.NET requires all parameters (even if they have default values) to be passed to the Stored Procedure and also expects them in the same order. But with SqlClient the same is not required. Solution to this is use BindByName property of OracleCommand class. Let me elaborate through a small sample code for better understanding:

Consider the following dummy procedure using the above Dummy Table
CREATE PROCEDURE [dbo].[DummyProc]
@Age As Int,
@Name As varchar(50) = ‘AA’ –default value
AS
BEGIN
SET NOCOUNT ON;
DECLARE @noOfRows as INT

SELECT * from Dummy d where d.Age = @Age AND d.Name = @Name

SELECT @noofrows = @@rowcount –tSql is not case sensitive
RETURN @noofrows
END
Now the C# code to pass parameters to this would be:

SqlConnection conn = new SqlConnection(@”ConnString”);
conn.Open();
SqlCommand command = conn.CreateCommand();
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = “DummyProc”;
command.Parameters.Add(new SqlParameter(“@Age”, 2)); //Order doesn’t matter
//command.Parameters.Add(new SqlParameter(“@Name”, “AA”)); – This is not required
SqlParameter paramReturnCode = command.CreateParameter();
paramReturnCode.ParameterName = “ReturnCode”;
paramReturnCode.DbType = DbType.Int32;
paramReturnCode.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(paramReturnCode);
command.ExecuteNonQuery();
Console.WriteLine(command.Parameters[@"ReturnCode"].Value);
conn.Close();

//Output
1

Pretty simple. Let’s move the above to oracle now & access it from ODP.NET. First let’s create a stored procedure in Oracle.

create or replace
PROCEDURE DUMMYPROC
( p_Age IN NUMBER
, p_Name IN VARCHAR2 DEFAULT ‘AA’
, p_NoOfRows OUT NUMBER
) AS
BEGIN
select count(*) into p_noofrows from dummy d where d.age = p_Age AND d.name = p_name;
–N.B. In above ‘into’ is required, for select returning a recordset you need a RefCursor
END DUMMYPROC;

Now let’s write some code using ODP.NET (Oracle.DataAccess.dll)

OracleConnection conn = new OracleConnection(@”ConnString”);
conn.Open();
OracleCommand command = conn.CreateCommand();
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = “DummyProc”;
//command.BindByName = true;
command.Parameters.Add(new OracleParameter(“p_Age”, 2));
//command.Parameters.Add(new OracleParameter(“p_Name”, “AA”)); // This is not required
OracleParameter paramReturnCode = command.CreateParameter();
paramReturnCode.ParameterName = “p_NoOfRows”;
paramReturnCode.OracleDbType = OracleDbType.Int32;
paramReturnCode.Direction = ParameterDirection.Output;
command.Parameters.Add(paramReturnCode);
command.ExecuteNonQuery();
Console.WriteLine(command.Parameters[@"p_NoOfRows"].Value);
conn.Close();

The above code on execution throws an error – PLS-00306: wrong number or types of arguments in call to ‘DUMMYPROC’. The issue here is oracle expects all parameters to be passed in same order for faster execution. But as it turns out that it’s not always the case. Run the same Oracle code above but uncomment line no. 7 – command.BindByName = true

And here you are with the expected output.
//Output
1

Hope this helps .

NHibernate – Lessons Learned

As usual doing a Hello World using a technology turns out to be quite simple. It’s only when you start getting into serious stuff, you run into issues & of course learn a lot. I am sharing few of my learning’s on NHibernate over last month. The DB I am using is Oracle which makes these observations more important as there is a lot available out there on NHibernate but only with SQL SERVER.

1) Optimistic locking: NHibernate has an element called <version>, defined right after ID which can handle concurrency for you. I used a TIMESTAMP column in oracle & mapped it to a DateTime property in my C# class.

<version column=”ROWVERSION” type=”DateTime” name=”RowVersion” />

Now when you insert an entity containing version as above, NHibernate would automatically generate the TimeStamp for you & on update it will also do an automatic comparison. If you need to catch the Exception arising out of this issue, it would be StaleObjectStateException.

2) Cascade issues: A good description of why / what are cascades can be found here, though there are still few issues you will run into. Consider an entity called Class, which has a many-to-one relationship with professor.

<many-to-one name=”Professor” column=”A_PROFESSORID” cascade=”none” lazy=”false” />

For unidirectional associations like many-to-one (there is no one-to-many back), it makes sense to assign “none” to cascade, otherwise you will see superfluous updates. In this scenario, whenever you modify class attributes / properties, it will fire updates for Professor.
Though you still may not be able to completely get rid of superfluous updates. Let me put up one more  scenario. I have a Department & I have a Class (one-to-many). Now for this you would keep cascade=”all” for obvious reasons as a save / update /delete to Department should also save / update / delete Classes. But if you modify only a single property of department you will still end up updating all classes . I hope such superfluous updates will be removed with upcoming versions.

In case you require a soft delete (which is the case for many applications, maintaining an IsActive Flag), you can keep cascade option as “Save-Update” in parent (one-to-many).

3) BiDirectional / UniDirectional navigational relationships / associations: If you want to define a one-way only traversal  through NHibernate you may not be quite successful. Say, a department has classes (one-to-many). Now if your DB (Domain) says that classes can’t stand on their own they have to always belong to a department then you are in a fix. You must provide a bidirectional reference so that whenever you create a new instance of a class, you can bind it to Department.

E.g.
public virtual void AddUniversityClass(UniversityClass universityClass)
{
if(_classes == null)
_classes = new ObservableList();
universityClass.Department = this; //BiDirectional, Class has a department & department has classes
_classes.Add(universityClass);
}

If you don’t do above your insert of Class would fail as they would have a null foreign key for Department. Sounds obvious but looks more like database structure affecting your object model (navigation), which you normally don’t want. Yet some may argue in favor of it, citing benefits of explicit constraints. One way could be making your navigation relationship (many-to-one for instance) private.

4) For collections don’t forget inverse=”true”: Why? I could have elaborated but it’s already explained here. This tells that responsibility for updating the column value is on the other side (just specifying inverse=’true’ on the collection <one-to-many> association).

5) Oracle supports batching through adonet.batch_size: This was confirmed to me by Ayende and Tomer. Batching would club the CRUD statements & send them to DB at one shot giving decent performance gain. The only thing I don’t like here is the attribute name. After all if the batching is done for oracle also, why call it adonet.batch_size? It was confusing for me atleast . Also this works only with ODP.NET.

6) Use fetch=”join” for one-to-many & many-to-one relationships: This is to avoid select N + 1 problem. In my infancy stage of programming I use fall into this trap (limited knowledge of SQL ). N.B. outer-join attribute is deprecated.

7) Always use Criteria queries unless you need to do a projection: Criteria Queries are easier to read, they leverage on configuration files (e.g. fetch=”join”), & they safely handle parameters (developers run into concatenation of parameters using HQL). But alas you can’t use Criteria Queries everywhere. For instance if you need to fetch only few fields of a given entity (no associations, only selected properties for a lookup display), HQL is the only way out.
E.g.
string hqlQuery = “select new Department(D.ID, D.Name) from Department as D”; // Your class needs to have this constructor along with the default one
IQuery _query = Session.CreateQuery(hqlQuery);
IList _list = _query.List();
var observablelist = new ObservableList();
foreach (Department department in _list) { observablelist.Add(department); }
N.B. Projections are of utmost importance in NHibernate. Try to avoid bringing any unnecessary data to the client even if you have to resort to untyped objects. You can use a reader like loop to create your typed object graph & throw that graph back to client.

8 ) Many-To-Many doesn’t work with extra columns: Many-To-Many is normally expressed in database with help of third table. Though NHibernate doesn’t require a corresponding class for the third (mapping) table, but if third table contains additional columns other then the primary keys of involved tables, NHibernate won’t be able to support them. Solution we found was to move those additional attributes from the third table to the involved tables.

9) Components are of good value: To be frank I ignored the components completely when I started with NHibernate. In fact the whole concept of Entity vs. Value objects wasn’t clear to me even after going through DDD books. I mean, I got Value objects don’t require an identity but how does that affect my programming. But boy, I was wrong. I could have elaborated on the same here, but would request you to go through Hibernate in Action section 3.5 (starting page 92-93).

10) unsaved-value is important: You can use it to specify default values for columns. NHibernate also uses it to distinguish between new & modified entities.
E.g. Session.SaveOrUpdate(“Department”, department); //unsaved value would be used here to determine whether to do an insert or update. My Id reads as below for Oracle:

<id name=”ID” column=”A_ID” unsaved-value=”0″>
      <generator class=”Infrastructure.GenerateID, Infrastructure” /><!– GenerateID is a class that implements IIdentifierGenerator interface, in my case it returns a GUID. There is no identity column in Oracle !–>
</id>

11) NHibernate supports database views: You can have a mapping file point to a database view as well. I normally use it for summary display (complex queries). Ensure you check your performance before getting into it (especially no where clauses attached). Views typically won’t have an primary column but your mapping file would still required one for ID element. So you can pick a column of your choice which is unique and make it part of ID by removing the same from property mapping. You can point generator class to native (an easy way to get away).

<id name=”_year” column=”YEAR” unsaved-value=”0″ access=”field”>
      <generator class=”native” />
</id>

12) Parameterized Queries: If you are using NHibernate ideally you would talk to underlying DB with Criteria queries, HQL or raw SQL. NHibernate supports safe parameter passing in all of them as show below:
a) Criteria Queries: ICriteria criteria = Session.CreateCriteria(typeof(Post), “P”).Add(Expression.Eq(“P.PostId”, postId));
b) HQL or Raw SQL: s.CreateQuery(“from Post p where p.Title = :title”).SetString(“title”,”NHibernate”).List();
You also need to add a line to NHibernate configuration as discussed here – <property name=’prepare_sql’>true</property>

13) NHProf: Finally you should use NHProf to improve your understanding on the way NHibernate works.

I would appreciate your observations on above points.

Accessing Oracle from .NET

I am a MS guy & prefer to work & express myself using MS technologies. Lately things changed. I had to work on a product that supported Oracle DB. It has been a long time since I had written code to connect to Oracle DB, less even to install an Oracle Database. Luckily for me Oracle DB was already installed. So I set out to connect to Oracle. I found that there are quite a few  options to connect to Oracle. I am going to discuss them in this post.

1) You can do it via Microsoft .NET data provider for ODBC / OLEDB. This look like old way of communication to me especially when ADO.NET provides the powerful data provider model.

2) Microsoft’s data provider for Oracle – This is installed by default while installing .NET Framework / VS.NET. The provider resides in System.Data.OracleClient assembly. This requires the installation of Oracle client software.
(N.B. If you are using .NET 1.0 / VS.NET 2002 you can download the provider here. Oracle Client software seems to be specific to version of Oracle, for instance you can get access to 10g client from here & 9i Client from here. I haven’t tried the backward compatibility for the Oracle Client Software i.e. accessing 9i database from 10g client).

3) Oracle Data Provider for .NET (ODP.NET) is the ADO.NET provider coming straight from Oracle. It seems to be better in Performance & more close to Oracle in terms of available features. You can download the same here. Note this bundle also includes ASP.NET Provider, Oracle Services for Distributed Transactions, support for .NET 2.0 & 1.x. I tried installing Oracle 11g ODAC, it works perfectly fine with VS.NET 2008 & I was able to connect to my 9i Oracle instance from it. Oracle also provides Developer Tools for Visual Studio (ODT.NET), which for instance allows you to access Oracle from server explorer. A detail list of ODT features can be found here. ODT also comes bundled with ODP, & the same can be downloaded from here. The base assembly for ODP.NET is Oracle.DataAccess.
(N.B. After installation you of Oracle Client / ODP you need to create a file named TNSNAMES.ORA in the %Oracle Installation%/Network/Admin folder).

4) Of course I didn’t use either of above. We are using a O/R mapper & one which seems to be reasonable at present is NHibernate. You can download its latest version 2.0 from here. NHibernate 2.0 wiki is here. Now the question is how to you make NHibernate use ODP.NET? Simplest solution is given here.

Hope this would save your valuable time.