Query Parameter Metadata

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Microsoft SQL Server
Post Reply
keithnolan
Posts: 10
Joined: Fri 25 Mar 2011 15:17
Location: Please select
Contact:

Query Parameter Metadata

Post by keithnolan » Tue 30 Jul 2013 14:45

Hi,

I was wondering if it's possible with your driver to determine the parameters and their types that a query expects based on a textual SQL query.

For instance if I have a query like "select * from customer where name = :name" is it possible for me to determine that their is 1 parameter called 'name' expected in this query?

Shalex
Site Admin
Posts: 8829
Joined: Thu 14 Aug 2008 12:44

Re: Query Parameter Metadata

Post by Shalex » Wed 31 Jul 2013 06:46


keithnolan
Posts: 10
Joined: Fri 25 Mar 2011 15:17
Location: Please select
Contact:

Re: Query Parameter Metadata

Post by keithnolan » Thu 17 Oct 2013 15:35

I can't seem to get this to work, maybe I've misunderstood the functionality.

My code is:

SqlCommand myComm = new SqlCommand();
myComm.CommandText = "select * from customer where name = :name";

com.ParameterCheck = true;
com.Prepare();

I would expect that the SqlCommand Parameter collection is now populated with one item but it is not. Any ideas?

Shalex
Site Admin
Posts: 8829
Joined: Thu 14 Aug 2008 12:44

Re: Query Parameter Metadata

Post by Shalex » Fri 18 Oct 2013 12:46

Please use the '@' prefix instead of ':':

Code: Select all

    using (SqlConnection conn = new SqlConnection()) {
        conn.ConnectionString = "TYPE_YOUR_CONNECTION_STRING";
        conn.Open();
        SqlCommand myComm = new SqlCommand();
        myComm.Connection = conn;
        myComm.CommandText = "select * from customer where name = @name";
        myComm.ParameterCheck = true;
    }
For more information about using parameters, refer to http://www.devart.com/dotconnect/sqlser ... eters.html.

keithnolan
Posts: 10
Joined: Fri 25 Mar 2011 15:17
Location: Please select
Contact:

Re: Query Parameter Metadata

Post by keithnolan » Fri 18 Oct 2013 13:38

Great that works now, thanks.

One last question, will the DbType of the parameters fetched always be AnsiString or can the correct type be determined.

e.g. in a query "Select * from customer where customer_id = :cust_id"

Where the field customer_id is a integer, can this be determined from the ParameterCheck call?

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Query Parameter Metadata

Post by Pinturiccio » Mon 21 Oct 2013 15:18

When the following code executes:

Code: Select all

myComm.CommandText = "select * from customer where name = @name";
myComm.ParameterCheck = true;
The parameter, named "@deptno", is created. This parameter is assigned SqlType=VarChar by default. When you assign a value to this parameter, the parameter type is determined by its value. For example, after executing the following code, the parameter has SqlType=Int.

Code: Select all

myComm.Parameters["@name"].Value = 10;

Post Reply