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'
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.
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;
select /*@NotAParameter*/ 1
where SomeField between @DateFrom and @DateToI 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)
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 :-).
