SqlCommand (CommandType Text) retrieve param collection

Question

Is there a way to get the parameter collection from SqlCommand when CommandType = Text?

For instance:

string MyDinamicSql = @"SELECT * FROM USERS where USERName = @Param1 and USERLogin=@Param2";
SqlCommand comand = connection.CreateCommand();
comand.CommandText = MyDinamicSql;
comand.CommandType = System.Data.CommandType.Text;

//Do something for fill comand.Parameters from MyDinamicSql...

I want a way to get a parameter collection with [@Param1,@Param2]... I don't know the sql queries (they are dynamic) and I want to get the parameters for my application to create them as input controls.

I can't do this with SqlCommandBuilder.DeriveParameters(command), because it only works with stored procedures.

Thank you!

How are the SQL strings being generated? Are they being built up from code or are they coming from your users? — Conrad Frix
– Conrad Frix, Commented Jan 27, 2012 at 18:43
They were written without a place for parameter name's store... :-/ They came from users, and my app don't know the query parameters. — rd_rscan
– rd_rscan, Commented Jan 27, 2012 at 18:55
In delphi I had something like FetchParams (on ClientDataSet) when its engine parse sql and do this. But in C# I not found any like it. — rd_rscan
– rd_rscan, Commented Jan 27, 2012 at 19:07
So the reason why I asked is because you'll need to either parse it yourself, or getting a third party library. If you can control the way the SQL is constructed it would make parsing it yourself a little easier. I suspect that why parameters on data.stackexchange.com look like ##Param1## — Conrad Frix
– Conrad Frix, Commented Jan 27, 2012 at 19:12

egrunin · Accepted Answer · 2012-01-27 18:30:49Z

2

No - until SQL Server parses the command, it's just a string.

answered Jan 27, 2012 at 18:30

egrunin

25.2k8 gold badges53 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rd_rscan Over a year ago

Ok, is there a way to force sql server parse my command text to get a parameter collection?

egrunin Over a year ago

SQL Server won't even try unless you give it parameters, sorry :(

Collectives™ on Stack Overflow

SqlCommand (CommandType Text) retrieve param collection

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related