CODEWITHSUNDEEP
c#sqlsql-serverparameterssqlcommand
MJay
MJay

Reputation: 537

Parse sql parameters from commandtext

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'

Upvotes: 2

Views: 3527

Answers (4)

bbsimonbb
bbsimonbb

Reputation: 29022

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 :-).

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1064324

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.

Upvotes: 3

MJay
MJay

Reputation: 537

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;

Upvotes: 1

Johan Aspeling
Johan Aspeling

Reputation: 817

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)

Upvotes: 0

Related Questions