Parse sql parameters from commandtext

Question

Is it possible to parse sql parameters from plain commandtext?

e.g.

//cmdtext = SELECT * FROM AdWorks.Countries WHERE id = @id
SqlCommand sqlc = new SqlCommand(cmdtext);
SqlParameterCollection parCol = sqlc.Parameters //should contain now 1 paramter called '@id'

this may help http://www.codeproject.com/Articles/32524/SQL-Parser — Sriram Sakthivel, Feb 28 '14 at 07:05
not really - your link just points to a simple statement rebuilding tool — MJay, Feb 28 '14 at 07:10

score 4 · Answer 1 · answered Jan 06 '15 at 08:34

4

If a SQL Server is available, the best option may be to simply ask the server what it thinks; the server has parsing and metadata functions built in, for example sp_describe_undeclared_parameters.

answered Jan 06 '15 at 08:34

Marc Gravell

1,026,079
266
2,566
2,900

MJay · Answer 2 · 2014-02-28T08:29:50.507

1

I ended up with this extention method (since I don't think there's a built in function):

public static class SqlParExtension
{
    public static void ParseParameters(this SqlCommand cmd)
    {
        var rxPattern = @"(?<=\= |\=)@\w*";
        foreach (System.Text.RegularExpressions.Match item in System.Text.RegularExpressions.Regex.Matches(cmd.CommandText, rxPattern))
        {
            var sqlp = new SqlParameter(item.Value, null);
            cmd.Parameters.Add(sqlp);
        }
    }
}

usage:

//cmdtext = SELECT * FROM AdWorks.Countries WHERE id = @id
SqlCommand sqlc = new SqlCommand(cmdtext);
sqlc.ParseParameters();

sqlc.Parameters["@id"].Value = value;

edited Feb 28 '14 at 08:29

answered Feb 28 '14 at 08:15

MJay

487
1
6
13

Counter example is a *commented out* parameter: `select /*@NotAParameter*/ 1` – Dmitry Bychenko Mar 01 '16 at 15:59
Parameter with between don't work with this : `where SomeField between @DateFrom and @DateTo` – Patrick Halley Aug 01 '23 at 17:23

score 0 · Answer 3 · answered Feb 28 '14 at 07:08

0

I will have to make sure about this but I'm sure you must add the range of parameters to the command. Like I say I will have to come back with this but you can try doing something like:

// Create a collection of parameters with the values that the procedure is expecting in your SQL client.
SqlParameter[] parameters = { new SqlParameter("@id", qid), 
new SqlParameter("@otherValue", value) };

// Add teh parameters to the command.
sqlc.Parameters.AddRange(parameters)

answered Feb 28 '14 at 07:08

Johan Aspeling

765
1
13
38

this is exactly what I tried to avoid. I want to receive the parameter collection from the query itself, not adding each by myself. – MJay Feb 28 '14 at 07:13
Do you receive the query as a string like you specified in your comment? If so you can use a simple Regular Expression to retrieve the values that is preceded by the '@' character. Im not sure if this is what you want to achieve though.. You can use this Regular Expression to get all of the variables from the select statement '@{1}[A-z]+' – Johan Aspeling Feb 28 '14 at 07:23
I have a bunch of queries, where I want to read the parameters, and then iterate through, to pass the values. To be more precise, is there a built in function for that? – MJay Feb 28 '14 at 07:28
Not that I'm aware of. I will play around a bit and let you know what I discover – Johan Aspeling Feb 28 '14 at 07:36

bbsimonbb · Answer 4 · 2016-05-13T14:44:10.627

You would be very welcome to have a look at my VS2015 extension, QueryFirst, that generates wrapper classes from .sql files, harvesting parameter declarations directly from your sql. You need to declare your parameters in the --designTime section of your request, but then you find them again directly as inputs to the Execute(), GetOne() or ExecuteScalar() methods. These methods return POCOs with meaningul property names. There's intellisense everywhere, and you don't have to type a line of parameter code, or connection code, or command code, or reader code, among NUMEROUS OTHER ADVANTAGES :-).

Parse sql parameters from commandtext

4 Answers4

Linked