Should I compile C# at runtime in this scenario?

Question

We have a MS SQL 2005 database. The data is uploaded in this database from Excel sheets. These Excel sheets come from various vendors and most of them never maintain a predefined structure so that we can create a separate insertion/update module. Until now I have created a module which accepts an Excel sheet in a predefined format and uploads the data from it. For every vendor someone has to format it in the predefined manner manually which takes too long and is a donkey work.

Now we have removed the manual formatting and put all database columns in one column, and drop-downs containing all Excel columns in another column. The user maps the desired columns and updates the database.

But most of the time this simple mapping falls short, since mostly we have to split/combine values based on some logic. To make it more user friendly and since most of my users are themselves C# programmers I have decided to remove the drop-downs and use text boxes where users can enter direct logic. For example:

dbcol1 = excel[col1].ToString().Replace("-"," ")
    +(int.Parse( excel[col6].ToString())*.1).ToString();

While searching for this I came across dynamically compiling C# code. Since I have never done so before and also I am not sure that this method will be efficient I need to know that am I moving in the right direction with this. Or is there a more efficient and faster way to do this?

Answer 1

Script it up? C# Script is pretty handy, if you want to compile and run code on the fly;

If you can string format something that is correct C# it'll let you compile and run it.

http://www.csscript.net/

For example:

dynamic script = CSScript.Evaluator
                         .LoadMethod(@"void SayHello(string greeting)
                                       {
                                           Console.WriteLine(greeting);
                                       }");
script.SayHello("Hello World!");

Probably not great for production, but you can at least prototype your idea

Answer 2

Normally you would use Reflection.Emit to generate a .NET assembly on the fly and execute that. However, it accepts only Intermediate Language instructions, not C#. And creating a whole new assembly each time you want to execute a piece of code is not efficient at all.

---

Roslyn is Microsoft's attempt at a compiler as a service. This is a new rewritten compiler for C# and VB.Net that can be called by an application to compile code, and it provides information about the code it is compiling.

Anders Hejlsberg (I believe) showed an example using the Roslyn compiler to implement a C# scripting interface: type some C# and it gets executed on the fly. While Roslyn is still in development, you might want to see if it would meed your needs by trying the Roslyn CTP.

C# as a Scripting Language in Your .NET Applications Using Roslyn is a CodeProject article that goes into some detail.

---

Finally, if you cannot (yet) or don't want to use Roslyn, or if it does not provide the execution speed you were looking for, then I think your best bet (in terms of performance) is to parse and interpret the scripting code yourself. However, this is error-prone hard work.