Get parameters for a ADO.NET CommandText

Question

Is there any way (maybe even in ADO.NET) to get a list of the parameters of a CommandText?

That is to say, given the following query:

INSERT INTO TABLE test VALUES(@value1, @value2)

Is there any way to know that the query parameters are value1, value2? Or should I have to parse the query on my own to find them (of course '@' syntax may change from one db engine to other...)?

Clarification:

I want to know the parameters name and the order in the query before having to fill the command parameters dictionary.

I remember I needed this too, unfortunately ended up with parsing it :( — Yves, Jan 23 '16 at 22:37

Rahul · Answer 1 · 2016-01-23T21:22:48.917

0

You can use SqlParameterCollection.Count property to get that information.

So you can do like

   SqlCommand cmd = new SqlCommand("insert into table test values(@value1, @value2)", con);
        cmd.Parameters.Add("@value1", SqlDbType.VarChar).Value = "test";
        cmd.Parameters.Add("@value2", SqlDbType.VarChar).Value = "test";

        int count = cmd.Parameters.Count; // you get the parameter count

You as well get the name of parameter using the parameter collection like

        string p1 = cmd.Parameters[0].ParameterName;
        string p2 = cmd.Parameters[1].ParameterName;

Not exactly sure what you mean by parameters order in the query

edited Jan 23 '16 at 21:22

answered Jan 23 '16 at 21:06

Rahul

76,197
13
71
125

I think the OP means they have a query string and they want to determine the parameters from the string. – juharr Jan 23 '16 at 21:27
@juharr, possibly yes looks like. well he can store the `SqlParameterCollection` to a variable and later can use that to get the information ... else parse the string. Thanks for pointing. if the answer anyway doesn't helps then I won't keep it. – Rahul Jan 23 '16 at 21:31
@juharr is right I want the parameters name from the query text to ensure that when the Parameters are filled, there is no mismatch in number/ order/name (you know, Oracle issues). – danijepg Jan 23 '16 at 21:34
@danijepg, then misinterpret here ... BTW no other way than parsing the query string which will be ugly if it's a bugger query. – Rahul Jan 23 '16 at 21:36

score 0 · Answer 2 · edited May 23 '17 at 11:59

If the query is a stored procedure, you can use SqlCommandBuilder.DeriveParameters (see How can I retrieve a list of parameters from a stored procedure in SQL Server). However that unfortunately only works for CommandType=StoredProcedure

In SQL Server 2012 (and above) you can use sp_describe_undeclared_parameters (see https://msdn.microsoft.com/en-us/library/ff878260.aspx)

Doesn't seem like there is any alternative for lower versions other than parsing the SQL query text directly (regex, or possibly using the SQLDOM that ships with SSDT (or via nuget): https://blogs.msdn.microsoft.com/arvindsh/2013/04/04/using-the-transactsql-scriptdom-parser-to-get-statement-counts/ )

Edit: simple F# example of finding parameters using the Microsoft.SqlServer.TransactSql.ScriptDom nuget:

#r "Microsoft.SqlServer.TransactSql.ScriptDom"

open System.IO
open System.Collections.Generic
open Microsoft.SqlServer.TransactSql.ScriptDom

let query = @"select * from dbo.Customers where FirstName=@FirstName"

let parser = new TSql120Parser(true)
let errors : ref<IList<ParseError>> = ref(null)

let frag = parser.Parse(new StringReader(query), errors) // the actual work

frag.ScriptTokenStream
|> Seq.filter (fun f -> f.TokenType = TSqlTokenType.Variable)
|> Seq.iter (fun t -> (printfn "Type = %A; Name = %s" t.TokenType t.Text))

outputs

Type = Variable; Name = @FirstName

...however if the script involves temporary variables you can easily get false positives, eg:

declare @temp nvarchar(50) = @FirstName
select * from dbo.Customers where FirstName=@temp

...finds @temp twice, and @FirstName

Get parameters for a ADO.NET CommandText

2 Answers2