OleDb – what a blast from the past. Got a couple of basic questions in the last week on how to access FoxPro data from .NET via OleDb and both questions got tripped up with how the query parameters are passed. It’s been so long since I’ve used the OleDb driver I had to look this up again myself to remember how this works through FoxPro OleDb.
The issue is that the VFP OleDb driver deals with parameters only on a positional basis rather than on a named basis. Since most examples of ADO.NET access data with SqlClient and use named parameters you’ll find that those types of queries against the VFP driver will fail.
With SqlClient you can specify a parameter by name like this:
SqlCommand cmd = new SqlCommand("select * from wws_customers where company like @Company");
cmd.Parameters.Add( new SqlParameter("@Company",'West Wind%');
Parameters are named and can be provided out of order – the native Sql Syntax handles matching up the parameter definitions with the named parameters in the actual string.
Visual FoxPro handles parameters using ? evaluations. In pure FoxPro code you can use placeholders with ?lcParameter where lcParameter has to be a variable in scope. When using OleDb though there’s no real operating environment and variables aren’t set so you typically only supply a parameters as a sole ? (without a name) which effectively is treated as a positional parameter.
OleDb access to the FoxPro provider requires that you use only a ? placeholder like this:
string connString = @"Provider=vfpoledb.1;Data Source=c:\wwapps\wc3\wwdemo;Exclusive=false;Nulls=false";
OleDbConnection Conn = new OleDbConnection(connString);
OleDbCommand Cmd = new OleDbCommand("select * from tt_cust where Company > ? and Company < ? ");
Cmd.Connection = Conn;
Cmd.Parameters.Add(new OleDbParameter("?", "D"));
Cmd.Parameters.Add(new OleDbParameter("?", "M"));
Conn.Open();
IDataReader reader = Cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader["Company"]);
}
Conn.Close();
The named parameter approach obviously is more flexible as you don’t have to ensure the same order when parameters are added as you have to with the VFP access. It’s crucial that parameters are added in the right order in this scenario or you’ll get invalid results.
In the example above I explicitly used just the ? for the parameter name, but the name is irrelevant in the driver. You can give the OleDbParameter any name of you choosing so you can just as easily use:
Cmd.Parameters.Add(new OleDbParameter("?startChar", "D"));
Cmd.Parameters.Add(new OleDbParameter("?endChar", "M"));
which is a little easier to read and keep straight. Remember though the position is what counts with those parameters! This should work both for query parameters as above in the SELECT WHERE clause as well as for INSERT/UPDATE statements and other explicit commands.
SQL Literal Strings – Don’t go there, please!
Incidentally both people who asked about the parameters ended up not using Sql parameters, but instead embedded the evaluated parameter values directly into the SQL string to get their queries to work.
So rather than using parameters they’d use code like this:
OleDbCommand oCmd = new OleDbCommand("select * from tt_cust where Company > '" +
startChar + "' and Company < '" + endChar + "'");
<rant>
This is EVIL! Please do not go down this path and embed literal values into SQL strings to ensure your applications are not vulnerable to SQL Injection attacks. SQL Injection attacks even for simple things like this are dangerous and once code is written as above they are difficult to find and fix later. Regardless of development language or SQL interface driver you are using make sure you use the provided parameter embedding language constructs which will ensure that parameter are only used as parameter values rather than embedded SQL commands that can compromise your database.
</endrant>
It’s kind of interesting to look back at raw ADO.NET code – man I can’t remember how long it’s been since I’ve written code like this actually – instead relying on either a Data Access layer or ORM to handle data access for me transparently. But there are still lots of people starting out with raw ADO.NET and there’s nothing wrong with that of course. Maybe one of the days I’ll post my basic DAL component here to help some folks to get past that initial pain – especially when coming from the ease of FoxPro data access :-}
Other Posts you might also like