Page 1 of 1
Query Parameter Metadata
Posted: Tue 30 Jul 2013 14:45
by 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?
Re: Query Parameter Metadata
Posted: Wed 31 Jul 2013 06:46
by Shalex
Re: Query Parameter Metadata
Posted: Thu 17 Oct 2013 15:35
by 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?
Re: Query Parameter Metadata
Posted: Fri 18 Oct 2013 12:46
by 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.
Re: Query Parameter Metadata
Posted: Fri 18 Oct 2013 13:38
by 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?
Re: Query Parameter Metadata
Posted: Mon 21 Oct 2013 15:18
by 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;