Extract parameters from SQL statement

[Phil]

Hei,
I'm currently trying to write a program in C# that will allow users to
parametrize their queries.
For instance, I have a query like this:

SELECT * FROM Customers Where Region = @Region AND Gender > @Gender

How can I extract the Parameters names without using String
manipulation (which is not perfect since sql statements can also
contain '@' in LIKE clauses for example)

I tried this way
Dim comm As New OleDbCommand(SqlStatement, myCon)
comm.Prepare()
MsgBox(comm.Parameters.Count.ToString())
comm.Dispose()
myCon.Close()
but this always return me 0 (zero)

Do you have any idea on how to solve this generic problem?
Regars
Philippe Graca

[Erland Sommarskog]

[posted and mailed, please reply in news]

Phil (ph************@hotmail.com) writes:

I'm currently trying to write a program in C# that will allow users to
parametrize their queries.
For instance, I have a query like this:

SELECT * FROM Customers Where Region = @Region AND Gender > @Gender

How can I extract the Parameters names without using String
manipulation (which is not perfect since sql statements can also
contain '@' in LIKE clauses for example)

I tried this way
Dim comm As New OleDbCommand(SqlStatement, myCon)
comm.Prepare()
MsgBox(comm.Parameters.Count.ToString())
comm.Dispose()
myCon.Close()
but this always return me 0 (zero)

C#? That looks like Visual Basic to me. :-?

IF I understand you correctly, you have a query string like:

SELECT * FROM tbl WHERE @a LIKE '@abc%' AND @bulle = 98

And you want to find @a and @bulle but not @abc? There is no support for
this in ADO .Net, but you would have to parse that string yourself. While
T-SQL is very quirky language to parse completely, this particular case
is not that rough: Traverse the string character by character (which
should be a lot easier in C# I guess, and when you encounter any @, ', "
and /* and -- you have a state change. If you see a ', you are blind to
everything but a new ', same goes for ". If you see /*, you look for */.
And if you see @, you continue until till you see a character that is
not legal in an identifier.

There is still a tricky part or two. Your parser must not misbehave if
fed illegal SQL. And how to handle of the users have entered a variable
in a place where a variable is not permitted, for instance in for a
table name. And most of all: how do you know the data type of the variable?

I think it is somewhere you need to sit down and reconsider whether it
is actually worth the effort.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Phil]

Hi Erland & thx
1-> Yes, this is vb.net ;)
2-> I've thought of this kind of state parsing mechanism but this
cames to me as a not 100% reliable solution
I was hoping that SQL server had some kind of API (if ado.net doesn't)
so that I could identify the variables in a TSQL command. Hence, I
think I'm going to give up for the moment.
Thx again
Phil

Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...

[posted and mailed, please reply in news]

Phil (ph************@hotmail.com) writes:

I'm currently trying to write a program in C# that will allow users to
parametrize their queries.
For instance, I have a query like this:

SELECT * FROM Customers Where Region = @Region AND Gender > @Gender

How can I extract the Parameters names without using String
manipulation (which is not perfect since sql statements can also
contain '@' in LIKE clauses for example)

I tried this way
Dim comm As New OleDbCommand(SqlStatement, myCon)
comm.Prepare()
MsgBox(comm.Parameters.Count.ToString())
comm.Dispose()
myCon.Close()
but this always return me 0 (zero)

C#? That looks like Visual Basic to me. :-?

IF I understand you correctly, you have a query string like:

SELECT * FROM tbl WHERE @a LIKE '@abc%' AND @bulle = 98

And you want to find @a and @bulle but not @abc? There is no support for
this in ADO .Net, but you would have to parse that string yourself. While
T-SQL is very quirky language to parse completely, this particular case
is not that rough: Traverse the string character by character (which
should be a lot easier in C# I guess, and when you encounter any @, ', "
and /* and -- you have a state change. If you see a ', you are blind to
everything but a new ', same goes for ". If you see /*, you look for */.
And if you see @, you continue until till you see a character that is
not legal in an identifier.

There is still a tricky part or two. Your parser must not misbehave if
fed illegal SQL. And how to handle of the users have entered a variable
in a place where a variable is not permitted, for instance in for a
table name. And most of all: how do you know the data type of the variable?

I think it is somewhere you need to sit down and reconsider whether it
is actually worth the effort.

[Dan Guzman]

Like Erland said, you'll need to parse the SQL statement string if you let
users enter freeform text, . Another technique I've seen is to include SQL
expression builder functionality in an app (e.g. column name/operator/value)
so that you can parameterize the statement without parsing.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Phil" <ph************@hotmail.com> wrote in message
news:78**************************@posting.google.c om...

Hi Erland & thx
1-> Yes, this is vb.net ;)
2-> I've thought of this kind of state parsing mechanism but this
cames to me as a not 100% reliable solution
I was hoping that SQL server had some kind of API (if ado.net doesn't)
so that I could identify the variables in a TSQL command. Hence, I
think I'm going to give up for the moment.
Thx again
Phil

Erland Sommarskog <es****@sommarskog.se> wrote in message
news:<Xn**********************@127.0.0.1>...

[posted and mailed, please reply in news]

Phil (ph************@hotmail.com) writes:

> I'm currently trying to write a program in C# that will allow users to
> parametrize their queries.
> For instance, I have a query like this:
>
> SELECT * FROM Customers Where Region = @Region AND Gender > @Gender
>
> How can I extract the Parameters names without using String
> manipulation (which is not perfect since sql statements can also
> contain '@' in LIKE clauses for example)
>
> I tried this way
> Dim comm As New OleDbCommand(SqlStatement, myCon)
> comm.Prepare()
> MsgBox(comm.Parameters.Count.ToString())
> comm.Dispose()
> myCon.Close()
> but this always return me 0 (zero)

C#? That looks like Visual Basic to me. :-?

IF I understand you correctly, you have a query string like:

SELECT * FROM tbl WHERE @a LIKE '@abc%' AND @bulle = 98

And you want to find @a and @bulle but not @abc? There is no support for
this in ADO .Net, but you would have to parse that string yourself. While
T-SQL is very quirky language to parse completely, this particular case
is not that rough: Traverse the string character by character (which
should be a lot easier in C# I guess, and when you encounter any @, ', "
and /* and -- you have a state change. If you see a ', you are blind to
everything but a new ', same goes for ". If you see /*, you look for */.
And if you see @, you continue until till you see a character that is
not legal in an identifier.

There is still a tricky part or two. Your parser must not misbehave if
fed illegal SQL. And how to handle of the users have entered a variable
in a place where a variable is not permitted, for instance in for a
table name. And most of all: how do you know the data type of the
variable?

I think it is somewhere you need to sit down and reconsider whether it
is actually worth the effort.