Query Parameter Metadata

[keithnolan]

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]

Please refer to http://www.devart.com/dotconnect/sqlser ... l#autosync.

[keithnolan]

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]

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]

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]

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;