Having trouble with string.Replace / Regex.Replace

Question

I have a sql query like this:

UPDATE StockUpdateQueue SET Synced = 1, SyncedAt = GETDATE() WHERE Id IN (@p0,@p1);

This query is dynamically generated. What I want to do is run it through a function, which will effectively replace all the @pN parameters with their corresponding values.

I have tried to do this with standard string.Replace as well as Regex.Replace with no luck - the replace is not taking place.

This is what I tried so far:

class Program
{
    static string _lastQuery;

    static void Main(string[] args)
    {
        var sqlQuery = "UPDATE StockUpdateQueue SET Synced = 1, SyncedAt = GETDATE() WHERE Id IN (@p0,@p1);";
        var sqlParamters = new Dictionary<string, object>()
        {
            { "@p0", 12345 },
            { "@p1", 65432 }
        };

        LogLastQuery(sqlQuery, sqlParamters);
    }

    static void LogLastQuery(string sqlQuery, Dictionary<string, object> sqlParamters = null)
    {
        _lastQuery = sqlQuery;
        if (sqlParamters != null && sqlParamters.Count > 0)
            foreach (KeyValuePair<string, object> sqlParamter in sqlParamters)
                _lastQuery = Regex.Replace(
                    _lastQuery, 
                    "\\@" + sqlParamter.Key,
                    sqlParamter.Value.GetType() == typeof(int) || sqlParamter.Value.GetType() == typeof(decimal)
                        ? sqlParamter.Value.ToString() 
                        : "'" + sqlParamter.Value.ToString() + "'");
    }
}

I want the function to do the parameter replace and ideally output something like this:

UPDATE StockUpdateQueue SET Synced = 1, SyncedAt = GETDATE() WHERE Id IN (12345,65432);

Any ideas?

Update

I use the sqlQuery and sqlParamters just as user un-lucky has shown, i.e.

{
    mySqlCommand.Parameters.AddWithValue(sqlParamter .Key, sqlParamter.Value);
}

It turns out, I have been generating the dictionary keys without the @ and .NET has been automatically adding them for me (when they are missing - as explained by user juharr).

Sometimes, I generate the sql query and parameters dynamically - where the list of keys and their corresponding values were generated in a for/foreach loop. This resulted in a case of mix usage going on.

So, to handle this - I've updated my function like this and it's working as intended:

internal void LogLastQuery(string sqlQuery, Dictionary<string, object> sqlParamters = null)
{
    _lastQuery = sqlQuery;
    if (sqlParamters != null && sqlParamters.Count > 0)
        foreach (KeyValuePair<string, object> sqlParamter in sqlParamters)
            _lastQuery = Regex.Replace(
                _lastQuery,
                (sqlParamter.Key.ToString()[0] != '@' ? "\\@" : "") + sqlParamter.Key,
                sqlParamter.Value.GetType() == typeof(int) || sqlParamter.Value.GetType() == typeof(decimal)
                    ? sqlParamter.Value.ToString()
                    : "'" + sqlParamter.Value.ToString() + "'");
}

This now deals with both cases. I am not sure if this is the best method, but it works fine for now.

Some people are pissed of if humans are making (for those) stupid simple mistakes. However I do not think, that downvoting without critism is the right way (I'm not the downvoter). — realvictorprm
– realvictorprm, Commented Jan 5, 2017 at 17:30

Yury Glushkov · Accepted Answer · 2017-01-05 17:28:20Z

6

Your key in dictionary already has "@" symbol, try use sqlParamter.Key instead of "\\@" + sqlParamter.Key.

answered Jan 5, 2017 at 17:28

Yury Glushkov

7607 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sujith karivelil · Accepted Answer · 2017-01-05 17:33:12Z

1

I think its better to create SqlCommand with that query and add those values as parameters will be the best option for you to execute the query, instead for replacement; which means you can try something like this:

 var sqlQuery = "UPDATE StockUpdateQueue SET Synced = 1, SyncedAt = GETDATE() WHERE Id IN (@p0,@p1);";
 var sqlParamters = new Dictionary<string, object>()
 {
      { "@p0", 12345 },
      { "@p1", 65432 }
 };
 SqlCommand mySqlCommand = new SqlCommand(sqlQuery , conSQL); // conSQL is the connection
 foreach (KeyValuePair<string, object> sqlParamter in sqlParamters)
 {
    mySqlCommand.Parameters.AddWithValue(sqlParamter .Key, sqlParamter.Value);
 }
 mySqlCommand .ExecuteNonQuery();

edited Jan 5, 2017 at 17:33

answered Jan 5, 2017 at 17:31

sujith karivelil

29.1k7 gold badges60 silver badges90 bronze badges

5 Comments

Latheesan Over a year ago

hi. I am doing exactly this already. but in the event of an exception occuring, I wanted to see what was the last sql query that ran and if any params were present, what those values were, so i wrote this LogLastQuery function.

sujith karivelil Over a year ago

@Latheesan: cool the go ahead with Jiří Gluškov's answer. happy coding

juharr Over a year ago

@Latheesan Why not just log the query and the parameters separately

Latheesan Over a year ago

I just noticed something odd. In my original question, I've actually got a typo. In my real app, I was actually doing this:

var sqlParamters = new Dictionary<string, object>()         {             { "p0", 12345 },             { "p1", 65432 }         };

and this was somehow getting executed, even though I had no @ for my dictionary keys when I did: mySqlCommand.Parameters.AddWithValue(sqlParamter .Key, sqlParamter.Value); Strange.

juharr Over a year ago

@Latheesan Not sure about mySqlParamter, but when creating SqlParamter if you leave off the "@" in the name it will automatically add it for you.

Collectives™ on Stack Overflow

Having trouble with string.Replace / Regex.Replace

Update

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Update

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related