Looking to design a tool to Translate Business logic from Stored Procedures to C# Business Layer [closed]

Question

Without getting into a discussion about whether the business logic should be in the database or at the application layer, since it has been covered elsewhere.

My team is translating 100K+ lines of PL/SQL code and moving the logic from the database into the application. We were using VB6 with straight calls to Oracle 9i Stored Procedures and Ad-hoc queries and are now using C#, .net 3.5, Winforms with NHibernate to an Oracle 9i database.

We have already found a wonderful tool to assist in converting the Ad-hoc queries, SmartCode, but it only creates code based on Tables and Views. We are looking for a tool to assist in converting the Stored Procedures.

The Stored Procedures have most of the Business logic in them that we want to migrate to the application layer. We are wondering if there are any tools to convert the Stored Procedures into C# code.

Assuming there are none, what would be the best place to start if we develop the tool in-house/open source. Is there another similar system with similar goals that could be used as a starting place?

Accepted Answer UPDATE: I have selected scope-creep's answer, because it appears to be the best method for implementing the issue presented in the question. For those that deal with this same issue, I heartedly recommend Adam's response, as he has strongly advocated against the use of a tool and provides a strong rationale. He has also provided the most interaction with this question and had the most up-voted response.

Thank you to everyone for your help and dialog.

Staats

How much code do you have in the stored procedures? If you convert it all by hand, how long do you think it will take? – Checani

Thousands of lines. From a cursory analysis, much of the code is similar, just particular to the table in question. Most of the "similar" code, which I estimate to be 60-70% is around null/empty string issues and soft-delete filtering. – Staats

I estimate it would take many months to convert the code by hand, which is why I am considering using or creating a tool to do this. – Staats

If its a few thousand lines, you are unlikely to be able to create a tool with less effort than it takes to just convert it. If you are talking about 100K lines, an automated tool could be a serious win. In between... its a gamble, especially if you don't have experience building such tools. – Checani

If you are interested in a tool that can parse PLSQL and could be the basis of a C# code generator, check my bio. Above caveats still apply. – Checani

It is above 100K lines and I agree that a tool would be a serious win, which is why I have offered a bounty. – Staats

The tool will most likely have to be custom... I've built relatively similar tools / processes in the past... Most of the procs were just basic CRUD that followed a very similar pattern, so we were able to automatically convert most of them. However, anything that had advanced logic (performing lookups, performing addition inserts, deletes, etc) is a lot more difficult. One way to handle it is to categorize the kinds of procs you have, then build tools for the biggest categories. Just out of curiosity - why do you want to move the business logic out of the database? – Blenny

Answer 1

One way to do it is to use ANTLR v3, to construct a domain specific language. ANTLR V3 has a PL/SQL Pl/SQL grammer for 10g,11g to construct a lexer/parser for PL/SQL, which would be the first step. A C# 3.0 code generator is available for the backend for C#3.0. The code generator is still under development, but it's in an advanced state.

I don't know how much work would ensue with this approach, but I certainly think it would cost less than doing manual translation.

There is a book available called The Definitive ANTLR Reference: Building Domain-Specific Languages . I know suggesting a book at this time, when you have a sh't load of work to do is crass, but it will give you an idea of the process involved, and perhaps enough to cost the conversion.

There has already been a question on Stack Overflow: Writing a Language Translator which links to the ANTLR Morph project, which is a subproject to define a common translation mechanism. The Doc and FAQ explain how it works. Essentially a script is used to define a translation mechanism. Its in early stages yet, but could be worth a look, as this is a common scenario which has not been addressed as yet.

This example explains how do create tree transformations, i.e. walking the tree to output translated code, found here: Tree Translations, with associated ANTLR documentation: Tree Consuruction

Finding the right compiler engineer would be key to success. I had a look at some sites, and their are a few compiler engineers available. I think it would be less expensive employing 1 or 2 compiler engineers for 3+ months, to do the work than employing than 4+ engineers for 3+ months for manual translation. In the UK you would be looking for a contractor to do it.

Hope that helps Bob.

Edit: 01/08

I've found another book which discusses creating language translators, found here, called Language Implementation Patterns

Triglyceride

@scope-creep are there any sample projects to work off of? If so, could you point me to them? – Staats

Even with good infrastructure, building a good translator takes significant time. In essense, what you have to do it to write translation rules for every basic source langauge idiom, and for those source language idioms of special interest, and get all these elements to work together. My team has experience doing this, and for a 100K SLOC, you are likely to see most of the PLSQL language elements. My guess is that one really good engineer might accomplish this in a year. 3 engineers won't succeed in 4 months. I believe in automated translation; but the scale has to be right. – Checani

Answer 2

I don't believe there are any converters for SQL to C#.

As for approaching the creation of such a tool, I would first say, don't... your business requirement sounds like it is to get the logic into C#.

Depending on the state of the application, you can do this in many ways: one sproc at a time; logical entities at a time (all customer logic, etc); whole hog; agile-ish where you leave the sprocs alone for the time being and call straight into them from C#, and then slowly take one of the prior approaches - always leaving yourself with a functioning application.

Loaded question really :-)

I personally would first try to get it working in C# straight calling into the sprocs. Then take logical entities, as you will find they may reference other sprocs. Doing a sproc at a time will fragment your C# logic during development and add extra overhead to the creation of business classes.

The strength in a C# domain model is the clear boundaries of responsibility and grouping of behaviour into your logical entities - so taking a sproc at a time, you will not see the bigger picture. Using a converter, it will end in unreadable, unmanageable code that you then have to learn - something you don't need to do if you created it in the first place.

So my conclusion, if there is one, is to save yourself time in the future and take this as an opportunity to redesign your business layer - as you likely have knowledge and experience of the system's production behaviour from out in the wild, so the conversion can factor in any lessons learnt.

Update: it turns out you have tooling options for the conversion. The only thing I will say to that approach is this: the resulting code is not going to be pretty. You have the benefit that your current SQL is understood by the development team - they know the code. A code generator is going to produce 100% new code that no one knows. Learning curve... as you are going to need to verify the output of the tool to make sure it isn't mutating your logic - no tool is infallible.

If you decide to use the tool, I can only suggest to break the conversion down into very, very small parts (presumably the smallest is going to be a script (or perhaps even batch within a script)). When you have a small set of conversion results, integrate this into the application and pass it through a review process.

Paschal

Totally agree this... Too many variables to pull WHERE clauses into C# code and create the proper objects and architecture. – Warfore

I have done further analysis and discovered this has more than 100K lines of PL/SQL. Would you still agree to doing this one at a time? I think it would take months of conversion work, and would be bug prone. – Staats

@Lucas It would be equally as bug prone using a conversion tool. I would say for such a large code base, you need to review whether it is financially feasible to migrate the code. I would suggest the halfway house - get C# communicating and using your SQL as it stands, and then as you perform project iterations, make sure some conversion work is given priority. There is no easy answer, it's going to be a slog... It sounds like most of the logic is in SQL so a conversion to C# is likely going to be akin to re-writing the entire application anyway, so the testing overhead is going to be huge. – Paschal

@Lucas reviewing the comments to your question, it sounds like Ira can help you out with a tool. If you manage to get something working in that regard, post back and share the goodness - I'd be curious to see how a SQL to C# conversion occurs. – Paschal

Answer 3

I couldn't find anything that converts PL/SQL directly to C#, but found a couple products that convert PL/SQL to Java (which you can then convert to C#).

Convert PL/SQL to Java:

SwisSQL: http://www.swissql.com/products/oracle-to-java/oracle-to-java.html
Exodus: http://www.ciphersoftinc.com/products/plsql-to-java-conversion-tools.html

Convert Java to C#

StackOverflow answer: Tool to convert java to c# code

Glaive

Indirect, but a technically correct answer - I'd really hate to see the resulting C# though... – Paschal

@Mien, this is definitely getting closer, though I agree with Adam, if there have been many layers of conversion, how much of the original logic would be in place and how maintainable would it be... I'm going to run some tests and report back. – Staats

Answer 4

Based on the input from scope-creep, Adam and Ira-Baxter. Especially, concerning doing things in a bite-sized fashion, clear boundaries of responsibility and grouping of behavior

One way would be to extend the SmartCode tool, which is already generating nice entities to work with and since it is open-source:

1. Extend the tool to read in the stored procedures, allowing for selection of stored procedures with reverse table/view selection (assuming there are any references in the stored procedures for tables/views not currently mapped).
2. Using the entities created by the tables and views, convert the packages, with containing stored procedures, to a class hierarchy.
3. Translate the business logic using lexicons and grammars.

Thoughts?

Staats

I can't comment too specifically, but open-source projects often have, umm, strange code bases. You will have to develop the tool off your own back and test it - basically the Total Cost of Ownership is going to be large. Also, there is no guarantee it can be crafted into what you need. I would suggest taking a small subset of your application logic in order to make a proof-of-concept of this SmartCode tool before you commit. – Paschal

But if you can get it doing what you require, then there is nothing wrong with this approach - but put the project management head on and question how much time this will take versus a re-write. The latter you can start right away and you don't lose the expertise you already have on the SQL. A new tool is an entirely different project. – Paschal

Answer 5

I hope this helps:

• Generating a Stored Procedure Declarations for NHibernate in PL/SQL
• Generating a Stored Procedure Wrapper for NHibernate in PL/SQL

If you're doing NHibernate with Oracle, you want to read Devio's blog.

• LLBLGen Pro feature:

  • Building blocks for your code: Entities, Value Types, Typed Lists, Typed Views and Stored Procedure calls.

Undercover