I’m looking for some ideas to implement T-SQL stored procedure transpiler to C# classes.
We have complex T-SQL stored procedure which was developed a while ago when people used to bake business logic into T-SQL.
While it got it’s own benefits of having business logic close to the database but it pretty much hooked up to MS-SQL server. Portability is a serious issue. Scaling is another bottle neck.
Wondering if you guys have any ideas to transpile T-SQL into C# language?
Any pointers in this direction is highly appreciated.
If I understand correctly, you’re looking for a way to extract the stored procedures into a C# application that still connects to the SQL server for all data accesses? At first sight this looks feasible for all language features that do not require running inside the DB (triggers maybe?). You can even target LINQ to produce nice integrated C# code.
As always, you must start with answering a few questions:
How much code needs to be converted? Namely, would it better be done by hand or automated?
How good are the tests (if any)? Without tests you’re basically rewriting from scratch.
Does your management perceive value in making this change? If not forget it.
It’s difficult to provide more insight without knowing the details. If you want we can setup a skype meeting to discuss this.
In general I would tackle this problem in this way.
First I would build or get a parser for T-SQL. If I would have to build it I would use ANTLR.
ANTLR would give me a parse-tree, I would translating to an AST expressed using some library that makes easy to work with trees/graphs. If I had to do it I would use Kolasu, an open-source library we built at Strumenta.
At that point I would do a series of incremental transformations on the AST, translating the T-SQL concepts into C# concepts. Or better, translating nodes representing T-SQL concepts into nodes representing C# concepts, if necessary inserting intermediate representations in between.
Finally I would need to go to the final version of the AST to C#. For that you can use code or templates.
The SQL server installed assemblies bring a parser. What I’d do is just use that (you can find it once installing a dev edition). Already being in .net I’d then convert the parse result to .net expression trees, transform to eliminate much of the record level processing cursor behavior to more set based and then just generate c# from the trees. .Net expression trees has magical powers since .net 4 where they enabled expression statements as well. There’s an obvious link to linq and queryableproviders if needed.