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?
Query Parameter Metadata
-
- Posts: 10
- Joined: Fri 25 Mar 2011 15:17
- Location: Please select
-
- Posts: 10
- Joined: Fri 25 Mar 2011 15:17
- Location: Please select
Re: Query Parameter Metadata
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?
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
Please use the '@' prefix instead of ':':
For more information about using parameters, refer to http://www.devart.com/dotconnect/sqlser ... eters.html.
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;
}
-
- Posts: 10
- Joined: Fri 25 Mar 2011 15:17
- Location: Please select
Re: Query Parameter Metadata
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?
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?
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Query Parameter Metadata
When the following code executes:
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.CommandText = "select * from customer where name = @name";
myComm.ParameterCheck = true;
Code: Select all
myComm.Parameters["@name"].Value = 10;