.NET SqlCommand - finding Parameters in inline querys (Regex?)

Question

I've got a method that uses sp_sproc_columns to find all parameters that need to be send for a stored procedure.

My question is how can I do something similar for inline SQL querys?

I want to get a list of the parameters the query is expecting.

Would the only way achieving this will be to use Regular expression? Any examples?

---

Example:

sql = "SELECT * FROM tbl WHERE id = @id AND name = @name"

I need to get a list that will contain @id and @name.

Answer 1

I suspect you would have to fully parse the SQL. It might be more complex than you expect, especially if your SQL is rude enough to contain things like:

• internally executing a stored procedure using named parameters; for example EXEC MyProc @foo = @localArg - here only @localArg is needed for your query
• having a string with 'some text @foo more text' in it -perhaps due to calling sp_ExecuteSQL (@foo is not part of your query)

Answer 2

Check the following code to get the Parameters Name in Inline queries:

  Dim SQL As String =  "SELECT * FROM tbl WHERE id = @id AND name = @name"

  Dim arr = SQL.Split(" "c)
  For Each str As String In arr
    If str.StartsWith("@") Then
      MessageBox.Show(str)
    End If
  Next

Answer 3

In the case of the SQL Server provider, and if you are using a stored procedure, you can use the SqlCommandBuilder.DeriveParameters method.

For other cases, I've always found that the best way, if your commands are at least constant, is to use the Designer support in Visual Studio. Either create a strongly-typed DataSet and add a query, or do it the old fashioned way and drag a SqlConnection and SqlCommand onto a design surface. In either case, when you configure the CommandText to be a statement with parameters, the Designer will ask you if you want the Parameters collection filled in. The result is that it will have generated the code to create a SqlCommand with the parameters collection already created. All you'll need to do is set the parameter values, and you're all set.

Answer 4

The way to use a RegularExpression isn't too hard either - if that's the way you prefer to find your parameters:

   Regex r = new Regex(@"(?<Parameter>@\w*)", RegexOptions.Compiled);

   foreach (Match m in r.Matches(sqlStatement))
   {
      if(m.Success)
      {
         string parameterName = m.Value;
      }
   }

However, this is only going to give you the parameter names - it can't possibly guess or determine the parameter type or any of the other parameter metadata - so in the end, this might be a quick, but probably also "dirty" way to do it.

Marc