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

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.

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