String Replacements for Word Merge

using asp.net 4

we do a lot of Word merges at work. rather than using the complicated conditional statements of Word i want to embed my own syntax. s开发者_JAVA百科omething like:

Dear Mr. { select lastname from users where userid = 7 },
Your invoice for this quarter is: ${ select amount from invoices where userid = 7 }.
......

ideally, i'd like this to get turned into:

string.Format("Dear Mr. {0},  Your invoice for this quarter is: ${1}", sqlEval[0], sqlEval[1]);

any ideas?

Well, I don't really recommend rolling your own solution for this, however I will answer the question as asked.

First, you need to process the text and extract the SQL statements. For that you'll need a simple parser:

/// <summary>Parses the input string and extracts a unique list of all placeholders.</summary>
/// <remarks>
///  This method does not handle escaping of delimiters
/// </remarks>
public static IList<string> Parse(string input)
{
    const char placeholderDelimStart = '{';
    const char placeholderDelimEnd = '}';

    var characters = input.ToCharArray();
    var placeHolders = new List<string>();
    string currentPlaceHolder = string.Empty;
    bool inPlaceHolder = false;
    for (int i = 0; i < characters.Length; i++)
    {
        var currentChar = characters[i];

        // Start of a placeholder
        if (!inPlaceHolder && currentChar == placeholderDelimStart)
        {
            currentPlaceHolder = string.Empty;
            inPlaceHolder = true;
            continue;
        }

        // Start of a placeholder when we already have one
        if (inPlaceHolder && currentChar == placeholderDelimStart)
            throw new InvalidOperationException("Unexpected character detected at position " + i);

        // We found the end marker while in a placeholder - we're done with this placeholder
        if (inPlaceHolder && currentChar == placeholderDelimEnd)
        {
            if (!placeHolders.Contains(currentPlaceHolder))
                placeHolders.Add(currentPlaceHolder);
            inPlaceHolder = false;
            continue;
        }

        // End of a placeholder with no matching start
        if (!inPlaceHolder && currentChar == placeholderDelimEnd)
            throw new InvalidOperationException("Unexpected character detected at position " + i);

        if (inPlaceHolder)
            currentPlaceHolder += currentChar;
    }
    return placeHolders;
}

Okay, so that will get you a list of SQL statements extracted from the input text. You'll probably want to tweak it to use properly typed parser exceptions and some input guards (which I elided for clarity).

Now you just need to replace those placeholders with the results of the evaluated SQL:

// Sample input
var input = "Hello Mr. {select firstname from users where userid=7}";

string output = input;
var extractedStatements = Parse(input);
foreach (var statement in extractedStatements)
{
    // Execute the SQL statement
    var result = Evaluate(statement);

    // Update the output with the result of the SQL statement
    output = output.Replace("{" + statement + "}", result);
}

This is obviously not the most efficient way to do this, but I think it sufficiently demonstrates the concept without muddying the waters.

You'll need to define the Evaluate(string) method. This will handle executing the SQL.

I just finished building a proprietary solution like this for a law firm here. I evaluated a product called Windward reports. It's a tad pricy, esp if you need a lot of copies, but for one user it's not bad.

it can pull from XML or SQL data sources (or more if I remember).

Might be worth a look (and no I don't work for 'em, just evaluated their stuff)

You might want to check out the razor engine project on codeplex

http://razorengine.codeplex.com/

Using SQL etc within your template looks like a bad idea. I'd suggest you make a ViewModel for each template.

The Razor thing is really easy to use. Just add a reference, import the namespace, and call the Parse method like so:

(VB guy so excuse syntax!)

MyViewModel myModel = new MyViewModel("Bob",150.00); //set properties

string myTemplate = "Dear Mr. @Model.FirstName,  Your invoice for this quarter is: @Model.InvoiceAmount";

string myOutput = Razor.Parse(myTemplate, myModel);

Your string can come from anywhere - I use this with my templates stored in a database, you could equally load it from files or whatever. It's very powerful as a view engine, you can do conditional stuff, loops, etc etc.

i ended up rolling my own solution but thanks. i really dislike if statements. i'll need to refactor them out. here it is:

var mailingMergeString = new MailingMergeString(input);
var output = mailingMergeString.ParseMailingMergeString();

public class MailingMergeString
{
    private string _input;

    public MailingMergeString(string input)
    {
        _input = input;
    }

    public string ParseMailingMergeString()
    {
        IList<SqlReplaceCommand> sqlCommands = new List<SqlReplaceCommand>();
        var i = 0;
        const string openBrace = "{";
        const string closeBrace = "}";

        while (string.IsNullOrWhiteSpace(_input) == false)
        {
            var sqlReplaceCommand = new SqlReplaceCommand();
            var open = _input.IndexOf(openBrace) + 1;
            var close = _input.IndexOf(closeBrace);
            var length = close != -1 ? close - open : _input.Length;
            var newInput = _input.Substring(close + 1);
            var nextClose = newInput.Contains(openBrace) ? newInput.IndexOf(openBrace) : newInput.Length;

            if (i == 0 && open > 0)
            {
                sqlReplaceCommand.Text = _input.Substring(0, open - 1);
                _input = _input.Substring(open - 1);
            }
            else
            {
                sqlReplaceCommand.Command = _input.Substring(open, length);
                sqlReplaceCommand.PlaceHolder = openBrace + i + closeBrace;
                sqlReplaceCommand.Text = _input.Substring(close + 1, nextClose);
                sqlReplaceCommand.NewInput = _input.Substring(close + 1);

                _input = newInput.Contains(openBrace) ? sqlReplaceCommand.NewInput : string.Empty;
            }

            sqlCommands.Add(sqlReplaceCommand);
            i++;
        }

        return sqlCommands.GetParsedString();
    }

    internal class SqlReplaceCommand
    {
        public string Command { get; set; }

        public string SqlResult { get; set; }

        public string PlaceHolder { get; set; }

        public string Text { get; set; }

        protected internal string NewInput { get; set; }
    }
}

internal static class SqlReplaceExtensions
{
    public static string GetParsedString(this IEnumerable<MailingMergeString.SqlReplaceCommand> sqlCommands)
    {
        return sqlCommands.Aggregate("", (current, replaceCommand) => current + (replaceCommand.PlaceHolder + replaceCommand.Text));
    }
}