Post reply

How to replace placeholders in a string from an ordered list of column names

  • September 5, 2019 at 4:13 pm

    #3677695

    We have a CI/CD project underway to use the options pattern in C#/.NET Core to remove settings and configuration from api's.  Email configuration is a particular issue because there're two things to manage: 1) smtp client information, and 2) the email templates (which get formatted with user information).  The emails here are for sending password reset links, password reset confirmations, username reminders, etc...  There has always been an option to send in plain text, html came later.  The smtp info and email templates are currently persisted as application settings (basically).  This system manages identities and passwords for multiple "site clients" (the pk of which is sitec_id).

    The current flow for password reset requests.  This is for a user to request to make a new password (users get sent a link to a secure site)):

    1. User (of a site client, through a registered application) sends a password reset request to the api
    2. Api does rpc (sql proc) to get personal info (returned as json)
    3. Api builds email body using String.Format() function (which combines email template with personal info) in C#
    4. Api sends email (with a link to a secure site) using smtp info from configuration

    There are a whole bunch of issues with this flow.  There are many, many text files and configurations in dev, stage, and prod and it has to be managed forever.  Also, there are undesirable (but acceptable if necessary) type conversions between SqlString and CLR string.  Anyway. rather than criticize the status quo here's how we want it to work:

    1. User (of a site client, through a registered application) sends a password reset request to the api
    2. Api does rpc (sql proc) to get smtp info and formatted email info (returned as json)
    3. Api sends email using smtp and formatted email from json (no options or configuration in C#)

    Storing and retrieving the smtp info is trivial.  Doing the string replacement of placeholders using an ordered list of column names doesn't seem to have an obvious solution tho.  It's possible we could continue the statue quo but with smtp info in the database.

    Google leads to this MSDN post:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e7fdecd7-a322-4cc1-b939-5dd13fa15a38/how-to-replace-placeholders-in-a-string-from-a-table-or-list?forum=transactsql

    The sample code offered is only for a 3 substitution limit.  To generalize the code a recursive cte seems likely to work but maybe there's a better way?  Also, how to store the ordered list of columns to be substituted?  Could this be done without dynamic sql (which would cause me to give up)?

     

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • September 6, 2019 at 5:10 pm

    #3678046

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • September 6, 2019 at 8:40 pm

    #3678093

    This is done.  I couldn't find any satisfactory solution to placeholder replacement so the string formatting still happens in C#.  All of the text files and templates have been moved to tables.  At some point we're going to look at a real templating solution.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply