How to describe parameter in ADO.NET/SqlClient

  • Thread starter Thread starter Aleksey Dmitriyev
  • Start date Start date
A

Aleksey Dmitriyev

How can I describe parameters in my SQL statement before Executing it?

For example, there a statement "select * from emp where ename = @ename". I
need to find out what type, size, and etc. of @ename parameters I need to
provide. I believe SqlCommand.Prepare will fail if I don't supply a
parameter for @name. Bu what if I don't know it?

I am looking for something similar to SQLDescribeParam() in ODBC or
ICommandWithParameters::GetParameterInfo() in OLEDB.

I cannot find anything like that in ADO.NET/SqlClient. My current ideas
include using SET FMTONLY ON, or invoking sp_prepare, but I don't see a
simple way to do that. Does anyone have an idea?

--Aleksey
 
My take on this issue is that its always better for the Application to know
the schema of the table it is trying to SELECT. If you still think your app
can not be aware of the schema, you have following Options
1. Specify the parameter name and the value of the parameter, this will make
some "guess" about the DBType based on the param value. But this could also
be wrong. To shield against this you need at very minimum specify the DbType
or even better the SqlDbType property of the Parameter.
2.You can use the SqlCommandBuilder.DeriveParameters() method only on Stored
Procedures. It is based on the same lines as the OLEDB and ODBC
functionality.
3. Manually get the schema information from server and use the information
to build the parameters.
HTH,
Sushil.
 
There may be many reasons why application cannot know upfront what
parameters exist in the sql statement. For example, the sql can be entered
by user manually, or may come from an external source. In my case, I need to
convert existing application, which is dynamically describing parameters.
Therefore, solutions (1, 2) are not applicable. Solution (3) is not easy to
implement, because it requires parsing sql statement and doing a lot of
extra legwork.

I am very unhappy these features are not available in SqlClient. At this
moment, I am trying to choose if I want to switch to Sql Native client
available for SQL Server 2005.

--Aleksey
 
Back
Top