CODEWITHSUNDEEP
c#.netsqlsql-server
AlexGad
AlexGad

Reputation: 6692

Dynamically replace SQL string with parameters in C#

Has anyone tried to dynamically turn a standard sql string into a parameterized query? In other words, I need to change this:

SELECT * FROM customers WHERE name = 'Adams'

to this:

SELECT * FROM customers WHERE name = @name

I've started to work with the Microsoft.Data.Schema.ScriptDom.Sql and Microsoft.Data.Schema.ScriptDom assemblies, but before I bear down on it, I was wondering if there is something already out in the wild that would do this.

Upvotes: 2

Views: 2404

Answers (2)

Mitch Wheat
Mitch Wheat

Reputation: 300719

SQL Server does this under the covers (and tools like ClearTrace).

One way is to use a regular expression to normalise (not exactly what you are looking for) such as this SQL CLR method based on work done by Itzik Ben-Gan and modified by Adam Machanic:

[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true)]
public static SqlString sqlsig(SqlString querystring)
{
    return (SqlString)Regex.Replace(
       querystring.Value,
       @"([\s,(=<>!](?![^\]]+[\]]))(?:(?:(?:(?:(?# expression coming
       )(?:([N])?(')(?:[^']'')*('))(?# character
       )(?:0x[\da-fA-F]*)(?# binary
       )(?:[-+]?(?:(?:[\d]*\.[\d]*[\d]+)(?# precise number
       )(?:[eE]?[\d]*)))(?# imprecise number
       )(?:[~]?[-+]?(?:[\d]+))(?# integer
       )(?:[nN][uU][lL][lL])(?# null
       ))(?:[\s]?[\+\-\*\/\%\&\\^][\s]?)?)+(?# operators
       )))",
       @"$1$2$3#$4");
}

but accuracy of output may not catch every possible parameterisable phrase.

I'm curious as to why you need this?

Update: As Martin mentioned, there is also the RML Utilities for SQL Server

Description of the Replay Markup Language (RML) Utilities for SQL Server

Upvotes: 5

Abe Miessler
Abe Miessler

Reputation: 85096

Well my knee jerk reaction for someone doing string searches/manipulation would be to use a regex to search/replace.

The hard part becomes determining what you replace with? a parameter called @Adam seems pretty weird and i'm not sure how you would know you want to replace it with @Name.

Can you describe what your end goals is? There may be a better way to do this...

Upvotes: 2

Related Questions