Geek Answers Handbook

.NET SqlCommand - searching for parameters in embedded queries (Regex?)

I have a method that uses sp_sproc_columns to find all the parameters that need to be sent for a stored procedure.

My question is, how can I do something like this for inline SQL queries?

I want to get a list of parameters that the request expects.

Would the only way to achieve this be to use regex? Any examples?

Example:

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

I need to get a list containing @id and @name .

+4
source share
4 answers

The way to use RegularExpression is also not too heavy - if you prefer to find your options:

  Regex r = new Regex(@"(?<Parameter>@\w*)", RegexOptions.Compiled); foreach (Match m in r.Matches(sqlStatement)) { if(m.Success) { string parameterName = m.Value; } }

However, this will only give you the names parameter - it cannot guess or determine the type of the parameter or any other parameter metadata - so in the end it can be a quick, but probably dirty way to do it.

Mark

+2
source

I suspect you will have to completely parse SQL. This can be harder than you expect, especially if your SQL is rude enough to contain things like:

  • internal execution of a stored procedure using named parameters; e.g. EXEC MyProc @foo = @localArg - only @localArg is required here for your request
  • with a line with 'some text @foo more text' in it - possibly due to a call to sp_ExecuteSQL ( @foo not part of your query)
0
source

Check the following code to get the parameter 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
0
source

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

In other cases, I have always thought that the best way, if your teams are at least persistent, is to use Designer support in Visual Studio. Either create a strongly typed DataSet and add a query, or do it the old-fashioned way and drag SqlConnection and SqlCommand onto the design surface. In any case, when you configure the CommandText as an operator with parameters, the Designer will ask you if you want the Parameters collection to fill up. As a result, code is created to create SqlCommand with a set of parameters already created. All you have to do is set the parameter values ​​and everything will be set.

0
source

More articles:


All Articles