Regular expression to replace SQL Parameter names with a '?'

  • Thread starter Thread starter Michael Lang
  • Start date Start date
M

Michael Lang

I'm trying to use regular expressions to format a SQL command to work with
ODBC. So I need to replace the following:

Update table Set (Name=@Name) Where ID = @ID
with...
Update table Set (Name=?) Where ID = ?

but also keep...
Select @@Identity
unchanged.

I started with...
Regex.Replace(commandText, @"@\w+","?")

but it does not leave the @@Identity alone. So I did this next...
Regex.Replace(commandText, @"[^@]@\w+","?")

But that always removes the character before the single '@' also... such
as...
Update table Set (Name?) Where ID =?

Anyone know how to fix this?

FYI,
usually you create a standard ODbcCommand like this...
cmd = new OdbcCommand("SELECT * FROM Customers " +
"WHERE Country = ? AND City = ?", conn);

However, I have a generic data access DLL that works for SQL or ODBC. The
same code creates one command...
cmd = new GenCommand("SELECT * FROM Customers " +
"WHERE Country = @Country AND City = @City", conn);

The GenCommand class handles creating the appropriate underlying data
provider instance (either SqlCommand or OdbcCommand), and formats the
command text before passing to the constructor or the provider's type. For
those interested, you can see details from link in my signature.
 
Actually, I have almost finished developing my own Regex composing and
testing app that will be available soon on the coming site
www.knowdotnet.com.

In the benchmarking I did, the difference was consistent, but not
significant - only around 1% more efficient for source strings with various
lengths. The main thing is that it just seems more logical and is easier to
read (although I guess if you were performing this thing millions of times,
1% may be significant to you)

Brian Davis
www.knowdotnet.com



Michael Lang said:
Yes, both worked. How can you tell which is more efficient? Any particular
resource you use outside of the .NET help files?

--
Michael Lang, MCSD
See my .NET open source projects
http://sourceforge.net/projects/dbobjecter (code generator)
http://sourceforge.net/projects/genadonet ("generic" ADO.NET)

Brian Davis said:
Actually, it makes more sense and is more efficient to use

Regex.Replace(commandText, @"(?<!@)@\w+","?")

but both should return the same results.

Brian Davis
www.knowdotnet.com



Brian Davis said:
Try this:

Regex.Replace(commandText, @"(?<=[^@])@\w+","?")


Brian Davis
www.knowdotnet.com



I'm trying to use regular expressions to format a SQL command to
Click to expand...
work
with
ODBC. So I need to replace the following:

Update table Set (Name=@Name) Where ID = @ID
with...
Update table Set (Name=?) Where ID = ?

but also keep...
Select @@Identity
unchanged.

I started with...
Regex.Replace(commandText, @"@\w+","?")

but it does not leave the @@Identity alone. So I did this next...
Regex.Replace(commandText, @"[^@]@\w+","?")

But that always removes the character before the single '@' also... such
as...
Update table Set (Name?) Where ID =?

Anyone know how to fix this?

FYI,
usually you create a standard ODbcCommand like this...
cmd = new OdbcCommand("SELECT * FROM Customers " +
"WHERE Country = ? AND City = ?", conn);

However, I have a generic data access DLL that works for SQL or
Click to expand...
ODBC.
The
same code creates one command...
cmd = new GenCommand("SELECT * FROM Customers " +
"WHERE Country = @Country AND City = @City", conn);

The GenCommand class handles creating the appropriate underlying data
provider instance (either SqlCommand or OdbcCommand), and formats the
command text before passing to the constructor or the provider's type.
For
those interested, you can see details from link in my signature.

--
Michael Lang, MCSD
See my .NET open source projects
http://sourceforge.net/projects/dbobjecter (code generator)
http://sourceforge.net/projects/genadonet ("generic" ADO.NET)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top