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.

Enhancing Team’s Productivity – .NET, VS.NET

Software projects normally have a scary deadline. One way to meet them is to increase your team’s productivity. I am going to jot down few practices I have been following, & look forward to read the ones you follow:

1) Using Macros for VS.NET repetitive tasks: I normally have a complex VS.NET folder Structure & this structure gets repeated for every module added. For instance creating a new module in my current solution requires you to create about 10 folders, starting from DTO, Factories, Domain Model, Exceptions, etc. This took quite a bit of time of developers about start a new module. So I created a VS.NET macro to automate this task.

2) Code Generators: I have seen many companies having a framework wherein they take care of common tasks starting from transactions to Workflows, etc. Their implementation normally revolves around Factory method or Template method patterns. If you follow this approach, using code generator you can create “Fill-In-The-Blanks” template for your developers. I don’t have a framework like this, but I have still created few code generators. I normally use NHibernate for my projects. Given it’s a fantastic tool, it takes sometime to generate mappings & corresponding classes which again might be error prone. I have created a code generator which not allows you to create mappings & classes but also creates Factories & Repositories for entities that are generated. (N.B. You can find one alternative approach to mapping files here).

3) Extracting Code Snippets: I use Snippet Designer a lot to extract common code patterns & insert them with zero effort. This definitely saves a lot of valuable time.

4) Tools: Though they cost we can’t program without them – ReSharper, CodeRush, etc. I personally use ReSharper. You can also check out plugins available for these tools. You can find a good plugin here.

5) Design Techniques: I normally prefer making cross cutting concerns oblivious to developers. This includes things are Security, Transactions, Logging, etc. They help in reducing lot of developer’s key strokes. You catch my recent article on same here. You can also use techniques like Visual Inheritance, though it’s tough to get it right with WPF (for a way out you can look here).

6) Keyboard Shortcuts: Although minor this can save lot of mouse clicks. The ones I use most frequently are CTRL + K + D (Alignment), CTRL + K + C (Comment), CTRL + K + U (UnComment), F12 (Navigate to a type) (though I prefer ReSharper shortcuts over above). An awesome way to speed up with XAML editing in VS.NET is this.

7) Proper Training: I guess all of us understand the importance of this but the level to which we get it is always less. I would also recommend productivity trainings once you master the basics of a technology / framework. I have written about one such technique here.

8 ) Appropriate Hardware: I pity the developers running on 1 GB Ram with VS.NET 2008, SQL Server, Oracle, etc. (even 2 GB is less).

9) Shared knowledge base: When working with a team you find many issues are recurring. A issue solved today by one developer is faced tomorrow by another developer. Keeping a shared knowledge base for team definitely boosts productivity.

10) Builds that always work: Integration of local working copies can consume a lot of valuable time of your projects. Normally developers have a tendency to get away with their check in. Have an hour per day where developers can integrate their work (or you can have couple per week). You can automate the build to save more time, but I leave it as a personal choice considering competency & cost involved.

11) Holidays/Working hours/Re-creation: Hmm… May be I am getting into aspects which I shouldn’t be not to mention salary. So I will stop here.

Let me know what you do to gear up your team’s producitivity .